April 7, 2009 at 7:28 am
Jeff Moden (4/6/2009)
jgrubb (4/6/2009)
By definition, identity has gaps, check BOL. If you need sequential id's without gaps, identity is not the way.Ummm.... you're gonna have to show the BOL link for that one. It is NOT by definition that an IDENTITY column will have gaps. They only occur if a rollback or deletion has occurred. From BOL...
Note:
If an identifier column exists for a table that has frequent deletions, gaps can occur between identity values. Deleted identity values are not reused. To avoid such gaps, do not use the IDENTITY property.
... and, there are very, very few good reasons to avoid gaps.
Well, I did learn something here. Hadn't looked at the exact implementation of identity for years (maybe 10). It used to be that every time the db started, it would reserve a block of Identities in memory (I can't remember the formula offhand). It would only increment the pointer to the next block when it ran out in memory ones. If some thing untoward happened, it would dump the whole block and start with the next pointer. Sounds like they don't do it that way now.
Because of that, I never used Identities for sequential numbering, if gaps were a problem.
However, you did also make my point. If a rollback could leave a gap, they aren't *perfect* for sequentials. Far better than they used to be though.
April 7, 2009 at 12:49 pm
Jeff Moden (4/6/2009)
jgrubb (4/6/2009)
By definition, identity has gaps, check BOL. If you need sequential id's without gaps, identity is not the way.Ummm.... you're gonna have to show the BOL link for that one. It is NOT by definition that an IDENTITY column will have gaps. They only occur if a rollback or deletion has occurred. From BOL...
Note:
If an identifier column exists for a table that has frequent deletions, gaps can occur between identity values. Deleted identity values are not reused. To avoid such gaps, do not use the IDENTITY property.
... and, there are very, very few good reasons to avoid gaps.
its for a business app reason, since my list is so large, and i have multiple apps hitting the same table, each of them grab a chunk of records (1000 or so) and it needs to be in order. I use another table with a singlerow that controls the last index that an application chose from. The problem is when i get gaps, in my code, im expecting 1000 records, if i get less, its more less of a waste of a trip because i dont want 10 records , or sometimes none at all) I suppose there could have been another way to do this by doing top 1000 where id > x and less then < but its too late for that implimentation
also having my app check for duplicate manually first would definately slow performance and would expect this to be a database process.
Also i wouldnt want to turn the constraint off and end up having dupes, i need to use this table live without dupes.
Im assuming my rollbacks are because all my apps are inserting data into a unique column asynch and one gets in, while another was almost getting in, but then gets kicked out cause some other app beat it to it?
Michael Evanchik
April 7, 2009 at 1:08 pm
you want a sequential unique ID, use Row_Number():
Select Row_Number() over(order by tbl.AutoIncID asc) as ID,
tbl.value
FROM tbl
How's that?
Cheers,
J-F
April 7, 2009 at 1:20 pm
xgcmcbain (4/7/2009)
its for a business app reason, since my list is so large, and i have multiple apps hitting the same table, each of them grab a chunk of records (1000 or so) and it needs to be in order. I use another table with a singlerow that controls the last index that an application chose from. The problem is when i get gaps, in my code, im expecting 1000 records, if i get less, its more less of a waste of a trip because i dont want 10 records , or sometimes none at all) I suppose there could have been another way to do this by doing top 1000 where id > x and less then < but its too late for that implimentationMichael Evanchik
Not quite true... in fact, as you're finding out, that's a huge pain in the neck. Use the ROW_NUMBER method that J-F suggested in the post immediately above this one.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2009 at 1:40 pm
Another thing you can do is import into a separate table, then insert from there into the final table. That'll get rid of your duplicates and your gaps. Does add a step to the import process, but a straight Insert Select is usually pretty easy to write and pretty fast to run.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 5:29 pm
as long as i row_number works in a where statment thats golden thank u
April 7, 2009 at 6:40 pm
xgcmcbain (4/7/2009)
as long as i row_number works in a where statment thats golden thank u
You'll have to wrap it in a CTE or Join Subquery to use it in the WHERE clause.
WITH CteSysTables(RowNumber, Id, Name)
AS
(
SELECT
Row_Number() OVER (order by object_id) As RowNumber,
object_id AS Id,
Name
FROM sys.tables
-- Fails - Windowed functions can only appear in the SELECT or ORDER BY clauses.
--WHERE Row_Number() OVER (order by object_id) < 5
)
SELECT
RowNumber,
Id,
Name
FROM CteSysTables
WHERE RowNumber < 5
April 8, 2009 at 7:04 am
xgcmcbain (4/7/2009)
as long as i row_number works in a where statment thats golden thank u
You can create a procedure that will take @Start and @End as parameters, and query your tables that way, I've made a sample for you to use, so you can understand what I mean.
Hope it helps,
CREATE TABLE Test (
ID INT IDENTITY ( 1 , 1 ),
VALUE VARCHAR(50) NULL)
INSERT INTO test
SELECT TOP 50000 sc1.name
+ ' plus '
+ sc2.name
FROM sys.columns sc1
CROSS JOIN sys.columns sc2
DECLARE @Start INT,
@End INT
SELECT @Start = 1500,
@End = 2499
SELECT NewTbl.ID,
NewTbl.VALUE
FROM (SELECT ROW_Number()
OVER(ORDER BY t.ID ASC) AS ROWID,
t.ID,
t.VALUE
FROM test t) AS NewTbl
WHERE ROWID BETWEEN @Start AND @END
ORDER BY ROWID ASC
Cheers,
J-F
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply