SSMS - INSERT script

SSMS allows you to quickly generate table scripts, such as INSERT, SELECT, and UPDATE, which can save lots of time when writing T-SQL.

However, I noticed that if you have an IDENTITY column the INSERT script will leave that out.

Granted, most of the time you won't be inserting a value into that column, and in fact you need SET IDENTITY_INSERT MyTable ON if you do wish to insert into that column. But I would prefer to see that column in the script, so I can remove it myself according to my needs.

SQL script encoding

During development work with SQL Server, I will generally write a series of change scripts that need to be applied in a specific order. For example, one script might add a new table and another creates a stored procedure that uses that new table. Therefore, when I name the scripts I will prefix them with a sequential number e.g. 01_tblCustomer.sql, 02_prcGetCustomer.sql and so forth. This has several advantages: each script can be written & tested separately; the scripts can be applied in the correct order; the dependencies are recorded correctly in SQL Server.

Originally I would pass the group of scripts to the DBA to be applied to the production database, but as the number of scripts grew the chance for error grew as well.

I wrote a small VBA program to loop thru the script folder & build up a change script, so the DBA only has to apply a single large script. This worked quite well when each individual script was started from a blank query window, but when I generated a Modify script using SSMS there were extra characters at the beginning of the SSMS-generated script; specifically there was an ASCII 255, 254 at the beginning of the first line.

It turns out that when you start a script from a new query window & save it, the default encoding is

Western European (Windows) - Codepage 1252

which corresponds to ANSI or plain text. However, when you use SSMS to generate a script, the default encoding is

Unicode - Codepage 1200

which is the reason that VBA was reading the extra characters from the top of the script file.

So then, when saving the SSMS-generated scripts, all I had to do was to use the Save button option Save with Encoding... and select the 1252 encoding.