CTE vs Temp Table vs Temp variables

Temp tables: they are just like any normal tables except that they are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that it calls. Just like normal tables and have primary keys, constraints, indexes and column statistics which are kept for the table which makes query execution really fast in most cases.

Temp tables have space assigned to them in the TempDB database and are generally accessed only from memory, unless the server is under memory pressure or the amount of data in the table is large.

Table Variables: These tables behave very much like other variables in their scoping rules. They are created when they are declared and are dropped when they go out of scope. They cannot be explicitly dropped. Like with temp tables, table variables also reside in TempDB. They are also generally accessed only from memory, unless the server is under memory pressure or the amount of data in the table is large.

Table variables can have a primary key, but no indexes can be created on them. They don’t have any statistics maintained on the columns. This makes table variables less optimal for large numbers of rows, as the optimizer has no way of knowing the number of rows in the table variable.

CTE: CTE’s are more like temporary views than anything else. When you look at the execution plan, you’ll see that they are substituted/inlined into the query but not materialized and stored anywhere. With the exception of recursion, they’re more to make queries simpler to write than faster to run.

Hope this helps…

Temporary Variables vs Temporary Tables SQL Server 2008/2005

Myth: A table variable is a memory-only structure.
This is not True. A Temp table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables.

Note: If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

Normally whether to go with Temp Variables or Temp Tables depends on:
•The number of rows that are inserted to the table.
•The number of recompilations the query is saved from.
•The type of queries and their dependency on indexes and statistics for performance.
Some other important advantages of temporary tables:
•Non-clustered indexes can be created on Temporary Tables. In Temp variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint, no other indexes can be created. That can influence the query performance when compared to a temporary table with non-clustered indexes.
•Table variables do not maintain statistics like temporary tables can. When working with complex queries on large tables, the lack of statistics may adversely affect the optimizers execution plan for a query, thus affecting the performance of that query.

Some Advantages of Table variables are:
•Temp Variables have a well-defined scope at the end of which they are automatically cleared off.
•Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
•Table variables require less locking and logging resources because they only for the duration of an update on the table variable. Since table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.

Normally, it is recommended that we can use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table. In that case, you can create indexes on the temporary table to increase query performance.
It’s always best to test both table variables and temporary tables for a particular query or stored procedure.

Hope this helps….

SSRS Queries for a DBA or Developers to monitor Reporting Server

The Script to Check Reports and Subcriptions with Sorted results per last Run Date

SELECT c.Name AS ReportName
, rs.ScheduleID AS JOB_NAME
, s.[Description]
, s.LastStatus
, s.LastRunTime
FROM ReportServer..[Catalog] c
JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID
jOIN ReportServer..ReportSchedule rs ON c.ItemID = rs.ReportID
AND rs.SubscriptionID = s.SubscriptionID
order by 5

To Check Latest Running reports with Full details of who is using reports sevices at what time and which report,…. etc..

SELECT [InstanceName]
,b.name
,[UserName]
,[RequestType]
,[Format]
,[Parameters]
,convert(varchar(10),[TimeStart],103)+' '+convert(varchar(8),[TimeStart],108) as TimeStart
,convert(varchar(10),[TimeEnd],103) +' '+convert(varchar(8),[TimeEnd],108) as TimeEnd
,[TimeDataRetrieval]
,[TimeProcessing]
,[TimeRendering]
,[Source]
,[Status]
,[ByteCount]
,[RowCount]
FROM [ReportServer].[dbo].[ExecutionLog] a
inner join [ReportServer].[dbo].[Catalog] b
on a.reportid=b.itemid

Parameter Sniffing in Stored Procedures in SQL Server 2008/2008 R2

Parameter Sniffing in Stored Procedures in SQL Server 2005/2008/2008 R2
Today let me cover one of a good topic on Parameter Sniffing in Stored Procedures:

Paramater Sniffing is not a unexpected behaviour when passing parameters into Store Procedure.


CREATE procedure [dbo].[Usp_Test_SP]
@Category as [nvarchar](60)=null
as
select Category ,Description,WorkOrder,Date Added from TestTable
where (sc.session_class_desc like @Category +'%' or @Category is null)

Say about SP causes a index seek + bookmark lookup then this compiled plan will be cached and reused for subsequent executions of the procedure.

But when sometime if rows returned are very high like 30% of Table rows then compiled plan may result in Full table scan and this is stored for future use.

and thus some executions will result in more time and some run quickly.

Easiest Approach to solve this is to use Temporary variable to store the parameters before using them in where Clause. For Example the above Stored Procedure Re-Written as below:


CREATE procedure [dbo].[Usp_Test_SP]
@Category as [nvarchar](60)=null
as
Declare @Category1 nvarchar(60)
set @Category1 = @Category
select Category ,Description,WorkOrder,Date Added from TestTable
where (sc.session_class_desc like @Category1 +'%' or @Category1 is null)

2nd Way of dealing this situation is using Recompile query hint.
This way is more helpfull if SP is used very less and above method does not work.

SQL Server DBA Home