Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

IF EXISTS DROP INDEX ... IF NOT EXISTS CREATE INDEX Expand / Collapse
Author
Message
Posted Monday, August 13, 2012 2:12 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 7:44 PM
Points: 4,240, Visits: 4,284
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/

Post #1344405
Posted Monday, August 13, 2012 2:17 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1344409
Posted Monday, August 13, 2012 2:21 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:24 PM
Points: 23,397, Visits: 32,246
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1344410
Posted Monday, August 13, 2012 2:23 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:24 PM
Points: 23,397, Visits: 32,246
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1344412
Posted Monday, August 13, 2012 2:25 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:29 PM
Points: 541, Visits: 1,050
What's wrong with using the standard "IF EXISTS"....DROP INDEX....CREATE INDEX structure used so commonly?


Ninja'd by Lynn
Post #1344415
Posted Monday, August 13, 2012 2:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 12,918, Visits: 32,085
obligatory compatibility nazi post:

sysindexes should be replaced with sys.indexes, as it's going to be dropped in some future version.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1344416
Posted Monday, August 13, 2012 2:32 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:24 PM
Points: 23,397, Visits: 32,246
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1344418
Posted Monday, August 13, 2012 2:36 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1344421
Posted Monday, August 13, 2012 2:43 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:24 PM
Points: 23,397, Visits: 32,246
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'.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1344422
Posted Monday, August 13, 2012 2:44 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:52 AM
Points: 43,042, Visits: 36,200
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1344424
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse