June 28, 2010 at 7:34 am
Hugo Kornelis (6/28/2010)
Your confusion comes from posters who claim they "post to get their point". That refers to the fact that every post made also awards a point. Not in the QotD category, but in the "Forum posters" category, as well as in the "Overall leaderboard". They compensate the loss of a point in one category by scoring a point in another category. And so, a system designed to encourase cinmmunity participation ends up resulting in numerous posts that once, a long time ago, when Compuserve still was an alive and kicking community, were mockingly referred to as "<aol>Me too!</aol>"
I must admit that I didn't think about that one! What is the point (no pun intended) of inane posts merely to rack up points? Hopefully others will have also learned this lesson and not cheapen the point system with spurious posts. Not only that, but because I'm following this thread it really clutters my inbox with all the "Me too!" posts.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
June 28, 2010 at 7:43 am
Duncan Pryde (6/28/2010)
Changing the subject somewhat...Although I knew that you can have negative seeds and increments for the IDENTITY property, I've never actually used them myself.
I'd therefore be interested to hear if anyone else had used them, and in what sort of situations?
Duncan
Good question!
One possible use of negative seed and positive increment is to increase the number of values before you run out. With data type int, the maximum value is 2 billion and a bit. If you expect that, during the lifetime of a database, there will be about 2 billion inserts, using int with the default seed and increment is a bit of a gamble. But if you use -2,147,483,648 as the seed and the default increment of 1, you get a total of over 4 billion insert attempts before you get an overflow error, and you still need only 4 bytes, instead of the 8 bytes you'd use if you choose bigint instead.
Another possible use is in replicated architectures. If new rows are inserted at two locations, you need to ensure that there are no duplicate keys generated. One way is to use even numbers for one location and odd numbers for the other (IDENTITY (2,2) and IDENTITY(1,2)) - the benefit of this system is that you can easily expand it to more nodes. But if you're sure there will always be two nodes, using positive numbers (IDENTITY(1,1)) at one location and negatives (IDENTITY(-1,-1)) at another is also a nice possibility.
June 28, 2010 at 7:51 am
The correct answer for the question that was asked is: -1
0
1.
They should of re-phrase the question like this: ...identity (-1,-1)
in order for aswer to be: -1
-2
-3.
June 28, 2010 at 7:55 am
Oopsie. The answer is -1,0,1.
As others have done, I validated that answer against a real database. Did the author of the question validate it before submitting?
Rule number one: Test your code before putting it into production. 🙂
George
June 28, 2010 at 8:04 am
Hugo Kornelis (6/28/2010)
Good question!One possible use of negative seed and positive increment is to increase the number of values before you run out. With data type int, the maximum value is 2 billion and a bit. If you expect that, during the lifetime of a database, there will be about 2 billion inserts, using int with the default seed and increment is a bit of a gamble. But if you use -2,147,483,648 as the seed and the default increment of 1, you get a total of over 4 billion insert attempts before you get an overflow error, and you still need only 4 bytes, instead of the 8 bytes you'd use if you choose bigint instead.
Another possible use is in replicated architectures. If new rows are inserted at two locations, you need to ensure that there are no duplicate keys generated. One way is to use even numbers for one location and odd numbers for the other (IDENTITY (2,2) and IDENTITY(1,2)) - the benefit of this system is that you can easily expand it to more nodes. But if you're sure there will always be two nodes, using positive numbers (IDENTITY(1,1)) at one location and negatives (IDENTITY(-1,-1)) at another is also a nice possibility.
Thanks Hugo - obvious really when you think about it!
Shame SQL doesn't support imaginary numbers, that way we could have have 4 nodes... 😉
Duncan
June 28, 2010 at 8:07 am
Everything necessary regarding the question's typo has already been covered. But I want to thank those who pointed out that "-0" (impossible value, of course, because 0 can be neither negative nor positive) was included in the most correct answer that I chose.
Honestly, I didn't even see the negative sign before the 0 when I first answered the question, but given that the identity increment was positive (1), I picked that choice (-1, -0, 1) as the best answer, along with about 88% of other respondents. But pointing out -0 helped me to realize I should be even more detail oriented when reviewing the QOTD. I wouldn't have changed my answer but I would have been more observant.
Thanks.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
June 28, 2010 at 8:08 am
Duncan Pryde (6/28/2010)
Shame SQL doesn't support imaginary numbers, that way we could have have 4 nodes... 😉
We had a 6-way replication scheme going on at one point--we just used identity columns that were 10 million apart, on the assumption that the data volumes wouldn't require more unique IDs than that. As it happens the replication project was canned for other reasons (mainly excessive bandwidth usage between the sites in question) before our assumption could be tested for accuracy! 😉
June 28, 2010 at 8:17 am
webrunner (6/28/2010)
Everything necessary regarding the question's typo has already been covered. But I want to thank those who pointed out that "-0" (impossible value, of course, because 0 can be neither negative nor positive) was included in the most correct answer that I chose.
Just as an FYI, Negative Zero is used for things in computing.
http://en.wikipedia.org/wiki/Signed_zero
June 28, 2010 at 8:18 am
Looks like a typo mistake either in the question IDENTITY(-1, 1) and answer -1,-0,1.
June 28, 2010 at 8:20 am
paul.knibbs (6/28/2010)
Duncan Pryde (6/28/2010)
Shame SQL doesn't support imaginary numbers, that way we could have have 4 nodes... 😉
We had a 6-way replication scheme going on at one point--we just used identity columns that were 10 million apart, on the assumption that the data volumes wouldn't require more unique IDs than that. As it happens the replication project was canned for other reasons (mainly excessive bandwidth usage between the sites in question) before our assumption could be tested for accuracy! 😉
A couple of years ago, I reseeded one of our nodes on a two-node system to 1 billion, thinking that would last long enough. Trouble is that the first node is up to over half a billion now, so I'll have to think about a different strategy within the next year or so, given the increasing rate at which data is being stored. :rolleyes:
More recently (learning from past experience and all that) on a four-node system I chose the option of larger increments, so went with (1,10), (2,10), (3,10) and (4,10) which should do us for a bit longer, particularly with using bigints instead of ints. I'd still never considered using negative numbers until today though!
Duncan
June 28, 2010 at 8:26 am
The question had a typo that has been corrected. Points have been awarded back
June 28, 2010 at 8:29 am
😀
June 28, 2010 at 8:30 am
mtassin (6/28/2010)
webrunner (6/28/2010)
Everything necessary regarding the question's typo has already been covered. But I want to thank those who pointed out that "-0" (impossible value, of course, because 0 can be neither negative nor positive) was included in the most correct answer that I chose.Just as an FYI, Negative Zero is used for things in computing.
Whoa, thanks. I dimly recall learning about using -0 regarding limits, approaching 0 from the negative side, but I didn't realize signed zeroes are actually used in computing.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
June 28, 2010 at 8:34 am
lbrigham (6/28/2010)
In case anyone hasn't caught it yet, either question or the answer is wrong. If both the seed and increment are -1, then the answer would be -1, -2, -3. As written the answer is -1, 0, 1...or did someone else already mention this?
I think you were the first to mention it. You get all the points!
Sorry to everybody else who will be getting no points. Your jobs will be extra hard today becasue you lost and your spouses will love you a little less when you get home tonight.
Also, global arming will increase by .03733% for everybody who did not post that either the question or the answer is incorrect.
And worst of all, nobody will learn that seeds can be negative and can also be incremented negatively.
June 28, 2010 at 8:36 am
One of the conclusions of todays question is that a lot of people participate in the QotD and in the discussion. I think that's a good sign.
Vera
Viewing 15 posts - 76 through 90 (of 144 total)
You must be logged in to reply to this topic. Login to reply