SQL Server essence

There are lots of articles, blogs, forums discussed the differences and similarities between table variables and local temporary tables (ref. as temp tables hereafter). E.g.:

• Sql server table variable vs. local temporary table talks more about the difference from execution perspective.
• http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html talks more about how to use them.
• And the recent article in SQLServerCentral.com

A summarization of table variable vs. temp table is attached in the appendix. Instead of repeating those well known facts, I'll focus on several differences that are hardly mentioned so far between table variable and temp table definitions.

XML Collection

The AdventureWorks sample database has an xml collection HumanResources.HRResumeSchemaCollection, which is used by the [Resume] column in the table HumanResources.JobCandidate.

The following script queries all the resumes into a table variable:

USE AdventureWorks
GO
DECLARE @TestXmls TABLE
(JobCandidateID int NOT NULL, [Resume] xml(HumanResources.HRResumeSchemaCollection) PRIMARY KEY CLUSTERED(JobCandidateID))

INSERT @TestXmls (JobCandidateID,[Resume])

SELECT JobCandidateID,[Resume] FROM HumanResources.JobCandidate
SELECT * FROM @TestXmls

GO

USE AdventureWorks
GO

It works perfectly. However, if you change the table variable to a temp table:

CREATE TABLE #TestXmls (JobCandidateID int NOT NULL,
  [Resume] xml(HumanResources.HRResumeSchemaCollection) 
  PRIMARY KEY CLUSTERED(JobCandidateID))

You will get the following error:

Msg 6314, Level 16, State 1, Line 2

Collection specified does not exist in metadata : 'HumanResources.HRResumeSchemaCollection'

This is because a temp table is created in TempDB, which does not have the xml collection HumanResources.HRResumeSchemaCollection. Though you can create the xml collection in the TempDB, it's not practical because:

• TempDB is recreated whenever SQL server restarts;
• TempDB is shared by all user/system databases and it is easily to have naming conflicts;

The work around is to use un-typed xml in the temporary table.

CREATE TABLE #TestXmls (JobCandidateID int NOT NULL,
  [Resume] xml PRIMARY KEY CLUSTERED(JobCandidateID))

INSERT #TestXmls (JobCandidateID,[Resume])

SELECT JobCandidateID,[Resume] FROM HumanResources.JobCandidate
SELECT * FROM #TestXmls
GO

User Defined DataTypes and User Defined Types (UDTs)

A user defined data type and UDT (CLR type) is similar to xml collection. It only resides in the database it is created. The AdventureWorks sample database has a user defined data type dbo.name, whose native data type is nvarchar(50). The following script queries all the address type names:

USE AdventureWorks
GO
DECLARE @TestUDTs TABLE (AddressTypeID int NOT NULL,
  [Name] dbo.Name NOT NULL PRIMARY KEY CLUSTERED(AddressTypeID))

INSERT @TestUDTs(AddressTypeID,[Name])

SELECT AddressTypeID,[Name]
 FROM Person.AddressType
SELECT * FROM @TestUDTs

It works fine. If you change to use temp table:

CREATE TABLE #TestUDTs (AddressTypeID int NOT NULL,
[Name] nvarchar(50) NOT NULL PRIMARY KEY CLUSTERED(AddressTypeID))

It will fail with the following error:

Msg 2715, Level 16, State 7, Line 1

Column, parameter, or variable #2: Cannot find data type dbo.Name.

The work around is to use the native data type of the user defined data type:

CREATE TABLE #TestUDTs (AddressTypeID int
NOT NULL,
[Name] nvarchar(50) NOT NULL PRIMARY KEY CLUSTERED(AddressTypeID))

UDTs are also scoped to a single database. If you use UDTs in a table variable, they are used in the same way as native data types. However since there is no equivalent native data type, if you need to use a UDT in a temp table, the UDT must be registered in tempdb the same way as for a user database, i.e. you need to CREATE the ASSEMLY and CREATE the TYPE in the TempDB. Due to the reasons mentioned in XML Collection

Collation

The collations for the string type (char, nchar, varchar, nvarchar) columns, if not given explicitly, will take the database collation of TempDB in a temp table, and inherit the collation of the current user database in a table variable. If not handled correctly, string comparing will fail.

Let's first find out the collation of the TempDB:

SELECT collation_name FROM sys.databases
WHERE database_id = DB_ID('tempdb')

To list all available collations:

SELECT * FROM fn_helpcollations()

Now let's create a test database, remember to use a different collation than the TempDB:

CREATE DATABASE CollateTest COLLATE Albanian_BIN
GO
USE CollateTest
GO

Create a table and populate some data:

CREATE TABLE dbo.Test1 (C2 int NOT NULL, C1 nvarchar(100) NOT NULL PRIMARY KEY CLUSTERED(C2))

INSERT dbo.Test1 (C1,C2) SELECT N'R1',100 UNION ALL SELECT N'R2',200

Now use a table variable to join with the table:

DECLARE @TestCollations TABLE (RID int identity not null, [Name] nvarchar(100) NOT NULL
    PRIMARY KEY CLUSTERED(RID))

INSERT @TestCollations ([Name]) VALUES (N'R2')
INSERT @TestCollations ([Name]) VALUES (N'R3')

SELECT A.* FROM dbo.Test1 A, @TestCollations B WHERE A.C1=B.[Name]
GO

It works fine. If you change to use temp table:

CREATE TABLE #TestCollations (RID int identity not null, [Name] nvarchar(100) NOT NULL
    PRIMARY KEY CLUSTERED(RID))

INSERT #TestCollations ([Name]) VALUES (N'R2')
INSERT #TestCollations ([Name]) VALUES (N'R3')

SELECT A.* FROM dbo.Test1 A, #TestCollations B WHERE A.C1=B.[Name]
GO

You get the following error:

Msg 468, Level 16, State 9, Line 6

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Albanian_BIN" in the equal to operation.

There are two workarounds. One is to force the collation conversion in the string comparing:

SELECT A.* 
 FROM dbo.Test1 A, #TestCollations B 
  WHERE A.C1=B.[Name] collate database_default

However it's a little bit annoying if you have lots of string comparing in your database. A better solution is to explicitly give the collation for the string column in the temp table:

IF object_id('tempdb..#TestCollations') IS NOT NULL
 DROP TABLE #TestCollations
GO
CREATE TABLE #TestCollations (
  RID int identity not null, 
  [Name] nvarchar(100) collate database_default NOT NULL 
  PRIMARY KEY CLUSTERED(RID))

INSERT #TestCollations ([Name]) VALUES (N'R2')
INSERT #TestCollations ([Name]) VALUES (N'R3')

SELECT A.* FROM dbo.Test1 A, #TestCollations B WHERE A.C1=B.[Name]
GO

The "collate database_default" clause in the column definition forces SQL Server to take current database(CollateTest)'s collation instead of TempDB's for the temp table.

If a SQL Server hosts multiple databases, or you need to restore external databases to the server, you can easily run into the collation conflict issue if the user databases use temp tables and string columns do not declare collations properly.

Conclusion

If you use table variables, you are free to use user defined data types, user defined types (UDTs), and xml collections that are defined in your database. You don't need to worry about collation conflicts. If you have to use a temp table due to various reasons, please keep in mind the following best practices:

• Always attach "collate database_default" clause for string columns in a temp table.
• Always use un-typed xml for xml columns in a temp table.
• Always use the equivalent native data type for a user defined data type in a temp table.
• If you need to use UDTs in a temp table, you have to register the type in TempDB.

 Appendix

Table Variable vs. Temp Table Summary:

Applications running in production frequently, at least more so than we are prone to admit, have problems. Diagnosing these problems can be a nightmare when we do not have access to information regarding the error. I have seen several solutions, but none that would allow me to log all activity in a central location. The one that comes most quickly to mind, is to use a table in a database to contain all of the messages.

The problem for me was that the table would grow very quickly to a point at which it was unmanageable. I could partition the table to increase the speed of both insert and query operations, but I would still have to manage the database (backup, etc.). I did like the idea of inserting a row into a table, however, as this made it simple to log activity by merely inserting the information into a table.

I also wanted to write the log messages to text files that would exist in a location that was readily available to me to be able to research problems. Typically, there are no shares defined on database servers that are available to developers

I finally hit on what I believe is a rather elegant solution: to overcome the growth of the table, I decided to use Service Broker.

Service Broker is a delivery system for routing messages from a Queue to a Service. This routing begins by what is known as the Initiator beginning a conversation with Service Broker and specifying the Target Service with which it wants to communicate. The Target Service can then send a response to the Initiator by sending a message back to the Initiator Service. By defining a Target Queue associated with the Target Service, the effect is that the Message is placed in the Target Queue. The processing of the messages in the Target Queue can be handled directly by SQL Server by specifying a stored procedure to call whenever a message appears on the queue.

The problem I had with using a stored procedure to process the messages was that I wanted to be able to specify a location for the log files and have flexibility in the format of the written messages. After a little reading, I decided that I could create a Windows Service that would query the Target Queue and process the messages. This meant that I would have control over the location of the log files and the format of the messages.

The tasks I needed to complete in order to make this solution work were:

• Define a table in the database of your choice. If you will be logging messages from multiple servers, creating a linked server in each of the other servers can simplify the insertion from these servers into the table.
• Define an INSTEAD trigger for the table that would handle the conversation with the Service Broker service.
• Define a Windows Service that would RECEIVE those messages and write them to a log file.

The sequence of events is:

1. The application senses a condition that requires logging information.
2. The application inserts the pertinent information into the log table.
3. The trigger for the table fires and begins the conversation with the Service Broker Target Service.
4. The Service Broker service adds the message to the Target Queue.
5. The Windows Service receives the message and writes the formatted information to the appropriate log file.

That's the guts of it. The details follow.

Message Type

This defines what kind of messages will be handled. The only specification here is that they will all be XML and verified by Service Broker as valid.

CREATE MESSAGE TYPE [LogRequest] 
 AUTHORIZATION [dbo] 
 VALIDATION = WELL_FORMED_XML

Message Contract

This defines what type of message will be handled and who will send it. We are only interested in sending messages from the client, so we specify the initiator as the sender.

CREATE CONTRACT [LogContract] 
 AUTHORIZATION [dbo] 
 (
 [LogRequest] SENT BY INITIATOR
 )

Queues

Initiator Queue

This defines the queue for the initiator. It is only used to send messages back to the sender, which we are not doing, but it is good practice to define one in case you ever decide to do so.

CREATE QUEUE [dbo].[LogInitiatorQueue]

Target Queue

This defines the queue into which the sender will put the messages.

CREATE QUEUE [dbo].[LogTargetQueue] 
 WITH 
 STATUS = ON , 
 RETENTION = OFF 
 ON [PRIMARY]

Services

Initiator Service

This defines the service for retrieving the messages from the initiator queue.

CREATE SERVICE [LogInitiatorService] 
 AUTHORIZATION [dbo] 
 ON QUEUE [dbo].[LogInitiatorQueue]

Target Service

This defines the service that will handle the messages from the target queue.

CREATE SERVICE [LogTargetService] 
 AUTHORIZATION [dbo] 
 ON QUEUE [dbo].[LogTargetQueue] 
 (
 [LogContract]
 )

Log Table

This table is where the client will insert the messages.

CREATE TABLE [dbo].[LogData]( 
 [MachineName] [varchar(50)] NOT NULL,
 [ProcessName] [varchar](50) NOT NULL,
 [ProcessMessage] [varchar](max) NOT NULL,
 [LogTime] [datetime] NULL
) ON [PRIMARY]

The messages will never actually be inserted into the table. Rather, they will be re-routed to the target service by the use of a trigger that is executed instead of allowing the data to be inserted.

CREATE TRIGGER [dbo].[LogData_Insert] 
 ON [dbo].[LogData] 
 INSTEAD OF INSERT
AS 
BEGIN
 SET NOCOUNT ON;
DECLARE 
 @MessageBody XML, 
 @Handle UNIQUEIDENTIFIER,
 @RequestMsg XML SELECT 

 @RequestMsg = 
 (
  SELECT 
   *
  FROM 
   Inserted 
  FOR XML PATH ('Row'), ROOT('LOG'), TYPE
 );
BEGIN TRANSACTION 
BEGIN DIALOG @Handle
 FROM SERVICE
 [LogInitiatorService]
 TO SERVICE
 N'LogTargetService'
 ON CONTRACT
 [LogContract]
 WITH
 ENCRYPTION = OFF;SEND ON CONVERSATION @Handle
 MESSAGE TYPE 
 [LogRequest](@RequestMsg);
COMMIT TRANSACTION
END

Windows Service

The service will be a Windows Service that will constantly be trying to receive messages from the Target Queue. The standard plumbing for a Windows Service will not be discussed here. The central code that performs the function of receiving the messages from the queue is as follows:

1. Create a connection to the database containing the Service Broker Target Queue.
2. Create a command for the connection that will receive the next message on the Target Queue. This command will wait forever for a message to be available.
3. Create an XML document from the character string received from the execution of the command. This allows us to retrieve the information within the message. Remember that the messages on the Target Queue are XML.
4. Format the information in the message.
5. Determine the log file to which you want to write the message. In my case, I am writing all log files by each computer in its own folder. Each log file will be named using the ProcessName within the message.
6. If log files are being created for each day, or some other period, check to se if the current file needs to be moved o an archive location.
7. Write the formatted message to the log file.

Connection

using ( SqlConnection Conn = new SqlConnection ( this.LogConnectionString ) )
{
Conn.Open ();

Command

using ( SqlCommand Cmd = Conn.CreateCommand () )
{
Cmd.CommandText = "ProcessLogQueue";

The contents of the stored procedure follow:

BEGIN TRANSACTION 

WAITFOR (RECEIVE TOP(1) 
 CONVERT (XML, CONVERT (VARCHAR(MAX), message_body) ) 
FROM 
 LogTargetQueue 

COMMIT TRANSACTION

Cmd.CommandType = System.Data.CommandType.StoredProcedure;
Cmd.CommandTimeout = 0; // no timeout

while ( !this.m_Terminate ) // looping until the service is stopped
{
string Response = Cmd.ExecuteScalar ().ToString (); // execute the command

if ( Response.Length > 0 )
{
XmlDocument Doc = new XmlDocument ();
Doc.LoadXml ( Response );
XmlNode RootNode = Doc.SelectSingleNode ( "LOG" );
XmlNode RowNode = RootNode.SelectSingleNode ( "Row" );
string ProcessName = RowNode.SelectSingleNode ( "ProcessName" ).InnerText;
string MachineName = RowNode.SelectSingleNode ( "MachineName" ).InnerText; string MachineFolder = Path.Combine ( this.LogFolder, MachineName );

if ( !Directory.Exists ( MachineFolder ) )
{
try
{
Directory.CreateDirectory ( MachineFolder );
}
catch ( Exception Ex )
{
// log the failure to a logfile for the Windows Service
return;
}
}

// create the name of the log file

string FileName = Path.Combine ( MachineFolder, ProcessName + ".log" );

try
{
this.CheckLog ( MachineName, FileName ); // does the log file nee to be archived?
}
catch ( Exception Ex )
{
// log the failure to a logfile for the Windows Service
return;
}

using ( StreamWriter SW = new StreamWriter ( FileName, true ) )
{
SW.WriteLine ( string.Format ( "{0} {1}", RowNode.SelectSingleNode ( "LogTime" ).InnerText, RowNode.SelectSingleNode ( "ProcessMessage" ).InnerText ) );
}
}
}
}
}
}
catch ( SqlException Ex )
{
// log the failure to a logfile for the Windows Service and quit
}
catch ( System.Threading.ThreadAbortException )
{
// we have been ordered to quit
}
catch ( Exception Ex )
{
// log the failure to a logfile for the Windows Service and quit
}
}

private void CheckLog ( string MachineName, string FileName )
{
FileInfo FI = new FileInfo ( FileName );

if ( FI.Exists )
{
DateTime Today = Convert.ToDateTime ( DateTime.Now.ToShortDateString () );

if ( FI.LastWriteTime < Today )
{
string ArchiveFileName = Path.GetFileName ( FileName ).Replace ( ".log", FI.LastWriteTime.ToString ( "yyyyMMdd" ) + ".log" );
string MachineFolder = Path.Combine ( this.ArchiveFolder, MachineName );

if ( !Directory.Exists ( MachineFolder ) )
{
Directory.CreateDirectory ( MachineFolder );
}

FI.MoveTo ( Path.Combine ( MachineFolder, ArchiveFileName ) );
}
}
}

An example of the XML produced by the trigger on the log table follows:

<LOG>
 <Row>
  <ProcessName>MyProcess</ProcessName>
  <ProcessMessage>This is the message</ProcessMessage>
  <LogTime>2009-12-20T11:47:47.677</LogTime>
  <MachineName>MyMachine</MachineName>
 </Row>
</LOG>

The essentials here are that we are requesting the next entry from the queue and will wait forever for it. Once we have a message, we extract the Machine Name and Process Name and build a file name from them, using the Machine Name as the folder name. If the file exists, we check to see when a message was last written. If it was not today, we rename it and move it to the archive folder.

Client

As was indicated earlier, the Client, either a stored procedure or any Win32 application, will quite simply insert the message into the database table and move on. As an example, suppose you have an order processing system and, at the end of the process you want to log the number of orders processed by each customer in a batch. You would collect the summary information in a temporary table and run the following code. By doing this, you have started the process described at the beginning of this document.

Note that, if the ProcessMessage is NULL, the Service Broker conversation will fail.

 INSERT INTO Logging.dbo.LogData
 (
  MachineName,
  ProcessName,
  ProcessMessage
 )
 SELECT
   CAST(SERVERPROPERTY('MachineName') AS varchar),
   'ProcessCustomerOrders',
   'Customer: ' + CAST(CutomerID AS varchar) + ' Orders: '+ CAST(OrderCount AS varchar)
  FROM
   @CustomerOrders

Conclusion

In the end, everyone will have their own preferences for this. Being an old main-frame nut, I am very used to scanning logs. The accumulation of logging information into text files makes it very easy for me to locate problems or trace activity without a lot of effort. As an aside, if you find yourself frequently looking at file content, I advise you to take a look at V (available at http://www.fileviewer.com/). It is hands-down the best utility I have ever seen. It includes the ability to tail a file to watch as it changes.

select count(*)

from tblPartitioned

where commodity = 'BOFRB'

select count(*)

from tblUnPartitioned

where commodity = 'BOFRB'

-----------

371

Table 'tblPartitioned'. Scan count 54, logical reads 109, physical reads 0, read-ahead reads 0, 

lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-----------

371

Table 'tblUnPartitioned'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, 

lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The graphical query plan clearly shows the difference in cost between the two queries. The top partitioned table query