September 10, 2012 at 7:44 am
Thanks for the easy question on Monday to build up my confidence this morning. 🙂
September 10, 2012 at 9:14 am
Thanks for the great question. Together with the link Hugo provided re: data type conversions/precedence, this was a good reminder of what SQL is doing in this type of query.
Rob Schripsema
Propack, Inc.
September 10, 2012 at 9:20 am
NIce question, although of course the ever-forgiving mysql actually returns 1 on both queries.... 🙂
September 10, 2012 at 9:28 am
I think the question would have demonstrated the concept better if it had been the following:
CREATE TABLE dbo.Zip_code
(
id VARCHAR (10) NULL,
Descr VARCHAR (max) NULL
)
GO
INSERT INTO dbo.Zip_code (id, Descr)
VALUES ('1111', 'AAAA')
GO
INSERT INTO dbo.Zip_code (id, Descr)
VALUES ('2222 ', 'bbbb')
GO
SELECT count(*) FROM zip_code WHERE id = 1111
GO
-- FOLLOWED BY
INSERT INTO dbo.Zip_code (id, Descr)
VALUES ('aaaa', 'bbbb')
GO
SELECT count(*) FROM zip_code WHERE id = 1111
GO
September 10, 2012 at 9:40 am
Nice question to start the week off with. Thanks!
September 10, 2012 at 9:40 am
It's good to start the week getting a sweet candy like this.
Thank's for the nice question 🙂
September 10, 2012 at 9:51 am
Thanks for an easy one for my Monday, Anal!
September 10, 2012 at 9:51 am
Raghavendra Mudugal (9/10/2012)
Hugo Kornelis (9/10/2012)
Raghavendra Mudugal (9/10/2012)
Hugo Kornelis (9/10/2012)
Good question - though I am waiting for the first person to claim that "error, error" should also be considered correct because they executed the code on a server or in a database with a case-sensitive collation....Hello Hugo, like how you mean? If the collation was CI then would the result be the 'E, E'? (to my knowledge the first select statement always returns valid row), and if the column collation is also applied to that table it still wouldn't make any difference.
With a case sensitive collation (CS), you'd get errors on both SELECT queries. In the CREATE TABLE and INSERT statement, the table name is Zip_code (uppercase Z). In the SELECT queries, that has changed to zip_code (lowercase z). In a case sensitive collation, those would be different tables.
I got it now, you are focusing on the table name here, and i was looking into the output (and was wondering how the collation will really make this happen and consider 'aaaa' as a valid comparison with number? :w00t: )
One good point here, from anywhere I copy the sql code I quickly change then all to upper case, so here for me the table name was also changed into 'ZIP_CODE' which did not made any difference when I created the new DB with CS collation to check this). Point to be noted here: "Keep the original SQL code as it is."
Thank you, Hugo.
Just a note, I try to write all my code as if the database was case sensitive. If a table is named ZipCode, then that is how I refer to it in my code. If column is defined myZip, again that is how I will refer to it. I find it safer, plus it makes things consistant.
September 10, 2012 at 9:52 am
Easy question, especially after working another post on SSC regarding exactly this issue.
September 10, 2012 at 10:11 am
I posted Question in some what different way.
But modera... changed the question...;-)
September 10, 2012 at 10:11 am
September 10, 2012 at 10:58 am
Lynn Pettis (9/10/2012)
Just a note, I try to write all my code as if the database was case sensitive. If a table is named ZipCode, then that is how I refer to it in my code. If column is defined myZip, again that is how I will refer to it. I find it safer, plus it makes things consistant.
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 11, 2012 at 12:17 am
Lynn Pettis (9/10/2012)
Just a note, I try to write all my code as if the database was case sensitive. If a table is named ZipCode, then that is how I refer to it in my code. If column is defined myZip, again that is how I will refer to it. I find it safer, plus it makes things consistant.
Thank you Lynn. Good Point.
(In general, I wouldn't worry much on the case sensitivity, as most of our databases are CI, so as long as the table/column name is spelled correctly. I mainly generate the SELECT SQL for that/those table/s and take the text and keep only those columns and use it as needed, and if the sql needs to be written manually then intellisense will take care of the name casing as it is represented. I like to keep this casing rule for java and c++ level, at least for me SQL coding should be freetext 🙂 )
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
September 11, 2012 at 5:14 am
Helps that my system at home is setup case sensitive. Forces me to work on being consistant. The problem I run into doing this, of course, are those who post code on SSC without being consistant. I end up fixing the code before I can do anything.
September 15, 2012 at 4:13 am
Nice simple question.
The collation issue added some amusement to what might otherwise have been a little dull.
Tom
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply