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 |
|
zion99
Posting Yak Master
141 Posts |
 Posted - 2007-07-23 : 15:30:45
|
| Hi All,1. Is it true that a primary key creates clustered index , but unique key creates non clustered index. (or whether primary key is associated with clustered index & unique key with non-clustered index)2. If this is true then since unique key allows more than one nulls, non-clustered indexes wont be much useful.Please clarify my doubt.Thanks. :) |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-23 : 16:02:33
|
| 1. yes2. non unique index will have a uniquifier added to it by sql server._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-23 : 16:32:11
|
| You can create non-clustered index on pkey if you like. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-23 : 16:34:42
|
| zion99, non-clustered indexes are indeed useful, why would you think they weren't?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
zion99
Posting Yak Master
141 Posts |
Posted - 2007-07-24 : 02:38:59
|
| tkizer, it is really useful to have a clustered index when we retrieve many rows of data, ranges of data, and when BETWEEN is used in the WHERE clause.Apart from single row retrieval where non-clustered index is a better option, i m not sure where can we use non-clustered index... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-24 : 11:58:27
|
| You can only have one clustered index on each table. Tables typically need more than one index, so you typically have a few non-clustered indexes on them as well. I'm not sure where you are getting your information.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
zion99
Posting Yak Master
141 Posts |
Posted - 2007-07-24 : 13:05:31
|
| i got info from the following link... but i had some doubts... so wanted to clarify the same...http://www.sql-server-performance.com/gv_index_data_structures.asp |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-25 : 17:13:22
|
quote:
Originally posted by zion99
1. Is it true that a primary key creates clustered index , but unique key creates non clustered index. (or whether primary key is associated with clustered index & unique key with non-clustered index)
Just a small clarification - this is the default but can be overridden. You can have nonclustered primary keys and clustered indexes (unique or not). Often the defualt behaviour is what you want anyway. |
|
|
|
|
|
|
Clustered & Non clustered index