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.
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.
ALTER DATABASE
Examples
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.
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).
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.
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.
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.
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.