Creating Tally Tables using CTE in SQL Server 2005/2008

A Tally table is simply a table with a single column of very well indexed sequential values starting at any value of your requirement and going up to some predefined threshold. The starting value & the ending value should not be arbitrary. It should be as per the requirement in question.

There are n numbers of ways in which a Tally Table could be generated and the most obvious one is by making use of loops.

Here, I would demonstrate how we could generate a Tally Table using CTE. We will make use of the recursive nature of CTEs to get our job done.

Generating a sequence of numbers from 1 to 20

DECLARE @Max AS INT = 20

;WITH CTE AS (

  SELECT 1 Num

  UNION ALL

  SELECT Num + 1 FROM CTE WHERE Num < @Max

)

SELECT * FROM CTE

Generating a sequence of Dates starting with the current date & going till next 20 days

DECLARE @MaxDate AS DATETIME = GETDATE() + 20

;WITH CTE AS (

  SELECT GETDATE() Dates

  UNION ALL

  SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate

)

SELECT * FROM CTE

I love this way of generating the Tally Tables as it’s just too simple and on the fly.

How do we read the SQL Server error log?

Well there is an undocumented system procedure to do it sp_readerrorlog.This procedure reads from the disk using an extended stored procedure. To use it you need to be in the security admin group. Or you will get -

Msg 15003, Level 16, State 1, Line 1

Only members of the securityadmin role can execute this stored procedure.

The parameters for sp_readerrorlog are -

@p1 = Log file to read, this is the number, if you specify 0 you will get the current file ERRORLOG 
@p2 = Type of log to read, SQL Server (1) or SQL Agent (2) 
@p3 = Some text to find in the log entries 
@p4 = Some more text to find in the log entries (essentially the result is @p3 AND @p4)

All of the above stated parameters are optional and the default values for each are –

@p1 = 0 (ZERO); will get the current file ERRORLOG 
@p2 = SQL Server (1) 
@p3 = NULL
@p4 = NULL

Using this information we can do the following

EXEC sp_readerrorlog 0, 1, 'SEARCH TEXT 1',' SEARCH TEXT 2'

This only reads one file. If the files have been cycled or the server restarted we will miss our change. We can use a second undocumented procedure to get the list of log files - sp_enumerrorlogs

This takes 1 optional parameter

@p1 = Type of log to read, SQL Server (1) or SQL Agent (2). Same as @p2 for sp_readerrorlog. This defaults on 1 if not specified.

Using these together we can go through the logs looking for our changes.

Prevent recursive CTE from entering an infinite loop

Common Table Expression (CTE) is not a new feature now. It has been around with the launch of SQL Server 2005. Since then, we might have come across many powerful examples of using CTE and have made the most out of it. The best use of CTE I take is through recursion. We can create recursive CTE to get our job done very easily in situations where we need to repeatedly refer a table/view.

The most common situation in which developers land up is an infinite loop for a badly build CTE. So, to counter this situation, we can make use of MAXRECURSION option.

Let’s give it a hands on..

USE AdventureWorks

GO

;WITH EmpCTE

AS

(

  SELECT

    EmployeeID, ManagerID

  FROM

    HumanResources.Employee

  WHERE

    ManagerID IS NULL

  UNION ALL

  SELECT

    e.EmployeeID, e.ManagerID

  FROM

    HumanResources.Employee e

  INNER JOIN

    EmpCTE cte

  ON cte.EmployeeID = e.ManagerID

)

SELECT * FROM EmpCTE OPTION (MAXRECURSION 4)

GO

Now if your CTE goes beyond 4th recursion it will throw an error and stop executing. And if you happen put MAXRECURSION value too low, it might be possible that before your desired result is accomplished it will throw an error.

For example if you change MAXRECURSION to value 3. It will throw following error.

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 3 has been exhausted before statement completion.

In short, MAXRECUSION is good way to prevent recursive CTE to enter an infinite loop.

Find all empty tables in SQL Server Database

Yesterday I was working on one of my Databases and wanted to find out all the empty tables in that. Suddenly, I realized that there is no direct way to get a list of all those tables; even SSMS also doesn’t help much for this.

After googling for a few minutes, I decided to use one of the undocumented stored procedure - sp_Msforeachtable.

To get the list of empty tables, we can use the below tsql –

EXEC sp_MSforeachtable 'IF NOT EXISTS (SELECT 1 FROM ?) PRINT ''?'' '

And, to get a list of tables having at least one row of data, we can use the below tsql –

EXEC sp_MSforeachtable 'IF EXISTS (SELECT 1 FROM ?) PRINT ''?'' '

Please note that the sp_MSforeachtable stored procedure is undocumented, so be careful about using this stored procedure in production code.