identity, indexes, datetime

jb
Well, choosing clustered index may not be so easy , you will have to
investigate many things. So, choosing "right" column/s will speed up the
query/is and improve perfomance ...

> Is there much difference in time overhead adding a clustered,
> non-clustered or identity field to existing tables with many rows (this
> database is used by many client and the time taken for the schema update
> is a concern).
Take a look at CREATE INDEX... WITH DROP EXISTSING option in the BOL
"jb" <b@.b.com> wrote in message
news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We have an largish audit table with many columns. There are
> inserts/deletes only on this table, no updates. Its never been indexed or
> had a pk. Reports are run against this table, usually on date ranges
> (datetime col) with joins to other tables on userid etc.
> Recently an identity column was added, to improve performance. For start
> I'm wondering would this do anything at all for performance? Presumably
> the table is sorted by this field in the absence of any other pk/index? If
> so what benefit is this if the column is never used?
> Surely the best strategy would be to put a clustered index on the datetime
> field and not bother with the identity?
> Is there much difference in time overhead adding a clustered,
> non-clustered or identity field to existing tables with many rows (this
> database is used by many client and the time taken for the schema update
> is a concern).
> Thanks
> JB.
Sory, should be DROP_EXISTING
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O62QTeCfHHA.3956@.TK2MSFTNGP03.phx.gbl...
> jb
> Well, choosing clustered index may not be so easy , you will have to
> investigate many things. So, choosing "right" column/s will speed up the
> query/is and improve perfomance ...
>
> Take a look at CREATE INDEX... WITH DROP EXISTSING option in the BOL
>
>
>
> "jb" <b@.b.com> wrote in message
> news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...
>

Identity column on table and Ignore Duplicate on Index

Hello,

I have a table with an Identity Column set up. I also have an index on the table that is set to Ignore Duplicate. Identity starts at 1 and is incremented by 1.

So first 5 rows inserted get identity

1

2

3

4

5

If I insert rows that get ignored because of the index with Ignore Duplicate, it is ignored correctly. But, the next row to get inserted will have an identity value of 7.

So, even though the insert was ignored because of the index with Ignore Duplicate, the Identity column was incremented behind the scenes.

Is there any way to avoid this?

Thanks,

John

DBCC CHECKIDENT with the reseed option.|||The value that is used to create the unique number is incremented each time an insert is done against the table. However, it that insert is rolled back (either because of user rollback or server rollback), the number will not be reset, as this would cause problems with multiple transactions getting unique numbers at the same time.

How important is it that there are no gaps in the numbers, i.e. is there a reason why you want to avoid gaps?

Thanks,|||The identity field is guaranteed to be UNIQUE, NOT SEQUENTIAL. This is a common misconception.

DO NOT do anything that depends on not having gaps. You will be sorry in the long run.

Identity column on table and Ignore Duplicate on Index

Hello,

I have a table with an Identity Column set up. I also have an index on the table that is set to Ignore Duplicate. Identity starts at 1 and is incremented by 1.

So first 5 rows inserted get identity

1

2

3

4

5

If I insert rows that get ignored because of the index with Ignore Duplicate, it is ignored correctly. But, the next row to get inserted will have an identity value of 7.

So, even though the insert was ignored because of the index with Ignore Duplicate, the Identity column was incremented behind the scenes.

Is there any way to avoid this?

Thanks,

John

DBCC CHECKIDENT with the reseed option.|||The value that is used to create the unique number is incremented each time an insert is done against the table. However, it that insert is rolled back (either because of user rollback or server rollback), the number will not be reset, as this would cause problems with multiple transactions getting unique numbers at the same time.

How important is it that there are no gaps in the numbers, i.e. is there a reason why you want to avoid gaps?

Thanks,|||The identity field is guaranteed to be UNIQUE, NOT SEQUENTIAL. This is a common misconception.

DO NOT do anything that depends on not having gaps. You will be sorry in the long run.

Identity Column and Index

Does identity column need to be indexed? For a very large table, if we do
not need the identity column to do selection, and the only purpose of the
identity column is be part of the primary key (clustered), do we need an
extra index for the identity column only? We are using sql server 2005.
Thanks,
Lijun
If there is already a primary key on the identity, then you don't need
another index. The PK automatically creates one.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
news:OyhEUeAUIHA.1204@.TK2MSFTNGP03.phx.gbl...
Does identity column need to be indexed? For a very large table, if we do
not need the identity column to do selection, and the only purpose of the
identity column is be part of the primary key (clustered), do we need an
extra index for the identity column only? We are using sql server 2005.
Thanks,
Lijun
|||Lijun
Determining whether or not a column needs an index does not depend on
whether the column has the identity property or not.
As you know, if the identity column is part of the primary key, it will be
part of an index. Keep in mind that there is nothing about the identity
property
that enforces uniqueness. If you need the identity column itself to be
unique, you should consider a unique constraint on that column.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
news:OyhEUeAUIHA.1204@.TK2MSFTNGP03.phx.gbl...
> Does identity column need to be indexed? For a very large table, if we do
> not need the identity column to do selection, and the only purpose of the
> identity column is be part of the primary key (clustered), do we need an
> extra index for the identity column only? We are using sql server 2005.
> Thanks,
> Lijun
>

Identity Column and Index

Does identity column need to be indexed? For a very large table, if we do
not need the identity column to do selection, and the only purpose of the
identity column is be part of the primary key (clustered), do we need an
extra index for the identity column only? We are using sql server 2005.
Thanks,
LijunLijun
Determining whether or not a column needs an index does not depend on
whether the column has the identity property or not.
As you know, if the identity column is part of the primary key, it will be
part of an index. Keep in mind that there is nothing about the identity
property
that enforces uniqueness. If you need the identity column itself to be
unique, you should consider a unique constraint on that column.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
news:OyhEUeAUIHA.1204@.TK2MSFTNGP03.phx.gbl...
> Does identity column need to be indexed? For a very large table, if we do
> not need the identity column to do selection, and the only purpose of the
> identity column is be part of the primary key (clustered), do we need an
> extra index for the identity column only? We are using sql server 2005.
> Thanks,
> Lijun
>|||If there is already a primary key on the identity, then you don't need
another index. The PK automatically creates one.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
news:OyhEUeAUIHA.1204@.TK2MSFTNGP03.phx.gbl...
Does identity column need to be indexed? For a very large table, if we do
not need the identity column to do selection, and the only purpose of the
identity column is be part of the primary key (clustered), do we need an
extra index for the identity column only? We are using sql server 2005.
Thanks,
Lijun|||The identity mechanism itself does not require an index. The column will
"know" what value to assign next.
So if there is no other reason to create the index (used in WHERE
clause, used in JOINs, etc.), then it is best to not waste an index on
the identity column.
--
Gert-Jan
Lijun Zhang wrote:
> Does identity column need to be indexed? For a very large table, if we do
> not need the identity column to do selection, and the only purpose of the
> identity column is be part of the primary key (clustered), do we need an
> extra index for the identity column only? We are using sql server 2005.
> Thanks,
> Lijun

identity and clustered indexes

If we have a monotonically increase value( like an identity column), then
which is better: clustered or non-clustered index. The table will be a heavy
insert table.
thanks.
There is no silver bullet answer. If you use non-clustered, do you have a
good candidate for an alternate clustered index? If so, does it increase in
the same order as the identity would (e.g. a datetime column)? How will the
data be queried? What is your definition of "heavy"?
http://www.aspfaq.com/
(Reverse address to reply.)
"SQLServer DBA" <sqlsdba@.bigfoot.com> wrote in message
news:2o1kp7F5hu8mU1@.uni-berlin.de...
> If we have a monotonically increase value( like an identity column), then
> which is better: clustered or non-clustered index. The table will be a
heavy
> insert table.
> thanks.
>
|||The table does have few other columns as SARG arguments which will be non-clustered
index. There is no surrogate primary key. Hence only identity column is used as a
primary key. The table is populated by values in the fields of a form entered
by users on the web, mainly for statistical purpose. We expect that it will be
getting around 50,000 inserts a day, in due course of time.
As I understand, having the identity column declared as clustered index will mean
that at insert time , the database engine has to insert the row at appropriate
page, which can include page splitting too. If it is a non-clustered index it
can just add pages at the end and then update the key value. Shouldn't that be
faster.
thanks again.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etdHxyIgEHA.644@.tk2msftngp13.phx.gbl...
> There is no silver bullet answer. If you use non-clustered, do you have a
> good candidate for an alternate clustered index? If so, does it increase in
> the same order as the identity would (e.g. a datetime column)? How will the
> data be queried? What is your definition of "heavy"?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "SQLServer DBA" <sqlsdba@.bigfoot.com> wrote in message
> news:2o1kp7F5hu8mU1@.uni-berlin.de...
> heavy
>
|||"SQLServer DBA" <sqlsdba@.bigfoot.com> wrote in message
news:2o1mebF5v0ppU1@.uni-berlin.de...
> The table does have few other columns as SARG arguments which will be
non-clustered
> index. There is no surrogate primary key. Hence only identity column is
used as a
> primary key. The table is populated by values in the fields of a form
entered
> by users on the web, mainly for statistical purpose. We expect that it
will be
> getting around 50,000 inserts a day, in due course of time.
> As I understand, having the identity column declared as clustered index
will mean
> that at insert time , the database engine has to insert the row at
appropriate
> page, which can include page splitting too. If it is a non-clustered index
it
> can just add pages at the end and then update the key value. Shouldn't
that be
> faster.
>
But with an identity column the inserted values are always at the end, so
the appropriate page is always the last page, and page splits shouldn't
happen. Moreover in the non-clustered index, the database has to write the
row and write the index entry, where in the clustered case the row and the
index are written onto the same page.
But the bottom line is that it can depend, and you should test.
Try this: The results come out strongly in favor of the clustered primary
key index:
David
create table test_clustered
(
id int identity primary key clustered,
name varchar(50) not null,
address1 varchar(50) not null,
address2 varchar(50) not null,
address3 varchar(50) not null
)
create index ix_test_clustered
on test_clustered(name)
create table test_nonclustered
(
id int identity primary key nonclustered,
name varchar(50) not null,
address1 varchar(50) not null,
address2 varchar(50) not null,
address3 varchar(50) not null
)
create index ix_test_nonclustered
on test_nonclustered(name)
go
set nocount on
begin transaction
declare @.i int
declare @.begin datetime
declare @.msg varchar(255)
set @.i = 0
set @.begin = getDate()
set @.msg = 'begin nonclustered '
print @.msg
while @.i < 50000
begin
insert into test_nonclustered
(name, address1, address2, address3)
values
(char(97+@.i%26) + 'name', 'address1', 'address2', 'address3')
set @.i = @.i + 1
end
set @.msg = 'end nonclustered elapsed ' + str(datediff(ms,@.begin,getDate()))
print @.msg
set @.begin = getdate()
set @.msg = 'begin clustered '
print @.msg
set @.i = 0
while @.i < 50000
begin
insert into test_clustered
(name, address1, address2, address3)
values
(char(97+@.i%26) + 'name', 'address1', 'address2', 'address3')
set @.i = @.i + 1
end
set @.msg = 'end clustered elapsed ' + str(datediff(ms,@.begin,getDate()))
print @.msg
commit transaction
go
truncate table test_clustered
truncate table test_nonclustered
|||Non-Clustered indexes are implemented behind the scenes as tables with
Clustered indexes on them. So you always run the risk of page splits
whether it is a Ci or NCI. The big difference is that NCI's do not tag
along the rest of each row, just the column(s) in the index expression.
50,000 inserts a day is really not that much and I wouldn't be as concerned
with page splits since you can control that somewhat with a proper fill
factor. If you don't do range type queries on the table a clustered index
on the Identity column is usually a good choice.
Andrew J. Kelly SQL MVP
"SQLServer DBA" <sqlsdba@.bigfoot.com> wrote in message
news:2o1mebF5v0ppU1@.uni-berlin.de...
> The table does have few other columns as SARG arguments which will be
non-clustered
> index. There is no surrogate primary key. Hence only identity column is
used as a
> primary key. The table is populated by values in the fields of a form
entered
> by users on the web, mainly for statistical purpose. We expect that it
will be
> getting around 50,000 inserts a day, in due course of time.
> As I understand, having the identity column declared as clustered index
will mean
> that at insert time , the database engine has to insert the row at
appropriate
> page, which can include page splitting too. If it is a non-clustered index
it
> can just add pages at the end and then update the key value. Shouldn't
that be[vbcol=seagreen]
> faster.
> thanks again.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:etdHxyIgEHA.644@.tk2msftngp13.phx.gbl...
a[vbcol=seagreen]
increase in[vbcol=seagreen]
the[vbcol=seagreen]
then
>

identity and clustered indexes

If we have a monotonically increase value( like an identity column), then
which is better: clustered or non-clustered index. The table will be a heavy
insert table.
thanks.There is no silver bullet answer. If you use non-clustered, do you have a
good candidate for an alternate clustered index? If so, does it increase in
the same order as the identity would (e.g. a datetime column)? How will the
data be queried? What is your definition of "heavy"?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"SQLServer DBA" <sqlsdba@.bigfoot.com> wrote in message
news:2o1kp7F5hu8mU1@.uni-berlin.de...
> If we have a monotonically increase value( like an identity column), then
> which is better: clustered or non-clustered index. The table will be a
heavy
> insert table.
> thanks.
>|||The table does have few other columns as SARG arguments which will be non-clustered
index. There is no surrogate primary key. Hence only identity column is used as a
primary key. The table is populated by values in the fields of a form entered
by users on the web, mainly for statistical purpose. We expect that it will be
getting around 50,000 inserts a day, in due course of time.
As I understand, having the identity column declared as clustered index will mean
that at insert time , the database engine has to insert the row at appropriate
page, which can include page splitting too. If it is a non-clustered index it
can just add pages at the end and then update the key value. Shouldn't that be
faster.
thanks again.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etdHxyIgEHA.644@.tk2msftngp13.phx.gbl...
> There is no silver bullet answer. If you use non-clustered, do you have a
> good candidate for an alternate clustered index? If so, does it increase in
> the same order as the identity would (e.g. a datetime column)? How will the
> data be queried? What is your definition of "heavy"?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "SQLServer DBA" <sqlsdba@.bigfoot.com> wrote in message
> news:2o1kp7F5hu8mU1@.uni-berlin.de...
> > If we have a monotonically increase value( like an identity column), then
> > which is better: clustered or non-clustered index. The table will be a
> heavy
> > insert table.
> >
> > thanks.
> >
> >
>|||"SQLServer DBA" <sqlsdba@.bigfoot.com> wrote in message
news:2o1mebF5v0ppU1@.uni-berlin.de...
> The table does have few other columns as SARG arguments which will be
non-clustered
> index. There is no surrogate primary key. Hence only identity column is
used as a
> primary key. The table is populated by values in the fields of a form
entered
> by users on the web, mainly for statistical purpose. We expect that it
will be
> getting around 50,000 inserts a day, in due course of time.
> As I understand, having the identity column declared as clustered index
will mean
> that at insert time , the database engine has to insert the row at
appropriate
> page, which can include page splitting too. If it is a non-clustered index
it
> can just add pages at the end and then update the key value. Shouldn't
that be
> faster.
>
But with an identity column the inserted values are always at the end, so
the appropriate page is always the last page, and page splits shouldn't
happen. Moreover in the non-clustered index, the database has to write the
row and write the index entry, where in the clustered case the row and the
index are written onto the same page.
But the bottom line is that it can depend, and you should test.
Try this: The results come out strongly in favor of the clustered primary
key index:
David
create table test_clustered
(
id int identity primary key clustered,
name varchar(50) not null,
address1 varchar(50) not null,
address2 varchar(50) not null,
address3 varchar(50) not null
)
create index ix_test_clustered
on test_clustered(name)
create table test_nonclustered
(
id int identity primary key nonclustered,
name varchar(50) not null,
address1 varchar(50) not null,
address2 varchar(50) not null,
address3 varchar(50) not null
)
create index ix_test_nonclustered
on test_nonclustered(name)
go
set nocount on
begin transaction
declare @.i int
declare @.begin datetime
declare @.msg varchar(255)
set @.i = 0
set @.begin = getDate()
set @.msg = 'begin nonclustered '
print @.msg
while @.i < 50000
begin
insert into test_nonclustered
(name, address1, address2, address3)
values
(char(97+@.i%26) + 'name', 'address1', 'address2', 'address3')
set @.i = @.i + 1
end
set @.msg = 'end nonclustered elapsed ' + str(datediff(ms,@.begin,getDate()))
print @.msg
set @.begin = getdate()
set @.msg = 'begin clustered '
print @.msg
set @.i = 0
while @.i < 50000
begin
insert into test_clustered
(name, address1, address2, address3)
values
(char(97+@.i%26) + 'name', 'address1', 'address2', 'address3')
set @.i = @.i + 1
end
set @.msg = 'end clustered elapsed ' + str(datediff(ms,@.begin,getDate()))
print @.msg
commit transaction
go
truncate table test_clustered
truncate table test_nonclustered|||Non-Clustered indexes are implemented behind the scenes as tables with
Clustered indexes on them. So you always run the risk of page splits
whether it is a Ci or NCI. The big difference is that NCI's do not tag
along the rest of each row, just the column(s) in the index expression.
50,000 inserts a day is really not that much and I wouldn't be as concerned
with page splits since you can control that somewhat with a proper fill
factor. If you don't do range type queries on the table a clustered index
on the Identity column is usually a good choice.
--
Andrew J. Kelly SQL MVP
"SQLServer DBA" <sqlsdba@.bigfoot.com> wrote in message
news:2o1mebF5v0ppU1@.uni-berlin.de...
> The table does have few other columns as SARG arguments which will be
non-clustered
> index. There is no surrogate primary key. Hence only identity column is
used as a
> primary key. The table is populated by values in the fields of a form
entered
> by users on the web, mainly for statistical purpose. We expect that it
will be
> getting around 50,000 inserts a day, in due course of time.
> As I understand, having the identity column declared as clustered index
will mean
> that at insert time , the database engine has to insert the row at
appropriate
> page, which can include page splitting too. If it is a non-clustered index
it
> can just add pages at the end and then update the key value. Shouldn't
that be
> faster.
> thanks again.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:etdHxyIgEHA.644@.tk2msftngp13.phx.gbl...
> > There is no silver bullet answer. If you use non-clustered, do you have
a
> > good candidate for an alternate clustered index? If so, does it
increase in
> > the same order as the identity would (e.g. a datetime column)? How will
the
> > data be queried? What is your definition of "heavy"?
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "SQLServer DBA" <sqlsdba@.bigfoot.com> wrote in message
> > news:2o1kp7F5hu8mU1@.uni-berlin.de...
> > > If we have a monotonically increase value( like an identity column),
then
> > > which is better: clustered or non-clustered index. The table will be a
> > heavy
> > > insert table.
> > >
> > > thanks.
> > >
> > >
> >
> >
>