richlion2 (5/26/2016)
Sorry for making this to drag on like this.Attached is a real example from one of my databases (not my design BTW). It shows a table with 2 first columns. The PK's are KEY_ID and FIXEL_ID, the latter being a VARCHAR.
I do not see a CLUSTERED index here, a non-clustered one is, this is the extract into SQL:
CREATE TABLE [dbo].[FIXEL](
[Key_id] [int] NOT NULL,
[fixel_id] [varchar](15) NOT NULL,
[name] [varchar](32) NULL,
/* ---- etc, etc, */
CONSTRAINT [PK_FIXEL] PRIMARY KEY NONCLUSTERED
(
[Key_id] ASC,
[fixel_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]
Whether a non-clustered index was explicitely requested I don't know. From the screenshot you may see a sample of the data. My question would be, why in this case a non-clustered index was chosen? Is it because a CLUSTERD (default) would not be created due to the types of the PK columns?
I can provide some more rows to show what's the table made up of.
Regards,
Ryszard
This does not have an identity so 'probably' the inserts are not sequential. But cant say anything unless you try and analyze and find out how the table is used.
Most data modelling tools would create a clustered index for PK and non clustered index for FKs. I have found no reason to change it 90 % of the time for OLTP database especially if you prefer small identity surrogate columns as PK, a review should be there in the checklist though. Definitely there are specific cases where you would want to alter the default behavior but mostly it will be specific designs only applicable for that particular scenario. So until and unless we know it I too tend to use default clustered index on PK.