August 13, 2012 at 2:12 pm
How can I make the following a little more error proof where I check the Table and Schema Name?
IF EXISTS (SELECT Name FROM sysindexes WHERE Name = 'IDX_QUOTE_DIMENSION_QD_TRANSACTION_ID')
DROP INDEX QUOTE_DIMENSION.IDX_QUOTE_DIMENSION_QD_TRANSACTION_ID
[/code]
IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IDX_QUOTE_DIMENSION_QD_TRANSACTION_ID')
CREATE INDEX IDX_QUOTE_DIMENSION_QD_TRANSACTION_ID ON QUOTE_DIMENSION (QD_TRANSACTION_ID)
You thought, ideas or suggesstions would be greatly appreciated.
🙂
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/
August 13, 2012 at 2:17 pm
Any reason to not use the "With Drop Existing" option on Create Index, instead of an explicit drop?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2012 at 2:21 pm
I would say to create the index if it doesn't exist. Using the DROP EXISTING implies that the index already exists. We do it here to ensure that the script is rerunnable when creating new indexes, so use it when modifying existing as well.
August 13, 2012 at 2:23 pm
My question is why do the IF EXISTS ... DROP followed by an IN NOT EXISTS ... CREATE? Why not just do the IF EXISTS ... DROP folloed immediately by the CREATE?
August 13, 2012 at 2:25 pm
What's wrong with using the standard "IF EXISTS"....DROP INDEX....CREATE INDEX structure used so commonly?
Ninja'd by Lynn 😀
August 13, 2012 at 2:28 pm
obligatory compatibility nazi post:
sysindexes should be replaced with sys.indexes, as it's going to be dropped in some future version.
Lowell
August 13, 2012 at 2:32 pm
This is how I would code it. Also note that I am using sys.indexes, not sysindexes.
IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('schema.tablename') AND NAME ='indexname')
DROP INDEX indexname ON SCHEMA.tablename;
GO
CREATE INDEX indexname ON SCHEMA.tablename(columnlist);
GO
Edit: Added batch separators just to be safe. Can't remember if the CREATE INDEX has to be first in a batch and quick look in BOL didn't exactly answer the question. Will need to dig a bit more to be sure.
August 13, 2012 at 2:36 pm
Lynn Pettis (8/13/2012)
This is how I would code it. Also note that I am using sys.indexes, not sysindexes.
IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('schema.tablename') AND NAME ='indexname')
DROP INDEX indexname ON SCHEMA.tablename;
CREATE INDEX indexname ON SCHEMA.tablename(columnlist);
Why not simply:
Create Index IndexName on schema.tablename (columnlist)
with (drop_existing = on);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2012 at 2:43 pm
GSquared (8/13/2012)
Lynn Pettis (8/13/2012)
This is how I would code it. Also note that I am using sys.indexes, not sysindexes.
IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('schema.tablename') AND NAME ='indexname')
DROP INDEX indexname ON SCHEMA.tablename;
CREATE INDEX indexname ON SCHEMA.tablename(columnlist);
Why not simply:
Create Index IndexName on schema.tablename (columnlist)
with (drop_existing = on);
Because DROP_EXISTING = ON assumes that the index already exists. From BOL:
DROP_EXISTING
Specifies that the named, preexisting clustered or nonclustered index should be dropped and rebuilt. The index name specified must be the same as a currently existing index. Because nonclustered indexes contain the clustering keys, the nonclustered indexes must be rebuilt when a clustered index is dropped. If a clustered index is recreated, the nonclustered indexes must be rebuilt to take the new set of keys into account.
The DROP_EXISTING clause enhances performance when re-creating a clustered index (with either the same or a different set of keys) on a table that also has nonclustered indexes. The DROP_EXISTING clause replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. The nonclustered indexes are rebuilt once, and only if the keys are different.
If the keys do not change (the same index name and columns as the original index are provided), the DROP_EXISTING clause does not sort the data again. This can be useful if the index must be compacted.
A clustered index cannot be converted to a nonclustered index using the DROP_EXISTING clause; however, a unique clustered index can be changed to a non-unique index, and vice versa.
Note When executing a CREATE INDEX statement with the DROP_EXISTING clause, SQL Server assumes that the index is consistent, that is, there is no corruption in the index. The rows in the specified index should be sorted by the specified key referenced in the CREATE INDEX statement.
Here is a test from my SandBox database:
/****** Object: Table [dbo].[test] Script Date: 08/13/2012 14:41:53 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))
DROP TABLE [dbo].[test]
GO
/****** Object: Table [dbo].[test] Script Date: 08/13/2012 14:41:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
[testid] [int] IDENTITY(1,1) NOT NULL,
[TestVal1] [int] NULL,
[TestVal2] [varchar](255) NULL,
[TestVal3] [int] NULL,
[TestVal4] [varchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE INDEX idx_test ON dbo.test(testid) WITH (drop_existing = ON );
GO
And the error message at the end:
Msg 7999, Level 16, State 9, Line 2
Could not find any index named 'idx_test' for table 'dbo.test'.
August 13, 2012 at 2:44 pm
GSquared (8/13/2012)
Why not simply:
Create Index IndexName on schema.tablename (columnlist)
with (drop_existing = on);
Because that does not work if the index does not exist.
Create Index doesnotexist on DBO.Test (ID)
with (drop_existing = on);
Msg 7999, Level 16, State 9, Line 1
Could not find any index named 'doesnotexist' for table 'DBO.Test'.
Create With drop_existing is equivalent to DROP INDEX ... CREATE INDEX, not IF EXISTS ... DROP INDEX ... CREATE INDEX
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2012 at 2:47 pm
As for the original question...
IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'Index name here' AND object_id = OBJECT_ID('Table Name Here')
DROP INDEX ....
CREATE INDEX ...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2012 at 2:56 pm
If I remember correctly, the Index name is unique. Do you really need to verify both the object AND table name? Couldn't you just make sure that the index name exists and then drop it?
August 13, 2012 at 2:57 pm
Index names are only unique per table. It's quite possible to have the same name for an index on multiple tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2012 at 3:06 pm
Ah, thanks Gail!
August 13, 2012 at 4:07 pm
Lynn Pettis (8/13/2012)
My question is why do the IF EXISTS ... DROP followed by an IN NOT EXISTS ... CREATE? Why not just do the IF EXISTS ... DROP folloed immediately by the CREATE?
I'm Droping the Indexes before I load the table and Creating them after the load.
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 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply