Assumptions on Indexing

I create the following:
CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
CREATE CLUSTERED INDEX icl ON t1(c3)
CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
the following columns:
c1, c2, c3
ASSUMPTION #1: I assume the reason for this is that clustered index columns
(in this case, c3) are appended to the end of nonclustered index columns as
a
row locator.
Why then, if the above assumption [ASSUMPTION #1] is true, when I perfor
m the
following:
CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
sequential column order
WITH DROP_EXISTING
When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
the following columns:
c1, c3, c2
ASSUMPTION #2: Based upon Assumption #1, I would expect to see the clustered
index column of c3 appended to the end of the nonclustered index column,
meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
assume the reason the clustered index was not appended was due to its [t
he
clustered index] being included in the nonclustered index, and appending
column c3 [the clustered index column] to the nonclustered index in
DBCC_SHOW_STATISTICS would be redundant.
Are the above assumptions true?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1> Are the above assumptions true?
Yes and yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:5e86871f6f516@.uwe...[vbcol
=seagreen]
>I create the following:
> CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
> CREATE CLUSTERED INDEX icl ON t1(c3)
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c2, c3
> ASSUMPTION #1: I assume the reason for this is that clustered index column
s
> (in this case, c3) are appended to the end of nonclustered index columns a
s a
> row locator.
> Why then, if the above assumption [ASSUMPTION #1] is true, when I perf
orm the
> following:
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
> sequential column order
> WITH DROP_EXISTING
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c3, c2
> ASSUMPTION #2: Based upon Assumption #1, I would expect to see the cluster
ed
> index column of c3 appended to the end of the nonclustered index column,
> meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
> assume the reason the clustered index was not appended was due to its [
;the
> clustered index] being included in the nonclustered index, and appending
> column c3 [the clustered index column] to the nonclustered index in
> DBCC_SHOW_STATISTICS would be redundant.
> Are the above assumptions true?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1[/vbcol]|||Yep. The clustered key(s) is always included in the nonclustered, but it
doesn't always have to be the right-most column.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5e86871f6f516@.uwe...
>I create the following:
> CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
> CREATE CLUSTERED INDEX icl ON t1(c3)
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c2, c3
> ASSUMPTION #1: I assume the reason for this is that clustered index
> columns
> (in this case, c3) are appended to the end of nonclustered index columns
> as a
> row locator.
> Why then, if the above assumption [ASSUMPTION #1] is true, when I perf
orm
> the
> following:
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
> sequential column order
> WITH DROP_EXISTING
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c3, c2
> ASSUMPTION #2: Based upon Assumption #1, I would expect to see the
> clustered
> index column of c3 appended to the end of the nonclustered index column,
> meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
> assume the reason the clustered index was not appended was due to its [
;the
> clustered index] being included in the nonclustered index, and appending
> column c3 [the clustered index column] to the nonclustered index in
> DBCC_SHOW_STATISTICS would be redundant.
> Are the above assumptions true?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1|||sort of.
clustered indexes are weird. they physically sort the data, and then
use the columns as a key to find the relevant rows. all of this is
inherently inefficient.
non clustered indexes use data positions in the table to locate rows.
well, if there is a clustered index on the table, then those data
positions are determined by the clustered column.
so, if we create a non clustered index on the table, the engine MUST
HAVE the data elements from the clustered column in order to find the
correct row.
to see how this all works, and make it real in your head, right down
some 5 rows of examples, then create how the index would really work.