Skip to main content

SQL Server GO command and Column Nullability

GO command

You can write sql statements/commands using tools such as SQL Server Management Studio Code Editor and sqlcmd. Then the sql code is executed by SQL Server. Above tools merely send your sql code to the SQL server to be executed.

SQL Server does NOT use GO command. It is never sent to the server. Rather, GO command is used by SQL Server Management Studio Code Editor, sqlcmd and osql utilities.

GO signals the end of a batch of Transact-SQL statements to the SQL Server utilities. source

What GO command do is to instruct the sqlcmd, osql, SQL Server Management Studio Code Editor etc. to send the code up until the GO command as one batch. Basically it is a code (batch) separator. The SQL Server utilities never send a GO command to the server.

Why and When GO command is needed

Before executing the sql code, SQL server validates it. If, for example, (single batch of) sql code includes both CREATE and ALTER statements, when validating ALTER statement, sql server raises error. This is because when ALTER statements is being validated, the table does not exist yet. SQL server thinks ALTER statement is referencing undefined table and raises error.

Below code threw error when ran with sqlcmd:

CREATE TABLE my_table (
ID int IDENTITY(1,1) NOT NULL,
column1 VARCHAR(10) NOT NULL,
column2 DECIMAL(3,0) NOT NULL,
);

ALTER TABLE my_table
ADD column3 DATETIME NULL;

To avoid error, we should execute CREATE and ALTER statements separately.

CREATE TABLE my_table (
ID int IDENTITY(1,1) NOT NULL,
column1 VARCHAR(10) NOT NULL,
column2 DECIMAL(3,0) NOT NULL,
);
-- ↑↑↑ Batch 1
GO
--  ↓↓↓↓ Batch 2
ALTER TABLE my_table
ADD column3 DATETIME NULL;

Nullability

While creating or altering the table, if you leave column nullability constraint unspecified ( whether the column can allow a null value or not), that column might end up with NOT NULL or NULL (nullable) data type depending on the database and session settings.

When you use CREATE TABLE or ALTER TABLE to create or alter a table, database and session settings influence and may override the nullability of the data type that is used in a column definition. Sometimes column left blank might be NOT NULL or NULL (nullable). source

Not defining column nullability explicitly leads to unexpected behaviors. For example, all columns defined within a PRIMARY KEY constraints must be defined as NOT NULL. If columns whose data types are nullable due to not being explicitly defined are used within PRIMARY KEY constraints, SQL Server raises error.

Below columns might end up either with NOT NULL or NULL (nullable) data type.

CREATE TABLE my_table (
column1 VARCHAR(10),
column2 DECIMAL(3,0),
);

To avoid unexpected behavior, it is recommended to explicitly define a column as NULL or NOT NULL.

CREATE TABLE my_table (
column1 VARCHAR(10) NOT NULL,
column2 DECIMAL(3,0) NULL,
);
Labels:

Comments

Post a Comment

Popular posts from this blog

Introduction to SQLFluff: How to make your SQL code clean and error-free

Image by Jake Aldridge from Pixabay You know oftentimes, the cause of runtime or compile errors and hours of debugging agony is all due to simply a missing semicolon. Have you ever had such experience? If you had, you are not alone. There are two ways to avoid these unfortunate situations: either become a perfect developer who never makes mistakes, or use helpful tools such as linters that can catch these errors early on. I am nowhere near being a perfect developer who never makes a mistake. In fact, I'm probably the opposite of a perfect developer, so even if I wanted to, I wouldn’t be able to teach you how to become a perfect developer. But what I can teach you is using linters. A Wikipedia defines a linter as a "static code analysis tool used to flag programming errors, bugs, stylistic errors and suspicious constructs." If you're not convinced yet on using linters, consider this scenario: in a large project with multiple members, different people tend to ...
Post a Comment
Read more

Git squash merge explained

There are many ways to integrate changes in git: regular / normal git merge, git squash merge, git rebase etc. This article explains git squash merge by comparing it to regular merge. Let’s use below example: In the repository with default main branch, after two commits, a new feature branch is created. Some work happened in feature branch. feature branch now has 2 commits that it shares with main branch, and three exclusive commits (exists only in feature branch). In the meantime, others worked on main branch and added two new commits (exists only in main branch). git log output of the main branch: c72d4a9 ( HEAD - > main ) fourth commit on main 2c3dd61 third commit on main 0c2eec3 second commit on main 9b968e8 first commit on main git log output of the feature branch: 786650f ( HEAD - > feature ) third commit on feature 21cbaf1 second commit on feature 677bc7f first commit on feature 0c2eec3 second commit on main 9b968e8 first commit on mai...
Post a Comment
Read more

How To Use KeePassXC Cli

There are similarly named programs: KeePass, KeePassX and KeePassXC (many of which are each others’ forks). Program Condition KeePass primarily for Windows. KeePassX no longer actively maintained. KeePassXC actively maintained and runs natively on Linux, macOS and Windows . Note: GUI version of the KeePassXC has more features than cli version. GUI version has variety of shortcuts as well. Regarding how to use GUI version of the KeePassXC, visit Getting Started Guide . Below features are available only in GUI version. Setting “Name” and “Description” fields of passwords database. Nesting Groups. Creating entry attributes ( open issue ). Adding Timed One-Time Passwords (TOTP). Adding entry with the same title as existing entry. KeePassXC stores all the passwords in passwords database. A passwords database (hereafter referred to as database) is an (encrypted) binary file. It can have any or no extension, but the .kdbx extension is commonly used. The ...
Post a Comment
Read more