Transact SQL (T-SQL) is the language used in Microsoft SQL Server databases. If you use Oracle, you use PL/SQL. If you use PostgreSQL, you use PL/pgSQL. While these languages are similar, they have nuances that make them proprietary to each database platform. If you plan to use Microsoft SQL Server, you need to know T-SQL. Learning T-SQL is a huge task, but you can get started with some basic instructions and understanding common functions.
Variables and Data Types
If you’ve worked with any other language, you’re familiar with variables and data types. T-SQL uses variables and data types similar to other coding languages, but the syntax is a bit different. SQL Server uses strings, numbers, and objects just like other languages. The following are some of the most frequently used variable data types you’ll use when programming T-SQL functions:
varchar(x) : Varchar is SQL Server’s string data type. The “x” defines the number of characters the variable or table column can hold.
int : Integer is the same in SQL Server as it is in other coding languages. This data type is a whole number from -2,147,483,648 to 2,147,483,648 or 4 bytes. You also have the option to use bigint or small int data types, which are also whole numbers but store more or less bytes respectively.
float: The float data type is a decimal number. It’s used for precision when you need more than just a whole number representation for your data. You can store up to 15 digits in your decimal precision data. You can also use the decimal and money data types, which also store decimal numbers but have a different numeric range and precision capabilities.
char : Char stores a specific number of characters. The “varchar” data type stands for “variable character.” The char data type is not variable. If you set the char data type for a column or variable, SQL Server stores the specified amount of characters in the database. For instance, if you use “char(5)” as a data type, SQL Server will pad space characters if you attempt to store a value that has less than five characters. Just like varchar, char will truncate and return an error if you try to store too many characters.
objects : SQL Server has a number of object data types. Older versions of SQL Server don’t support some of the newer types. Some basic data types you will come across when you program SQL are table, uniqueidentifier, xml and cursor. The table data type is what you think: a table object with rows and columns. The uniqueidentifier data type is a 32-character randomly created by the server. You can create a new uniqueidentifier using the “newId” function. Xml returns Xml data you can use to loop through structured data in your front-end applications, and finally, a cursor is a SQL Server object used to loop through data in your stored procedures.
Declaring Your Variables
You create a new T-SQL variable using the “DECLARE” keyword. The following code creates a new variable in SQL Server:
DECLARE @name varchar(50) DECLARE @orderId int
The above code declares two variables. The first is named @name and allows you to store 50 characters as a string. The second variable is named @orderId and it’s declared with the integer data type. You can declare any number of variables in your T-SQL statements. These variables are local to the stored procedure or code you create on-the-fly. You can use dynamic SQL or stored procedures, but stored procedures are preferred.
Stored Procedures in SQL Server
Stored procedures are similar to functions in other languages. SQL Server also has functions, but they are not the standard when calling T-SQL statements from your front-end code. Stored procedures are pre-compiled statements you store in your SQL Server as objects, which makes them faster than creating dynamic SQL statements. The speed is not noticeable on small applications, but they can make a huge difference in enterprise applications.
You can also declare variables in stored procedures. The following code is a stored procedure with the same variables as the above code:
CREATE PROC GetOrder ( @name varchar(50), @orderId int ) AS select * from orders where orderId = @orderId GO
Notice there is no DECLARE statement in the above code. The variables are declared with the stored procedure. You can still use them within the procedure. Stored procedures are similar to other coding language methods. You pass them parameters, and these parameter variables are local and accessible only within the stored procedure. After you finish running the procedure, the variables are then destroyed.
In the above procedure, two parameter values are passed: @name and @orderId. In this example, only one parameter is used, but you can have hundreds of statements in one stored procedure. In this example, data is selected from the orders table where the orderId matches with the parameter value passed. You can see how stored procedures are better than creating dynamic SQL. First, they are more secure and protect you from SQL injection, which is a common way to hack a website. Second, they allow you to just call the procedure and pass parameters instead of having long lines of strings in your front-end code. Using variables passed to the procedure, they are also completely dynamic and return data sets or values based on these passed parameters.
Basic Transactions and CRUD
Create (insert), Read (select), Update and Delete (CRUD) are the four main actions you perform on your SQL Server database. A database is useless if you can’t edit and manage the data, and that’s where CRUD is used.
First, the INSERT statement creates (the “C” in CRUD) a record. The following code is an example of an INSERT statement for T-SQL:
insert into customer (name, email) values (‘Jennifer’, ‘email@example.com’)
The above statement inserts data into the “customer” table. You specify the columns you want to populate with data. In this example, the “name” and “email” columns are populated with the data contained in the “VALUES” section. If you don’t specify columns, they must either allow nulls or you give them a default value. If you try to insert a record and don’t add data in required columns without defaults, the insert statement will fail and you receive an error.
You can also insert data from one table into another. The following code takes a list of users from an old table into the customer table:
insert into customer (name, email) select name, email from archive_customer
The above statement retrieves a list of archived customers and inserts them into the table named “customer.” In this example, all archived customers are pulled from the archive_customer table, but you can also use the where clause to filter the records. For instance, maybe you just want to pull records created within a certain date range. The following code inserts archived customers that were created between two dates:
insert into customer (name, email) select name, email from archive_customer where create_date between ‘1/1/2014’ and ‘2/1/2014’
Next, you have the SELECT statement (the “R” in the CRUD acronym), which is used to read and retrieve data from your tables. The SELECT statement is probably the most commonly used keyword when you work with your data. The SELECT statement is used in subqueries and main queries that return data sets to your front-end code. When you work with your data, you will also run SELECT statements just to analyze and review your data.
The following T-SQL code selects a list of orders for a particular customer:
select * from order where customerId = @customerId
The above SELECT statement is different from the INSERT statement shown previously. This statement is more dynamic, because it uses variables. If this was a stored procedure, you would dynamically pass the customer Id to the variable named @customerId. The statement selects a list of order information based on the @customerId value, which you can assume is unique. As long as this variable is unique to one customer, you only see a list of orders for one particular customer.
The SELECT statement has several clauses and phrases you can use to filter records. For instance, you can also use the “IN” statement. This statement lets you return only records that exist in another table. For instance, take the following code:
select * from customer where customerId = @customerId and customerId in (select customerId from order)
In the above statement, the customer record is only returned if there is an associated order record. Ultimately, the business logic says “return this customer record only if the user has an order with us.” You can also accomplish this task with a JOIN. The JOIN statement links one table with another based on specific primary and foreign keys. The following code has the same results as the above SELECT statement:
select * from customer c join order o on c.customerId = o.customerId where c.customerId = @customerId
The above statement also uses the concept of aliases. These aliases require less typing and they just point to a specific table with just one letter instead of typing out the entire table name for each column. If you don’t specify and alias or table name with a column in a joined query and both tables have the same column name, your T-SQL statement will return an error indicating that ambiguous column names were used.
Next, UPDATE is used to edit your database content. The most important part of your UPDATE statement is the “where” clause. The where clause determines the records that update when you run the statement. Take the following code:
update customer set first_name = @firstname where customerId = @customerId
This statement edits any record that has a customerId with the matching @customerId variable value. If you excluded the where clause from your statements, the update statement would change all records in your customer table. You probably don’t want to change all records to one specific first name, so you use the where clause to filter out the records you don’t want to edit. In the above statement, it’s assumed that customerId is a unique column, so the statement should only update one record.
You can edit more than one column at a time. The following code sets the first and last name column for a specific customer:
update customer set first_name = @firstname, last_name = @lastname where customerId = @customerId
Finally, you might want to delete records from your tables. Delete statements are discouraged, because the only way to get them back is to search through database logs or restore data from a backup. Most database administrators urge you to have an active and inactive column. This means you only edit this particular column and leave the rest of your data intact. If you ever need to go back and see data, you don’t need to sift through archives. Instead, you just find the inactive records using a T-SQL select statement.
The following code is an example of a DELETE T-SQL statement:
delete from customer where customerId = @customerId
In the above statement, notice that you don’t specify any columns like the other statements. That’s because you’re deleting an entire record, so all columns from that record are removed. In this statement, the where clause is also important. If you exclude the where clause, you delete every record from your table. For this reason, it’s important to always remember a where clause in your delete statements or you’re forced to retrieve data from archives or backups.
This article just brushes the surface of T-SQL. T-SQL is a powerful language that lets you perform any action on your data, and it’s an integral part of your front-end applications. You can use SQL for mobile apps, web services and regular web pages such as ecommerce or content sites. With stored procedures, you always have reusable code that you can transfer to other databases and use with multiple apps.