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

 
SELECT * 
FROM Albums;

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

CREATE DATABASE Examples

xxxxxxxxxx
 
CREATE DATABASE Music;

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.

More CREATE DATABASE Examples

ALTER DATABASE Examples

x
 
USE master;  
GO
ALTER DATABASE Solutions   
ADD FILE 
(  
    NAME = Solutions_dat_2,  
    FILENAME = 'D:\mssql\data\Solutions_dat2.ndf',  
    SIZE = 10MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5%  
);
GO

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.

More ALTER DATABASE Examples

CREATE TABLE Examples

xxxxxxxxxx
 
CREATE TABLE Artists (
    ArtistId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ArtistName nvarchar(255) NOT NULL,
    ActiveFrom date
);

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

More CREATE TABLE Examples

ALTER TABLE Examples

xxxxxxxxxx
 
ALTER TABLE Tasks
ADD TaskDescription varchar(255) NULL;

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

xxxxxxxxxx
 
INSERT INTO Artists (ArtistName, ActiveFrom)
VALUES 
  ('Iron Maiden','1975-12-25'),
  ('AC/DC','1973-01-11'), 
  ('Allan Holdsworth','1969-01-01'),
  ('Buddy Rich','1919-01-01'),
  ('Devin Townsend','1993-01-01'),
  ('Jim Reeves','1948-01-01'),
  ('Tom Jones','1963-01-01'),
  ('Maroon 5','1994-01-01'),
  ('The Script','2001-01-01'),
  ('Lit','1988-06-26'),
  ('Black Sabbath','1968-01-01'),
  ('Michael Learns to Rock','1988-03-15'),
  ('Carabao','1981-01-01'),
  ('Karnivool','1997-01-01'),
  ('Birds of Tokyo','2004-01-01'),
  ('Bodyjar','1990-01-01');

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

More INSERT Examples

Create a Relationship

xxxxxxxxxx
 
CONSTRAINT FK_Albums_Artists FOREIGN KEY (ArtistId)     
    REFERENCES dbo.Artists (ArtistId)     
    ON DELETE NO ACTION    
    ON UPDATE NO ACTION

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

xxxxxxxxxx
 
SELECT AlbumName, YEAR(ReleaseDate) AS Year 
FROM Albums;

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

xxxxxxxxxx
 
/* Replace the view name with your own view name */
CREATE VIEW RockAlbums 
AS
/* Replace the following query with your own */
SELECT AlbumName, ArtistName
FROM Albums
    INNER JOIN Artists
    ON Albums.ArtistId = Artists.ArtistId 
    INNER JOIN Genres
    ON Albums.GenreId = Genres.GenreId
WHERE Genres.Genre = 'Rock';

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

Alter a View

xxxxxxxxxx
 
/* Replace the view name with your own view name */
ALTER VIEW RockAlbums 
AS
/* Replace the following query with your own */
SELECT AlbumName, ArtistName, ReleaseDate
FROM Albums
    INNER JOIN Artists
    ON Albums.ArtistId = Artists.ArtistId 
    INNER JOIN Genres
    ON Albums.GenreId = Genres.GenreId
WHERE Genres.Genre = 'Rock';

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

Run a View

xxxxxxxxxx
 
SELECT * 
FROM RockAlbums;

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

Create a Stored Procedure

xxxxxxxxxx
 
/* Replace procedure name and parameter with your own */    
CREATE PROCEDURE spAlbumsFromArtist 
    @ArtistName varchar(255)
AS
/* Replace everything below with your own code */
    SELECT AlbumName, ReleaseDate
    FROM Albums
        INNER JOIN Artists
        ON Albums.ArtistId = Artists.ArtistId 
    WHERE Artists.ArtistName = @ArtistName;

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

xxxxxxxxxx
 
EXECUTE spAlbumsFromArtist @ArtistName = "Devin Townsend";

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

Backup a Database

Windows file system:

xxxxxxxxxx
 
BACKUP DATABASE Music  
TO DISK = 'C:\Backups\Music.bak';

Linux/Mac file system:

xxxxxxxxxx
 
BACKUP DATABASE Music  
TO DISK = '/var/opt/mssql/data/Music.bak';

Using PowerShell (without specifying the location):

xxxxxxxxxx
 
Backup-SqlDatabase -ServerInstance MyServer -Database Music

Using PowerShell (and specifying the location):

xxxxxxxxxx
 
Backup-SqlDatabase -ServerInstance MyServer -Database Music -BackupFile 'C:\Backups\Music.bak'

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