MasterofProject

Sogou A very useful function - COALESCE

Label COALESCESqlserverfunctionT-SQL
45145 people read comment(5) Collection report
Classification:

A lot of people know the isnull function, but few people know the coalesce function, people will inadvertently used to coalesce function, and found that it is stronger and more powerful than the isnull, in fact so far, this function is indeed very useful, this article mainly explain the use some of the basic:

 

First look at the brief definition of books online:

 

Returns the first non null expression in its argument


 

Grammar:

 

COALESCE (Expression[,N...])

 

 

If all the parameters are COALESCE, then NULL 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 that contains a ISNULL with a non null argument will be treated as a NULL NOT, and an expression that contains a COALESCE with a non null argument will be treated as a NULL. In Server SQL, if you want to create an index that contains an expression with a COALESCE that has a non null parameter, you can use the PERSISTED column attribute to persist the calculated column, as shown in the following statement:


 

TABLE #CheckSumTest CREATE
(
Int identity ID,
Int DEFAULT Num (RAND () * 100),
AS COALESCE RowCheckSum (CHECKSUM (ID, Num), 0) PRIMARY KEY PERSISTED
);

 


 

Here are a few more useful examples:


First, look at the use of this function from the coalesce method, MSDN function (the following referred to as the function), to return a parameter in the non null values. Such as:

 

 

COALESCE SELECT (NULL, NULL, GETDATE ())


Since the two parameters are null, so the return value of the getdate () function, that is, the current time. Returns the first non null value. Since this function is the first non null value, the parameter must have at least one non null value. If you use the following query, you will be reported to the:

 

COALESCE SELECT (NULL, NULL, NULL)



Then take a look at the function of the application to Pivot, the following statement on the AdventureWorks database to run:

Name SELECT
HumanResources.Department FROM
WHERE ('Executive Generaland Administration'GroupName=)


Will get the following results:



If you want to reverse the results, you can use the following statement:

@DepartmentName VARCHAR DECLARE (1000)
  
@DepartmentName SELECT = COALESCE (@DepartmentName, ') + Name +'; ';'
HumanResources.Department FROM
WHERE ('Executive Generaland Administration'GroupName=)
  
@DepartmentName AS DepartmentNames SELECT
  





Use the function 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 a semicolon to distinguish independent operation. The following statement is the value of the column in the Person schema, which has the name Name:


@SQL VARCHAR DECLARE (MAX)
 
TABLE #TMP CREATE
(VARCHAR Clmn (500),
VARCHAR Val (50)
 
SELECT (@SQL=COALESCE @SQL, +CAST'INSERT INTO #TMP ('') Select '+ TABLE_SCHEMA +' + TABLE_NAME + '.'
The 'AS + COLUMN_NAME + Clmn, Name + TABLE_SCHEMA + FROM' + TABLE_NAME +'.['
'AS']; VARCHAR (MAX))
INFORMATION_SCHEMA.COLUMNS FROM
Sysobjects B ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME JOIN = B.NAME
COLUMN_NAME WHERE ='Name'
Xtype AND ='U'
TABLE_SCHEMA AND ='Person'
 
@SQL PRINT
EXEC (@SQL)
 
SELECT * #TMP FROM
TABLE #TMP DROP


 


There is also a very important function:. This feature is very effective when you try to restore a library and find that you can't have exclusive access. We come to open multiple windows, to simulate a number of connections. And then execute the following script:

@SQL VARCHAR DECLARE (8000)
  
@SQL SELECT = COALESCE (@SQL) +'Kill '+ CAST (AS VARCHAR SPID (10)) +'; ';'
Sys.sysprocesses FROM
DBID WHERE = DB_ID ('AdventureWorks')
  
@SQL --EXEC Replace (@SQL) the print PRINT statement with exec to execute


 

The results are as follows:


Then you can copy the results, and then kill all session.


top
Nine
step on
One
Guess you're looking for
View comments
* the above user comments only represent their personal views, does not represent the views or position of the CSDN website
    personal data
    • Visit909202 times
    • Integral:Eleven thousand four hundred and eight
    • Grade
    • Rank:672nd name
    • Original100
    • Reproduced:37
    • Translation:95
    • Comments:424
    My micro blog
    Blog column
    Latest comments
    Quantum statistics