How to change the default file encoding in Sql Server Management Studio

Recently I was working on SQL code and data migration engagement for a major Financial institiuation. I was leading the team of SQL developers on this proposition. We have been provided thousands of SQL scripts and hundreds of Stored Procedures along with similar amount of user-defined SQL database functions to convert them in new supporting format. Developers were converting the SQL codes, saving it and committing these to the version-control system GIT.  During Database code analysis and review (which follows immediately after development) to reduce the number of “code smells” that creep into your database builds, the Governance team noticed something wrong with the file encoding.

So, What went wrong?

The file that is used to create a new query window has ANSI encoding but when they save the file it was in UTF-16. Unicode characters were broken 🙁

It didn’t took much time to figure out the issue and resolve, but I decided to write the article explaining steps to resolve.

The default encoding for SQL Server Management Studio sql files is UTF-16, more specifically, either Western European (Windows) - Codepage 1252 or Unicode - Codepage 1200. These encodings play havoc with a git diff as these encoding appear as binary files.

Advanced Save Options

The preferred encoding is Unicode (UTF-8 with signature) - Codepage 65001

How to solve the File encoding in SSMS?

We can change the default file encoding in order to be the one we want in the first place. What I have done was change from ANSI encoding to UTF-8.

  • From within SSMS, open the sql template file named SQLFile.sql, by default in one of these locations: –
    • %ProgramFiles%\Microsoft SQL Server\[Sql Version]\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\
    • %ProgramFiles(x86)%\Microsoft SQL Server\[Sql Version]\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\
    • %ProgramFiles%\Microsoft SQL Server\[Sql Version]\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql
    • %ProgramFiles(x86)%\Microsoft SQL Server\[Sql Version]\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql

My path to the file is C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql where the 140 stands for the SSMS v17 (in my case, right know I’m using the v17.5).

Save File with Encoding

  • Resave using correct encoding:
    • File => Save As
    • Click the arrow next to the Save button
    • Choose the relevant encoding: Unicode (UTF-8 with asignature) - Codepage 65001

Advanced Save Options

All new query windows will default to UTF-8 files

'Coz sharing is caring

4 thoughts on “How to change the default file encoding in Sql Server Management Studio”

  1. What’s up to every one, the contents existing at this web site are
    in fact remarkable for people knowledge, well, keep up
    the good work fellows.

  2. I will right away seize your rss feed as I can not
    in finding your email subscription link or e-newsletter service.
    Do you’ve any? Kindly permit me recognise in order that I may subscribe.
    Thanks. Ahaa, its fastidious dialogue concerning this article here at this blog,
    I have read all that, so now me also commenting at this place.

    Way cool! Some extremely valid points! I appreciate
    you penning this post and also the rest of the site is very
    good.

  3. It’s nearly impossible to find well-informed people about this topic, however, you seem like you know what you’re talking about!
    Thanks

  4. Admiring the time and effort you put into your
    blog and in depth information you present. It’s awesome to come across a blog
    every once in a while that isn’t the same unwanted rehashed
    information. Great read! I’ve bookmarked your site and I’m including your RSS feeds to my Google account.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.