By: Siddharth Mehta | Comments | Related: > SQL Server Management Studio Configuration
Problem
SQL Server collation is an important setting when creating database objects. Once the SQL Server collation has been configured for the instance or database, it is not simple to update the collations and even after fixing collations there can be performance or other unexpected issues. One of the best ways to ensure that collation issues do not happen is to ensure that the collation settings are properly specified in the script that generates the database objects. SQL Server Management Studio (SSMS) by default does not include scripting collation settings. In this tip we will demonstrate how to include the collation settings.
Solution
SSMS provides options to control the script generation behavior, which can be configured to generate collation settings while generating scripts.
In this tip we will create a blank database with specific collation settings and generate a script using SSMS for these objects. After that we will modify the script generation setting in SSMS and compare the scripts for differences. Follow the steps below.
Open SSMS, and create a new database by right-clicking on the database folder in object explorer and selecting the "New Database" menu option. Select a specific collation "Latin1_General_BIN2" as shown below for the new database and click "OK" to create the database.
Right-click on the newly created database, and select Script Database As > Create To > New Query Editor Window. This will generate the script as shown below. Unless you knew there was a collation difference, you probably wouldn't even notice that the collation settings for the CREATE DATABASE are missing in the script.
If you execute this script and create the database, when you check the properties of the database, you will notice that the database has been created with the default collation settings inherited from the server level collation. This becomes a problem in the future as the expected collation is different. Those who are not experienced with this behavior of SSMS would not notice this until it becomes an issue.
It is very easy to fix this scripting behavior. Navigate to the Tools > Options menu in SSMS. Search for scripting options, which you will find under the SQL Server Object Explorer category. You will notice that by default, the "Include collation" setting property is set to "False", which means the collation settings will not be included during script generation. Change the value of this property to "True". You can also change other properties as required. For example, you may want to have the script include checks for the existence of the database object that you intend to create. For this setting you can change the "Check for object existence" property to "True" which by default has the value set to "False".
Generate the script again by right-clicking on the database, and select Script Database As > Create To > New Query Editor Window. This will generate the script as shown below. You will notice that this time the collation settings are included in the script in the CREATE DATABASE section.
With these steps, the script generation behavior will ensure collation and other important settings are included in the script generation process.
Next Steps
- Try to learn more about the configuration options for script generation in SQL Server Management Studio to ensure that there are no other features which may be important for your day to day tasks that are excluded by default. Change the settings and script out objects to see what the differences look like in the scripts.
- Check out these additional resources:
- SQL Server Management Studio Tips
- SQL Server Management Studio Tips, Tricks and Customizations Video Tutorial
- SQL Server Collation Tips
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
