Coalesce, a very useful function

Turn: http://blog.csdn.net/dba_huangzj/article/details/8300784

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-null expression in the parameter.

 

 

Syntax:

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:

 

 

[SQL]View plaincopyprint?

1. Create Table # checksumtest
2. (
3. Id int identity,
4. Num int default (RAND () * 100 ),
5. Rowchecksum as coalesce (checksum (ID, num), 0) persisted primary key
6. );

 

 

 

 

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:

 

 

[SQL]View plaincopyprint?

1. 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:

 

 

[SQL]View plaincopyprint?

1. 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:

 

 

[SQL]View plaincopyprint?

1. Select name
2. From HumanResources. Department
3. Where (groupname = 'executive generaland admin ')

 

 

The following result is displayed:

 

 

To reverse the result, use the following statement:

 

 

[SQL]View plaincopyprint?

1. Declare @ departmentname varchar (1000)
3. Select @ departmentname = coalesce (@ departmentname, '') + name + ';'
4. From HumanResources. Department
5. Where (groupname = 'executive generaland admin ')
7. 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:

 

 

[SQL]View plaincopyprint?

1. Declare @ SQL varchar (max)
3. Create Table # TMP
4. (Clmn varchar (500 ),
5. Val varchar (50 ))
7. Select @ SQL = coalesce (@ SQL, '') + Cast ('insert into # TMP select ''' + table_schema + '.' + table_name + '.'
8. + Column_name + ''' as clmn, name from '+ table_schema +'. ['+ table_name +
9. '];' As varchar (max ))
10. From information_schema.columns
11. Join sysobjects B on information_schema.columns.table_name = B. Name
12. Where column_name = 'name'
13. And xtype = 'U'
14. And table_schema = 'person'
16. Print @ SQL
17. Exec (@ SQL)
19. Select * from # TMP
20. 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:

 

[SQL]View plaincopyprint?

1. Declare @ SQL varchar (8000)
3. Select @ SQL = coalesce (@ SQL, '') + 'Kill '+ Cast (spid as varchar (10) + ';'
4. From SYS. sysprocesses
5. Where dbid = db_id ('adventureworks ')
7. 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.

Coalesce, a very useful function