T-SQL: Using result of a dynamic SQL query in a variable or table

Although, not a recommended practice, but sometimes we have to write our queries using dynamic SQL. In such situations, it is generally needed to fetch the result (scalar or tabular) of dynamic SQL into the main (non-dynamic) query. This is not straight forward because dynamic SQL runs in its own scope and we cannot access the variables defined in main query. This post presents a few approaches to consume the result of a dynamic SQL query:

sp_ExecuteSql stored procedure

This is the most generic and powerful method of invoking dynamic SQL since it allows us to write a parameterized dynamic query with input/output parameters. Here’s a simple example of using sp_executesql to consume the result of a dynamic SQL query:


declare @today datetime
exec sp_executesql
    N'Select @internalVariable = GetDate()', --dynamic query
    N'@internalVariable DateTime output', --query parameters
    @internalVariable = @today output --parameter mapping
select @today

Table variables and Temporary tables

This method is used when we want to get a tabular result set from our dynamic query. Here’s an example to get the result of a dynamically created SQL query by using table variables:


declare @myTable table
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

insert into @myTable
    exec (N'select name, database_id, create_date from sys.databases') --dynamic query

select * from @myTable

Here’s the same example that uses a temporary table to fetch the result set of a dynamic SQL query:


create table #myTable
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

insert into #myTable
    exec (N'select name, database_id, create_date from sys.databases') --dynamic query

select * from #myTable
drop table #myTable

Since temporary tables have physical existence so we can refer to the temporary table inside our dynamic SQL query as well. Here’s an example illustrating this technique:


create table #myTable
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

--dynamic query
exec sp_executesql
    N'insert into #myTable
        select name, database_id, create_date from sys.databases'

select * from #myTable
drop table #myTable

Temporary tables or Table variables can also be used to fetch the result of a stored procedure. Notice that for saving this result, the columns of table variable/temporary table must match with the result of stored procedure. That is, we need to take “ALL” the columns. Here’s an example that grabs the result set from a stored procedure into a table variable.


declare @myTable table
(
    ServerName nvarchar(256),
    NetworkName nvarchar(256),
    Status nvarchar(4000),
    ID int,
    Collation nvarchar(256),
    ConnectTimeOut int,
    QueryTimeOut int
)

insert into @myTable
    exec sp_helpserver

select * from @myTable

Also, here’s an example to get result of a stored procedure using temporary table:


create table #myTable
(
    ServerName nvarchar(256),
    NetworkName nvarchar(256),
    Status nvarchar(4000),
    ID int,
    Collation nvarchar(256),
    ConnectTimeOut int,
    QueryTimeOut int
)

insert into #myTable
    exec sp_helpserver

select * from #myTable
drop table #myTable

Thats all from me. Let me know if you have any more solutions.

SSMS: How to restore differential backups

There are two ways to restore a differential backup in SQL Server:

  • Directly use TSQL statements as described in this MSDN page
  • Use SQL Server Management Studio user interface as described here.

If you are using SQL Management Studio to restore differential backups, and you have restored full backups several time using SSMS, but this is your first time to restore a differential backup then you are likely to encounter the following error:

Restore failed for Server 'servername'. (Microsoft.SqlServer.SmoExtended)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)

This is because you tried to restore a differential backup on an available/operational/functional/running database which is not allowed.

In order to restore a differential backup, you will first need to restore the last full backup with NO RECOVERY option. So, in SSMS you need to select the appropriate full-backup and choose Restore With NoRecovery option from the Options page as depicted in the following screenshot.

restore-full-backup-with-norecovery

Once restored, the database will be shown in the Object Explorer as Restoring.

database-in-restore

Notice that the database is non-available/non-functional at this time and is waiting for a differential backup to be applied. Now, restore the appropriate differential backup and choose Restore With Recovery from the Options page:

restore-differential-backup-with-recovery

That’s it. You have successfully restored a differential backup.

A Final Note:

Note that differential backups are cumulative and each differential backup contain changes since the last full backup, not the last differential backup. So if you have a full backup of 2009-01-01 and have differential backups for each day, and you want to restore your database to 2009-01-10, then you just need to restore the full backup (with no recovery) of 2009-01-01 followed by the differential backup of 2009-01-10. For more information, read the following MSDN articles from SQL Server books online:

T-SQL: Using common table expressions (CTE) to generate sequences

One of the best enhancements in T-SQL with SQL Server 2005 was Common Table Expressions(CTEs). CTEs are very helpful in writing more readable and manageable queries. The good things don’t end here; self-referencing CTEs are a very powerful method of implementing recursion in SQL queries. In this post, I will present a few examples of generating sequences using CTEs.
The following statements create a number sequence from 1 to 1000.

--define start and end limits
Declare @start int, @end int
Select @start=1, @end=1000

;With NumberSequence( Number ) as
(
    Select @start as Number
        union all
    Select Number + 1
        from NumberSequence
        where Number < @end
)

--select result
Select * From NumberSequence Option (MaxRecursion 1000)

And the output is:
Number sequence

Notice that when selecting records from the CTE, we are setting a value for MaxRecursion Option. This one is important since the maximum recursion level for a CTE is set to 100 by default, and so any sequence with more than 100 rows will generate an error. We can turn the recursion limit off by using MaxRecursion 0, but this is not a good idea since it opens the possibility of infinite recursion in case of a small error in CTE.

Here’s another example that creates date sequence from 2009-03-01 to 2009-04-10.

--define start and end limits
Declare @todate datetime, @fromdate datetime
Select @fromdate='2009-03-01', @todate='2009-04-10'

;With DateSequence( Date ) as
(
    Select @fromdate as Date
        union all
    Select dateadd(day, 1, Date)
    	from DateSequence
    	where Date < @todate
)

--select result
Select * from DateSequence option (MaxRecursion 1000)

This one generates an output similar to:
Date Sequence

Notice, that this example, that generates date sequences can also be used to create Time Dimension for a Data Warehouse. If the granularity of time dimension is not too fine, CTEs give a nice alternative to loops. Here’s an example:

--define limits
Declare @todate datetime, @fromdate datetime
set @fromdate = '2009-01-01'
set @todate = '2009-12-31'

;With DateSequence( [Date] ) as
(
	Select @fromdate as [Date]
		union all
	Select dateadd(day, 1, [Date])
		from DateSequence
		where Date < @todate
)

--select result
Select
	CONVERT(VARCHAR,[Date],112) as ID,
	[Date] as [Date],
	DATEPART(DAY,[Date]) as [Day],
	CASE
		 WHEN DATEPART(DAY,[Date]) = 1 THEN CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'st'
		 WHEN DATEPART(DAY,[Date]) = 2 THEN CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'nd'
		 WHEN DATEPART(DAY,[Date]) = 3 THEN CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'rd'
		 ELSE CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'th'
	END as [DaySuffix],
	DATENAME(dw, [Date]) as [DayOfWeek],
	DATEPART(DAYOFYEAR,[Date]) as [DayOfYear],
	DATEPART(WEEK,[Date]) as [WeekOfYear],
	DATEPART(WEEK,[Date]) + 1 - DATEPART(WEEK,CAST(DATEPART(MONTH,[Date]) AS VARCHAR) + '/1/' + CAST(DATEPART(YEAR,[Date]) AS VARCHAR)) as [WeekOfMonth],
	DATEPART(MONTH,[Date]) as [Month],
	DATENAME(MONTH,[Date]) as [MonthName],
	DATEPART(QUARTER,[Date]) as [Quarter],
	CASE DATEPART(QUARTER,[Date])
		WHEN 1 THEN 'First'
		WHEN 2 THEN 'Second'
		WHEN 3 THEN 'Third'
		WHEN 4 THEN 'Fourth'
	END as [QuarterName],
	DATEPART(YEAR,[Date]) as [Year]
from DateSequence option (MaxRecursion 10000)

And here’s the beautiful output of the above query to be used as a time dimension (click to enlarge the image):
Time Dimension

TSQL Challenge 7: Listing the 5 biggest tables on the server

A few weeks ago, I stumbled upon this blog that presents cool T-SQL challenges. I submitted an entry for challenge 7 that asked to write the shortest script to list the 5 biggest tables on a server. Here’s my solution:

create table #temp
(
	[database] nvarchar(MAX),
	[table] nvarchar(MAX),
	[rows] int,
	[reserved_size] nvarchar(100),
	[data_size] nvarchar(100),
	[index_size] nvarchar(100),
	[unused_space] nvarchar(100)
)

declare @sql nvarchar(MAX)
set @sql=replace('if !~! not in (!master!,!model!,!msdb!,!tempdb!)
  exec [~].dbo.sp_msforeachtable
    "insert into #temp([table], [rows], [reserved_size], [data_size], [index_size], [unused_space])
      exec [~].dbo.sp_spaceused !?!"','!',char(39))

EXEC sp_MSForEachDB
	@command1=@sql,
	@command2="update #temp set [database]='~' where [database] is null",
	@replacechar='~'

select top(5) [database] as base, [table], [data_size] as size, [rows] as rows
from #temp
order by Cast(LEFT([data_size],len([data_size])-3) as int) desc

drop table #temp

So, I started by creating a temporary table with columns (database, table, rows, reserved_size, data_size, index_size, unused_space) for the output. I used the two undocumented stored procedures sp_MSforeachdb and sp_MSforeachtable to iterate through all the tables in all the databases and executed sp_spaceused as described in the following pseudo code:

foreach(database db in serverDatabases)
  if (db not in 'master', 'msdb', 'model', 'tempdb')
    foreach(table t in db.Tables)
    {
       insert into #temp (table, rows, reserved_size, data_size, index_size, unused_space)
         execute sp_spaceused for table 't'

       --at this point, our #temp table will be populated with data for each table
       --but the 'database' column will be 'null', so now replace it with the name of database
       update #temp
         set [database] = 'db' where [database] is null
    }

The most important part is that I am using an update operation for storing the database name in the temporary table. Thanks to Microsoft that we can give a set of 3 commands to the above mentioned undocumented stored procedures. Another hard part was to create a single t-sql statement that iterates for all tables inside a database and execute sp_spaceused. I did this by a complex combination of single quotes, double quotes and the replace function. In the last, I am just selecting the top(5) rows ordered by size.

To enter the contest, I reduced the script length by replacing all the variable/column names with a single length identifier. Here was my final submission:

create table #t(d nvarchar(MAX),t nvarchar(MAX),r int,x nvarchar(100),s nvarchar(100),y nvarchar(100),z nvarchar(100))
declare @s nvarchar(MAX)
set @s=replace('if !~! not in (!master!,!model!,!msdb!,!tempdb!) exec [~].dbo.sp_msforeachtable "insert into #t(t, r,x,s,y,z) exec [~].dbo.sp_spaceused !?!"','!',char(39))
EXEC sp_MSForEachDB @command1=@s, @command2="update #t set d='~' where d is null", @replacechar='~'
select top(5) d as base, t as [table], s as size, r as rows from #t order by Cast(LEFT(s,len(s)-3) as int) desc
drop table #t

Let’s wait and see the solution of other players.

T-SQL: sp_MSForEachDB and sp_MSForEachTable – Undocumented but very powerful stored procedures

I recently came across the following two undocumented stored procedures:

  • sp_MSforeachdb
  • sp_MSforeachtable

These stored procedures take a command (infact upto 3 commands) and run it for all the objects (databases on a server or tables inside a database, depending upon the stored procedure). Here are a few examples of the usage of these stored procedures (Note that these statements may take long duration depending upon the number of databases/tables):

--Display no. of objects per databases
exec sp_MSforeachdb 'select "?", count(*) as TotalObjects from [?].dbo.sysobjects'

--Display no. of rows per table
exec sp_MSforeachtable 'select "?" as TableName, count(*) as TotalRows from ?'

--Display total space used for each table
exec sp_MSforeachtable 'exec sp_spaceused [?]'

Beware, never ever underestimate the powers of these stored procedures. In a single line, you can:

  • Destroy all the databases on a server
  • Delete all the data inside all the tables
  • Drop all the tables
  • Even more dangerous things I can’t think at this time

In this small post, I am not going to demonstrate how to do the above mentioned tricks but make sure you are extremely cautious when using the above stored procedures.

C#: Executing batch T-SQL Scripts containing GO statements

At times, we developers need to run SQL scripts from our .NET applications, in, say Installer Applications. This is not always easy since large SQL scripts typically contain a GO statement to separate individual batches and our ADO.NET classes under System.Data namespace do not know how to handle it. The reason is that the “GO” statement is not a native T-SQL statement but rather used by SQL Server Management Studio to terminate the batches it is sending to the server. To tackle this situation, we have the following choices:

1. Split the script on “GO” command into smaller scripts and execute those individual scripts

A very primitive solution would be to split the script on “GO” text and run the individual sub-scripts in sequence. The problem is how to get a robust split mechanism. Generally, a line break before and after the GO works fine. Here’s how to do this:

//get the script
string scriptText = GetScript();

//split the script on "GO" commands
string[] splitter = new string[] { "\r\nGO\r\n" };
string[] commandTexts = scriptText.Split(splitter,
  StringSplitOptions.RemoveEmptyEntries);
foreach (string commandText in commandTexts)
{
  //execute commandText
}

The above code can produce unnecessary splitting in some situations, thus creating an incorrect SQL Command. A more better approach would be to use Regular Expressions. Again, the problem is how to create a pattern that is robust enough to tackle all sort of scripts. Lets look into the second solution now.

2. Use the Server class from SQL Server Management Objects (SMO)

For this, we need to add the following references in our project:

  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Management.Sdk.Sfc

After that, we can simply execute the entire script (with all the “GO” statements) using the code below:

string connectionString, scriptText;
SqlConnection sqlConnection = new SqlConnection(connectionString);
ServerConnection svrConnection = new ServerConnection(sqlConnection);
Server server = new Server(svrConnection);
server.ConnectionContext.ExecuteNonQuery(scriptText);

This is a fairly generic and robust solution and the great thing is that it does not require any change in our original script. Hooray!!!

SSIS: Comparing two versions of same package

SQL Server Integration Services yields a very bitter experience when comparing two versions of a package to see the differences. The problem is that the package XML is arbitrarily reordered when we try to save a package in the Visual Studio designer. Well, luckily, there’s a free and very nice solution to this problem: BIDS helper, a Visual Studio add-on that leverages BI development using SQL Server 2005/2008. SmartDiff is a part of BIDs helper and it compares two packages after pre-processing the package definition files and producing a uniform layout for comparison.

To demonstrate, let me show the result of two versions of one of my SSIS packages. I did not modify anything and just saved the same package twice. Here’s a summary for an ordinary diff that shows how the VS designer messes up the two versions:

Summary of ordinary diff

Using SmartDiff, here’s the result:

Summary after SmartDiff

Looks great, only two differences. Here’s the actual output from SmartDiff: Click to enlarge the image:

Actual Diff using SmartDiff

That’s really cool. BIDs helper is an add-on every BI developer must have. It contains many other features other than SmartDiff. Have a try here.