SQL Fun #01

Write shortest T-SQL code to print 0.00 without using any number and built-in function

What is Tally Table in SQL Server?

In this post, I am going to explain about Tally table and it’s uses in T-SQL programming.

A Tally table is like any other table but having a single column of sequential numbers, values starting from 1 (or 0) to some N (int) number.

The largest number in the Tally table should be based on what suits your system, application, or database most. So don't use very high number. Also column of Tally should be indexed for better performance.

I use Tally table to generate 25 years of dates, so my Tally tables will have values from 1 to 10,000 (25 years * 365.25 days = 9131.25)

How to Create a Tally Table

There are several methods to create a Tally table. I will use one of the simplest and obvious option - WHILE loop because it's easier to explain and simpler than others.

SET NOCOUNT ON;
IF OBJECT_ID('dbo.Tally') IS NOT NULL   DROP TABLE dbo.Tally
GO

-- Define how many rows you want in Tally table.
-- I am inserting only 10000 rows
SET ROWCOUNT 10000

SELECT IDENTITY(INT, 1, 1) ID
INTO dbo.Tally
FROM master.sys.all_columns c
CROSS JOIN master.sys.all_columns c1
-- you may use one more cross join if tally table required hundreds of million rows

SET ROWCOUNT 0

-- ADD (unique) clustered index
CREATE UNIQUE CLUSTERED INDEX PKC_Tally ON dbo.Tally (ID)
GO

 

How to use Tally table in T-SQL and what are the advantages?

There are several advantages of a Tally table. Here are some of the examples:

Ø  To generate Date Range for given Start Date and End Date

Ø  To Manipulate strings, like:

·         Find the positions of a character in a string.

·         Find the total occurances of a character in a string

·         Split comma seperated values

 

I will explain these advantages with examples.

Generate Date Range using Tally Table

Generally you would require a WHILE loop to create Date Range values. However, it is very easy to generate date range using tally. Its much faster than WHILE loop:
 

-- Generate Date range

DECLARE @BeginDate DATE = '2001-01-01', @EndDate DATE = '2025-12-31'
SELECT DATEADD(DD, ID-1, @BeginDate) [Date]
,DAY(DATEADD(DD, ID-1, @BeginDate)) [Day]
,MONTH(DATEADD(DD, ID-1, @BeginDate)) [Month]
,YEAR(DATEADD(DD, ID-1, @BeginDate)) [Year]
FROM dbo.Tally
WHERE ID <= DATEDIFF(DD, @BeginDate, @EndDate) + 1

Here is the output:

 

 

 

Find a Character Positions in a String using Tally Table

You can find a character position using string functions and WHILE loop. But Tally table makes it much simpler than any other method, yet faster. Here is an example:

-- Find the position numbers of comma in a given string.

DECLARE @Str VARCHAR(1000), @FindChar CHAR(1) = ','
SET @Str = 'Hari,Jon,Ravi,Vijay,Peter,Max' --Input String
 
SELECT ID AS CharPosition
FROM dbo.Tally 
WHERE ID <= LEN(@Str) 
AND SUBSTRING(@Str, ID, 1) = @FindChar
ORDER BY ID

 

Here is the output:

 

 

 

Find count of all the occurrences of a Character in a String using Tally Table

You can find the count of all the occurrences of a character in a string using Tally table by slightly modifying above query:
 

-- Find the occurrences of a character in a given string.

DECLARE @Str VARCHAR(1000), @FindChar CHAR(1) = 'a'
SET @Str = 'Hari,Jon,Ravi,Vijay,Peter,Max' --Input String

SELECT COUNT(1) AS CharCount

FROM (
SELECT ID AS CharPosition
FROM dbo.Tally 
WHERE ID <= LEN(@Str) 
AND SUBSTRING(@Str, ID, 1) = @FindChar
) AS Temp
 

Here is the output:

 

 

Split Comma Seperated values using Tally table

I had posted a separate article about Function to Split Multi-valued String couple of years back.

The logic implemented in that function could be much simpler by using Tally table. You can split the values without WHILE loop. It would be interesting to compare the performance of these two mechanism.

--Split Comma Seperated values

DECLARE @Str VARCHAR(1000), @Delimiter CHAR(1) = ','
SET @Str = 'Hari,Jon,Ravi,Vijay,Peter,Max'

-- Append delimiter at the beginning and end

SET @Str = @Delimiter + @Str + @Delimiter

SELECT SUBSTRING(@Str, ID+1, CHARINDEX(@Delimiter, @Str, ID+1) - ID-1) SplitedString

FROM dbo.Tally 
WHERE ID < LEN(@Str)
AND SUBSTRING(@Str, ID, 1) = @Delimiter
 

Here is the output:

 

How to Find Database Growth using T-SQL

In this post, I am going to share a stored procedure to calculate the percentage of file growth of a database. This growth rate can be handy to plan ahead for future storage needs.


IF OBJECT_ID(N'dbo.DatabaseGrowth', 'P') IS NOT NULL
BEGIN
    PRINT 'Dropping procedure dbo.DatabaseGrowth'
    DROP PROCEDURE dbo.DatabaseGrowth
END
GO

PRINT 'Creating procedure dbo.DatabaseGrowth'
GO

CREATE PROC dbo.DatabaseGrowth @pDBName sysname = NULL
AS
BEGIN
/*********************************************************************************
Description: Procedure to calulate the file growth %ages for a given database and
show the growth rate so that we can plan ahead for future storage needs.

How to use:
--------------
Example 1: To see the file growth of the current database:
EXEC dbo.DatabaseGrowth

Example 2: To see the file growth for [Test] database:
EXEC dbo.DatabaseGrowth 'Test'

--------------------------------------------------------------------------------
Change History
--------------------------------------------------------------------------------
Date Name Comments
---------- ----------- ---------------------------------------------------------
01/15/2012 Hari Sharma Created
********************************************************************************/
SET NOCOUNT ON;
DECLARE @DatabaseName SYSNAME

-- Use current database, if a database name is not specified in input parameter
SET @DatabaseName = ISNULL(@pDBName, DB_NAME())
SELECT  backup_start_date AS StartTime
        ,@DatabaseName AS DatabaseName
        ,filegroup_name AS FilegroupName
        ,logical_name AS LogicalFilename
        ,physical_name AS PhysicalFilename
        ,CONVERT(NUMERIC(9,2), file_size/1048576) AS FileSizeInMB
        ,Growth AS PercentageGrowth
FROM (
     SELECT b.backup_start_date
           ,a.backup_set_id
           ,a.file_size
           ,a.logical_name
           ,a.[filegroup_name]
           ,a.physical_name
           ,(SELECT CONVERT(numeric(5,2),((a.file_size * 100.00)/i1.file_size)-100)
             FROM msdb.dbo.backupfile i1
             WHERE i1.backup_set_id =
              (
              SELECT MAX(i2.backup_set_id)
              FROM msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3
              ON i2.backup_set_id = i3.backup_set_id
              WHERE i2.backup_set_id < a.backup_set_id
              AND i2.file_type='D'
              AND i3.database_name = @DatabaseName
              AND i2.logical_name = a.logical_name
              AND i2.logical_name = i1.logical_name
              AND i3.type = 'D'
              )
              AND i1.file_type = 'D'
) AS Growth
FROM msdb.dbo.backupfile a
JOIN msdb.dbo.backupset b
ON a.backup_set_id = b.backup_set_id
WHERE b.database_name = @DatabaseName
AND a.file_type = 'D'
AND b.type = 'D'
) AS Derived
WHERE   ISNULL(Growth, 0.0) <> 0.0
ORDER BY logical_name, StartTime
END

GO