I can only speak for the locations I’ve worked at of course, but in all of the places I’ve worked collation is something that rarely comes up. This is probably somewhat different if you live in Europe where you are likely to have applications dealing with multiple languages but I couldn’t say for certain. That said, collation rarely comes up. Until it does. And when it does, it can be really really simple, or it can be a real doozy. Probably best to get an idea of some of what’s involved in changing a collation.
- So first, a little light reading to get an idea of what collation is: Collation and Unicode Support. Make sure you at least know what CS, AS, KS, and WS are and of course their opposites CI, AI, KI, and WI. (25 pts)
- Query a list of databases. Once sorted by name but case sensitive and once sorted by name and case insensitive. (10 pts)
- Run this script. It will create a database and a table in the database.
ALTER DATABASE CollateDB COLLATE SQL_Latin1_General_CP1_CS_AI;
USE CollateDB;
SELECT * INTO dbo.CollateTest FROM sys.databases;
CREATE INDEX ix_CollateTest ON CollateTest(name);
ALTER TABLE CollateTest ADD CONSTRAINT ck_CollateTest CHECK (Len(name) < 100);
GO
- Check the collation of the columns in the table and compare them to the default collation for the database. Then change the collation of the table (i.e. all of the columns in the table). (15 pts)
- Using a test database, preferably something like Adventureworks or WWI (Wide World Importers) change the collation of the database. (25 pts)
- Now, if you have an instance that you can play with, change the collation for the instance. (25 pts)
- Now create a new database with a couple of tables in it. Check the collation of the new database (and columns)
- For some bonus points explore what collations are used when you create temp tables and table variables. Try creating them from the context (use USE) of databases with different default collations.