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!