How to Specify the Collation in a Query in SQL Server (T-SQL)

In database systems, collation determines how data is sorted and compared in a database. For example, when you run a query using the ORDER BY clause, collation determines whether or not uppercase letters and lowercase letters are treated the same.

In SQL Server, collation is specified at the server level, the database level, and the column level.

Collation can also be applied to a character string expression to apply a collation cast. For example, you can use the COLLATE clause in a T-SQL SELECT statement to specify the collation to be used. Like this:

USE Music;
SELECT ArtistId, ArtistName
FROM Artists
ORDER BY ArtistName COLLATE Latin1_General_CI_AI;

That collation uses CI for case-insensitive, and AI for accent-insensitive.
Continue reading

How to Find the Collation in SQL Server (T-SQL)

Collation can be specified at the server level, database level, column level, expression level, and the identifier level. A different method is required for each of these.

Server Level Collation

Here’s how to find the server level collation:

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation';

This returns the collation like this:

Server Collation
----------------------------
SQL_Latin1_General_CP1_CI_AS

Continue reading

How to Change the Collation of a SQL Server Database using T-SQL

This is a quick article to demonstrate how to use Transact-SQL to change the collation of a database in SQL Server.

Here’s the basic code:

USE master;  
GO
 
ALTER DATABASE Solutions  
COLLATE French_CI_AS ;  
GO 

This first changes to the master database, then we use the ALTER DATABASE statement to change the collation of the Solutions database to French_CI_AS collation.
Continue reading

How to Rename a SQL Server Database using T-SQL

While you can certainly rename a SQL Server database in the SSMS GUI by right-clicking on the database name and selecting Rename, sometimes you might prefer (or need) to do it using Transact-SQL.

The most basic way to rename a database using T-SQL is like this:

-- Change to the master database
USE master;

-- Change the database name
ALTER DATABASE Films  
Modify Name = Movies; 
GO

The only problem with this basic script is that it doesn’t change the name of the data files and log files. In most cases you’ll probably want to change the names of these files to match the new name. In that case you can take the following script and replace the database name with your own (as well as its file names and paths):
Continue reading

How to Specify the Location of Data Files and Log Files when Creating a Database in SQL Server

The simplest way to create a database in SQL Server is to use CREATE DATABASE my_database without specifying anything else. When you do this, data files and log files are created in the default location (see how to find the default location).

However, sometimes you might want the data files and log files to reside in a different location. If that’s the case, use the following code example to explicitly state your own location for the database’s data files and log files.

USE master;
GO
CREATE DATABASE Solutions
ON
( NAME = Solutions_dat,  
    FILENAME = 'D:\mssql\data\Solutionsdat.mdf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB )  
LOG ON
( NAME = Solutions_log,  
    FILENAME = 'D:\mssql\data\Solutionslog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB );
GO

That example uses Windows path conventions (starts with a drive letter and uses a backslash).

Continue reading

How to Find the Default File Location for Data Files and Log Files in SQL Server

Any time you create a database in SQL Server, two files are created. One is the data file, and the other is the transaction log file.

The location of these files will depend on whether or not you explicitly specify a location for these files when you create the database. If not, they will be created in the default location.

You can find the default location with the following code:

SELECT
  SERVERPROPERTY('InstanceDefaultDataPath') AS 'Data Files',
  SERVERPROPERTY('InstanceDefaultLogPath') AS 'Log Files'

Continue reading

How to Insert Values into an IDENTITY Column in SQL Server

If you’ve ever tried to insert values into an identity column in SQL Server, you might’ve seen an error like this:

Cannot insert explicit value for identity column in table ‘Artists’ when IDENTITY_INSERT is set to OFF.

This is normal. An identity column is there for a reason. It automatically populates the column with an incrementing value for each row that’s inserted. Therefore there’s no need for you to insert a value into that column.

However, sometimes you do need to insert a value into an identity column. For example, you could be populating the database with data that needs to retain its own identity values. If this is the case, you’ll need to override the IDENTITY property. Here’s how.

Continue reading