SQL Homework – December 2021 – Changing collation

Leave a comment

December 2, 2021 by Kenneth Fisher

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,617 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: