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:
Siddharth has more than 14 years of experience in the IT Industry, with more than a decade of experience in Business Intelligence and Analytics, for clients banking, logistics, government, Media Entertainment, products, life sciences and other domains. He has been a lead architect for a portfolio of 40+ apps, containing apps in web, mobile, BI, Analytics, data warehousing, reporting, collaboration, CMS, NoSQL and other technologies. He has several certifications and is a published author for online and print-media publications, as well as the MSDN Library.
In his present role, he remains responsible for architecture design, technology stack selection, infrastructure design, 3rd party products evaluation and procurement, and performance engineering. These applications use technologies like Elasticsearch / Lucene, MongoDB, SharePoint 2013 and 2010, jQuery-based framework like Highcharts and GoJS, SQL Server and the Microsoft Business Intelligence stack (SSIS, SSAS, SSRS, MDX, PowerPivot, PowerView), jQueryMobile, Bootstrap, iOS xCode framework, and many others.
- MSSQLTips Awards: Champion (100+ tips) – 2018 | Author of the Year – 2017 | Author Contender – 2016, 2018-2019
