Transact SQL: Stored Procedures and Database Programming

shutterstock_190893782Transact SQL (or T-SQL) is programming for your database. T-SQL is specific to the Microsoft SQL Server database platform. This means that this article is specific to MSSQL, but most SQL languages are a flavor of each other. This means that each language is similar, so learning one will make it easier to learn another.

New to SQL Server? Understand how to program MSSQL T-SQL at Udemy.com.

You can connect to your database in several ways. Most SQL Server administrators prefer SQL Server Management Studio. Management Studio is a visual representation of your database, so it’s easier to manage objects. Each part of the database is considered an object. Objects include the database itself, tables, users, triggers, indexes and views. You can create, edit and delete each of these objects using transact SQL.

Create a Database

Before you do anything, you need to create a database. The database is the main object that encompasses all parts of your database including the actual data. The following code is how you create a database using T-SQL:

CREATE DATABASE MyDatabase
GO

The database name is the above example is “MyDatabase.” Notice the “Go” statement. This statement tells the compiler to run the statement. Technically, you can run several lines of SQL statements and specify when to run each of them. Usually, in a stored procedure, the GO statement is set at the end of the stored procedure so that code runs all at once. In this case, the CREATE DATABASE statement is evaluated and runs right away without any other statements compiled first.

Take an in-depth look at SQL Server and databases at Udemy.com.

Create Table

After you create a database, you need to fill it with tables. Tables are the organizational units that store your data. For instance, if you have a database of customers, you would have a table named “Customers” and any related information such as “Sales” and “ContactInformation.”

For instance, the following code creates a table named “Customers”:

CREATE TABLE Customers
(ID int PRIMARY KEY NOT NULL,
Firstname varchar(25) NOT NULL,
Lastname varchar(25) NULL )
GO

The first column in the above creation statement sets the ID for the table. It’s a primary key, which has an automatic requirement for uniqueness. While MSSQL doesn’t require you to set a primary key for each table, good table design dictates that every table needs a primary key. The primary key is a clustered index, and it speeds up queries tremendously. A primary key can’t contain a NULL value, so notice that the ID column is also set as “NOT NULL.”

Next, two more columns are created. This table not only holds the customer’s ID but it also holds the customer’s first and last name in separate columns. A new person might want to store first and last name in the same column, but this is poor design. There will be times when you want to do a search on just a last name and do some kind of manipulation based on that search.

Add Records to a Table

With a table created, you now need to add data to the table. The following code shows you how to insert a customer record into the previously created table:

INSERT INTO Customers (ID, Firstname, Lastname) VALUES (1, ‘John’, ‘Smith’)
GO

Whenever you need to insert data, you run the T-SQL “INSERT INTO” statement. The first set of parenthesis contain the columns you want to use and the second set is for the data. The values inserted must match the columns specified. There are alternative forms of the INSERT INTO statement, but the above is the main format that you’ll need to use for each added record.

Update Records

After you insert data, you’ll probably want to update or edit the data. This is accomplished using the UPDATE statement. For instance, suppose you realize that the last name you entered was spelled wrong. You would then want to change the customer’s last name. Since you only want to change the one customer, you’d use the customer’s ID, because it is unique to that one customer. The following code is how you edit a table in your database:

UPDATE Customers SET lastname=’Smiths’ WHERE ID = 1
GO

In the above statement, you’ll edit the customer’s last name to “Smiths.” You always want to remember the WHERE clause in your update statement or you’ll accidentally edit the data in the entire table.

Reading and Selecting Data from Your Table

Storing your data isn’t enough. You also need to read your data. You can do this using the MSSQL SELECT statement. The following code is an example of the SELECT statement:

SELECT * FROM Customers
GO

After you run the above statement, you see all columns and records in the table.  You typically want to limit the data you see. When your table gets to be millions of records, returning all those records causes severe performance issues. Understand that all of those records first need time to return and second the application needs the memory to store those records.

To combat this issue, you select a set block of records. For instance, instead of retrieving a million records, you would return a set of 100 records. In the case of the Customers table, there is only one record, but suppose you want to see information pertaining to that one customer. You would then use the WHERE clause in your SELECT statement.

The following code is an example of writing a SELECT statement with a WHERE clause:

SELECT * FROM Customers WHERE ID = 1
GO

Just like the UPDATE statement’s WHERE clause, this WHERE clause specifies that you want to see only the customer with an ID of 1. The WHERE clause has several options, and you use these options depending on the number of records you want to see as well as the specific records. The WHERE clause lets you filter records using any column in the table, but you can also use subqueries to view records that match another table in your database.

For example, the following code selects only Customer records that are also present in an Order table:

SELECT * FROM Customers WHERE ID in (SELECT CustomerID FROM Order)

Create a Login Account to Access Data

You have data, tables and your database but you need to give login permission to an account. This account needs security to access your tables. Typically, you want to create a login name for each database in your SQL Server. The reason is to avoid risking unauthorized access to the entire database server when a hacker gains access to just one database. SQL Server allows you to get very granular with your database permissions, so you can set access to specific tables, records and columns. However, for the most part, you can just create a login that accesses the database and no other databases. This protects your entire server and limits access to just the one database.

The following code creates a login account for SQL with a password:

CREATE LOGIN mylogin WITH PASSWORD = 'mypass'
GO

The above code creates the login “mylogin” with the password “mypass.”

The T-SQL language is vast and takes more than just a small article to understand it. If you have experience in other programming languages, it might help you understand transact SQL, but SQL is a bit different than other languages. The best way to handle learning SQL is to take an online course such as Learning Microsoft SQL 101 at Udemy.com. With time and practice, you can manipulate your table’s data like a pro.