clustered indexes in OLTP

Hi everyone,
I came across an opinion that clustered indexes shouldn't be used in busy
OLTP systems. What are pros and cons in that?
Thanks a lot in advance
AlexIt is very useful that a table has a clustered index. What column(s) are you
using for the key, that is another thing. For example, if your table store
orders and the clustered index key is column [orderdate], I do not see a
problem there, but if you are using a uniqueidentifier data type column then
every new insert will cause that the index and table to be sort because of
the ordering of this kind of data.
AMB
"Alex" wrote:

> Hi everyone,
> I came across an opinion that clustered indexes shouldn't be used in busy
> OLTP systems. What are pros and cons in that?
> Thanks a lot in advance
> Alex
>
>|||the opinion is a dangerous one.
Generally speaking, all tables should have a clustered index. this is
crucial to the health of SQL Server.
If you need, I can explain in more detail...
Greg Jackson
PDX, Oregon|||Yes Greg, please, just in a few words
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%239u%23J7RQFHA.3448@.TK2MSFTNGP10.phx.gbl...
> the opinion is a dangerous one.
> Generally speaking, all tables should have a clustered index. this is
> crucial to the health of SQL Server.
> If you need, I can explain in more detail...
>
> Greg Jackson
> PDX, Oregon
>|||Thank you Greg
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eIVchJSQFHA.604@.TK2MSFTNGP10.phx.gbl...
>a table without a clustered index is known as a Heap.
> In a nutshell, heaps can cause Fragmentation which results in Page Splits
> (Expensive on IO)
> IF the table in quesiton, has nonclustered indexes, they cannot be
> defragged without a cluster.
> in order to do index maintenance a Clustered index is absolutely
> necessary.
>
> Here is an article I wrote that discusses many of these specifics.
>
> Cheers
> Greg J
>|||Greg, but why can nc index not be defragged without a cluster?
Alex
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eIVchJSQFHA.604@.TK2MSFTNGP10.phx.gbl...
>a table without a clustered index is known as a Heap.
> In a nutshell, heaps can cause Fragmentation which results in Page Splits
> (Expensive on IO)
> IF the table in quesiton, has nonclustered indexes, they cannot be
> defragged without a cluster.
> in order to do index maintenance a Clustered index is absolutely
> necessary.
>
> Here is an article I wrote that discusses many of these specifics.
>
> Cheers
> Greg J
>|||a heap has no order to it.
with no "Order" how can you move data to contiguous disk space...?
that is the answer. Its impossible.
GAJ|||Well, my understanding is a heap itself (its data pages) can't be defraged
but nonclustered indexes on it (index pages) can be?
Alex
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAqhz3SQFHA.3144@.tk2msftngp13.phx.gbl...
>a heap has no order to it.
> with no "Order" how can you move data to contiguous disk space...?
>
> that is the answer. Its impossible.
>
> GAJ
>|||no that is incorrect.
easy to prove.
run dbcc indexdefrag or dbcc dbreindex on a heap
before you run check frag levels with ShowContig
check again AFTER ShowContig
you'll see that frag levels (Scan density) have not improved.
GAJ|||http://www.sql-server-performance.c...red_indexes.asp
http://www.quest-pipelines.com/newsletter-v4/1103_B.htm
AMB
"Alex" wrote:

> Yes Greg, please, just in a few words
> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:%239u%23J7RQFHA.3448@.TK2MSFTNGP10.phx.gbl...
>
>

Clustered Index on Non-Unique Column

I've done a little research but I'd like to get some opinion from the group.
I'll also preface this by saying that we can't change how it was done in the
first place, only investigate how to fix it if possible.
Let's say you've got a table that has 4,333,776 rows. Currently that table
has a PK that is functioning as a clustered index(it's the identity). You've
then got two more indexes that were placed on this table that are not
clustered.
The problem is that the clustered index is never used, and it appears that
if we moved the clustered to one of the other keys we may gain some
performance. The problem is that the column that is preferrable isn't unique
so SQL Server will add the "uniqueifer" to it. I've checked on the
selectivity ratio of the column and it's currently at .81. Not sure if
that's .81% or 81%. If it's the latter I wonder if it's close enough to the
sometimes suggested 95% unique.
Any thoughts? Any more details I can give to make this more helpful?
Is the clustered index unique? If not, why not? Also, what is the PK?
Have you got the complete build script for the table, including indexes and
constraints?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:036D72E7-F282-4316-AA6B-53F555B9DF19@.microsoft.com...
I've done a little research but I'd like to get some opinion from the group.
I'll also preface this by saying that we can't change how it was done in the
first place, only investigate how to fix it if possible.
Let's say you've got a table that has 4,333,776 rows. Currently that table
has a PK that is functioning as a clustered index(it's the identity).
You've
then got two more indexes that were placed on this table that are not
clustered.
The problem is that the clustered index is never used, and it appears that
if we moved the clustered to one of the other keys we may gain some
performance. The problem is that the column that is preferrable isn't unique
so SQL Server will add the "uniqueifer" to it. I've checked on the
selectivity ratio of the column and it's currently at .81. Not sure if
that's .81% or 81%. If it's the latter I wonder if it's close enough to the
sometimes suggested 95% unique.
Any thoughts? Any more details I can give to make this more helpful?
|||It does sound like the clustered index is being wasted on field that doesn't
usualy get searched.
Clustered indexes are great for range queries. Say you wanted to get a list
of records from 07/010/2007 to 07/31/2007, having a clueterd index on the
date field would be great.
Nonclustered indexes are better for queries that return one or a few records.
MG
"jason7655" wrote:

> I've done a little research but I'd like to get some opinion from the group.
> I'll also preface this by saying that we can't change how it was done in the
> first place, only investigate how to fix it if possible.
> Let's say you've got a table that has 4,333,776 rows. Currently that table
> has a PK that is functioning as a clustered index(it's the identity). You've
> then got two more indexes that were placed on this table that are not
> clustered.
> The problem is that the clustered index is never used, and it appears that
> if we moved the clustered to one of the other keys we may gain some
> performance. The problem is that the column that is preferrable isn't unique
> so SQL Server will add the "uniqueifer" to it. I've checked on the
> selectivity ratio of the column and it's currently at .81. Not sure if
> that's .81% or 81%. If it's the latter I wonder if it's close enough to the
> sometimes suggested 95% unique.
> Any thoughts? Any more details I can give to make this more helpful?
|||jason7655 wrote:
> I've done a little research but I'd like to get some opinion from the group.
> I'll also preface this by saying that we can't change how it was done in the
> first place, only investigate how to fix it if possible.
> Let's say you've got a table that has 4,333,776 rows. Currently that table
> has a PK that is functioning as a clustered index(it's the identity). You've
> then got two more indexes that were placed on this table that are not
> clustered.
> The problem is that the clustered index is never used, and it appears that
> if we moved the clustered to one of the other keys we may gain some
> performance. The problem is that the column that is preferrable isn't unique
> so SQL Server will add the "uniqueifer" to it. I've checked on the
> selectivity ratio of the column and it's currently at .81. Not sure if
> that's .81% or 81%. If it's the latter I wonder if it's close enough to the
> sometimes suggested 95% unique.
> Any thoughts? Any more details I can give to make this more helpful?
Why is it a problem that the clustered index is never used?
Anyway, if the clustered index is not unique, then a uniqueifier is only
added to "duplicate" rows. For those rows, the key requires more
storage. Depending on your table structure it will require anywhere
between 6 and 8 bytes more per row. This extra space might not be a
problem for you, but that is something for you to decide.
Another thing to remember is that the clustered index keys are also
stored in all nonclustered indexes. So a narrow clustered index key
(such as int) is preferred over a wide index key. If you choose a very
wide clustered index key, then you other nonclustered index will grow a
lot (and slow down just as much).
HTH,
Gert-Jan
|||PK = Primary Key
Let me try to explain a little better.
This table has 3 indexes.
Index 1
Primary key, clustered. 1,2,3,4,5,etc.
Index 2
non-clustered. Use 2nd most.
Index 3
non-clustered, but the main column used by most queries. Used the most.
From my limited reading of sites (including
http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx,
http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx, and
various other documents) it would seem that we could gain performance by
changing the Index 3 to clustered.
It's not unique and that raises another question. I would hope that the SQL
Server "uniquefier" that gets added would not change the makeup of the
current data, only create a new column.
Does that make things more clear? Is this a good idea?
"Gert-Jan Strik" wrote:

> jason7655 wrote:
> Why is it a problem that the clustered index is never used?
> Anyway, if the clustered index is not unique, then a uniqueifier is only
> added to "duplicate" rows. For those rows, the key requires more
> storage. Depending on your table structure it will require anywhere
> between 6 and 8 bytes more per row. This extra space might not be a
> problem for you, but that is something for you to decide.
> Another thing to remember is that the clustered index keys are also
> stored in all nonclustered indexes. So a narrow clustered index key
> (such as int) is preferred over a wide index key. If you choose a very
> wide clustered index key, then you other nonclustered index will grow a
> lot (and slow down just as much).
> HTH,
> Gert-Jan
>
|||I've also found the following snippet that has gotten me a little confused
and seems to go counter to other things I've read:
"Think of a clustered index as the foundation of the table. Keep it simple
and clean. The point is that if you add non-clustered indexes to the table,
they repeat the clustered index and add the indexed column(s). So ideally
your clustered index should be on a single column.
Ideally, a clustered index should be on a column where new rows have a
steadily increasing value. Otherwise the clustered index will get fragmented,
and so will the non-clustered indexes based on it.
In short, for a table that sees lots of inserts, use only the identity
column for the clustered index."
(http://sql-server-performance.com/Community/forums/p/9800/53961.aspx#53961)
I could use some clarification on this one.
"jason7655" wrote:
[vbcol=seagreen]
> PK = Primary Key
> Let me try to explain a little better.
> This table has 3 indexes.
> Index 1
> Primary key, clustered. 1,2,3,4,5,etc.
> Index 2
> non-clustered. Use 2nd most.
> Index 3
> non-clustered, but the main column used by most queries. Used the most.
> From my limited reading of sites (including
> http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx,
> http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx, and
> various other documents) it would seem that we could gain performance by
> changing the Index 3 to clustered.
> It's not unique and that raises another question. I would hope that the SQL
> Server "uniquefier" that gets added would not change the makeup of the
> current data, only create a new column.
> Does that make things more clear? Is this a good idea?
> "Gert-Jan Strik" wrote:
|||Jason,
Finding rows by seeking the clustered index is less expensive than
seeking a nonclustered index followed by a bookmark lookup. However, for
exact match selects, the difference usually isn't that big, for example
WHERE my_id = 15.
Where the clustered index really comes in handy is when you select a
range that potentially returns a lot of rows (or causes a lot of rows to
be processed). For example WHERE order_date >= '20070701' AND order_date
< '20070801'. In that case, the column order_date would really benefit
from a clustered index.
Another situation is a table that is joined very often to the table that
it is referrencing, and the Primary Key contains the Foreign Key of the
referenced table. For example a "Order Details" table with a Primary Key
on (order_id, line_no) that references an Orders table on column
(order_id). In such a case, creating the clustered index on the Primary
Key/Foreign Key can be a good choice. Basically it is the same rule
here. When joining, there are potentially many rows that need to be
processed.
So changing your index 3 to be the clustered index could be a good
choice, but there is not enough information for me to decide. If you
join this table a lot on the columns of index 1, then join performance
could drop. You should analyse your workload, and decide which queries
are most important (with respect to performance). The, if possible, you
should test the different scenario's and see what works best for you.
BTW: if the clustered index is defined as not unique, the uniqueifier is
added under the covers. Its existance of columns values will never be
exposed when you select from the table.
As for your other post, see my opinion inline:

> I've also found the following snippet that has gotten me a little confused
> and seems to go counter to other things I've read:
> "Think of a clustered index as the foundation of the table. Keep it simple
> and clean. The point is that if you add non-clustered indexes to the table,
> they repeat the clustered index and add the indexed column(s).
Correct.

> So ideally your clustered index should be on a single column.
The point is not how many columns it concerns. The point is how many
bytes the key uses. From that point of view, two int columns are better
than one varchar(50) column with an average fill of 25 characters. Even
6 int columns are better than a varchar(50) column with average string
size of 25 characters.

> Ideally, a clustered index should be on a column where new rows have a
> steadily increasing value. Otherwise the clustered index will get fragmented,
True. So from that point of view, a clustered index on a GUID is not a
good idea, unless you defragment regularly.

> and so will the non-clustered indexes based on it.
I think this is false. Nonclustered indexes will fragment if new values
of the (nonclustered) indexed key are not "steadily increasing".
However, since nonclustered indexes are much smaller than the clustered
index (which includes the table data), the cost of fragmentation of a
nonclustered index is typically a lot smaller. Of course, regular
defragmenting is the solution for this too.
There is one exception. If your nonclustered index has a very low
selectivy (just a few distinct values), then the nonclustered index'
fragmentation will be very similar to the clustered index'
fragmentation.

> In short, for a table that sees lots of inserts, use only the identity
> column for the clustered index."
> (http://sql-server-performance.com/Community/forums/p/9800/53961.aspx#53961)
I definitely disagree with this statement as a general rule, especially
because of the compulsory tone.
For starters, if you don't use Identity as a (surrogate) key, then IMO
you should not add one.
Next, in many situations, the primary key's index is used a lot in
joins, but in other situations (such as filtering on "Lookup Tables")
the optimizer's access path is different and the primary key's index is
used very little.
And finally: it is very unusual to have range selects on an Identity
column. Such range selects are much more common for datetime columns.
So putting a clustered index on an Identity Primary Key could be a good
choice, but it really depends on your situation, and IMO you should not
have policy to always add an Identity and/or to always create the
clustered index on the Identity.
HTH,
Gert-Jan
jason7655 wrote:
> PK = Primary Key
> Let me try to explain a little better.
> This table has 3 indexes.
> Index 1
> Primary key, clustered. 1,2,3,4,5,etc.
> Index 2
> non-clustered. Use 2nd most.
> Index 3
> non-clustered, but the main column used by most queries. Used the most.
> From my limited reading of sites (including
> http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx,
> http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx, and
> various other documents) it would seem that we could gain performance by
> changing the Index 3 to clustered.
> It's not unique and that raises another question. I would hope that the SQL
> Server "uniquefier" that gets added would not change the makeup of the
> current data, only create a new column.
> Does that make things more clear? Is this a good idea?
>
[snip]

Clustered Index on Non-Unique Column

I've done a little research but I'd like to get some opinion from the group.
I'll also preface this by saying that we can't change how it was done in the
first place, only investigate how to fix it if possible.
Let's say you've got a table that has 4,333,776 rows. Currently that table
has a PK that is functioning as a clustered index(it's the identity). You'v
e
then got two more indexes that were placed on this table that are not
clustered.
The problem is that the clustered index is never used, and it appears that
if we moved the clustered to one of the other keys we may gain some
performance. The problem is that the column that is preferrable isn't unique
so SQL Server will add the "uniqueifer" to it. I've checked on the
selectivity ratio of the column and it's currently at .81. Not sure if
that's .81% or 81%. If it's the latter I wonder if it's close enough to the
sometimes suggested 95% unique.
Any thoughts? Any more details I can give to make this more helpful?Is the clustered index unique? If not, why not? Also, what is the PK?
Have you got the complete build script for the table, including indexes and
constraints?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:036D72E7-F282-4316-AA6B-53F555B9DF19@.microsoft.com...
I've done a little research but I'd like to get some opinion from the group.
I'll also preface this by saying that we can't change how it was done in the
first place, only investigate how to fix it if possible.
Let's say you've got a table that has 4,333,776 rows. Currently that table
has a PK that is functioning as a clustered index(it's the identity).
You've
then got two more indexes that were placed on this table that are not
clustered.
The problem is that the clustered index is never used, and it appears that
if we moved the clustered to one of the other keys we may gain some
performance. The problem is that the column that is preferrable isn't unique
so SQL Server will add the "uniqueifer" to it. I've checked on the
selectivity ratio of the column and it's currently at .81. Not sure if
that's .81% or 81%. If it's the latter I wonder if it's close enough to the
sometimes suggested 95% unique.
Any thoughts? Any more details I can give to make this more helpful?|||It does sound like the clustered index is being wasted on field that doesn't
usualy get searched.
Clustered indexes are great for range queries. Say you wanted to get a list
of records from 07/010/2007 to 07/31/2007, having a clueterd index on the
date field would be great.
Nonclustered indexes are better for queries that return one or a few records
.
--
MG
"jason7655" wrote:

> I've done a little research but I'd like to get some opinion from the grou
p.
> I'll also preface this by saying that we can't change how it was done in t
he
> first place, only investigate how to fix it if possible.
> Let's say you've got a table that has 4,333,776 rows. Currently that tabl
e
> has a PK that is functioning as a clustered index(it's the identity). You
've
> then got two more indexes that were placed on this table that are not
> clustered.
> The problem is that the clustered index is never used, and it appears that
> if we moved the clustered to one of the other keys we may gain some
> performance. The problem is that the column that is preferrable isn't uniq
ue
> so SQL Server will add the "uniqueifer" to it. I've checked on the
> selectivity ratio of the column and it's currently at .81. Not sure if
> that's .81% or 81%. If it's the latter I wonder if it's close enough to th
e
> sometimes suggested 95% unique.
> Any thoughts? Any more details I can give to make this more helpful?|||jason7655 wrote:
> I've done a little research but I'd like to get some opinion from the grou
p.
> I'll also preface this by saying that we can't change how it was done in t
he
> first place, only investigate how to fix it if possible.
> Let's say you've got a table that has 4,333,776 rows. Currently that tabl
e
> has a PK that is functioning as a clustered index(it's the identity). You
've
> then got two more indexes that were placed on this table that are not
> clustered.
> The problem is that the clustered index is never used, and it appears that
> if we moved the clustered to one of the other keys we may gain some
> performance. The problem is that the column that is preferrable isn't uniq
ue
> so SQL Server will add the "uniqueifer" to it. I've checked on the
> selectivity ratio of the column and it's currently at .81. Not sure if
> that's .81% or 81%. If it's the latter I wonder if it's close enough to th
e
> sometimes suggested 95% unique.
> Any thoughts? Any more details I can give to make this more helpful?
Why is it a problem that the clustered index is never used?
Anyway, if the clustered index is not unique, then a uniqueifier is only
added to "duplicate" rows. For those rows, the key requires more
storage. Depending on your table structure it will require anywhere
between 6 and 8 bytes more per row. This extra space might not be a
problem for you, but that is something for you to decide.
Another thing to remember is that the clustered index keys are also
stored in all nonclustered indexes. So a narrow clustered index key
(such as int) is preferred over a wide index key. If you choose a very
wide clustered index key, then you other nonclustered index will grow a
lot (and slow down just as much).
HTH,
Gert-Jan|||PK = Primary Key
Let me try to explain a little better.
This table has 3 indexes.
Index 1
Primary key, clustered. 1,2,3,4,5,etc.
Index 2
non-clustered. Use 2nd most.
Index 3
non-clustered, but the main column used by most queries. Used the most.
From my limited reading of sites (including
http://www.sql-server-performance.c...ndexes_p1.aspx,
http://www.sql-server-performance.c...ndexes_p2.aspx, and
various other documents) it would seem that we could gain performance by
changing the Index 3 to clustered.
It's not unique and that raises another question. I would hope that the SQL
Server "uniquefier" that gets added would not change the makeup of the
current data, only create a new column.
Does that make things more clear? Is this a good idea?
"Gert-Jan Strik" wrote:

> jason7655 wrote:
> Why is it a problem that the clustered index is never used?
> Anyway, if the clustered index is not unique, then a uniqueifier is only
> added to "duplicate" rows. For those rows, the key requires more
> storage. Depending on your table structure it will require anywhere
> between 6 and 8 bytes more per row. This extra space might not be a
> problem for you, but that is something for you to decide.
> Another thing to remember is that the clustered index keys are also
> stored in all nonclustered indexes. So a narrow clustered index key
> (such as int) is preferred over a wide index key. If you choose a very
> wide clustered index key, then you other nonclustered index will grow a
> lot (and slow down just as much).
> HTH,
> Gert-Jan
>|||I've also found the following snippet that has gotten me a little confused
and seems to go counter to other things I've read:
"Think of a clustered index as the foundation of the table. Keep it simple
and clean. The point is that if you add non-clustered indexes to the table,
they repeat the clustered index and add the indexed column(s). So ideally
your clustered index should be on a single column.
Ideally, a clustered index should be on a column where new rows have a
steadily increasing value. Otherwise the clustered index will get fragmented
,
and so will the non-clustered indexes based on it.
In short, for a table that sees lots of inserts, use only the identity
column for the clustered index."
(http://sql-server-performance.com/C...3961.aspx#53961)
I could use some clarification on this one.
"jason7655" wrote:
[vbcol=seagreen]
> PK = Primary Key
> Let me try to explain a little better.
> This table has 3 indexes.
> Index 1
> Primary key, clustered. 1,2,3,4,5,etc.
> Index 2
> non-clustered. Use 2nd most.
> Index 3
> non-clustered, but the main column used by most queries. Used the most.
> From my limited reading of sites (including
> http://www.sql-server-performance.c...ndexes_p1.aspx,
> http://www.sql-server-performance.c...ndexes_p2.aspx, and
> various other documents) it would seem that we could gain performance by
> changing the Index 3 to clustered.
> It's not unique and that raises another question. I would hope that the SQ
L
> Server "uniquefier" that gets added would not change the makeup of the
> current data, only create a new column.
> Does that make things more clear? Is this a good idea?
> "Gert-Jan Strik" wrote:
>|||Jason,
Finding rows by seeking the clustered index is less expensive than
seeking a nonclustered index followed by a bookmark lookup. However, for
exact match selects, the difference usually isn't that big, for example
WHERE my_id = 15.
Where the clustered index really comes in handy is when you select a
range that potentially returns a lot of rows (or causes a lot of rows to
be processed). For example WHERE order_date >= '20070701' AND order_date
< '20070801'. In that case, the column order_date would really benefit
from a clustered index.
Another situation is a table that is joined very often to the table that
it is referrencing, and the Primary Key contains the Foreign Key of the
referenced table. For example a "Order Details" table with a Primary Key
on (order_id, line_no) that references an Orders table on column
(order_id). In such a case, creating the clustered index on the Primary
Key/Foreign Key can be a good choice. Basically it is the same rule
here. When joining, there are potentially many rows that need to be
processed.
So changing your index 3 to be the clustered index could be a good
choice, but there is not enough information for me to decide. If you
join this table a lot on the columns of index 1, then join performance
could drop. You should analyse your workload, and decide which queries
are most important (with respect to performance). The, if possible, you
should test the different scenario's and see what works best for you.
BTW: if the clustered index is defined as not unique, the uniqueifier is
added under the covers. Its existance of columns values will never be
exposed when you select from the table.
As for your other post, see my opinion inline:

> I've also found the following snippet that has gotten me a little confused
> and seems to go counter to other things I've read:
> "Think of a clustered index as the foundation of the table. Keep it simple
> and clean. The point is that if you add non-clustered indexes to the table
,
> they repeat the clustered index and add the indexed column(s).
Correct.

> So ideally your clustered index should be on a single column.
The point is not how many columns it concerns. The point is how many
bytes the key uses. From that point of view, two int columns are better
than one varchar(50) column with an average fill of 25 characters. Even
6 int columns are better than a varchar(50) column with average string
size of 25 characters.

> Ideally, a clustered index should be on a column where new rows have a
> steadily increasing value. Otherwise the clustered index will get fragmented,[/vbc
ol]
True. So from that point of view, a clustered index on a GUID is not a
good idea, unless you defragment regularly.
[vbcol=seagreen]
> and so will the non-clustered indexes based on it.
I think this is false. Nonclustered indexes will fragment if new values
of the (nonclustered) indexed key are not "steadily increasing".
However, since nonclustered indexes are much smaller than the clustered
index (which includes the table data), the cost of fragmentation of a
nonclustered index is typically a lot smaller. Of course, regular
defragmenting is the solution for this too.
There is one exception. If your nonclustered index has a very low
selectivy (just a few distinct values), then the nonclustered index'
fragmentation will be very similar to the clustered index'
fragmentation.

> In short, for a table that sees lots of inserts, use only the identity
> column for the clustered index."
> (http://sql-server-performance.com/C...3961.aspx#53961)[/vbco
l]
I definitely disagree with this statement as a general rule, especially
because of the compulsory tone.
For starters, if you don't use Identity as a (surrogate) key, then IMO
you should not add one.
Next, in many situations, the primary key's index is used a lot in
joins, but in other situations (such as filtering on "Lookup Tables")
the optimizer's access path is different and the primary key's index is
used very little.
And finally: it is very unusual to have range selects on an Identity
column. Such range selects are much more common for datetime columns.
So putting a clustered index on an Identity Primary Key could be a good
choice, but it really depends on your situation, and IMO you should not
have policy to always add an Identity and/or to always create the
clustered index on the Identity.
HTH,
Gert-Jan
jason7655 wrote:[vbcol=seagreen]
> PK = Primary Key
> Let me try to explain a little better.
> This table has 3 indexes.
> Index 1
> Primary key, clustered. 1,2,3,4,5,etc.
> Index 2
> non-clustered. Use 2nd most.
> Index 3
> non-clustered, but the main column used by most queries. Used the most.
> From my limited reading of sites (including
> http://www.sql-server-performance.c...ndexes_p1.aspx,
> http://www.sql-server-performance.c...ndexes_p2.aspx, and
> various other documents) it would seem that we could gain performance by
> changing the Index 3 to clustered.
> It's not unique and that raises another question. I would hope that the SQ
L
> Server "uniquefier" that gets added would not change the makeup of the
> current data, only create a new column.
> Does that make things more clear? Is this a good idea?
>
[snip]

Clustered Index on Non-Unique Column

I've done a little research but I'd like to get some opinion from the group.
I'll also preface this by saying that we can't change how it was done in the
first place, only investigate how to fix it if possible.
Let's say you've got a table that has 4,333,776 rows. Currently that table
has a PK that is functioning as a clustered index(it's the identity). You've
then got two more indexes that were placed on this table that are not
clustered.
The problem is that the clustered index is never used, and it appears that
if we moved the clustered to one of the other keys we may gain some
performance. The problem is that the column that is preferrable isn't unique
so SQL Server will add the "uniqueifer" to it. I've checked on the
selectivity ratio of the column and it's currently at .81. Not sure if
that's .81% or 81%. If it's the latter I wonder if it's close enough to the
sometimes suggested 95% unique.
Any thoughts? Any more details I can give to make this more helpful?Is the clustered index unique? If not, why not? Also, what is the PK?
Have you got the complete build script for the table, including indexes and
constraints?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:036D72E7-F282-4316-AA6B-53F555B9DF19@.microsoft.com...
I've done a little research but I'd like to get some opinion from the group.
I'll also preface this by saying that we can't change how it was done in the
first place, only investigate how to fix it if possible.
Let's say you've got a table that has 4,333,776 rows. Currently that table
has a PK that is functioning as a clustered index(it's the identity).
You've
then got two more indexes that were placed on this table that are not
clustered.
The problem is that the clustered index is never used, and it appears that
if we moved the clustered to one of the other keys we may gain some
performance. The problem is that the column that is preferrable isn't unique
so SQL Server will add the "uniqueifer" to it. I've checked on the
selectivity ratio of the column and it's currently at .81. Not sure if
that's .81% or 81%. If it's the latter I wonder if it's close enough to the
sometimes suggested 95% unique.
Any thoughts? Any more details I can give to make this more helpful?|||It does sound like the clustered index is being wasted on field that doesn't
usualy get searched.
Clustered indexes are great for range queries. Say you wanted to get a list
of records from 07/010/2007 to 07/31/2007, having a clueterd index on the
date field would be great.
Nonclustered indexes are better for queries that return one or a few records.
--
MG
"jason7655" wrote:
> I've done a little research but I'd like to get some opinion from the group.
> I'll also preface this by saying that we can't change how it was done in the
> first place, only investigate how to fix it if possible.
> Let's say you've got a table that has 4,333,776 rows. Currently that table
> has a PK that is functioning as a clustered index(it's the identity). You've
> then got two more indexes that were placed on this table that are not
> clustered.
> The problem is that the clustered index is never used, and it appears that
> if we moved the clustered to one of the other keys we may gain some
> performance. The problem is that the column that is preferrable isn't unique
> so SQL Server will add the "uniqueifer" to it. I've checked on the
> selectivity ratio of the column and it's currently at .81. Not sure if
> that's .81% or 81%. If it's the latter I wonder if it's close enough to the
> sometimes suggested 95% unique.
> Any thoughts? Any more details I can give to make this more helpful?|||jason7655 wrote:
> I've done a little research but I'd like to get some opinion from the group.
> I'll also preface this by saying that we can't change how it was done in the
> first place, only investigate how to fix it if possible.
> Let's say you've got a table that has 4,333,776 rows. Currently that table
> has a PK that is functioning as a clustered index(it's the identity). You've
> then got two more indexes that were placed on this table that are not
> clustered.
> The problem is that the clustered index is never used, and it appears that
> if we moved the clustered to one of the other keys we may gain some
> performance. The problem is that the column that is preferrable isn't unique
> so SQL Server will add the "uniqueifer" to it. I've checked on the
> selectivity ratio of the column and it's currently at .81. Not sure if
> that's .81% or 81%. If it's the latter I wonder if it's close enough to the
> sometimes suggested 95% unique.
> Any thoughts? Any more details I can give to make this more helpful?
Why is it a problem that the clustered index is never used?
Anyway, if the clustered index is not unique, then a uniqueifier is only
added to "duplicate" rows. For those rows, the key requires more
storage. Depending on your table structure it will require anywhere
between 6 and 8 bytes more per row. This extra space might not be a
problem for you, but that is something for you to decide.
Another thing to remember is that the clustered index keys are also
stored in all nonclustered indexes. So a narrow clustered index key
(such as int) is preferred over a wide index key. If you choose a very
wide clustered index key, then you other nonclustered index will grow a
lot (and slow down just as much).
HTH,
Gert-Jan|||PK = Primary Key
Let me try to explain a little better.
This table has 3 indexes.
Index 1
Primary key, clustered. 1,2,3,4,5,etc.
Index 2
non-clustered. Use 2nd most.
Index 3
non-clustered, but the main column used by most queries. Used the most.
From my limited reading of sites (including
http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx,
http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx, and
various other documents) it would seem that we could gain performance by
changing the Index 3 to clustered.
It's not unique and that raises another question. I would hope that the SQL
Server "uniquefier" that gets added would not change the makeup of the
current data, only create a new column.
Does that make things more clear? Is this a good idea?
"Gert-Jan Strik" wrote:
> jason7655 wrote:
> >
> > I've done a little research but I'd like to get some opinion from the group.
> >
> > I'll also preface this by saying that we can't change how it was done in the
> > first place, only investigate how to fix it if possible.
> >
> > Let's say you've got a table that has 4,333,776 rows. Currently that table
> > has a PK that is functioning as a clustered index(it's the identity). You've
> > then got two more indexes that were placed on this table that are not
> > clustered.
> >
> > The problem is that the clustered index is never used, and it appears that
> > if we moved the clustered to one of the other keys we may gain some
> > performance. The problem is that the column that is preferrable isn't unique
> > so SQL Server will add the "uniqueifer" to it. I've checked on the
> > selectivity ratio of the column and it's currently at .81. Not sure if
> > that's .81% or 81%. If it's the latter I wonder if it's close enough to the
> > sometimes suggested 95% unique.
> >
> > Any thoughts? Any more details I can give to make this more helpful?
> Why is it a problem that the clustered index is never used?
> Anyway, if the clustered index is not unique, then a uniqueifier is only
> added to "duplicate" rows. For those rows, the key requires more
> storage. Depending on your table structure it will require anywhere
> between 6 and 8 bytes more per row. This extra space might not be a
> problem for you, but that is something for you to decide.
> Another thing to remember is that the clustered index keys are also
> stored in all nonclustered indexes. So a narrow clustered index key
> (such as int) is preferred over a wide index key. If you choose a very
> wide clustered index key, then you other nonclustered index will grow a
> lot (and slow down just as much).
> HTH,
> Gert-Jan
>|||I've also found the following snippet that has gotten me a little confused
and seems to go counter to other things I've read:
"Think of a clustered index as the foundation of the table. Keep it simple
and clean. The point is that if you add non-clustered indexes to the table,
they repeat the clustered index and add the indexed column(s). So ideally
your clustered index should be on a single column.
Ideally, a clustered index should be on a column where new rows have a
steadily increasing value. Otherwise the clustered index will get fragmented,
and so will the non-clustered indexes based on it.
In short, for a table that sees lots of inserts, use only the identity
column for the clustered index."
(http://sql-server-performance.com/Community/forums/p/9800/53961.aspx#53961)
I could use some clarification on this one.
"jason7655" wrote:
> PK = Primary Key
> Let me try to explain a little better.
> This table has 3 indexes.
> Index 1
> Primary key, clustered. 1,2,3,4,5,etc.
> Index 2
> non-clustered. Use 2nd most.
> Index 3
> non-clustered, but the main column used by most queries. Used the most.
> From my limited reading of sites (including
> http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx,
> http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx, and
> various other documents) it would seem that we could gain performance by
> changing the Index 3 to clustered.
> It's not unique and that raises another question. I would hope that the SQL
> Server "uniquefier" that gets added would not change the makeup of the
> current data, only create a new column.
> Does that make things more clear? Is this a good idea?
> "Gert-Jan Strik" wrote:
> > jason7655 wrote:
> > >
> > > I've done a little research but I'd like to get some opinion from the group.
> > >
> > > I'll also preface this by saying that we can't change how it was done in the
> > > first place, only investigate how to fix it if possible.
> > >
> > > Let's say you've got a table that has 4,333,776 rows. Currently that table
> > > has a PK that is functioning as a clustered index(it's the identity). You've
> > > then got two more indexes that were placed on this table that are not
> > > clustered.
> > >
> > > The problem is that the clustered index is never used, and it appears that
> > > if we moved the clustered to one of the other keys we may gain some
> > > performance. The problem is that the column that is preferrable isn't unique
> > > so SQL Server will add the "uniqueifer" to it. I've checked on the
> > > selectivity ratio of the column and it's currently at .81. Not sure if
> > > that's .81% or 81%. If it's the latter I wonder if it's close enough to the
> > > sometimes suggested 95% unique.
> > >
> > > Any thoughts? Any more details I can give to make this more helpful?
> >
> > Why is it a problem that the clustered index is never used?
> >
> > Anyway, if the clustered index is not unique, then a uniqueifier is only
> > added to "duplicate" rows. For those rows, the key requires more
> > storage. Depending on your table structure it will require anywhere
> > between 6 and 8 bytes more per row. This extra space might not be a
> > problem for you, but that is something for you to decide.
> >
> > Another thing to remember is that the clustered index keys are also
> > stored in all nonclustered indexes. So a narrow clustered index key
> > (such as int) is preferred over a wide index key. If you choose a very
> > wide clustered index key, then you other nonclustered index will grow a
> > lot (and slow down just as much).
> >
> > HTH,
> > Gert-Jan
> >|||Jason,
Finding rows by seeking the clustered index is less expensive than
seeking a nonclustered index followed by a bookmark lookup. However, for
exact match selects, the difference usually isn't that big, for example
WHERE my_id = 15.
Where the clustered index really comes in handy is when you select a
range that potentially returns a lot of rows (or causes a lot of rows to
be processed). For example WHERE order_date >= '20070701' AND order_date
< '20070801'. In that case, the column order_date would really benefit
from a clustered index.
Another situation is a table that is joined very often to the table that
it is referrencing, and the Primary Key contains the Foreign Key of the
referenced table. For example a "Order Details" table with a Primary Key
on (order_id, line_no) that references an Orders table on column
(order_id). In such a case, creating the clustered index on the Primary
Key/Foreign Key can be a good choice. Basically it is the same rule
here. When joining, there are potentially many rows that need to be
processed.
So changing your index 3 to be the clustered index could be a good
choice, but there is not enough information for me to decide. If you
join this table a lot on the columns of index 1, then join performance
could drop. You should analyse your workload, and decide which queries
are most important (with respect to performance). The, if possible, you
should test the different scenario's and see what works best for you.
BTW: if the clustered index is defined as not unique, the uniqueifier is
added under the covers. Its existance of columns values will never be
exposed when you select from the table.
As for your other post, see my opinion inline:
> I've also found the following snippet that has gotten me a little confused
> and seems to go counter to other things I've read:
> "Think of a clustered index as the foundation of the table. Keep it simple
> and clean. The point is that if you add non-clustered indexes to the table,
> they repeat the clustered index and add the indexed column(s).
Correct.
> So ideally your clustered index should be on a single column.
The point is not how many columns it concerns. The point is how many
bytes the key uses. From that point of view, two int columns are better
than one varchar(50) column with an average fill of 25 characters. Even
6 int columns are better than a varchar(50) column with average string
size of 25 characters.
> Ideally, a clustered index should be on a column where new rows have a
> steadily increasing value. Otherwise the clustered index will get fragmented,
True. So from that point of view, a clustered index on a GUID is not a
good idea, unless you defragment regularly.
> and so will the non-clustered indexes based on it.
I think this is false. Nonclustered indexes will fragment if new values
of the (nonclustered) indexed key are not "steadily increasing".
However, since nonclustered indexes are much smaller than the clustered
index (which includes the table data), the cost of fragmentation of a
nonclustered index is typically a lot smaller. Of course, regular
defragmenting is the solution for this too.
There is one exception. If your nonclustered index has a very low
selectivy (just a few distinct values), then the nonclustered index'
fragmentation will be very similar to the clustered index'
fragmentation.
> In short, for a table that sees lots of inserts, use only the identity
> column for the clustered index."
> (http://sql-server-performance.com/Community/forums/p/9800/53961.aspx#53961)
I definitely disagree with this statement as a general rule, especially
because of the compulsory tone.
For starters, if you don't use Identity as a (surrogate) key, then IMO
you should not add one.
Next, in many situations, the primary key's index is used a lot in
joins, but in other situations (such as filtering on "Lookup Tables")
the optimizer's access path is different and the primary key's index is
used very little.
And finally: it is very unusual to have range selects on an Identity
column. Such range selects are much more common for datetime columns.
So putting a clustered index on an Identity Primary Key could be a good
choice, but it really depends on your situation, and IMO you should not
have policy to always add an Identity and/or to always create the
clustered index on the Identity.
HTH,
Gert-Jan
jason7655 wrote:
> PK = Primary Key
> Let me try to explain a little better.
> This table has 3 indexes.
> Index 1
> Primary key, clustered. 1,2,3,4,5,etc.
> Index 2
> non-clustered. Use 2nd most.
> Index 3
> non-clustered, but the main column used by most queries. Used the most.
> From my limited reading of sites (including
> http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx,
> http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx, and
> various other documents) it would seem that we could gain performance by
> changing the Index 3 to clustered.
> It's not unique and that raises another question. I would hope that the SQL
> Server "uniquefier" that gets added would not change the makeup of the
> current data, only create a new column.
> Does that make things more clear? Is this a good idea?
>
[snip]

clustered index on IDENTITY column

Hi, what opinion do you people have with clustered index on IDENTITY column?
Is there more advantage or divantage?
Wouldn't it causes all insertion to be added to the same page. Is this 'hot
page' a big issue? Can it helps to prevent fragmentation? How is it so?
Secondly, is adding IDENTITY column itself recommended? Is it because by
plainly using the logical unique (eg. Passport Number that is assumed to be
unique) would be slower, maybe because it is not integer; or maybe composite
primary key? Is a IDENTITY primary key useful because of its use as a foreig
n
in related table?
thanks
EugeneHi:
Using a clustered index on an identity column is recommended. It helps
because all the values of a clustered index based on identity values are
unique.
The issue is a bit more complex, however, it wouldn't cause more insertion
problems or hot pages than using a not so distinct primary key column.
Perhaps, if you had a specific problem you are workign on, the context for
the question will be clearer.
Thanks,
Webmaster
http://www.kdkeys.net
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:E684212D-5AD0-4F00-8549-780DE4225D86@.microsoft.com...
> Hi, what opinion do you people have with clustered index on IDENTITY
> column?
> Is there more advantage or divantage?
> Wouldn't it causes all insertion to be added to the same page. Is this
> 'hot
> page' a big issue? Can it helps to prevent fragmentation? How is it so?
> Secondly, is adding IDENTITY column itself recommended? Is it because by
> plainly using the logical unique (eg. Passport Number that is assumed to
> be
> unique) would be slower, maybe because it is not integer; or maybe
> composite
> primary key? Is a IDENTITY primary key useful because of its use as a
> foreign
> in related table?
> thanks
> Eugene|||Using a clustered index on an IDENTITY column is usually best, provided it
is the primary key for the table. Here are a couple reasons:
(1) identity columns are ususally smaller, so joins are faster. In addition
the clustered index key is used as the row locator in every nonclustered
index, so a smaller clustered index key means smaller nonclustered indexes.
Smaller is always faster, because fewer disk reads are required to access an
index.
(2) identity columns usually increase, therefore new rows are generally
added at the end of the clustered index, thus avoiding page splits and all
of the performance issues associated with them.
In my opinion, it is always better to have a primary key that is guaranteed
to be stable, as opposed to a natural or composite key that can change.
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:E684212D-5AD0-4F00-8549-780DE4225D86@.microsoft.com...
> Hi, what opinion do you people have with clustered index on IDENTITY
column?
> Is there more advantage or divantage?
> Wouldn't it causes all insertion to be added to the same page. Is this
'hot
> page' a big issue? Can it helps to prevent fragmentation? How is it so?
> Secondly, is adding IDENTITY column itself recommended? Is it because by
> plainly using the logical unique (eg. Passport Number that is assumed to
be
> unique) would be slower, maybe because it is not integer; or maybe
composite
> primary key? Is a IDENTITY primary key useful because of its use as a
foreign
> in related table?
> thanks
> Eugene|||Hi Eugene,
A few issue here
Clustered Index are the data itself. So the leaf pages of the index are the
data pages. Having an identity make no difference to this what so every.
All it means in the identity column sequence is the same as the logical
layout of the leaf pages. Can this stop or prevent fragmentation yeah
perhaps but if say your Passport Number increased in value and didn't change
(as primary key) this would do the same thing. The issue really isn't
identity or not it is the candidate for PK shouldn't change over time. If
it is likely to then it should not be PK. Remember that a leaf page is only
8K in size the data in that page for is a little bit smaller. So the number
of rows that can fit in a page depends on the row size and the intital fill
factor.
There big debate between the issue of int ot big int over other data types
for PK (in terms of speed and index size ) I would say that if you are
looking at this as a problem then your scope for issues are too small. Disk
space is too cheap to worry about in most cases and in most cases the size
of a PK or index isn't then performance problem. More importantly is making
sure you have covering indexes or at least indexes on the tables that relate
to the grouping and where statements in your views and sql statements.
kind regards
Greg O
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:E684212D-5AD0-4F00-8549-780DE4225D86@.microsoft.com...
> Hi, what opinion do you people have with clustered index on IDENTITY
> column?
> Is there more advantage or divantage?
> Wouldn't it causes all insertion to be added to the same page. Is this
> 'hot
> page' a big issue? Can it helps to prevent fragmentation? How is it so?
> Secondly, is adding IDENTITY column itself recommended? Is it because by
> plainly using the logical unique (eg. Passport Number that is assumed to
> be
> unique) would be slower, maybe because it is not integer; or maybe
> composite
> primary key? Is a IDENTITY primary key useful because of its use as a
> foreign
> in related table?
> thanks
> Eugene|||Hi All, thanks for the kind reply.
I am not looking at a particular project now, I want to know the
technical/academic knowledge/experience on this consideration.
Brian, you mentioned, adding to the end of the clustered index prevent page
split, how can it be? Isn't that when the page becomes full, the page might
still split if the b-tree node cannot accomodate further? Or are you
comparing between insertion in the middle of the page? Is the difference in
page split potential very big?
Greg, if the passport number is same throughtout the db lifespan, and
incrementing, then it would be in the same sequence as IDENTITY. HOw bout if
it is always same, but not incrementing (meaning it might be added in the
middle)? What is the impact compare to sequential insertion? I think
something like my question to Brian.
Nope, I am not looking at int and bigint comparison. I agree that disk cost
is cheap, but I have reservation that since bigint is larger, it may result
in less rows being packed in a single page, thus more pages, page split :P
especially in the case where each row size is very small (the extra four
bytes would be insignicant if the row size is large, right?) I have a
question here on the int and bigint cpu performance. Is it because our
current computer is 32bit, so it performs better with four bytes int? Then,
next time when most of the computing systems move to 64bit machine, would
bigint be a better choice?
thanks, thanks a great lot
Eugene|||On Sun, 7 Aug 2005 01:29:01 -0700, Eugene wrote:

>Hi All, thanks for the kind reply.
>I am not looking at a particular project now, I want to know the
>technical/academic knowledge/experience on this consideration.
>Brian, you mentioned, adding to the end of the clustered index prevent page
>split, how can it be? Isn't that when the page becomes full, the page might
>still split if the b-tree node cannot accomodate further?
Hi Eugene,
With an insertion at the end of the page, there won't be a page split.
If the page is full, a new page is simply opened, and the new row is
inserted as the first row on the new page. The new page will of course
also have to be inserted in the next higher level of the B-tree index,
but with the insertion at the end of the table, that new index entry
will also be at the end of the page.

> Or are you
>comparing between insertion in the middle of the page? Is the difference in
>page split potential very big?
That is where the difference occurs. If a row needs to be inserted in
the middle of a page that is already full, half of the rows need to be
moved to a new page to make place. That takes more time than simply
opening a new page for the new row.
Run the following example to see the difference between clustering on an
increasing value or clustering on a random value (be sure to run it a
few times, to exclude the influence of other processes running on your
computer).
CREATE TABLE Test (Col1 int NOT NULL PRIMARY KEY NONCLUSTERED IDENTITY,
Col2 int NOT NULL UNIQUE CLUSTERED,
OtherData char(40) NOT NULL)
go
-- Use same seed to start with
SELECT RAND(123)
go
DECLARE @.Start datetime, @.End datetime, @.Done char(1)
SET @.Done = 'N'
SET @.Start = CURRENT_TIMESTAMP
WHILE @.Done = 'N'
BEGIN
INSERT INTO Test (Col2, OtherData)
SELECT RAND() * 2000000000, ''
IF SCOPE_IDENTITY() >= 10000
SET @.Done = 'Y'
END
SET @.End = CURRENT_TIMESTAMP
SELECT @.Start AS Started, @.End AS Finished, DATEDIFF(ms, @.Start, @.End)
AS Elapsed
go
sp_spaceused 'Test', 'TRUE'
go
DROP TABLE Test
go
CREATE TABLE Test (Col1 int NOT NULL PRIMARY KEY CLUSTERED IDENTITY,
Col2 int NOT NULL UNIQUE NONCLUSTERED,
OtherData char(2) NOT NULL)
go
-- Use same seed to start with
SELECT RAND(123)
go
DECLARE @.Start datetime, @.End datetime, @.Done char(1)
SET @.Done = 'N'
SET @.Start = CURRENT_TIMESTAMP
WHILE @.Done = 'N'
BEGIN
INSERT INTO Test (Col2, OtherData)
SELECT RAND() * 2000000000, ''
IF SCOPE_IDENTITY() >= 10000
SET @.Done = 'Y'
END
SET @.End = CURRENT_TIMESTAMP
SELECT @.Start AS Started, @.End AS Finished, DATEDIFF(ms, @.Start, @.End)
AS Elapsed
go
sp_spaceused 'Test', 'TRUE'
go
DROP TABLE Test
go

>Nope, I am not looking at int and bigint comparison. I agree that disk cost
>is cheap, but I have reservation that since bigint is larger, it may result
>in less rows being packed in a single page, thus more pages, page split :P
>especially in the case where each row size is very small (the extra four
>bytes would be insignicant if the row size is large, right?)
Yes, on big rows, the 4 extra bytes won't make much difference (unless
they are just the few bytes that make the difference between three rows
per page or two rows per page, of course).
But don't forget the indexes. The clustered index key is included in
every nonclustered index as well. Indexes are usually small, so the 4
extra bytes do make a difference there.
If you change the code above to use bigint instead of int, you'll see
that the time to insert all rows goes up a bit. But the real difference
will be in retrieving data - especially on queries that might use an
index scan on the nonclustered index.

> I have a
>question here on the int and bigint cpu performance. Is it because our
>current computer is 32bit, so it performs better with four bytes int? Then,
>next time when most of the computing systems move to 64bit machine, would
>bigint be a better choice?
I doubt if that is a factor of importance. It is my experience that
performance is governed by physical I/O first, logical I/O second. The
amount of work the processor has to do is usually irrelevant - the CPU
will probably still spend most of it's time waiting for the next page to
be read from disk.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Eugene,
It doesn't prevent page splits, it minimizes them. In a clustered index,
the rows of the table live in the leaf pages, which are all at the same
level in the tree. Rows are added to the leaf pages at the end, in order,
so insertions don't cause page splits for leaf pages. With an integer key,
each index page can store 539 index rows, so splits of index pages are also
minimized. If only inserts occur, then I believe that page splits do not
occur at all, because the tree height is the same and optimal regardless of
whether the rightmost node at each level has 1 key (except the root which
has 2 keys, of course) and all nodes to the left are full, or if each node
at each level has the same number of keys. Index pages are therefore
inserted at the end of each level and a new root page is created when the
index becomes full. I'm pretty sure that's what happens, but it's possible
that the rightmost node at each level splits when inserting into a full
index. Even so, the number of splits is still minimized, because inserts do
not occur in the middle of the structure.
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:1B76D478-166D-4A60-AA49-0A38988AD97E@.microsoft.com...
> Hi All, thanks for the kind reply.
> I am not looking at a particular project now, I want to know the
> technical/academic knowledge/experience on this consideration.
> Brian, you mentioned, adding to the end of the clustered index prevent
page
> split, how can it be? Isn't that when the page becomes full, the page
might
> still split if the b-tree node cannot accomodate further? Or are you
> comparing between insertion in the middle of the page? Is the difference
in
> page split potential very big?
> Greg, if the passport number is same throughtout the db lifespan, and
> incrementing, then it would be in the same sequence as IDENTITY. HOw bout
if
> it is always same, but not incrementing (meaning it might be added in the
> middle)? What is the impact compare to sequential insertion? I think
> something like my question to Brian.
> Nope, I am not looking at int and bigint comparison. I agree that disk
cost
> is cheap, but I have reservation that since bigint is larger, it may
result
> in less rows being packed in a single page, thus more pages, page split :P
> especially in the case where each row size is very small (the extra four
> bytes would be insignicant if the row size is large, right?) I have a
> question here on the int and bigint cpu performance. Is it because our
> current computer is 32bit, so it performs better with four bytes int?
Then,
> next time when most of the computing systems move to 64bit machine, would
> bigint be a better choice?
> thanks, thanks a great lot
> Eugene|||A hotspot at the end of a table does exist, but the impact of this is
generally minimal, especially if your RAID controllers can cache writes as
well as reads. (RAID controllers with a battery can implement a write-back
cache as opposed to a write-through cache, which means that the data to be
written remain in RAM and are only periodically flushed out to disk.) I'm
not really convinced that a "hotspot" affects overall performance anyway,
because having writes spread throughout a table can require the head to move
more often (disk ss), which has a much more detrimental impact on
performance.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:O4##Ya1mFHA.2444@.tk2msftngp13.phx.gbl...
> Eugene,
> It doesn't prevent page splits, it minimizes them. In a clustered index,
> the rows of the table live in the leaf pages, which are all at the same
> level in the tree. Rows are added to the leaf pages at the end, in order,
> so insertions don't cause page splits for leaf pages. With an integer
key,
> each index page can store 539 index rows, so splits of index pages are
also
> minimized. If only inserts occur, then I believe that page splits do not
> occur at all, because the tree height is the same and optimal regardless
of
> whether the rightmost node at each level has 1 key (except the root which
> has 2 keys, of course) and all nodes to the left are full, or if each node
> at each level has the same number of keys. Index pages are therefore
> inserted at the end of each level and a new root page is created when the
> index becomes full. I'm pretty sure that's what happens, but it's
possible
> that the rightmost node at each level splits when inserting into a full
> index. Even so, the number of splits is still minimized, because inserts
do
> not occur in the middle of the structure.
>
> "Eugene" <Eugene@.discussions.microsoft.com> wrote in message
> news:1B76D478-166D-4A60-AA49-0A38988AD97E@.microsoft.com...
> page
> might
> in
bout
> if
the
> cost
> result
:P
> Then,
would
>|||On Sat, 6 Aug 2005 11:59:07 -0700, Eugene
<Eugene@.discussions.microsoft.com> wrote:
>Hi, what opinion do you people have with clustered index on IDENTITY column
?
>Is there more advantage or divantage?
It's very fashionable, whatever the theoretical or practical
arguments.

>Wouldn't it causes all insertion to be added to the same page. Is this 'hot
>page' a big issue? Can it helps to prevent fragmentation? How is it so?
Other posters have addressed this.

>Secondly, is adding IDENTITY column itself recommended? Is it because by
>plainly using the logical unique (eg. Passport Number that is assumed to be
>unique) would be slower, maybe because it is not integer; or maybe composit
e
>primary key? Is a IDENTITY primary key useful because of its use as a forei
gn
>in related table?
Just a note that if you have a typically modest application of a few
dozen users on a typically modest database of say under 1gb running on
a typically powerful server of 2 processors, 3ghz, 4gb RAM, RAID5,
mirrored logs, ... then you're about 1000% overpowered and few of
these concerns will ever become visible, assuming your data model and
application are put together at all competently.
Heck, half the apps I see anymore turn out to me missing PKs or other
major indices, accidentally dropped (or duplicated!) during
maintenance over the months or years, and nobody notices for months or
years except for a few grumbles, and then the first thing they usually
do is upgrade the hardware, not audit the system!
J.|||I just googled "a little Dr. Codd" and found nothing... same with BOL.
har har har
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123458943.236091.255690@.g43g2000cwa.googlegroups.com...
> plainly using the logical unique (eg. Passport Number that is assumed
> to be
> unique) would be slower, maybe because it is not integer; or maybe
> composite
> primary key? <<
> Do not use IDENTITY or other proprietary exposed physical locators in
> an RDBMS. Read a little Dr. Codd for the details of what surrogate is.
> Also look up validation and verification as properties for all data
> elements. Then look up the design of navigational databases which
> newbies mimic with IDENTITY. In fact, making IDENTITY the key would
> allow duplicate passport numbers to go undetected
> 2) Most of the time you do not wish to waste your one clustered index
> on a unique column. The Sybase/SQL Server implementation is based on a
> file system. It uses physically contigous storage and tree indexes.
> This means that you get one and only one clustered index per table. If
> you do a lot of GROUP BY's on one set of columns (totals by city, state
> or whatever), then a table scan on that sorted ordering will be much
> faster than random access. This is where you gain performance.
> 3) The "hot page" is not as big an issue as it has been in earlier
> releases. Data quality and integrity is a much, much bigger issue. If
> you have an (n) column nautral key in your data model, you **must**
> enforce it and IDENTITY will not change this fact of life.
> We live in an age of 64-bit hardware, parallel processors and all that
> jazz. Tuning an RDBMS at the byte-level is a waste of time and
> resources. The right answer is to remove redundancy instead. Do the
> math -- how much time do you need to read a byte off of a hard drive?
> What is the speed of main storage? So even if I need 100 times more
> processor time to do a join, I am ahead of ther game.
>