SQL Server, new T-SQL features

The original: SQL Server-T SQL new feature

Sequence Sequence

SQL Server now implements the sequence as an object, and the example syntax for creating a sequence is as follows:

CREATE SEQUENCE DemosequenceSTART with by 1;

The method of using the sequence is expressed as follows:

SELECT VALUE  for Demosequence

The difference between a sequence and the previous seed column (identity) is obvious, the seed column is limited to the forefront, and the sequence is an object-level implementation that can be shared across multiple tables. This feature in the management software serial number generation, is a good start. Similar to the seed column, the sequence can also be reset, as in the following example

ALTER SEQUENCE  with 1;

The values of a sequence can use integer types, such as tinyint, smallint, int, bigint, decimal, or numeric type with a decimal precision of 0.

The sequence limit (limitation) has two, one is that the sequence does not support transactions, even if a rollback (rollback) operation is performed in the transaction, the sequence still returns the next element.

Second, the sequence does not support SQL Server replication (replication), and the sequence is not replicated to the subscribed SQL Server instance. If the default value of a table depends on a sequence, and the sequence is not replicable, this results in a scripting error for the subscribed SQL Server.

Data paging page page data

SQL Server has been improving the data paging method, SQL Server 2005 built-in Row_number function can be implemented, the example code is as follows

select  *from  ( Select  row_number () over  (order  by  CustomerID) as  sequencenumber, *from  Customers) as  temptablewhere  sequencenumber > and  SequenceNumber <= 

SQL Server 2012 has a more concise syntax, as shown in the example code below

SELECT * from CustomersORDERbyROWSFETCHNEXT  ROWSonly;

Sort by customer number, skip the previous 10 records and take the 10th record. This is like the SKIP.TAKE,LINQ syntax example in LINQ:

var customers=customerlist.skip (10). Take (10);

Exception Handling Exception Handling

SQL Server 2005 introduced a similar to. NET language exception handling mechanism into T-SQL code, please refer to the following example

BEGINTRYBEGIN TRANSACTION–StartTheTransaction    --Delete the Customer    DELETE  fromCustomersWHEREEmployeeID = ' cactu '--Commit the change    COMMIT TRANSACTIONENDTRYBEGINCatch--There is an error    IF @ @TRANCOUNT> 0ROLLBACK TRANSACTION    --Raise An error with the details of the exception    DECLARE@ErrMsg nvarchar (4000), @ErrSeverityint    SELECT@ErrMsg = Error_message (), @ErrSeverity = Error_severity ()RAISERROR(@ErrMsg, @ErrSeverity, 1)ENDCatch

If you catch an exception in a catch statement block, you can only refer to the RaiseError function to continue throwing the exception. The new version of SQL Server introduces the Throw keyword, which replaces the role of the RaiseError function. The reference code is as follows

begin  TRY begin      TRANSACTION  --Start the TRANSACTION  --Delete the Customer  delete  from  Customers where  EmployeeID = ' cactu ' --Commit the change  
   
    commit  
    transaction  
    end  TRY 
    begin  CATCH 
    --there is an error  
    rollback  
    transactio N  
    --Re throw the exception  Throw
    end  CATCH 
   

An advantage of the exception handling mechanism is the N-tier rollback (rollback), which throws an exception to the program, looking up the layer up until the code that handles the exception is found.

Stored procedure execution improvement execute Procedure Enhanced

In the old SQL Server version, to return the column information for a query statement, you can use the SET FMTONLY statement, which returns the result column instead of the actual data, refer to the following statement:

SET on; GO SELECT * from      dbo. GbitemGOSETOFF;

A stored procedure is a precompiled batch of statement blocks that can be precompiled to improve performance, the previous version of SQL Server application keywords ( WITH RECOMPILE) You can force recompilation of stored procedures to generate a new execution plan.) The new version of SQL Server improves the return information of the query results, and can be used to name the query results of the stored procedure. The following code re-defines the returned column information for the stored procedure:

EXEC Custorderdetail ' 2 'withRESULTsets(    varchar(),    varchar (+), varchar (+), varchar (+), varchar (+))                ;

The exec parameter with Results set can redefine the returned column name or type based on the actual return result of the stored procedure. Refer to the following SQL statement:

CREATE PROCEDUREDenali_withresultset asBEGIN        SELECT1 as No, ' Tsql ' Type, ' Withresultset ' asFeatureUNION  All       SELECT2 as No, ' Tsql ' Type, ' Throw ' asFeatureUNION  All       SELECT3 as No, ' Tsql ' Type, ' Offset ' asFeatureUNION  All       SELECT4 as No, ' Tsql ' Type, 'Sequence’ asFeatureENDGOEXECDenali_withresultset with RESULT sets(       (No int, Featuretypevarchar(+), FeatureNamevarchar(50)))

The code above demonstrates how to use the with result set to modify the return column name of a stored procedure. This feature is integrated with third-party tools, such as SSIS tasks, and the report will have some improvement.

Meta-Data functions Metadata function

The new version of SQL Server adds several stored procedures to get the metadata for the SQL Server object, such as the following SQL statement:

EXEC sp_describe_first_result_set @tsql =n' SELECT * from Gbitem '

It returns the metadata information for each column of the table Gbitem, such as the column name, whether it is nullable, the data type, the sort, and so on.

The following example shows how to apply the above mentioned functions to return the metadata for a stored procedure:

create  proc  Production.testprocas  select  Name, ProductID, Color from  Production.Product; select  Name, Safetystocklevel, sellstartdate from  production.product; go  select  * from  sys.dm_exec_describe_first_result_set ( ' Production.testproc ' , null , 0); 

This function can also return metadata information for multiple SQL batch queries, please refer to the example code below

select  * from  sys.dm_exec_describe_ First_result_set (n ' SELECT CustomerID, TerritoryID, accountnumber from Sales.Customer WHERE CustomerID = @CustomerID; SELECT * from Sales.SalesOrderHeader; ' , N ' @CustomerID int ' , 0) as< /span> A; go  

If a stored procedure wants to return multiple sets of records, in an older version of SQL Server, you can only get the recordset that was last returned. A new version of SQL Server has made some improvements, and you can specify the recordset to return.

create  proc  TestProc2 as  select  object_id, name from  sys.objects; select  name, schema_id, create_date from  sys.objects; go  select  * from  sys.dm_exec_describe_first_ Result_set_for_object (object_id ( ' TestProc2 ' ), 0); select  * from  sys.dm_exec_describe_first_result_set_for_ Object (object_id ( ' TestProc2 ' ), 1); go  

This feature brings a lot of convenience to the processing of the program, in order to return two result sets without having to define two duplicate stored procedures, but only to return the results differently.

SQL functions SQL function

New versions of SQL Server add a lot of functions, please refer to the article of Friends of the park SQL Server 2012 new built-in function attempt

The advent of these functions can bring convenience to SQL programming. However, I think since SQL Server 2005 introduced the CLR, implementing these functions is quite easy, directly to the. NET BCL layer of a simple package, do not know why after two important versions (SQL server 2008,sql server R2), Before adding these basic functions.

For more features of SQL Server, see here:

http://dattatreysindol.com/2012/07/30/sql-server-2012-transact-sql-enhancements-learning-resources/

SQL Server, new T-SQL features