Monthly Archives: March 2016

T-SQL

How I Create a Nums Table

Leave a comment

You need a Nums or Tally table. You really do. OK, you can make do without, but having one really makes a lot of operations easier.

A Nums table is a good candidate for either an admin database or for the user database, depending on how tightly coupled it is with the stored procedures, functions, views, etc. On the one hand, you might want to make sure that the Nums table moves with a backup and restore of your primary database. On the other hand, you may not want the overhead of multiple Nums tables across several databases.

My preferred way to create and populate a Nums tables is:

create table Nums
(
	n int not null,
	constraint pk_Nums primary key clustered (n)
);
 
with	l0 as (select 1 v union all select 1),
		l1 as (select a.v from l0 a, l0),
		l2 as (select a.v from l1 a, l1),
		l3 as (select a.v from l2 a, l2),
		l4 as (select a.v from l3 a, l3),
		l5 as (select a.v from l4 a, l4),
		n as (select row_number() over (order by (select null)) n from l5)
insert Nums(n)
select n.n
from n
where n.n <= 1000000;

The downside here is that the code is ugly, and unless you are familiar with what’s
going on it may take a bit to decipher the script.

The upside is that the script is fast (about 5 seconds on my machine to load 1,000,000 records) and is quite adaptable. If you want more than one million numbers in the Nums table, just increase the value in the script up to about 4 billion. And if for reason you need more than that just add another CTE for “l6” following the same pattern.

For a million numbers, this occupies less than 13 MB of storage, so in most cases the overhead for a single database is pretty trivial.

Finally, I really like the flexibility of taking this same method and generating a virtual Nums table. Yes, it add a lot of ugly to an otherwise nice script, but you also get independence from having to have that Nums table, which is useful in conditions where can’t have an Admin database and can’t modify the user database.

If I want to find gaps in the IDs in my Customer table, I can come up with something like this:

declare @maxCustomerId int = (select max(CustomerID) from Customer);
 
with	l0 as (select 1 v union all select 1),
		l1 as (select a.v from l0 a, l0),
		l2 as (select a.v from l1 a, l1),
		l3 as (select a.v from l2 a, l2),
		l4 as (select a.v from l3 a, l3),
		l5 as (select a.v from l4 a, l4),
		Nums as (select row_number() over (order by (select null)) n from l5)
select	Nums.n MissingCustomerId
from	Nums left join Customer cust on Nums.n = cust.CustomerID
where	Nums.n <= @maxCustomerId
and		cust.CustomerID is null;

The nice thing is that SQL Server quite efficiently optimizes the query and doesn’t evaluate any more CTEs than are really necessary based on the constraints used on the Nums CTE.

This also makes it easy and efficient to create a table-valued function instead of (or in addition to) a persisted Nums table:

if exists (select * from sys.objects where type = 'IF' and name = 'fnNums')
	drop function dbo.fnNums;
go
create function dbo.fnNums()
returns table
as
return
with	l0 as (select 1 v union all select 1),
		l1 as (select a.v from l0 a, l0),
		l2 as (select a.v from l1 a, l1),
		l3 as (select a.v from l2 a, l2),
		l4 as (select a.v from l3 a, l3),
		l5 as (select a.v from l4 a, l4),
		Nums as (select row_number() over (order by (select null)) n from l5)
select Nums.n
from Nums
go
SQL Development

Capturing output from SQL Server using C#

3 Comments

I have recently been doing some preliminary work on a tool that needs to read non-tabular output from SQL Server. This is the stuff that normally appears on the “messages” tab in Management Studio.

Consider the following query:

1
2
3

set nocount on;
print 'Now selecting from sys.databases';
select * from sys.databases;

When I run this in SSMS, I get the following on the messages tab:

Now selecting from sys.databases

So how we capture the print statement? It turns out to be pretty easy.

using System;
using System.Data;
using System.Data.SqlClient;
 
namespace Playground
{
    class SqlOutputCapture
    {
        public static void Test()
        {
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = @"servername";
            sb.InitialCatalog = "master";
            sb.IntegratedSecurity = true;
 
            using (SqlConnection connection = new SqlConnection(sb.ToString()))
            {
                connection.InfoMessage += OnInfoMessageGenerated;                string sql = @"set nocount on; print 'Now selecting from sys.databases';
					select * from sys.databases;";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        using (DataSet set = new DataSet())
                        {
                            adapter.Fill(set);
                        }
                    }
                }
            }
        }
 
        private static void OnInfoMessageGenerated(object sender, SqlInfoMessageEventArgs args)        {            Console.WriteLine("{0}", args.Message);        }    }
}

This writes the following to the console.

Now selecting from sys.databases

So let’s flip the NOCOUNT to off:

                string sql = @"set nocount off; print 'Now selecting from sys.databases';
					select * from sys.databases;";

When we run the query in SSMS, we now get:

Now selecting from sys.databases

(33 row(s) affected)

But executing the C# code outputs:

Now selecting from sys.databases

So why don’t we get the message about the number of rows?

It turns out that not everything that appears in Management Studio’s “messages” tab directly comes from SQL Server, at least in literal text format. Setting NOCOUNT OFF doesn’t cause SQL Server to return a literal string “(33 row(s) affected)” but rather affects the data sent in the underlying TDS network stream.

We can sort of emulate what SSMS does.

        public static void Test()
        {
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = @"servername";
            sb.InitialCatalog = "master";
            sb.IntegratedSecurity = true;
 
            using (SqlConnection connection = new SqlConnection(sb.ToString()))
            {
                connection.InfoMessage += OnInfoMessageGenerated;
                string sql = @"set nocount off; print 'Now selecting from sys.databases';
					select * from sys.databases;";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.StatementCompleted += OnStatementCompleted;                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        using (DataSet set = new DataSet())
                        {
                            adapter.Fill(set);
                        }
                    }
                }
            }
        }
 
        static void OnStatementCompleted(object sender, StatementCompletedEventArgs args)        {            Console.WriteLine("({0} row(s) affected)", args.RecordCount);        } 
        private static void OnInfoMessageGenerated(object sender, SqlInfoMessageEventArgs args)
        {
            Console.WriteLine("{0}", args.Message);
        }

And now we get:

Now selecting from sys.databases
(33 row(s) affected)

Another option is to use the connection statistics:

        public static void Test()
        {
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = @"servername";
            sb.InitialCatalog = "master";
            sb.IntegratedSecurity = true;
 
            using (SqlConnection connection = new SqlConnection(sb.ToString()))
            {
                connection.InfoMessage += OnInfoMessageGenerated;
                connection.StatisticsEnabled = true;                string sql = @"set nocount off; print 'Now selecting from sys.databases';
					select * from sys.databases;";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        using (DataSet set = new DataSet())
                        {
                            adapter.Fill(set);
                        }
                    }
                }
 
                System.Collections.IDictionary statistics = connection.RetrieveStatistics();
                object rowsAffected = statistics["SelectRows"];                Console.WriteLine("({0} rows(s) affected)", rowsAffected);            }
        }

Which also returns:

Now selecting from sys.databases
(33 row(s) affected)

However, be aware that the number of rows returned is cumulative for the connection. The statistics that are returned contain other interesting properties, including ConnectionTime, ExecutionTime, and ServerRoundtrips, as well as data about the network traffic, including BytesSent and BytesReceived.

It is worth nothing that the SqlConnection.InfoMessage sends more than a simple message to the output stream. The SqlInfoMessageEventArgs object also contains an Errors property which is a collection of errors being returned to the client. Using this, we can capture error information similar to what is display in SSMS. Let’s put a couple of errors into our SQL statement.

        public static void Test()
        {
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = @"servername";
            sb.InitialCatalog = "master";
            sb.IntegratedSecurity = true;
 
            using (SqlConnection connection = new SqlConnection(sb.ToString()))
            {
                connection.Open();
                connection.InfoMessage += OnInfoMessageGenerated;
                connection.FireInfoMessageEventOnUserErrors = true;                string sql = "printf 'Now selecting from sys.databasii'; select * from sys.databasii;";                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        using (DataSet set = new DataSet())
                        {
                            adapter.Fill(set);
                        }
                    }
                }
            }
        }
 
        private static void OnInfoMessageGenerated(object sender, SqlInfoMessageEventArgs args)        {            foreach (SqlError err in args.Errors)            {                Console.WriteLine("Msg {0}, Level {1}, State {2}, Line {3}",                    err.Number, err.Class, err.State, err.LineNumber);                Console.WriteLine("{0}", err.Message);            }        }

The output is:

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'printf'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.databasii'.