Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Polling Db to Apply Index

Author  Topic 

antonarcher
Starting Member

4 Posts

Posted - 2013-10-16 : 10:04:34
Apologies for the beginner question.

To prevent a duplication bug on some of my automatically created DB tables, I have to apply a unique nonclustered index as below.

create unique nonclustered index
ct_pk_dup on 'ct_pk05__'

I would like to write a query for schedule that could poll the sys tables and apply the index to all tables containing 'ct_pk' and apply the index if it isnt there. Whats the best way to approach this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 10:07:16
use dynamic sql. Build CREATE INDEX string based on INFORMATION_SCHEMA.COLUMNS table and then use EXEC or sp_executesql to execute it to create the index. You can use a check on sys.index to see if it already exists

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-16 : 10:13:24
quote:
Originally posted by antonarcher

Apologies for the beginner question.

To prevent a duplication bug on some of my automatically created DB tables, I have to apply a unique nonclustered index as below.

create unique nonclustered index
ct_pk_dup on 'ct_pk05__'

I would like to write a query for schedule that could poll the sys tables and apply the index to all tables containing 'ct_pk' and apply the index if it isnt there. Whats the best way to approach this?

An alternative to polling would be to set up a DDL trigger that would examine the table created to test if it has the column of interest and then create the index on it.

In theory that should be more robust than polling, but I have not experimented with it, so do some testing if you decide to go that route.

When you do create the unique index, what do you/would you do with duplicates that may already exist in the table?

I know this is easy for me to say, but it seems like doing this is treating the symptom rather than the disease. So if you are able to fix the duplication bug, I would go that route even if it takes more efforts.
Go to Top of Page

antonarcher
Starting Member

4 Posts

Posted - 2013-10-16 : 10:22:00
Thanks both

The duplicates only occur if this index isnt in place, and as the tables are being created regularly I need to get the index on as soon as possible after creation, its a bug in the application code.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-16 : 10:36:19
quote:
Originally posted by antonarcher

Thanks both

The duplicates only occur if this index isnt in place, and as the tables are being created regularly I need to get the index on as soon as possible after creation, its a bug in the application code.

In that case I would definitely explore DDL trigger. When you implement the DDL trigger, the index is created immediately as part of the table creation. So the possibility that invalid data would be entered into the table in the interval between table creation and index creation does not exist.

I meant to post this link to the documentation for DDL triggers in my previous post, but forgot to do so: http://technet.microsoft.com/en-us/library/ms190989(v=sql.105).aspx
Go to Top of Page

antonarcher
Starting Member

4 Posts

Posted - 2013-10-17 : 04:25:14
Thanks for your help
Go to Top of Page
   

- Advertisement -