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 |
|
brian147
Starting Member
10 Posts |
 Posted - 2010-03-02 : 04:51:27
|
| I have a large table, with a number of non-clustered indexes, into which I needed to "bulk-insert" further records.Prior to the import, I dropped the indexes, then imported the new records and finally re-created the indexes.Everything is fine and dandy.The bit I don't understand is this: How is the "index_id" in sys.indexes still the same?I use this index_id in other views eg "with (index=n)".Did I get lucky or does SQL Server somehow remember the name of the index? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-02 : 05:00:43
|
I would always SSMS let create a create-script for that index before deleting it.So I can be sure it will be the same name again.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
brian147
Starting Member
10 Posts |
Posted - 2010-03-02 : 05:11:26
|
| webfred,I did re-create the indexes with the same name. I still don't understand how it is that after I explicitly "DROPped" an index, then "CREATEd" a new (identical) index later, it has the same index_id? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-02 : 05:19:48
|
I think if you're going to create an index without giving a wanted name then sql server has a way to build a name for this new index.If sql server is always going the same way to build the name then it is possible that it comes up with the same name again because the name in this case isn't already in use because you have dropped it.But I think it is not very reliable, hence my post to create a script.In this script there is a given name for the index so it will be always the same name each time you create it after dropping.did you get me?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-02 : 05:43:58
|
| It gets the same ID, because that's the next highest unused index id. If someone were to create another index between the time you drop and the time you recreate, you wouldn't get the same index id.Why are you using index hints? Are you sure that you know better than the query optimiser the best index in all possible circumstances?--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
index drop and create