.SQL File Format – SQL Server scripts

SQL database dump

SQL Server has no tool like mysqldump built into the server. Its usual text form of a database comes from the Generate Scripts wizard in SQL Server Management Studio, which can include the rows as well as the schema. The file is run with sqlcmd or opened in Management Studio.

The file

The script is in batches ended by GO. Every name, including a type’s, is in square brackets. A comment names each object. The default is often UTF-16 with a byte order mark, so a reader has to go by that rather than assume ASCII.

USE [shop]
GO
/****** Object:  Table [dbo].[fruit]    Script Date: 01/02/2024 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[fruit](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_fruit] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[fruit] ON
INSERT [dbo].[fruit] ([id], [name]) VALUES (1, N'apple')
INSERT [dbo].[fruit] ([id], [name]) VALUES (2, N'pear')
SET IDENTITY_INSERT [dbo].[fruit] OFF
GO

What each part does

LineMeaning
GOEnds a batch. It is a command of the tools, never sent to the server. See Ending statements.
SET ANSI_NULLS, SET QUOTED_IDENTIFIERSession settings, with these usually ON. QUOTED_IDENTIFIER affects how double-quoted text is read.
SET IDENTITY_INSERT … ONLets rows set the IDENTITY column.
N'…'Text in the national character set, UTF-16 in the server.
CAST(… AS Decimal(18, 2))How Management Studio writes some values.
ON [PRIMARY]The filegroup a table or index is stored in.

Statements without semicolons

Statements in a batch need no semicolon, so a reader has to find the next statement from its first word. Each INSERT above starts a new statement simply by coming at the start of a line.

Keys and defaults added later are written as separate batches:

ALTER TABLE [dbo].[fruit] ADD CONSTRAINT [DF_fruit_name] DEFAULT (N'none') FOR [name]
GO
ALTER TABLE [dbo].[pet] WITH CHECK ADD CONSTRAINT [FK_pet] FOREIGN KEY([fruit])
REFERENCES [dbo].[fruit] ([id])
GO

A CREATE PROCEDURE, FUNCTION, TRIGGER or VIEW is the only statement in its batch, so it runs to the next GO, whatever is inside it.