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.
CREATE DATABASE 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.
ALTER DATABASE Examples
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
Creates a table called Artists with 3 columns (ArtistId, ArtistName, and ActiveFrom consisting of various data types and constraints).
ALTER 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.
INSERT Examples
Inserts data into the ArtistName and ActiveFrom columns of the Artists table.
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.
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.