Create objects in SQL Server 2012 with appropriate collation

In this topic I show You how create in SQL Server object with any collation or change collation in existing object.

First check which collation is default for your databases, so collation of instance SQL Server. How check that this database has this collation?
Right click the instance node in Object Explorer window. Choose Properties from the list.

In Properties window You see name of collation.

So the default collation is Polish_CI_AS : case insensitive and accent sensitive.
After this check, we create database with Polish_CS_AS. Let name it mydb.

CREATE DATABASE mydb  COLLATE Polish_CS_AS;

Check collation for this database.

If You create table, in column  collation will be the same.
So create table in this mydb database stores names of people.

USE mydb;
GO
CREATE TABLE tab 
( 
  id int,
  name varchar(100)
);

How check that columns of this tab table has the same collation as mydb database?
Right click the name of column in table node in Object Explorer window. Choose Properties from the list.

In General tab in Column Properties window You see collation of this column of table.

Insert two records into tab table.

INSERT INTO tab VALUES(1,'Anna');
INSERT INTO tab VALUES(2,'anna');

And sort it data according to name column.

SELECT id, name FROM tab 
ORDER BY name;

You see names from low letter to big letter:

How change collation in this table for name column?
You may alter this table.

ALTER TABLE tab
ALTER COLUMN name varchar(100) COLLATE Polish_CI_AS;

And sort data in name column.

SELECT id, name FROM tab 
ORDER BY name;

You see records without differ low or big letter this same version:

Each column in the same table may have diffrent collation.

Create table of people name contains one column with insensitive text and second column with sensitive text.

USE mydb;
GO
CREATE TABLE people 
( 
  id int,
  name1 varchar(100) COLLATE Polish_CI_AS,
  name2 varchar(100) COLLATE Polish_CS_AS,
);

Insert into this table 4 records:

USE mydb;
GO
INSERT INTO people 
VALUES ( 1,'Anna','Miller'),
 ( 2,'anna','miller'),
 ( 3,'anna','Miller'),
 ( 4,'Anna','miller')
;

Sort records in this table according to first name1 column and second name2 column.

SELECT id, name1, name2 
FROM people
ORDER BY name1, name2 ;

And then sort records in this table according to first name1 column and second name2 column.

SELECT id, name1, name2 
FROM people
ORDER BY name2, name1 ;

And sort rows according only one column name1:

SELECT id, name1, name2 
FROM people
ORDER BY name1 ;

And sort rows according only one column name2:

SELECT id, name1, name2 
FROM people
ORDER BY name2 ;

You may check collation of column in people table.
First right click the node of column name and choose Properties.
For name1 column:


For name2 column:

And how change collation for database? Change it for mydb database from Polish_CS_AS to Polish_CI_AI .

ALTER DATABASE mydb
COLLATE Polish_CI_AI;

If after execute this query You see:

Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'mydb' cannot be set to Polish_CI_AI.

You must close all windows of SQL Server(example New Query and so on) and all another connections.
After that it should be work fine.
Now You may check collation your database and columns in its tables.
For database:

Only new objects creating in this database will be have this new collation. The old objects have own collation inherit from old database collation or setted for this objects before this operation changing collation for database.