SQL server paging, IsNull and coalesce performance comparison (eight)

Objective

In the previous section we talked about the data types and some of the things we need to be aware of in the string, and this section goes on to talk about string lines as well as other content and interspersed content, short content, and in-depth explanations.

Paging method

In SQL 2005 or SQL 2008 we are using the Row_number window function to do paging, about the window function, we will be in the SQL advanced in the detailed talk. As follows:

= 40  SELECT> @StartRow and RowNumber < @EndRowORDER by [address], [City], [R Egion];

The above code must not need me to explain, while using the view can also be paged

> @StartRow and RowNumber < @EndRowORDER by [address], [City], [Region]go

Let's take a look at the two. Are there any performance differences between using SQL query statements and views? Come on, the test is.

From here we can see that there is no difference in performance overhead, and in most cases it should be the same. However, after SQL 2011 version of the new syntax to implement paging, it is estimated that we still use the row_number, perhaps to be compatible with the SQL version to 2005, the use of offset-fetch filter filtering, it is based on SQL 2011. The above we take data from 31 to 40, if implemented with Offset-fetch, we can see that the function literally means to know how much data is skipped and how much data to crawl, so we need to skip the previous 30 data, we need to take the next 10 data.

Ten 3  * fromsales.customersorder to CustID* @PageSize rowsfetch NEXT   ROWS only GO

If the SQL version requirements are not low, the use of offset-fetch to achieve a very cool burst.

Coalesce Compare to ISNULL

In the previous section we talked about some string functions, which omitted a string function called COALESCE, which is only available on SQL 2008+, and there are several other similar handling of string functions, which we look at together. MSDN defines it as: evaluates the variable sequentially and returns the current value of the first expression that is not initially equal to NULL. Returns the data type of the expression with the highest data type precedence. If all expressions are non-nullable, the type of the result is also not nullable. If all parameters are null, then COALESCE returns NULL. There should be at least one null value for the null type. To be blunt is to deal with NULL. Let's take a quick look at the next example.

+ COALESCE (n"+ region, n") + N',' + City as Locationfrom Sales.customers

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

+ CONCAT (country,n'+ region, N',' + City ) as Locationfrom Sales.customers

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

The MSDN Concat function is interpreted as:CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires at least two input values, otherwise an error is raised.all parameters are implicitly converted to string types, and then concatenated together. Null values are 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 the existing data type conversion rules.

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

Discussion on coalesce and IsNull function

Some people may think that IsNull is faster than the COALESCE function, or that some people think that the 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 that, so what is the difference between the two, we look together.

(1) Coalesce and ISNULL processing data type First difference

The COALESCE function determines that the type output is based on the data type precedence "datatype precedence", so the datetime priority is higher than int when working with int as follows.

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

For the IsNull function, the data type is not affected by the data type precedence, but rather through the first effect of the function argument list, isnull in exchange and coalesce in the merging of all parameter queries.

0);

Let's see what happens when we do the following

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

A datetime cannot be converted to int

(2) IsNull can cause data loss

Let's look at an example of the contrast between the two.

DECLARE @c5 VARCHAR (5'coalesce"jeffcky Wang"  ' ISNULL ', ISNULL (@c5, ' Jeffcky Wang      ');

The length of the string variable we defined above is 5, and the use of the IsNull string is intercepted, where we can assume that IsNull will result in data loss rather than error. Why does it come to this end? We have already said that IsNull is affected by the first parameter, its length is defined as 5, so it can only be 5, which is intercepted, and the COALESCE function looks at all elements, at this point 12, so it will be fully returned. We can see it by running the following.

DECLARE @c5 VARCHAR (5); SELECT   'jeffcky Wang'),   'jeffcky Wang' = Type_name (system_type_id), Max_length, is_nullable from  sys.columns  =  OBJECT_ID ('dbo. Testisnull_coalesce');

We see that the results of the coalesce merge above are nullable and isnull are not, a little bit different.

(3) Coalesce need to persist when calculating columns

Let's look at the difference between the two, and we'll look at the differences between IsNull and coalesce by calculating the column and creating a primary key or non-null constraint on it.

CREATE TABLE dbo. Createisnull (  a INT,  PRIMARY KEY);

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

CREATE TABLE dbo. Createcoalesce (  a INT,  PRIMARY KEY);

Obviously we need to persist the column by adding the persisted keyword.

CREATE TABLE dbo. Createcoalesce (  a INT,  PERSISTED) PRIMARY KEY);

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

DECLARE @c CHAR ("x" "y"   'x', 'y';

Here we are, in fact, a little generalization of the difference:IsNull focuses on substitution, while Coalesce focuses on merging. Coalesce shows that null is ignored and filled and compressed with an empty string, while isnull to null fills with an empty string but does not compress.

(4) Coalesce function supports more than two parameters

For multiple parameter inputs, the ISNULL function requires nested calls, and coalesce can handle any number, as far as the upper limit is unknown, so for multiple parameters use coalesce more, as with 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;

Both end up executing and using case

Case         [tempdb].[ DBO]. [Table]. [A] is isn't NULL then [tempdb]. [dbo]. [Table]. [A]    ELSE case when [tempdb]. [dbo]. [Table]. [b] is not NULL and then [tempdb]. [dbo]. [Table]. [b]    ELSE case when [tempdb]. [dbo]. [Table]. [C] is isn't NULL then [tempdb]. [dbo]. [Table]. [C]    ELSE case when [tempdb]. [dbo]. [Table]. [d] is isn't NULL then [tempdb]. [dbo]. [Table]. [d]    ELSE case when [tempdb]. [dbo]. [Table]. [E] is not NULL and then [tempdb]. [dbo]. [Table]. [E]    ELSE case when [tempdb]. [dbo]. [Table]. [F] is isn't NULL then [tempdb]. [dbo]. [Table]. [F]    ELSE [tempdb]. [dbo]. [Table]. [G] End end End End end

(5) Performance comparison of Coalesce and IsNull

Let's run the following query

DBCC dropcleanbuffers;declare @a VARCHAR (5),  -- ='str_a', -- ThisLine changed per Test @b VARCHAR (5),  -- ='Str_b', -- ThisLine 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', -- ThisLine changed per Test @b VARCHAR (5),  -- ='Str_b', -- ThisLine 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 have a query for four scenarios: (1) Two parameters are null (2) The first parameter is null (3) The second parameter is null (4) two parameters are null. Test each scene 10 times and end up with the following results

From the above, there is not much difference in performance, we do not need to worry about it, of course, the above scenario is not completely covered, or at least to explain part. The results we got above look at the execution time, now let's look at the two query execution plans.

0 ) from  sys.tables as T;   0 ) from  sys.tables as T;

The above may not be accurate, but also related to hardware configuration, it is possible to coalesce poor performance and IsNull. There should be no big difference in performance.

(6) Inconsistent description of isnull and natural language

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

IF ISNULL (something)    do something

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

IF Something is NULL    Do something   -- OrIF ISNULL (something, null) is  NULLdo  something  --"  'do   something

(7) Using GUIDs to see the isnull of the wonderful flowers

Let's look at an example before we introduce this section to subvert your thoughts and make you mad.

' Jeffckywang ') As Col1

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

' Jeffckywang '  'dbo. IsNullExample2';

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

Summarize

The above focus on the differences between coalesce and ISNULL functions, through this section of the two scenarios and differences, we should have a little idea, in the end is the use of coalesce or isnull it? In most cases, it is still good to use coalesce, one is that this function is as a SQL standard function, the second relative to isnull it can support more parameters, and isnull need to nest, and for IsNull there is no application scenario, Of course there is the use of isnull when querying data to determine whether the data is null, in this case, for example, the following

SELECT       "' ) into dbo. Isnullexample;

A comparison of ISNULL and coalesce is presented in this article: deciding between Coalesce and ISNULL in SQL Server. We conclude this section with a brief, in-depth understanding of our next section, good night!

SQL server paging, IsNull and coalesce performance comparison (eight)