Some new features of SQL Server2012 in development _mssql

One, added the sequence object.
This is the most familiar database object for Oracle users, and is now finally seen in SQL Server for similar objects, just a little bit differently in the syntax used. The creation syntax is also created SEQUENCE, which uses the next value for to remove a value:

Copy 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, you are:

Copy Code code as follows:

INSERT into T1 (C1,C2)
VALUES (NEXT VALUE for sq_1, ' Test ');

But doesn't seem to provide the syntax to get the current value, do you have to remove a value?

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

Copy Code code as follows:

SELECT *
From
(
Select *,row_number () over (p.project_id) R
From PROJECT P
where p.is_deleted=0
) x
where X.R between 10001 and 10020 instead of 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;

Obviously, with the new syntax, the code looks more concise and the meaning is more clearly expressed. In terms of execution efficiency, try it, it's the same.

Third, some new system functions.
3.1 The IIf function equivalent to the three-mesh operator in C #

This function is the same as the IIf function in VBA, which determines whether the first parameter's expression is true, returns the second argument, and returns the third argument if it is false.

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, so the old writing is:

Copy 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 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 Join Concat function without judging type and null string

SQL Server would have a simple connection to a string, using the "+" number directly, but you need to pay attention to two problems, one must be type is a string type, if it is a numeric type then reported syntax errors, you must convert the number type to a string. The second is that if one of the values is null, then the result of the entire connection is a null string, so you need to judge null, so the query that would have been just a connection string would be very complex to write:

Copy 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, ignore the type directly, ignore the null check, and connect directly to a Non-empty string:

Copy Code code as follows:

Select P.project_id,concat (P.code, ', ', p.name, ', ', P.nick_name, ', ', p.size)
From PROJECT P

Can obviously feel the simplicity of a lot.

3.3 Format function formatted when converting to a string.

To convert a number or date to a string, you can use the CONVERT function and take a person of the third integer type to specify the format of the transformation, but this method is cumbersome, the integer parameters are not easy to understand and remember, and are inflexible. The Format function is now equivalent to the String.Format function in C #, where you can expect the output in the second argument.

Copy Code code as follows:

Select P.project_id,format (p.created_time, ' yyyy-mm-dd '), CONVERT (varchar (m), p.created_time,112)
From PROJECT P

3.4 Let the enumeration display a more convenient choose function.

Enumeration values are often used in the program, and tinyint is used in the database to hold the enumeration values, but it is not easy to understand what the enumeration values mean when viewed, and you must look at the code to see what the 1 corresponds to, and what the 2 corresponds to. If you want to display a string when displayed, you need to use the case when to judge. Now you can use the Choose function to make it easy to convert enumerations into strings. For example, to show the status of the project, our query is:

Copy Code code as follows:

Select P.code,choose (p.status, ' plan ', ' Exec ', ' Complete ', ' Abort ', ' Fail ')
From PROJECT P

The Chosse function has several drawbacks compared to case, 1 is not supported 0 and negative, so if the value of the enumeration is 0 then there is no way to show, 2 is the enumeration value must be continuous and relatively small, can not use 100, 200 equivalent, then if you use choose to write dead. There is no default value, when the case is used, if there is a mismatch there is also an else value can be displayed, and the use of choose if there is no match, then the null value. So personally I think 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 month and the day as an argument, returning the object of the specified data type, which is the equivalent of the CONVERT function. The overall use is not much, here is not much introduction.

The enhancement of over clause and some new analytic functions.
Before the over clause is used for rank,row_number and other ranking functions, now over clause has been greatly enhanced, you can apply over clause to aggregate functions, but also add some analytic functions.

For example, I have a project and Customer table, a customer for multiple projects, now need to know the customer's information and each customer's latest project code, this if not previously difficult to implement, now we have an analytic function, you can use First_value or Last_value again with the over clause, Get the results we want:

Copy 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