☰ Contents

T-SQL Code - Part 1

This section includes many activities that demonstrate the functionality of the topics.

Note: some of the demonstrations and activities have dependencies on prior activities being performed for the objects to exist.

It is important to note that there are several variations of the SQL (Structured Query Language) code. The broad standard language is ANSI SQL and is common across all SQL brands. Most brands have extended the ANSI SQL to include proprietary commands. Microsoft has expanded SQL Server with T-SQL (Transactional SQL).

 

Basics

SQL code is used to instruct the server to do something. The most common data manipulation involves adding, reading or deleting data.

In the case of data retrieval the goals are

• Manipulate the desired data
• Manipulate the data is an efficient manner
• Make the code human readable

Code Format

T-SQL code formatting is not specific in most cases.

• Case Sensitive -- Most SQL Server installations are not configured to be case sensitive, in other words SELECT, Select and select are all the same to the server. It is not important to use any specific case. It can make the code easier to read if some coding standards are followed.
• White Space -- White space, carriage returns and line feeds are not important in the code to the SQL Server. Standardized use of them can make the code easier to read.
• Comments --Adding comments to code also goes a long way in making code easier to read. They do not make the code slower or add any noticeable work for the database server.

Quotation Marks

It is important to pay attention to the use of quotes. In T-SQL

Single quote -- Used to designate string values in a query.

SET @DBName = 'AdventureWorks2014'

Double-Quote -- Usually double quotation marks are used for aliases and names with unusual characters.

column name "First name"

Simple rule: [S]ingle quote for [S]trings, [D]ouble quote for things in the [D]atabase

 

Variables

A variable is a named placeholder that holds a value. The value it holds can be changed within the code at any time.

In T-SQL code, a variable is named with a preceding @ symbol. i.e., @MyVariableName.

User Variables

Like many computer languages, SQL requires variables to be Declared and typed before they are used.

Declaring variables

DECLARE @DBName NVARCHAR(128)

Assigning a value to a variable

SET @DBName = 'MyDatabase'

There are many ways to write the definition, learn to understand all the ways it can be written.

DECLARE @MyVar1 [int]
DECLARE @MyVar2 [int]
SET @MyVar1 = 1
SET @MyVar2 = 2

Multiple declarations can be written in a single line written as

DECLARE @MyVar1 [int], @MyVar2 [int]

For SQL 2012 and newer, the declaration and assignment can be on a singe line written as

DECLARE @MyVar1 [int] = 1
DECLARE @MyVar2 [int] = 2

Or written as

DECLARE @MyVar1 [int] = 1, @MyVar2 [int] = 2

SQL 2008 limitation

This statement does not work for SQL 2008 R2 and older.

DECLARE @MyVar [int] = 5;

It cannot be written on one line and must be on separate lines

DECLARE @MyVar [int]
SET @MyVar = 5

 

Configuration Functions

SQL Server also exposes system information through built-in configuration functions, prefixed with @@. Unlike user variables, these values are set by the server and reflect the current session or instance state — they cannot be assigned by the user.

Here are a few of the configuration functions available.

• @@SERVERNAME — Name of the local server running SQL Server.
• @@SPID — Session ID of the current user process.
• @@VERSION — nvarchar; system and build information for the current installation of SQL Server.

Additional Reading: Configuration Functions — Microsoft Docs

 

Getting Output

Select Statement

The SELECT statement is the most common command used in SQL code.

It is used to return result sets from expressions including variables and datatables.

SELECT GETDATE()

SELECT * FROM sys.databases

Many examples of using SELECT are presented in this book.

Print Statement

The PRINT statement is useful for troubleshooting scripts

It will post text to the Messages tab.

PRINT GETDATE()

PRINT 'I am a PRINT message'

Even if result sets exist, the Message tab will also exist.

SELECT @@SERVERNAME
PRINT 'I am a PRINT message'

-- 0203-02-05 Demonstration Code ==========================
-- Prints the int variable
DECLARE @Item1 int
DECLARE @Item2 varchar(5)
SET @Item1 = 5
SET @Item2 = 'xyz'
PRINT @Item1

Failing code

This code fails because of an attempt to concatenate a String and Number. (Item1 = 5)

-- 0203-02-10 Demonstration Code ==========================
-- Print with Concatenation
-- Fails with data type error
DECLARE @Item1 int
DECLARE @Item2 varchar(5)
SET @Item1 = 5
SET @Item2 = 'xyz'
PRINT 'Item1 = ' + @Item1

A later section will demonstrate how to get this code to work with CAST.

 

The GO Statement

The GO statement is not technically a valid SQL statement, it only applies to SSMS. It will not work in stored procedures, functions etc. It is used to separate a script into 2 or more batches. It is the same as individually executing each part of a script that is separated with a GO.

Note: The GO statement establishes a scope for things like variables. Variables declared before a GO statement will not exist after a GO statement.

The GO statement must be the only command on a line, it must be in the far left of the editor (line position 1). It cannot be included in any script that is not directly executed in SSMS, including part of a script that is assigned to a variable to be executed (EXECUTE sp_executesql).

A common place to see the GO command is in the top of scripts that needs to ensure the desired databases is selected (in scope) for the rest of the script.

USE [master]
GO

Other typical places for GO are:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Variable Scope

When variables are created, they live only in the batch they were created in. They are automatically destroyed when the batch is completed. They are not available outside the batch. They are destroyed after a GO statement.

-- 0204-01-05 Demonstration Code ================
-- Single batch
-- Shows 2 results sets, (one for each SELECT)
DECLARE @Name varchar(50)
SET @Name = 'John Smith'
SELECT @Name
SET @Name = 'Jane Doe'
SELECT @Name

-- 0204-01-10 Demonstration Code ================
-- Two batches separated with GO command
-- Fails because the second SELECT is in a new batch
-- Error: Must declare the scalar variable "@Name"
DECLARE @Name varchar(50)
SET @Name = 'John Smith'
SELECT @Name
SET @Name = 'Jane Doe'
GO
SELECT @Name

REMEMBER: It is not always necessary to execute a script to determine if a syntax error exists. Instead of executing the script, click the checkmark in the toolbar. Then if there are no syntax errors, proceed to executing the script.

-- 0204-01-15 Demonstration Code ================
-- Fails twice because the second SET and SELECT is in a new batch
-- Error: Must declare the scalar variable "@Name"
DECLARE @Name varchar(50)
SET @Name = 'John Smith'
SELECT @Name
GO
SET @Name = 'Jane Doe'
SELECT @Name

-- 0204-01-20 Demonstration Code ================
-- Shows 2 result sets
-- Works because the @Name variable is declared in each batch
DECLARE @Name varchar(50)
SET @Name = 'John Smith'
SELECT @Name
GO
DECLARE @Name varchar(50)
SET @Name = 'Jane Doe'
SELECT @Name

Multiple Results Sets

Sometimes a GO statement is not necessary and other times it is.

Return 2 result sets, in a single batch. Execute all the commands below together, each SELECT statement will return a result set.

-- 0204-02-05 Demonstration Code ================
SELECT @@SERVERNAME
SELECT @@VERSION

This will also return 2 result sets, in a single batch. Execute all the commands below together, each SELECT statement will return a result set.

SELECT @@SERVERNAME
GO
SELECT @@VERSION
GO

Sometimes the GO is necessary.

The commands below will fail with the error 'sp_who2' is not a valid login or you do not have permission.

sp_who2
sp_who2

The commands below will fail with the error Incorrect syntax near 'active'.

sp_who2
sp_who2 active

Adding a GO between the commands allow them to execute because they are in separate batches. Shows 2 result sets.

sp_who2
GO
sp_who2 active

The EXEC (EXECUTE) Command

Instead of using a GO command, the EXEC command can be used to execute each command in a separate batch. The code below will return 2 result sets.

-- 0204-03-05 Demonstration Code =========================
EXEC sp_who2
EXEC sp_who2 active

The EXEC command will be demonstrated in more depth in a later chapter.

Back to Variable Scope

-- 0204-04-05 Demonstration Code ================
-- Fails twice because the second SET and SELECT is in a new batch
-- Error: Must declare the scalar variable "@Name"
DECLARE @Name varchar(50)
SET @Name = 'John Smith'
SELECT @Name
sp_who2
GO
SET @Name = 'Jane Doe'
SELECT @Name
sp_who2 active

-- 0204-04-10 Demonstration Code ================
-- Fails
-- Error: Incorrect syntax near 'active'
DECLARE @Name varchar(50)
SET @Name = 'John Smith'
SELECT @Name
sp_who2
GO
DECLARE @Name varchar(50)
SET @Name = 'Jane Doe'
SELECT @Name

sp_who2 active

-- 0204-04-15 Demonstration Code ================
-- Returns 3 result sets
-- 4 result sets are expected, but the 1st sp_who2 does not execute, no error reported.
DECLARE @Name varchar(50)
SET @Name = 'John Smith'
SELECT @Name
sp_who2
GO
sp_who2 active
DECLARE @Name varchar(50)
SET @Name = 'Jane Doe'
SELECT @Name

-- 0204-04-20 Demonstration Code ================
-- Returns 4 result sets
sp_who2

DECLARE @Name varchar(50)
SET @Name = 'John Smith'
SELECT @Name
GO
sp_who2 active
DECLARE @Name varchar(50)
SET @Name = 'Jane Doe'
SELECT @Name

-- 0204-04-25 Demonstration Code ================
-- Returns 4 result sets
DECLARE @Name varchar(50)
SET @Name = 'John Smith'
SELECT @Name
EXEC sp_who2
SET @Name = 'Jane Doe'
SELECT @Name
EXEC sp_who2 active

 

Working with Databases

Databases store data objects (tables, views etc), as well as data and security. Multiple databases may exist on a single SQL instance.

Note: 4 system databases are always present on an instance (master, model, msdb, and tempdb). These are discussed in later sections.

Creating a Database using the SSMS GUI

Right-click the Database node > Select New Database …

For this exercise use Simple Recovery Model.

Click OK to create the database.

Note: The Compatibility Level can be set here. Typically select the highest available unless it must be another value.

Creating Code from SSMS GUI

Right-click the Database node > Select New Database …

After changing desired options, click the Script > Script Action to New Query Window.

Click Cancel to abort the operation

Code for creating a database has been generated.

Execute this code to create the database

Note: Since the action was not performed in the left pane, the left pane must be refreshed for the database to appear.

Deleting a Database using the SSMS GUI

IMPORTANT: Once a database is deleted, it cannot be undone. The only way to get the database back is to restore it from a backup.

Right-click the database > select Delete

Verify the Object Name is the database you want to delete.

It is best practice to tick the "Close existing connections" checkbox; otherwise, if connects to the database are open the delete will fail.

Click OK to delete the database.

Note: The database can also be deleted by selecting it and pressing the DELETE key on the keyboard.

 

Working with Tables

Tables exist inside a database. They contain data rows, similar to a spreadsheet. Multiple tables typically exist in a database.

Creating a table using the SSMS GUI

Right-click on tables > Select New Table

Enter some columns

Click the Save icon in the toolbar to save the table

Enter a name for the table

Close the Script tab

Expand the Table node to see the new table

Expand the table name and columns node to see the columns

Creating a Table from a Script

Create a table script from the existing table using these steps

Right-click the table > Script table as > CREATE to > New Query Editor Window

The following code is created

Ensure a database named MyDatabase exists.

Execute this create table code to create a new table.

-- 0206-02-05 Demonstration Code ================
-- Create a table for future demonstrations
USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
[MyColumn1] [varchar](50),
[MyColumn2] [varchar](50),
) ON [PRIMARY]
GO

Deleting Tables using SSMS GUI

Right-click a table > Select Script Table as > DROP to > New Query Editor Window.

Note: The Script to window can also be accomplished by the GUI

Right-Click the table > Select Delete > Click the Script action at the top of the window.

 

Working with Table Data

Adding Data to MyTable

Tables contain data rows.

The SQL command to add data rows is the INSERT.

Syntax

INSERT INTO <tablename> (<columnnames>) VALUES (<columnvalues>)

Ensure a table named MyTable exists in the database named MyDatabase.

Execute this script to add data rows to MyTable.

-- 0207-01-05 Demonstration Code ================
-- Delete all the rows in the table before adding new rows
TRUNCATE TABLE MyTable

-- Add data rows to the table with a value for both columns
INSERT INTO MyTable (MyColumn1, MyColumn2)
VALUES ('Col2Value_HasValue','Has Value');

-- Add a row with the second value as BLANK
INSERT INTO MyTable (MyColumn1, MyColumn2)
VALUES ('Col2Value_Blank','');

-- Add a row with the word NULL for the second column
INSERT INTO MyTable (MyColumn1, MyColumn2)
VALUES ('Col2Value_NULL', 'NULL');

-- Add a row with the word null for the second column
INSERT INTO MyTable (MyColumn1, MyColumn2)
VALUES ('Col2Value_NULL', 'null');

-- Add a row with no value for the second column (NULL)
INSERT INTO MyTable (MyColumn1, MyColumn2)
VALUES ('Col2Value_NULL', NULL);

-- Add a row with no value for the second column (NULL)
INSERT INTO MyTable (MyColumn1)
VALUES ('Col2Value_NULL');
--

-- Query all the table rows
SELECT * FROM MyTable

When ‘NULL’ is not NULL

Null can be a text value and it can represent no value.

‘NULL’ – a Text value of the word NULL which is a value.

‘’ – No value

‘ ‘ – one or more spaces can also be a value of spaces.

NULL – represents nothing as a value. Also referred to as initialized as nothing.

The scripts below demonstrate retrieving data sets that meet specific criteria and can be executed on the table created and populated in the activities chapter.

Execute each SELECT separately

-- 0207-02-05 Demonstration Code ================
-- 2 rows returned, the rows with the value "Null' in MyColumn2
SELECT * FROM MyTable
WHERE MyColumn2 = 'NULL'

-- 0207-02-10 Demonstration Code ================
-- 2 rows returned, the rows with no value in MyColumn2
SELECT * FROM MyTable
WHERE MyColumn2 Is NULL

-- 0207-02-15 Demonstration Code ================
-- 4 rows returned
SELECT * FROM MyTable
WHERE
MyColumn2 = 'NULL'
OR MyColumn2 Is NULL

MyOtherTable

In the table below, two of the columns are designated as NOT NULL. This prevents data rows being added that do not have a value for those columns.

Ensure a database named MyDatabase exists.

Execute this create table code to create a new table.

-- 0207-03-05 Demonstration Code ================
-- Create a table for future demonstrations
USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MyOtherTable](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Description] [nvarchar](128) NULL
) ON [PRIMARY]
GO

Note: The column Data Types and the NOT NULL (Additional information about Data Types and NULL values can be found on the internet).

Refresh the table node to see the tables.

Deleting Data

Data rows can be deleted from a table in 2 ways.

DELETE

Syntax:

DELETE FROM <table name>

Will delete each row one at a time.

This can also filter which rows are deleted using a WHERE clause.

DELETE FROM <table name> WHERE <column name> = <some value>

Records actions to the transaction log and deletes can be replicated.

Does NOT reset Identity (covered later)

Delete all rows in the table

DELETE FROM MyOtherTable

TRUNCATE

Syntax

TRUNCATE TABLE <table name>

Deletes all rows from a table in a single action.

Can NOT filter which rows are deleted

Does NOT write to the transaction log and cannot be performed on Azure SQL Databases, databases is an Azure Managed Instance or a replicated table.

Does reset identity (covered later)

Before we start adding data in this demonstration, ensure that the table does not already contain data by running this command.

Remember, this will delete all records from the table and there is no UNDO feature.

Truncate (Delete) all rows in the table

TRUNCATE TABLE MyOtherTable

In a later demonstration we will further explore the difference between Delete and Truncate.

Adding Data to MyOtherTable

Execute each of the commands below separately. There are some data row addition attempts that will demonstrate failures because they do not provide valid data for the NOT NULL columns (ID & Name).

-- 0207-05-05 Demonstration Code ================
-- Add a row with all values
INSERT INTO MyOtherTable ([ID], [Name], [Description])
VALUES (4,'ID 4 Name','ID4 Has Name');

-- 1207-05-10 Demonstration Code ================
-- Add a row with No Description
INSERT INTO MyOtherTable ([ID], [Name], [Description])
VALUES ('7','','ID4 Name is Blank');

-- 0207-05-15 Demonstration Code ================
-- View the data rows added
-- 2 rows returned
SELECT * FROM MyOtherTable

-- 0207-05-20 Demonstration Code ================
-- Attempt to add a row with Only the ID Column
-- Fails because the Name cannot be omitted
INSERT INTO MyOtherTable ([ID])
VALUES (9);

-- 0207-05-25 Demonstration Code ================
-- Add a row with Name as an integer value (102)
-- SQL will convert the integer to a string value
INSERT INTO MyOtherTable ([ID], [Name], [Description])
VALUES ('2',102,'ID2 Name is an integer');

-- 0207-05-30 Demonstration Code ================
-- Attempt to add a row with ID as a string value (abc)
-- Fails: SQL cannot convert a string that is not a number to a integer.
INSERT INTO MyOtherTable ([ID], [Name], [Description])
VALUES ('abc',102,'ID2 Name is an integer');

SELECT * FROM MyOtherTable

A later demonstration will discuss data types in more detail.

 

Identity

It is recommended that each table have a unique identifier to enable acting a specific row. This facilitates Normalized data.

It is common to have an auto-numbered column named something like ID.

Only 1 column per table can be an Identity.

When a table is created, it can be created to have an identity.

Note: Typically, a primary key is an Identity column and is required on a table for it to be eligible for Transactional Replication.

Create Table With Identity

-- 0208-01-05 Demonstration Code ================
USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MyIdentityTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Description] [nvarchar](128) NULL
) ON [PRIMARY]
GO

Note: In some cases, a GUID is used as a unique identifier. In this case, it is recommended to not have that primary key as a clustered index because the GUIDs are randomly created.

Adding data to a Table with Identity

The nature of an identity is that it automatically creates a unique value for the Identity column. Typically, a sequential auto-numbered value. It is not common to populate the identity column when adding records to a table. In the cases where data is being migrated or promoted, the identity with the data is required in the new location. By default, SQL Server prevents populating the identity column when adding data. This behavior can be temporarily overridden with the Identity Insert directive.

-- 0208-02-05 Demonstration Code ================
-- Add a row with all values except Identity
-- Each time this script is executed it create a row with a sequential identity
INSERT INTO MyIdentityTable ([Name], [Description])
VALUES ('Ident NameA','Ident Has NameA');

INSERT INTO MyIdentityTable ([Name], [Description])
VALUES ('Ident NameB','Ident Has NameB');

SELECT * FROM MyIdentityTable

Identity Insert

-- 0208-03-05 Demonstration Code ================
-- Add a row with ALL values (including ID)
-- Fails: Cannot insert a value into an identity column
INSERT INTO MyIdentityTable ([ID], [Name], [Description])
VALUES (4,'ID 4 Name','ID4 Has Name');

SELECT * FROM MyIdentityTable

Setting the Identity Insert to ON will allow populating the identity column. Ensure the Identity Insert is set to OFF when the inserts are completed.

-- 0208-03-10 Demonstration Code ================
-- Add a row with ALL values (including ID)
-- Insert a value into the Identity column
-- Warning, this will allow inserting duplicate identity values
SET IDENTITY_INSERT MyIdentityTable ON;

INSERT INTO MyIdentityTable ([ID], [Name], [Description])
VALUES (4,'ID 4 Name','ID4 Has Name');

SET IDENTITY_INSERT MyIdentityTable OFF;

SELECT * FROM MyIdentityTable

Identity using the SSMS GUI

An Identity column can also be designated while creating a table in the GUI

While the column is highlighted, look to the bottom and set the properties for the column “Is Identity” to Yes.

Altering an Identity Column

It is not possible to modify an existing table to have an identity column. The table must be dropped then recreated.

A workaround to add an identity column to an existing table would be to rename the existing table, create a new table with the identity then copy the rows from the old table to the new table. Keep in mind that Allow Identity Insert must be enabled if populating the identity column.

Identity for Empty Table

Depending on how the table was emptied, determines what the next Identity value will be.

Each time this script runs it will always restart the ident at 1, because it is emptied with TRUNCATE.

-- 0208-06-05 Demonstration Code ================
-- TRUNCATE ALL rows and add new rows
-- Empties the table and inserts rows
TRUNCATE TABLE MyIdentityTable

INSERT INTO MyIdentityTable ([Name], [Description])
VALUES ('Ident 1','Row 1');

INSERT INTO MyIdentityTable ([Name], [Description])
VALUES ('Iden 2','Row 2');

INSERT INTO MyIdentityTable ([Name], [Description])
VALUES ('Ident 3','Row 3');

SELECT * FROM MyIdentityTable

Each time this script runs, it will continue the number from the last number because it was emptied with DELETE.

-- 0208-06-10 Demonstration Code ================
-- DELETE ALL rows and add new rows
-- Empties the table and inserts rows
DELETE FROM MyIdentityTable

INSERT INTO MyIdentityTable ([Name], [Description])
VALUES ('Ident 1','Row 1');

INSERT INTO MyIdentityTable ([Name], [Description])
VALUES ('Iden 2','Row 2');

INSERT INTO MyIdentityTable ([Name], [Description])
VALUES ('Ident 3','Row 3');

SELECT * FROM MyIdentityTable