February 4, 2015 at 5:06 am
Hi There ,
All non-clustered-index (NCI) will also store the key column of clustered-index(CI)
While creating the NCI, if we intentionally include the key column what would happen, is that occupy space one more time?
Means to store key column, will space occupied twice ?
Thanks in advance
February 4, 2015 at 5:23 am
You can't include a column that's already in the key.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2015 at 4:08 pm
vignesh.ms (2/4/2015)
Hi There ,All non-clustered-index (NCI) will also store the key column of clustered-index(CI)
While creating the NCI, if we intentionally include the key column what would happen, is that occupy space one more time?
Means to store key column, will space occupied twice ?
Thanks in advance
I must be reading differently than Gilamonster's answer so sorry if I'm misinterpretting this and as usual I expect I could be completely wrong!
CREATE TABLE [dbo].[pats_tab](
[testkey] [char](100) NOT NULL,
[data] [char](100) NULL,
[data2] [char](100) NULL,
CONSTRAINT [PK_pats_tab] PRIMARY KEY CLUSTERED
(
[testkey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [index1] ON [dbo].[pats_tab]
(
[data] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [index2] ON [dbo].[pats_tab]
(
[data] ASC,
[testkey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Yes, I scripted out a bunch of moused table and index creations :hehe:
When I jammed a few thousand records in there, then moused the properties of index2, according to the figures from ssms regarding "average row size" for index2, it did not add in space for the key column again.
While creating the NCI, if we intentionally include the key column what would happen, is that occupy space one more time?
My guess is "no".
February 4, 2015 at 10:39 pm
Edit: No, it's me who mis-read the question.
A nonclustered index can only ever contain a column once. Hence if you explicitly reference a column that's implicitly in the index already, it's not added a second time, as the column cannot be in the index twice.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply