SQL Server – Create a database with the right collation

Background

Trained one of the utilities that I am evaluating against a new SQL Server Instance.

Greeted with a few error messages.

TroubleShooting

Other Environments

I have tried this same utility against other SQL Server Instances and so I know that the application itself is good.

This Environment

I also know that this SQL Server Instance is configured a bit differently.

It’s collation is binary.

And, so a bit precise about letter cases.

Remediation

Out utility drops the existing database and creates a new one upon each use.

Outline

Here are choices we have to address :-

  1. Database
    • Collation
      • Specify a case-insensitive collation during database creation
      • Change Database collation right after creating database; yet preceding any object creation

Tasks

Database

Create Database with case insensitive collation

Syntax

create database [database]
	collate Latin1_General_CI_AS
go

Sample

create database [DBLab]
	collate Latin1_General_CI_AS
go

Create Database and change collation before creating objects

Syntax

create database [database];
go

alter database [database]
	collate Latin1_General_CI_AS
go 

Sample

create database [DBLab];
go

alter database [DBLab]
	collate Latin1_General_CI_AS
go

Summary

See some things are easy to do if …

  1. Application is open source
  2. Application is designed to be extensible
  3. If error is determined before too late

 

Leave a Reply

Please log in using one of these methods to post your comment:

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