Transact-SQL (T-SQL) is Microsoft's extension to SQL. Here are examples of some of the more common T-SQL statements.
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.
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).
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.
Inserts data into the ArtistName and ActiveFrom columns of the Artists table.
Creates a relationship between the Artists and Albums tables. Uses each table's ArtistId column as the primary key and foreign key.
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
smalldatetime, etc) and there are many different functions for dealing with dates (for example
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
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.