Analysis of SQL Server paging mode IsNull and coalesce performance comparison _mssql

Objective

In the previous section we talked about data types and a few things to note in the string, and we went on to talk about string lines as well as other content and interspersed content, short content, in-depth explanation. (Refer to the article "detailed SQL Server data type")

Paging Way

In SQL 2005 or SQL 2008 we use the Row_number open window function for paging, and we'll talk about the window function in detail in the SQL step. As follows:

Use TSQL2012
go
DECLARE @StartRow int
DECLARE @EndRow int
Set @StartRow = to
set @EndRow =
SEL ECT [Address], [City], [region] from
(
SELECT [sc].[ Address], [SC]. [City], [SC]. [Region],
row_number () over (order by
[sc].[ Address], [SC]. [CITY],[SC]. [CustID]) As RowNumber from
sales.customers SC) salecustomer
WHERE rownumber > @StartRow and RowNumber < @EndRow 
   order by [address], [City], [region];

The code above must not be explained by me, and can also be paginated with views

With Salecustomer as
(
SELECT [sc].[ Address], [SC]. [City], [SC]. [Region],
row_number () over (order by
[sc].[ Address], [SC]. [CITY],[SC]. [CustID]) As RowNumber
from Sales.customers SC)
SELECT [address], [City], [region] from
salecustomer
WHERE RowNumber > @StartRow and RowNumber < @EndRow
ORDER BY [address], [City], [region]
Go

Let's take a look at whether the two use SQL query statements and views have no performance differences? Come on, the test is.

From here we can see that the two are not different in terms of performance costs, most of the case should be the same. But after SQL 2011, there is a new syntax for paging, and it is estimated that we still use row_number, possibly to be compatible with the SQL version to 2005, using Offset-fetch for filtering filtering, which is based on SQL 2011. The above we take from 31 to 40 of the data, if implemented with Offset-fetch, we see this function literal meaning can know how much skip the data and grab how much data, so we need to skip the first 30 data, we need to take the next 10 data.

Use TSQL2012
go
DECLARE @PageSize int = ten, @PageIndex int = 3
SELECT * from
sales.customers
ORD ER by CustID
OFFSET @PageIndex * @PageSize ROWS
FETCH NEXT rows

If the SQL version requirements are not low, the use of offset-fetch to achieve it is a blast.

Coalesce Compare to ISNULL

In the previous section we talked about string functions, which omitted a string function called COALESCE, which is available on the SQL 2008+, and there are several other similar handles to string functions, which we'll look at together. It is defined by MSDN to evaluate the variable in order and return the current value of the first expression that is not initially equal to NULL. Returns the data type of the expression with the highest priority for the data type. If all expressions are not nullable, the type of the result is also not nullable. If all parameters are NULL, COALESCE returns NULL. At least one null value should be a null type. To be blunt is to treat null. Let's take a quick look at the next example.

Use TSQL2012
go
SELECT custid, country, region, city,
 Country + COALESCE (n ' + region, n ' ") + n ', ' + City as Lo cation from
sales.customers

As we can see above, we use the COALESCE function to substitute null strings for processing. SQL 2012 also introduces the CONCAT function to receive an input list to connect and automatically replace null with an empty string, which can also be replaced with the concat function.

Use TSQL2012
go
SELECT custid, country, region, city,
country + CONCAT (country,n "+ region, N ', ' + City) as Lo cation from
sales.customers

At the same time we see that the following figure knows that there are at least two concat function parameters:

MSDN interprets the CONCAT function as follows: CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires at least two input values, or an error is raised. All parameters are implicitly converted to string types, and then concatenated together. The null value is implicitly converted to an empty string. If all parameters are null, an empty string of type varchar (1) is returned. The process of implicitly converting to a string follows an existing data type conversion rule.

Let's go back to the COALESCE function, mainly to see how it differs from the IsNull function.

Discussion on coalesce and ISNULL functions

Some people may think that IsNull is faster than the COALESCE function, or that isnull and coalesce functions are equivalent, and others think that the coalesce function should be preferred because it is an ANSI SQL standard function. Think of it, then what's the difference between the two, let's look at it together.

(1) Coalesce and ISNULL processing data types prefer different

The COALESCE function determines that the output of the type is based on the data type precedence of the "data type precedence", so that the datetime priority is higher than int when processing int.

DECLARE @int int, @datetime datetime;
SELECT COALESCE (@datetime, 0);
SELECT COALESCE (@int, current_timestamp);

For the IsNull function, the data type is unaffected by the priority of the data type, but by the first item of the function argument list, isnull in exchange and coalesce on the merging of all the parameter queries.

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

Let's see what happens when you do the following

DECLARE @int int, @datetime datetime;
SELECT ISNULL (@int, Current_timestamp);

A datetime cannot be converted to int

At this point we need to explicitly do the following conversions

DECLARE @int int, @datetime datetime;
SELECT ISNULL (@int, CONVERT (Int,current_timestamp));
SELECT ISNULL (@int, CAST (current_timestamp as int));

(2) IsNull will cause data loss

Let's take a look at the comparison between the two examples

DECLARE @c5 VARCHAR (5);
Select ' Coalesce ', Coalesce (@c5, ' Jeffcky Wang ')
UNION all
SELECT ' ISNULL ', ISNULL (@c5, ' Jeffcky Wang ');

We define the string variable length of 5 and use the IsNull string to be intercepted, where we can assume that the isnull will result in loss of data rather than error. Why is there such a result? We've talked about that IsNull is affected by the first parameter, the length is defined as 5, so only 5, which is intercepted, and the COALESCE function looks at all the elements, and 12 so it returns completely. We can see it by running the following.

DECLARE @c5 VARCHAR (5);
SELECT 
 C = COALESCE (@c5, ' Jeffcky Wang '), 
 i = ISNULL (@c5, ' Jeffcky Wang ') into
dbo. Testisnull_coalesce
SELECT name, t = type_name (system_type_id), Max_length, is_nullable from
 sys.columns
 WHERE [object_id] = object_id (' dbo. Testisnull_coalesce ');

We see the result of the above coalesce merger to be nullable and IsNull not, a little bit different.

(3) Coalesce need to be persisted when calculating the columns

Let's take a look at the biggest difference between the two, and we'll look at the difference between IsNull and coalesce by counting the columns and creating primary keys or non-null constraints on them.

CREATE TABLE dbo. Createisnull
(a
 INT,
 B as ISNULL (A,) PRIMARY KEY
);

Let's take a look at the COALESCE function to compute the column

CREATE TABLE dbo. Createcoalesce
(a
 INT,
 B as coalesce (A,) 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,) PERSISTED PRIMARY KEY
);

Let's take a look at a different one.

DECLARE @c CHAR (a);
SELECT ' x ' + COALESCE (@c, ') + ' Y ';
SELECT ' x ' + ISNULL (@c, ') + ' Y ';

We're here. In fact, we can generalize a little about the difference between the two: IsNull focuses on substitution, while Coalesce focuses on merging. The coalesce display ignores null and fills and compresses with an empty string, while IsNull null is populated with an empty string but not compressed.

(4) Coalesce function supports more than two parameters

For multiple parameter inputs, the ISNULL function requires nested calls, and coalesce can handle any number, and the upper limit is unknown, so use coalesce more for multiple parameters, such as using multiple parameter inputs.

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

And for IsNull, we need to do this.

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

When they finally execute, they use the same

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

(5) Comparison of performance between Coalesce and IsNull

We'll run the following query

DBCC dropcleanbuffers;
DECLARE 
 @a VARCHAR (5),--= ' str_a ',--this line changed the
 per Test @b VARCHAR (5),--= ' str_b ',--- Ed per test
 @v VARCHAR (5), 
 @x INT   = 0,
 @time DATETIME2 (7) = Sysdatetime ();
While @x <= 500000
BEGIN
 SET @v = COALESCE (@a, @b);--coalesce
 SET @x + + 1;
End
SELECT DATEDIFF (millisecond, @time, Sysdatetime ());
Go
DBCC dropcleanbuffers;
DECLARE 
 @a VARCHAR (5),--= ' str_a ',---this line changed the
 per Test @b VARCHAR (5),--= ' str_b ',----The line Cha nged per Test
 @v VARCHAR (5), 
 @x INT   = 0,
 @time DATETIME2 (7) = Sysdatetime ();
While @x <= 500000
BEGIN
 SET @v = ISNULL (@a, @b);--isnull
 SET @x + + 1;
End
SELECT DATEDIFF (millisecond, @time, Sysdatetime ());

We have queries four scenarios: (1) Two parameters are null (2) The first argument is null (3) The second argument is null (4) and two parameters are null. Each scene is tested 10 times and the results are as follows

We can see from the above that there is not much difference in performance, we do not need to worry too much, of course, the above scenario is not fully covered, at least to explain a part. Above we get the results to view the execution time, now let's look at both the query execution plan.

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, but also related to hardware configuration, it is possible to coalesce performance difference and isnull. There should be no significant difference in performance.

(6) IsNull and natural language descriptions are inconsistent

Why is it inconsistent with the natural language description? That is, when we judge what a value is null to do, not to do anything for NULL, the query language SQL is described as follows:

IF ISNULL (something)
 -do something

We use the natural language perspective to translate what we do if something is null, which is inconsistent at this time. Because there is no Boolean type in SQL Server, we can only do the following conversions

IF something is null
 --does something
--or
if ISNULL (something, NULL) is null
 --does something
--or
IF ISNULL (Something, ') = '
 --do something

(7) using GUID to see the isnull of exotic flowers

Before introducing this section, let's take a look at an example to subvert your thoughts and make you mad.

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

So it's okay, we insert it into the table and look at the description of its column

SELECT ISNULL (NEWID (), ' Jeffckywang ') as Col1 into
dbo. IsNullExample2;
EXEC sp_help ' dbo. IsNullExample2 ';

The data in the table does exist, but the description of the column is nullable.

Summarize

The above focuses on the difference between coalesce and ISNULL functions, through this section of the two scenes and differences, we should have a little idea, in the end is the use of coalesce or isnull it? In most cases, it is better to take advantage of coalesce, one is that this function is as a SQL standard function, the second one can support more parameters relative to IsNull, while isnull needs nesting, and for IsNull there is no application scenario? Of course, there is a decision to determine whether the data is null when querying the data, in which case the isnull is used, for example, the following

SELECT 
 ISNULL (argument, ') into 
dbo. Isnullexample;

This article is a comparative reference article about ISNULL and coalesce: deciding between Coalesce and ISNULL in SQL Server. This is the end of this section, short content, deep understanding, we'll see you next day, good night!

Above is the entire content of this article, I hope the content of this article for everyone's study or work can bring certain help, if there are questions you can message exchange, but also hope that a lot of support cloud Habitat community!