Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to determine if an instance of SQL Server is case sensitive or not

How can I find out if an instance of SQL Server 2005 allows case sensitive databases or not?

By case sensitive, I mean case sensitivity of the objects in the database, i.e. the following two statements are not equivalent:

SELECT * FROM TABLE
SELECT * FROM table

I've looked in the property pages of the server (in Management Studio) but I couldn't see it.

like image 578
Justin Avatar asked Sep 17 '09 15:09

Justin


3 Answers

SELECT DATABASEPROPERTYEX('DatabaseNameHere', 'Collation') SQLCollation;

Returns "SQL_Latin1_General_CP1_CI_AS", the CI is what indicates case insensitivity

like image 62
Josh Stodola Avatar answered Sep 29 '22 12:09

Josh Stodola


In Management studio, right click on Instance in the object explorer and then click on "properties" to see the server properties. In the "General" section look at the collation. The default case insensitive setting is SQL_Latin1_General_CP1_CI_AS. The case sensitive setting is Latin1_General_CS_AS.

like image 31
Matt Wrock Avatar answered Sep 29 '22 12:09

Matt Wrock


The collation of a database can be different to the server collation. There is no restriction.

When you CREATE DATABASE, you specify it there or it assumes the collation of the model databases (which should be the server collation).

SELECT
    DATABASEPROPERTYEX('MyDB', 'Collation'), 
    SERVERPROPERTY ('Collation')
like image 26
gbn Avatar answered Sep 29 '22 11:09

gbn