SQL Server COALESCE functions detailed
Many people know the IsNull function, but few people know the COALESCE function, people will inadvertently use the COALESCE function, and found that it is more powerful than isnull, in fact, so far, this function is indeed very useful, this article mainly explains some of the basic uses:
first look at the brief definition of Books Online:
Returns the first NON-EMPTY expression syntax in its arguments:
If all parameters are NULL, COALESCE returns NULL. At least one null value should be a null type. Although ISNULL equals coalesce, their behavior is different. An expression that contains a ISNULL with a non-null parameter is treated as NOT null, and an expression containing a coalesce with non-null arguments is considered null. In SQL Server, to create an index on an expression containing a coalesce with a non-null parameter, you can use the PERSISTED column property to persist the computed column as shown in the following statement:
CREATE TABLE #CheckSumTest
(
ID int identity,
Num int DEFAULT (RAND () *),
rowchecksum as COALESCE ( CHECKSUM (ID, num), 0) PERSISTED PRIMARY KEY
Here are some more useful examples:
First, take a look at the use of this function from MSDN, the COALESCE function (hereinafter referred to as the function), return a parameter Non-null value. such as:
Because all two parameters are null, the value of the GETDATE () function is returned, which is the current time. Returns the first Non-null value. Since this function returns the first Non-empty value, there must be at least one non-null value in the parameter, and an error will be given if you use the following query:
Then take a look at the application of the function to pivot, the following statement runs on the AdventureWorks database:
SELECT Name
from humanresources.department
WHERE
Will get the following results:
If you want to reverse the result, you can use the following statement:
DECLARE @DepartmentName VARCHAR (1000)
SELECT @DepartmentName = COALESCE (@DepartmentName, ') + Name + '; '
From humanresources.department
WHERE (groupname= ' Executive generaland Administration ')
Use functions to execute multiple SQL commands:
When you know that this function can be reversed, you should also know that it can run multiple SQL commands. and use semicolons to differentiate individual operations. The following statement is the value of a column named name in the person schema:
DECLARE @SQL VARCHAR (MAX)
CREATE TABLE #TMP
(clmn VARCHAR (m),
Val VARCHAR ())
SELECT @SQL =coalesce (@SQL, ') +cast (' INSERT into #TMP Select ' + Table_schema + '. ' + table_name + '.
') + column_name + ' as CLMN, NAME from ' + Table_schema + '. [' + table_name +
']; ' As VARCHAR (MAX)) from
information_schema. COLUMNS
JOIN sysobjects B on information_schema. COLUMNS. table_name = b.name
WHERE column_name = ' NAME ' and
xtype = ' U ' and
table_schema = ' person '
PRINT @SQL
exec (@SQL)
SELECT * from #TMP
There is also a very important function:. This function is very effective when you try to restore a library and discover that you cannot access it exclusively. Let's open multiple windows to simulate multiple connections. Then execute the following script:
DECLARE @SQL VARCHAR (8000)
SELECT @SQL = COALESCE (@SQL, ') + ' Kill ' + CAST (spid as VARCHAR (10)) + '; '
from sys.sysprocesses
WHERE DBID = db_id (' AdventureWorks ')
The results are as follows:
Then you can copy the results and kill all sessions at once.
Thank you for reading, I hope to help you, thank you for your support for this site!