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.
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
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. |
|
|
|
antonarcher
Starting Member
4 Posts |
Posted - 2013-10-16 : 10:22:00
|
| Thanks bothThe 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. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-16 : 10:36:19
|
quote:
Originally posted by antonarcher
Thanks bothThe 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 |
|
|
|
antonarcher
Starting Member
4 Posts |
Posted - 2013-10-17 : 04:25:14
|
| Thanks for your help |
|
|
|
|
|
|
|
|
Polling Db to Apply Index