Category Archives: Community

Articles on community events.

#0424 – SQL Server – Null value is eliminated by an aggregate or other SET operation. – Why? How to fix?


I recently ran into a forum post where the poster wanted to know why they were getting the following warning during query execution:

"Null value is eliminated by an aggregate or other SET operation."

Having been asked this question a few times by a few of my office colleagues as well, I thought to write up a quick post on the reason behind this warning.

A quick test

The test below is simple – I am creating a sample test table which allows NULL values to be inserted. I am then trying to perform a simple aggregate function (SUM) over the NULL-able column. Upon checking the “Messages” tab, we see that no warning is returned.

USE [tempdb];
GO
SET NOCOUNT ON;

--Safety Check
IF OBJECT_ID('dbo.NULLAggregation','U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[NULLAggregation];
END
GO

--Create the test table
CREATE TABLE [dbo].[NULLAggregation] 
    ([Id]          INT           NOT NULL IDENTITY (1,1),
     [Value]       INT               NULL,
     [ValueString] VARCHAR(50)   NOT NULL,
     CONSTRAINT [pk_NULLAggregation] PRIMARY KEY ([Id])
    );
GO

--Insert the test data
INSERT INTO [dbo].[NULLAggregation] ([Value], [ValueString])
VALUES ( 1, 'One'),
       (10, 'Ten'),
       (22, 'Twenty-Two');
GO

--Perform the aggregation
--NOTE: No NULL values are in the table at this point
SELECT SUM([na].[Value]) AS [SumOfValues]
FROM [dbo].[NULLAggregation] AS [na];
GO

/****************************************
RESULT
-----------
SumOfValues
-----------
33
****************************************/

Now, I add a single record with a NULL value in the [Value] column and repeat the aggregation. While the result is the same, we have a warning in the “Messages” tab.

INSERT INTO [dbo].[NULLAggregation] ([Value], [ValueString])
VALUES ( NULL, 'One');
GO

SELECT SUM([na].[Value]) AS [SumOfValues]
FROM [dbo].[NULLAggregation] AS [na];
GO

/****************************************
RESULT
-----------
SumOfValues
-----------
33
Warning: Null value is eliminated by an aggregate or other SET operation.
****************************************/
A screenshot showing the warning encountered in SSMS when an aggregation operation is performed on a NULL value.
Warning encountered when aggregating on NULL values

The reason for the warning

To begin – this is just a warning and not an error. If your script/job is failing it is probably failing due to some other data condition OR as an indirect result of operating on NULL values.

The warning simply suggests that an aggregation operation is being done on a NULL value. If no NULL values are present in the dataset being evaluated, then the warning is not encountered.

Solutions

The important thing is to consider what is important for the business/domain.

  • If processing on NULL values are okay for the business/domain, then one of the following two (2) workaround can be applied:
    • The warning can either be ignored OR
    • Use the “SET ANSI_WARNINGS OFF” option for your query/procedure
  • If processing on NULL values is not acceptable for your business/domain, then
    • Either use a simple WHERE clause to remove the records with NULL values from the aggregation
    • Use input validations in the application code and NOT NULL checks in the database to stop the NULL values from being entered by the users

NOTE: If you do decide to use the “SET ANSI_WARNINGS OFF” option, please do so with caution. It can have unintended consequences with string operations as well (https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql?view=sql-server-ver15)

I trust you found this post useful.

Until we meet next time,

Be courteous. Drive responsibly.

#0373 – SQL Server – Interview Questions – Parameters required for server-side paging mechanisms


For a mid-level SQL developer profile, this is one of my favourite interview questions if their current projects involve some sort of paging mechanism while displaying results on a grid.

What is the minimum number of parameters that your stored procedure needs to implement server-side paging?

Despite the multiple blog posts around this topic, this question surprisingly still stumps most of the developers.

The answer is quite simple – at a basic level, you absolutely need two (2) input parameters on your stored procedure to implement server-side paging:

  1. The page number
  2. The page size, i.e. the number of records to be displayed on each page

Of course, the implementation may vary – one can have multiple additional parameters like the sort column and the sort order, but at the very minimum one needs the two (2) parameters listed above.

For reference, here is a sample implementation (for SQL Server 2008 R2 and SQL Server 2012):

USE AdventureWorks2012 ;
GO
SET NOCOUNT ON ;

DECLARE @pageSize INT = 100 ;
DECLARE @pageNumber INT = 3 ;

--SQL 2008 R2 Method
--Source Data, fetched with paging
;
WITH    SourceDataCTE ( CarrierTrackingNumber, SalesOrderId, TotalShipmentValue, RowNumber )
          AS ( SELECT   sod.CarrierTrackingNumber,
                        sod.SalesOrderId,
                        SUM(sod.LineTotal) AS TotalShipmentValue,
                        ROW_NUMBER() OVER ( ORDER BY sod.CarrierTrackingNumber, sod.SalesOrderId ) AS RowNumber
               FROM     Sales.SalesOrderHeader AS soh
                        INNER JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID
               WHERE    soh.ShipDate IS NOT NULL
                        AND sod.CarrierTrackingNumber IS NOT NULL
               GROUP BY sod.CarrierTrackingNumber,
                        sod.SalesOrderId
             )
    SELECT TOP ( @pageSize )
            sdCTE.CarrierTrackingNumber,
            sdCTE.SalesOrderId,
            sdCTE.TotalShipmentValue,
                     sdCTE.RowNumber
    FROM    SourceDataCTE AS sdCTE
    WHERE   sdCTE.RowNumber BETWEEN ( ( @pageNumber * @pageSize ) + 1 )
                            AND     ( ( @pageNumber + 1 ) * @pageSize ) ;

--SQL 2012 Method
SELECT   sod.CarrierTrackingNumber,
        sod.SalesOrderId,
        --SUM(sod.LineTotal) OVER ( ORDER BY sod.CarrierTrackingNumber, sod.SalesOrderId ) AS TotalShipmentValue,
        SUM(sod.LineTotal) AS TotalShipmentValue,
        ROW_NUMBER() OVER ( ORDER BY sod.CarrierTrackingNumber, sod.SalesOrderId ) AS RowNumber
FROM     Sales.SalesOrderHeader AS soh
        INNER JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID
WHERE    soh.ShipDate IS NOT NULL
        AND sod.CarrierTrackingNumber IS NOT NULL
GROUP BY sod.CarrierTrackingNumber,
        sod.SalesOrderId
ORDER BY sod.CarrierTrackingNumber
OFFSET (@pageNumber * @pageSize) ROWS
FETCH NEXT (@pageSize) ROWS ONLY
GO

Here’s the result of the code snippet provided above:

Shows the output of the various server-side paging mechanisms available for Microsoft SQL Server

SQL Server Paging Mechanisms

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

#0369 – Visual Studio Solutions – SSIS and Coded UI projects – encounter “System.ArgumentNullException” and tests do not build successfully


Recently, we undertook a project to write a couple of automation tools for the utilities that we use to move data around.

The automation solution involved a couple of SSIS packages that created a few data views, generated some key columns and cross-referenced records with predefined static data to facilitate data comparison. In addition, we also wrote a couple of Coded UI tests which would read configuration parameters from a simple Excel file and run through the utilities so that they can run independently without any need of human interaction.

The issue

Unfortunately, it seems that at least in Visual Studio 2012, one cannot have both – an SSIS project and a Coded UI project. This is because the Coded UI tests fail to build with a “System.ArgumentNullException” error.

Here are the simple steps to reproduce the issue:

  1. Launch VS2012
  2. In an new solution, add a new Integration Services project. Leave defaults and save the solution
  3. In this solution, add a new Coded UI Test project. Leave defaults and save the solution. The solution now has the 2 projects as required
  4. Go to Test -> Windows -> Test Explorer
  5. In the solution explorer, right click on the solution and choose “Build Solution”
  6. Build fails unexpectedly with the following error:
System.ArgumentNullException: Value cannot be null.
Parameter name: source
at Microsoft.VisualStudio.Shell.ValidateArg.NotNull[T](T arg, String parameterName)
at Microsoft.VisualStudio.Shell.ValidateArg.NotNullOrEmpty[T](IEnumerable`1 arg, String parameterName)
at Microsoft.VisualStudio.Shell.Interop.VSProjectExtensions.CompareNormalizedPath(String source, String target)
at Microsoft.VisualStudio.Shell.Interop.VSProjectExtensions.GetKeyOutputForIVsOutputGroup(IVsOutputGroup outputGroup)
at Microsoft.VisualStudio.Shell.Interop.VSProjectExtensions.GetProjectOutputPath(IVsProjectCfg2 projectConfig, String groupName)
at Microsoft.VisualStudio.Shell.Interop.VSProjectExtensions.GetKeyOutputForGroup(IVsSolutionBuildManager buildManager, IVsProject project, String groupName)
at Microsoft.VisualStudio.TestWindow.VsAdapters.VsTestContainer.get_Source()
at Microsoft.VisualStudio.TestWindow.VsAdapters.VsTestContainer.CreateTimeStamp()
at Microsoft.VisualStudio.TestWindow.VsAdapters.VsTestContainer..ctor(ITestContainerDiscoverer discoverer, ILogger log, IVsProject project, IServiceProvider serviceProvider, IVsAppContainerUtilities appContainerUtilities)
at Microsoft.VisualStudio.TestWindow.VsAdapters.VsProjectOutputContainerDiscoverer.<get_TestContainers>b__0(IVsProject p)
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Microsoft.VisualStudio.TestWindow.Controller.TestContainerDiscovererExtension.GetSortedContainers(ITestContainerDiscoverer discoverer)
at Microsoft.VisualStudio.TestWindow.Controller.TestContainerProvider.GetContainersFromDiscoverer(ITestContainerDiscoverer discoverer)

The Workaround

The workaround to build and run the Coded UI tests is to simply unload (removal of the project is not necessary) the SSIS project(s) for the time being. Once the Coded UI tests are done, the SSIS project(s) can be loaded back into the solution before checking it back into source control.

MS Connect Case

While the workaround is simple enough, it can be slightly inconvenient when there are multiple team members who consume the solution. I therefore wrote up a Connect case for this issue (ID# 2194579). Here’s the link: https://connect.microsoft.com/VisualStudio/feedback/details/2194579/solution-with-both-coded-ui-and-integration-services-projects-encounters-system-argumentnullexception-and-tests-do-not-build-successfully

If you encounter the same issue, or if you believe it should be fixed, please “up” vote on the MS connect item linked above.

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

#0339 – SQL Server – I’ve moved! How to stay in touch with me?


My blog has always been the documentation of my evolutionary journey through the world of databases, especially, Microsoft SQL Server. Today, on the birthday of our daughters, I am pleased to announce the start of my blog, SQLTwins at http://nakulvachhrajani.com. I dedicate my journey with Microsoft SQL Server to our daughters, Devika & Devina.

The content on my pages in BeyondRelational.com [Link] will be moved (gradually) over to the new blog.

Today’s post is a brief summary of the various ways in which you can stay in touch with me.

My Blog

I will start posting to my blog (http://nakulvachhrajani.com)  effective today.

You can leave a comment (every one of which will be read by me). I would like to hear from you, so please leave your feedback – whether you liked the post, did not like it (if not, why), and what else would you like to know. I eagerly look forward to your comments.

You can follow my blog or even subscribe via E-mail!

My Linked-In Profile:

You can take a look at my Linked In Profile here:

View Nakul Vachhrajani's profile on LinkedIn

Social Media Presence

You can follow me on the following:

Twitter: Tweet to @sqltwins

Google+: +Nakul

Facebook: The SQLTwins Page on FB

Do note that it’s generally a busy day at the office for me, so I don’t post updates on my stream often.

That’s pretty much it. There aren’t too many channels, which helps me to read each reply, comment or query that you might send out to me.

Until we meet next time,

Be courteous. Drive responsibly.