SQL Server COALESCE function details and examples, coalesce function details

SQL Server COALESCE function details and examples, coalesce function details

SQL Server COALESCE Functions

Many people know the ISNULL function, but few know the Coalesce function. People will accidentally use the Coalesce function and find it more powerful than ISNULL. In fact, this function has been very useful so far, this article mainly describes some basic usage:

 First, let's take a look at the brief definitions of books online:

Returns the first non-empty expression syntax in the parameter:

COALESCE ( expression [ ,...n ] ) 

If all parameters are NULL, COALESCE returns NULL. At least one Null value should be NULL type. Although ISNULL is equivalent to COALESCE, their behavior is different. Expressions that contain ISNULL with non-NULL parameters are considered not null, while expressions that contain COALESCE with non-NULL parameters are considered NULL. To create an index for a COALESCE expression that contains non-null parameters in SQL Server, you can use the PERSISTED column attribute to make the calculation column persistent, as shown in the following statement:

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

 The following are some useful examples:
First, let's look at the usage of this function from MSDN. The coalesce function (hereinafter referred to as the function) returns a non-null value of the parameter. For example:

SELECT COALESCE(NULL, NULL, GETDATE()) 

Because both parameters are null, the value of the getdate () function is returned, that is, the current time. That is, the first non-empty value is returned. Because this function returns the first non-empty value, there must be at least one non-empty value in the parameter. If you use the following query, an error will be returned:

SELECT COALESCE(NULL, NULL, NULL) 

Then let's take a look at applying the function to the notebook. The following statement runs on the AdventureWorks database:

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

The following result is displayed:

To reverse the result, use the following statement:

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

Use a 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. Separate operations with semicolons. The following statement indicates the value of a column named Name in the Person architecture:

DECLARE @SQL VARCHAR(MAX)     CREATE TABLE #TMP    (Clmn VARCHAR(500),     Val VARCHAR(50))     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 an important feature :. This function is very effective when you try to restore a database and find that it cannot be exclusively accessed. 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')    PRINT @SQL --EXEC(@SQL) Replace the print statement with exec to execute 

The result is as follows:

Then you can copy the result and kill all sessions at one time.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!