Identity column: February 2012

Friday, February 24, 2012

Identity column not returnd after AddNew / Update with ADO and SQL 2005

Hi,

I add new records to a table with ADO. The tables contain an auto-increment identity column. I want to retrieve the identity value after the insert operation. This works fine for SQL Server 2000. On SQL Server 2005 this only works if I use a table in the select statement. If I use a view in the select statement, ADO returns no value for the identity column, a trace with profiler shows that there is no Select @.@.IDENTITY statement.

What is the reason for this behavior?

How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?

Best regards,

George

Hi,

this is not the best solution but try:

me.RecordSource="SELECT * FROM table1"

instead of

me.RecordSource="SELECT * FROM query"

It works for me.

Best regards, Matjaz

|||

Well, of course this would work, but unfortunatly I relied on the SQL Server 2000 behavior a lot, so changing this would be a very much work.

From my point of view this clearly is a bug in SQL Server 2005 or the new OLE-DB provider. Or is there a new property in ADO so that the behavior is the same?

Anyone any ideas?

Best regards, George

|||

It would be great if you reported this at http://lab.msdn.microsoft.com/productfeedback. If you do, you are more likely to get the attention of the right people, and you might find out if this is a known bug, and if so, what the status is.

Thanks

|||

Sorry, because i have exactly the same problem, i didn't read your message completely ( I have read a hundreds of questions but no answers). We both came to the same conclusion.

George, I'd like to stay in touch with you. This is my e-mail: info@.finesa.si

Best regards, Matjaz

|||

Here is a small sample to reproduce the behavior:

It turns out, that the false behavior only occurs if a foreign key column is set. So I've included this in the snippet. I've also noticed, that the CursorLocation Property of the Connection has to be set to adUseServer for the SQL-Server 2005 to make this work, for SQL-Server 2000 it has to be adUseClient.

Private Sub InsertRecord(addlink As Boolean)
On Error GoTo fail
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

con.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestIt;Data Source=.")
rs.CursorLocation = adUseServer
rs.Open "SELECT * FROM TestView WHERE ID = 0", con, adOpenKeyset, adLockOptimistic, adCmdText
rs.AddNew
rs("Nr") = "1"
rs("Description") = "Hello Nr. 1"
If addlink = True Then rs("Test2_ID") = 1
rs.Update
MsgBox "ID of new record is " & CStr(rs("ID"))
rs.Close
con.Close
GoTo quit
fail:
MsgBox Err.Description
quit:
End Sub

Here comes the T-SQL code for creating the sample database:

use master
go
create database TestIt
go
USE [TestIt]
GO
CREATE TABLE [dbo].[Test2]
(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nr] [nchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
)
GO
CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nr] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Test2_ID] [int] NULL,
)
GO
ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [FK_Test_Test2] FOREIGN KEY([Test2_ID])
REFERENCES [dbo].[Test2] ([ID])
GO
ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_Test2]
go
CREATE VIEW [dbo].[TestView]
AS
SELECT dbo.Test.ID, dbo.Test.Nr, dbo.Test.Description, dbo.Test.Test2_ID, dbo.Test2.Nr AS Nr2, dbo.Test2.Description AS Desc2
FROM dbo.Test LEFT OUTER JOIN
dbo.Test2 ON dbo.Test.Test2_ID = dbo.Test2.ID

GO
insert into Test2(Nr, Description) VALUES('1','The Number 1')
go

Johnny

|||I have encountered this same problem too. Has anyone found a solution?

It's used in a complex bound form with an n-m relationship (3 records).

Needless to say changing this code to reference the tables directly is

a big change and lots of work.|||

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.

Thank you for any help

|||

OdilonS wrote:

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.
Thank you for any help
I found the source of the error.
Be in mind that in my case the routine was working very well until
the MDAC 2.5.
I'm not sure about the versions 2.6 and 2.7, however,
on Windows 98 SE and XP with MDAC 2.8 it stops to run (ADO change his behavior ).
The solution was just re-stablish the ActiveConnection before Open the
recordset again, like this .....

oRs.ActiveConnection = oCn
where oRs and oCn are respectivelly
ADODB.Recordset and ADODB.Connection, valids objects.

|||ADO 2.7, SQL Server 2005

Im having the EXACT same problem. We have not changed our code. When migrating from SQL Server 2000 to 2005 we immediately noticed the following database update pattern start to fail in a number of areas.

1. A new record is added to a recordset (The record contains an Identity
column)
2. The recordset is posted back to the database (RS.UpdateBatch)
3. A value is changed at the UI
4. The recordset is again posted back to the database
5. > Boom < The BatchUpdate fails - Source="Microsoft Cursor Engine"
Description="Row cannot be located for updating. Some values may have been
changed since it was last read.">

We have traced this down to a difference in the way identity column
information is returned to a recordset after new records are inserted into
the database. I examined this issue from 2 perspectives. 1, I ran SQL
profiler on both databases for the same transaction, and 2, I persisted my
recordset to XML before and after the first batch update (the one in which
the record is INSERTED) to look for differences between SQL Server 2000 and
2005. Here's what I found:

In the traces below, you'd notice a SELECT @.@.IDENTITY which occurs
immediately after the initial INSERT statement for SQL Server 2000 but not
for 2005. Why should this be missing? Presumably ADO itself issues this
SELECT statement. So why doesn't it do so with 2005?

SQL Server 2000 Profile Trace - This works!
-

INSERT INTO "FreedomDemo".."PROCEDURES"
("PatientID",
"ProcedureTypeID",
"DateOfProcedureY",
"DateOfProcedureM",
"DateOfProcedureD",
"ExternalOrigin",
"OverrideExternalSource",
"RecAuthor",
"RecStamp",
"RecModType")
VALUES (8.803915900000000e+007, 47, 2000, NULL, NULL, 0, 1, 1, 'Aug 8 2006
3:10PM', 'I')

SELECT @.@.IDENTITY

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

WHERE "PatientProcedureID"=54 <<-- Good! ADO has gotten this value
into its recordset.
AND "DateOfProcedureY"=2000
AND "DateOfProcedureM" IS NULL
AND "DateOfProcedureD" IS NULL
AND "OverrideExternalSource"=1

SQL Server 2005 Profile Trace - This FAILS!
-
INSERT INTO "FreedomDemo".."PROCEDURES"
("PatientID",
"ProcedureTypeID",
"DateOfProcedureY",
"DateOfProcedureM",
"DateOfProcedureD",
"ExternalOrigin",
"OverrideExternalSource",
"RecAuthor",
"RecStamp",
"RecModType")
VALUES (4.51692e+008, 47, 2000, NULL, NULL, 0, 1, 1, '2006-08-08
15:21:24:000','I')

<< Hmm, no SELECT @.@.IDENTITY occurs after the preceding INSERT >>

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

WHERE "PatientProcedureID"=0 <<-- Wrong! This value is not 0; As an
Identity column this is set to some value.
AND "DateOfProcedureY"=2000
AND "DateOfProcedureM" IS NULL
AND "DateOfProcedureD" IS NULL
AND "OverrideExternalSource"=1

Here are the relevant abstracts from the recordsets serialized as XML
(complete XML attached, if you are interested). In the following snippets
you'll notice the following. Before the INSERT statement, the identity
column is not represented in the <z:row>. After the INSERT it appears.
However, with SQL Server 2000 this column is populated with the correct
value, but with SQL Server 2005, this column shows a value of 0 - incorrect.

This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted.

Can anyone help with this? This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted. This is a significant deviation of functionality between SQL Server 2000 and SQL Server 2005. Anyone using RS.BatchUpdate and identity columns will be affected. This should account for a significant number of Microsoft's customers. Has this been fixed?

Thanks very much for your help!

- Joseph Geretz -

SQL Server 2000: Before and after RS.Update - This works!

Before update: PatientProcedureID is null, not represented in the <z:row>:

<z:row PatientID="451692044" ProcedureTypeID="47" DateOfProcedureY="2000"
ExternalOrigin="False" OverrideExternalSource="True" RecAuthor="1"
RecStamp="2006-08-08T15:55:12" RecModType="I" rs:forcenull="DateOfProcedureM
DateOfProcedureD"/>

After update: PatientProcedureID shows as 55 in the <z:row> GOOD!:

<z:row PatientProcedureID="55" PatientID="451692044" ProcedureTypeID="47"
DateOfProcedureY="2000" ExternalOrigin="False" OverrideExternalSource="True"
RecDeleted="False" RecAuthor="1" RecStamp="2006-08-08T15:55:12"
RecModType="I"/>

SQL Server 2005: Before and after RS.Update - This FAILS!
-

Before update: PatientProcedureID is null, not represented in the <z:row>:

<z:row PatientID="759886920" ProcedureTypeID="47" DateOfProcedureY="2000"
ExternalOrigin="False" OverrideExternalSource="True" RecAuthor="1"
RecStamp="2006-08-08T15:59:57" RecModType="I" rs:forcenull="DateOfProcedureM
DateOfProcedureD"/>

After update: PatientProcedureID shows as 0 in the <z:row> This is WRONG!!!:
-
<z:row PatientProcedureID="0" PatientID="759886920" ProcedureTypeID="47"
DateOfProcedureY="2000" ExternalOrigin="False" OverrideExternalSource="True"
RecDeleted="False" RecAuthor="1" RecStamp="2006-08-08T15:59:57"
RecModType="I"/>|||

OK, I've found the solution. Basically, with SQL Server 2005, you need to
explicitly set the Resync behavior, although with SQL Server 2000,
developers who didn't set this explicitly have been getting away with the
default behavior in many (most?) cases. Here's a sample which is working for
me on SQL Server 2005, and I've checked as well that this is backward
compatible with SQL Server 2000.
RS.Properties("Update Resync") = adResyncInserts + adResyncAutoIncrement
RS.Properties("Resync Command") = "SELECT * FROM VALLPATIENTSPROCEDURES

WHERE PATIENTPROCEDUREID IN

(SELECT @.@.IDENTITY FROM PROCEDURES)"
http://windowssdk.msdn.microsoft.com/en-us/library/ms676738.aspx
The *default* behavior for a recordset in which these properties are
unspecified has definitely changed from SQL Server 2000 to SQL Server 2005.
<grrr>Thanks Microsoft. It's always nice when the newer technology works
differently than the older technolgy did, and of course, if you can make it
more difficult for us as well, that's great too! :-\ </grrr>
Hope this helps someone else avoid the pain I just went through with this.
But I still think that MS should release a service pack to put this back to the way it used to be!

Nov 21, 2006:

FYI: There is now a Microsoft Hotfix for this issue: http://support.microsoft.com/kb/920974/en-us

I haven't tested this, but the lierature suggests that this will resolve the issue.

Thanks IgorB for bringing this to our attention (post on page 2 of this thread).

|||Sorry if I resume this old post.
I have the same exact problema, but I wonder if this is a documented change in behaviour or a bug of SQL 2005 that should work as SQL 2000 but doesn't....

I ask this because if there is a bug (as Zoya Bashirova - MSFT says) I will try to escalate to point some attention on it (hoping to get some attention)
If this is a documented "feature" I will make my programmers work on it following Joseph Geretz suggestions.

This is a very impacting thing on my environment...

Thanks to all!

|||

From my point of view this is a bug, although one could argue it is a "feature":

The same ADO code works on a table and fails on a view. For me this clearly is a bug, but we decided to change our code, because this was a relativly easy job.

If you decide to escalate this issue, please keep us informed.

Thanks,

George

|||As my developers says (according to Joseph Geretz findings), this has to do with the default behaviourof an ADO recordset keyset, where the default behaviour has changed from "autoresync" to something else...

I wonder if there is a way to specify on the server that this behaviour should change back to "autoresync": a server side parameter, a registry key or whatever...

The thing that seem really strange to me is that there is not a lot of documentation on this "issue".|||

Your developer is right, the default behavior changed. But the strange thing is, the behavior changed for views only. From my point of view this is not consistent. A view should behave exatly the same way as a table, which is not the case, so I call this a bug.

The easiest way to change this setting is via connection string, there is no way to change it on the server as far as I know.

It seems everyone is using ADO.NET these days, so they do not pay too much attention to guys like us developing with ADO 2.8

George

Identity column jumps indefinitely

Guys,

Iam new to this forum, Hello to all...
Iam facing a problem in my application. Have recently noticed that my primary key column which is an " identity " with increment 1 being set.
But now iam noticing a various jumps in the number instead of 1. The numbers in the jump is not consistent.
Has anyone faced this kinda problem.
???many people have seen this situation

those who are using identity columns only to provide identity (uniqueness) do not see a problem at all

those who are concerned about gaps in the sequence of numbers, as representing a problem for their application, should re-design their application so that they don't rely on identity columns|||I agree with your comments not to use the identity on the application.
But in my case, i dont delete the records, it automatically jumps the numbers.
say for example a record is created with number 301 today morning
during afternoon there is another new record with number 899.
But why this jump is happening. Iam curious to know about it...|||yes, i'm curious too

when it gets close to the 2-billion number, you may want to have a look at it again

:)|||This is old info, off the top of head, but as I remember it:

Basically, the Identity feature 'grabs' a block of numbers, and doles them out. Not sure what the default is. Assume 100 (1-100). When the first insert happens, it gives out '1'.
For performance, the server grabs them in bunches, and saves the next, (101), so it doesn't need to keep getting locks for each insert. If the db server goes down, the next record will get '101'.

Don't use identity for consecutive numbering.

Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers

IDENTITY Column Insert

Hi,
Version: SQL Server 2000 Standarad Edition.
----
Does SQL Server Insert value to the IDENTITY column in one action, or does
it INSERT other columns of the table and UPDATE the IDENTITY column as a
separate statement, internally?
If I issue an independent SELECT WITH NOLOCK on such a table, is there a
possibility of getting wrong value for the Identity column anytime?
Thanks,
payyansits atomic. no problems with duplicate.|||Hi
You will not get duplicates. When a row is inserted, the Identity value is
incremented. If that transaction rolls back, the identity value will not be
decremented and you will find 'holes' in the sequences. This is by design to
enable higher concurrency.
If you need an absolute guarantee of sequential numbers, you have to do it
through a trigger and thta implies holding locks that will hurt performance.
Issuing a SELECT with NOLCOK will result in uncommitted rows being returned.
In effect, you might be processing against rows that my not exist at a later
stage in your DB. Use with care.
Regards
Mike
"payyans" wrote:
> Hi,
> Version: SQL Server 2000 Standarad Edition.
> ----
> Does SQL Server Insert value to the IDENTITY column in one action, or does
> it INSERT other columns of the table and UPDATE the IDENTITY column as a
> separate statement, internally?
> If I issue an independent SELECT WITH NOLOCK on such a table, is there a
> possibility of getting wrong value for the Identity column anytime?
> Thanks,
> payyans

IDENTITY Column Insert

Hi,
Version: SQL Server 2000 Standarad Edition.
Does SQL Server Insert value to the IDENTITY column in one action, or does
it INSERT other columns of the table and UPDATE the IDENTITY column as a
separate statement, internally?
If I issue an independent SELECT WITH NOLOCK on such a table, is there a
possibility of getting wrong value for the Identity column anytime?
Thanks,
payyans
its atomic. no problems with duplicate.
|||Hi
You will not get duplicates. When a row is inserted, the Identity value is
incremented. If that transaction rolls back, the identity value will not be
decremented and you will find 'holes' in the sequences. This is by design to
enable higher concurrency.
If you need an absolute guarantee of sequential numbers, you have to do it
through a trigger and thta implies holding locks that will hurt performance.
Issuing a SELECT with NOLCOK will result in uncommitted rows being returned.
In effect, you might be processing against rows that my not exist at a later
stage in your DB. Use with care.
Regards
Mike
"payyans" wrote:

> Hi,
> Version: SQL Server 2000 Standarad Edition.
> ----
> Does SQL Server Insert value to the IDENTITY column in one action, or does
> it INSERT other columns of the table and UPDATE the IDENTITY column as a
> separate statement, internally?
> If I issue an independent SELECT WITH NOLOCK on such a table, is there a
> possibility of getting wrong value for the Identity column anytime?
> Thanks,
> payyans

IDENTITY Column Insert

Hi,
Version: SQL Server 2000 Standarad Edition.
----
Does SQL Server Insert value to the IDENTITY column in one action, or does
it INSERT other columns of the table and UPDATE the IDENTITY column as a
separate statement, internally?
If I issue an independent SELECT WITH NOLOCK on such a table, is there a
possibility of getting wrong value for the Identity column anytime?
Thanks,
payyansits atomic. no problems with duplicate.|||Hi
You will not get duplicates. When a row is inserted, the Identity value is
incremented. If that transaction rolls back, the identity value will not be
decremented and you will find 'holes' in the sequences. This is by design to
enable higher concurrency.
If you need an absolute guarantee of sequential numbers, you have to do it
through a trigger and thta implies holding locks that will hurt performance.
Issuing a SELECT with NOLCOK will result in uncommitted rows being returned.
In effect, you might be processing against rows that my not exist at a later
stage in your DB. Use with care.
Regards
Mike
"payyans" wrote:

> Hi,
> Version: SQL Server 2000 Standarad Edition.
> ----
> Does SQL Server Insert value to the IDENTITY column in one action, or does
> it INSERT other columns of the table and UPDATE the IDENTITY column as a
> separate statement, internally?
> If I issue an independent SELECT WITH NOLOCK on such a table, is there a
> possibility of getting wrong value for the Identity column anytime?
> Thanks,
> payyans

Identity Column Increment Control

Hi,
I have a table with identity column. I set the increment
to 1. But after I have deleted a couple of rows then
inserted a new row, the increment is not based on the
existing row number. For example, I had 100 rows already.
After I deleted two rows from the bottom., the last row I
have is 98. Then if I insert another row, it starts from
101 instead of 99. How can I solve this problem.
Thanks,
Derek
This is expected. The next identity value will not be in sequence, you will
see gaps in the identity values. The increment is not based on the existing
row number, however it will be the next of last generated identity value for
the table.
You can use "dbcc checkident" to reset the identity value of the table.
ex:
create table tt(i int not null identity, ii varchar(6000))
go
insert into tt (ii) values('x')
insert into tt (ii) values('y')
go
delete from tt where i = 2
go
DBCC CHECKIDENT (tt, RESEED, 1)
GO
insert into tt (ii) values('z')
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||>> The increment is not based on the existing
row number, <<
I mean to say, it is not based on the last value of the identity column.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Identity Column Increment Control

Hi,
I have a table with identity column. I set the increment
to 1. But after I have deleted a couple of rows then
inserted a new row, the increment is not based on the
existing row number. For example, I had 100 rows already.
After I deleted two rows from the bottom., the last row I
have is 98. Then if I insert another row, it starts from
101 instead of 99. How can I solve this problem.
Thanks,
DerekThis is expected. The next identity value will not be in sequence, you will
see gaps in the identity values. The increment is not based on the existing
row number, however it will be the next of last generated identity value for
the table.
You can use "dbcc checkident" to reset the identity value of the table.
ex:
create table tt(i int not null identity, ii varchar(6000))
go
insert into tt (ii) values('x')
insert into tt (ii) values('y')
go
delete from tt where i = 2
go
DBCC CHECKIDENT (tt, RESEED, 1)
GO
insert into tt (ii) values('z')
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||>> The increment is not based on the existing
row number, <<
I mean to say, it is not based on the last value of the identity column.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Identity column increases abruptly, abnormally.

Can you verify the application/code which is used inside tge trigger and
confirm whether you have too many rollbacks happening.
This is the only thing I can think off now.
This sort of issue might also happen if the server does not goes off through
a graceful shutdown.
Thanks
Hari
SQL Server MVP
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another
> Table2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Also check whether there is replication involved with range based identity.
May be one of the subscribers has an identity range starting from 500 +...
"Sameer" wrote:

> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another Tabl
e2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Thanks Hari for ur prompt reply.
ya there r some possibilities of rollbacks.
i really appreciate ur guess.
Can u please explain what happens to a identity column & its values when
rollback comes into picture?
Please help .. I may be able to solve the problem by ur valuable feedback.
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hari Prasad" wrote:

> Hi,
> Some thing new

> Can you verify the application/code which is used inside tge trigger and
> confirm whether you have too many rollbacks happening.
> This is the only thing I can think off now.
> This sort of issue might also happen if the server does not goes off throu
gh
> a graceful shutdown.
> Thanks
> Hari
> SQL Server MVP
> "Sameer" <Sameer@.discussions.microsoft.com> wrote in message
> news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
>
>|||On Fri, 25 Aug 2006 03:19:01 -0700, Sameer wrote:

>Hi
>I am facing a big problem. please help.
>We r having SQL Server 2000 Enterprise.
>I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
>delete.
>These triggers are inserting the audit for these actions into another Table
2.
>Table2 has an identity column which increment by 1 automatically.
>Problem:
>Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
>As identity column increases automatically, I am helpless.
>Is there any patch/service pack for this.
Hi Sameer,
It might help if you posted some code.
Hugo Kornelis, SQL Server MVP|||On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:

>Thanks Hari for ur prompt reply.
>ya there r some possibilities of rollbacks.
>i really appreciate ur guess.
>Can u please explain what happens to a identity column & its values when
>rollback comes into picture?
Hi Sameer,
Since the identity value generation is performed outside of the
transaction context, the values generated for the inserts that were
rolled back are not re-used. See the repro below.
CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
OtherCol int NOT NULL CHECK (OtherCol > 0));
-- First row is inserted
INSERT INTO Demo (OtherCol) VALUES (1);
-- Second row explicitly rolled back
BEGIN TRAN;
INSERT INTO Demo (OtherCol) VALUES (2);
ROLLBACK TRAN;
-- Third row implicitly rolled back because constraint is violated
INSERT INTO Demo (OtherCol) VALUES (-3);
-- Fourth row is inserted
INSERT INTO Demo (OtherCol) VALUES (4);
-- Check results
SELECT * FROM Demo;
DROP TABLE Demo;
Hugo Kornelis, SQL Server MVP|||How can i solve this identity increase problem ... as identity is not in
context of transaction.
Please suggest a solution to rollback identity with transaction.
i'll be grateful to u.
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hugo Kornelis" wrote:

> On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
>
> Hi Sameer,
> Since the identity value generation is performed outside of the
> transaction context, the values generated for the inserts that were
> rolled back are not re-used. See the repro below.
> CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
> OtherCol int NOT NULL CHECK (OtherCol > 0));
> -- First row is inserted
> INSERT INTO Demo (OtherCol) VALUES (1);
> -- Second row explicitly rolled back
> BEGIN TRAN;
> INSERT INTO Demo (OtherCol) VALUES (2);
> ROLLBACK TRAN;
> -- Third row implicitly rolled back because constraint is violated
> INSERT INTO Demo (OtherCol) VALUES (-3);
> -- Fourth row is inserted
> INSERT INTO Demo (OtherCol) VALUES (4);
> -- Check results
> SELECT * FROM Demo;
> DROP TABLE Demo;
>
> --
> Hugo Kornelis, SQL Server MVP
>|||Basically, you can't keep an identify column from skipping values in the
face of rollbacks unless the transactions are serialized. You need to assign
your own number and then wait for the transaction that uses that number to
commit before assigning the next one. This will have lousy performance but
it's the only way that you can roll back a transaction without missing a
number. For example if you set the sequence number to max sequence number +
1, you need to wait until that insert commits before you can set the next
one because the next transaction needs the new number before it assigns its
number. If you assign the number outside of the transaction with an
identity column, rollbacks will lose the number because it is used and not
written to the table.
Bottom line, you can't guarantee sequential numbers without sequential
transactions so your choices are to make the transactions sequential or to
live with skipped numbers.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:D229D6C8-38C3-4CC5-A972-368670CB88EA@.microsoft.com...[vbcol=seagreen]
> How can i solve this identity increase problem ... as identity is not in
> context of transaction.
> Please suggest a solution to rollback identity with transaction.
> i'll be grateful to u.
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens Business Services
> Bracknell
> UK
>
> "Hugo Kornelis" wrote:
>|||Lines: 24
Organization: Posted via Supernews, http://www.supernews.com
X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:10634
On Sat, 26 Aug 2006 10:21:02 -0700, Sameer wrote:

>How can i solve this identity increase problem ... as identity is not in
>context of transaction.
>Please suggest a solution to rollback identity with transaction.
>i'll be grateful to u.
Hi Sameer,
IDENTITY should never be used if the actuall values matter. It is
intended to be used as a surrogate key, for internal use by the database
logic but not exposed to the user.
If you care aboout gaps in your IDENTITY sequence, then you shouldn't
use IDENTITY at all. Find the current maximum value in the table, add
one and use that as your new value. Use locks to ensure that two
connections executing the same coode at the same time won't get the same
maximum value, then try to insert new rows with the same new value. Your
concurrency will suffer, but you're rid of gaps - until you manuallly
delete a row, of course.
Hugo Kornelis, SQL Server MVP

Identity column increases abruptly, abnormally.

Hi
I am facing a big problem. please help.
We r having SQL Server 2000 Enterprise.
I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
delete.
These triggers are inserting the audit for these actions into another Table2.
Table2 has an identity column which increment by 1 automatically.
Problem:
Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
As identity column increases automatically, I am helpless.
Is there any patch/service pack for this.
Please Suggest.
--
Regards
Sameer Gupta
C# Designer & Developer
Siemens
Bracknell
UKHi,
Some thing new :)
Can you verify the application/code which is used inside tge trigger and
confirm whether you have too many rollbacks happening.
This is the only thing I can think off now.
This sort of issue might also happen if the server does not goes off through
a graceful shutdown.
Thanks
Hari
SQL Server MVP
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another
> Table2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Also check whether there is replication involved with range based identity.
May be one of the subscribers has an identity range starting from 500 +...
"Sameer" wrote:
> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another Table2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Thanks Hari for ur prompt reply.
ya there r some possibilities of rollbacks.
i really appreciate ur guess.
Can u please explain what happens to a identity column & its values when
rollback comes into picture?
Please help .. I may be able to solve the problem by ur valuable feedback.
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hari Prasad" wrote:
> Hi,
> Some thing new :)
> Can you verify the application/code which is used inside tge trigger and
> confirm whether you have too many rollbacks happening.
> This is the only thing I can think off now.
> This sort of issue might also happen if the server does not goes off through
> a graceful shutdown.
> Thanks
> Hari
> SQL Server MVP
> "Sameer" <Sameer@.discussions.microsoft.com> wrote in message
> news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
> > Hi
> >
> > I am facing a big problem. please help.
> > We r having SQL Server 2000 Enterprise.
> >
> > I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> > delete.
> > These triggers are inserting the audit for these actions into another
> > Table2.
> > Table2 has an identity column which increment by 1 automatically.
> >
> > Problem:
> > Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> > As identity column increases automatically, I am helpless.
> >
> > Is there any patch/service pack for this.
> >
> > Please Suggest.
> >
> >
> > --
> > Regards
> > Sameer Gupta
> > C# Designer & Developer
> > Siemens
> > Bracknell
> > UK
>
>|||On Fri, 25 Aug 2006 03:19:01 -0700, Sameer wrote:
>Hi
>I am facing a big problem. please help.
>We r having SQL Server 2000 Enterprise.
>I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
>delete.
>These triggers are inserting the audit for these actions into another Table2.
>Table2 has an identity column which increment by 1 automatically.
>Problem:
>Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
>As identity column increases automatically, I am helpless.
>Is there any patch/service pack for this.
Hi Sameer,
It might help if you posted some code.
--
Hugo Kornelis, SQL Server MVP|||On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
>Thanks Hari for ur prompt reply.
>ya there r some possibilities of rollbacks.
>i really appreciate ur guess.
>Can u please explain what happens to a identity column & its values when
>rollback comes into picture?
Hi Sameer,
Since the identity value generation is performed outside of the
transaction context, the values generated for the inserts that were
rolled back are not re-used. See the repro below.
CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
OtherCol int NOT NULL CHECK (OtherCol > 0));
-- First row is inserted
INSERT INTO Demo (OtherCol) VALUES (1);
-- Second row explicitly rolled back
BEGIN TRAN;
INSERT INTO Demo (OtherCol) VALUES (2);
ROLLBACK TRAN;
-- Third row implicitly rolled back because constraint is violated
INSERT INTO Demo (OtherCol) VALUES (-3);
-- Fourth row is inserted
INSERT INTO Demo (OtherCol) VALUES (4);
-- Check results
SELECT * FROM Demo;
DROP TABLE Demo;
Hugo Kornelis, SQL Server MVP|||How can i solve this identity increase problem ... as identity is not in
context of transaction.
Please suggest a solution to rollback identity with transaction.
i'll be grateful to u.
--
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hugo Kornelis" wrote:
> On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
> >Thanks Hari for ur prompt reply.
> >
> >ya there r some possibilities of rollbacks.
> >i really appreciate ur guess.
> >Can u please explain what happens to a identity column & its values when
> >rollback comes into picture?
> Hi Sameer,
> Since the identity value generation is performed outside of the
> transaction context, the values generated for the inserts that were
> rolled back are not re-used. See the repro below.
> CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
> OtherCol int NOT NULL CHECK (OtherCol > 0));
> -- First row is inserted
> INSERT INTO Demo (OtherCol) VALUES (1);
> -- Second row explicitly rolled back
> BEGIN TRAN;
> INSERT INTO Demo (OtherCol) VALUES (2);
> ROLLBACK TRAN;
> -- Third row implicitly rolled back because constraint is violated
> INSERT INTO Demo (OtherCol) VALUES (-3);
> -- Fourth row is inserted
> INSERT INTO Demo (OtherCol) VALUES (4);
> -- Check results
> SELECT * FROM Demo;
> DROP TABLE Demo;
>
> --
> Hugo Kornelis, SQL Server MVP
>|||Basically, you can't keep an identify column from skipping values in the
face of rollbacks unless the transactions are serialized. You need to assign
your own number and then wait for the transaction that uses that number to
commit before assigning the next one. This will have lousy performance but
it's the only way that you can roll back a transaction without missing a
number. For example if you set the sequence number to max sequence number +
1, you need to wait until that insert commits before you can set the next
one because the next transaction needs the new number before it assigns its
number. If you assign the number outside of the transaction with an
identity column, rollbacks will lose the number because it is used and not
written to the table.
Bottom line, you can't guarantee sequential numbers without sequential
transactions so your choices are to make the transactions sequential or to
live with skipped numbers.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:D229D6C8-38C3-4CC5-A972-368670CB88EA@.microsoft.com...
> How can i solve this identity increase problem ... as identity is not in
> context of transaction.
> Please suggest a solution to rollback identity with transaction.
> i'll be grateful to u.
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens Business Services
> Bracknell
> UK
>
> "Hugo Kornelis" wrote:
>> On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
>> >Thanks Hari for ur prompt reply.
>> >
>> >ya there r some possibilities of rollbacks.
>> >i really appreciate ur guess.
>> >Can u please explain what happens to a identity column & its values when
>> >rollback comes into picture?
>> Hi Sameer,
>> Since the identity value generation is performed outside of the
>> transaction context, the values generated for the inserts that were
>> rolled back are not re-used. See the repro below.
>> CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
>> OtherCol int NOT NULL CHECK (OtherCol > 0));
>> -- First row is inserted
>> INSERT INTO Demo (OtherCol) VALUES (1);
>> -- Second row explicitly rolled back
>> BEGIN TRAN;
>> INSERT INTO Demo (OtherCol) VALUES (2);
>> ROLLBACK TRAN;
>> -- Third row implicitly rolled back because constraint is violated
>> INSERT INTO Demo (OtherCol) VALUES (-3);
>> -- Fourth row is inserted
>> INSERT INTO Demo (OtherCol) VALUES (4);
>> -- Check results
>> SELECT * FROM Demo;
>> DROP TABLE Demo;
>>
>> --
>> Hugo Kornelis, SQL Server MVP|||On Sat, 26 Aug 2006 10:21:02 -0700, Sameer wrote:
>How can i solve this identity increase problem ... as identity is not in
>context of transaction.
>Please suggest a solution to rollback identity with transaction.
>i'll be grateful to u.
Hi Sameer,
IDENTITY should never be used if the actuall values matter. It is
intended to be used as a surrogate key, for internal use by the database
logic but not exposed to the user.
If you care aboout gaps in your IDENTITY sequence, then you shouldn't
use IDENTITY at all. Find the current maximum value in the table, add
one and use that as your new value. Use locks to ensure that two
connections executing the same coode at the same time won't get the same
maximum value, then try to insert new rows with the same new value. Your
concurrency will suffer, but you're rid of gaps - until you manuallly
delete a row, of course.
--
Hugo Kornelis, SQL Server MVP|||Thanks Mr. Hugo, Mr. Hari & Mr. Roger for ur kind suggestions.
I am fully able diagnose what's happening. I just come out of a potential
turn around for the issue.
Thanks very much. I am grateful for ur prompt replies.
--
Regards
Sameer Gupta
C# Designer & Developer
Siemens
Bracknell
UK

Identity column in temp table

Hi,
I am trying to create a temp table with an identity column. Here is the code that I am using...

SELECT UserId, IDENTITY(int, 1, 1) AS colId
INTO #User
FROM MyUserTable
WHERE UserId = 1
I am getting an error though.
Server: Msg 8108, Level 16, State 1, Line 9
Cannot add identity column, using the SELECT INTO statement, to table'#User', which already has column 'UserId' that inherits the identityproperty.
Is there any way to work around this?
Thanks for your help.
You'll have to explictly create the #User table with your 2 columns and then INSERT INTO it.

IDENTITY column in SQL 2000 and linked tables in MS Access

Please help

We have an application written in MS Access. The tables are linked to a SQL 2000 database.
The problem is that sometimes insert a new record in a table freezes and times out after a while without anything has happened.
When installing the application the *mdb file is copied over to the C drive and an ODBC connection is written to the registry.
The application is used by many in the company.
We have problems on tables defined with IDENTITY columns. Can this be our problem and how can we solve it?

Regards Anna-LenaIt all depends

How is the application written...

Do all of your controls have a data source property that is a table in SQL Server?

Or do you use unbound controls...

You might be better off posting here though

http://www.dbforums.com/forumdisplay.php?f=84

I doubt it's a problem with IDENTITY though|||The problem is that Access does not work will with large numbers of users. Anything above six to ten simultaneous users can cause problems.
I assume you have a form linked to a table or a view in your SQL Server database. Well, Access likes to copy down the ENTIRE recordset so that you can step through the results. When you have several people who each have loaded their own local copies of the same recordset and then try inserting a new record, I'd guess it plays havoc with the locking.
You might try having each user download a filtered subset of the data, reducing the number of copies of the same record that are spread over multiple terminals.|||The only way this works is to have all unbound controls, using rs.whatever and write code to fill in the controls, and to perform dml back to the database|||Which, in my opinion, takes away most of the advantages of using MS Access. So the application may as well be written on a more robust platform.|||Which, in my opinion, takes away most of the advantages of using MS Access. Apart from it being a one stop shop GUI\ report generator that can be distributed with xcopy you mean? A disconnected Access app is by no means the best solution in many situations but I would use one over a linked Access version any day**. And I would use Access over (for example) .NET in many circumstances too. In fact I do.

EDIT - ** Actually I got over excited and fibbed there. A disconnected app might be overdesigned for many applications (e.g. those that are accessed by a very small number of people).|||I use Access for rapid application development of apps with low user counts, and for that ADP files work fine. I never use linked tables, though.

Identity column in query result

I am firing a query like:

SELECT TaskName, StartDate FROMTasks
WHERE StartDate >= '01-Aug-2003'

Now the result comes as:

TaskName StartDate
--------
Task1 01-Aug-2003
Task5 10-Oct-2003

etc.

Now what I want is this:

Sl. No. TaskName StartDate
-----------
1 Task1 01-Aug-2003
2 Task5 10-Oct-2003

How do I get the Sl. No. field (it does not exist in the table).
Can I do it?

-surajit"Surajit Laha" <s_laha@.rediffmail.com> wrote in message
news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> I am firing a query like:
> SELECT TaskName, StartDate FROMTasks
> WHERE StartDate >= '01-Aug-2003'
> Now the result comes as:
> TaskName StartDate
> --------
> Task1 01-Aug-2003
> Task5 10-Oct-2003
> etc.
> Now what I want is this:
>
> Sl. No. TaskName StartDate
> -----------
> 1 Task1 01-Aug-2003
> 2 Task5 10-Oct-2003
>
> How do I get the Sl. No. field (it does not exist in the table).
> Can I do it?
> -surajit

SELECT T1.StartDate, T1.TaskName, COUNT(*) AS Cnt
FROM Tasks AS T1
INNER JOIN
Tasks AS T2
ON T2.StartDate <= T1.StartDate AND
T1.StartDate >= '20030801' AND
T2.StartDate >= '20030801'
GROUP BY T1.StartDate, T1.TaskName

Regards,
jag|||Hi

If TaskName is unique and gives you the correct order then try:

select ( SELECT COUNT(*) FROM tmp c WHERE c.TaskName <= b.TaskName ) as id,
b.TaskName, b.Startdate
from Tasks b
order by TaskName

John

"Surajit Laha" <s_laha@.rediffmail.com> wrote in message
news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> I am firing a query like:
> SELECT TaskName, StartDate FROMTasks
> WHERE StartDate >= '01-Aug-2003'
> Now the result comes as:
> TaskName StartDate
> --------
> Task1 01-Aug-2003
> Task5 10-Oct-2003
> etc.
> Now what I want is this:
>
> Sl. No. TaskName StartDate
> -----------
> 1 Task1 01-Aug-2003
> 2 Task5 10-Oct-2003
>
> How do I get the Sl. No. field (it does not exist in the table).
> Can I do it?
> -surajit|||Surajit,

I'm not saying this is a better way, it's just another option:

SELECT identity(int) as Sl,
TaskName,
StartDate
INTO #Tmp
FROM Tasks
WHERE StartDate >= '01-Aug-2003'

SELECT * FROM #Tmp

DROP TABLE #Tmp

Shervin

Unfortunately none of them worked!
The serial no. column returned some big values and do not appear
serially.

The last one (using a temp table) is ok, but I do not want to use temp
tables.

Task name may not be unique, there are Task_ID and Task_UID which
forms a composite key for this table.

But how can that help?

Please help/comment.

-surajit

> "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > I am firing a query like:
> > SELECT TaskName, StartDate FROMTasks
> > WHERE StartDate >= '01-Aug-2003'
> > Now the result comes as:
> > TaskName StartDate
> > --------
> > Task1 01-Aug-2003
> > Task5 10-Oct-2003
> > etc.
> > Now what I want is this:
> > Sl. No. TaskName StartDate
> > -----------
> > 1 Task1 01-Aug-2003
> > 2 Task5 10-Oct-2003
> > How do I get the Sl. No. field (it does not exist in the table).
> > Can I do it?
> > -surajit|||Surajit,

Can you tell us more about the nature of your problem? How big is your
table? Isn't it possible to generate sequence numbers in your front-end
application? Why don't you want to use temporary tables? Is it because of
some technical or performance problem or you just prefer not to use temp
tables?

Shervin

"Surajit Laha" <s_laha@.rediffmail.com> wrote in message
news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> Hi all,
> Unfortunately none of them worked!
> The serial no. column returned some big values and do not appear
> serially.
> The last one (using a temp table) is ok, but I do not want to use temp
> tables.
> Task name may not be unique, there are Task_ID and Task_UID which
> forms a composite key for this table.
> But how can that help?
> Please help/comment.
> -surajit
>
> > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > I am firing a query like:
> > > > SELECT TaskName, StartDate FROMTasks
> > > WHERE StartDate >= '01-Aug-2003'
> > > > Now the result comes as:
> > > > TaskName StartDate
> > > --------
> > > Task1 01-Aug-2003
> > > Task5 10-Oct-2003
> > > > etc.
> > > > Now what I want is this:
> > > > > Sl. No. TaskName StartDate
> > > -----------
> > > 1 Task1 01-Aug-2003
> > > 2 Task5 10-Oct-2003
> > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > Can I do it?
> > > > -surajit|||Hi

It would help if you posted DDL (Create table statements), example data
(Insert Statements) and your own attempts to solve the problem, then
everyone would have a clear understanding of your problem actually is.

John

The table has 25000 rows at this moment and increasing.

I can obviously generate a sequence in the front end, but:
1) I want to do it in the back end, to check if it is possible
2) I dont want to use Temp tables

If I accept any of these, I can solve this problem right away.

But I want to find an option of doing it in the backend, following
these constraints, and want to know if it is possible.

If it is not possible, then it is important for me to know that it is
NOT POSSIBLE.

The description of the table does not help much, as I would like to
use this idea (if I get any) in any query result from any table.

Thanks,

-surajit

"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vo8icukkap6l9a@.corp.supernews.com>...
> Surajit,
> Can you tell us more about the nature of your problem? How big is your
> table? Isn't it possible to generate sequence numbers in your front-end
> application? Why don't you want to use temporary tables? Is it because of
> some technical or performance problem or you just prefer not to use temp
> tables?
> Shervin
> "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> > Hi all,
> > Unfortunately none of them worked!
> > The serial no. column returned some big values and do not appear
> > serially.
> > The last one (using a temp table) is ok, but I do not want to use temp
> > tables.
> > Task name may not be unique, there are Task_ID and Task_UID which
> > forms a composite key for this table.
> > But how can that help?
> > Please help/comment.
> > -surajit
> > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > > I am firing a query like:
> > > > > > SELECT TaskName, StartDate FROMTasks
> > > > WHERE StartDate >= '01-Aug-2003'
> > > > > > Now the result comes as:
> > > > > > TaskName StartDate
> > > > --------
> > > > Task1 01-Aug-2003
> > > > Task5 10-Oct-2003
> > > > > > etc.
> > > > > > Now what I want is this:
> > > > > > > > Sl. No. TaskName StartDate
> > > > -----------
> > > > 1 Task1 01-Aug-2003
> > > > 2 Task5 10-Oct-2003
> > > > > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > > Can I do it?
> > > > > > -surajit|||Hi,
I don't think you can get a serial number with a simple SELECT, unless
you migrate on Oracle RDBMS which has a pseudo-column named ROWNUM
very useful for your purpose; rather, on MS-SQL 2K, you can create a
function that return a table. This example run on Northwind sample
database:

######

create function dbo.FN_ORDERS()
returns @.tab table (
ROWNUM int,
OrderId int,
OrderDate datetime,
ShipName nvarchar(40)
) as
begin
declare @.OrderId int
declare @.OrderDate datetime
declare @.ShipName nvarchar(40)
declare @.i int
declare c cursor for
select OrderId, OrderDate, ShipName from Northwind.dbo.Orders
set @.i = 0
open c
fetch c into @.OrderId, @.OrderDate, @.ShipName
while @.@.fetch_status = 0 begin
set @.i = @.i + 1
insert @.tab values (@.i, @.OrderId, @.OrderDate, @.ShipName)
fetch c into @.OrderId, @.OrderDate, @.ShipName
end
close c
deallocate c
return
end
go

select * from dbo.FN_ORDERS()

######

Obviously you have to create one function per table and change your
front end code...

Bye.

s_laha@.rediffmail.com (Surajit Laha) wrote in message news:<ba8ee108.0310082016.4fe9bca5@.posting.google.com>...
> Hi,
> The table has 25000 rows at this moment and increasing.
> I can obviously generate a sequence in the front end, but:
> 1) I want to do it in the back end, to check if it is possible
> 2) I dont want to use Temp tables
> If I accept any of these, I can solve this problem right away.
> But I want to find an option of doing it in the backend, following
> these constraints, and want to know if it is possible.
> If it is not possible, then it is important for me to know that it is
> NOT POSSIBLE.
> The description of the table does not help much, as I would like to
> use this idea (if I get any) in any query result from any table.
> Thanks,
> -surajit
>
>
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vo8icukkap6l9a@.corp.supernews.com>...
> > Surajit,
> > Can you tell us more about the nature of your problem? How big is your
> > table? Isn't it possible to generate sequence numbers in your front-end
> > application? Why don't you want to use temporary tables? Is it because of
> > some technical or performance problem or you just prefer not to use temp
> > tables?
> > Shervin
> > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> > > Hi all,
> > > > Unfortunately none of them worked!
> > > The serial no. column returned some big values and do not appear
> > > serially.
> > > > The last one (using a temp table) is ok, but I do not want to use temp
> > > tables.
> > > > Task name may not be unique, there are Task_ID and Task_UID which
> > > forms a composite key for this table.
> > > > But how can that help?
> > > > Please help/comment.
> > > > -surajit
> > > > > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > > > I am firing a query like:
> > > > > > > > SELECT TaskName, StartDate FROMTasks
> > > > > WHERE StartDate >= '01-Aug-2003'
> > > > > > > > Now the result comes as:
> > > > > > > > TaskName StartDate
> > > > > --------
> > > > > Task1 01-Aug-2003
> > > > > Task5 10-Oct-2003
> > > > > > > > etc.
> > > > > > > > Now what I want is this:
> > > > > > > > > > > Sl. No. TaskName StartDate
> > > > > -----------
> > > > > 1 Task1 01-Aug-2003
> > > > > 2 Task5 10-Oct-2003
> > > > > > > > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > > > Can I do it?
> > > > > > > > -surajit|||Surajit,

I'm afraid you can't find a straight forward solution for this problem. If
you really want to do this without a temp table and on the back end, the
only other way that I can think about is a self-join and counting the
records (as both Johns posted), but this solution is not efficient on large
result sets.

I wish SQL-Server had a ROWNUM pseudo column like Oracle.

Shervin

"Surajit Laha" <s_laha@.rediffmail.com> wrote in message
news:ba8ee108.0310082016.4fe9bca5@.posting.google.c om...
> Hi,
> The table has 25000 rows at this moment and increasing.
> I can obviously generate a sequence in the front end, but:
> 1) I want to do it in the back end, to check if it is possible
> 2) I dont want to use Temp tables
> If I accept any of these, I can solve this problem right away.
> But I want to find an option of doing it in the backend, following
> these constraints, and want to know if it is possible.
> If it is not possible, then it is important for me to know that it is
> NOT POSSIBLE.
> The description of the table does not help much, as I would like to
> use this idea (if I get any) in any query result from any table.
> Thanks,
> -surajit
>
>
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:<vo8icukkap6l9a@.corp.supernews.com>...
> > Surajit,
> > Can you tell us more about the nature of your problem? How big is your
> > table? Isn't it possible to generate sequence numbers in your front-end
> > application? Why don't you want to use temporary tables? Is it because
of
> > some technical or performance problem or you just prefer not to use temp
> > tables?
> > Shervin
> > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> > > Hi all,
> > > > Unfortunately none of them worked!
> > > The serial no. column returned some big values and do not appear
> > > serially.
> > > > The last one (using a temp table) is ok, but I do not want to use temp
> > > tables.
> > > > Task name may not be unique, there are Task_ID and Task_UID which
> > > forms a composite key for this table.
> > > > But how can that help?
> > > > Please help/comment.
> > > > -surajit
> > > > > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > > > I am firing a query like:
> > > > > > > > SELECT TaskName, StartDate FROMTasks
> > > > > WHERE StartDate >= '01-Aug-2003'
> > > > > > > > Now the result comes as:
> > > > > > > > TaskName StartDate
> > > > > --------
> > > > > Task1 01-Aug-2003
> > > > > Task5 10-Oct-2003
> > > > > > > > etc.
> > > > > > > > Now what I want is this:
> > > > > > > > > > > Sl. No. TaskName StartDate
> > > > > -----------
> > > > > 1 Task1 01-Aug-2003
> > > > > 2 Task5 10-Oct-2003
> > > > > > > > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > > > Can I do it?
> > > > > > > > -surajit|||Hi,

Thanks for youir solution.
Although it uses the idea of inserting the data in a table, it is a good solution.

So, a simple SELECT can not do the trick.

Thanks a lot to you all, who contributed, and helped me a lot!

regards,
-surajit

mj_23@.libero.it (Mauro) wrote in message news:<a2af5c1f.0310090553.279c32c7@.posting.google.com>...
> Hi,
> I don't think you can get a serial number with a simple SELECT, unless
> you migrate on Oracle RDBMS which has a pseudo-column named ROWNUM
> very useful for your purpose; rather, on MS-SQL 2K, you can create a
> function that return a table. This example run on Northwind sample
> database:
> ######
> create function dbo.FN_ORDERS()
> returns @.tab table (
> ROWNUM int,
> OrderId int,
> OrderDate datetime,
> ShipName nvarchar(40)
> ) as
> begin
> declare @.OrderId int
> declare @.OrderDate datetime
> declare @.ShipName nvarchar(40)
> declare @.i int
> declare c cursor for
> select OrderId, OrderDate, ShipName from Northwind.dbo.Orders
> set @.i = 0
> open c
> fetch c into @.OrderId, @.OrderDate, @.ShipName
> while @.@.fetch_status = 0 begin
> set @.i = @.i + 1
> insert @.tab values (@.i, @.OrderId, @.OrderDate, @.ShipName)
> fetch c into @.OrderId, @.OrderDate, @.ShipName
> end
> close c
> deallocate c
> return
> end
> go
> select * from dbo.FN_ORDERS()
> ######
>
> Obviously you have to create one function per table and change your
> front end code...
> Bye.
>
> s_laha@.rediffmail.com (Surajit Laha) wrote in message news:<ba8ee108.0310082016.4fe9bca5@.posting.google.com>...
> > Hi,
> > The table has 25000 rows at this moment and increasing.
> > I can obviously generate a sequence in the front end, but:
> > 1) I want to do it in the back end, to check if it is possible
> > 2) I dont want to use Temp tables
> > If I accept any of these, I can solve this problem right away.
> > But I want to find an option of doing it in the backend, following
> > these constraints, and want to know if it is possible.
> > If it is not possible, then it is important for me to know that it is
> > NOT POSSIBLE.
> > The description of the table does not help much, as I would like to
> > use this idea (if I get any) in any query result from any table.
> > Thanks,
> > -surajit
> > "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vo8icukkap6l9a@.corp.supernews.com>...
> > > Surajit,
> > > > Can you tell us more about the nature of your problem? How big is your
> > > table? Isn't it possible to generate sequence numbers in your front-end
> > > application? Why don't you want to use temporary tables? Is it because of
> > > some technical or performance problem or you just prefer not to use temp
> > > tables?
> > > > Shervin
> > > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> > > > Hi all,
> > > > > > Unfortunately none of them worked!
> > > > The serial no. column returned some big values and do not appear
> > > > serially.
> > > > > > The last one (using a temp table) is ok, but I do not want to use temp
> > > > tables.
> > > > > > Task name may not be unique, there are Task_ID and Task_UID which
> > > > forms a composite key for this table.
> > > > > > But how can that help?
> > > > > > Please help/comment.
> > > > > > -surajit
> > > > > > > > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > > > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > > > > I am firing a query like:
> > > > > > > > > > SELECT TaskName, StartDate FROMTasks
> > > > > > WHERE StartDate >= '01-Aug-2003'
> > > > > > > > > > Now the result comes as:
> > > > > > > > > > TaskName StartDate
> > > > > > --------
> > > > > > Task1 01-Aug-2003
> > > > > > Task5 10-Oct-2003
> > > > > > > > > > etc.
> > > > > > > > > > Now what I want is this:
> > > > > > > > > > > > > > Sl. No. TaskName StartDate
> > > > > > -----------
> > > > > > 1 Task1 01-Aug-2003
> > > > > > 2 Task5 10-Oct-2003
> > > > > > > > > > > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > > > > Can I do it?
> > > > > > > > > > -surajit|||[posted and mailed, please reply in news]

Surajit Laha (s_laha@.rediffmail.com) writes:
> Thanks for youir solution.
> Although it uses the idea of inserting the data in a table, it is a good
> solution.
> So, a simple SELECT can not do the trick.

Actually it can. See the script below. However, the performance is likely
to be bad. Using a temp table with an identity column would be a lot faster.

CREATE TABLE surajit (taskid int NOT NULL,
taskuid int NOT NULL,
taskname varchar(12) NOT NULL,
startdate datetime NOT NULL,
PRIMARY KEY (taskid, taskuid))
go
INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (1, 1, 'Task 1', '20021212')

INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (1, 3, 'Task 1.2', '20021224')

INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (2, 1, 'Task 2', '20030605')

INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (3, 1, 'Task 3', '20010915')

INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (3, 2, 'Task 3', '20011015')

go
SELECT taskname, startdate,
cnt = (SELECT COUNT(*)
FROM surajit b
WHERE b.taskname < a.taskname OR
(b.taskname = a.taskname AND
b.taskid < a.taskid OR
(b.taskid = a.taskid AND
b.taskuid < a.taskuid))) + 1
FROM surajit a
ORDER BY cnt

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Identity Column Fix

Hello, I am new to SQL and inhereted a system with a corrupt table. About 1000 records have an invalid barcode field which is an identity column and does not allow me to update once set. I need to fix these 1000 records by changing the field to 14 digits instead of the incorrect 13. What are some of my options? I was thinking about making a copy of the DB using the Wizzard, then turning the identity feature OFF and doing the fix using command SQL, then turning it back on and copying the DB back to production.

Any suggestions ? Thx

Copying the entire database seems like overkill for just 1000 rows. I've briefly put together an example below that shows how you can copy the data to a staging table, modify the data, then copy it back into your original table. Hope you find this useful.

Be aware that Foreign Key constraints could prevent you deleting rows from the main table. You'd have to drop the constraints then cascade the changes through to any referencing tables before re-applying the constraints. You'll have to do this regardless of the correction method chosen.

Chris

IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL

DROP TABLE #MyTable

IF OBJECT_ID('tempdb..#StagingTable') IS NOT NULL

DROP TABLE #StagingTable

CREATE TABLE #MyTable

(ID INT IDENTITY PRIMARY KEY NOT NULL, MyColumn VARCHAR(10))

--Create a copy of the main table - note absence of the IDENTITY property

CREATE TABLE #StagingTable

(ID INT PRIMARY KEY NOT NULL, MyColumn VARCHAR(10))

--Set up test data

INSERT INTO #MyTable(MyColumn) VALUES('Test')

SELECT [ID], MyColumn

FROM #MyTable

--Copy the rogue data to the staging table

INSERT INTO #StagingTable([ID], MyColumn)

SELECT [ID], MyColumn

FROM #MyTable

WHERE [ID] = 1

--Correct the data in the staging table

UPDATE #StagingTable

SET [ID] = 2

--Delete the rogue data from the main table

DELETE FROM #MyTable

WHERE [ID] = 1

--Copy the corrected data to the production table

SET IDENTITY_INSERT #MyTable ON

INSERT INTO #MyTable([ID], MyColumn)

SELECT [ID], MyColumn

FROM #StagingTable

SET IDENTITY_INSERT #MyTable OFF

SELECT [ID], MyColumn

FROM #MyTable

|||

Identity Column Fix

Any suggestions ? Thx

Chris

IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL

DROP TABLE #MyTable

IF OBJECT_ID('tempdb..#StagingTable') IS NOT NULL

DROP TABLE #StagingTable

CREATE TABLE #MyTable

(ID INT IDENTITY PRIMARY KEY NOT NULL, MyColumn VARCHAR(10))

--Create a copy of the main table - note absence of the IDENTITY property

CREATE TABLE #StagingTable

(ID INT PRIMARY KEY NOT NULL, MyColumn VARCHAR(10))

--Set up test data

INSERT INTO #MyTable(MyColumn) VALUES('Test')

SELECT [ID], MyColumn

FROM #MyTable

--Copy the rogue data to the staging table

INSERT INTO #StagingTable([ID], MyColumn)

SELECT [ID], MyColumn

FROM #MyTable

WHERE [ID] = 1

--Correct the data in the staging table

UPDATE #StagingTable

SET [ID] = 2

--Delete the rogue data from the main table

DELETE FROM #MyTable

WHERE [ID] = 1

--Copy the corrected data to the production table

SET IDENTITY_INSERT #MyTable ON

INSERT INTO #MyTable([ID], MyColumn)

SELECT [ID], MyColumn

FROM #StagingTable

SET IDENTITY_INSERT #MyTable OFF

SELECT [ID], MyColumn

FROM #MyTable

|||

Identity column exists for a table - how to know programatically

Hi,
Is there any way, that I can determine whether a table has any identity
column, programmatically in SQL Server. In other words, is it stored some
where like syscolumns or sysconstraints or whatever, whether a table has
identity column and what column has the identity property set to on? I am
referring to SQL Server 2000.
Thanks in advance
oursptHi
SELECT IDENT_SEED(OBJECT_NAME(id)) AS seed,
IDENT_INCR(OBJECT_NAME(id)) AS incr,
OBJECT_NAME(id) AS tbl
FROM syscolumns
WHERE (status & 128) = 128
"ourspt" <ourspt@.discussions.microsoft.com> wrote in message
news:D9DFCDEE-B7D6-4E3C-A40F-D352C1DF4308@.microsoft.com...
> Hi,
> Is there any way, that I can determine whether a table has any identity
> column, programmatically in SQL Server. In other words, is it stored some
> where like syscolumns or sysconstraints or whatever, whether a table has
> identity column and what column has the identity property set to on? I am
> referring to SQL Server 2000.
> Thanks in advance
> ourspt|||Hi,
check for sp_help <table_name> in BOL
http://msdn.microsoft.com/library/d... />
p_304w.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ourspt" wrote:

> Hi,
> Is there any way, that I can determine whether a table has any identity
> column, programmatically in SQL Server. In other words, is it stored some
> where like syscolumns or sysconstraints or whatever, whether a table has
> identity column and what column has the identity property set to on? I am
> referring to SQL Server 2000.
> Thanks in advance
> ourspt|||You can use the OBJECTPROPERTY() function for that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ourspt" <ourspt@.discussions.microsoft.com> wrote in message
news:D9DFCDEE-B7D6-4E3C-A40F-D352C1DF4308@.microsoft.com...
> Hi,
> Is there any way, that I can determine whether a table has any identity
> column, programmatically in SQL Server. In other words, is it stored some
> where like syscolumns or sysconstraints or whatever, whether a table has
> identity column and what column has the identity property set to on? I am
> referring to SQL Server 2000.
> Thanks in advance
> ourspt|||If what you're trying to do is determine which column it is, then You can
refer to it direstly in a Select Statement using the keyword IDENTITYCOL, as
in
Select IDENTITYCOL From TableName
If there is no IdentityColumn in the table this will, howver, throw an
error...
***Invalid column name 'identitycol'.***
"ourspt" wrote:

> Hi,
> Is there any way, that I can determine whether a table has any identity
> column, programmatically in SQL Server. In other words, is it stored some
> where like syscolumns or sysconstraints or whatever, whether a table has
> identity column and what column has the identity property set to on? I am
> referring to SQL Server 2000.
> Thanks in advance
> ourspt

Identity column confusion

I have imported a pretty large table from Access to MSSQL 2K
I have a column that is a ID column, which is an int. If I try to set it to be an identity column, the sort in the table gets all messed up :-
If I add a new column and want to have that as the identity my sort gets messed up (sort is extremely important to me in this case)
Anybody has any idea why this would be happening? I am trying to add a column via Enterprise Manager
Thanks
Branka> If I try to set it to be an identity column, the sort in the table gets
all messed up :-(
That's because your table isn't "sorted" ... a table, by definition, is an
unordered set of rows. There is no relationship between application of an
IDENTITY addition and any conceptual sort order you might envision. You
might get lucky if you add a clustered index on your desired sort column(s),
and *then* apply the IDENTITY, but this behavior is not guaranteed -- purely
coincidental.
Also, are you going to do this over again, every time the data in your
desired sort column(s) change? What happens when you add a row, the new row
will have the highest IDENTITY value, but it will almost certainly no longer
fit your "very important" sort order.
Instead, I think you should generate this "rank" number at query time. See
http://www.aspfaq.com/2427 for some ideas.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||> I have a column that is a ID column, which is an int. If I try to set it
to be an identity column, the sort in the table gets all messed up :-(
Do you mean the ID values change or that you simply see the rows in a
different order? In SQL, tables have no logical ordering. If you want to see
the table in order use ORDER BY on your SELECT statement - that's the only
way to guarantee that the rows will be returned in a particular order.
> If I add a new column and want to have that as the identity my sort gets
messed up (sort is extremely important to me in this case).
I'm not sure what you're asking here. You can't have two IDENTITY columns in
a table, nor should that be necessary. If you mean that the sequence of
values in the column is important to you then don't use IDENTITY. You can't
control the order in which IDENTITY values are assigned to rows. The order
is indeterminate, there may be gaps and IDENTITY columns don't even have to
be unique (although typically they are assigned a UNIQUE constraint).
--
David Portas
--
Please reply only to the newsgroup
--|||> IDENTITY columns don't even have to be unique
That's a great point that I think a lot of people miss.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Identity column as a primary key with foreign key relationships

Greetings All,
We want to have a user_id which will be a primary key and all other tables
will be joining on this user_id. My question is
Option 1: Have user_id an identity and have foreign key on this identity
table.
Option 2: Dont use identity as user_id and generate user_id with some logic.
Our group is having mixed opion and we will go with maximum number of
suggestions we get here. Also please include why you think the option you
selected is right.
Thanks,
Arshad
--
Arshad
arshadmd-nospam@.gmail.comIDENTITY all the way unless you have good reason for genrerating your
own ID. What justification /resoning do you have for not using it?|||Arshad,
Unfortunately you will also get mixed opinions here. You may read a
response from Celko where he will insult you and insist that you never
use an identity column as a primary key. Others will tell you that it
is OK. I've been through a similar issue and here is what I've learned:
1) If your project will ever be moved to a system like Oracle, you
don't want to use Identity columns as primary keys. If you will never
move your db to another system other than SQL, then using an identity
column as a primary key is OK.
2) I personally have used identity columns as primary keys in many
different scenarios and I have never had a problem with it.
3) Creating a primary key with a stored procedure or some other logic
will work but it only creates more work in coding for you and adds
additional bulk to the table that is not necessary because you will
still have the unique identity as well as a unique primary key.
4) Using multiple columns to create your primary key (social sec. and
birthday) is possible, but that can lead to confusi table relationships
and additional bulk to each related table. I like my table
relationships to be simple and intuitive, which is why I use identity
columns as primary keys.
The bottom line is that you cannot port your db to other systems if you
do this, but if porting your code to another system is not likely to
happen, then the decision is a matter of preference for the developers.
I find using identity columns as primary keys keeps the design simple
and does not add needless bulk and clutter.|||Use an autogenerated Primary key of type integer for a primary key on which
you build relationships. If you also want to use this primary key as a
userId by which to find records this is fine say you want this to be a
customer number. However having a customer 1 followed by a customer 2 etc,,
may not be a good idea if these codes are used to allow any customer access
to the database, say via internet. Its a better idea in such a case to have
non-sequential customer identifications. So a combination of RecordId as the
primary key being automatically genereated and used for relations PK-FK and
a separate USERID field that you generate and that must also be unique is
probably better. One way to generate a non sequential ID is by using a
default value derived as follows. (rand((datepart(month,getdate()) * 100000
+ datepart(second,getdate()) * 1000 + datepart(millisecond,getdate()))) *
1000000000). This generates a random number based on the seeds of the
computer date, you can ensure that its unique by making the column a unique
index.
In my view the primary key should never be part of data being viewed or used
directly by the end user and should always be created automatically by the
database engine and should ONLY be used for that particular purpose. Other
than that the basic most generally accepted rule is make the database engine
itself do as much of the work as possible for ensuring data correctnes,
there is also the fact that there are practical situations in which you may
wnt to change a userid, if the userid is the primary key you have to do
cascading updates and this takes time. If your PK is totally outside of what
users may be allowed to use, there are no cascades involved for updates,
ever.
Also, having the database do the work, removes the onus on the individual
programmer of having to call procedures specifically for creating
Primarykeys or ensuring relationship constraints are correctly applied. If
you do this in code you WILL make mistakes and you WILL forget to call the
procedures sometime.
I've seen many apps that do this in code (still today) and have had to
transfer data from these to corrrectly structured relational tables and each
time I have found things like orphan records or duplicate keys where none
should be.
Hope ths helps and that I haven't started anyone ranting and raving :-)
RD
"Arshad" <Arshad@.discussions.microsoft.com> wrote in message
news:19DC4A02-8C08-4F99-BB86-56CD9309892D@.microsoft.com...
> Greetings All,
> We want to have a user_id which will be a primary key and all other tables
> will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this identity
> table.
> Option 2: Dont use identity as user_id and generate user_id with some
> logic.
> Our group is having mixed opion and we will go with maximum number of
> suggestions we get here. Also please include why you think the option you
> selected is right.
> Thanks,
> Arshad
> --
> Arshad
> arshadmd-nospam@.gmail.com|||On Fri, 29 Jul 2005 08:50:01 -0700, Arshad wrote:
>Greetings All,
>We want to have a user_id which will be a primary key and all other tables
>will be joining on this user_id. My question is
>Option 1: Have user_id an identity and have foreign key on this identity
>table.
>Option 2: Dont use identity as user_id and generate user_id with some logic.
>Our group is having mixed opion and we will go with maximum number of
>suggestions we get here. Also please include why you think the option you
>selected is right.
>Thanks,
>Arshad
Hi Arshad,
In general, keys can fulfill two distinct functions.
Their first function is to provide a link between a row in a table and
an entity in the real world outside of the database. This is what I call
the business key, since in most cases, the business dictates what key to
use. If the users are employees and the HR department issues employee
numbers, than the business key is the employee number. In an American
tax-related database, SSN would be the business key. In a database that
supports the upkeep of a computer network, the username assigned by the
sysadmins for logging on to the network would be the business key. For
my dentists' customers, last name + address + date of birth might
qualify as the business key. And so on, and so on.
You should only consider having the database generate the business key
if there is at present no business key - and you'll still need to find
who'se in charge and get him or her to sign of on your proposal, since
it's not your job to change the business' processes.
The second function of a key is to link a row in one table to a related
row in (usually) another table - the well known FOREIGN KEY constraint.
In most cases, the FOREIGN KEY will refer to the PRIMARY KEY of the
related table. But it can also refer to any column (or combination of
columns) that is declared as UNIQUE in the related table.
My usual procedure is:
- First, find the business key, This one is always needed, since there
is no sense in storing data in a database if it can't be related back to
the real-world entities that it's supposed to describe.
- Second, determine of there will be any other tables referring to the
rows in this table. If there are, then determine if the business key is
a good condidate for implementing the FOREIGN KEY constraint. If it
isn't (e.g. becuase it is prone to frequent change, or because it is so
long that it would degrade performance in the database), then I'll
introduce a surrogate key - and in 99.9% of all cases, IDENTITY serves
fine as a surrogate key.
That leaves me with two possible designs:
1. Business key is suitable to be used in the FK relationship:
CREATE TABLE Tab1 (BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (BusinessKey)
)
CREATE TABLE Tab2 (BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 some_datetype [NOT] NULL,
other columns,
PRIMARY KEY (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (BusinessKey)
ON UPDATE CASCADE
ON DELETE NO ACTION
)
2. Business key is not suitable for FK relationship - use surrogate key:
CREATE TABLE Tab1 (Tab1_ID int NOT NULL IDENTITY,
BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (Tab1_ID),
UNIQUE (BusinessKey)
)
CREATE TABLE Tab2 (Tab2_ID int NOT NULL IDENTITY,
BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 int [NOT] NULL,
other columns,
PRIMARY KEY (Tab2ID),
UNIQUE (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (Tab1_ID
ON UPDATE NO ACTION -- Note this change!!
ON DELETE NO ACTION
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Arshad wrote:
> Greetings All,
> We want to have a user_id which will be a primary key and all other tables
> will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this identity
> table.
i use this method 99% of the time and have no problems.
the bigger issue for me is what should be the clustered index on the
table.|||Arshad:
Identities do make things easier, but they do bring along some extra
baggage that you need to be aware of.
As Elroyskimms suggested problems regarding another vendor's DBMS
(although there is a work around in Oracle w/ sequences), you will also
need to consider if you ever plan on any type of replication or merging
of data between databases, as the uniqueness of the key does not exist in
this scope.
If you skin is thick enough :>), I'd recommend posting this to
microsoft.public.sqlserver.programming, and await the verbal assault from
Joe Celko. If you can get past his insults and obtuse style, he does
have a good insight on "some" issues.
Jeff Clausius
SourceGear
=?Utf-8?B?QXJzaGFk?= <Arshad@.discussions.microsoft.com> wrote in
news:19DC4A02-8C08-4F99-BB86-56CD9309892D@.microsoft.com:
> Greetings All,
> We want to have a user_id which will be a primary key and all other
> tables will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this
> identity table.
> Option 2: Dont use identity as user_id and generate user_id with some
> logic.
> Our group is having mixed opion and we will go with maximum number of
> suggestions we get here. Also please include why you think the option
> you selected is right.
> Thanks,
> Arshad
>|||Jeff Clausius wrote:
> If you skin is thick enough :>), I'd recommend posting this to
> microsoft.public.sqlserver.programming, and await the verbal assault from
> Joe Celko. If you can get past his insults and obtuse style, he does
> have a good insight on "some" issues.
I couldn't have said it better myself!
-E|||When I create a diagram, and thereby make constraints, it is nice to
have a singe field to link to that is unique. If there isn't a single
field that makes it unique then and Identity is the perfect choice.
Since I now have this unique key, why not have it the primary key?
This ID is never going to change, nor can it be changed because of the
constraints, with records from other tables referencing it. What I
really despise is when two tables are linked together by more than one
field. If I want to make other unique key constraints I can, but for
consistency I always make the Identity column the primary key.
Another thing I like to do is always name the Identity column ID. That
way I immediately know it is the identity and the primary key for the
table I am looking at. Now lets say the name of the table is
"Department". Now when I link it to the Department Table to the
Employee table, I create a field in the Employee table called
"DepartmentID", and link it to the ID column in the Department table.
So I always know that a field that just ends in "..ID" references the
Primary Key and Identity of another table, and I know the name of the
table, because it is what is in front of "ID". I never have to wonder
or look at constraints to see if a field is referencing another table.
I always know what table and field it references just by the name of the
field. Even those who hate identity columns admin there are times that
they make sense. I say if you are ever going to use them, always use
them. Consistency is the key to making things simple.
Ken Cushing
West Valley City
kcushing
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1761833.html

Friday, February 24, 2012

Identity column not returnd after AddNew / Update with ADO and SQL 2005

Identity column jumps indefinitely

IDENTITY Column Insert

IDENTITY Column Insert

IDENTITY Column Insert

Identity Column Increment Control

Identity Column Increment Control

Identity column increases abruptly, abnormally.

Identity column increases abruptly, abnormally.

Identity column in temp table

IDENTITY column in SQL 2000 and linked tables in MS Access

Identity column in query result

Identity Column Fix

Identity Column Fix

Identity column exists for a table - how to know programatically

Identity column confusion

Identity column as a primary key with foreign key relationships

Identity column

Blog Archive

About Me