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 |
|
silas2
Yak Posting Veteran
65 Posts |
 Posted - 2005-08-30 : 14:22:45
|
| When you Upsize from Access using the wizard, unsurprisingly, a Unique index is created on the PK field, but these are all non-clustered. I presume there isn't one definitive answer to whether a index should be clustered or not, (which I understand means the table's records are held on disk contiguously), but generally, is it worth altering these all to become clustered? Would you selectively cluster only those tables which you think would benefit most? Leave them all unclustered and look for bottle-necks? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-30 : 14:25:56
|
| Every table should have a clustered index. Whether or not the PK should be the clustered one or not is dependent on your queries. If you only have one index on the table and it's the PK, then definitely make it clustered.Tara |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-08-30 : 16:54:39
|
| keep in mind that you are limited to a single clustered index per table.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-30 : 23:57:36
|
BoL says:A clustered index is particularly efficient on columns that are often searched for ranges of values.Clustered indexes are also efficient for finding a specific row when the indexed value is uniqueIt is important to define the clustered index key with as few columns as possibleConsider using a clustered index for:- Columns that contain a large number of distinct values.
- Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
- Columns that are accessed sequentially.
- Queries that return large result sets.
- Columns that are frequently accessed by queries involving join or GROUP BY clauses; typically these are foreign key columns. An index on the column(s) specified in the ORDER BY or GROUP BY clause eliminates the need for SQL Server to sort the data because the rows are already sorted. This improves query performance.
- OLTP-type applications where very fast single row lookup is required, typically by means of the primary key. Create a clustered index on the primary key.
Clustered indexes are not a good choice for:- Columns that undergo frequent changes
- Wide keys
Kristen |
|
|
|
silas2
Yak Posting Veteran
65 Posts |
Posted - 2005-08-31 : 06:00:51
|
| Can you "cluster" an index which was previously non-clustered or do you have to drop and recreate it? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-31 : 06:33:57
|
| Yup, I can't think of any other way. That will cause all the other indexes to be rebuilt too (they will use the Key of the Clustered Index instead of a pointer to the record) - that might take a reasonably chunk of time if your tables are bigKristen |
|
|
|
silas2
Yak Posting Veteran
65 Posts |
Posted - 2005-08-31 : 07:41:41
|
| sorry, to clarify, are you saying you CAN "cluster" an index which already exists? I can only find DROP/CREATE INDEX in the BOL (Not alter). |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-08-31 : 07:48:29
|
| CREATE INDEX has an optional clause WITH DROP_EXISTING that lets you do it in one command, but you still have to drop the existing index. |
|
|
|
|
|
|
|
|
Upsizing - Clustered indexes