Some new features of SQL Server2012 in software development

The authorities have given a lot of new features to SQL2012 relative to sql2008r2, but most of them are floating clouds for the average developer, and there are some new features that are more useful to developers. I finally see similar objects in SQL Server, just a little bit different from the syntax used. The creation syntax is also create SEQUENCE, which is used when you need to use next value for to remove a value:

Copy the code code as follows:
CREATE SEQUENCE [dbo]. [Sq_1]
As [bigint]
START with 1
INCREMENT by 1;

SELECT NEXT VALUE for [sq_1] as Firstuse;

If you want to insert a value, it is:

Copy the code code as follows:
INSERT into T1 (C1,C2)
VALUES (NEXT VALUE for sq_1, ' Test ');

But does it seem like there is no syntax for getting the current value, do I have to take down a value?

Second, the new paging query syntax.
Previously paged in SQL Server, first with top or temporary tables, and then with the Row_number function implemented paging, now the newest SQL2012 can be paged by the ORDER BY clause followed by offset and fetch, feeling a bit like the syntax of LINQ. For example, after querying 1W rows of 20 valid project information, then row_number paged query SQL is:

Copy the code code as follows:
SELECT *
From
(
Select *,row_number () over (order by p.project_id) R
From PROJECT P
where p.is_deleted=0
) x
where X.R between 10001 and 10020 have a new syntax, the query statement is:

SELECT *
From PROJECT P
where p.is_deleted=0
ORDER by p.project_id
OFFSET 10001 ROWS
FETCH NEXT + ROWS only;

It is clear that the new syntax will make the code look more concise and the meaning expressed more clearly. In terms of execution efficiency, it's the same as a try.

Third, some new system functions.
3.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:

Copy the code code as follows:
Select P.code,case when p.size>100 then ' Big ' Else ' Small ' end as Size_string
From PROJECT P
Where SIZE is not null

Now, we can simply write:

Copy the code code as follows:
Select P.code,iif (p.size>100, ' Big ', ' Small ') as Size_string
From PROJECT P
Where SIZE is not null

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

Copy the code code as follows:
Select p.project_id, p.code+ ', ' +p.name+ ', ' +isnull (P.nick_name, ') + ', ' +isnull (CONVERT (varchar), p.size), ')
From PROJECT P

Now use the CONCAT function, ignoring the type directly, ignoring null checks and connecting directly to a non-empty string:

Copy the code code as follows:
Select P.project_id,concat (P.code, ', ', p.name, ', ', P.nick_name, ', ', p.size)
From PROJECT P

You can clearly feel a lot of brevity.

The Format function is formatted when you convert 3.3 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.

Copy the code code as follows:
Select P.project_id,format (p.created_time, ' yyyy-mm-dd '), CONVERT (varchar (), p.created_time,112)
From PROJECT P

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

Copy the code code as follows:
Select P.code,choose (p.status, ' Plan ', ' Exec ', ' complete ', ' Abort ', ' Fail ')
From PROJECT 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

3.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, not much in this introduction.

Iv. enhancements to the over clause and some additional 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:

Copy the code code as follows:
SELECT DISTINCT C.*,first_value (p.code) over (PARTITION by c.client_id ORDER by P.[created_time] desc) as Last_project_ CODE
From PROJECT P
INNER JOIN CLIENT C
On p.client_id=c.client_id

Some new features of SQL Server2012 in software development