Martin Smith

17,427 reputation

43578

bio	website
	location	United Kingdom
	age
visits	member for	1 year, 7 months
	seen	1 hour ago
stats	profile views	1,386

Email: stackoverflowmartinsmith@gmail.com

	17,427 reputation	bio	website		visits	member for	1 year, 7 months
43578 badges		location	United Kingdom		seen	1 hour ago

summary answers questions tags badges favorites bounties reputation activity

1,925 Actions

suggestions reviews revisions comments badges posts accepts all

Mar 2	comment	Implementation of stack in MySQL Cross posted here
Mar 2	answered	Selecting with varbinary(max) criteria (in the where clause)
Mar 2	comment	When to use identity increment? What RDBMS are you using? How would you generate the sequential number for the second option?
Mar 2	comment	check constraint that has dynamic list @Catcall - That's true. And in the case that the new constraint is just the old constraint with some additional items in the list it shouldn't cause an integrity problem (as long as the new constraint is created before the old constraint is dropped), It does mean the constraint is untrusted however and can't be used by the optimiser. e.g. For a trusted constraint `Color in ('red','green','blue')` a query on `WHERE Color = 'Purple'` wouldn't touch the table at all.
Mar 2	revised	check constraint that has dynamic list added 609 characters in body
Mar 2	comment	check constraint that has dynamic list @user2080105 - Then you obviously run the risk of getting invalid data such as "fish" stored instead of an expected colour.
Mar 2	comment	check constraint that has dynamic list @user2080105 - It's not exactly a huge change to the model and gives you greater flexibility and efficiency. You can stick with using a check constraint if you really want but as I say in my answer altering this will in fact require `CREATE`-ing a new constraint and will require all rows of the table to be read to validate it. Additionally if you are using a FK it is trivial to add an admin interface that allows new colours to be added as it just adds new rows to that table.
Mar 2	answered	check constraint that has dynamic list
Mar 2	answered	Different results rebuilding an index online and offline
Feb 28	comment	Subquery in the VALUES clause Regarding your `CROSS APPLY ... VALUES` question: You can `CROSS APPLY` `derived_table` as indicated here. And the remarks for `derived_table` specifically indicate the `VALUES` table constructor can be used
Feb 27	comment	Is there a better option than Union All for multiple selects from the same row? @MaxVernon Thanks. I first came across that trick here I think.
Feb 27	revised	Is there a better option than Union All for multiple selects from the same row? added 994 characters in body
Feb 27	answered	Is there a better option than Union All for multiple selects from the same row?
Feb 27	revised	Does SQL Server read all of a COALESCE function even if the first argument is not NULL? added 538 characters in body
Feb 27	comment	Is a join optimized to a where clause at runtime? @AaronBertrand - I doubt it! Don't think I've ever seen anyone use it.
Feb 27	comment	Is a join optimized to a where clause at runtime? One edge case where it does make a difference is if you add a (deprecated) `GROUP BY ALL` in
Feb 27	comment	Is a join optimized to a where clause at runtime? The ANSI 89 syntax for inner join is not being deprecated.
Feb 25	comment	Subquery in the VALUES clause @KennethFisher - Unless perhaps the OP is mistaken about being on 2008 R2. Googling that error message indicates that it wasn't always allowed.
Feb 25	comment	Subquery in the VALUES clause sub queries are fine there. See the grammar for `INSERT`. It allows `VALUES ( { DEFAULT \| NULL \| expression } [ ,...n ] ) [ ,...n ]` where sub queries are an `expression`
Feb 25	comment	Subquery in the VALUES clause Your code works fine for me on 2008 and 2012 both with an empty table and subsequent inserts. I presume there is some good reason you aren't using an `IDENTITY` column here?