SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Multiple usage of functions to ensure “value” is not null?!

In this post I’m describing a piece of code that is not well written. The author (unknown) had applied three functions for a variable to ensure it never accepts NULLs. The code is given in the next screen shot.

ScreenShot 1. The query with three functions on OriginalAmount

As you can see the author had used three functions on the OriginalAmount column. I disagree for that approach of using twice the ISNULL function. Additionally the potential replacement of a NULL to 0 (instead of 0.0), forces an unnecessary implicit conversion. The replacement for the above query is the following

DECLARE @TotalDeposit DECIMAL(19,6);
SELECT	@TotalDeposit = SUM(t.OriginalAmount)
		FROM	dbo.Transactions (NOLOCK) t
		WHERE	t.WalletID = 40689;
SELECT @TotalDeposit;

Query1. Query updated, displaces ISNULL functions and only SUM is used

In order to prove to the author that there is a sufficient appliance of the ISNULL functions, I’m making the next example demo with the data from the referenced tables. First executing the query for an example WalletID = 40689 to take a look on the data from the screen shot below.

ScreenShot2. The query data

Next I’m creating a temp table and populating it with the data for WalletID=40689.

IF OBJECT_ID('tempdb..#tmpTransactions') IS NOT NULL	
	DROP TABLE #tmpTransactions;
CREATE TABLE #tmpTransactions(
	OriginalAmount DECIMAL(19,6),
INSERT INTO #tmpTransactions ( ID, OriginalAmount, WalletID )
SELECT ID,OriginalAmount,WalletID 
FROM dbo.Transactions WHERE WalletID=40689;

Then I check if there are any zero values for the OriginalAmount column and make the following update just to ensure I have NULLs instead of zeros in the data set. I want to make the example closer to a potential real-case scenario when there would be multiple NULLs and for which the author had been potentially worried.

SELECT * FROM #tmpTransactions
WHERE OriginalAmount = 0.0

UPDATE #tmpTransactions
SET OriginalAmount=NULL
WHERE OriginalAmount = 0.0

Running the same query from above produces the same result.

ScreenShot 3. The code-refactored query with the temp table.

In the Messages there is the expected warning for the elimination of the NULLs in aggregated functions.

Warning: Null value is eliminated by an aggregate or another SET operation.

The warning is not changing the results. The query can be re-written in a more elegant way by using only the SUM function.

DECLARE @TotalDeposit DECIMAL(19,6);
SELECT	@TotalDeposit = SUM(t.OriginalAmount)
		FROM	dbo.Transactions (NOLOCK) t 
SELECT ISNULL(@TotalDeposit,0.0)

Applying two additional functions for such a simple query and with consideration that the Transaction table is counting millions of rows and there can be thousands of transactions for a WalletID, is not a well designed code.

However, if of any reason there is a possibility for obtaining a NULL and to just ensure I really never have that NULL “value” for the @TotalDepost variable I can use the ISNULL function on it in the end and now it won’t touch the performances of the code.

Si vis pacem, para sql

Developer, Administrator and Architect with 10+ years of expertise in data analysis, design, programming, performance tuning, upgrades, migrations, high availability solutions implementation, backup & recovery strategies and database capacity planning expertise.


Leave a comment on the original post [igormicev.com, opens in a new window]

Loading comments...