I'm using T-SQL (MS SQL Server) but most of the features exist in other databases. The syntax for functions may be different, as may be the symbol used to create temporary tables. One specific example is that MySQL doesn't have CTEs (common table expressions).
I ran the examples in Microsoft SQL Server Management Studio.
The examples are very simple and I do not go into any depth describing the features. This is very much an introduction and, as I stated, is to let you know that these things exist and encourage you to investigate further.
I use a single table, and the examples are all very similar. There are obviously reasons why you would chose one feature over another, and some are more performant than others. Investigate further after the tutorial.
- Stored Procedures
- User-Defined Functions
- Table-Valued Functions *
- Multi-Statement Table-Valued Functions *
- Common Table Expressions (CTEs) *
- Temporary Tables
- Table Variables *
* these are features that may not exist, or have a different form, in databases other than SQL Server.
CREATE DATABASE Staff GO USE Staff GO
(GO and semi-colons are often unnecessary, I tend to overuse them a little.)
CREATE TABLE Staff (StaffID int PRIMARY KEY, FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL, Salary money, Grade tinyint, OfficeID varchar(5) NOT NULL, DeptID varchar(5), Extn smallint, Bonus bit, BonusRate decimal(4,3), StartDate date, EndDate date, );
Populate some data:
A subquery isn't a separate object like a view or stored procedure. It is an essential aspect of SQL that allows us to nest one query inside another. (It is in this nested sense that I include it in a tutorial about objects.)
The subquery can resolve to a single (scalar) value..
SELECT FirstName, LastName, Salary FROM Staff WHERE Salary > (SELECT Avg(Salary) FROM Staff);
.. or a set of data, most often a single column used with In():
SELECT FirstName, LastName, Grade, Salary FROM Staff WHERE StaffID In(SELECT StaffID FROM Staff WHERE Grade > 3);
Subqueries are a significant feature of SQL. Bear in mind though, that often the same results can be obtained with a careful rewriting using joins, which will be more efficient. Here are a couple of examples from MySQL: Rewriting Subqueries as Joins.
Views are an extremely useful feature. Often, we will start a query by adding and joining the same three tables. We can save this set-up as a view and then use it as if it were another table in the database. Saved objects like views and stored procedures can be optimized by the database.
CREATE VIEW LondonStaff AS SELECT FirstName, LastName, OfficeID FROM Staff WHERE OfficeID = 'LDN' GO SELECT * FROM LondonStaff ORDER BY LastName, FirstName;
To see your view in the Object Explorer, right-click the Views folder and choose Refresh.
I suspect I am not alone is this, but I often forget the keyword AS, so you might find it helps if you put AS on its own line, rather than at the end of the first line.
Note that when using CREATE to create an object it most often needs to be the first statement in the query window. (I always put it first anyway, so that I don't have to worry about whether this is required or not.) If followed by other statements you should use GO after the CREATE statement. However, I recommend that you create an object and test it as separate queries, in separate windows.
It is common to use the prefix vw_ to name views. sp_ would be a prefix to name stored procedures, although I avoid this because this is how the (many) system SPs are named. Using sp without the underscore could be considered.
Stored procedures are an extremely important feature of SQL. I present two very simple examples here, but there is a lot more to them. They can
- Return a dataset or value(s)
- Receive input parameters (variables)
- Return values using output parameters
- Include programming logic and statements
A significant example would be to execute a number of INSERT and UPDATE statements, returning a value to indicate whether this process succeeded or not.
As saved objects they are optimized by the database, but they also offer possibilities in terms of security and permissions. Some companies actually use SPs almost exclusively.
CREATE PROCEDURE FullNames AS BEGIN SELECT FirstName + ' ' + LastName AS FullName FROM Staff ORDER BY LastName, FirstName END GO EXEC FullNames
You can use EXEC or EXECUTE to run the stored procedure. You can even omit EXEC but I do not recommend this; always include EXEC to make it clear what you are doing.
The last statement (before END) is a SELECT statement, so this is what will be returned; it doesn't need to be RETURNed explicitly.
The following example demonstrates how to pass a parameter, a value, to an SP.
CREATE PROCEDURE OfficeStaff @Office varchar(5) AS SELECT FirstName, LastName, OfficeID FROM Staff WHERE OfficeID = @Office; GO EXEC OfficeStaff 'LDN';
Default values for SP (and FUNCTION) parameters can be specified using @ParameterName data-type = someValue in the declaration.
A single value can be returned using RETURN @SomeValue, or using the keyword OUTPUT.
Stored Procedures: Returning Data
These articles introduce some programming concepts:
T-SQL Programming Part 1 - Defining Variables, and IF...ELSE logic
T-SQL Programming Part 2 - Building a T-SQL Loop
CREATE FUNCTION Bigger(@x int, @y int) RETURNS int AS BEGIN RETURN CASE WHEN @x > @y THEN @x ELSE @y END END GO SELECT dbo.Bigger(5, 10) -- dbo is necessary I found
These can be very useful of course. However, I would reserve them for complex, or hard to read, functions, that I will use a number of times. A user-defined function in a query will not be as efficient as the equivalent using SQL expressions and built-in functions.
Note that FUNCTIONs use parentheses (), stored procedures don't. For both, multiple arguments (parameter values) would be supplied separated by commas.
A table-valued function can be considered as a view that accepts parameters. (Or as a function that returns a table, if you like.)
CREATE FUNCTION OfficeStaff2(@Office varchar(5)) RETURNS TABLE AS RETURN SELECT * FROM Staff WHERE OfficeID = @Office GO DECLARE @ChosenOffice varchar(5) -- a variable SET @ChosenOffice = 'BIR' SELECT * FROM OfficeStaff2(@ChosenOffice)
Compare RETURNS TABLE with RETURNS int in the previous example.
There are also multi-statement table-valued functions. These define, populate and return a table. Bear in mind when you investigate this that stored procedures are generally preferred to them I believe, in terms of performance. Here is an example:
CREATE FUNCTION LowerGrades(@Lowest tinyint) RETURNS @LowerTable TABLE ( FName varchar(30), LName varchar(30) ) AS BEGIN INSERT INTO @LowerTable SELECT FirstName, LastName FROM Staff WHERE Grade < @Lowest; RETURN END GO SELECT FName, LName FROM LowerGrades(3)
Using Table-Valued Functions in SQL Server
Common Table Expressions (CTEs)
These use the WITH keyword and essentially allow us to break a query into two parts. The WITH defines, or describes, a dataset that is used by the immediately following statement.
;WITH Londoners (StaffID, FirstName, LastName, Salary) AS ( SELECT StaffID, FirstName, LastName, Salary FROM Staff WHERE OfficeID = 'LDN' ) SELECT * FROM Londoners WHERE Salary > 20000;
If I were writing the same CTE more than once then I would consider creating a view.
It is a convention to precede WITH with a semi-colon, to avoid any potential issues with an unterminated preceding statement.
Create a table with its name preceded by # to create a temporary table. This will persist during the user's current session, and can be treated as any other table.
CREATE TABLE #TempLondon ( FirstName varchar(30), LastName varchar(30) ) GO INSERT INTO #TempLondon SELECT FirstName, LastName FROM Staff; SELECT * FROM #TempLondon; DROP TABLE #TempLondon -- not essential
# creates a local, user, temp table. It is not really 'local', it is stored in tempdb, but it is 'user' because no one else can see it. (It can be cached in memory so, in these terms, can be considered local.)
## would create a global temp table. Other people can see and use it. It will be removed when all sessions (meaning all users) or connections are closed.
Similar to temp tables, but these exist only during the current function, stored procedure or batch that they are declared in.
DECLARE @LondonTable TABLE ( FName varchar(30), LName varchar(30), Grade tinyint ); INSERT INTO @LondonTable (FName, LName, Grade) SELECT FirstName, LastName, Grade FROM Staff WHERE OfficeID = 'LDN'; SELECT * FROM @LondonTable
They are suitable for 100 rows or less.
Difference between CTE and Temp Table and Table Variable
This post has been edited by andrewsw: 12 October 2015 - 09:13 AM