The T-SQL language constantly undergoes modifications. Every release bring in something new, something to help develop an easier solution to a common business problem. With Microsoft SQL Server 2012, enhancements have been incorporated to the Windows functions to allow an easier solution to what is commonly referred to as the “Running Total Problem”.

The whole concept of running totals is that as the transactions continue to be added, the system maintains the total of a given sequence. This allows the system to fetch this summary without having to maintain any record of the individual transactions itself.

Here’s an example based on the transactions for one of the SalesOrders in the AdventureWorks2012 sample database.

Essentially, the running total at each row is the sum of the running total value from the previous row and the expression to be added from the current row (in this case, Unit Price * Order Quantity). That’s where the problems start in SQL Server.

The running total is calculated in a sequence and unfortunately, SQL Server does not guarantee the order of records in a result set which makes it difficult to compute the running total in a SET based operation. The solution to this problem has therefore been a CURSOR operation where each record is independently processed in a loop.

However, the T-SQL enhancements made in SQL Server 2012 have a possible solution to this problem. The the aggregate function SUM() has been enhanced to operate as a windowing function which now supports the ORDER BY clause in the OVER clause. This gives the ability for SUM to be performed in the intended sequence of the transactions. Here’s a query to demonstrate this:

USE AdventureWorks2012;
GO
SELECT SalesOrderID,
       SalesOrderDetailID,
       UnitPrice,
       OrderQty,
       LineTotal,
       SUM(UnitPrice * OrderQty) 
            OVER (PARTITION BY SalesOrderId 
                  ORDER BY SalesOrderDetailID
                 ) 
                 AS RunningTotal
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43659;
GO

NOTE: Attempting to run this query against a SQL Server 2008R2 or lower environment will result in an error related to missing support of the ORDER BY clause. They support the use of OVER and PARTITION BY with the SUM() function, but do not support ORDER BY.

The result set available from the query is shown below.

As can be seen from the result set, Microsoft SQL Server auto-calculated the running total for the given Sales Order in a given sequence. These results also match with the table provided above.

Finally, the purpose of a running total is that a system/user should not have to maintain a record of all the transactions. Hence, the maximum value of the running total should match the Sub-total stored on the SalesOrder header record.

USE AdventureWorks2012;
GO
SELECT SalesOrderID,
       SubTotal,
       TaxAmt,
       Freight,
       TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 43659;
GO

Summary

The ORDER BY clause can be used when using SUM() over a window defined by the OVER clause to generate a running total of values in a given sequence.

It is really amazing that the solution to  a problem that has challenged many brains over the years can be this simple. Kudos to the #SQLServer product development team at #Microsoft. Thanks, guys!

Further Reading

• Windowing functions – Underappreciated features of Microsoft SQL Server [Link]
  
• Aggregate Function – SUM [Link]
  
• Over Clause [Link]
  
• Running Totals, Wikipedia [Link]

Until we meet next time,

Be courteous. Drive responsibly.

Today’s post is a simple one, but inspired from a question I encountered in one of the forums. Comparing dates and date-time values are fairly common requirements and we already know a solution for realization of this requirement – the DATEDIFF function.

However, the query that was posted on the forums was about comparison of time values. Let us assume the requirement to be such that if the current time is less than 08:00AM, the default time being returned should be 08:00AM. Else, it can be whatever the current time is. The person who posted the query apparently wanted to know how to achieve this requirement.

The DATEDIFF function can help us here as well. DATEDIFF can work with date, time and date-time values.Here’s an example:

USE tempdb;
GO
DECLARE @referenceValue TIME = '08:00';
DECLARE @comparisonValue TIME = '07:30';

--Let us check the output for a value that is less
--than the reference value
SELECT @comparisonValue AS ComparisonValue,
       @referenceValue AS ReferenceValue,
       CASE WHEN DATEDIFF(SECOND,@referenceValue,@comparisonValue) < 0
            THEN '08:00'
            ELSE @comparisonValue
            END AS ReturnTime;

--Now, let us check the output for a value that is greater
--than the reference value
SELECT @comparisonValue = '08:30';

SELECT @comparisonValue AS ComparisonValue,
       @referenceValue AS ReferenceValue,
       CASE WHEN DATEDIFF(SECOND,@referenceValue,@comparisonValue) < 0
            THEN '08:00'
            ELSE @comparisonValue
            END AS ReturnTime;
GO

Here’s the output:

Further Reading

• Msg 402 – The data types datetime and time are incompatible in the add/subtract operator [Link]
• DATEDIFF date comparison function [Books On Line Link]

Until we meet next time,
Be courteous. Drive responsibly.

Earlier this week, I wrote a post on ANSI_NULL_DFLT_ON (Impact of ANSI_NULL_DFLT_ON on Temporary Tables). In this post, I used code similar to the following to ascertain whether the ANSI_NULL_DFLT_ON setting is set to ON as a user option (via the connection properties) or not.

IF (@@OPTIONS & 1024) > 0
    PRINT 'ANSI_NULL_DFLT_ON is SET';
ELSE
    PRINT 'ANSI_NULL_DFLT_ON is OFF';
GO

When I was originally introduced to the concept of the @@OPTIONS configuration function, the one question that came into my mind was:

How do I know what configuration value stands for which SET option?

I’m sure that many of you who read the posts from last week would also have had the same question.

The unique values for all user configuration options are documented in the following Books On Line/TechNet page: Configure the user options Server Configuration Option.

It is interesting to note that ultimately they correspond to the bit positions corresponding to a particular option (which is why we can do a bit-wise AND with the final value returned by @@OPTIONS to verify whether a particular option is enabled or not) – another example of how optimized the operation of SQL Server is by default.

Further reading:

• The @@OPTIONS configuration function [Link]
• Configure the user options Server Configuration option [Link]

Until we meet next time,

Be courteous. Drive responsibly.

Some lessons are learnt the hard way. Today, it is a well-accepted best practice to always define the NULL-ability on the columns of a temporary table definition. This is because the ANSI_NULL_DFLT_ON connection option influences the default value being inserted into a column when the NULL-ability of the column is not specified. Here’s the extract from Books-On-Line:

When SET ANSI_NULL_DFLT_ON is ON, new columns created by using the ALTER TABLE and CREATE TABLE statements allow null values if the nullability status of the column is not explicitly specified. SET ANSI_NULL_DFLT_ON does not affect columns created with an explicit NULL or NOT NULL.

Most enterprise applications use temporary objects, especially table variables and temporary tables. Some of these applications have been around since a decade or more. They have a mix of “legacy” code (written in the days of SQL 2000) and “modern” code (written in SQL 2008 and beyond).

I was recently called upon to assist with an error that one of our customers was encountering in their production environment. They were attempting to run a weekly routine and encountered an error similar to the following:

Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column ‘City’, table ‘tempdb.dbo.#NullsCheck_________________________________________________________________________________________________________000000000008’; column does not allow nulls. INSERT fails.
The statement has been terminated.

As is the case with most production issues, everything was working fine in our development and quality assurance environments.

While we were going through the scripts to see what could have gone wrong, we learnt that in another troubleshooting attempt by the technical support team, the same routine executed successfully when executed from another SQL Server client. The focus immediately shifted to the connection options.

Once we started reviewing the connection options, the problem was detected almost immediately. In the SSMS on the SQL server client environment where errors were encountered, the ANSI_NULL_DLFT_ON was set to OFF/unchecked in Tools –> Options –> Query Execution –> SQL Server –> ANSI:

ANSI_NULL_DFLT_ON is ON

On most SQL Server clients, the ANSI_NULL_DFLT_ON is set to ON by default. In such environments, when an attempt is made to insert NULL values into a column where the NULL-ability is undefined, no error will be reported. The following example demonstrates this.

USE AdventureWorks2012;
GO
IF (@@OPTIONS & 1024) > 0
    PRINT 'ANSI_NULL_DFLT_ON is SET';
ELSE
    PRINT 'ANSI_NULL_DFLT_ON is OFF';
GO

--Safety Check
IF OBJECT_ID('tempdb..#NullsCheck','U') IS NOT NULL
    DROP TABLE #NullsCheck;
GO

--Table definition 
--Notice that the NULL-ability on the columns is undefined
CREATE TABLE #NullsCheck ([Id] INT IDENTITY(1,1),
                          [City] VARCHAR(50),
                          [State] VARCHAR(50)
                         );
GO

--Attempt to insert test data
INSERT INTO #NullsCheck ([City],[State])
VALUES (NULL, NULL),
       ('Portsmouth','New Hampshire'),
       ('Boston', 'Massachusetts');
GO

--Select from the table
SELECT * FROM #NullsCheck;
GO

/*****************************
RESULTS
---------------------------
Id  City        State
---------------------------
1   NULL        NULL
2   Portsmouth  New Hampshire
3   Boston      Massachusetts
*****************************/

Before we close the query editor window in SSMS, let us take a quick look at the NULL-ability on the columns of the temporary table.

--Check the column properties
SELECT tisc.TABLE_NAME,
       tisc.COLUMN_NAME,
       tisc.IS_NULLABLE
FROM tempdb.INFORMATION_SCHEMA.COLUMNS AS tisc
WHERE tisc.TABLE_NAME LIKE '#NullsCheck%';
GO

ANSI_NULL_DFLT_ON is OFF

When ANSI_NULL_DFLT_ON is OFF (as it was in this case), the same script will return the error that we were seeing.

This is because, when the temporary table is created, the table definition has been created with the columns as NOT NULL. Here’s the same check as above, but after turning the ANSI_NULL_DFLT_ON to OFF:

Lessons Learnt

1. Always define the NULL-ability when defining tables – temporary or otherwise
   
2. Whenever a new coding standard is adopted for an existing system, it is always a good idea to have an inventory of objects that violated the coding standard when the standards were adopted
   
3. As service releases of the product are released, it is a good idea to revisit the inventory and make the entire code conform to the standard over time

Further Reading

• Fun with Temporary Tables – Named Constraints, Msg 2714, 1750 [Link]
  
• Fun with Temporary Tables – Foreign Keys [Link]
  
• ANSI_NULL_DFLT_ON [Books On Line Link]

Until we meet next time,

Be courteous. Drive responsibly.

Last week, I re-learnt something which, according to me, is a little counter-intuitive. I was studying read-only databases out of academic interest and noticed something interesting:

When a database is marked as Read-only, the underlying file/file-groups are not marked Read-only.

Here’s a simple test wherein we:

1. Create a test database with multiple file-groups (the test works equally well with a single file-group)
2. Set the database to READ_ONLY
3. Check the file-group properties

USE master;
GO
--Create the test database
CREATE DATABASE ReadOnlyDB
ON
PRIMARY  (Name=ReadOnlyDB_Primary,
          FileName='C:DatabasesSQL2012ReadOnlyDB_Primary.mdf'
         ),
FILEGROUP SecondaryFG
        (Name=ReadOnlyDB_Secondary,
         FileName='C:DatabasesSQL2012ReadOnlyDB_Secondary.ndf'
        )
LOG ON (Name=ReadOnlyDB_Log,
        FileName='C:DatabasesSQL2012ReadOnlyDB_Log.ldf'
       );
GO

USE master;
GO
--Set the database to READ_ONLY
ALTER DATABASE ReadOnlyDB SET READ_ONLY;
GO

USE ReadOnlyDB;
GO
--Check the File & File-group properties
SELECT sfg.is_read_only,sfg.*
FROM sys.filegroups AS sfg;
GO
SELECT sdf.is_read_only,sdf.*
FROM sys.database_files AS sdf;
GO

As can be seen from the output, none of the file-groups were marked as read-only even though the database is read-only. To confirm that the database is indeed read-only, let us attempt to create a table on the database.

USE ReadOnlyDB;
GO
CREATE TABLE TestTbl (Id INT IDENTITY(1,1),
                      Name VARCHAR(50)
                     )
ON SecondaryFG;
GO

Msg 3906, Level 16, State 1, Line 1

Failed to update database “ReadOnlyDB” because the database is read-only.

A possible explanation

The fact that the file-groups are not marked read-only even though the database is read-only is counter-intuitive.

The only possible explanation that I have is that primary file-groups cannot be marked read-only which is why SQL Server does not automatically attempt to mark the file-groups and log files as read-only.

Further Reading

• SQL Server Myth: Log files are removed when a database is made READ_ONLY [Link]

Until we meet next time,

Be courteous. Drive responsibly.