CHECKING specific values

Question

CHECKING specific values

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Sergiy (2/24/2016)
patrickmcginnis59 10839 (2/24/2016)I got 2 rows if I set xact_abort on, maybe that's his default?
Yep, spot on.
Isn't it anyone's default?
Same as ANSI_NULLS?

I don't change either of those typically. Of course ANSI_NULLS being turned off will not be possible. They are finally going to force it to be like everyone else. https://msdn.microsoft.com/en-us/library/ms188048.aspx

I did try your code with xact_abort on and bingo, 2 rows.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

The defaults with a US SQL Server are what I used. I'm not going to list out client settings, collations, etc. If your settings are different, then you'll need to adjust for these questions.

TomThomson SSC Guru Points: 104773 More actions · Answer 3

Sean Lange (2/23/2016)
I am shocked at how many people (9% at time of this post) said there would be 4 rows. :w00t:
Excellent question Steve.

I'm not shocked; several people will be as careless as me :blush: and notice the NULL and then not bother to check whether the other values are valid, sust assuming (wrongly) that the behaviour with NULL is the only point to the question because that's the thing that most people get wrong and that many people claim (because they haven't a clue what the point of NULL is and why it is provided in SQL) is a bad part of the SQL specification of a CHeCk constraint.

edit: I think the 22% who picked 2 as the answer are are far bigger problem that the 9% who picked 4 - I certinly wouldn't allow any of those 22% to do any schema design until I was certain that they have learned to understand and accept what NULLs are about and how check constraints work and realise that if they don't want to allow NULLs for a column they should declare it as NOT NULL (or, if they want to be stupid about it because they think they know better than Ted Codd, include "COL is not NULL" in their check constraint).

Tom

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

akljfhnlaflkj

SSC Guru

Points: 76202

March 9, 2016 at 4:46 pm

#1864144

Thanks for the question.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Thanks for the question.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP