FILTER
-
Nice and easy. Thanks!
-
I'm confused. The result I'm getting is 8, and I'd be curious to know why. I tried both Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS collations - I've had a difficult time finding an authoritative reference, but as far as I can tell one of these should be the default collation for England (the question references an English server, not an American server). No version is mentioned, though this clearly won't run on anything earlier than 2008. I'm running a 2005 server, so I had to alter the syntax slightly:
CREATE TABLE #test
(
col1 INT,
col2 CHAR(2) COLLATE Latin1_General_CI_AS
)
GO
INSERT INTO #test
SELECT 1,'AB'
UNION ALL
SELECT 1,'AB'
UNION ALL
SELECT 2,'Ab'
UNION ALL
SELECT 1,'AB'
UNION ALL
SELECT 3,'aB'
UNION ALL
SELECT 4,'ab'
UNION ALL
SELECT 5,'XY'
UNION ALL
SELECT 6,'xy'
GO
SELECT COUNT(*)
FROM #test
WHERE col2 IN ('AB', 'aB', 'xy');
DROP TABLE #test
This returns 8 with either collation. Can anyone explain why that might be? For curiosity's sake I tried SQL_Latin1_General_CP1_CS_AS and got 5.
ron
-----
a haiku...NULL is not zero
NULL is not an empty string
NULL is the unknown -
Hugo Kornelis (9/18/2012)
... This may all be due to my English reading skills (I'm not a native English speaker), but in any case, I wanted to add a comment to clarify this in case ohers have the same misunderstanding ...Hugo, don't worry about not being a native speaker. Your command of English is better than most natives. But, you point is well taken. Words can sometimes be misinterpreted, as they are inexact tools for communication. Such is their beauty and their shortcoming. Thanks for adding greatly to the discussion, as you always do.
Lokesh, thanks for the question. It was easier than some. But, complicated questions don't always lead to more learning.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
ronmoses (9/18/2012)
I'm confused. The result I'm getting is 8, and I'd be curious to know why.(...)
This returns 8 with either collation. Can anyone explain why that might be? For curiosity's sake I tried SQL_Latin1_General_CP1_CS_AS and got 5.
Double-check the INSERT statements in your code. You have inserted a total of three copies of the (1, 'AB') row; that should be only one.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
ronmoses (9/18/2012)
I'm confused. The result I'm getting is 8, and I'd be curious to know why.Unless I'm missing something, it's because you repeated the SELECT 1,'AB' three times.
(Edited to hide my rustiness with XML!)
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
Hugo Kornelis (9/18/2012)
Double-check the INSERT statements in your code. You have inserted a total of three copies of the (1, 'AB') row; that should be only one.Whoops. Copy-paste error while converting the VALUES to SELECT. Big duh on my part, thanks.
Ron
-----
a haiku...NULL is not zero
NULL is not an empty string
NULL is the unknown -
In addition to what Hugo Kornelis supplied here is a reference listing the default collations for all languages and countries.
Default Collations in SQL Server Setup
http://msdn.microsoft.com/en-us/library/ms143508(v=sql.105).aspx
-
Thanks for a question I didn't have to question my gut instinct answer about.
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
+1 🙂
Best,
Naseer Ahmad
SQL Server DBA -
Nice back to basics question. I am astounded that at the time of my posting there are 111 (14%) that answered this wrong. I can understand those that answered 3 because they may have not known the default was case insensitive, but the ones that answered none is amazing. It seems we can't review the basics enough.
Well done Lokesh, looking forward to your next submission.
_______________________________________________________________
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/ -
Thanks for the question
-
Nice easy question.
I'm sure I selected 6, but the system told me I was wrong. I guess I didn't select what I though selected - senility setting in, perhaps?
Tom
-
Hugo Kornelis (9/18/2012)
Good question; the explanation could have been better."To make this query case sensitive we need to either set the collation level to Case Sensitive or use 'COLLATE SQL_Latin1_General_CP1_CS_AS' in the select query with col2"
To me, the above sentence reads as if there are two methods. The second one is to force a different collation in the query (as demonstrated in the profivded code fragment); the first one is to, somehow, "set the collation level". And that, again: to me, sounds as if some SET statement would be used.
This may all be due to my English reading skills (I'm not a native English speaker), but in any case, I wanted to add a comment to clarify this in case ohers have the same misunderstanding,
First, there is no SET statement to control the default collation used in comparisons. The collation used in a comparison will always be the collation of the column used, unless an explicit COLLATE clause is used to override that.
The only way to make this query use a case sensitive collation without specifying it in the table is to change the column's collation. This can be done by adding a COLLATE clause to the CREATE TABLE (or ALTER TABLE) statement. Or it can be done by changing the default collation of the database (using the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement) and not specifying a collation when creating the table. Finally, the last way to get case sensitive comparisons by default is to specify a case sensitive collation when installing the SQL Server instance, then using the defaults when creating database and table. Changing the default collation for an already installed instance is very hard; it requires rebuilding the master.
The link in the explanation does not work, unfortunately. I think the author intended to post two links, but accidentally mashed them together. Here are the links I think he tried to supply:
* On MSDN, the official documentation on COLLATE: http://msdn.microsoft.com/en-us/library/ms184391.aspx
* A recent article on SQLServerCentral about case sensitive searches: http://www.sqlservercentral.com/blogs/nycnet/2012/01/19/case-sensitive-searches-in-t-sql/[/url]
+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 -
Stewart "Arturius" Campbell (9/18/2012)
Good question, thanksHowever, I must agree with Hugo regarding the explanation, espacially with regard to SET.
Hey Stewart/Hugo
Taken your words. Will be more specific with the explanation hereon.
Thanks for pointing that out. There is always scope for improvement 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply