Categories
Database MSSQL MySQL

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

By Swatantra Kumar

Swatantra is an engineering leader with a successful record in building, nurturing, managing, and leading a multi-disciplinary, diverse, and distributed team of engineers and managers developing and delivering solutions. Professionally, he oversees solution design-development-delivery, cloud transition, IT strategies, technical and organizational leadership, TOM, IT governance, digital transformation, Innovation, stakeholder management, management consulting, and technology vision & strategy. When he's not working, he enjoys reading about and working with new technologies, and trying to get his friends to make the move to new web trends. He has written, co-written, and published many articles in international journals, on various domains/topics including Open Source, Networks, Low-Code, Mobile Technologies, and Business Intelligence. He made a proposal for an information management system at the University level during his graduation days.

5 replies on “How to change the default file encoding in Sql Server Management Studio”

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.

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.

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

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.