Is it possible to set MAXDOP on index create

In SQL2000, I have a CREATE INDEX statement that I would like to run with
(MAXDOP 1).  When I try putting OPTION (MAXDOP 1) at the end of the sreate
statement - it throws an error.

Is there any other way to do this?

Thanks in advance.

Here is an example from
http://msdn2.microsoft.com/en-us/library/ms189329(SQL.90).aspx

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name =  N'IX_ProductVendor_V
endorID')
DROP INDEX  IX_ProductVendor_Ven
dorID ON Purchasing.ProductVendor;
GO
CREATE INDEX  IX_ProductVendor_Ven
dorID
ON Purchasing.ProductVendor (VendorID)
WITH (MAXDOP=8);
GO


"TJT" wrote:

> In SQL2000, I have a CREATE INDEX statement that I would like to run with
> (MAXDOP 1).  When I try putting OPTION (MAXDOP 1) at the end of the sreate
> statement - it throws an error.
>
> Is there any other way to do this?
>
> Thanks in advance.
>
>
>

Yes - but I think that is for SQL2005.  I am working with SQL2000


<Edgardo Valdez>; <MCSD>; "MCDBA"
< EdgardoValdezMCSDMCD
BA@discussions.microsoft.com> wrote in message
news:E8E7B53A-6E66-44D5-8251- 8D3FA33951CC@microso
ft.com...
> Here is an example from
> http://msdn2.microsoft.com/en-us/library/ms189329(SQL.90).aspx
>
> USE AdventureWorks;
> GO
> IF EXISTS (SELECT name FROM sys.indexes
>             WHERE name =  N'IX_ProductVendor_V
endorID')
>     DROP INDEX  IX_ProductVendor_Ven
dorID ON Purchasing.ProductVendor;
> GO
> CREATE INDEX  IX_ProductVendor_Ven
dorID
> ON Purchasing.ProductVendor (VendorID)
> WITH (MAXDOP=8);
> GO
>
>
> "TJT" wrote:
> 
with 
 sreate[color=darkred
] 

From Books Online SQL Server 2000

"Microsoft® SQL Server™ 2000 uses the same algorithms to determine the
degree of parallelism (the total number of separate threads to run) for
create index operations as it does for other Transact-SQL statements. The
only difference is that the CREATE INDEX, CREATE TABLE, or ALTER TABLE
statements that create indexes do not support the MAXDOP query hint. The
maximum degree of parallelism for an index creation is subject to the max
degree of parallelism server configuration option, but you cannot set a
different MAXDOP value for individual index creation operations."


"TJT" wrote:

> Yes - but I think that is for SQL2005.  I am working with SQL2000
>
>
> <Edgardo Valdez>; <MCSD>; "MCDBA"
> < EdgardoValdezMCSDMCD
BA@discussions.microsoft.com> wrote in message
> news:E8E7B53A-6E66-44D5-8251- 8D3FA33951CC@microso
ft.com... 
> with 
> sreate 
>
>
>

That is not possible with SQL2000.  But you can change the MAXDOP at the
server level with sp_configure before the Create Index and change it back
again afterwards.  But during that time it will be a server wide setting.

--
Andrew J. Kelly  SQL MVP


"TJT" <TJT@nospam.com> wrote in message
news:OEdkailWGHA.3760@TK2MSFTNGP02.phx.gbl...
> In SQL2000, I have a CREATE INDEX statement that I would like to run with
> (MAXDOP 1).  When I try putting OPTION (MAXDOP 1) at the end of the sreate
> statement - it throws an error.
>
> Is there any other way to do this?
>
> Thanks in advance.
>
>