Tip: Go [count]

Shortcuts & tips from an Impatient DBA #8

This series of short posts will demonstrate some lesser-known features, keyboard shortcuts, and other tips that make my day as a DBA more productive. None of these are groundbreaking super-secret features–they are the little things that I do as part of my daily work that make me more efficient.

 

GO [count]

I should start this one out with a disclaimer that this isn’t something you’re going to use in production code–but it can be really helpful for sample/repro/example code.

I always forget about this one–maybe other people use it all the time, but I always forget it exists.

If you use GO [N], the SSMS (or sqlcmd) will execute the batch N times.

In this example, the CREATE TABLE will happen once, and the INSERT statement will happen 10,000 times. Because the entire batch is run N times (and not just the previous statement), you need the GO after the CREATE TABLE–otherwise it would try to run both the CREATE TABLE and the INSERT 10,000 times and the CREATE TABLE must happen only once.

CREATE TABLE AM2 (Id int identity(1,1),
     Comments nvarchar(100))
GO
INSERT INTO AM2 (Comments)
SELECT REPLICATE('AM2',25)
GO 10000

There are better, more efficient ways to create a table with 10,000 rows–but that’s for another day.