WITH DROP_EXISTING = ON Fails before I create the INDEX on SQL Server Instance
-
I execute this statement on my PC and it works:
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
ON Production.WorkOrder(ProductID)
WITH (FILLFACTOR = 80,
PAD_INDEX = ON,
DROP_EXISTING = ON);
GO
I execute this statement and it fails because the Index does not exists
CREATE NONCLUSTERED INDEX IDX_ENTITY_DIMENSION_ED_TRANSACTION_ID ON dw.ENTITY_DIMENSION (ED_TRANSACTION_ID)
WITH (FILLFACTOR = 80,
PAD_INDEX = ON,
DROP_EXISTING = ON);
GO
If I create the Index without the Options and execute the command with the options it is successful.
Is there an option that is set that controls this behavior?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
I tried this on my PC. Same result.
Seems to be a limitation of DROP_EXISTING :unsure:
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
lokeshvij (7/22/2012)
I tried this on my PC. Same result.Seems to be a limitation of DROP_EXISTING :unsure:
Did it work on not?
I get different results and it appears to be machine dependent.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
It did not work for me as well.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
From what I can tell reading BOL, this is as designed.
DROP_EXISTING = { ON | OFF }
Specifies that the named, preexisting clustered, or nonclustered is dropped and rebuilt. The default is OFF.
ON
The existing index is dropped and rebuilt. The index name specified must be the same as a currently existing index; however, the index definition can be modified. For example, you can specify different columns, sort order, partition scheme, or index options.
OFF
An error is displayed if the specified index name already exists.
-
I determined that if the Index does not exist you will get the error no matter what.
You have to first create the Index and then execute with that option.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Welsh Corgi (7/22/2012)
I determined that if the Index does not exist you will get the error no matter what.You have to first create the Index and then execute with that option.
As I said, as designed. If you use DROP_EXISTING = ON, the index must exist. If it doesn't, you get an error.
-
Yes, but I'm trying to create the Index without error regardless of whether the Index Exists or not.
So correct me but I don't believe that you directly answered my question?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Then you need to do something like this prior to the CREATE INDEX statement:
if exists(select 1
from sys.tables t inner join sys.indexes i on (t.object_id = i.object_id)
where t.object_id = object_id('dbo.TestUpd') and i.name = 'IX_NotNullID')
drop index IX_NotNullID on dbo.TestUpd;
This will check if the index esixts on the table, and if so, drop it. You the follow that with the CREATE INDEX statement without using the DROP_EXISTING = ON.
-
Realizing I have an unnecessary join, this would also work:
if exists(select 1
from sys.indexes i
where i.object_id = object_id('dbo.TestUpd') and i.name = 'IX_NotNullID')
drop index IX_NotNullID on dbo.TestUpd;
-
Or you could do either of the following:
begin try
create index IX_NotNullID on dbo.TestUpd (
TestUpdId,
TestVal
)
where TestVal is not null
with (drop_existing = off);
end try
begin catch
create index IX_NotNullID on dbo.TestUpd (
TestUpdId,
TestVal
)
where TestVal is not null
with (drop_existing = on);
end catch
go
begin try
create index IX_NotNullID on dbo.TestUpd (
TestUpdId,
TestVal
)
where TestVal is not null
with (drop_existing = on);
end try
begin catch
create index IX_NotNullID on dbo.TestUpd (
TestUpdId,
TestVal
)
where TestVal is not null
with (drop_existing = off);
end catch
go
-
Lynn Pettis (7/22/2012)
Realizing I have an unnecessary join, this would also work:if exists(select 1
from sys.indexes i
where i.object_id = object_id('dbo.TestUpd') and i.name = 'IX_NotNullID')
drop index IX_NotNullID on dbo.TestUpd;
I'm doing a IS NOT EXISTS and IF EXISTS on CREATE and DROP FK's.
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Lynn Pettis (7/22/2012)
Or you could do either of the following:begin try
create index IX_NotNullID on dbo.TestUpd (
TestUpdId,
TestVal
)
where TestVal is not null
with (drop_existing = off);
end try
begin catch
create index IX_NotNullID on dbo.TestUpd (
TestUpdId,
TestVal
)
where TestVal is not null
with (drop_existing = on);
end catch
go
begin try
create index IX_NotNullID on dbo.TestUpd (
TestUpdId,
TestVal
)
where TestVal is not null
with (drop_existing = on);
end try
begin catch
create index IX_NotNullID on dbo.TestUpd (
TestUpdId,
TestVal
)
where TestVal is not null
with (drop_existing = off);
end catch
go
Thank you!:cool:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply