Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Identity Seeding Expand / Collapse
Author
Message
Posted Friday, November 30, 2012 8:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 839, Visits: 7,373
Koen Verbeeck (11/30/2012)
Stefan Krzywicki (11/30/2012)
Koen Verbeeck (11/30/2012)
I once had a fact table that could have more than 2 billion rows, but still less than a bigint, so I started with a negative seed so I could keep my identity column as an int.


Why didn't you want it as a bigint?


Because it takes up more bytes than an int?
On 2^31-1 rows (the maximum if you only take positive identity values) you take up 8,5 GB. If you take a bigint, for the same data, you consume another 8,5GB.


I thought that might be it. Wasn't sure if there was a reason other than space.


--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Post #1391386
Posted Friday, November 30, 2012 9:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, October 19, 2014 11:12 AM
Points: 7,791, Visits: 9,545
Once I included an identity with increment 0 in one of the questions in a set designed to discover whether job applicants had two brain cells to rub together. Does that count as using something other than (1,1), or would that be misusing? The result was rather disappointing, only about 10% said anything like "you can't do that" or "that's an invalid increment".

A couple of times I've used integer identity(-2147483648,1) for table which would have rather a lot of inserts (rather a lot of deletes too - the actual row count at any point of time would be much less than 2 billion, but the identity range needed to be that big).


Tom
Post #1391425
Posted Friday, November 30, 2012 9:37 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 730, Visits: 5,320
If our devs had used the patient ID value as the identity, it would have been a (10,10) seed. The way the current billing application handles patient IDs is increments of 10, then if it's a family practice, family members get added using the base +1. So wife (primary policy holder) might be patid 1270, the husband (on the wifes policy) would be 1271, and the three kids would be 1272-1274.

Luckily, we've never run into anyone who would use all 10 values...
Post #1391426
Posted Friday, November 30, 2012 9:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 2,873, Visits: 5,185
jasona.work (11/30/2012)
If our devs had used the patient ID value as the identity, it would have been a (10,10) seed. The way the current billing application handles patient IDs is increments of 10, then if it's a family practice, family members get added using the base +1. So wife (primary policy holder) might be patid 1270, the husband (on the wifes policy) would be 1271, and the three kids would be 1272-1274.

Luckily, we've never run into anyone who would use all 10 values...


You will have trouble with that:

http://www.people.co.uk/news/uk-world-news/2011/04/17/meet-britain-s-biggest-family-102039-23066431/






_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1391428
Posted Friday, November 30, 2012 9:50 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 839, Visits: 7,373
L' Eomot Inversé (11/30/2012)
Once I included an identity with increment 0 in one of the questions in a set designed to discover whether job applicants had two brain cells to rub together. Does that count as using something other than (1,1), or would that be misusing? The result was rather disappointing, only about 10% said anything like "you can't do that" or "that's an invalid increment".

A couple of times I've used integer identity(-2147483648,1) for table which would have rather a lot of inserts (rather a lot of deletes too - the actual row count at any point of time would be much less than 2 billion, but the identity range needed to be that big).


No, no. I like that. Good interview question.

This is just idle musing on my part. I wanted to find how other people use something basic that has a changeable, but rarely used component.

I think general discussions of this sort can be good for learning and exploring the tech. Also lets you think more about what you do routinely.


--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Post #1391430
Posted Friday, November 30, 2012 9:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 839, Visits: 7,373
jasona.work (11/30/2012)
If our devs had used the patient ID value as the identity, it would have been a (10,10) seed. The way the current billing application handles patient IDs is increments of 10, then if it's a family practice, family members get added using the base +1. So wife (primary policy holder) might be patid 1270, the husband (on the wifes policy) would be 1271, and the three kids would be 1272-1274.

Luckily, we've never run into anyone who would use all 10 values...


I don't think that's the way I'd set that up. I'd be more likely (if it was important to keep the ID similar for some reason) to have a second column for a sub-ID and use both columns for the key. That way you can have an 11 person family and it doesn't matter.


--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Post #1391432
Posted Friday, November 30, 2012 12:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 4:43 AM
Points: 5,401, Visits: 7,512
Typically I do it for merge replication. Considering how much I try to avoid that particular method of replication, I don't do it much.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1391507
Posted Monday, December 3, 2012 8:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 5,584, Visits: 6,380
I've definitely had to seed at a different number, sometimes at zero, sometimes with multiple zeros after the starting number (Invoicing). The only time I've used different increments, though, is in discussing Identity with other people. I've never had to use an increment other than 1 in RL work.

EDIT: To clarify the increment statement, my environment contains all sorts of different systems run by different OSs. Which means having to comply with certain unchangable sized datatypes. Mainframe, for instance, has some fields which are 12 digits long. We can't go smaller for data that feeds to that system or the mainframe will freak out. So we start at 100,000,000,000 (without the commas of course) for certain types of values. (This is a whitewashed example).


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1392008
Posted Monday, December 3, 2012 5:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 35,366, Visits: 31,902
jasona.work (11/30/2012)
If our devs had used the patient ID value as the identity, it would have been a (10,10) seed. The way the current billing application handles patient IDs is increments of 10, then if it's a family practice, family members get added using the base +1. So wife (primary policy holder) might be patid 1270, the husband (on the wifes policy) would be 1271, and the three kids would be 1272-1274.

Luckily, we've never run into anyone who would use all 10 values...



Having just been through a similar nightmare...

<Irony=ON>

WHHHHAAAATTTT??? They didn't see the painfully obvious advantages of storing such unpredictable data as full elemental XML with multiple hierarchical levels that would handle things correctly ad spontaneously even if one of the children decided to have a sex change or wanted to become the father of his mother's children thereby becoming the brother of his own children?

Even if they messed up there, they really could have benefitted from EDI here.

<Irony=OFF>

Can't.... hold.... it... in.... GAAAAHHHH!!!! SPOM!!! ROFLMAO!!!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1392204
Posted Monday, December 3, 2012 6:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 35,366, Visits: 31,902
To answer the original question, yes... we start most tables off at 1000 with the understanding that 0 through 10 are typically reserved for "very special use" and 11 through 1000 are reserved for "other things that may come up".

I've also had to do things like what JasonA and Steve Thompson had to go through (although I fought tooth and nail to not have it so).

I've also reseeded an IDENTITY PK back to the beginning to "freeze" inserts on a table without the use of a trigger.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1392206
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse