/*============================================================================
  File:     ChangingDatabaseCollation.sql

  Summary:  This script creates a sample database TestCollation and then
			shows the impact of changing the database collation.
			
  Date:     March 15, 2005

  SQL Server Version: 9.00.1090 (IDW13)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- This script will help you see the impact of changing database collations.

CREATE DATABASE TestCollation
go
USE TestCollation
go
SELECT DATABASEPROPERTYEX('TestCollation', 'Collation')
	-- the default for us english without changes at the windows layer or changes during SQL Server install is:
	-- SQL_Latin1_General_CP1_CI_AS

	-- To force it use:
-- 		CREATE DATABASE TestCollation
-- 		COLLATE SQL_Latin1_General_CP1_CI_AS

go
-- To see the list of collations use the system function
SELECT * FROM ::fn_helpcollations()
go

-- Since this started from a question on how can I CHANGE collations here's how.
-- Let's change the collation to a case sensitive collation...
ALTER DATABASE TestCollation
	COLLATE Latin1_General_CS_AS_KS_WS
go

-- Confirm the change?
SELECT DATABASEPROPERTYEX('TestCollation', 'Collation')
go

-- Create some objects 
CREATE TABLE dbo.Test
(	Col1	int		NOT NULL)
go

CREATE TABLE dbo.test
(	Col1	int		NOT NULL)
go

CREATE TABLE dbo.TEST
(	Col1	int		NOT NULL)
go

-- Now try to change back to case insensitive...
ALTER DATABASE TestCollation
	COLLATE SQL_Latin1_General_CP1_CI_AS
go

-- This will generate these helpful messages:

-- Msg 1505, Level 16, State 1, Line 1
-- CREATE UNIQUE INDEX terminated because a duplicate key was found for 
-- object name 'sys.sysschobjs' and index name 'nc1'.  The duplicate key 
-- value is (0, 1, test).
-- Msg 5072, Level 16, State 1, Line 1
-- ALTER DATABASE failed. The default collation of database 'TestCollation' 
-- cannot be set to SQL_Latin1_General_CP1_CI_AS.
-- The statement has been terminated.

-- For more info check out BOL under
--     COLLATE
--      "Specifying Collations"

-- fyi...

-- To see the server's setting
EXEC sp_helpsort

-- To see the database's setting
EXEC sp_helpdb dbname

-- To see the table's setting (for each column)
EXEC sp_help tname

-- Clean up the test database
USE master
go
DROP DATABASE TestCollation
go