1,816 reputation
713
bio website
location
age
visits member for 3 years, 4 months
seen Aug 16 at 21:18

Jun
28
comment Can I losslessly decompose this table?
What are the key(s) in your original table? What dependencies is it supposed to satsify? You seem to be saying that child_id->parent_id, in which case child_id and parent_id cannot both be part of the same key in that table.
Jun
27
answered Fact table foreign keys null?
Jun
23
comment Cartesian Product SQL / Inserts with multiple Values Clauses
Technically a CROSS JOIN in SQL isn't the same as the Cartesian Product but CROSS JOIN may be what you want. A comma (,) is the alternative syntax that can be used instead of CROSS JOIN (not all SQL versions support CROSS JOIN but most do support the "comma join" syntax)
Jun
21
comment Emulate a TSQL sequence via a stored procedure
@Hogan, IDENTITY_INSERT has local scope so there is no race condition when that happens. There's no reason to ask these types of questions here - you can test out such scenarios using debug breakpoints in Management Studio.
Jun
16
comment Emulate a TSQL sequence via a stored procedure
In the circumstances you describe B would get 501, A would get 502, which you say is correct. The bit you got wrong is where you say "A runs the first 4 lines (setting ID to 50)" That could not happen because the IDENTITY property doesn't allow the current IDENTITY value to be rolled back, even using IDENTITY_INSERT. The highest number of the identity sequence is always preserved. It's easy enough to try it and confirm that yourself.
Jun
15
comment Emulate a TSQL sequence via a stored procedure
@Hogan, Using my code the value returned is always higher than any previous value returned and higher than the value passed in (unless you TRUNCATE or reseed the IDENTITY column). I understood that to be what you wanted. Please try it yourself and let us know if you need more help.
Jun
14
comment Emulate a TSQL sequence via a stored procedure
Interesting. I get different results (on 2008r2). Can you post the full repro with DDL and state your build/version.
Jun
14
awarded  Editor
Jun
14
comment Emulate a TSQL sequence via a stored procedure
@Hogan, I've modified my suggestion to handle the input parameter. It's mostly untested though.
Jun
14
revised Emulate a TSQL sequence via a stored procedure
added 118 characters in body
Jun
14
comment Why are Denali sequences supposed to perform better than identity columns?
I know it doesn't answer your question but aside from any performance difference, sequences have other advantages. For example a sequence doesn't stop you updating the target column, which is a very inconvenient limitation of IDENTITY.
Jun
14
awarded  Commentator
Jun
14
comment Emulate a TSQL sequence via a stored procedure
You beat me to it. Note that SQL Server 2011 includes SEQUENCE functionality so the requirement to invent your own should go away soon (not before time).
Jun
14
answered Emulate a TSQL sequence via a stored procedure
Jun
3
revised database-design wiki excerpt
My edit focuses on defining database design rather than the development process. The time and sequencing of design within the development process is irrelevant and out of place in the excerpt.
Jun
3
comment In SQL, is it composite or compound keys?
Walter's edit is an improvement because it makes the point that a compound contains references.
Jun
3
suggested suggested edit on database-design tag wiki excerpt
Jun
2
awarded  Critic
Jun
2
comment In SQL, is it composite or compound keys?
"Key" is just short for "candidate key". The textbook definition of a candidate key is and always has been a "minimal superkey". References: Date's Dictionary pg17 or The Alice Book if you are in any doubt about the definition of a key.
Jun
2
comment In SQL, is it composite or compound keys?
I think you missed the relevant point though, which is that a compound key is made up of keys from other entities. A proper subset of a key can't possibly be a key. As I'm sure you know, a key is required to be minimal (within the table of which it is a key) - so if you remove any attribute from it then it wouldn't be a key any more.