Correct method to make DB case sensitive 1

[SantaMufasa]

We have SQL Server 2005 running on Windows 2003. The installation (i.e., all its databases) is currently "case insensitive"; we must change to "case sensitive" with the least amount of impact on the installation and its databases.

What is the least-risky, easiest method to achieve this goal?

[For this relative SQL Server "illiterut", please guide me explicitly (i.e., actual clicks or commands). I, of course, have admin privileges in Enterprise Manager.]



Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[gmmastros]

Santa,

I suggest you look at this article:

http://www.databasejournal.com/features/mssql/article.php/3302341

(particularly page 2, where it shows how to change the collation.) If this doesn't help, let me know and I will look into it when I get back in to work tomorrow.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SantaMufasa]

George,

Thanks for posting the URL. The content was not, however, explicit about how to effect this change for SQL Server 2005.

Can you (or anyone else) offer a reliable route/reference to achieve this objective?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[gmmastros]

Santa,

My apologies for not being particularly helpful with that link. I was at home when I responded last night and didn't have SQL 2005 handy.

Case sensitivity in SQL Server is dependent on the collation. You can determine the collation of the server by opening a query window (within the management studio) and running the following command.

[tt][blue]Select SERVERPROPERTY ('collation')[/blue][/tt]

This is the default collation and probably won't help you much (but I'm getting to that). When I run this on my server, I get SQL_Latin1_General_CP1_CI_AS. Where you see CI, that indicates "case insensitive". I think this only affects new databases that are created.

You can see the collation for an existing database by running the following commands:

[tt][blue]
Use [!]DatabaseName[/!]
Select convert(sysname,DatabasePropertyEx(db_name(),'Collation'))[/blue][/tt]

The collation for the server and the collation for the database probably match, but they don't need to.

You can change the default collation for a database by running the following command.

[tt][blue]Alter Database [!]DatabaseName[/!] Collate SQL_Latin1_General_CP1_CS_AS[/blue][/tt]

I think that by changing the collation on a database, that it will ONLY affect new tables and fields added to the database. If you want to change the collation for an existing field within a table, you would issue the following command:

[tt][blue]Alter Table [!]TableName[/!] Alter Column [!]ColumnName[/!] VarChar([!]50[/!]) Collate SQL_Latin1_General_CP1_CS_AS[/blue][/tt]

If you want the entire database to have that collation, you would have to run this command for every field in every table that has a datatype of char, nchar, varchar, and nvarchar.

Alternatively, you can use the GUI within the management studio to do this.

1. Open Management Studio
2. Connect to your server
3. click View -> Object Explorer
4. drill down to your database
5. open 'tables'
6. right click your table.
7. click 'modify'

A list of fields will be shown. Near the bottom of the window, you will see 'column properties'. Underneath column properties, you will see information regarding the column. in there, you will see collation. It is probably set for <database default>, but you can change it (for the char, nchar, varchar, and nvarchar fields).

I don't really know to what extent you want this case sensitivity issue implemented for. However, I would like to point out that you can 'change' the collation during the course of a query so that it is used ONLY for comparison purposes. Here's how.

Select FieldList...
From   Table
Where  Field [!]Collate SQL_Latin1_General_CP1_CS_AS[/!] = 'Test'

This will not change the collation on the server, database, or even field. It will, however, change the collation for the duration of the query (for comparison purposes).

I hope you find this information useful. If anything is unclear, let me know and I will try to explain further.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SantaMufasa]

Thanks, George, for your thorough and high-quality response. Hava

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[gmmastros]

Thanks Santa. I'm glad I could help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom