Overview
The default encoding for SSMS 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.
The preferred encoding is Unicode (UTF-8 with asignature) - Codepage 65001
Steps to resolve
- 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
- 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
All new query windows will default to UTF-8 files
August 6, 2018 at 2:10 pm
This will works with “New Query” command. But if you right click on object then select “Modify” command, it will choose UTF-16 LE (code page 1200) as the file’s encoding.
December 6, 2018 at 1:34 am
That’s good and well for new query windows, but when you right-click on an existing stored procedure in a database and select “Modify”, than SSMS still defaults to UTF-16 encoding when you save that editor window to disk.
Any solution or suggestions for that case?
January 19, 2019 at 7:05 am
I found that I had to Run SSMS as Administrator in order to save the encoding change to sqlfile.sql.
March 13, 2019 at 1:16 am
Doesn’t work in Microsoft SQL Server Management Studio v 14.0.17277.0
May 14, 2020 at 7:19 am
Looks like for SSMS 18 it is:
C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\SqlWorkbenchProjectItems\Sql
There is also one in:
C:\Users\jcoryat\AppData\Roaming\Microsoft\SQL Server Management Studio\18.0\Templates\Sql