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 |
|
kowalsky
Starting Member
29 Posts |
 Posted - 2011-01-27 : 12:27:10
|
| hi all,i am obviously missing some details, nevertheless I can't understand how SQL Server is doing this indexing ... so I have an identity column which is meant to be the primary key of my table. SQL Server is creating a CLUSTERED index on this column even though I understand a clustered index is used for the columns with non unique values and usually have a small number of possible values. If I try to create another clustered index on a different column that HAS 2 values (MALE/FEMALE) I am told that I can't put more than 1 clustered index one one given table!How am I supposed to proceed? I am using Windows Server 2003 and SQL Server 2008 R2,thanks,kowlaksy |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-27 : 12:34:56
|
| from bolPRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint.CLUSTERED index means the table itself will be physically sorted based on it so obvioulsy you cant have more than 1 clustered index on table as you cant sort table physically more than 1 way simultaneously------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
kowalsky
Starting Member
29 Posts |
Posted - 2011-01-27 : 12:49:01
|
| vishakh16,thanks!the second part of your first statement may not be right: especially for this purpose I dropped the table, re-created it and specified non-clustered index for my pk constraint (actually you can't specify non-clustered, however you omit the CLUSTERED keyword). To my surprise, the newly created table has a CLUSTERED Index on the PK. And that's by default ...Again:CREATE TABLE [dbo].[READINGS]( [READING_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [SOURCE_TYPE] [nvarchar](2) NOT NULL, [READING_VERSION] [nvarchar](2) NOT NULL, [READING_VALUE] [numeric](18, 6) NOT NULL, CONSTRAINT [PK_READINGS] PRIMARY KEY ( [READING_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]After you execute this you will get one table with ONE CLUSTERED INDEX on READING_ID ... if you edit your script you will see that the keyword CLUSTERED magically appears after the PRIMARY KEY.Which seems to imply that will be never able to create a clustered index on a field different from your PK ... unless you do not create a PK, you set your clustered index and then alter the table and create your PK ... seems unnaturla to me! Is this really the way I am supposed to do it?thanks,kowalskykowalsky |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-27 : 12:59:19
|
you can create clustered index on another column provided you make PK explicitly as NON CLUSTERED. try this and then make another column clustered.CREATE TABLE [dbo].[READINGS]([READING_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,[SOURCE_TYPE] [nvarchar](2) NOT NULL,[READING_VERSION] [nvarchar](2) NOT NULL,[READING_VALUE] [numeric](18, 6) NOT NULL,CONSTRAINT [PK_READINGS] PRIMARY KEY NONCLUSTERED([READING_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
kowalsky
Starting Member
29 Posts |
Posted - 2011-01-27 : 13:16:56
|
| OK,so YOU CAN SPECIFY NONCLUSTERED ... my bad, I was under the impression that the default is NONCLUSTERED and if you omit the CLUSTERED keyword you will get your NONCLUSTERED index, apparently it is the other way around! Thanks again,kowalskykowalsky |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-27 : 13:19:53
|
no probs you're welcome  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
|
|
|
|
|
clustered vs non clustered index