SQL Server 2012 T_SQL新特性之一

系统函数

字符串函数

CONCAT 字符串连接函数

                SELECTCONCAT(String_Value1,String_Value2, String_Value3 [, String_ValueN])

FORMAT字符串格式化函数

DECLARE @d DATETIME = '01/01/2011';

SELECT FORMAT(@d, 'd', 'en-US') AS US_Result;

SELECT FORMAT(@d, 'd', 'fr-FR')  AS FR_Result;

SELECT FORMAT(@d, 'd', 'de-DE') AS DE_Result;

逻辑类函数

CHOOSE 按索引号选择值函数

SELECT CHOOSE( 0,'TRUE', 'FALSE', 'Unknown') AS Returns_Null;

SELECT CHOOSE( 1,'TRUE', 'FALSE', 'Unknown') AS Returns_First;

SELECT CHOOSE( 2,'TRUE', 'FALSE', 'Unknown') AS Returns_Second;

SELECT CHOOSE( 3,'TRUE', 'FALSE', 'Unknown') AS Returns_Third;

SELECT CHOOSE( 4,'TRUE', 'FALSE', 'Unknown') AS Result_NULL;

IFF 布尔运算函数

SELECT IIF(-1 < 1,'TRUE', 'FALSE' ) ASResult;

日期类函数

DATETIME2FROMPARTS 根据给定年月日显示日期的函数

SELECT DATEFROMPARTS(2010,12,31)AS Result;

SELECT DATETIME2FROMPARTS(2010,12,31,23,59,59,0,0)AS Result;

SELECT DATETIMEFROMPARTS(2010,12,31,23,59,59,0)AS Result;

SELECT DATETIMEOFFSETFROMPARTS(2010,12,31,14,23,23,0,12,0,7)AS Result;

SELECT SMALLDATETIMEFROMPARTS(2010,12,31,23,59)AS Result

SELECT TIMEFROMPARTS(23,59,59,0,0)AS Result;

EOMONTH 显示月末的函数

SELECT EOMONTH('20120301')LeapYearFebLastDay;

SELECT EOMONTH('20130301')NonLeapYearFebLastDay;

转换类函数

PARSE 和 CAST 相似，可以指定本地化语言转换

SELECT PARSE('13 December 2010'AS datetime2 USING 'EN-US')AS Result

SELECT CAST('13 December 2010'AS datetime2)AS Result

TRY_PARSE 转换不成功为NULL

-- No error

SELECT PARSE('100.000'AS INT)AS ValueInt;

SELECT TRY_PARSE('100.000'AS INT)AS ValueInt;

-- Error

SELECT PARSE('A100.000'AS INT)AS ValueInt;

SELECT TRY_PARSE('A100.000'AS INT)AS ValueInt;

 

TRY_CONVERT 转换不成功为NULL

-- No error

SELECT CONVERT(INT,'100') AS ValueInt;

SELECT TRY_CONVERT(INT,'100') AS ValueInt;

-- Error

SELECT CONVERT(INT,'A100.000') AS ValueInt;

SELECT TRY_CONVERT(INT,'A100.000') AS ValueInt;

聚合函数

FIRST_VALUE

SELECTName, ListPrice,

       FIRST_VALUE(Name)OVER (ORDERBY ListPrice ASC) AS LeastExpensive

FROM Production.Product

WHERE ProductSubcategoryID= 37;

LAST_VALUE

SELECT Name,ListPrice, ProductSubcategoryID,

       LAST_VALUE(Name)OVER (ORDERBY ListPrice ASC) AS LeastExpensive

FROM Production.Product

WHERE ProductSubcategoryID= 1;

LEAD

SELECTBusinessEntityID,YEAR(QuotaDate)AS SalesYear,SalesQuota AS CurrentQuota,

    LEAD(SalesQuota, 1,0)OVER (ORDERBY YEAR(QuotaDate))AS PreviousQuota

FROM Sales.SalesPersonQuotaHistory

WHERE BusinessEntityID= 275 and YEAR(QuotaDate)IN('2005','2006');

LAG

SELECT TerritoryName,BusinessEntityID,SalesYTD,

       LAG(SalesYTD, 1, 0)OVER (PARTITIONBY TerritoryName ORDER BY SalesYTDDESC) AS PrevRepSales

FROM Sales.vSalesPerson

WHERE TerritoryNameIN(N'Northwest',N'Canada')

ORDER BY TerritoryName;

 

T_SQL 新对象

1.    CREATE SEQUENCE

CREATE SEQUENCE [dbo].[SQ1]

 AS [tinyint]

 START WITH 1

INCREMENT BY 1

 MINVALUE 1

 MAXVALUE 3

 CYCLE

CACHE

GO

 SELECT NEXT VALUE FOR [dbo].[SQ1]

 

 

2.    THROW

 

BEGIN TRY

     SELECT 1/0

END TRY

BEGIN CATCH

     --THROW --50001,'自定义错误',1

     RAISERROR ('自定义错误',16,1)

END CATCH

 

3.    Fetch and Offset           

 

--SQL  Server 2012 

SELECT LOADID,LOADQT   

FROM LOAT 

ORDER BY SHPDAT,SHPTIM  

OFFSET 2 ROWS 

FETCH NEXT 2 ROWS ONLY; 

 

--SQL  Server 2008 R2  

SELECT LOADID,LOADQT   

FROM ( 

SELECT ROW_NUMBER() OVER(ORDER BY SHPDAT,SHPTIM) AS SEQ, LOADID,LOADQT  

FROM LOAT) AS TempTable 

WHERE SEQ > 2 and SEQ <= 4 

  

4.       RESULT SETS

Multiple Result sets  / Single Result set

CREATE PROCEDURE GetEmployeesWithMultipleResultsets

AS

BEGIN

SELECT '1' AS EmpId, 'Visual Studio' AS Name

 

SELECT '2' AS EmpId, 'SQL Server' AS Name

END

GO

 

EXEC GetEmployeesWithMultipleResultsets

GO

 

EXECUTE   GetEmployeesWithMultipleResultsets

WITH RESULT SETS

(

       (

       EmployeeId1 INT,

       EmployeeName1 VARCHAR(150)

       ),

       (

       EmployeeId2 INT NOT NULL,

       EmployeeFullName2 VARCHAR(150) NOT NULL

       )

)