Wednesday, February 10, 2010

Custom Batch Separator in T-SQL

I happen to see below piece of code recently which I couldn't compile whereas I saw one of my fellow developers running this in his machine

create table #test
ID int identity(1,1),
Val varchar(100)

insert into #test
values('test val')
put 1000

select * from #test

drop table #test

I had never heard of put statement in SQL. After looking closely at the code, I understood that its been used as a batch separator in the above context. Investigating further I came across the fact that GO is just the default batch separator provided by SQL and its possible for us to override this through query options in SQL Management Studio. For this just click on Query->Query Options from top menu and you would be provided with below screen

Changing the default value to whatever value you want makes it batch separator for that particular connection. In the above instance they've used value as PUT.
Please note that any newly opened connection will again default batch separator value back to GO.

EDIT: In SSMS 2008 R2 the option is as below