Tag Archives: #TSQL

Articles on T-SQL. This can be a script or a syntax element

#0417 – SQL Server – Select row count of local temp tables

Leave a reply

I was recently contacted by a fellow team member who was interested in finding out the number of records in a temporary table which was being used as part of a long-running script.

As I had mentioned in one of my previous posts, local temporary tables are only accessible to the connection that created them. Hence, if a script is already running, the only connection that can execute queries against the local temporary table is the connection itself making this requirement a tricky one to work with.

The Solution

The solution for this is to realize that all tables – permanent or local consume storage either in a system/user database or in the tempdb. Hence, we can access this meta-data to fulfill our requirement. Let’s check it out with a demo.

Demo

In any window, we can access the dynamic management view (DMV): [sys].[dm_db_partition_stats]. As we know, this DMV returns page and row-count information for every partition in the current database.

So, let’s open a new query window in the SQL Server Management Studio and run the following query:

--Create a new test database
IF (DB_ID('SQLTwins') IS NULL)
BEGIN
    CREATE DATABASE [SQLTwins];
END
GO

USE [SQLTwins];
GO

--Window 01
BEGIN
    IF OBJECT_ID('tempdb..#SQLTwinsDemo','U') IS NOT NULL
    BEGIN
        DROP TABLE [dbo].[#SQLTwinsDemo];
    END

    CREATE TABLE [dbo].[#SQLTwinsDemo] ([Number] INT         NOT NULL,
                                        [Value]  VARCHAR(50) NOT NULL
                                       );

    INSERT INTO [dbo].[#SQLTwinsDemo] ([Number],
                                       [Value]
                                      )
    VALUES (9, 'Nine' ),
           (8, 'Eight'),
           (7, 'Seven'),
           (6, 'Six'  ),
           (5, 'Five' ),
           (4, 'Four' ),
           (3, 'Three'),
           (2, 'Two'  ),
           (1, 'One'  );
END

Now, in another window, try to run a simple row count query. As expected, it would return an error.

USE [SQLTwins];
GO
SELECT COUNT(*) FROM [dbo].[#SQLTwinsDemo];
GO

Msg 208, Level 16, State 0, Line 3
Invalid object name '#SQLTwinsDemo'.

Now, let’s use the DMV: [sys].[dm_db_partition_stats] in another window to get the row count information.

USE [SQLTwins];
GO
--Now, do this in Window #2
BEGIN
    SELECT [st].[name] AS [TableName],
           [partitionStatistics].[row_count] AS [RowCount]
    FROM [tempdb].[sys].[dm_db_partition_stats] AS [partitionStatistics]
    INNER JOIN [tempdb].[sys].[tables] AS [st] ON [st].[object_id] = [partitionStatistics].[object_id]
    WHERE [st].[name] LIKE '%SQLTwinsDemo%'
      AND ([partitionStatistics].[index_id] = 0  --Table is a heap
           OR
           [partitionStatistics].[index_id] = 1  --Table has a clustered index
          )
END
Fetching RowCount for local temporary tables using SQL Server DMVs

Hope it helps!

Until we meet next time,

Be courteous. Drive responsibly,

#0416 – SQL Server – Msg 8101 – Use column lists when working with IDENTITY columns

1 Reply

I have often written about IDENTITY columns on my blog. Identity columns, most commonly used to implement auto-increment keys, have been around for more than a decade now. Yet, I often see teams run into interesting use cases especially in cases where data is being migrated from one system to another.

Today’s post is based on one such incident that came to my attention.

The team was trying to migrate data from one table to another as part of an exercise to change the database structure for more efficiency. When moving the data from one table to another, they were using the option (SET IDENTITY_INSERT ON) in order to explicitly insert values into the Identity column. However, they were running into an error.

Msg 8101, Level 16, State 1, Line 24
An explicit value for the identity column in table 'dbo.tIdentity' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Here is a simulation of what they were doing:

USE tempdb;
GO
SET NOCOUNT ON;
--Prepare the environment
--   Create a table, and add some test data into it
--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO
--Create a table
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY(1,1),
                            IdentityValue VARCHAR(10)
                           );
GO
--Turn Explicit IDENTITY_INSERT ON and insert duplicate IDENTITY values
SET IDENTITY_INSERT dbo.tIdentity ON;
GO
--NOTICE: No column list has been supplied in the INSERT
INSERT INTO dbo.tIdentity
VALUES (1, 'One'),
       (2, 'Two');
GO

--RESULTS
--Msg 8101, Level 16, State 1, Line 24
--An explicit value for the identity column in table 'dbo.tIdentity' can only be pecified when a column list is used and IDENTITY_INSERT is ON.

The Solution

Let’s re-read the error. It clearly gives an indication of what the issue is – if we need to insert an explicit value into Identity columns, we need to explicitly use column lists in our insert statements, as shown below.

USE tempdb;
GO
SET NOCOUNT ON;
--Prepare the environment
--Create a table, and add some test data into it
--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY(1,1),
                            IdentityValue VARCHAR(10)
                           );
GO

--Turn Explicit IDENTITY_INSERT ON and insert duplicate IDENTITY values
SET IDENTITY_INSERT dbo.tIdentity ON;
GO

--NOTE: Column list has been supplied in the INSERT,
--      so, no errors will be encountered    
INSERT INTO dbo.tIdentity ([IdentityId], [IdentityValue])
VALUES (1, 'One'),
       (2, 'Two');
GO

--Confirm that data has been inserted
SELECT IdentityId,
       IdentityValue
FROM dbo.tIdentity;
GO

--Now that data has been inserted, turn OFF IDENTITY_INSERT
SET IDENTITY_INSERT dbo.tIdentity OFF;
GO

-----------------------------------------------------------------
--RESULTS
----------
--IdentityId  IdentityValue
--1           One
--2           Two
 -----------------------------------------------------------------

Hope you will find this helpful.

Untill we meet next time,

Be courteous. Drive responsibly.

#0415 – SQL Server – Performance Tuning – Use STRING_AGG to generate comma separated strings

Leave a reply

With more and more data being exchanged over APIs, generating comma-separated strings are becoming a much more common requirement.

A few years ago, I wrote about two different ways to generate comma-separated strings. The most common one I find to be in use when generating comma-separated values from a table is the intermediate conversion of XML. This however, is a very costly mechanism and can potentially take minutes for the query to run depending upon the amount of data involved.

SQL Server 2017 brings a new aggregate function that can be used to generate comma-separated values extremely fast. The function is STRING_AGG().

Here’s a sample of it’s usage:

 --WARNING: THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
 --         WARRANTY.
 --         FOR DEMONSTRATION PURPOSES ONLY       
 --Step 01: Generate Temp table to store source data
 DECLARE @NamesTable TABLE ([Id]   INT, 
                            [Name] NVARCHAR(50)
                           );
 --Step 02: Generate test data
 INSERT INTO @NamesTable
 VALUES (1, 'A'),
        (2, 'D'),
        (2, 'C'),
        (3, 'E'),
        (3, 'H'),
        (3, 'G');
 --Step 03: Using STRING_AGG to generate comma-separated strings
 SELECT STRING_AGG(tbl.Name, ',') AS [CommaSeparatedString]
 FROM @NamesTable AS tbl;
 GO
 /RESULTS**
 CommaSeparatedString
 A,D,C,E,H,G
 */

Advantages of STRING_AGG:

  • Can be used just like any other aggregate function in a query
  • Can work with any user supplied separator – doesn’t necessarily have to be a comma
  • No manual step required – Separators are not added at the end of the concatenated string
  • STRING_AGG() is significantly faster than using XML based methods
  • Can be used with any compatibility level as long as the version is SQL Server 2017 (or higher) and Azure SQL database

Here’s an example of how STRING_AGG can be used with any separator:

 --WARNING: THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
 --         WARRANTY.
 --         FOR DEMONSTRATION PURPOSES ONLY       
 --Step 01: Generate Temp table to store source data
 DECLARE @NamesTable TABLE ([Id]   INT, 
                            [Name] NVARCHAR(50)
                           );
 --Step 02: Generate test data
 INSERT INTO @NamesTable
 VALUES (1, 'A'),
        (2, 'D'),
        (2, 'C'),
        (3, 'E'),
        (3, 'H'),
        (3, 'G');
 --Step 03: Using STRING_AGG to generate comma-separated strings
 SELECT STRING_AGG(tbl.Name, '-*-') AS [CustomSeparatorString]
 FROM @NamesTable AS tbl;
 GO
 /RESULTS**
 CustomSeparatorString
 A--D--C--E--H--G /

A minor challenge

As with every new feature, there may be a small usability challenge with STRING_AGG. One cannot use keywords like DISTINCT to ensure that only distinct values are used for generating the comma-separated string. There is however a Azure feedback item open where you can exercise your vote if you feel this feature is useful.

Further Reading

  • Different ways to generate a comma-separated string from a table [Blog Link]
  • STRING_AGG() Aggregate Function [MSDN BOL]

Until we meet next time,

Be courteous. Drive responsibly.

#0410 – SQL Server – Dividing a TimeSpan by an Integer to find average time per execution

1 Reply

I recently encountered an interesting question on the forums the other day. The question was how to determine the average time taken by a single execution of the report provided we know how many times the report ran and the total time taken for all those executions.

The challenge is that the total time taken for all the report executions is a timespan value (datatype TIME in SQL Server). A TIME value cannot be divided by an INTEGER. If we try to do that, we run into an error – an operand clash.

USE [tempdb];
GO
DECLARE @timeSpan TIME = '03:18:20';
DECLARE @numberOfExecutions INT = 99;

SELECT @timeSpan/@numberOfExecutions;
GO

Msg 206, Level 16, State 2, Line 6
Operand type clash: time is incompatible with int

The solution is to realize that a timespan/TIME value is ultimately the number of seconds passed from a given instant. Once the timespan is converted to the appropriate unit (number of seconds), dividing by the number of executions should be quite simple.

Here’s the working example:

USE [tempdb];
GO
DECLARE @timeSpan TIME = '03:18:20';
DECLARE @numberOfExecutions INT = 99;

SELECT @timeSpan AS TotalActiveTime,
       DATEDIFF(SECOND,'1900-01-01 00:00:00.000',CAST(@timeSpan AS DATETIME)) AS TotalExecutionTimeInSeconds,
       DATEDIFF(SECOND,'1900-01-01 00:00:00.000',CAST(@timeSpan AS DATETIME))/(@numberOfExecutions * 1.0) AS TimePerExecution;
GO

/* RESULTS
TotalActiveTime  TotalExecutionTimeInSeconds TimePerExecution   
---------------- --------------------------- -------------------
03:18:20.0000000 11900                       120.20202020202020
*/

I trust this simple thought will help in resolving a business problem someday.

Until we meet next time,

Be courteous. Drive responsibly.

Collapsed Regions using BEGIN_END

#0409 – SQL Server – Code Blocks – Equivalent of #region…#endregion

1 Reply

I was recently participating in a forum and came across an interesting question. What attracted my attention was that the person was trying to keep their T-SQL code clean and readable (which in itself is a rare sight).

The person was trying to group their T-SQL code into regions. In the world of application development technologies (e.g. C#) we would typically use the #region….#endregion combination. However, it does not work with T-SQL because the hash (#) is used to define temporary tables.

In T-SQL, the basic control-of-flow statements  that allow you to group the code are the BEGIN…END keywords. The BEGIN…END keywords can be used to logically group code so that they can be collapsed or expanded as required.

Collapsed Regions using BEGIN_END

Collapsed Regions using BEGIN_END

Expanded Regions using BEGIN_END

Expanded Regions using BEGIN_END

Summarizing,

The BEGIN…END keywords are therefore the functional equivalents of the #region…#endregion statements.

Until we meet next time,

Be courteous. Drive responsibly.