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 |
|
adlo
Posting Yak Master
108 Posts |
 Posted - 2010-04-08 : 02:35:15
|
| HiI have an EXISTING table I need to make changesColumn1 intColumn2 intColumn3 intColumn4 intColumn5 stringColumn0 is primary key that is how it is designed.Since it is the primary key and i don't have a clustered index data gets sorted on it.I have an EXISTING \ none clustered index on my table.CREATE NONCLUSTERED INDEX IX_myTableIndex ON dbo.myTable ( Column2, Column3 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]I have to add an addition non clustered index on these 3 columnCREATE UNIQUE NONCLUSTERED INDEX IX_myTableIndex2 ON dbo.myTable ( Column2, Column3 Column4 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]question 1My question is since the second index is just an extra column as I understand and the second new index the first two columns will still be indexed so I do not need the first index? Is this correct or do I still need the first index?question 2.Column1 is a auto number primary key. So it sorts automatically on this index. I understand a clustered index would sort the table in the clustered index order and would better as a none clustered index since it is stored in the table itself. Is this correct and is this best practice to do if one has no choice of having an auto number Col1 primary key since this sorting won't be on the auto number field anymore. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-08 : 03:12:46
|
| Q1 : Correct, after create IX_myTableIndex2 you can drop IX_myTableIndex Q2: " Column1 is a auto number primary key. So it sorts automatically on this index"Nope, that's not the case. It may do "most" of the time, but there is NO guarantee of ANY order with a relational database. If you want a specific order you have to use an ORDER BY clause.However, as a general rule you should have a Clustered Index on all tables. Normally the Primary Key is the best candidate. If you do not have a Clustered Index on a table (i.e. what is called a "heap" table) then there are certain maintenance routines that cannot perform a good job of optimising the table / reclaiming unused space etc. |
 |
|
|
adlo
Posting Yak Master
108 Posts |
Posted - 2010-04-08 : 04:52:17
|
| I understand if you don't have a clustered index the primary key is automatically the clustered index.So if I understand correct I can make my unique unclustered index above to a unique clustered index as this would save space as clustered indexes are stored in the table itself.What is the point then of creating a nonclustered unique index? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-08 : 05:00:35
|
| "I understand if you don't have a clustered index the primary key is automatically the primary key."Sorry, I don't understand what you mean"So if I understand correct I can make my unique unclustered index above to a unique clustered index as this would save space as clustered indexes are stored in the database."You should have a clustered index on the table, ideally, and usually it is the Primary Key. If you are not sure then jsut make sure that the Primary Key is using the CLUSTERED index."What is the point then of creating a nonclustered unique index?"The Primary key must be unique (and is usually the Clustered Index).If you have ANOTHER Unique constraint on your table, then creating a Unique Index on it (NONclustered) will ensure that all values in that column are unique.In my experience the conditions where this occurs are quite rare. Maybe you have a Product Code (primary key) and also have the Product Code used by another system, which is different, but also unique.If you use surrogate Primary Key then you will usually have a Unique NONclustered index for the Natural Primary Key (also unique) |
|
|
|
adlo
Posting Yak Master
108 Posts |
Posted - 2010-04-08 : 08:13:53
|
| Misspoke.I understand if you don't have a clustered index the clustered index is automatically the primary key.as well as So if I understand correct I can make my unique unclustered index above to a unique clustered index as this would save space as clustered indexes are stored in the table itselfThank you for you answer. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-08 : 09:32:43
|
| If you create a Primary Key it will be created as the Clustered Index by default. But you can force a Primary Key to NOT use a Clustered Index. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-09 : 15:18:31
|
quote:
Originally posted by adlo
I understand if you don't have a clustered index the clustered index is automatically the primary key.
No, other way around. If there is no clustered index and you add a primary key, by default that primary key will be enforced by a clustered index.quote:
So if I understand correct I can make my unique unclustered index above to a unique clustered index as this would save space as clustered indexes are stored in the table itself
A cluster is not stored in the table. It is the table. The leaf level of a clustered index contains the actual data pages of the table.Do note that a 3-column index may not be the best of choices for a clustered index.This may be worth reading (as well as the other 2 parts of the series)http://www.sqlservercentral.com/articles/Indexing/68563/--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Noob question regarding indexes