Developer Forum

by **Zarko Jova** » Wed, 31 Aug 2005 18:13:11

it seems that a clustered index is better. try both ways and look at the
execution plan(s)

by **Roji. P. T** » Wed, 31 Aug 2005 18:17:02

his is a multi-part message in MIME format.

Pradeep,

Thats a good idea because clustered index is ideal for range search.

One advantage of having a clustered index on the IDENTITY column is that it will help you avoid page split problems.
But your assumption about the order of IDENTITY value is wrong. IDENTITY onloy provides a logical sequence, whereas a clustered index
controls the order in which the rows are physically stored.

--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com

"Pradeep Kutty" < XXXX@XXXXX.COM > wrote in message news:% XXXX@XXXXX.COM ...
Hi All,

I have a table (detail table) with fields ID (Identity) primary Key and a DTTM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with its master table.

I have many reports which uses this table and for all the reports the basic criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 week or so.

Im planning to add a clustered index on DTTM field so that the reports would become faster compared to a table scan what its doing now.

My question is, is it a good idea to create a clustered index on a Datetime field?
or is it a better way to make ID the clustered index and then create a non clustered index on DTTM?

But I always had the doubt that, what is the purpose of creating a clustered index on an identity field that too which is already a primary key,
since an identity field is already ordered. Does it make sense to create a clustered in index on Identity field.

Add to this most of my Stored procedures which are used to retrieve uses ID to join with its master table.
DTTM would be used only in reports...

Thanks,
Prad

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2722" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Pradeep,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>>Im planning to add a clustered index on DTTM
field so that the reports would become faster compared to a table scan what its
doing now.</FONT></DIV>
<DIV><FONT face=Arial size=2>Thats a good idea because clustered index is
ideal for range search.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>
<DIV>>But I always had the doubt that, what is the purpose of creating a
clustered index on an identity field that too which is already a primary
key,</DIV>
<DIV>>since an identity field is already ordered. Does it make sense to
create a clustered in index on Identity field.</DIV>
<DIV> </DIV>
<DIV>One advantage of having a clustered index on the IDENTITY column is that it
will help you avoid page split problems.</DIV>
<DIV>But your assumption about the order of IDENTITY value is wrong. IDENTITY
onloy provides a logical sequence, whereas a clustered index<BR>controls the
order in which the rows are physically stored.</DIV>
<DIV> </DIV>
<DIV><

by **Grif** » Wed, 31 Aug 2005 19:19:56

his is a multi-part message in MIME format.

One note of caution (playing devil's advocate here).

I don't know how many people you have updating your table or the hardware you use but....

....one problem with clustered indexes based on the ID is that all WRITES must occur on the same place on the disk, or on the same disk if you're using an array of disks...everyone's writing data to a new row that goes in after the last row.

If you have a huge number of updates occurring (which you probably don't) then this can cause a problem as you effectively get a "hot spot" on the disk where everyone is attempting to write to the same part of the disk. Compare this to a clustered index on (say) the surname, where new rows are added to different parts of the disk (or on different disks in an array of disks).

Griff

"Pradeep Kutty" < XXXX@XXXXX.COM > wrote in message news:% XXXX@XXXXX.COM ...
Hi All,

I have a table (detail table) with fields ID (Identity) primary Key and a DTTM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with its master table.

I have many reports which uses this table and for all the reports the basic criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 week or so.

Im planning to add a clustered index on DTTM field so that the reports would become faster compared to a table scan what its doing now.

My question is, is it a good idea to create a clustered index on a Datetime field?
or is it a better way to make ID the clustered index and then create a non clustered index on DTTM?

But I always had the doubt that, what is the purpose of creating a clustered index on an identity field that too which is already a primary key,
since an identity field is already ordered. Does it make sense to create a clustered in index on Identity field.

Add to this most of my Stored procedures which are used to retrieve uses ID to join with its master table.
DTTM would be used only in reports...

Thanks,
Prad

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2722" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT size=2>One note of caution (playing devil's advocate
here).</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>I don't know how many people you have updating your table or
the hardware you use but....</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>...one problem with clustered indexes based on the ID is that
all WRITES must occur on the same place on the disk, or on the same disk if
you're using an array of disks...everyone's writing data to a new row that goes
in after the last row.</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>If you have a huge number of updates occurring (which you
probably don't) then this can cause a problem as you effectively get a "hot
spot" on the disk where everyone is attempting to write to the same part of the
disk. Compare this to a clustered index on (say) the surname, where new
rows are ad

by **Brian Selz** » Wed, 31 Aug 2005 19:52:17

his is a multi-part message in MIME format.

If it is used in joins, then I would put the clustered index on the IDENTITY column. This can speed up inserts into this table, inserts into related tables, and joins between this table and related tables. If the order of the IDENTITY increment matches the order of the clustered index on the IDENTITY column, then all inserts will occur at the end of the table, which minimizes the required index maintenance operations.

If a table has a clustered index, then all nonclustered indexes use the clustered index key to locate rows in the table. If you put a nonclustered index on the primary key, then every join will result in an additional step in the execution plan--a bookmark lookup. This extra level of indirection can significantly reduce the performance of every join. In addition, if you use a clustered index on a datetime column, and the datetime column is not a candidate key, then SQL Server will add a 4-byte uniqifier to every index row so that the index key can be used in nonclustered indexes to locate rows. This increases the size of each nonclustered index, and can further reduce query performance, especially with respect to joins.

To boost performance for reporting, you have other options aside from simply adding an index. Here are a couple: (1) use a covering index so that the bookmark lookup will not be necessary, or (2) create an indexed view, and use both the datetime and the identity column (in that order) as the clustered index key for the view. If all of the columns necessary for the query exist in the index key, then there is no need for SQL Server to access the actual data row, so the performance degradation resulting from the use a nonclustered index will be minimized. If that doesn't provide adequate reporting performance, the indexed view option will at least meet the select performance of accessing a table with a clustered index directly, without degrading the performance of the joins. It should be noted, however, that insert performance will be degraded by the addition of any index or indexed view.

"Pradeep Kutty" < XXXX@XXXXX.COM > wrote in message news:# XXXX@XXXXX.COM ...
Hi All,

I have a table (detail table) with fields ID (Identity) primary Key and a DTTM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with its master table.

I have many reports which uses this table and for all the reports the basic criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 week or so.

Im planning to add a clustered index on DTTM field so that the reports would become faster compared to a table scan what its doing now.

My question is, is it a good idea to create a clustered index on a Datetime field?
or is it a better way to make ID the clustered index and then create a non clustered index on DTTM?

But I always had the doubt that, what is the purpose of creating a clustered index on an identity field that too which is already a primary key,
since an identity field is already ordered. Does it make sense to create a clustered in index on Identity field.

Add to this most of my Stored procedures which are used to retrieve uses ID to join with its master table.
DTTM would be used only in reports...

Thanks,
Prad

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=

by **Robert Kle** » Wed, 31 Aug 2005 21:48:32

Either I overlooked it or nobody actually mentioned a composite index. If
you always do queries that join by your PK and use only a date range then
a composite clustered index on (timestamp, ID) might also be worth
considering. Or am I missing something here?

Kind regards

robert

by **Pradeep Ku** » Wed, 31 Aug 2005 22:15:03

This is a multi-part message in MIME format.

Hi All,

I have a table (detail table) with fields ID (Identity) primary Key and a DTTM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with its master table.

I have many reports which uses this table and for all the reports the basic criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 week or so.

Im planning to add a clustered index on DTTM field so that the reports would become faster compared to a table scan what its doing now.

My question is, is it a good idea to create a clustered index on a Datetime field?
or is it a better way to make ID the clustered index and then create a non clustered index on DTTM?

But I always had the doubt that, what is the purpose of creating a clustered index on an identity field that too which is already a primary key,
since an identity field is already ordered. Does it make sense to create a clustered in index on Identity field.

Add to this most of my Stored procedures which are used to retrieve uses ID to join with its master table.
DTTM would be used only in reports...

Thanks,
Prad

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2722" name=GENERATOR>
<STYLE></STYLE>
</HEAD><FONT face=Arial><FONT size=2>
<BODY>
<DIV>Hi All,</DIV>
<DIV> </DIV>
<DIV>I have a table (detail table) with fields ID (Identity) primary Key and a
DTTM datetime field which is a heap.</DIV>
<DIV>Right now there is a non clustered index on ID which is used to join with
its master table.</DIV>
<DIV> </DIV>
<DIV>I have many reports which uses this table and for all the reports the basic
criteria is between DTTM.</DIV>
<DIV>say I run the report for say for a date range of 1 month, 1 week or
so.</DIV>
<DIV> </DIV>
<DIV>Im planning to add a clustered index on DTTM field so that the reports
would become faster compared to a table scan what its doing now.</DIV>
<DIV> </DIV>
<DIV>My question is, is it a good idea to create a clustered index on a Datetime
field?</DIV>
<DIV>or is it a better way to make ID the clustered index and then create a non
clustered index on DTTM?</DIV>
<DIV> </DIV>
<DIV>But I always had the doubt that, what is the purpose of creating a
clustered index on an identity field that too which is already a primary
key,</DIV>
<DIV>since an identity field is already ordered. Does it make sense to create a
clustered in index on Identity field.</DIV>
<DIV> </DIV>
<DIV>Add to this most of my Stored procedures which are used to retrieve uses ID
to join with its master table.</DIV>
<DIV>DTTM would be used only in reports...</DIV>
<DIV> </DIV>
<DIV>Thanks,</DIV>
<DIV>Prad</DIV>
<DIV> </DIV></BODY></HTML></FONT></FONT>

Developer Forum

Clustered Index on Date Field or Identity Field ....

Clustered Index on Date Field or Identity Field ....

Clustered Index on Date Field or Identity Field ....

Clustered Index on Date Field or Identity Field ....

Clustered Index on Date Field or Identity Field ....

Clustered Index on Date Field or Identity Field ....

Clustered Index on Date Field or Identity Field ....