December 19, 2003 at 1:43 am
Hi,
Setting Identity columns for a transaction table is correct?
Thanks in advance
Jag
December 19, 2003 at 2:01 am
Some people might argue that using the identity property is bad, but I like it.
I think it makes somehow easier.
Btw,
CREATE TABLE [dbo].[TEST] (
[ID1] [int] IDENTITY (1, 1) NOT NULL ,
[ID2] [int] IDENTITY (1, 1) NOT NULL
)
GO
throws an error.
Only one column in a table can have an identity property.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 19, 2003 at 2:38 am
Frank,
Thanks for the suggestion.
If I am using a transaction table,
say Emp,
fields are,
EmpId int,
EmpName VarChar(50)
EmpId is the identity column and the identity increment is 1.
Suppose the EmpId value reaches 50 and then I delete the last 5 records. When I insert a new record the new EmpId will be 51 instead of 46.Since there is no WHERE clause option for the TRUNCATE option I can't use the value of the deleted EmpIDs again.
I think that raises an issue, am I right Frank?
December 19, 2003 at 2:46 am
Not really!
In almost any case due to several reasons sooner or later there will be gaps in the identity sequence.
CREATE TABLE MyIdentity(
id INT IDENTITY(1,1) NOT NULL,
some_other VARCHAR(10)
)
GO
BEGIN TRAN
INSERT INTO MyIdentity VALUES('Erster')
COMMIT TRAN
BEGIN TRAN
INSERT INTO MyIdentity VALUES('Zweiter')
ROLLBACK TRAN
BEGIN TRAN
INSERT INTO MyIdentity VALUES('Dritter')
COMMIT TRAN
SELECT * FROM MyIdentity
DROP TABLE MyIdentity
GO
ergibt einen Output von
id some_other
----------- ----------
1 Erster
3 Dritter
(2 row(s) affected)
But it is unimportant, unless you're after some sequential numbering. I think that is presentational stuff. If you're after this, you might want to read
http://www.sqlservercentral.com/columnists/glarsen/sequentialnumbering.asp
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 19, 2003 at 5:05 am
Frank,
Guru,Thank you very much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply