July 10, 2012 at 3:02 pm
I encountered, what to me was strange, on a SQL 2005 server today. I ran a query that resembled...
select * from table
where name = 'MIKE'
which returned 0 records. I knew records existed so I ran
Select * from table
where name = 'mike'
which returned the records I expected. My question is why when the coallation is set to SQL_Latin_1_CP1_CI_AS, would Case make a difference. I've tested, so I have determined that this is normal. I just don't understand why. If someone could enlighten me I would be appreciative. Stuff like this screws with me until I figure it out, so you'll help me sleep better 🙂
July 10, 2012 at 3:15 pm
Collation can be set at several levels, server, database, column. You may want to do some checking just to be sure.
July 10, 2012 at 3:19 pm
Here's a query to show column level collations
Select t.name as TableName, c.name as ColumnName, ty.name as DataType, c.max_length
,'('+ convert(varchar,c.precision) +','+ convert(varchar,c.scale)+')' as Precision_Scale
,d.definition as DefaultConstraint
,c.collation_name
From sys.tables t
Inner Join sys.columns c
on t.object_id = c.object_id
Inner Join sys.types ty
on c.system_type_id = ty.system_type_id
Left Outer Join sys.default_constraints d
on d.parent_object_id = c.object_id
and d.parent_column_id = c.column_id
Order By t.name,c.column_id;
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
July 11, 2012 at 2:13 pm
Thank you so much for clearing that up for me. I found the issue was that the column was set to Latin1_General_BIN which is case sensitive. Now I'm going through the process of finding out why, etc....
Thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply