Temporary Tables

Temporary Tables

The simple answer is yes you can. Let look at a simple CREATE TABLE statement:

CREATE TABLE #Yaks (

YakID int,

YakName char(30) )

Select * from #yaks

You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table.

Temporary tables are created in tempdb. If you run this query:

CREATE TABLE #Yaks (

YakID int,

YakName char(30) )

select name

from tempdb..sysobjects 

where name like '#yak%'

You'll get something like this:

name

------------------------------------------------------------------------------------ #Yaks__________________________________________________________00000000001D

(1 row(s) affected)

drop table #yaks

Global Temporary Tables

You can also create global temporary tables. These are named with two pound signs(##). For example, ##YakHerders is a global temporary table. Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.  These are rarely used in SQL Server.

CREATE TABLE ##Yaks (

YakID int,

YakName char(30) )

Select * from ##yaks

What is the difference between a Local and a Global temporary table?

Local temporary tables	Global temporary tables
Created  with One pound signs(#). .	. Created  with two pound signs(##).
Multiple users can’t share a local temporary table, Only available to the current Db connection for current user and are cleared when connection is closed	Available to any connection once created. They are cleared when the last connection is closed Can be shared by multiple user sessions. But when sessions close table removed.

Handling SQL Server Errors

Handling SQL Server Errors

The basics of TRY CATCH error handling in T-SQL introduced in SQL Server 2005. It includes the usage of common functions to return information about the error and using the TRY CATCH block in stored procedures and transactions.

SQL Server uses the following syntax to capture errors in Transact-SQL statements:

BEGIN TRY

    SELECT [First] = 1

    SELECT [Second] = 1/0

    SELECT [Third] = 3

END TRY

BEGIN CATCH

    PRINT 'An error occurred'

END CATCH

This returns the following output:

First

-----------

1

(1 row(s) affected)

Second

-----------

(0 row(s) affected)

An error occurred

A key difference from SQL Server 2000 is that execution is halted when SQL Server encounters an error.  At that point execution transfers to the CATCH block.  This error isn't returned to the client application or calling program.  The TRY CATCH block consumes the error.

returned to the client application or calling program.  The TRY CATCH block consumes the error.

Error Functions

Inside the CATCH block there are a number of specialized functions to return information about the error.

BEGIN TRY

    SELECT [Second] = 1/0

END TRY

BEGIN CATCH

    SELECT [Error_Line] = ERROR_LINE(),

           [Error_Number] = ERROR_NUMBER(),

           [Error_Severity] = ERROR_SEVERITY(),

           [Error_State] = ERROR_STATE()

    SELECT [Error_Message] = ERROR_MESSAGE()

END CATCH

Second

-----------

(0 row(s) affected)

Error_Line  Error_Number Error_Severity Error_State

----------- ------------ -------------- -----------

2           8134         16             1

(1 row(s) affected)

Error_Message

---------------------------------------------------

Divide by zero error encountered.

(1 row(s) affected)

These are the functions you can use inside a CATCH block.  These functions all return NULL if they are called from outside a CATCH block.

• ERROR_NUMBER.  The number of the error that occurred.  This is similar to @@ERROR except that it will return the same number for the duration of the CATCH block.
• ERROR_MESSAGE.  The complete text of the error message including any substiture parameters such as object names.
• ERROR_LINE.  This is the line number of the batch or stored procedure where the error occured.
• ERROR_SEVERITY.  This is the severity of the error.  The CATCH block only fires for errors with severity 11 or higher.  Error severities from 11 to 16 are typically user or code errors. Severity levels from 17 to 25 are usually software or hardware errors where processing may not be able to continue.
• ERROR_STATE.  This is sometimes used by the system to return more information about the error.
• ERROR_PROCEDURE.  If the error was generated inside a stored procedure this will hold the name of the procedure.

Trapping Errors in Stored Procedures

A TRY CATCH block can catch errors in stored procedures called by other stored procedures.  An example is:

BEGIN TRY

    EXEC ParentError

END TRY

BEGIN CATCH

    SELECT Error_Line = ERROR_LINE(),

           Error_Proc = ERROR_PROCEDURE()

END CATCH

Assuming that the ParentError stored procedure calls the ChildError stored procedure which generates an error you'd see output like this:

Error_Line  Error_Proc

----------- -------------

5           ChildError

Error Handling and Transactions

 The TRY CATCH syntax also works well with transactions.  Below is a common pattern used inside stored procedures for transactions.  Any errors cause the transaction to roll back.

BEGIN TRY

    BEGIN TRANSACTION

    INSERT INTO dbo.invoice_header

        (invoice_number, client_number)

    VALUES (2367, 19)

    INSERT INTO dbo.invoice_detail

        (invoice_number, line_number, part_number)

    VALUES (2367, 1, 84367)

    COMMIT TRANSACTION

END TRY

BEGIN CATCH

    IF @@TRANCOUNT() > 0 ROLLBACK TRANSACTION

    -- And do some cool error handling

END CATCH

Cursors: An Overview

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

Please note that cursors are the SLOWEST way to access data inside SQL Server. The should only be used when you truly need to access one row at a time. The only reason I can think of for that is to call a stored procedure on each row.  In the Cursor Performance article I discovered that cursors are over thirty times slower than set based alternatives.

The basic syntax of a cursor is:

DECLARE @AuthorID char(11)
 
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors

OPEN c1

FETCH NEXT FROM c1
INTO @AuthorID

WHILE @@FETCH_STATUS = 0
BEGIN

 PRINT @AuthorID

 FETCH NEXT FROM c1
 INTO @AuthorID

END

CLOSE c1
DEALLOCATE c1

The DECLARE CURSOR statement defines the SELECT statement that forms the basis of the cursor. You can do just about anything here that you can do in a SELECT statement. The OPEN statement statement executes the SELECT statement and populates the result set. The FETCH statement returns a row from the result set into the variable. You can select multiple columns and return them into multiple variables. The variable @@FETCH_STATUS is used to determine if there are any more rows. It will contain 0 as long as there are more rows. We use a WHILE loop to move through each row of the result set.

The READ_ONLY clause is important in the code sample above.  That dramatically improves the performance of the cursor.

In this example, I just print the contents of the variable. You can execute any type of statement you wish here. In a recent script I wrote I used a cursor to move through the rows in a table and call a stored procedure for each row passing it the primary key. Given that cursors are not very fast and calling a stored procedure for each row in a table is also very slow, my script was a resource hog. However, the stored procedure I was calling was written by the software vendor and was a very easy solution to my problem. In this case, I might have something like this:

EXEC spUpdateAuthor (@AuthorID)

instead of my Print statement. The CLOSE statement releases the row set and the DEALLOCATE statement releases the resources associated with a cursor.

If you are going to update the rows as you go through them, you can use the UPDATE clause when you declare a cursor. You'll also have to remove the READ_ONLY clause from above.

DECLARE c1 CURSOR FOR
SELECT au_id, au_lname
FROM authors
FOR UPDATE OF au_lname

You can code your UPDATE statement to update the current row in the cursor like this

UPDATE authors
SET au_lname = UPPER(Smith)
WHERE CURRENT OF c1

        ---------------Forward cursor's declaration----------

DECLARE Col CURSOR FAST_FORWARD

FOR

--Distinct state name will be iterate in cursor and will become column name

SELECT DISTINCT State as [column] FROM SalesSummaryOfRegions Order By State

--Opening the cursor

OPEN Col

--fetch first record (state name) from cursor

FETCH Col INTO @strColumnList

--while loop iterates number of records times

WHILE @@FETCH_STATUS=0

BEGIN

--assign state name in query variable

SET @strSQL=@strSQL+@strColumnList+', '

--after assigning state, fetching next records

FETCH Col INTO @strColumnList

END

--after finishining iteration closing and deallocating cursor from memory

CLOSE Col

DEALLOCATE Col

--now creating SELECT statement from state list assigned to @strSQL variable.

SET @strSQL=LEFT(@strSQL,LEN(@strSQL)-1)

SET @strSQL='SELECT Item, ' + @strSQL 

+ ' FROM SalesSummaryOfRegions PIVOT (SUM(TotalSales) for State IN ('

+ @strSQL + ')) AS piv'

--execute SELECT statement created in @strSQL

EXEC sp_executeSQL @strSQL