June 25, 2014 at 7:48 pm
Can nonclustered index be created without first creating clustered index? I guess so. but I read a book it mentioned that nonclustered index will use clustered index to find the data.
does cluster index has to be created on a unique field? I know nonclustered index can be unique or not unique.
thanks
June 25, 2014 at 10:05 pm
Dennisql (6/25/2014)
Can nonclustered index be created without first creating clustered index? I guess so. but I read a book it mentioned that nonclustered index will use clustered index to find the data.does cluster index has to be created on a unique field? I know nonclustered index can be unique or not unique.
thanks
SQL Server lets you create non-clustered indexes without a clustered index.
If a clustered index happens to exist prior to the creation of a non-clustered index, then, as you read, the non-clustered index will use the clustered index. What actually happens is that the leaf node of the non-clustered index contains the values of the columns in the clustered index (not a pointer to the actual rows). When you access data via the non-clustered index, SQL will then lookup the data in the table via the clustered index (if it needs to, of course).
Clustered indexes can be unique or non-unique - that is up to you
June 26, 2014 at 2:30 am
Take a read through these:
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
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
June 26, 2014 at 3:17 am
Generally, because of how SQL Server stores and retrieves data, it's a good idea on most tables to have a clustered index. Because of how clustered indexes are stored, it's a good idea to make them unique. You can create a clustered index on a heap table (that's a table without a clustered index) after you've created nonclustered indexes on that table. That will change the way the data is stored and it will change the nonclustered indexes so that they point to it instead of the RID of the heap. But no, you don't have to have a clustered index and it doesn't have to be unique. That's just a preferred construct.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 26, 2014 at 11:40 am
Yes, you can create a non-clustered index w/o a clustered index, but you (almost) never should.
The cluster key does not have to be defined as unique (SQL will internally make it unique if it isn't). Don't worry about that. Vastly more important is to choose the best column(s) to build the clustered index on. After that, you can spend time on the less important q of whether or not to force it to be unique.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply