T-SQL Examples

Transact-SQL (T-SQL) is Microsoft's extension to SQL. Here are examples of some of the more common T-SQL statements.

SELECT Examples

Selects all data from the Artists table.

This is the most basic example of a SELECT statement. You can also do things like, select certain columns, filter the results by applying certain criteria to the query, run queries across multiple tables, and more.

More SELECT Examples


This example creates a database called Music.

This is the most basic way to create a database using T-SQL. You can also do things like specify where to put the database data files and transaction log files, how much disk space they should use up, how much they're allowed to grow, and more.



This example code adds a data file to the Solutions database. This example assumes a Windows operating system (see below for a Linux and Mac example).

The file doesn't need to go into the same location as specified here — this is just an example. You can specify whatever location you need to put the log file in.



Creates a table called Artists with 3 columns (ArtistId, ArtistName, and ActiveFrom consisting of various data types and constraints).

More CREATE TABLE Examples


This example adds a new column called TaskDescription to the Tasks table. This column has a data type of varchar(255) and it's allowed to have null values.

More ALTER TABLE Examples

INSERT Examples

Inserts data into the ArtistName and ActiveFrom columns of the Artists table.

More INSERT Examples

Create a Relationship

Creates a relationship between the Artists and Albums tables. Uses each table's ArtistId column as the primary key and foreign key.

More Relationship Examples

Format the Date

There are many different ways that dates and times can be treated in databases. In SQL Server there are different data types for storing dates (such as date, time, datetime, smalldatetime, etc) and there are many different functions for dealing with dates (for example SYSDATETIME(), GETDATE( ), CURRENT_TIMESTAMP, etc).

In this example, I use the YEAR() function to return just the year part of the date.

I also assign an alias to the result of the YEAR() function, of which we pass the ReleaseDate column as an argument.

Create a View

Creates a view called RockAlbums. Replace this name with your own name.

Alter a View

Alters the view from the previous example. In this example I simply add a column to the SELECT statement.

Run a View

You can run a view by using a SELECT statement, just as you would when querying a normal table.

Create a Stored Procedure

Creates a stored procedure called spAlbumsFromArtist that accepts a parameter called @ArtistName with a data type of varchar(255).

Replace the procedure name and data type with your own.

Execute a Stored Procedure

Executes the spAlbumsFromArtist stored procedure, passing in Devin Townsend as the parameter value.

Backup a Database

Windows file system:

Linux/Mac file system:

Using PowerShell (without specifying the location):

Using PowerShell (and specifying the location):

Backs up a database called Music to the specified location on disk. Change the location as required.