May 26, 2016 at 4:30 am
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.
May 30, 2016 at 10:35 am
Terje Hermanseter (5/26/2016)
However, I like Kimberly Tripp's advice. Choose the clustering key separately from the PK, keep it unique, fixed, and narrow..
I'm not sure I fully understand the "choose the clustering key separately from the PK" part. I thought it was perfectly ok to use the PK as the clustered key as long as it fulfills the other criterias: keep it unique, narrow, static, ever-increasing and fixed width?
It is, but you're choosing them separately. You're picking a PK based on some unique criteria. You're looking at a clustered index as being narrow, static, increasing, fixed. If they match up, great, but you choose them separately.
May 30, 2016 at 12:40 pm
Steve Jones - SSC Editor (5/30/2016)
Terje Hermanseter (5/26/2016)
However, I like Kimberly Tripp's advice. Choose the clustering key separately from the PK, keep it unique, fixed, and narrow..
I'm not sure I fully understand the "choose the clustering key separately from the PK" part. I thought it was perfectly ok to use the PK as the clustered key as long as it fulfills the other criterias: keep it unique, narrow, static, ever-increasing and fixed width?
It is, but you're choosing them separately. You're picking a PK based on some unique criteria. You're looking at a clustered index as being narrow, static, increasing, fixed. If they match up, great, but you choose them separately.
Thanks! That was clarifying.
January 16, 2017 at 5:22 pm
I am still amazed at how many people here come across long term SQL Server practitioners who don't know what a clustered index is. I cannot remember how long ago, if at all, I was last in an interview that didn't ask what one was. Whichever side of the table I sat. Whether for a DBA or a developer.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 17, 2017 at 12:16 am
Gary Varga - Monday, January 16, 2017 5:22 PMI am still amazed at how many people here come across long term SQL Server practitioners who don't know what a clustered index is. I cannot remember how long ago, if at all, I was last in an interview that didn't ask what one was. Whichever side of the table I sat. Whether for a DBA or a developer.
"Clustered Index" collective noun for a group of indices?
;-0
January 17, 2017 at 12:27 am
Yet Another DBA - Tuesday, January 17, 2017 12:16 AMGary Varga - Monday, January 16, 2017 5:22 PMI am still amazed at how many people here come across long term SQL Server practitioners who don't know what a clustered index is. I cannot remember how long ago, if at all, I was last in an interview that didn't ask what one was. Whichever side of the table I sat. Whether for a DBA or a developer."Clustered Index" collective noun for a group of indices?
;-0
It was late. I couldn't sleep. You're mean. (Only joking 😛 Off to edit post.)
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 17, 2017 at 3:04 am
Yet Another DBA - Tuesday, January 17, 2017 12:16 AMGary Varga - Monday, January 16, 2017 5:22 PMI am still amazed at how many people here come across long term SQL Server practitioners who don't know what a clustered index is. I cannot remember how long ago, if at all, I was last in an interview that didn't ask what one was. Whichever side of the table I sat. Whether for a DBA or a developer."Clustered Index" collective noun for a group of indices?
;-0
That's just silly. Everybody knows the correct collective noun is a fragment of indices
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply