Maxdop hint

Is parallelism only triggered for select statements or do
inserts/updates/deletes also go thru parallelism such as
update a
set col1 = 1
from a join b
on a.col2=b.col3 ............
or
delete from a join b on a.col1=b.col2 ........
If so, and if we want to disable parallelism, how can we do so for
deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
hint at the end of the statement ?From Books Online , "Degree of Parallelism" topic
"The INSERT, UPDATE, and DELETE operators are executed serially; however,
the WHERE clause of either an UPDATE or DELETE, or SELECT portion of an
INSERT statement may be executed in parallel. The actual data changes are
then serially applied to the database."
You can disable parallelism for INSERT, UPDATE and DELETE where relevant, in
the same way as for SELECT, with OPTION (MAXDOP 1).
--
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23U6JVjvSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Is parallelism only triggered for select statements or do
> inserts/updates/deletes also go thru parallelism such as
> update a
> set col1 = 1
> from a join b
> on a.col2=b.col3 ............
> or
> delete from a join b on a.col1=b.col2 ........
> If so, and if we want to disable parallelism, how can we do so for
> deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
> hint at the end of the statement ?
>
>
>|||Parallelism can also be set globally for the server in SEM>
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23U6JVjvSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Is parallelism only triggered for select statements or do
> inserts/updates/deletes also go thru parallelism such as
> update a
> set col1 = 1
> from a join b
> on a.col2=b.col3 ............
> or
> delete from a join b on a.col1=b.col2 ........
> If so, and if we want to disable parallelism, how can we do so for
> deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
> hint at the end of the statement ?
>
>
>

Max Value in Column Other than Return Value Column

Hi,
This may be a very simple query but I couldn't figure out how to do it. I
have a table with two columns, COL1 and COL2. I need to write a query that
will return the value of COL1 from the row where COL2 has the greatest value
.
For example,
COL1 COL2
-- --
A 2
B 3
C 1
The query should return "B".
I don't know if this makes a difference, but this query will actually be a
subquery in the select clause of a main query.
Thanks!
JohnSELECT COL1
FROM myTable
WHERE COL2=(SELECT MAX(COL2) FROM myTable)
Note that if COL2 is not unique you may get multiple
rows back|||Here is one way to do it:
declare @.tbl table
(
col1 varchar (10)
, col2 int
)
insert into @.tbl
values ('A',1)
insert into @.tbl
values ('B',3)
insert into @.tbl
values ('C',2)
-- Actual Query
select a.Col1
from @.tbl a
inner join (select MAX(col2) col2
from @.tbl
) b
on a.col2 = b.col2
"John Walker" wrote:

> Hi,
> This may be a very simple query but I couldn't figure out how to do it. I
> have a table with two columns, COL1 and COL2. I need to write a query tha
t
> will return the value of COL1 from the row where COL2 has the greatest val
ue.
> For example,
> COL1 COL2
> -- --
> A 2
> B 3
> C 1
> The query should return "B".
> I don't know if this makes a difference, but this query will actually be a
> subquery in the select clause of a main query.
> Thanks!
> John
>|||Ok, i see. You have to use a subquery to first get that Max value then use
that to filter. Yeah that makes sense.
Thanks,
John
"John Walker" wrote:

> Hi,
> This may be a very simple query but I couldn't figure out how to do it. I
> have a table with two columns, COL1 and COL2. I need to write a query tha
t
> will return the value of COL1 from the row where COL2 has the greatest val
ue.
> For example,
> COL1 COL2
> -- --
> A 2
> B 3
> C 1
> The query should return "B".
> I don't know if this makes a difference, but this query will actually be a
> subquery in the select clause of a main query.
> Thanks!
> John
>|||Or you could use TOP:
SELECT TOP 1 col1
FROM T1
ORDER BY col2 DESC;
Note though, that if col2 is not unique, this query is non-deterministic
(more than one possible "correct" result), and will still return a single
row.
Unlike the following query which can potentially return multiple rows:
SELECT col1
FROM T1
WHERE col2 =
(SELECT MAX(col2)
FROM T1);
If col2 is non-unique, and you're still after a deterministic result with a
single row, you need to introduce a tiebreaker. For example, you can use the
primary key:
SELECT TOP 1 col1
FROM T1
ORDER BY col2 DESC, pk DESC;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"John Walker" <JohnWalker@.discussions.microsoft.com> wrote in message
news:AFF206D6-74E3-482E-B095-D128F2F90A1A@.microsoft.com...
> Ok, i see. You have to use a subquery to first get that Max value then
> use
> that to filter. Yeah that makes sense.
> Thanks,
> John
> "John Walker" wrote:
>