November 6, 2013 at 8:55 pm
Comments posted to this topic are about the item COUNT, COALESCE, ISNULL, DISTINCT
November 6, 2013 at 9:56 pm
Very good question 😛
Thanks Steve 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 6, 2013 at 10:31 pm
Steve,
you state 'NOTE: You will notice that James was case insenstive (COLLATE SQL_Latin1_General_CP1_CI_AS )' in your answer but this is very much site specific.
(I answered on the basis that you were assuming a case insensitive environment.)
If the database one was playing in was defined with collation SQL_Latin1_General_CP1_CS_AS (for instance), as inherited from the model database on creation, then the results would be difference up front.
To avoid ambiguity, you could have forced the collation on table creation with:
CREATE TABLE TEST ( NAME VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS);
Regards, Murray.
November 7, 2013 at 12:50 am
murray-906152 (11/6/2013)
Steve,you state 'NOTE: You will notice that James was case insenstive (COLLATE SQL_Latin1_General_CP1_CI_AS )' in your answer but this is very much site specific.
(I answered on the basis that you were assuming a case insensitive environment.)
If the database one was playing in was defined with collation SQL_Latin1_General_CP1_CS_AS (for instance), as inherited from the model database on creation, then the results would be difference up front.
To avoid ambiguity, you could have forced the collation on table creation with:
CREATE TABLE TEST ( NAME VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS);
Regards, Murray.
+1
Anyway, good question. 🙂
November 7, 2013 at 12:50 am
This was removed by the editor as SPAM
November 7, 2013 at 2:10 am
I knew the CS vs CI "default" point would be raised as soon as I read the question.
However, as the only different answer you get with a CS environment wasn't one of the options then it was pretty safe to assume we were in a case-insensitive world.
November 7, 2013 at 2:12 am
Thanks for the question!
Considering that it states "choose 3 answers" and there was no name "JAMES", for me was pretty clear that the author assumed a case insensitive scenario.
November 7, 2013 at 3:42 am
Richard Warr (11/7/2013)
I knew the CS vs CI "default" point would be raised as soon as I read the question.However, as the only different answer you get with a CS environment wasn't one of the options then it was pretty safe to assume we were in a case-insensitive world.
+1. Out of few SQL boxes, the box in which i tested this box was CS env, though i understood that by default Steve had CI in mind. Good question. 🙂
November 7, 2013 at 7:19 am
Nice one...
November 7, 2013 at 7:25 am
Good question, even with the case sensitivity issue left open. As a general rule, better to specify server settings that were in place, than to expect answerers to make the correct assumptions.
Thanks for the question Steve!
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
November 7, 2013 at 8:09 am
Good question.
It's generally not a good idea to leave the case sensitivity issue unspecified as a large number of people override the installation default so that use Model database having a case sensitive database collation, resulting in ever database having that case sensitive database collation unless it is explicitly overridden. However in this casethe answer options didn't allow for case insensitivity, so it just meant people had to do that little bit of extra work to get it right.
Point 5 in the explanation, although it is a direct quotation from BOL, is actually incorrect: the definition of @@rowcount is a bit more complicated that that, and in this case no rows are affexted but 4 rows are read and it returns 4, not 0. BOL is sometimes a bit sloppy with its definitions.
Tom
November 7, 2013 at 8:19 am
sqlnaive (11/7/2013)
Richard Warr (11/7/2013)
I knew the CS vs CI "default" point would be raised as soon as I read the question.However, as the only different answer you get with a CS environment wasn't one of the options then it was pretty safe to assume we were in a case-insensitive world.
+1. Out of few SQL boxes, the box in which i tested this box was CS env, though i understood that by default Steve had CI in mind. Good question. 🙂
To me, the answer choices showed that it was assumed that the database was CI. I can see other's points though that case sensitivity settings should be declared in the question if the data raises that question. A good question nonetheless.
November 7, 2013 at 8:52 am
Whew, that took a few minutes... Thanks, Steve!
November 7, 2013 at 10:30 am
L' Eomot Inversé (11/7/2013)
Point 5 in the explanation, although it is a direct quotation from BOL, is actually incorrect: the definition of @@rowcount is a bit more complicated that that, and in this case no rows are affexted but 4 rows are read and it returns 4, not 0. BOL is sometimes a bit sloppy with its definitions.
I think @@ROWCOUNT is displaying what was inserted into the table.
November 7, 2013 at 11:14 am
kevin.l.williams (11/7/2013)
L' Eomot Inversé (11/7/2013)
Point 5 in the explanation, although it is a direct quotation from BOL, is actually incorrect: the definition of @@rowcount is a bit more complicated that that, and in this case no rows are affexted but 4 rows are read and it returns 4, not 0. BOL is sometimes a bit sloppy with its definitions.I think @@ROWCOUNT is displaying what was inserted into the table.
You are right; knowing that the statement in BOL (and in the explanation) was wrong I went off in a random direction with two errors:I both applied @@rowcount to the wrong statement, and forgot to count the rows output by that wrong statement. The only case where the number of rows read is used instead of the number of rows affcted is, I think, where a select statement returns rows not because there are no rows that match the selection criteria but because it is an assignment statement and the values assignedare not aggregate.
Thoroughly stupid of me. :blush:
Tom
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply