Viewing Collation (Server/Database/Column) Via T-SQL

Hey everyone and welcome back to SQL Something!

Real quick one today and that is viewing Collation information via T-SQL (I will do a follow up post eventually on how to do it via Management Studio with pictures).

Now onto the T-SQL.

Getting Database Creation Date Via T-SQL

Hey everyone! Welcome back to SQL Something!

The first post for April is going to be pretty simple. Today we are going to look at how to retrieve the date that a database was created via T-SQL. Could be useful in some situations. I, for instance, just wanted to know when a DB was swapped out for a empty one. The new empty DB wasn't put to use the same day/time it was created, so the timestamps of the actual data in the DB didn't exactly provide me with what I wanted.

Anyway, I digress. Let's look at what sys.databases can do in order to help with our problem.

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: 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. :-)