I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s 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 w
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 c
lustered 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 c
lustered 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,
PradPradeep Kutty wrote:
> 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 w
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
>
it seems that a clustered index is better. try both ways and look at the
execution plan(s)|||Pradeep,
>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.
Thats a good idea because clustered index is ideal for range search.
>But I always had the doubt that, what is the purpose of creating a clustere
d 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 clustere
d in index on Identity field.
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 onl
oy 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" <pradeepk@.healthasyst.com> wrote in message news:%23EVpg8TrF
HA.716@.TK2MSFTNGP10.phx.gbl...
Hi All,
I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s 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 w
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 c
lustered 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 c
lustered 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|||One note of caution (playing devil's advocate here).
I don't know how many people you have updating your table or the hardware yo
u use but...
...one problem with clustered indexes based on the ID is that all WRITES mu
st 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) th
en 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" <pradeepk@.healthasyst.com> wrote in message news:%23EVpg8TrF
HA.716@.TK2MSFTNGP10.phx.gbl...
Hi All,
I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s 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 w
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 c
lustered 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 c
lustered 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|||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 ta
bles, 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 clus
tered index key to locate rows in the table. If you put a nonclustered inde
x on the primary key, then every join will result in an additional step in t
he execution plan--a bookmark lookup. This extra level of indirection can s
ignificantly 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 can
didate key, then SQL Server will add a 4-byte uniqifier to every index row s
o that the index key can be used in nonclustered indexes to locate rows. Th
is increases the size of each nonclustered index, and can further reduce que
ry 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 b
ookmark 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 noncluste
red index will be minimized. If that doesn't provide adequate reporting per
formance, 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 performa
nce will be degraded by the addition of any index or indexed view.
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message news:#EVpg8TrFHA
.716@.TK2MSFTNGP10.phx.gbl...
Hi All,
I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s 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 w
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 c
lustered 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 c
lustered 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|||Pradeep Kutty wrote:
> 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 w
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...
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
No comments:
Post a Comment