Reprint Address: http://blog.csdn.net/dba_huangzj/article/details/8300784#
Directory
- First look at the brief definition of Books Online
- Grammar
- Here are a few more useful examples
- First take a look at how this function is used on MSDN COALESCE function returns a value that is not NULL in a parameter, such as
- Then take a look at applying the function to pivot. The following statement runs on the AdventureWorks database
- If you want to reverse the result you can use the following statement
- Using functions to execute multiple SQL commands
- There is also a very important feature when you try to restore a library and find that it is not exclusive access when this feature is very effective we can open multiple windows to simulate multiple connections and execute the following script
Many people know the IsNull function, but few people know that 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 use:
first look at the brief definition of Books Online:
Returns the first non-empty expression in its argument
Syntax:
expression [ ,
If all parameters are null, then COALESCE returns NULL. There should be at least one null value for the null type. Although ISNULL is equivalent to coalesce, their behavior is different. An expression containing a ISNULL with a non-null argument is treated as NOT null, and an expression containing a coalesce with a non-null argument is treated as null. In SQL Server, to create an index on an expression containing a coalesce with a non-null argument, you can use the PERSISTED column property to persist the computed column, as shown in the following statement:
[SQL]View Plaincopyprint?
- create table #CheckSumTest
- (
- ID int identity,
- Num int default (RAND () * +), span>
- rowchecksum AS Span class= "func" >coalesce (CHECKSUM (ID, num), 0) PERSISTED primary key&n Bsp
- );
CREATE TABLE #CheckSumTest ( ID int identity, Num int DEFAULT (RAND () *), rowchecksum as COALESCE ( CHECKSUM (ID, num), 0) PERSISTED PRIMARY KEY );
Here are a few more useful examples:
first, take a look at the use of this function from MSDN, the COALESCE function (hereinafter referred to as the function), and return a non-null value for a parameter. such as:
[SQL]View Plaincopyprint?
- SELECT COALESCE (null, null, GETDATE ())
SELECT COALESCE (null, NULL, GETDATE ())
Since all two parameters are null, the value of the GETDATE () function is returned, which is the current time. That is, the first non-null value is returned. Since this function returns the first non-null value, there must be at least one non-null value inside the parameter, and if you use the following query, you will get an error:
[SQL]View Plaincopyprint?
- SELECT COALESCE (null, null, null)
SELECT COALESCE (null, NULL, NULL)
then take a look at applying the function to pivot, and the following statement runs on the AdventureWorks database:
[SQL]View Plaincopyprint?
- SELECT Name
- From HumanResources.Department
- WHERE (groupname= ' Executive generaland Administration ')
SELECT Name from humanresources.department WHERE (groupname= ' Executive generaland Administration ')
will get the following result:
If you want to reverse the result, you can use the following statement:
[SQL]View Plaincopyprint?
- declare @DepartmentName varchar (
-
- select @ Departmentname = coalesce (@DepartmentName, name + '; '
- from humanresources.department
- where (groupname= Span class= "string" > ' Executive Generaland administration ')
-
- select @DepartmentName as departmentnames
-
DECLARE @DepartmentName VARCHAR (+) SELECT @DepartmentName = COALESCE (@DepartmentName, ') + Name + '; ' From humanresources.department WHERE (groupname= ' Executive generaland Administration ') SELECT @ Departmentname as Departmentnames
use functions to execute multiple SQL commands:
Once you know that this function can be reversed, you should also know that it can run multiple SQL commands. and use semicolons to differentiate between individual operations. The following statement is the value of a column named name under the person schema:
[SQL]View Plaincopyprint?
- DECLARE @SQL VARCHAR (MAX)
- CREATE TABLE #TMP
- (CLMN VARCHAR),
- 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
- DROP TABLE #TMP
DECLARE @SQL VARCHAR (MAX) CREATE TABLE #TMP (clmn varchar ($), 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 DROP TABLE #TMP
There is also a very important function:. This feature works well when you try to restore a library and find that it cannot be accessed exclusively. Let's open multiple windows to simulate multiple connections. Then execute the following script:
[SQL]View Plaincopyprint?
- declare @SQL varchar (8000)
-
- select @SQL = coalesce (@SQL, ' Kill ' + cast (spid span class= "keyword" >as varchar (ten) +
- from sys.sysprocesses
- where DBID = db_id ( Span class= "string" > ' AdventureWorks ')
-
- print @ SQL --exec (@SQL) Replace The print statement with EXEC to execute
DECLARE @SQL varchar (8000) SELECT @SQL = COALESCE (@SQL, ") + ' Kill ' + CAST (spid as VARCHAR (10)) + '; ' From sys.sysprocesses WHERE DBID = db_id (' AdventureWorks ') print @SQL--exec (@SQL) Replace the print Statement with EXEC to execute
The results are as follows:
Then you can copy the results and kill all the sessions at once.