SQL Server coalesce functions and examples _mysql

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!