SQL2012 New System functions & analysis functions

First, System functions

1 IIf function equivalent to the trinocular operator in C #

This function is the same as the IIf function in VBA, determines whether the expression of the first argument is true, returns the second argument, and False returns a third argument.

With this function many times we can no longer use complex case-time syntax. For example, we determine the size of the item to display the corresponding string, then the old wording is:

SelectP.code, Case whenP.size> - Then'Big' Else'Small'End asSize_string
fromPROJECT P
whereSIZE is notNULL

--now, we can simply write:
SelectP.code,iif (p.size> -,'Big','Small') asSize_string
fromPROJECT P
whereSIZE is notNULL

2 string connection concat function without judging type and null

SQL Server has a simple connection to a string, using the "+" sign directly, but you need to be aware of the two questions, one is that the required type is a string type and, if it is a numeric type, it will report a syntax error, so you must convert the number type to a string. Second, if one of the values is null, then the result of the entire connection is a null string, so you also need to determine the null, so a query that is just a connection string will be very complex:

SelectP.PROJECT_ID, P.code+','+P.name+','+ISNULL(P.nick_name,"')+','+ISNULL(CONVERT(varchar( -), P.size),"')
fromPROJECT P


--now use the concat function, ignoring the type directly, ignoring null checks and connecting directly to a non-empty string:
SelectP.project_id,concat (P.code,',', P.name,',', P.nick_name,',', p.size)
fromPROJECT P

The concat is used to connect two strings as shown in its name. But the enhancement over the past can eliminate the hassle of type conversion and directly connect multiple values to a string value to return

You can clearly feel a lot of brevity.

3. The Format function is formatted when converting to a string.

Before you convert a number or date to a string, you can use the CONVERT function to specify the format of the transformation with a parameter of the third integer type, but it is too cumbersome, the integer parameter is not easy to understand and remember, and it is not flexible. The current format function is equivalent to the String.Format function in C #, which you can expect to output in the second argument.

SelectP.project_id,format (P.created_time,'YYYY-MM-DD'),CONVERT(varchar( -), P.created_time, the)
fromPROJECT

4 Let the enumeration show a more convenient choose function.

Enumeration values are often used in a program to hold enumeration values in a database, but it is not easy to understand the meaning of the enumeration values when viewed, and you must see what the code looks for 1 and what 2 corresponds to know. If you want to display a string in the display, you need to use case when to judge. You can now use the Choose function to make it easy to convert an enumeration into a string. For example, to show the status of a project, our query is:

SelectP.code,choose (P.status,'Plan','Exec',' Complete','Abort','Fail')
fromPROJECT P

The Chosse function has several drawbacks than the case, 1 is not supported 0 and negative numbers, so if the value of the enumeration is 0 then there is no way to display, 2 is the enumeration value must be continuous and smaller, can not use 100, 200 equivalent, if used choose that must be written dead. Without the default value, when using case, if there is another else value that does not match, and if there is no match after using choose, then it is a null value. So personally, the use of this function is very

5 various date-time functions.

Except for a eomonth function that returns the last day of a given date, the other new function is to pass the date as a parameter, returning the object of the specified data type, which is equivalent to the transformation of the CONVERT function. The overall use is not much, in this not much introduction

Second, the over clause enhancement and some new analytic functions.

The over clause was previously used for ranking functions such as Rank,row_number, and now the over clause has been greatly enhanced to apply the over clause to the aggregation function and to add some analytic functions.

For example, I have a project and a Customer table, a customer for multiple projects, now need to know the customer's information and each customer's latest project code, this is not a good implementation, now we have the analysis function, you can use First_value or last_value and then with the over clause, Get the results we want:

SelectdistinctC.*, First_value (P.code) Over(PARTITION byc.client_idOrder byP.[Created_time]desc) asLast_project_code
fromPROJECT P
InnerJoinCLIENT C
onp.client_id=c.client_id

SQL2012 New System functions & analysis functions