February 12, 2004 at 6:39 am
Hi,
I am totaly new to SQL*SERVER. I found that all our primary keys indexes have been created with the "clustered" option (which is the default). I really think we shouldn't have done this. Could someone explain the pros and cons of this approach or point to any resources that'd provide guildelines on when to use clustered indexes? Thank you!
February 16, 2004 at 8:00 am
This was removed by the editor as SPAM
February 18, 2004 at 7:45 am
On the whole every table should have a clustered index. You will want to use somewhat basic index design in deciding which fields to use for the clustered index. Here is a good article:
http://www.sql-server-performance.com/clustered_indexes.asp
If you do a Google search for Clustere Index you will get lots of good articles. Do a search on this site for Clustered Index and you will get some very good references.
Ross
February 20, 2004 at 6:22 am
Hope it's ok that I add my question to your thread.
I created a clustered index on column Zipcode, intending that the columns would always be sorted. Then I created a primary key on the other column.
Why, when I do a select, is the Zipcode column not sorted? Other tables set up the same, do sort on the clustered index.
Thanks - Marc
Code:
CREATE TABLE [dbo].[globZipcodes2] (
[ZipcodeID] [int] IDENTITY (1, 1) NOT NULL ,
[Zipcode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [NDX_Zipcode_CLUSTERED_UNIQUE] ON [dbo].[globZipcodes2]([Zipcode]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[globZipcodes2] ADD
CONSTRAINT [PK_globZipcodes2] PRIMARY KEY NONCLUSTERED
([ZipcodeID]) ON [PRIMARY]
GO
February 20, 2004 at 12:06 pm
Your ZIPCODE Field is a Varchar and therefore will probably be sorted in Alpha order not numerical order. For example (1,2,3,10,12,20) would sort (1,10,12,2,20,3).
February 20, 2004 at 1:18 pm
Thanks for responding.
Great point! However, wouldn't (01,02,03,10,12,20) sort as (01,02,03,10,12,20)? Would this concept mess it up if I had data such as (33256-0456, 33256)!!!?
Here's what I did:
1. I dropped the indexes, removed the PK and indenty, then rebuilt the table as follows:
Set Zipcode to PK, Set ZipCodeID to Identity and it works fine.
2. I wonder, with such a simple table design (and static data), if I even need the ID field. I was not planning on using the ID in a join or FK anyway. Just using zipcode in a combo box.
3. I also wonder if I there was an extra index in the original situation as when I compared the old code to the new, I saw the PK was named PK_globZipcodes2 previously!!!
Marc
February 20, 2004 at 1:26 pm
I have found that if you Create the table with primary key.
CREATE TABLE tbl_Example (
[Field1] [int] Not Null Primary Key,
[Field1] [Char] Null)
on [Primary]
Go
there will be no object in sysobjects called PK_tbl_Example....
If you use and alter statement and add the constraint it will create an object. Either way a clustered index will automatically be created to support the primary key.
Cheers.
Glen Anderson
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply