<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Identity Seeding
27 posts, Page 2 of 3
««
1
2
3
»»
Identity Seeding
Rate Topic
Display Mode
Topic Options
Author
Message
Stefan Krzywicki
Stefan Krzywicki
Posted Friday, November 30, 2012 8:48 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 2,418,
Visits: 6,009
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
L' Eomot Inversé
L' Eomot Inversé
Posted Friday, November 30, 2012 9:35 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 6:38 PM
Points: 7,077,
Visits: 7,116
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #1391425
jasona.work
jasona.work
Posted Friday, November 30, 2012 9:37 AM
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 4:30 PM
Points: 467,
Visits: 2,338
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
Eugene Elutin
Eugene Elutin
Posted Friday, November 30, 2012 9:50 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 10:59 AM
Points: 2,525,
Visits: 4,324
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
Stefan Krzywicki
Stefan Krzywicki
Posted Friday, November 30, 2012 9:50 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 2,418,
Visits: 6,009
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
Stefan Krzywicki
Stefan Krzywicki
Posted Friday, November 30, 2012 9:53 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 2,418,
Visits: 6,009
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
Evil Kraig F
Evil Kraig F
Posted Friday, November 30, 2012 12:36 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 5,658,
Visits: 6,100
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
Brandie Tarvin
Brandie Tarvin
Posted Monday, December 03, 2012 8:53 AM
SSCertifiable
Group: General Forum Members
Last Login: 2 days ago @ 12:35 PM
Points: 6,650,
Visits: 5,666
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, MCDBA, MCSA
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
Jeff Moden
Jeff Moden
Posted Monday, December 03, 2012 5:55 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 8:21 PM
Points: 32,893,
Visits: 26,765
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1392204
Jeff Moden
Jeff Moden
Posted Monday, December 03, 2012 6:11 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 8:21 PM
Points: 32,893,
Visits: 26,765
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1392206
« Prev Topic
|
Next Topic »
27 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
