1,880 reputation
713
bio website
location
age
visits member for 3 years, 8 months
seen 4 hours ago

Jul
1
answered What are the top 5 design decisions which need to be made when implementing a database?
Jun
29
comment What are some common and useful fields for a users table in a database?
Don't store password. Store the hash only.
Jun
29
comment MySQL: Why is auto_increment limited to just primary keys?
+1 for the fact that auto_increment isn't limited to PKs. Not sure why you think using negative numbers for a surrogate key leads to "huge problems" though.
Jun
29
answered MySQL: Why is auto_increment limited to just primary keys?
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.