Temp Tables and Table Vars

There are two types of temporary storage: temporary tables and table variables. Within temporary tables, there are two subtypes - local and global. For each you need to pay attention to how long the temporary objects last and who can see them (their scope).

Local temp tables have a name that starts with the pound sign. The session that creates them can see and use them. This is true for all levels within the session, such as a call to a procedure or function. Internally, SQL Server adds a suffix to the object name so there is no contention with similarly named objects in other sessions.

Global temp tables have a name that starts with two pound signs. They are visible to all session (thus global). There are destroyed when the original session that created them ends, and there are no other references to the objects.

Table variables are declared. You do not create them. Their name has an @ sign at the beginning. Only the batch that creates them can see them. They get destroyed at the end of the batch. They are not visible at lower levels, such as within a procedure or function call. Unlike temporary tables which get changed as part of a transaction, table variable changes are immediately and permanently applied.

Set Based Operations Versus Cursors

Normally when you run a SQL command, you are performing set based operations. You are working with data in tables as a whole. The results are processed as a set. There is no inherent ordering (barring an ORDER BY of the results). This is the recommended method in most cases because it provides the best performance.

You could, instead, execute SQL iteratively. Then you are going to have to use cursors. Here is the order of operations then:

1. OPEN the cursor
2. FETCH data from the cursor
3. DELLOCATE the cursor
4. CLOSE the cursor

Another way to execute SQL one row at a time is to always use TOP(1) to limit results to a single row, and then repeat.

I find that there are some operations that are so complicated, or volume so huge, that a set based approach just does not work. In those scenarios, I put cursors to work. There might be some optimizations to committing the changes in batches to make this work as fast as possible. It is sometimes just a necessary evil to go iterative.

Organization of Data

Let's start with the definition of a page. It is a physical unit on the disk. Data is stored in it. The size of a page in SQL Server is 8192 bytes (8kb). Each page belongs to a single table, index, or indexed view.

Next let's talk about a heap. It is made up of pages on disk. There is no ordering of data in the heap. Thus for tables stored in the heap, there is no clustered index on the table. Within the heap, rows are referenced via a row identifier (RID), which is an 8-byte value.

Now that we understand what a page and heap are, let's talk about the Index Allocation Map (IAM). This itself is stored as a system page. It points to space in the heap. The internal structure is a doubly linked list. It is used to scan through the heap. When you first insert data into a table, the IAM is created.

Performance Monitoring

To monitor performance of queries, SQL Server has Extended Events. This is a lightweight monitoring system. The SQL Server auditing system is based on Extended Events. You can use Extended Events either through the New Session Wizard GUI, or through the New Session UI GUI.

Another option for monitoring is SQL Trace. This captures events in the database internally. It is an old technique and is officially deprecated. You create the traces through system stored procedure calls. You can also create them via the SQL Server Problem UI. The events are filtered and put into queues.

There are some commands you can issue so that queries automatically generate output to help with monitoring:

• SET STATISTICS IO ON
• SET STATISTICS TIME ON
• SET SHOWPLAN_TEXT / SET SHOWPLAN_XML
• SET STATISTICS_PROFILE / SET STATISTICS_XML

IO causes general stats to be shown. TIME displays CPU and elapsed time. SHOWPLAN gives you estimated plans in either Text or XML format. PROFILE gives you actual plans used in text format; XML gives you the same in XML format.

In the database, there are Dynamic Management Objects (DMOs). These are a bunch of views and functions to help conduct monitoring. They are part of the sys schema. As of SQL Server 2012, there are about 130 of these DMOs. They are grouped in categories like operating system, execution, and indexes. One important example is sys.dm_os_sys_info.

Performance and Optimization

When you have a lot of data and/or are executing complicated SQL against it, performance matters. There is a whole science behind understanding how to see how SQL Server is attacking the problems posed by executing queries, as well as helping SQL Server perform the work efficiently.

To start with, SQL Server has a relationship engine that does the work to parse, bind, and optimize SQL statements that it needs to execute. Parse means to check for the syntax of the SQL and create a parsing tree. Bind means to resolve object names and cerate an algebraic tree. And optimization is to find an execution plan to run the query.

There is a Query Optimizer used by SQL Server that is cost based. Each plan to execute a SQL statement will assign a cost. The cost is calculated based on the algorithms used by the query and an estimate of the rows involved. These row estimates are based on statistics that are gathered on the data.

Stored Procedures

Let’s talk about stored procedures. These are routines written in the T-SQL programming language. They are a single batch of code. You can call these routines using an EXEC command. Obviously, these routines can do DML. But they also can issue DDL. They are not allowed to do a USE to switch databases. They can have parameters which act as variables within the routines. Parameters are passed in a comma separated list. These parameters can optionally be OUTPUT returning values back to the caller. The routines exit when they hit a RETURN statement.

Triggers are similar to procedures. But you never explicitly call triggers. They are implicitly executed when you do DML on a table or view. There are two types of triggers – AFTER triggers and INSTEAD OF triggers. The triggers fire once for a whole SQL statement. Their activity is part of a transaction. Trigger actions can cause other triggers to fire, up to a maximum nested depth of 32. The UPDATE() function takes a column as input and tells whether that column was affected by the DML that caused the trigger to fire. Similarly, the COLUMNS_UPDATED() function returns a bitmap of all field affected.

Dynamic SQL

There is a method to constructing SQL statements in a way where the results are controlled by a dynamically created statement. On other words, the SQL statement itself can depend on some logic based on inputs. The straightforward way to do this is to put together a character string that contains SQL, and then pass it to the EXEC function to run.

There are some dangers to doing an EXEC(). If you are relying from input from users in an application, you subject the executed SQL to possible hacks via what’s known as SQL Injection. To prevent this, you can instead call the sp_executesql() function which will apply certain safeguards to the generated SQL string to run. It also has better performance in some situations.

A place where you cannot utilize dynamic SQL are user defined functions (UDFs). These return a scalar value or a table. They cannot perform DDL operations. When they return a table, there are two ways to code this: (1) an inline table value function, or (2) a multi statement table value function.

Inline table value functions need to BEGIN and END in the function. You just declare them to RETURN TABLE, and the body is simply AS RETURN (SELECT …). Multi statement table value functions need to specify RETURNS @my_table TABLE(). Then the body of the function will be AS BEGIN INSERT @my_table() …. END.