Page 1 of 1

More Than Just Tables (Views, SPs etc.)

#1 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6819
  • View blog
  • Posts: 28,250
  • Joined: 12-December 12

Post icon  Posted 10 October 2015 - 03:20 PM

The purpose of this tutorial is to make you aware of objects that we can use to hold temporary data, going beyond the creation of a single query based on the original tables and joins. For example, views, stored procedures, etc.. It should also prove useful as a quick reference and reminder of the basic syntax for the features.

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.

  • Subqueries
  • Views
  • 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.

Sample Data

USE Staff

(GO and semi-colons are often unnecessary, I tend to overuse them a little.)

    (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 
SELECT FirstName, LastName, OfficeID FROM Staff 
WHERE OfficeID = 'LDN'

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

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.
    SELECT FirstName + ' ' + LastName AS FullName
    FROM Staff 
    ORDER BY LastName, FirstName

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)
    SELECT FirstName, LastName, OfficeID 
    FROM Staff WHERE OfficeID = @Office;
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

User-Defined Functions
CREATE FUNCTION Bigger(@x int, @y int)
            WHEN @x > @y THEN @x
            ELSE @y

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.

Table-Valued Functions

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))

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)
	FName varchar(30),
	LName varchar(30)
	INSERT INTO @LowerTable 
	SELECT FirstName, LastName FROM Staff 
	WHERE Grade < @Lowest;
SELECT FName, LName FROM LowerGrades(3)

Using Table-Valued Functions in SQL Server
User-Defined Functions

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.

Temporary Tables

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)
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.

Temporary Tables

Table Variables

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.

table (Transact-SQL)

Difference between CTE and Temp Table and Table Variable

This post has been edited by andrewsw: 12 October 2015 - 09:13 AM

Is This A Good Question/Topic? 3
  • +

Replies To: More Than Just Tables (Views, SPs etc.)

#2 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Posted 11 October 2015 - 09:04 PM

Nice article! Very good introduction to the various database objects in MSSQL, an essential read for those looking to improve their SQL knowledge beyond the basics.

For those interested in CTEs, I wrote a more detailed tutorial on them concentrating on using them for hierarchical data. You can find that tutorial here.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1