Is GO required before and after SET IDENTITY_INSERT to ON/OFF

Question

Do I need GO before and after SET IDENTITY_INSERT to ON/OFF?

I see a lot of samples, articles use this, but when I skip GO, the script works fine.

GO
SET IDENTITY_INSERT [PropertyValue] ON
GO

-- Some script

GO
SET IDENTITY_INSERT [PropertyValue] OFF
GO

I wonder if this is a good code conversion.

Answer 1

You don't need to. The GO instructs eg. SQL Server Management Studio to execute the query as a separate command. Some clauses need this, and some don't.

For example, it's usually necessary to use GO to separate a CREATE TABLE clause from INSERTs into the same table.

EDIT: Seems this is no longer the case, as pointed out by Damien. CREATE TABLE can be followed by INSERT just fine, and the same applies to DROP TABLE and others. Always improving, eh? :) I've tried some other cases I remember, and it seems you no longer need to have a CREATE PROCEDURE as the last statement in a command either. Times change :)

set identity_insert doesn't need to be separated by GO. It's usually used as safe practice when you're autogenerating SQL code, because you're not necessarily aware of what's going on elsewhere (eg. you might have been creating the table in the previous clause, so to be sure, you put a GO before the identity insert). But it's not necessary, and will not yield you any specific benefit, if you use it in your hand written SQL that you execute as one batch.

Answer 2

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments. Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.