February 22, 2016 at 8:26 pm
Comments posted to this topic are about the item CHECKING specific values
February 22, 2016 at 10:24 pm
Ez, Thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
February 22, 2016 at 10:42 pm
This was removed by the editor as SPAM
February 23, 2016 at 1:22 am
This harks back to a similar post a little while back, and as a result I got this right. However, I still fail to see any logic in a process that is told "I only want you to insert values that are in the following list" and then allows one in that cannot be checked because it is "missing" ! To me that NULL should be rejected simply because it cannot pass the test in a positive fashion. You don't get into the club if you ain't got ID !!
And I accept, too, that it would better be achieved by making the column NOT NULL, but that really isn't my point.
February 23, 2016 at 1:51 am
Nice question,
But Not having much information on MSDN for 'NULL' behaviour and incorrect value pass to the constraint.
February 23, 2016 at 5:28 am
Nice question. Check constraints are a way of enforcing data integrity by design that I wish more people used. Thanks.
February 23, 2016 at 6:54 am
Bob JH Cullen (2/23/2016)
This harks back to a similar post a little while back, and as a result I got this right. However, I still fail to see any logic in a process that is told "I only want you to insert values that are in the following list" and then allows one in that cannot be checked because it is "missing" ! To me that NULL should be rejected simply because it cannot pass the test in a positive fashion. You don't get into the club if you ain't got ID !!And I accept, too, that it would better be achieved by making the column NOT NULL, but that really isn't my point.
I agree with this sentiment. I'm sure it made sense in some way for whoever was implementing the construct but for me its just one more bit of trivia to remember when coding SQL, theres plenty of things with SQL and T-SQL I'd do differently, but I'm not the one in the position to ship a database server.
select 1 where null in (1,2,3)
-- returns no rows
*shrug*
February 23, 2016 at 7:15 am
I am shocked at how many people (9% at time of this post) said there would be 4 rows. :w00t:
Excellent question Steve.
_______________________________________________________________
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/
February 23, 2016 at 7:18 am
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.
That is interesting, isn't it? We're currently at 60% correct. I guess the question was harder than I thought.
February 23, 2016 at 7:18 am
I initially selected the "four" option. Then the coffee kicked in. Whew!
February 23, 2016 at 7:40 am
Ed Wagner (2/23/2016)
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.
That is interesting, isn't it? We're currently at 60% correct. I guess the question was harder than I thought.
Actually the 60% isn't much of a surprise as I would except many people to choose 2 or error. But 4 is just shocking. That indicates that nearly 1 in 10 people that frequent the QOTD don't understand the very basics of check constraints. I certainly don't claim to be some sort of expert or anything but wow!!! I feel like posting a "trick" question about how to get the current system time from t-sql. 😉
_______________________________________________________________
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/
February 23, 2016 at 7:58 am
Sean Lange (2/23/2016)
Ed Wagner (2/23/2016)
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.
That is interesting, isn't it? We're currently at 60% correct. I guess the question was harder than I thought.
Actually the 60% isn't much of a surprise as I would except many people to choose 2 or error. But 4 is just shocking. That indicates that nearly 1 in 10 people that frequent the QOTD don't understand the very basics of check constraints. I certainly don't claim to be some sort of expert or anything but wow!!! I feel like posting a "trick" question about how to get the current system time from t-sql. 😉
I was going to select 4, but then I double checked the values used in the constraints and the inserts. I realized that the third value was different. Maybe others made the same assumption and didn't stop to double check.
February 23, 2016 at 8:07 am
Luis Cazares (2/23/2016)
Sean Lange (2/23/2016)
Ed Wagner (2/23/2016)
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.
That is interesting, isn't it? We're currently at 60% correct. I guess the question was harder than I thought.
Actually the 60% isn't much of a surprise as I would except many people to choose 2 or error. But 4 is just shocking. That indicates that nearly 1 in 10 people that frequent the QOTD don't understand the very basics of check constraints. I certainly don't claim to be some sort of expert or anything but wow!!! I feel like posting a "trick" question about how to get the current system time from t-sql. 😉
I was going to select 4, but then I double checked the values used in the constraints and the inserts. I realized that the third value was different. Maybe others made the same assumption and didn't stop to double check.
I guess I can see that too. Especially if not enough coffee yet. You see the constraint and the NULL in the insert so immediately realize that one will be inserted which could be what the author was looking for. I know I have made that mistake enough times with QOTD.
_______________________________________________________________
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/
February 23, 2016 at 8:19 am
So does CASE not matter here? All of the constraints were ALL CAPS but the inserts used TitleCase.
February 23, 2016 at 8:30 am
IowaDave (2/23/2016)
So does CASE not matter here? All of the constraints were ALL CAPS but the inserts used TitleCase.
It would if you were using a case sensitive collation. But the QOTD always assumes the default and the default collation is case insensitive.
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply