Mini Post: Change Column Collation

Hey guys, this is how you go about changing the collation of a column.

Run the following, putting in your database, column and collation names where appropriate:


ALTER TABLE dbo.YourTable ALTER COLUMN [YourColumn]
            varchar(20)COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL;
GO

Double check that your column collation was change by running the following on your database:

SELECT name, collation_name 
FROM sys.columns 
WHERE name = N'YourColumn';

Mini Post: Change Database Collation

So just to get this one out of the way (was bugging me that I didn't write it as yet), here's how to change the collation of a database. (See bottom of post for links to other related collation posts)

Use only one of the following solutions: EITHER Management Studio OR TSQL.

SQL Server 2008: Change Instance Collation Via Rebuilding The System Databases

Hey everyone, welcome back to the first proper length SQL Something in a while.

In an earlier post I outlined how to get queries to ignore collation. In a future mini post I will show how to change a database's collation.

Today's focus is the instance's collation.

Changing the collation of a SQL Instance is not something to be taken lightly (which is why it's good practtice to choose the correct collation the first time you set up the instance). In order to change it you have (as far as I know) two options:

• Reinstall the instance.
• Rebuild The System Databases.

So without further ado, let's look at the second option.

Mini Post: Get Sizes Of Tables In A Database

Hey all, here's something I've been meaning to put up for a while.

Its a very common question that pops up from time to time so I thought I'd share the answer in one more place on the internet.

Well two answers actually: The Report Solution and The Query Solution.

Mini Post: View Queries Currently Running On Your Instance

Hey all! Below is a quick and easy way you can see what queries are currently running on your instance.


SELECT QueryText.TEXT AS Query,
DB_Name(Requests.database_id) AS DBName,
Requests.session_id AS Session_ID,
Requests.status AS Status,
Requests.command AS Command_Type,
Requests.cpu_time AS CPU_Time,
Requests.total_elapsed_time/1000 AS Time_In_Seconds,
Requests.total_elapsed_time AS Time_In_MilliSeconds
FROM sys.dm_exec_requests Requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS QueryText

The query makes use of the sys.dm_exec_requests and sys.dm_exec_sql_text Dynamic Management Objects (DMOs). Check the links for additional columns that may be useful to you.

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Adjust Queries to Ignore Collation

Heya people, gonna quickly run through how to adjust queries to ignore collation. I will return with another Mini Post to show how to actually change the collation of an instance and a DB.

You may have run into an scenario where your database has a different collation from that of your instance
(for example, the database could have been taken from an instance with a different collation than that of your current instance).

When you attempt to compare two columns, one with collation A and one with collation B, you will get the following error:
"Cannot resolve the collation conflict"
along with the names of collation A and B as well as the comparison operation that was attempted (whether it was '=', BETWEEN', 'IN' etc)

To resolve this, you can set the collation of A (the previously foreign DB) to be the collation of the current instance. In your WHERE clause where you are doing your comparisons place "COLLATE DATABASE_DEFAULT" immediately after the name of the column used for comparison like so:

...WHERE A.Col1 COLLATE DATABASE_DEFAULT in (SELECT B.Col1 FROM B)

No more error. :-)


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)