Randomly selected quiz question (want a different question? Click here):
I have long been advocating the use of identity columns to generate sequential numbers without sacrificing concurrency. Basically, the trick is to use a table with only an identity column and insert a dummy row for the sole purpose of obtaining the generated identity value. The basic method boils down to this (I first described it here in 1998 as part of an approach to avoid identity gaps, but the number-generator approach can very well be used on its own as well):
create table keytable (dummy_key numeric(10,0) identity)
-- insert a 'dummy' row to generate new number
insert keytable values ()
-- pick up the identity value
declare @new_key numeric(10)
select @new_key = @@identity
-- now use this value in subsequent processing...
There can be various reasons why you'd want to use an identity column this way, despite the fact that avoiding identity gaps isn't really a concern anymore these days (since those gaps can be limited in size, and easily repaired if they occur after all). One possible reason is that you may want to perform some additional work before using the identity value as a primary key (for example, by computing and appending a checksum digit). It may also make coding easier if you can decouple generating numbers from using them.
Anyway, here's the question: a significant improvement to this method above has recently become possible. Do you know how?
Click here for the answer.
More ASE quiz questions: 2013 - 2012 - 2011 - 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001
For a randomly selected quiz question, click here.