When working with SQL Server, there are often multiple ways to achieve the same result. One common scenario is the use of table variables and temp tables. While there are many articles and discussions on the differences and similarities between these two options, there are some lesser-known differences that are worth exploring.

XML Collection

One key difference between table variables and temp tables is how they handle XML collections. If you have an XML collection in your database and you try to use it in a temp table, you may encounter an error. This is because temp tables are created in TempDB, which does not have access to the XML collection. One workaround is to use un-typed XML in the temp table instead.

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

User Defined DataTypes and User Defined Types (UDTs)

Similar to XML collections, user defined data types (UDTs) and user defined types (CLR types) are scoped to the database in which they are created. If you try to use a UDT in a temp table, you may encounter an error. One workaround is to use the native data type of the UDT in the temp table instead.

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

INSERT #TestUDTs (AddressTypeID, [Name])
SELECT AddressTypeID, [Name] FROM Person.AddressType

SELECT * FROM #TestUDTs

Collation

Collation can also be a factor when using table variables and temp tables. If the collation for string columns is not explicitly specified, temp tables will inherit the collation of the TempDB, while table variables will inherit the collation of the current user database. This can lead to collation conflicts when comparing strings. One solution is to force collation conversion in the string comparison, or explicitly specify the collation for the string column in the temp table.

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]

Conclusion

When deciding between table variables and temp tables, it’s important to consider these differences. Table variables allow you to use user defined data types, UDTs, and XML collections defined in your database without any issues. However, if you need to use a temp table, it’s best to follow these best practices:

• Always attach the “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, register the type in TempDB.

By understanding these differences and following best practices, you can make informed decisions when working with table variables and temp tables in SQL Server.