Hi All
How Can I Select From Table With IDENTITY For Row Count
The Table Has No IDENTITY Column
Select Col1,IDENTITY As Col2 From My Table
Col1 Col2
A 1
B 2
C 3
D 4
.. ..
ThanksHere is an article I wrote about this that might help:
http://www.databasejournal.com/feat...cle.php/2244821
"Taha" wrote:
> Hi All
> How Can I Select From Table With IDENTITY For Row Count
> The Table Has No IDENTITY Column
> Select Col1,IDENTITY As Col2 From My Table
> Col1 Col2
> A 1
> B 2
> C 3
> D 4
> ... ..
> Thanks
>
>
>
>|||OK
Thank You Greg Larsen
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:CE2DF0A4-859B-465F-A55F-B4630F8917A7@.microsoft.com...
> Here is an article I wrote about this that might help:
> http://www.databasejournal.com/feat...cle.php/2244821
>
> "Taha" wrote:
>|||I have an article too. Not to compete with Greg, in fact I'm sure they're
similar. Just posting for completeness.
http://www.aspfaq.com/2427
"Taha" <taha105@.hotmail.com> wrote in message
news:OeRTdCwlGHA.4772@.TK2MSFTNGP04.phx.gbl...
> Hi All
> How Can I Select From Table With IDENTITY For Row Count
> The Table Has No IDENTITY Column
> Select Col1,IDENTITY As Col2 From My Table
> Col1 Col2
> A 1
> B 2
> C 3
> D 4
> .. ..
> Thanks
>
>
>
Showing posts with label row. Show all posts
Showing posts with label row. Show all posts
Monday, March 19, 2012
Identity range managed by replication is full and must be updated by a replication agent.
Hello,
You have better luck in microsoft.public.sqlsever.replication. Myself,
I have very little experience of replication.
I'm getting the following error message when I try add a row using a
Stored Procedure.
"The identity range managed by replication is full and must be updated
by a replication agent".
I read up on the subject and have tried the following solutions
according to MSDN without any luck.(http://support.Microsoft.com/kb/
304706 )
sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/
library/aa239401(SQL.80).aspx ) has no effect
For Testing:
I've reloaded everything from scratch, created the pulications from by
running the sql scripts generated,created replication snapshots and
started the agents.
I've checked the current Identity values in the Agent Table:
DBCC CHECKIDENT ('Agent', NORESEED)
Checking identity information: current identity value '18606', current
column value '18606'.
I check the Table to make sure there will be no conflicts with the
primary key:
SELECT AgentID FROM Agent ORDER BY AgentID DESC
18603 is the largest AgentID in the table.
Using the Table Article Properties in the Publications Properties
Dialog, I can see values of:
Range Size at Publisher: 100,000
Range Size at Subscribers: 100
New range @. percentage: 80
In my mind this means that the Publisher will assign a new range when
the Current Indentity value goes over 80,000?
The Identity range for this table cannot be exhausted! I'm not sure
what to try next.
Please! any insight will be of great help!
Regards,
Bm(miller.brettm@.gmail.com) writes:
Quote:
Originally Posted by
I'm getting the following error message when I try add a row using a
Stored Procedure.
>
"The identity range managed by replication is full and must be updated
by a replication agent".
>
I read up on the subject and have tried the following solutions
according to MSDN without any luck.(http://support.Microsoft.com/kb/
304706 )
>
sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/
library/aa239401(SQL.80).aspx ) has no effect
You have better luck in microsoft.public.sqlsever.replication. Myself,
I have very little experience of replication.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Friday, March 9, 2012
Identity Increment
Hello, I need some help writing a script to generate Identity keys. I cannot use the row number generator because I would like to start the identity at a package level variable. Is this possible?
Thank you in advance
Thank you in advance
I just find this using a web search engine:
http://www.sqljunkies.com/WebLog/sqlbi/archive/2005/05/30/15684.aspx
I know that has been also discussed in this forum; I recommned you tu use the search functionality of the forum
|||http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/Identity corruption after 200-400 inserts???
Sql 2k, server has four dual-core processors. We're encountering an error
every 200-400 inserts that we are attempting to insert a duplicate row. The
table is simple:
BrokenIdentity
ID PK, identity
foo varchar
bar varchar
and so is the insert:
INSERT INTO BrokenIdentity (foo, bar) VALUES ('tar','fu')
but after about 200-400 inserts in a row we get the 'cannot insert duplicate
row' error. The rate this is happening is pretty shocking... the machine
isn't being shut down between the last good insert and the failing insert, so
that isn't the cause. Is this a common error with this type of modern
hardware? Are there any patches for this? Will setting the server to use
only one processor prevent this from happening? Does this also happen in
2k5? TIA.
Are shure that is the only type of insert statement you use?
Milan
"William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
message news:DDB9BB67-48B4-4478-B103-B88BE937ADB4@.microsoft.com...
> Sql 2k, server has four dual-core processors. We're encountering an error
> every 200-400 inserts that we are attempting to insert a duplicate row.
> The
> table is simple:
> BrokenIdentity
> ID PK, identity
> foo varchar
> bar varchar
> and so is the insert:
> INSERT INTO BrokenIdentity (foo, bar) VALUES ('tar','fu')
> but after about 200-400 inserts in a row we get the 'cannot insert
> duplicate
> row' error. The rate this is happening is pretty shocking... the machine
> isn't being shut down between the last good insert and the failing insert,
> so
> that isn't the cause. Is this a common error with this type of modern
> hardware? Are there any patches for this? Will setting the server to use
> only one processor prevent this from happening? Does this also happen in
> 2k5? TIA.
every 200-400 inserts that we are attempting to insert a duplicate row. The
table is simple:
BrokenIdentity
ID PK, identity
foo varchar
bar varchar
and so is the insert:
INSERT INTO BrokenIdentity (foo, bar) VALUES ('tar','fu')
but after about 200-400 inserts in a row we get the 'cannot insert duplicate
row' error. The rate this is happening is pretty shocking... the machine
isn't being shut down between the last good insert and the failing insert, so
that isn't the cause. Is this a common error with this type of modern
hardware? Are there any patches for this? Will setting the server to use
only one processor prevent this from happening? Does this also happen in
2k5? TIA.
Are shure that is the only type of insert statement you use?
Milan
"William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
message news:DDB9BB67-48B4-4478-B103-B88BE937ADB4@.microsoft.com...
> Sql 2k, server has four dual-core processors. We're encountering an error
> every 200-400 inserts that we are attempting to insert a duplicate row.
> The
> table is simple:
> BrokenIdentity
> ID PK, identity
> foo varchar
> bar varchar
> and so is the insert:
> INSERT INTO BrokenIdentity (foo, bar) VALUES ('tar','fu')
> but after about 200-400 inserts in a row we get the 'cannot insert
> duplicate
> row' error. The rate this is happening is pretty shocking... the machine
> isn't being shut down between the last good insert and the failing insert,
> so
> that isn't the cause. Is this a common error with this type of modern
> hardware? Are there any patches for this? Will setting the server to use
> only one processor prevent this from happening? Does this also happen in
> 2k5? TIA.
Identity corruption after 200-400 inserts???
Sql 2k, server has four dual-core processors. We're encountering an error
every 200-400 inserts that we are attempting to insert a duplicate row. The
table is simple:
BrokenIdentity
ID PK, identity
foo varchar
bar varchar
and so is the insert:
INSERT INTO BrokenIdentity (foo, bar) VALUES ('tar','fu')
but after about 200-400 inserts in a row we get the 'cannot insert duplicate
row' error. The rate this is happening is pretty shocking... the machine
isn't being shut down between the last good insert and the failing insert, s
o
that isn't the cause. Is this a common error with this type of modern
hardware? Are there any patches for this? Will setting the server to use
only one processor prevent this from happening? Does this also happen in
2k5? TIA.Are shure that is the only type of insert statement you use?
Milan
"William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
message news:DDB9BB67-48B4-4478-B103-B88BE937ADB4@.microsoft.com...
> Sql 2k, server has four dual-core processors. We're encountering an error
> every 200-400 inserts that we are attempting to insert a duplicate row.
> The
> table is simple:
> BrokenIdentity
> ID PK, identity
> foo varchar
> bar varchar
> and so is the insert:
> INSERT INTO BrokenIdentity (foo, bar) VALUES ('tar','fu')
> but after about 200-400 inserts in a row we get the 'cannot insert
> duplicate
> row' error. The rate this is happening is pretty shocking... the machine
> isn't being shut down between the last good insert and the failing insert,
> so
> that isn't the cause. Is this a common error with this type of modern
> hardware? Are there any patches for this? Will setting the server to use
> only one processor prevent this from happening? Does this also happen in
> 2k5? TIA.
every 200-400 inserts that we are attempting to insert a duplicate row. The
table is simple:
BrokenIdentity
ID PK, identity
foo varchar
bar varchar
and so is the insert:
INSERT INTO BrokenIdentity (foo, bar) VALUES ('tar','fu')
but after about 200-400 inserts in a row we get the 'cannot insert duplicate
row' error. The rate this is happening is pretty shocking... the machine
isn't being shut down between the last good insert and the failing insert, s
o
that isn't the cause. Is this a common error with this type of modern
hardware? Are there any patches for this? Will setting the server to use
only one processor prevent this from happening? Does this also happen in
2k5? TIA.Are shure that is the only type of insert statement you use?
Milan
"William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
message news:DDB9BB67-48B4-4478-B103-B88BE937ADB4@.microsoft.com...
> Sql 2k, server has four dual-core processors. We're encountering an error
> every 200-400 inserts that we are attempting to insert a duplicate row.
> The
> table is simple:
> BrokenIdentity
> ID PK, identity
> foo varchar
> bar varchar
> and so is the insert:
> INSERT INTO BrokenIdentity (foo, bar) VALUES ('tar','fu')
> but after about 200-400 inserts in a row we get the 'cannot insert
> duplicate
> row' error. The rate this is happening is pretty shocking... the machine
> isn't being shut down between the last good insert and the failing insert,
> so
> that isn't the cause. Is this a common error with this type of modern
> hardware? Are there any patches for this? Will setting the server to use
> only one processor prevent this from happening? Does this also happen in
> 2k5? TIA.
Wednesday, March 7, 2012
Identity column without script?
I have a database that is already created and I would like to change all the row id columns (which are currently bigint fields) and turn them into autonumbering Identity fields. Is there a way to do this through the enterprise manager or do I need to recreate all tables in the database using a script that creates identity columns in CREATE_TABLE and then import existing data into it?
Thanks in advance!USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99(Col1 bigint, col2 char(2))
GO
INSERT INTO myTable99(Col1, Col2)
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 3,'c'
GO
-- This is what EM will Do
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_myTable99
(
Col1 int NOT NULL IDENTITY (1, 1),
col2 char(2) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 ON
GO
IF EXISTS(SELECT * FROM dbo.myTable99)
EXEC('INSERT INTO dbo.Tmp_myTable99 (Col1, col2)
SELECT CONVERT(int, Col1), col2 FROM dbo.myTable99 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 OFF
GO
DROP TABLE dbo.myTable99
GO
EXECUTE sp_rename N'dbo.Tmp_myTable99', N'myTable99', 'OBJECT'
GO
COMMIT
GO
SELECT * FROM myTable99
GO
sp_help myTable99
GO
SET NOCOUNT OFF
DROP TABLE myTable99
GO|||Yes, but I wanted to do it through the enterprise manager (the database is already set up and populated, but not in production yet.). I was hoping maybe all I had to do was enter a formula in the design view for each table, and then do inserts in code without having to enter that field in my inserts.
Can this be done?|||if you are asking if you can add an identity to a column after the table has been created and the data exists, the answer is yes
open the enterprise manager
right click the table that you want to modify
Right click the table and select Design Table
in the grid at the top choose the column you want to add the identity to
and at the bottom select the following properties
identity = yes
seed = this is the initial value which in the case of existing data is the highest value placed in the column. os for example if your last value in the col was 234 the identityseed would be 234
increment the number that you want to increase the identity by. usually 1
when you insert another row to this table the identity will add 1 to the seed and give you 235 as your next value.
is this what you wanted? :eek:|||That's exactly what I wanted! Thanks!!
Thanks in advance!USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99(Col1 bigint, col2 char(2))
GO
INSERT INTO myTable99(Col1, Col2)
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 3,'c'
GO
-- This is what EM will Do
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_myTable99
(
Col1 int NOT NULL IDENTITY (1, 1),
col2 char(2) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 ON
GO
IF EXISTS(SELECT * FROM dbo.myTable99)
EXEC('INSERT INTO dbo.Tmp_myTable99 (Col1, col2)
SELECT CONVERT(int, Col1), col2 FROM dbo.myTable99 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 OFF
GO
DROP TABLE dbo.myTable99
GO
EXECUTE sp_rename N'dbo.Tmp_myTable99', N'myTable99', 'OBJECT'
GO
COMMIT
GO
SELECT * FROM myTable99
GO
sp_help myTable99
GO
SET NOCOUNT OFF
DROP TABLE myTable99
GO|||Yes, but I wanted to do it through the enterprise manager (the database is already set up and populated, but not in production yet.). I was hoping maybe all I had to do was enter a formula in the design view for each table, and then do inserts in code without having to enter that field in my inserts.
Can this be done?|||if you are asking if you can add an identity to a column after the table has been created and the data exists, the answer is yes
open the enterprise manager
right click the table that you want to modify
Right click the table and select Design Table
in the grid at the top choose the column you want to add the identity to
and at the bottom select the following properties
identity = yes
seed = this is the initial value which in the case of existing data is the highest value placed in the column. os for example if your last value in the col was 234 the identityseed would be 234
increment the number that you want to increase the identity by. usually 1
when you insert another row to this table the identity will add 1 to the seed and give you 235 as your next value.
is this what you wanted? :eek:|||That's exactly what I wanted! Thanks!!
Friday, February 24, 2012
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
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
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
Hi,
Do many people use the IDENTITY type for a column?
The advantage is that every time you add a row, that identity column is
automatically incremented, however, I have always wondered about the
following issues that I could try to test and find out, but my testing will
not necessarily reveil all there is to know that you might know off the top
of your heads.
1) can you manually insert an identity? for example, row 1, 2 and 3 already
exist and I manually INSERT a row with id 5, what will happen when identity
reaches 4 and wish to move to 5? will it skip 5 because it already exists,
overwrite it, or what?
2) What happens if identity reaches maximum? If INDENTITY is reset, while
rows already exist, what will happen to existing rows? See question 1.
3) identity keeps moving up.. if later rows are deleted, gaps are formed.
How can this be prevented? Is there a way to find most efficiently which
ID's are available?
So basically, imagine I have a table like this:
CREATE TABLE MyTable (
Id IDENTITY(1,1) PRIMARY KEY,
OtherData TEXT
)
GO
INSERT INTO MyTable(OtherData) VALUES ("test1")
INSERT INTO MyTable(OtherData) VALUES ("test2")
INSERT INTO MyTable(OtherData) VALUES ("test3")
INSERT INTO MyTable(OtherData) VALUES ("test4")
INSERT INTO MyTable(OtherData) VALUES ("test5")
DELETE FROM MyTable WHERE Id=2 OR Id=4
Now I wish to have a query that gives the available id's back (2 and 4 in
this case).
The problem is, how do you info about rows that do not exist?
Only way I can think of is get MAX(Id) and then loop through all values in
descending order until you find one that doesn't exist, testing each one.
And this can become very slow in case of many records, and can't be the way.
Any suggestions? How are IDENTITY columns used? Just keep letting it go up
until it runs out? I know this would take millions of records maybe and in
real life situations may never happen, but theoretically, it just doesn't
sound right.
rows will get deleted and added and at some point in the future, I will have
reached the ceiling because deleted id's are never reused.
Or is it much better to use a "FindFreeId" type of stored procedure or
whatever, wit or without an IDENTITY column?
LisaI just thought of a way that it might be done:
Find all consecutive rows where the difference between ID values is more
than 1.
Is there a better way?
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23lmHXdnXFHA.2768@.tk2msftngp13.phx.gbl...
> Hi,
> Do many people use the IDENTITY type for a column?
> The advantage is that every time you add a row, that identity column is
> automatically incremented, however, I have always wondered about the
> following issues that I could try to test and find out, but my testing
> will not necessarily reveil all there is to know that you might know off
> the top of your heads.
> 1) can you manually insert an identity? for example, row 1, 2 and 3
> already exist and I manually INSERT a row with id 5, what will happen when
> identity reaches 4 and wish to move to 5? will it skip 5 because it
> already exists, overwrite it, or what?
> 2) What happens if identity reaches maximum? If INDENTITY is reset, while
> rows already exist, what will happen to existing rows? See question 1.
> 3) identity keeps moving up.. if later rows are deleted, gaps are formed.
> How can this be prevented? Is there a way to find most efficiently which
> ID's are available?
> So basically, imagine I have a table like this:
> CREATE TABLE MyTable (
> Id IDENTITY(1,1) PRIMARY KEY,
> OtherData TEXT
> )
> GO
> INSERT INTO MyTable(OtherData) VALUES ("test1")
> INSERT INTO MyTable(OtherData) VALUES ("test2")
> INSERT INTO MyTable(OtherData) VALUES ("test3")
> INSERT INTO MyTable(OtherData) VALUES ("test4")
> INSERT INTO MyTable(OtherData) VALUES ("test5")
> DELETE FROM MyTable WHERE Id=2 OR Id=4
> Now I wish to have a query that gives the available id's back (2 and 4 in
> this case).
> The problem is, how do you info about rows that do not exist?
> Only way I can think of is get MAX(Id) and then loop through all values in
> descending order until you find one that doesn't exist, testing each one.
> And this can become very slow in case of many records, and can't be the
> way.
> Any suggestions? How are IDENTITY columns used? Just keep letting it go up
> until it runs out? I know this would take millions of records maybe and in
> real life situations may never happen, but theoretically, it just doesn't
> sound right.
> rows will get deleted and added and at some point in the future, I will
> have reached the ceiling because deleted id's are never reused.
> Or is it much better to use a "FindFreeId" type of stored procedure or
> whatever, wit or without an IDENTITY column?
> Lisa
>|||>> Do many people use the IDENTITY type for a column? <<
Lots of them, usually newbies who have not had a course in RDBMS so
they would understand what a relatioanl key is and still think in terms
of sequential files and records. Google some of my rants about this
design flaw.
Or talk to an old programmer who grew up with magnetic tape files which
had record numbers instead of relational keys. We had all those
problems with 1950's technology. Dijkstra was right; we keep
re-invetning the same errors over and over in this trade.|||Many opinions:
http://www.dbpd.com/vault/9805xtra.htm
http://www.sqlteam.com/forums/topic...136&whichpage=1
Types" target="_blank">http://www.ssw.com.au/SSW/Standards...ey
Types
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23lmHXdnXFHA.2768@.tk2msftngp13.phx.gbl...
> Hi,
> Do many people use the IDENTITY type for a column?
> The advantage is that every time you add a row, that identity column is
> automatically incremented, however, I have always wondered about the
> following issues that I could try to test and find out, but my testing
> will not necessarily reveil all there is to know that you might know off
> the top of your heads.
> 1) can you manually insert an identity? for example, row 1, 2 and 3
> already exist and I manually INSERT a row with id 5, what will happen when
> identity reaches 4 and wish to move to 5? will it skip 5 because it
> already exists, overwrite it, or what?
> 2) What happens if identity reaches maximum? If INDENTITY is reset, while
> rows already exist, what will happen to existing rows? See question 1.
> 3) identity keeps moving up.. if later rows are deleted, gaps are formed.
> How can this be prevented? Is there a way to find most efficiently which
> ID's are available?
> So basically, imagine I have a table like this:
> CREATE TABLE MyTable (
> Id IDENTITY(1,1) PRIMARY KEY,
> OtherData TEXT
> )
> GO
> INSERT INTO MyTable(OtherData) VALUES ("test1")
> INSERT INTO MyTable(OtherData) VALUES ("test2")
> INSERT INTO MyTable(OtherData) VALUES ("test3")
> INSERT INTO MyTable(OtherData) VALUES ("test4")
> INSERT INTO MyTable(OtherData) VALUES ("test5")
> DELETE FROM MyTable WHERE Id=2 OR Id=4
> Now I wish to have a query that gives the available id's back (2 and 4 in
> this case).
> The problem is, how do you info about rows that do not exist?
> Only way I can think of is get MAX(Id) and then loop through all values in
> descending order until you find one that doesn't exist, testing each one.
> And this can become very slow in case of many records, and can't be the
> way.
> Any suggestions? How are IDENTITY columns used? Just keep letting it go up
> until it runs out? I know this would take millions of records maybe and in
> real life situations may never happen, but theoretically, it just doesn't
> sound right.
> rows will get deleted and added and at some point in the future, I will
> have reached the ceiling because deleted id's are never reused.
> Or is it much better to use a "FindFreeId" type of stored procedure or
> whatever, wit or without an IDENTITY column?
> Lisa
>|||This is a bit of a holy issue.
> Do many people use the IDENTITY type for a column?
Yes much to the chagrin of purists.
<snip>
> 1) can you manually insert an identity? for example, row 1, 2 and 3 alread
y
> exist and I manually INSERT a row with id 5, what will happen when identit
y
> reaches 4 and wish to move to 5? will it skip 5 because it already exists,
> overwrite it, or what?
Yes. In SQL Server, you can use the Identity_Insert statement to temporarily
allow the forceful setting of an identity value. If the identity column is t
he
primary key, then even if Identity_Insert is enabled, duplicates will not be
allowed. Thus, this is one of the reasons that if you are making your identi
ty
column the primary key, you should explicitly do so.
> 2) What happens if identity reaches maximum? If INDENTITY is reset, while
rows
> already exist, what will happen to existing rows? See question 1.
It will throw an error. However, this can be easily anticipated and handled
by
adjusting the data type of the column to something larger. It is highly doub
tful
you will reach the cap of a BigInt.
> 3) identity keeps moving up.. if later rows are deleted, gaps are formed.
How
> can this be prevented? Is there a way to find most efficiently which ID's
are
> available?
Gaps will form and you shouldn't care about it. Identity columns are not mea
nt
to provide sequence with no gaps. For that, you should use a different, cust
om
and intentional mechanism. Identity columns are simply a cheap and easy way
of
providing a unique identifier for each row.
> Any suggestions? How are IDENTITY columns used? Just keep letting it go up
> until it runs out? I know this would take millions of records maybe and in
> real life situations may never happen, but theoretically, it just doesn't
> sound right.
> rows will get deleted and added and at some point in the future, I will ha
ve
> reached the ceiling because deleted id's are never reused.
Purists would agree and thus do not recommend their use. People like Mr. Cel
ko
(and officially Codd) argue that the primary key should be an assemblage of
actual data values not some system generated key. However, since it is highl
y
unlikely that you will run out of key values, this reason alone should not b
e a
deterrent. If you thought it was even remotely possible to hit 2 billion row
s
(the maximum for an int) you can use a BigInt.
Thomas|||"Thomas Coleman" <thomas@.newsgroup.nospam> wrote in message
news:Op23lUoXFHA.3864@.TK2MSFTNGP10.phx.gbl...
> This is a bit of a holy issue.
>
> Yes much to the chagrin of purists.
> <snip>
>
> Yes. In SQL Server, you can use the Identity_Insert statement to
> temporarily allow the forceful setting of an identity value. If the
> identity column is the primary key, then even if Identity_Insert is
> enabled, duplicates will not be allowed. Thus, this is one of the reasons
> that if you are making your identity column the primary key, you should
> explicitly do so.
Thomas,
Lisa might get the wrong impression from your answer to this part.
The answer to "Will it skip 5 because it already exists, overwrite it, or
what?" is this, I think:
If the value 5 is inserted "manually", and 5 is larger than the
next "automatic" identity value would have been, the identity
seed for this table will be reset as if 5 were an automatically
inserted value (6 will be inserted next, skipping any values
below 5 that were never generated). If 5 is smaller than
would otherwise have been inserted, the sequence of automatically
generated values will not be changed.
These rules apply whether or not the insert of the value 5 succeeds
or fails. It can fail because of a primary key or unique constraint
on the column if 5 is already a value in the column.
The Books Online topic SET IDENTITY_INSERT and the other
articles about the identity property explain this behavior.
SK
>
> It will throw an error. However, this can be easily anticipated and
> handled by adjusting the data type of the column to something larger. It
> is highly doubtful you will reach the cap of a BigInt.
>
> Gaps will form and you shouldn't care about it. Identity columns are not
> meant to provide sequence with no gaps. For that, you should use a
> different, custom and intentional mechanism. Identity columns are simply a
> cheap and easy way of providing a unique identifier for each row.
>
> Purists would agree and thus do not recommend their use. People like Mr.
> Celko (and officially Codd) argue that the primary key should be an
> assemblage of actual data values not some system generated key. However,
> since it is highly unlikely that you will run out of key values, this
> reason alone should not be a deterrent. If you thought it was even
> remotely possible to hit 2 billion rows (the maximum for an int) you can
> use a BigInt.
>
> Thomas
>|||Joe,
Do you know many "newbies" who have been doing this so long they "still
think in terms of sequential files and records?" ;-)
Lisa,
As you read more on this issue you'll see a debate between the "practical"
side and the "purist" side. Both views have valid points that should be
considered when making your design. When to use an Identity? That depends on
the requirements. But it's a NOT moral issue and it's neither always good,
or always bad. There is no perfect right answer. It just depends.
-Paul
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116731472.050155.78780@.o13g2000cwo.googlegroups.com...
> Lots of them, usually newbies who have not had a course in RDBMS so
> they would understand what a relatioanl key is and still think in terms
> of sequential files and records. Google some of my rants about this
> design flaw.
> Or talk to an old programmer who grew up with magnetic tape files which
> had record numbers instead of relational keys. We had all those
> problems with 1950's technology. Dijkstra was right; we keep
> re-invetning the same errors over and over in this trade.
>|||> Lisa might get the wrong impression from your answer to this part.
> The answer to "Will it skip 5 because it already exists, overwrite it, or
> what?" is this, I think:
> If the value 5 is inserted "manually", and 5 is larger than the
> next "automatic" identity value would have been, the identity
> seed for this table will be reset as if 5 were an automatically
> inserted value (6 will be inserted next, skipping any values
> below 5 that were never generated). If 5 is smaller than
> would otherwise have been inserted, the sequence of automatically
> generated values will not be changed.
> These rules apply whether or not the insert of the value 5 succeeds
> or fails. It can fail because of a primary key or unique constraint
> on the column if 5 is already a value in the column.
I think you're right about the interpretation of her question. Although, as
I
mentioned, perfect sequence and the ability to predict new values should not
be
a consideration if using an identity column.
Thomas|||"Thomas Coleman" <thomas@.newsgroup.nospam> wrote in message
news:Op23lUoXFHA.3864@.TK2MSFTNGP10.phx.gbl...
> This is a bit of a holy issue.
Only for people who can't walk and chew gum at the same time.
> Yes much to the chagrin of purists.
> <snip>
>
already
identity
exists,
> Yes. In SQL Server, you can use the Identity_Insert statement to
temporarily
> allow the forceful setting of an identity value. If the identity column is
the
> primary key, then even if Identity_Insert is enabled, duplicates will not
be
> allowed. Thus, this is one of the reasons that if you are making your
identity
> column the primary key, you should explicitly do so.
>
while rows
> It will throw an error. However, this can be easily anticipated and
handled by
> adjusting the data type of the column to something larger. It is highly
doubtful
> you will reach the cap of a BigInt.
>
formed. How
ID's are
> Gaps will form and you shouldn't care about it. Identity columns are not
meant
> to provide sequence with no gaps. For that, you should use a different,
custom
> and intentional mechanism. Identity columns are simply a cheap and easy
way of
> providing a unique identifier for each row.
>
up
in
doesn't
have
> Purists would agree and thus do not recommend their use. People like Mr.
Celko
> (and officially Codd) argue that the primary key should be an assemblage
of
> actual data values not some system generated key. However, since it is
highly
> unlikely that you will run out of key values, this reason alone should not
be a
> deterrent. If you thought it was even remotely possible to hit 2 billion
rows
> (the maximum for an int) you can use a BigInt.
>
> Thomas
>
Do many people use the IDENTITY type for a column?
The advantage is that every time you add a row, that identity column is
automatically incremented, however, I have always wondered about the
following issues that I could try to test and find out, but my testing will
not necessarily reveil all there is to know that you might know off the top
of your heads.
1) can you manually insert an identity? for example, row 1, 2 and 3 already
exist and I manually INSERT a row with id 5, what will happen when identity
reaches 4 and wish to move to 5? will it skip 5 because it already exists,
overwrite it, or what?
2) What happens if identity reaches maximum? If INDENTITY is reset, while
rows already exist, what will happen to existing rows? See question 1.
3) identity keeps moving up.. if later rows are deleted, gaps are formed.
How can this be prevented? Is there a way to find most efficiently which
ID's are available?
So basically, imagine I have a table like this:
CREATE TABLE MyTable (
Id IDENTITY(1,1) PRIMARY KEY,
OtherData TEXT
)
GO
INSERT INTO MyTable(OtherData) VALUES ("test1")
INSERT INTO MyTable(OtherData) VALUES ("test2")
INSERT INTO MyTable(OtherData) VALUES ("test3")
INSERT INTO MyTable(OtherData) VALUES ("test4")
INSERT INTO MyTable(OtherData) VALUES ("test5")
DELETE FROM MyTable WHERE Id=2 OR Id=4
Now I wish to have a query that gives the available id's back (2 and 4 in
this case).
The problem is, how do you info about rows that do not exist?
Only way I can think of is get MAX(Id) and then loop through all values in
descending order until you find one that doesn't exist, testing each one.
And this can become very slow in case of many records, and can't be the way.
Any suggestions? How are IDENTITY columns used? Just keep letting it go up
until it runs out? I know this would take millions of records maybe and in
real life situations may never happen, but theoretically, it just doesn't
sound right.
rows will get deleted and added and at some point in the future, I will have
reached the ceiling because deleted id's are never reused.
Or is it much better to use a "FindFreeId" type of stored procedure or
whatever, wit or without an IDENTITY column?
LisaI just thought of a way that it might be done:
Find all consecutive rows where the difference between ID values is more
than 1.
Is there a better way?
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23lmHXdnXFHA.2768@.tk2msftngp13.phx.gbl...
> Hi,
> Do many people use the IDENTITY type for a column?
> The advantage is that every time you add a row, that identity column is
> automatically incremented, however, I have always wondered about the
> following issues that I could try to test and find out, but my testing
> will not necessarily reveil all there is to know that you might know off
> the top of your heads.
> 1) can you manually insert an identity? for example, row 1, 2 and 3
> already exist and I manually INSERT a row with id 5, what will happen when
> identity reaches 4 and wish to move to 5? will it skip 5 because it
> already exists, overwrite it, or what?
> 2) What happens if identity reaches maximum? If INDENTITY is reset, while
> rows already exist, what will happen to existing rows? See question 1.
> 3) identity keeps moving up.. if later rows are deleted, gaps are formed.
> How can this be prevented? Is there a way to find most efficiently which
> ID's are available?
> So basically, imagine I have a table like this:
> CREATE TABLE MyTable (
> Id IDENTITY(1,1) PRIMARY KEY,
> OtherData TEXT
> )
> GO
> INSERT INTO MyTable(OtherData) VALUES ("test1")
> INSERT INTO MyTable(OtherData) VALUES ("test2")
> INSERT INTO MyTable(OtherData) VALUES ("test3")
> INSERT INTO MyTable(OtherData) VALUES ("test4")
> INSERT INTO MyTable(OtherData) VALUES ("test5")
> DELETE FROM MyTable WHERE Id=2 OR Id=4
> Now I wish to have a query that gives the available id's back (2 and 4 in
> this case).
> The problem is, how do you info about rows that do not exist?
> Only way I can think of is get MAX(Id) and then loop through all values in
> descending order until you find one that doesn't exist, testing each one.
> And this can become very slow in case of many records, and can't be the
> way.
> Any suggestions? How are IDENTITY columns used? Just keep letting it go up
> until it runs out? I know this would take millions of records maybe and in
> real life situations may never happen, but theoretically, it just doesn't
> sound right.
> rows will get deleted and added and at some point in the future, I will
> have reached the ceiling because deleted id's are never reused.
> Or is it much better to use a "FindFreeId" type of stored procedure or
> whatever, wit or without an IDENTITY column?
> Lisa
>|||>> Do many people use the IDENTITY type for a column? <<
Lots of them, usually newbies who have not had a course in RDBMS so
they would understand what a relatioanl key is and still think in terms
of sequential files and records. Google some of my rants about this
design flaw.
Or talk to an old programmer who grew up with magnetic tape files which
had record numbers instead of relational keys. We had all those
problems with 1950's technology. Dijkstra was right; we keep
re-invetning the same errors over and over in this trade.|||Many opinions:
http://www.dbpd.com/vault/9805xtra.htm
http://www.sqlteam.com/forums/topic...136&whichpage=1
Types" target="_blank">http://www.ssw.com.au/SSW/Standards...ey
Types
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23lmHXdnXFHA.2768@.tk2msftngp13.phx.gbl...
> Hi,
> Do many people use the IDENTITY type for a column?
> The advantage is that every time you add a row, that identity column is
> automatically incremented, however, I have always wondered about the
> following issues that I could try to test and find out, but my testing
> will not necessarily reveil all there is to know that you might know off
> the top of your heads.
> 1) can you manually insert an identity? for example, row 1, 2 and 3
> already exist and I manually INSERT a row with id 5, what will happen when
> identity reaches 4 and wish to move to 5? will it skip 5 because it
> already exists, overwrite it, or what?
> 2) What happens if identity reaches maximum? If INDENTITY is reset, while
> rows already exist, what will happen to existing rows? See question 1.
> 3) identity keeps moving up.. if later rows are deleted, gaps are formed.
> How can this be prevented? Is there a way to find most efficiently which
> ID's are available?
> So basically, imagine I have a table like this:
> CREATE TABLE MyTable (
> Id IDENTITY(1,1) PRIMARY KEY,
> OtherData TEXT
> )
> GO
> INSERT INTO MyTable(OtherData) VALUES ("test1")
> INSERT INTO MyTable(OtherData) VALUES ("test2")
> INSERT INTO MyTable(OtherData) VALUES ("test3")
> INSERT INTO MyTable(OtherData) VALUES ("test4")
> INSERT INTO MyTable(OtherData) VALUES ("test5")
> DELETE FROM MyTable WHERE Id=2 OR Id=4
> Now I wish to have a query that gives the available id's back (2 and 4 in
> this case).
> The problem is, how do you info about rows that do not exist?
> Only way I can think of is get MAX(Id) and then loop through all values in
> descending order until you find one that doesn't exist, testing each one.
> And this can become very slow in case of many records, and can't be the
> way.
> Any suggestions? How are IDENTITY columns used? Just keep letting it go up
> until it runs out? I know this would take millions of records maybe and in
> real life situations may never happen, but theoretically, it just doesn't
> sound right.
> rows will get deleted and added and at some point in the future, I will
> have reached the ceiling because deleted id's are never reused.
> Or is it much better to use a "FindFreeId" type of stored procedure or
> whatever, wit or without an IDENTITY column?
> Lisa
>|||This is a bit of a holy issue.
> Do many people use the IDENTITY type for a column?
Yes much to the chagrin of purists.
<snip>
> 1) can you manually insert an identity? for example, row 1, 2 and 3 alread
y
> exist and I manually INSERT a row with id 5, what will happen when identit
y
> reaches 4 and wish to move to 5? will it skip 5 because it already exists,
> overwrite it, or what?
Yes. In SQL Server, you can use the Identity_Insert statement to temporarily
allow the forceful setting of an identity value. If the identity column is t
he
primary key, then even if Identity_Insert is enabled, duplicates will not be
allowed. Thus, this is one of the reasons that if you are making your identi
ty
column the primary key, you should explicitly do so.
> 2) What happens if identity reaches maximum? If INDENTITY is reset, while
rows
> already exist, what will happen to existing rows? See question 1.
It will throw an error. However, this can be easily anticipated and handled
by
adjusting the data type of the column to something larger. It is highly doub
tful
you will reach the cap of a BigInt.
> 3) identity keeps moving up.. if later rows are deleted, gaps are formed.
How
> can this be prevented? Is there a way to find most efficiently which ID's
are
> available?
Gaps will form and you shouldn't care about it. Identity columns are not mea
nt
to provide sequence with no gaps. For that, you should use a different, cust
om
and intentional mechanism. Identity columns are simply a cheap and easy way
of
providing a unique identifier for each row.
> Any suggestions? How are IDENTITY columns used? Just keep letting it go up
> until it runs out? I know this would take millions of records maybe and in
> real life situations may never happen, but theoretically, it just doesn't
> sound right.
> rows will get deleted and added and at some point in the future, I will ha
ve
> reached the ceiling because deleted id's are never reused.
Purists would agree and thus do not recommend their use. People like Mr. Cel
ko
(and officially Codd) argue that the primary key should be an assemblage of
actual data values not some system generated key. However, since it is highl
y
unlikely that you will run out of key values, this reason alone should not b
e a
deterrent. If you thought it was even remotely possible to hit 2 billion row
s
(the maximum for an int) you can use a BigInt.
Thomas|||"Thomas Coleman" <thomas@.newsgroup.nospam> wrote in message
news:Op23lUoXFHA.3864@.TK2MSFTNGP10.phx.gbl...
> This is a bit of a holy issue.
>
> Yes much to the chagrin of purists.
> <snip>
>
> Yes. In SQL Server, you can use the Identity_Insert statement to
> temporarily allow the forceful setting of an identity value. If the
> identity column is the primary key, then even if Identity_Insert is
> enabled, duplicates will not be allowed. Thus, this is one of the reasons
> that if you are making your identity column the primary key, you should
> explicitly do so.
Thomas,
Lisa might get the wrong impression from your answer to this part.
The answer to "Will it skip 5 because it already exists, overwrite it, or
what?" is this, I think:
If the value 5 is inserted "manually", and 5 is larger than the
next "automatic" identity value would have been, the identity
seed for this table will be reset as if 5 were an automatically
inserted value (6 will be inserted next, skipping any values
below 5 that were never generated). If 5 is smaller than
would otherwise have been inserted, the sequence of automatically
generated values will not be changed.
These rules apply whether or not the insert of the value 5 succeeds
or fails. It can fail because of a primary key or unique constraint
on the column if 5 is already a value in the column.
The Books Online topic SET IDENTITY_INSERT and the other
articles about the identity property explain this behavior.
SK
>
> It will throw an error. However, this can be easily anticipated and
> handled by adjusting the data type of the column to something larger. It
> is highly doubtful you will reach the cap of a BigInt.
>
> Gaps will form and you shouldn't care about it. Identity columns are not
> meant to provide sequence with no gaps. For that, you should use a
> different, custom and intentional mechanism. Identity columns are simply a
> cheap and easy way of providing a unique identifier for each row.
>
> Purists would agree and thus do not recommend their use. People like Mr.
> Celko (and officially Codd) argue that the primary key should be an
> assemblage of actual data values not some system generated key. However,
> since it is highly unlikely that you will run out of key values, this
> reason alone should not be a deterrent. If you thought it was even
> remotely possible to hit 2 billion rows (the maximum for an int) you can
> use a BigInt.
>
> Thomas
>|||Joe,
Do you know many "newbies" who have been doing this so long they "still
think in terms of sequential files and records?" ;-)
Lisa,
As you read more on this issue you'll see a debate between the "practical"
side and the "purist" side. Both views have valid points that should be
considered when making your design. When to use an Identity? That depends on
the requirements. But it's a NOT moral issue and it's neither always good,
or always bad. There is no perfect right answer. It just depends.
-Paul
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116731472.050155.78780@.o13g2000cwo.googlegroups.com...
> Lots of them, usually newbies who have not had a course in RDBMS so
> they would understand what a relatioanl key is and still think in terms
> of sequential files and records. Google some of my rants about this
> design flaw.
> Or talk to an old programmer who grew up with magnetic tape files which
> had record numbers instead of relational keys. We had all those
> problems with 1950's technology. Dijkstra was right; we keep
> re-invetning the same errors over and over in this trade.
>|||> Lisa might get the wrong impression from your answer to this part.
> The answer to "Will it skip 5 because it already exists, overwrite it, or
> what?" is this, I think:
> If the value 5 is inserted "manually", and 5 is larger than the
> next "automatic" identity value would have been, the identity
> seed for this table will be reset as if 5 were an automatically
> inserted value (6 will be inserted next, skipping any values
> below 5 that were never generated). If 5 is smaller than
> would otherwise have been inserted, the sequence of automatically
> generated values will not be changed.
> These rules apply whether or not the insert of the value 5 succeeds
> or fails. It can fail because of a primary key or unique constraint
> on the column if 5 is already a value in the column.
I think you're right about the interpretation of her question. Although, as
I
mentioned, perfect sequence and the ability to predict new values should not
be
a consideration if using an identity column.
Thomas|||"Thomas Coleman" <thomas@.newsgroup.nospam> wrote in message
news:Op23lUoXFHA.3864@.TK2MSFTNGP10.phx.gbl...
> This is a bit of a holy issue.
Only for people who can't walk and chew gum at the same time.
> Yes much to the chagrin of purists.
> <snip>
>
already
identity
exists,
> Yes. In SQL Server, you can use the Identity_Insert statement to
temporarily
> allow the forceful setting of an identity value. If the identity column is
the
> primary key, then even if Identity_Insert is enabled, duplicates will not
be
> allowed. Thus, this is one of the reasons that if you are making your
identity
> column the primary key, you should explicitly do so.
>
while rows
> It will throw an error. However, this can be easily anticipated and
handled by
> adjusting the data type of the column to something larger. It is highly
doubtful
> you will reach the cap of a BigInt.
>
formed. How
ID's are
> Gaps will form and you shouldn't care about it. Identity columns are not
meant
> to provide sequence with no gaps. For that, you should use a different,
custom
> and intentional mechanism. Identity columns are simply a cheap and easy
way of
> providing a unique identifier for each row.
>
up
in
doesn't
have
> Purists would agree and thus do not recommend their use. People like Mr.
Celko
> (and officially Codd) argue that the primary key should be an assemblage
of
> actual data values not some system generated key. However, since it is
highly
> unlikely that you will run out of key values, this reason alone should not
be a
> deterrent. If you thought it was even remotely possible to hit 2 billion
rows
> (the maximum for an int) you can use a BigInt.
>
> Thomas
>
Sunday, February 19, 2012
identity and rollback
This is more of an academic question and it related to SQL Server 2000.
I have a table that has an identity column. If i try to insert a row and
decide to roll back the transaction, the next insert will have a gap in the
numbering sequence of the identity column.
Is there a way to avoid the gap in the numbering?
Thanks.
ShahriarUnfortunately there is no way to avoid these gaps using an identity column.
You would have to implement your own solution according to your requirements
.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Shahriar" wrote:
> This is more of an academic question and it related to SQL Server 2000.
> I have a table that has an identity column. If i try to insert a row and
> decide to roll back the transaction, the next insert will have a gap in th
e
> numbering sequence of the identity column.
> Is there a way to avoid the gap in the numbering?
> Thanks.
> Shahriar|||> I have a table that has an identity column. If i try to insert a row and
> decide to roll back the transaction, the next insert will have a gap in
> the
> numbering sequence of the identity column.
> Is there a way to avoid the gap in the numbering?
Yes, don't use IDENTITY.
The advantage of IDENTITY is that it does not lock the entire table to
insert a row. You get "assigned" a value when you start your insert, and
since others may start an insert after that (but before you commit or
rollback), SQL Server has no choice but to resign your number as "taken"...
Why do you care about gaps?
The IDENTITY value itself has no real meaning - after all, the system
generated it for you. So, if you have a rollback, what does it mean, and to
whom, that there is no row where the surrogate identifier is = 4?
http://www.aspfaq.com/2523
I have a table that has an identity column. If i try to insert a row and
decide to roll back the transaction, the next insert will have a gap in the
numbering sequence of the identity column.
Is there a way to avoid the gap in the numbering?
Thanks.
ShahriarUnfortunately there is no way to avoid these gaps using an identity column.
You would have to implement your own solution according to your requirements
.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Shahriar" wrote:
> This is more of an academic question and it related to SQL Server 2000.
> I have a table that has an identity column. If i try to insert a row and
> decide to roll back the transaction, the next insert will have a gap in th
e
> numbering sequence of the identity column.
> Is there a way to avoid the gap in the numbering?
> Thanks.
> Shahriar|||> I have a table that has an identity column. If i try to insert a row and
> decide to roll back the transaction, the next insert will have a gap in
> the
> numbering sequence of the identity column.
> Is there a way to avoid the gap in the numbering?
Yes, don't use IDENTITY.
The advantage of IDENTITY is that it does not lock the entire table to
insert a row. You get "assigned" a value when you start your insert, and
since others may start an insert after that (but before you commit or
rollback), SQL Server has no choice but to resign your number as "taken"...
Why do you care about gaps?
The IDENTITY value itself has no real meaning - after all, the system
generated it for you. So, if you have a rollback, what does it mean, and to
whom, that there is no row where the surrogate identifier is = 4?
http://www.aspfaq.com/2523
Identifying the servercontrol in repeater
Hell Sir,
I am using repeater control to show the result after search .and using checkbox control in itemtemplate row .After searchresult i am facing a problem in identifying the checked checkboxes in the itemtemplate of repeater control .
Please provide appropriate solution ...
thanks for ur attention...
Hi,
take a look at this article: the technique's similar for repeaters:Using a comma delimited string with id's as input parameter for a SQL query. But if I remember correctly you shouldn't loop the Rows but Items instead.
Grz, Kris.
|||Hello sir
i am asking about the repeater control
please forward the solution in case of repeater control
|||Hi,
In your code-behind file, try to use FindControl to get the checkbox on your page.See the following sample.
for (int i = 0; i <this.Repeater1.Items.Count; i++) {bool ifchecked=(Checkbox(this.Repeater1.Items[i].FindControl("Checkbox1"))).Checked;}Hope that helps. Thanks.
Identifying the ErrorColumn (in rejected data rows)
I've noticed that when a row fails to transform/parse, (I.E., the data was truncated) you can use the DFT audit component to see which column contained the error. However, this error column displays the ID of a particular column, NOT the actual order in which the columns are setup (defined in the conn mgr).
Is there any way to modify this so that it will show the sequencial column number, or at the very least, the externalmetadatacolum number?
thanks,
I had hoped that it would be easy to use a Script Component to add Error Description (in place of ErrorCode) and Column Name (in place of ErrorColumn) to a standard error output.
However, while the former is easy (using Me.ComponentMetadata.GetErrorDescription), the latter is not. The column ID that you have in ErrorColumn is the ID of the column in the column collection of the previous component, where the error occurred, and is no longer the same if you choose to include the column of the same name among the input columns of your Script component. So you can't grab the column object to get its name or any of its other properties and, of course, you can't grab a runtime reference to any other components in the data flow.
-Doug
Subscribe to:
Posts (Atom)