sqlvogel

1,630 reputation

612

bio	website
	location
	age
visits	member for	2 years, 1 month
	seen	yesterday
stats	profile views	71

	1,630 reputation	bio	website		visits	member for	2 years, 1 month
612 badges		location			seen	yesterday

summary answers questions tags badges favorites bounties reputation activity

120 Actions

suggestions reviews revisions comments badges posts accepts all

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.