Performance Comparison Between ISNULL and COALESCE in the paging mode of SQL Server, isnullcoalesce

Performance Comparison Between ISNULL and COALESCE in the paging mode of SQL Server, isnullcoalesce

Preface

In the previous section, we explained the data type and several notes in the string. In this section, we will continue to talk about the number of string lines, other content and interspersed content, short content, and in-depth explanation. (For details, see "Data Types in SQL Server".)

Paging Mode

In SQL 2005 or SQL 2008, we use ROW_NUMBER Window Function for paging. For window function, we will explain in detail in SQL advanced. As follows:

USE TSQL2012GODECLARE @StartRow INTDECLARE @EndRow INTSET @StartRow = 31SET @EndRow = 40SELECT [address], [city], [region]FROM (SELECT [SC].[address], [SC].[city], [SC].[region],ROW_NUMBER() OVER(ORDER BY [SC].[address], [SC].[city],[SC].[custid]) AS RowNumberFROM Sales.Customers SC) SaleCustomerWHERE RowNumber > @StartRow AND RowNumber < @EndRowORDER BY [address], [city], [region];

The above Code does not need to be explained, and the view can also be used for paging.

WITH SaleCustomer AS(SELECT [SC].[address], [SC].[city], [SC].[region],ROW_NUMBER() OVER(ORDER BY [SC].[address], [SC].[city],[SC].[custid]) AS RowNumberFROM Sales.Customers SC)SELECT [address], [city], [region]FROM SaleCustomerWHERE RowNumber > @StartRow AND RowNumber < @EndRowORDER BY [address], [city], [region]GO

Let's take a look at the performance differences between the two SQL query statements and views? .

From this, we can see that the performance overhead of the two is no different. In most cases, they should be the same. However, in Versions later than SQL 2011, a new syntax is used to implement paging. It is estimated that we still use ROW_NUMBER to ensure compatibility with SQL 2005, OFFSET-FETCH is used for filtering. It is only available after SQL 2011. In the above example, we take data from 31 to 40. If OFFSET-FETCH is used, we can see how much data is skipped and then how much data is captured, therefore, we need to skip the first 30 data records and retrieve the next 10 data records.

USE TSQL2012GODECLARE @PageSize INT = 10, @PageIndex INT = 3SELECT *FROM Sales.CustomersORDER BY custidOFFSET @PageIndex * @PageSize ROWSFETCH NEXT 10 ROWS ONLYGO

If you do not have low requirements for the SQL version, the use of OFFSET-FETCH is simply amazing.

COALESCE compare to ISNULL

In the previous section, we talked about some string functions. One of the missing string functions is COALESCE, which is available only on SQL 2008 +. There are several other functions similar to string functions, let's take a look. Msdn defines it as: Calculate the variable in order and return the current value of the first expression that is not equal to NULL at first. Returns the Data Type of the expression with the highest data type priority. If all expressions cannot be Null, The result type cannot be Null. If all parameters are NULL, COALESCE returns NULL. At least one Null value should be NULL type. To put it bluntly, it is the processing of NULL. Let's take a look at the next example.

USE TSQL2012GOSELECT custid, country, region, city, country + COALESCE(N''+ region, N'') + N',' + city AS locationFROM Sales.Customers

As we can see above, we use the COALESCE function to replace NULL with a NULL string for processing. SQL 2012 also introduces the CONCAT function to receive a list of input to be connected and automatically replaces NULL with a NULL String. The above can also be replaced by the CONCAT function.

USE TSQL2012GOSELECT custid, country, region, city,country + CONCAT(country,N''+ region, N',' + city) AS locationFROM Sales.Customers

At the same time, we can see that the CONCAT function parameter must have at least two:

Msdn interprets CONCAT as follows: CONCAT uses variable number of string parameters and concatenates them into a single string. It requires at least two input values; otherwise, an error is thrown. All parameters are implicitly converted to the string type and connected together. The Null value is implicitly converted to a Null string. If all parameters are Null, an empty string of the varchar (1) type is returned. Implicit conversion to strings follows the existing data type conversion rules.

We continue to return to the COALESCE function, mainly to see the difference between it and the ISNULL function.

COALESCE and ISNULL Functions

Some people may think that ISNULL is faster than COALESCE function, or some people think that ISNULL is the same as COALESCE function, and others think that they should tend to use COALESCE function because it is ansi SQL standard function. Let's take a look at the differences between the two.

(1) COALESCE and ISNULL process different data types first

The COALESCE function determines that the type output is based on the data type precedence. Therefore, the DATETIME priority is higher than the INT priority when INT is processed as follows.

DECLARE @int INT, @datetime DATETIME;SELECT COALESCE(@datetime, 0);SELECT COALESCE(@int, CURRENT_TIMESTAMP);

For ISNULL functions, the data type is not affected by the Data Type priority, but is affected by the first item in the function parameter list. ISNULL is the exchange, and COALESCE is the combination of all parameter queries.

DECLARE @int INT, @datetime DATETIME;SELECT ISNULL(@datetime, 0);

Let's take a look at the following operations:

DECLARE @int INT, @datetime DATETIME;SELECT ISNULL(@int, CURRENT_TIMESTAMP);

In this case, the DATETIME cannot be converted to INT.

In this case, the following conversions must be performed explicitly:

DECLARE @int INT, @datetime DATETIME;SELECT ISNULL(@int, CONVERT(INT,CURRENT_TIMESTAMP));SELECT ISNULL(@int, CAST(CURRENT_TIMESTAMP AS INT));

(2) ISNULL may cause data loss

Let's look at the comparison examples of the two.

DECLARE @c5 VARCHAR(5);SELECT 'COALESCE', COALESCE(@c5, 'Jeffcky Wang')UNION ALLSELECT 'ISNULL', ISNULL(@c5, 'Jeffcky Wang');

In the preceding definition, the length of the string variable is 5, but the ISNULL string is truncated. Here we can think that ISNULL will cause data loss rather than error. Why is such a result? As mentioned above, ISNULL is affected by the first parameter. Its length is defined as 5, so it can only be 5, which is truncated. The COALESCE function focuses on detecting all elements, this is 12, so it will return completely. We can see by running the following command.

DECLARE @c5 VARCHAR(5);SELECT  c = COALESCE(@c5, 'Jeffcky Wang'),  i = ISNULL(@c5, 'Jeffcky Wang')INTO dbo.TestISNULL_COALESCESELECT name, t = TYPE_NAME(system_type_id), max_length, is_nullable FROM sys.columns WHERE [object_id] = OBJECT_ID('dbo.TestISNULL_COALESCE');

We can see that the result of COALESCE merging is empty, and ISNULL is not. There is a little difference.

(3) COALESCE requires persistence for column computing

Next, let's take a look at the biggest difference between the two. We can create a primary key or non-null constraint on the calculated column to see the difference between ISNULL and COALESCE.

CREATE TABLE dbo.CreateISNULL( a INT, b AS ISNULL(a, 15) PRIMARY KEY);

Let's take a look at the COALESCE function to calculate columns.

CREATE TABLE dbo.CreateCOALESCE( a INT, b AS COALESCE(a, 15) PRIMARY KEY);

Obviously, we need to persist the column by adding the PERSISTED keyword as follows.

CREATE TABLE dbo.CreateCOALESCE( a INT, b AS COALESCE(a, 15) PERSISTED PRIMARY KEY);

Let's take a look at the difference between the two.

DECLARE @c CHAR(10);SELECT 'x' + COALESCE(@c, '') + 'y';SELECT 'x' + ISNULL(@c, '') + 'y';

Here we can summarize the differences between the two: ISNULL focuses on replacement, while COALESCE focuses on merging. COALESCE indicates that NULL is ignored and is filled and compressed with an empty string, while ISNULL indicates that NULL is filled with an empty string but not compressed.

(4) The COALESCE function supports more than two parameters.

For multiple parameter input, the ISNULL function requires nested calls, and COALESCE can process any number of parameters. The upper limit is unknown. Therefore, COALESCE is used for multiple parameters. The following describes how to use multiple parameter input.

SELECT COALESCE(a, b, c, d, e, f, g) FROM dbo.table;

For ISNULL, we need to do this.

SELECT ISNULL(a, ISNULL(b, ISNULL(c, ISNULL(d, ISNULL(e, ISNULL(f, g)))))) FROM dbo.table;

The final execution of the two is the same as that of the use CASE.

CASE   WHEN [tempdb].[dbo].[table].[a] IS NOT NULL THEN [tempdb].[dbo].[table].[a]  ELSE CASE WHEN [tempdb].[dbo].[table].[b] IS NOT NULL THEN [tempdb].[dbo].[table].[b]  ELSE CASE WHEN [tempdb].[dbo].[table].[c] IS NOT NULL THEN [tempdb].[dbo].[table].[c]  ELSE CASE WHEN [tempdb].[dbo].[table].[d] IS NOT NULL THEN [tempdb].[dbo].[table].[d]  ELSE CASE WHEN [tempdb].[dbo].[table].[e] IS NOT NULL THEN [tempdb].[dbo].[table].[e]  ELSE CASE WHEN [tempdb].[dbo].[table].[f] IS NOT NULL THEN [tempdb].[dbo].[table].[f]  ELSE [tempdb].[dbo].[table].[g] END END END END END END

(5) Performance Comparison Between COALESCE and ISNULL

Run the following query:

DBCC DROPCLEANBUFFERS;DECLARE  @a VARCHAR(5), -- = 'str_a', -- this line changed per test @b VARCHAR(5), -- = 'str_b', -- this line changed per test @v VARCHAR(5),  @x INT   = 0, @time DATETIME2(7) = SYSDATETIME();WHILE @x <= 500000BEGIN SET @v = COALESCE(@a, @b); --COALESCE SET @x += 1;ENDSELECT DATEDIFF(MILLISECOND, @time, SYSDATETIME());GODBCC DROPCLEANBUFFERS;DECLARE  @a VARCHAR(5), -- = 'str_a', -- this line changed per test @b VARCHAR(5), -- = 'str_b', -- this line changed per test @v VARCHAR(5),  @x INT   = 0, @time DATETIME2(7) = SYSDATETIME();WHILE @x <= 500000BEGIN SET @v = ISNULL(@a, @b); --ISNULL SET @x += 1;ENDSELECT DATEDIFF(MILLISECOND, @time, SYSDATETIME());

We can query four scenarios: (1) Both parameters are NULL (2) The first parameter is NULL (3) and the second parameter is NULL (4). Both parameters are NULL. The following results are obtained after ten tests in each scenario:

From the above, we can see that there is no major difference in the performance between the two. We don't need to worry too much. Of course, the above scenarios are not completely covered. At least we can explain some of them. The execution time of the preceding result is displayed. Now let's look at the execution plan of the two.

SELECT COALESCE((SELECT MAX(index_id) FROM sys.indexes WHERE [object_id] = t.[object_id]), 0) FROM sys.tables AS t;SELECT ISNULL((SELECT MAX(index_id) FROM sys.indexes WHERE [object_id] = t.[object_id]), 0) FROM sys.tables AS t;

The above may not be accurate. It may also be related to hardware configuration, or the performance difference between COALESCE and ISNULL. There should be no big difference in performance between the two.

(6) ISNULL and natural language descriptions are inconsistent

Why is it different from the natural language description? That is to say, when we determine what a value will do if it is NULL and do not do anything if it is NULL, the SQL statement in the query language is described as follows:

IF ISNULL(something) -- do something

From a natural language perspective, if something is NULL, the translation is inconsistent. Because there is no boolean type in SQL Server, we can only perform the following Conversions

IF something IS NULL -- do something-- orIF ISNULL(something, NULL) IS NULL -- do something-- orIF ISNULL(something, '') = '' -- do something

(7) Use GUID to check the ISNULL

Before this section, let's take a look at an example to subvert your ideas and drive you crazy.

SELECT ISNULL(NEWID(), 'JeffckyWang') AS Col1

In this case, it is okay. We will insert it into the table and then look at the description of its columns.

SELECT ISNULL(NEWID(), 'JeffckyWang') AS Col1INTO dbo.IsNullExample2;EXEC sp_help 'dbo.IsNullExample2';

The data in the table does exist, but the description of the column can be empty.

Summary

The above focuses on the differences between COALESCE and ISNULL functions. Through this section, we will talk about the scenarios and differences between the two. Should we have some ideas about whether COALESCE or ISNULL should be used? In most cases, it is better to use COALESCE. First, this function is used as a standard SQL function, and the second function supports more parameters than ISNULL, while ISNULL requires nesting, is there no Application Scenario for ISNULL? Of course, when querying data, you can determine whether the data is NULL. In this case, use ISNULL, for example

SELECT  ISNULL(argument, '') INTO dbo.IsNullExample;

This document compares ISNULL and COALESCE: Deciding between COALESCE and ISNULL in SQL Server. This section ends with a brief introduction and in-depth understanding. We will try again later in this section. good night!

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, you can leave a message and share it with us!