I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

When are SQL Server schema objects Case Senstive?

I've come across an interesting thing today that i didn't even think could be an issue:

Case senstive object names (tables, columns, triggers, views, etc....).

If your database is created with a Case Sensitive collation then all object names will be Case Sensitive.

I always thought that collations are used for data and not schema but i guess i was wrong.

We had an issue with a view that had a lower case column name in it when it should be uppercase.

 

A bit of code to prove it:

create database testCollation collate Latin1_General_CS_AS -- case sensitive collation
go
use testCollation 

create table Test(Col1 int, col2 int)
insert into Test
select 1, 1 union all
select 1, 2 union all
select 2, 1 union all
select 2, 2 
go
SELECT Col1, col2 FROM Test -- works
SELECT col1, col2 FROM Test -- fails with error: Invalid column name 'col1'.
SELECT Col1, col2 FROM test -- fails with error: Invalid object name 'test'.

drop table Test
use master
drop database testCollation 

Print | posted on Thursday, December 07, 2006 2:05 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: When are SQL Server schema objects Case Senstive?

The schema itself is also stored in database tables. Because the collation affects all tables in the database the schema is affected by this setting, too.

I have experienced this in my latest project. It is very annoying at the beginning (you're used to use case-insensitive SQL), but after a while you get used to it ;)
12/7/2006 3:32 PM | Gerald
Gravatar

# re: When are SQL Server schema objects Case Senstive?

yes exactly.

but somehow i managed to ignore that :))
12/7/2006 4:06 PM | mladen
Gravatar

# re: When are SQL Server schema objects Case Senstive?

Not sure why you see this as surprising?
Enlighten me:-p
12/8/2006 3:02 AM | Jon
Gravatar

# re: When are SQL Server schema objects Case Senstive?

I'm not seeing it as surprising...
It's interesting and shows how a simple collation can give you some trouble.

and if you start working on a new server you know nothing about and you get this
type of error i'm betting you're not going to think of collations first.
12/8/2006 12:35 PM | Mladen
Gravatar

# re: When are SQL Server schema objects Case Senstive?

Well I do agree some people will have problems, but if you come from a programming background its not so bad. C/C++ is case sensitive as well so
int i;
int I;

are two different things. So its not so bad!
12/8/2006 2:18 PM | Jon
Gravatar

# re: When are SQL Server schema objects Case Senstive?

I come from a C/C++ world so when I first came across t-sql, I was surprised and somewhat disappointed that it is NOT case sensitive by default (at least, most databases I have worked with have been case-insensitive)
12/14/2006 3:40 AM | Jesse
Gravatar

# re: When are SQL Server schema objects Case Senstive?

I think what you guys are missing here is that this is exactly the behavior one sees with Oracle. Database object references are case-insensitive, but the data in the database can be case-sensitive. So, if you're on a project where you're replacing Oracle with SQL Server, and there are lots of apps that hit the database, this can be a big deal.

Though it may not be possible, I would like to be able to have a MS-SQL database that treats data in a case-sensitive fashion, but allows queries to refer to database objects ignoring case.
8/12/2009 6:19 PM | Kelly
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET