Identity/Seed Values

I have a question regarding using the Identity/Seed values. I have migrated
a number of tables form Access to SQL. In Access the identity/seed values
were called autonumbers.
The big problem with autonumbers in Access was that they could change at any
time on you, getting renumbered or sometimes even becoming negative values,
etc.
Now, in my application I have need for a unique number as the primary key AS
LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
formatted a special way, I do not need it to be sequential, etc. But I do
need it to stay the same after it has been entered.
So, my question is this: I know in Access i could not rely on the autonumber
to stay the same. Does the identity/seed value propoerties of a SQL table
have this same issue? Or can I plan on this value staying the same over
time?
Thanks
JoeIdentity values in SQL Server start at the seed value. By default it
increments by 1 and cannot be manually inserted or updated.
"Joe Williams" <joe@.anywhere.com> wrote in message
news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> I have a question regarding using the Identity/Seed values. I have
migrated
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at
any
> time on you, getting renumbered or sometimes even becoming negative
values,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the
autonumber
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>|||Hi
Once the Identity value is stored in a table, it reamains the same and does
not change.
Regards
Mike
"Joe Williams" wrote:

> I have a question regarding using the Identity/Seed values. I have migrate
d
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at a
ny
> time on you, getting renumbered or sometimes even becoming negative values
,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the autonumb
er
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>
>|||When you say that the autonumbers change in Access, do you mean that
previously inserted values change (with no intervention on the user or
programmer) or just that the values inserted do not always increment
sequentially?
"Joe Williams" <joe@.anywhere.com> wrote in message
news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> I have a question regarding using the Identity/Seed values. I have
migrated
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at
any
> time on you, getting renumbered or sometimes even becoming negative
values,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the
autonumber
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>|||> I have need for a unique number...
What you absolutely do need is a proper natural key in your data. IDENTITY
is not a substitute for this. Logically you should always be able to remove
IDENTITY and its referencing columns and replace it with another key in your
table or with another artificial key without changing its meaning. Don't
expose the IDENTITY key to users otherwise you build business processes on
something over which you don't have complete control in all cases - for
example, if you need to integrate data from multiple tables with IDENTITY
keys or in some replication scenarios.
Don't make assumptions about the sequence of IDENTITY values, its continuity
(there may be gaps) or even its uniqueness (except when it's defined with a
PK or unique constraint).
Having said all that, SQL Server doesn't change the IDENTITY value
automatically for any reason once it's assigned and you cannot change an
IDENTITY yourself except by deleting and then inserting a row (another reaso
n
why you shouldn't tie external meaning to an arbitrary IDENTITY value).
David Portas
SQL Server MVP
--|||you need see "SET IDENTITY_INSERT" en SQL libray
"JohnnyAppleseed" wrote:

> When you say that the autonumbers change in Access, do you mean that
> previously inserted values change (with no intervention on the user or
> programmer) or just that the values inserted do not always increment
> sequentially?
> "Joe Williams" <joe@.anywhere.com> wrote in message
> news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> migrated
> any
> values,
> AS
> autonumber
>
>

identity, indexes, datetime

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.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...
>|||> Recently an identity column was added, to improve performance. For start I'm wondering wou
ld this
> do anything at all for performance?
No. Well, it will increase row size a little so it will decrease performance
slightly.

> Presumably the table is sorted by this field in the absence of any other pk/index?

Nope. Identity is a logical construct, and will have no bearing on physical
structure.

> If so what benefit is this if the column is never used?
None, from a performance standpoint. From a logical standpoint, we get into
the discussion of
identifying rows, natural vs. surrogate keys, but that is another topic.

> Surely the best strategy would be to put a clustered index on the datetime
field and not bother
> with the identity?
That seems like the natural thing to do. However, another candidate is to cl
uster over the join
column(s). Do some testing what will give most gain. Whever you cluster on,
you probably want to
have non-clustered index(es) on the other(s).

> Is there much difference in time overhead adding a clustered, non-clustere
d or identity field to
> existing tables with many rows (this database is used by many client and t
he time taken for the
> schema update is a concern).
Yes. Creating a cl ix will physicall re-sort the table, during you will have
exclusive lock (unless
you are on 2005 and create using ONLINE option). Creating nc index will not
re-sort data, only build
the index during which you have shared lock (unless using ONLINE). Adding an
identity column will
proably mean SQL Server has to touch every row, end result can be similar to
creating a cl ix
(sometimes, this can be deferred, but I very very much doubt adding an ident
ity column can be
deferred, considering the identity value need to be populated).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"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/delete
s 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-clustere
d or identity field to
> existing tables with many rows (this database is used by many client and t
he time taken for the
> schema update is a concern).
> Thanks
> JB.|||It is possible that adding an identity might make index rows on other
indexes smaller (if any other indexes exist). I know the OP stated there
were no other indexes on this particular table, but for argument's sake,
consider what happens when a heap table with NCIXs has an integer identity
column added. The ROWIDs in the NCIXs are reduced in size from 8bytes to
4bytes, reducing the storage size of those NCIXs. For NCIXs with only a
column or two, this can be a substantial reduction in overall size of the
index..
Is this important? Probably not terribly much, but I just thought it's worth
pointing out that although adding an identity column does widen the width of
table row storage, it can actually have the reverse effect on non-clustered
indexes. If performance critical queries rely on those NCIXs, it can
sometimes be a useful technique.. Where the identity is a BigInt however,
there's obviously no such gain..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2340HOgCfHHA.3932@.TK2MSFTNGP02.phx.gbl...
> No. Well, it will increase row size a little so it will decrease
> performance slightly.
>
> Nope. Identity is a logical construct, and will have no bearing on
> physical structure.
>
> None, from a performance standpoint. From a logical standpoint, we get
> into the discussion of identifying rows, natural vs. surrogate keys, but
> that is another topic.
>
> That seems like the natural thing to do. However, another candidate is to
> cluster over the join column(s). Do some testing what will give most gain.
> Whever you cluster on, you probably want to have non-clustered index(es)
> on the other(s).
>
> Yes. Creating a cl ix will physicall re-sort the table, during you will
> have exclusive lock (unless you are on 2005 and create using ONLINE
> option). Creating nc index will not re-sort data, only build the index
> during which you have shared lock (unless using ONLINE). Adding an
> identity column will proably mean SQL Server has to touch every row, end
> result can be similar to creating a cl ix (sometimes, this can be
> deferred, but I very very much doubt adding an identity column can be
> deferred, considering the identity value need to be populated).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "jb" <b@.b.com> wrote in message
> news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...
>|||>> Yes. Creating a cl ix will physicall re-sort the table, during you will[vbcol=seagreen]
Tested this on 2.2 million records:
Clustered [on datetime field] - 5 mins
Non-clustered - 30 secs
Adding identity - ~1minsql

Identity values not replicated even though 'not for replication' i

How did you init you subscriber? With a backup of publisher?
I have seen that error when replicated transactions are waiting to be picked
up in tran log. To confirm- try to truncate your log at subscriber:
backup log 'dbname' with truncate_only
If cannot trunate because of trans pending repl - run the following on
subscriber
-- REMOVE TRANSACTION IN LOG TO ALLOW FOR TRUNCATE -
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0, @.reset = 1
That error will have you chasing your tail.
ChrisB MCDBA
MSSQLConsulting.com
"pshroads@.gmail.com" wrote:

> I need to use transactional replication to replicate from a SQL Server
> 2005 machine (publisher) to a SQL Server 2000 machine (subscriber).
> The data will be the same on both servers when I start the replication
> so I will not be initializing using an snapshot. The identity columns
> on both databases are currently not set to 'not for replication'. When
> I create the publication is see that the identity columns are
> automatically set to 'not for replication'. I manually change the
> identity columns on the subscriber to also be 'not for replication'.
> Then I start replication. However the identity columns are not
> replicated with the error:
> Cannot insert explicit value for identity column in table 'tab1' when
> IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number:
> 544)
> As a test I set up the replication between 2005 and 2000 and used a
> snapshot to initialize the subscription. In this case the identity
> values are replicated.
> Any idea why setting the 'not for replication' manually on the 2000
> subscriber doesn't work?
> Thanks!
>
In your sp_MSupdxxx stored procedure - comment out the update of your ident
column.
Chris
"pshroads@.gmail.com" wrote:

> Hi Chris,
> Thanks for your reply. Here's a little background:
> We are doing a side-by-side upgrade from SQL Server 2000 to 2005 and
> after the upgrade I want to be replicate from 2005 back to 2000 in
> case we need to roll back to 2000 after our site is live and data is
> changing. I am log shipping from 2000 to 2005. When we are ready to
> cut over I will stop log shipping and the databases will be identical
> on both 2000 and 2005. So that's why there is no initial sync at the
> subscriber.
> I tried your suggestion but it still didn't work. I even tried
> manually creating and populating a test table on both the publisher
> and subscriber and it sill doesn't work. It seems the only way that it
> will work is to initialize the subscriber with a snapshot which with a
> 600GB database seems like it will take a long time.
>

IDENTITY_INSERT Problem

Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
2.8) in that I cannot insert a specific value to an identity field. (lines
below with >>> are code lines. I am using Python, but the syntax should be
about the same as VBScript)

First, I create an ADO Connection and create my table.

>>> c = win32com.client.Dispatch('ADODB.Connection')

>>> dsn = 'DRIVER=SQL
Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
Access Components;SERVER=SERVER\INSTANCE;"'

>>> c.Open(dsn)

>>> sql = 'CREATE TABLE Table_Name ('

>>> sql += 'ID_Field INTEGER PRIMARY KEY IDENTITY(1,1), '

>>> sql += 'Field_2 nchar(50) NOT NULL, '

>>> sql += 'Field_3 FLOAT DEFAULT 0.0)'

>>> c.Execute(sql)

This works fine. Then, I attempt to allow insertion into the ID_Field.

>>> c.Execute("SET IDENTITY_INSERT Table_Name ON")

This seems to work in that it does not throw an error and gives a return
of -1. Then I open a Recordset

>>> r = win32com.client.Dispatch('ADODB.Recordset')

>>> r.Open('Table_Name', c, 2, 4)

Last, I am attempt to add a record to the recordset with an explicit ID,

>>> r.AddNew()

>>> r.Fields.Item('ID_Field').Value = 45

but this fails with the error of

"Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done."

Even worse, if I now try to set the identity field to allow inserts again,
Updating() causes an error that I must use an explicit value for ID_Field,
but if I try to give it one, it fails with the above error. I have to
destroy the recordset object at this point to get any further.

I am told that SET IDENTITY_INSERT only remains active for one statement and
thus must be combined with the insert, but I do not know how to do this.

There is a similar sounding bug w/ SQL 7
(http://support.microsoft.com/defaul...b;EN-US;253157), but there
is no indication that it affects newer versions of the DB. Does anyone have
any suggestions or ideas?

Thanks for any help,

-d"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in message news:<10gas90gh9n8j44@.corp.supernews.com>...
> Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
> 2.8) in that I cannot insert a specific value to an identity field. (lines
> below with >>> are code lines. I am using Python, but the syntax should be
> about the same as VBScript)

<snip
I haven't done much ADO programming, so I can't really say much about
the specific error, except to note that this KB article suggests
reviewing the connection string:

http://support.microsoft.com/defaul...kb;EN-US;269495

You might want to try connecting like this instead, to see if it makes
a difference:

c.Provider = 'sqloledb'
dsn = 'Server=MyServer;Database=MyDB;Trusted_Connection= Yes'
c.Open(dsn)

Apart from that, SET IDENTITY_INSERT remains on for your session until
you turn it off, and it can only be on for one table at a time. So I'm
not sure what you mean by putting it together with the INSERT.

One option to consider is to encapsulate your INSERT in a stored
procedure, then call the stored procedure rather than updating the
recordset directly. I don't know how well this fits with what you're
trying to do, but using stored procedures is good practice anyway:

create proc dbo.MyProc
@.ID_Field int,
@.Field_2 nchar(50),
@.Field_3 float
as
set nocount on
begin
set identity_insert dbo.Table_Name on
insert into dbo.Table_Name
(ID_Field, Field_2, Field_3)
values (@.ID_Field, @.Field_2, @.Field_3)
set identity_insert dbo.Table_Name off
end

Simon

Identity_Insert OFF

I try to insert values to a field (which is a bigint identity(1 ,1) primary key) and i take message that Identity_Insert is OFF
What i should do ?
thank you
This is sample code pasted from SQL Books Online from the SET
IDENTITY_INSERT property:
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO
-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO
You can find the answers to almost every question about syntax in BOL.
HTH,
Mary
On Wed, 14 Apr 2004 23:06:03 -0700, George
<anonymous@.discussions.microsoft.com> wrote:

>I try to insert values to a field (which is a bigint identity(1 ,1) primary key) and i take message that Identity_Insert is OFF
>What i should do ?
>thank you

IDENTITY_INSERT

dear jeff johnson ,
thanks for ur response.
I have one table name fixtures in client machine .In that table one column
is identity type .now i am insert one Row from server database to that clien
t
database
now i have to on the identity_insert in client machine from server machine
give some suggestion.
sangeetha-server machine
sankar--client machine
i am running this query from my machine my machine name is sangeeta
EXEC master.dbo.xp_cmdshell 'osql -U scoremate -P scoremate -S sankar -Q
"set identity_insert scoremate.dbo.fixtures on "'
insert into openrowset('MSDASQL','DRIVER={SQL
Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
'select * from scoremate.dbo.fixtures')
select compcode,seasonid,matchid,matchdt,
time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
ground,umpires,genuser,
gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
openrowset('MSDASQL','DRIVER={SQL
Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
'select compcode,seasonid,matchid=15,matchdt,
time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
ground,umpires,genuser,
gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
scoremate.dbo.fixtures where matchid=13')Hi
Everything seems to point to the client machine?
You may be better off connecting to the server where you are doing the
inserts and not using your OPENROWSET as the destination of the insert.
John
"MOHAMED NASEER" wrote:

> dear jeff johnson ,
> thanks for ur response.
> I have one table name fixtures in client machine .In that table one colum
n
> is identity type .now i am insert one Row from server database to that cli
ent
> database
> now i have to on the identity_insert in client machine from server machine
> give some suggestion.
> sangeetha-server machine
> sankar--client machine
> i am running this query from my machine my machine name is sangeeta
> EXEC master.dbo.xp_cmdshell 'osql -U scoremate -P scoremate -S sankar -Q
> "set identity_insert scoremate.dbo.fixtures on "'
> insert into openrowset('MSDASQL','DRIVER={SQL
> Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
> 'select * from scoremate.dbo.fixtures')
> select compcode,seasonid,matchid,matchdt,
> time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
> ground,umpires,genuser,
> gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
> openrowset('MSDASQL','DRIVER={SQL
> Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
> 'select compcode,seasonid,matchid=15,matchdt,
> time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
> ground,umpires,genuser,
> gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
> scoremate.dbo.fixtures where matchid=13')sql

Identity/Seed Values

I have a question regarding using the Identity/Seed values. I have migrated
a number of tables form Access to SQL. In Access the identity/seed values
were called autonumbers.
The big problem with autonumbers in Access was that they could change at any
time on you, getting renumbered or sometimes even becoming negative values,
etc.
Now, in my application I have need for a unique number as the primary key AS
LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
formatted a special way, I do not need it to be sequential, etc. But I do
need it to stay the same after it has been entered.
So, my question is this: I know in Access i could not rely on the autonumber
to stay the same. Does the identity/seed value propoerties of a SQL table
have this same issue? Or can I plan on this value staying the same over
time?
Thanks
JoeIdentity values in SQL Server start at the seed value. By default it
increments by 1 and cannot be manually inserted or updated.
"Joe Williams" <joe@.anywhere.com> wrote in message
news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> I have a question regarding using the Identity/Seed values. I have
migrated
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at
any
> time on you, getting renumbered or sometimes even becoming negative
values,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the
autonumber
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>|||Hi
Once the Identity value is stored in a table, it reamains the same and does
not change.
Regards
Mike
"Joe Williams" wrote:

> I have a question regarding using the Identity/Seed values. I have migrate
d
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at a
ny
> time on you, getting renumbered or sometimes even becoming negative values
,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the autonumb
er
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>
>|||When you say that the autonumbers change in Access, do you mean that
previously inserted values change (with no intervention on the user or
programmer) or just that the values inserted do not always increment
sequentially?
"Joe Williams" <joe@.anywhere.com> wrote in message
news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> I have a question regarding using the Identity/Seed values. I have
migrated
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at
any
> time on you, getting renumbered or sometimes even becoming negative
values,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the
autonumber
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>|||> I have need for a unique number...
What you absolutely do need is a proper natural key in your data. IDENTITY
is not a substitute for this. Logically you should always be able to remove
IDENTITY and its referencing columns and replace it with another key in your
table or with another artificial key without changing its meaning. Don't
expose the IDENTITY key to users otherwise you build business processes on
something over which you don't have complete control in all cases - for
example, if you need to integrate data from multiple tables with IDENTITY
keys or in some replication scenarios.
Don't make assumptions about the sequence of IDENTITY values, its continuity
(there may be gaps) or even its uniqueness (except when it's defined with a
PK or unique constraint).
Having said all that, SQL Server doesn't change the IDENTITY value
automatically for any reason once it's assigned and you cannot change an
IDENTITY yourself except by deleting and then inserting a row (another reaso
n
why you shouldn't tie external meaning to an arbitrary IDENTITY value).
David Portas
SQL Server MVP
--|||you need see "SET IDENTITY_INSERT" en SQL libray
"JohnnyAppleseed" wrote:

> When you say that the autonumbers change in Access, do you mean that
> previously inserted values change (with no intervention on the user or
> programmer) or just that the values inserted do not always increment
> sequentially?
> "Joe Williams" <joe@.anywhere.com> wrote in message
> news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> migrated
> any
> values,
> AS
> autonumber
>
>

Identity...I need to get the last (or highest number in Identity column)...

Ok,
I just need to know how to get the last record inserted by the highest
IDENTITY number. Even if the computer was rebooted and it was two
weeks ago. (Does not have to do with the session).
Any help is appreciated.
Thanks,
TrintSELECT IDENT_CURRENT('table_name') ;

--
David Portas
SQL Server MVP
--

"trint" <trinity.smith@.gmail.com> wrote in message
news:1127164340.720014.70160@.g14g2000cwa.googlegro ups.com...
> Ok,
> I just need to know how to get the last record inserted by the highest
> IDENTITY number. Even if the computer was rebooted and it was two
> weeks ago. (Does not have to do with the session).
> Any help is appreciated.
> Thanks,
> Trint|||Thank you for your quick response, David.
Trint

..Net programmer
trinity.smith@.gmail.com

*** Sent via Developersdex http://www.developersdex.com ***

identity, plus pk?

We have a table that has an identity field along with 5 other domain
fields. The identity field is not declared as a primary key. The
table has 3.5 million records.

A consultant was hired recently to provide insight. His major
recommendation: modify the table to make the identity field a primary
key (i.e., alter table add constraint...)

Is that sound advice? Is it OK to have a table with identity but no
primary keys? What would be the impact on performance?Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files.

Actually, IDENTITY cannot be a relational key by definition. I would
drop that column and construct a proper key from the other columns. I
am willing to bet that you will fidn that you have a lot of invalid and
redudant data in this "non-table".|||>From a strict performance perspective, the presence or absence of keys
has no real effect, especially given the fact that you've managed to
collect 3.5 million records of data without relational constraints.

Your consultant probably meant to encourage you to build a unique
clustered index, which is built by default when you add a primary key
constraint. However, they are not the same; a clustered unique index
can exist without a primary key, and a primary key need not be
clustered (it must, however, be unique). Check the Books OnLine for
clustered indexes, or visit www.sql-server-performance.com for more
help with indexes.

Stu|||(newtophp2000@.yahoo.com) writes:
> We have a table that has an identity field along with 5 other domain
> fields. The identity field is not declared as a primary key. The
> table has 3.5 million records.
> A consultant was hired recently to provide insight. His major
> recommendation: modify the table to make the identity field a primary
> key (i.e., alter table add constraint...)
> Is that sound advice? Is it OK to have a table with identity but no
> primary keys? What would be the impact on performance?

If the table does have a primary key, defining one is a very good idea.
If the identity column is the only column that is unique in the table,
then there is not much choice.

It's difficult to say what the performance might be, since I don't know what
indexes there are on the table today. But if there are none at all, then
adding an index on the identity column is likely to improve things.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> If the table does have a primary key, defining one is a very good idea.
> If the identity column is the only column that is unique in the table,
> then there is not much choice.
> It's difficult to say what the performance might be, since I don't know what
> indexes there are on the table today. But if there are none at all, then
> adding an index on the identity column is likely to improve things.

Erland & Stu,

Thank you very much for your input; it was right on the money. The
table as it stands does not have any indexes and the identity field
provides the uniqueness criteria for us. The performance is quite
good. We will do some tests to see the impact of a primary
key/clustered index on overall performance before moving forward.

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, indexes, datetime

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.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 ...
>> 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.
>|||> Recently an identity column was added, to improve performance. For start I'm wondering would this
> do anything at all for performance?
No. Well, it will increase row size a little so it will decrease performance slightly.
> Presumably the table is sorted by this field in the absence of any other pk/index?
Nope. Identity is a logical construct, and will have no bearing on physical structure.
> If so what benefit is this if the column is never used?
None, from a performance standpoint. From a logical standpoint, we get into the discussion of
identifying rows, natural vs. surrogate keys, but that is another topic.
> Surely the best strategy would be to put a clustered index on the datetime field and not bother
> with the identity?
That seems like the natural thing to do. However, another candidate is to cluster over the join
column(s). Do some testing what will give most gain. Whever you cluster on, you probably want to
have non-clustered index(es) on the other(s).
> 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).
Yes. Creating a cl ix will physicall re-sort the table, during you will have exclusive lock (unless
you are on 2005 and create using ONLINE option). Creating nc index will not re-sort data, only build
the index during which you have shared lock (unless using ONLINE). Adding an identity column will
proably mean SQL Server has to touch every row, end result can be similar to creating a cl ix
(sometimes, this can be deferred, but I very very much doubt adding an identity column can be
deferred, considering the identity value need to be populated).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"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.|||It is possible that adding an identity might make index rows on other
indexes smaller (if any other indexes exist). I know the OP stated there
were no other indexes on this particular table, but for argument's sake,
consider what happens when a heap table with NCIXs has an integer identity
column added. The ROWIDs in the NCIXs are reduced in size from 8bytes to
4bytes, reducing the storage size of those NCIXs. For NCIXs with only a
column or two, this can be a substantial reduction in overall size of the
index..
Is this important? Probably not terribly much, but I just thought it's worth
pointing out that although adding an identity column does widen the width of
table row storage, it can actually have the reverse effect on non-clustered
indexes. If performance critical queries rely on those NCIXs, it can
sometimes be a useful technique.. Where the identity is a BigInt however,
there's obviously no such gain..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2340HOgCfHHA.3932@.TK2MSFTNGP02.phx.gbl...
>> Recently an identity column was added, to improve performance. For start
>> I'm wondering would this do anything at all for performance?
> No. Well, it will increase row size a little so it will decrease
> performance slightly.
>
>> Presumably the table is sorted by this field in the absence of any other
>> pk/index?
> Nope. Identity is a logical construct, and will have no bearing on
> physical structure.
>
>> If so what benefit is this if the column is never used?
> None, from a performance standpoint. From a logical standpoint, we get
> into the discussion of identifying rows, natural vs. surrogate keys, but
> that is another topic.
>
>> Surely the best strategy would be to put a clustered index on the
>> datetime field and not bother with the identity?
> That seems like the natural thing to do. However, another candidate is to
> cluster over the join column(s). Do some testing what will give most gain.
> Whever you cluster on, you probably want to have non-clustered index(es)
> on the other(s).
>
>> 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).
> Yes. Creating a cl ix will physicall re-sort the table, during you will
> have exclusive lock (unless you are on 2005 and create using ONLINE
> option). Creating nc index will not re-sort data, only build the index
> during which you have shared lock (unless using ONLINE). Adding an
> identity column will proably mean SQL Server has to touch every row, end
> result can be similar to creating a cl ix (sometimes, this can be
> deferred, but I very very much doubt adding an identity column can be
> deferred, considering the identity value need to be populated).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "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.
>|||>> Yes. Creating a cl ix will physicall re-sort the table, during you will
>> have exclusive lock (unless you are on 2005 and create using ONLINE
>> option). Creating nc index will not re-sort data, only build the index
>> during which you have shared lock (unless using ONLINE). Adding an
>> identity column will proably mean SQL Server has to touch every row, end
>> result can be similar to creating a cl ix (sometimes, this can be
>> deferred, but I very very much doubt adding an identity column can be
>> deferred, considering the identity value need to be populated).
Tested this on 2.2 million records:
Clustered [on datetime field] - 5 mins
Non-clustered - 30 secs
Adding identity - ~1min

IDENTITY with inline sql works different in 2005 than 2000

Hi All:
In SQL 2000, I used this SQL statement to assign an ID to a sorted recordset:
SELECT VPSystemID, ID,IDENTITY(int, 1, 1) AS SO INTO
arqdouj_20Aug07_104036_3843636
FROM (
SELECT TOP 100 PERCENT VPSystemID,ID FROM VPAPointUser_marqdouj ORDER BY
[VPM Distance],Joint,IndicationTypeID,Active DESC
) Data
The resulting inline recordset was sorted *BEFORE* the identity was applied.
It appears to me now, that in 2005 the identity is applied before the inline
sql recordset is sorted; at least that is the results I am seeing.
Should I expect the recordset to be sorted first? Is this a bug with 2005?
Is there some other method I should be using to achieve the results I want?
Thanks.
--
Doug.> Should I expect the recordset to be sorted first? Is this a bug with
> 2005?
In the opinion of a lot of people, the (undocumented) behavior in SQL Server
2000 was the bug. A lot of people complain that 2005 "broke" their code,
but in all honesty, they shouldn't have been relying on undocumented
behavior. Usually the example is
CREATE VIEW dbo.foo
AS
SELECT TOP 100 PERCENT ...
ORDER BY bar;
GO
SELECT * FROM dbo.foo; -- no ORDER BY!
> Is there some other method I should be using to achieve the results I
> want?
Well, you can generate the rank using ROW_NUMBER() OVER (ORDER BY ...)
however that does not make an IDENTITY column if that is what you're
expecting.
A|||Hi Aaron:
Thanks for the reply.
I did eventually find the row_number() function, and was able to get the
results I wanted. I didn't need an identity column, just a field that
contains the sort order.
Doug.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Should I expect the recordset to be sorted first? Is this a bug with
> > 2005?
> In the opinion of a lot of people, the (undocumented) behavior in SQL Server
> 2000 was the bug. A lot of people complain that 2005 "broke" their code,
> but in all honesty, they shouldn't have been relying on undocumented
> behavior. Usually the example is
> CREATE VIEW dbo.foo
> AS
> SELECT TOP 100 PERCENT ...
> ORDER BY bar;
> GO
> SELECT * FROM dbo.foo; -- no ORDER BY!
> > Is there some other method I should be using to achieve the results I
> > want?
> Well, you can generate the rank using ROW_NUMBER() OVER (ORDER BY ...)
> however that does not make an IDENTITY column if that is what you're
> expecting.
> A
>
>

Identity whithout identity column

Hi!
Please help!
Is there a function in SQL Server that returns "ROWID" from any table
(without identity column) or how to get that number?
Regards, MarkoThere is no internal rowid in SQL Server (such violated the relational model). If you say what you
would need it for, we can suggest alternatives.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Marko Kopaè" <marko.kopac@.mais.si> wrote in message
news:%23Gp%23PYngDHA.2484@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Please help!
> Is there a function in SQL Server that returns "ROWID" from any table
> (without identity column) or how to get that number?
> Regards, Marko
>

Identity vs. Identity(1,1)

Is there a difference in the resulting values for the identity fields if I
create a table and specify one of the following:
CREATE TABLE #Temp (TempID int identity, Description(100) )
CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
In other words, if (1,1) is not specified after declaring a field as identity,
is (1,1) the assumed default?
Message posted via http://www.droptable.com
Hi cbrichards
BOL says that "You must specify both the seed and increment or neither.
If neither is specified, the default is (1,1)." identity by itself
should be the same as identity(1,1)
When you insert a few records into each table and selected them back
out, what do you get?
CREATE TABLE #Temp (TempID int identity, Description varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
CREATE TABLE #Temp (TempID int identity(1,1), Description
varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
KenJ
cbrichards via droptable.com wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
|||Yes, if you are not specifying the value then it will be defaulted to (1,1)
Thanks
Hari
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6a0b564bac5a5@.uwe...
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as
> identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>
|||Hi
When specifying the identity property the seed and increment values are
optional, with a default of 1 for each. Therefore your two tables will be
equivalent. See http://msdn2.microsoft.com/en-us/library/ms186775.aspx for
more
John
"cbrichards via droptable.com" wrote:

> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>

Identity vs. Identity(1,1)

Is there a difference in the resulting values for the identity fields if I
create a table and specify one of the following:
CREATE TABLE #Temp (TempID int identity, Description(100) )
CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
In other words, if (1,1) is not specified after declaring a field as identit
y,
is (1,1) the assumed default?
Message posted via http://www.droptable.comHi cbrichards
BOL says that "You must specify both the seed and increment or neither.
If neither is specified, the default is (1,1)." identity by itself
should be the same as identity(1,1)
When you insert a few records into each table and selected them back
out, what do you get?
CREATE TABLE #Temp (TempID int identity, Description varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
CREATE TABLE #Temp (TempID int identity(1,1), Description
varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
KenJ
cbrichards via droptable.com wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as ident
ity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com|||Yes, if you are not specifying the value then it will be defaulted to (1,1)
Thanks
Hari
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6a0b564bac5a5@.uwe...
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as
> identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>|||Hi
When specifying the identity property the seed and increment values are
optional, with a default of 1 for each. Therefore your two tables will be
equivalent. See http://msdn2.microsoft.com/en-us/library/ms186775.aspx for
more
John
"cbrichards via droptable.com" wrote:

> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as ident
ity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>sql

Identity vs. Identity(1,1)

Is there a difference in the resulting values for the identity fields if I
create a table and specify one of the following:
CREATE TABLE #Temp (TempID int identity, Description(100) )
CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
In other words, if (1,1) is not specified after declaring a field as identity,
is (1,1) the assumed default?
--
Message posted via http://www.sqlmonster.comHi cbrichards
BOL says that "You must specify both the seed and increment or neither.
If neither is specified, the default is (1,1)." identity by itself
should be the same as identity(1,1)
When you insert a few records into each table and selected them back
out, what do you get?
CREATE TABLE #Temp (TempID int identity, Description varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
CREATE TABLE #Temp (TempID int identity(1,1), Description
varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
KenJ
cbrichards via SQLMonster.com wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.sqlmonster.com|||Yes, if you are not specifying the value then it will be defaulted to (1,1)
Thanks
Hari
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6a0b564bac5a5@.uwe...
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as
> identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.sqlmonster.com
>

Identity values not replicated even though 'not for replication' i

How did you init you subscriber? With a backup of publisher?
I have seen that error when replicated transactions are waiting to be picked
up in tran log. To confirm- try to truncate your log at subscriber:
backup log 'dbname' with truncate_only
If cannot trunate because of trans pending repl - run the following on
subscriber
-- REMOVE TRANSACTION IN LOG TO ALLOW FOR TRUNCATE -
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0, @.reset = 1
That error will have you chasing your tail.
ChrisB MCDBA
MSSQLConsulting.com
"pshroads@.gmail.com" wrote:

> I need to use transactional replication to replicate from a SQL Server
> 2005 machine (publisher) to a SQL Server 2000 machine (subscriber).
> The data will be the same on both servers when I start the replication
> so I will not be initializing using an snapshot. The identity columns
> on both databases are currently not set to 'not for replication'. When
> I create the publication is see that the identity columns are
> automatically set to 'not for replication'. I manually change the
> identity columns on the subscriber to also be 'not for replication'.
> Then I start replication. However the identity columns are not
> replicated with the error:
> Cannot insert explicit value for identity column in table 'tab1' when
> IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number:
> 544)
> As a test I set up the replication between 2005 and 2000 and used a
> snapshot to initialize the subscription. In this case the identity
> values are replicated.
> Any idea why setting the 'not for replication' manually on the 2000
> subscriber doesn't work?
> Thanks!
>
In your sp_MSupdxxx stored procedure - comment out the update of your ident
column.
Chris
"pshroads@.gmail.com" wrote:

> Hi Chris,
> Thanks for your reply. Here's a little background:
> We are doing a side-by-side upgrade from SQL Server 2000 to 2005 and
> after the upgrade I want to be replicate from 2005 back to 2000 in
> case we need to roll back to 2000 after our site is live and data is
> changing. I am log shipping from 2000 to 2005. When we are ready to
> cut over I will stop log shipping and the databases will be identical
> on both 2000 and 2005. So that's why there is no initial sync at the
> subscriber.
> I tried your suggestion but it still didn't work. I even tried
> manually creating and populating a test table on both the publisher
> and subscriber and it sill doesn't work. It seems the only way that it
> will work is to initialize the subscriber with a snapshot which with a
> 600GB database seems like it will take a long time.
>

Identity values is checnges by initial snapshot

Hi, it's me again
We have merge replication setup between three servers, a push to one server
and a pull to the other server. There are many tables which use identity
fields. We are not using identity ranges. All were created with the not for
replication option on the identity declaration. I noticed that after the
initially snapshot is applied, that the first identity value available at
the subscriber is not the seed value, but one greater than the largest value
in the snapshot for that table. To get all the subscribers to generate
identities independent of the publisher, I placed DBCC CheckIdent( table,
ReSeed, 1 ) statements for each table in the post snapshot script. The
system appears to hang trying to reseed the identity field. When I tried to
check the value in Query Analyzer with: DBCC CheckIdent( table, NoReSeed ),
the command ran for over fifteen minutes before I killed it. The identity
values are independent after the snapshot is applied, but can I, how can I
reseed the identity for each subscriber.
Tia,
Paul
Hello Paul,
I have similar implementation that I install at my customers. I have devised
some extensive tools to manage all the identity stuff and work well. One of
them is to create a job that executes the DBCC command. This may apply to
you.
Hope this helps,
Raj
|||> To get all the subscribers to generate
> identities independent of the publisher, I placed DBCC CheckIdent( table,
> ReSeed, 1 ) statements for each table in the post snapshot script.
I do not understand what you were trying to do.. What exactly you expect
from such an approach ? If you have merge replication, and you would change
(somehow) identity seed in table on the subscriber database to 1 (as you
have tried), then inserting a new value into this table would cause a
generation of value 1 in identity column. But that is nonsence, because you
might already have such value. It is absolutely normal that after applying
snapshot seed is changed to "max value + 1".
If you do not want to use SQL server identity range handling, then I would
suggest you to change identity increment to a number of databases involved
in merge replication - if you have publisher and two subscribers, identity
increment should be at least three. Then after aplying snapshot you should
change identity seed to following values :
on publisher = Max value + 1
on publisher = Max value + 2
on publisher = Max value + 3
Of course instead of "Max value" you could use any value bigger than "Max
value".
With such an approach all further inserts into those databases will have
identity values which do not intersect - so any collisions in this area are
almost impossible
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"PaulW" <MSNewsGroup@.Digi-Sol.com> wrote in message
news:Oebqgo%23GEHA.240@.TK2MSFTNGP12.phx.gbl...
> Hi, it's me again
>
> We have merge replication setup between three servers, a push to one
server
> and a pull to the other server. There are many tables which use identity
> fields. We are not using identity ranges. All were created with the not
for
> replication option on the identity declaration. I noticed that after the
> initially snapshot is applied, that the first identity value available at
> the subscriber is not the seed value, but one greater than the largest
value
> in the snapshot for that table. To get all the subscribers to generate
> identities independent of the publisher, I placed DBCC CheckIdent( table,
> ReSeed, 1 ) statements for each table in the post snapshot script. The
> system appears to hang trying to reseed the identity field. When I tried
to
> check the value in Query Analyzer with: DBCC CheckIdent( table,
NoReSeed ),
> the command ran for over fifteen minutes before I killed it. The identity
> values are independent after the snapshot is applied, but can I, how can I
> reseed the identity for each subscriber.
>
> Tia,
> Paul
>
>
|||Actually Kestutis, you will end up with repeated identities any way unless
you set ranges - which Paul specifically said that he is not doing. This
also makes sense if the identity is matched with some form of server or
location value to make up a unique value.
Ron L
"Kestutis Adomavicius" <kicker@.nospam-mail.lt> wrote in message
news:Ot6qAgLHEHA.3584@.TK2MSFTNGP09.phx.gbl...
table,
> I do not understand what you were trying to do.. What exactly you expect
> from such an approach ? If you have merge replication, and you would
change
> (somehow) identity seed in table on the subscriber database to 1 (as you
> have tried), then inserting a new value into this table would cause a
> generation of value 1 in identity column. But that is nonsence, because
you
> might already have such value. It is absolutely normal that after applying
> snapshot seed is changed to "max value + 1".
> If you do not want to use SQL server identity range handling, then I would
> suggest you to change identity increment to a number of databases involved
> in merge replication - if you have publisher and two subscribers, identity
> increment should be at least three. Then after aplying snapshot you should
> change identity seed to following values :
> on publisher = Max value + 1
> on publisher = Max value + 2
> on publisher = Max value + 3
> Of course instead of "Max value" you could use any value bigger than "Max
> value".
> With such an approach all further inserts into those databases will have
> identity values which do not intersect - so any collisions in this area
are
> almost impossible
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
> "PaulW" <MSNewsGroup@.Digi-Sol.com> wrote in message
> news:Oebqgo%23GEHA.240@.TK2MSFTNGP12.phx.gbl...
> server
> for
at
> value
table,
> to
> NoReSeed ),
identity
I
>
|||There is a way of avoiding repeated identities, by controlling the increment
rather than the seed:
a.. Machine 1 has a seed of 1 and an increment of 2 (odd, positive
numbers)
b.. Machine 2 has a seed of 2 and an increment of 2 (even, positive
numbers)
For 4 machines:
a.. Machine 1 has a seed of 1 and an increment of 2 (odd, positive
numbers)
b.. Machine 2 has a seed of 2 and an increment of 2 (even, positive
numbers)
c.. Machine 3 has a seed of -1 and an increment of -2 (odd, negative
numbers)
d.. Machine 4 has a seed of -2 and an increment of -2 (even, negative
numbers)
This is Michael Hotek's 'invention' -
http://www.mssqlserver.com/replicati..._identity.asp.
On the site above he has the algorithm for a larger number of machines. If
this is used, you can allocate a massive range and then effectively forget
about it.
Regards,
Paul Ibison
|||Paul
The fact here is that we want them repeated. We want Server1 to have
sequence numbers 1 - ..., Server2 to have sequence numvers 1 - ..., etc.
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23DhdlwMHEHA.3528@.TK2MSFTNGP09.phx.gbl...
> There is a way of avoiding repeated identities, by controlling the
increment
> rather than the seed:
> a.. Machine 1 has a seed of 1 and an increment of 2 (odd, positive
> numbers)
> b.. Machine 2 has a seed of 2 and an increment of 2 (even, positive
> numbers)
> For 4 machines:
> a.. Machine 1 has a seed of 1 and an increment of 2 (odd, positive
> numbers)
> b.. Machine 2 has a seed of 2 and an increment of 2 (even, positive
> numbers)
> c.. Machine 3 has a seed of -1 and an increment of -2 (odd, negative
> numbers)
> d.. Machine 4 has a seed of -2 and an increment of -2 (even, negative
> numbers)
> This is Michael Hotek's 'invention' -
> http://www.mssqlserver.com/replicati..._identity.asp.
> On the site above he has the algorithm for a larger number of machines. If
> this is used, you can allocate a massive range and then effectively forget
> about it.
> Regards,
> Paul Ibison
>
|||No, you probably missed the point.. Identity increment is set to a number of
databases participating in transaction and on every database identity
increment is set to a number increased by 1. So identities on different
databases would be following:
Participating databases = Identity increment = 3
DB1 (publisher) - 1, 4, 7, 10, ...
DB2 (subscriber) - 2, 5, 8, 11, ...
DB3 (subscriber) - 4, 6, 9, 12, ...
If for some reason such approach is not suitable for you it does not mean
that it will not work, because it WORKS
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Ron Lounsbury" <rlounsbury@.bogusAddress.com> wrote in message
news:eoTXGFMHEHA.2432@.TK2MSFTNGP11.phx.gbl...
> Actually Kestutis, you will end up with repeated identities any way unless
> you set ranges - which Paul specifically said that he is not doing. This
> also makes sense if the identity is matched with some form of server or
> location value to make up a unique value.
> Ron L
>
> "Kestutis Adomavicius" <kicker@.nospam-mail.lt> wrote in message
> news:Ot6qAgLHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> table,
> change
> you
applying
would
involved
identity
should
"Max
> are
identity
not
the
> at
> table,
tried
> identity
can
> I
>
|||What is the point of having identity column (usually primary keys) values
repeated ..?
If this column is not a primary key, then I think you will have to create
not identity column and increment it manually (on insert trigger) on every
database..
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Ron Lounsbury" <rlounsbury@.bogusAddress.com> wrote in message
news:eSX$rTNHEHA.3564@.TK2MSFTNGP09.phx.gbl...
> Paul
> The fact here is that we want them repeated. We want Server1 to have
> sequence numbers 1 - ..., Server2 to have sequence numvers 1 - ..., etc.
> Ron L
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:%23DhdlwMHEHA.3528@.TK2MSFTNGP09.phx.gbl...
> increment
If
forget
>
|||Statement "Identity increment is set to a number of databases participating
in transaction and on every database identity increment is set to a number
increased by 1." should be "Identity increment is set to a number of
databases participating in transaction and on every database identity seed
is set to a number increased by 1."
Sorry for mistype
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Kestutis Adomavicius" <kicker@.nospam-mail.lt> wrote in message
news:eYHzmyTHEHA.1944@.TK2MSFTNGP11.phx.gbl...
> No, you probably missed the point.. Identity increment is set to a number
of
> databases participating in transaction and on every database identity
> increment is set to a number increased by 1. So identities on different
> databases would be following:
> Participating databases = Identity increment = 3
> DB1 (publisher) - 1, 4, 7, 10, ...
> DB2 (subscriber) - 2, 5, 8, 11, ...
> DB3 (subscriber) - 4, 6, 9, 12, ...
> If for some reason such approach is not suitable for you it does not mean
> that it will not work, because it WORKS
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
|||I have discovered what was happening. There is a bug in SQL Server where a
process "appears" to be non-yielding on scheduler. This is why the post
snapshot script hung. The same script works on a different machine. There is
a fix for the problem, but you have to call Microsoft, an incident. If they
decide it is the bug, they have a fix and credit you the incident. This is
the second hot fix I I needed since installing SP3A.
Thanks,
Paul
"Raj Moloye" <rkmoloye@.hotmail.com> wrote in message
news:eSieV1AHEHA.1432@.TK2MSFTNGP12.phx.gbl...
> Hello Paul,
> I have similar implementation that I install at my customers. I have
devised
> some extensive tools to manage all the identity stuff and work well. One
of
> them is to create a job that executes the DBCC command. This may apply to
> you.
> Hope this helps,
> Raj
>

IDENTITY values in a stored procedure

Hi All,
This is my stored procedure

CREATE PROCEDURE testProc AS
BEGIN
CREATE TABLE #tblTest(ID INT NOT NULL IDENTITY, Col1 INT)
INSERT INTO #tblTest(Col1)
SELECT colA FROM tableA ORDER BY colA

END

This is my simple procedure, I wanted to know whether the IDENTITY values created in #tblTest will always be consistent, I mean without losing any number in between. i.e. ID column will have values 1,2,3,4,5....
or is there any chance of ID column having values like 1,2, 4, 6,7,8...

Please reply...
qaAs long as you don't do any deletes from your temp table, your identity column should remain sequential with no gaps.|||Thanks for your quick response.sql

Identity Values

Hello,
I have the Following Problem:
I have some tables on a SQL Server database that have primary keys
without the identity property.
This was necessary for importing data from old databases...
Now I want to change some primary key columns to be an Identity.
Of crourse the identity seed should be set to a value higher than the
highest existing value in the column.
I can do that easily with the Enterprise Manager, but how can I do that
with transact sql statements?
Regards FerdinandMake the changes in the table designer of EM but don't save them yet. Then
look on the toolbar for the button that is 3rd from the left. It will show
you how EM makes the changes.
Andrew J. Kelly SQL MVP
"Ferdinand Zaubzer" <ferdl@.gmx.at> wrote in message
news:uXnBWaeHGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the Following Problem:
> I have some tables on a SQL Server database that have primary keys without
> the identity property.
> This was necessary for importing data from old databases...
> Now I want to change some primary key columns to be an Identity.
> Of crourse the identity seed should be set to a value higher than the
> highest existing value in the column.
> I can do that easily with the Enterprise Manager, but how can I do that
> with transact sql statements?
> Regards Ferdinand|||Why would you destroy a perfectly good primary key by replacing it with an
identity column?
I know there are different schools of thought on this one, but I have always
found a logical primary key based on the actual values in the table to be
far more intuitive than an identity field, which is little more than an
artificial row number in my book. Having keys based on real values makes
joining to other tables far easier, even if slightly more typing and storage
is used in the process.
From a programming and maintainability perspective, I would stick with the
original primary keys.
Of course, this is just my opinion. Some folks consider an identity field
to be a requirement on every table. I avoid using them as a general rule.
"Ferdinand Zaubzer" <ferdl@.gmx.at> wrote in message
news:uXnBWaeHGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the Following Problem:
> I have some tables on a SQL Server database that have primary keys
> without the identity property.
> This was necessary for importing data from old databases...
> Now I want to change some primary key columns to be an Identity.
> Of crourse the identity seed should be set to a value higher than the
> highest existing value in the column.
> I can do that easily with the Enterprise Manager, but how can I do that
> with transact sql statements?
> Regards Ferdinand