SQL Server2012在軟體開發中的一些新特性

標籤：concat   bsp   last   3.3   ber   查詢語句   軟體開發   div   轉換   

官方給出了一大堆SQL2012相對於SQL2008R2的新特性，但是大多數對於普通開發人員來說都是浮雲，根本用不到，下面就說說一些對於開發人員來說比較有用的新特性。 一、在SQL Server中終於也看到了類似的對象，只是在使用的文法上有一點點不一樣。建立文法也是CREATE SEQUENCE，使用的時候需要使用NEXT VALUE FOR來取下一個值：

 

複製代碼代碼如下:
CREATE SEQUENCE [dbo].[SQ_1] 
 AS [bigint]
 START WITH 1
 INCREMENT BY 1;

 

SELECT NEXT VALUE FOR [SQ_1] AS FirstUse;

 

如果要插入一個值，那麼就是：

 

複製代碼代碼如下:
INSERT INTO t1(c1,c2)
VALUES (NEXT VALUE FOR SQ_1, ‘Test‘) ;

 

但是好像沒有提供獲得當前值的文法，難道必須取下一個值？

二、新的分頁查詢文法。
以前在SQL Server中分頁，最早是用top或者暫存資料表，後來出現了ROW_NUMBER函數實現分頁，現在最新的SQL2012可以在order by子句後跟offset和fetch來分頁，感覺有點像是LINQ的文法。比如查詢1W行之後的20條有效項目資訊，那麼ROW_NUMBER分頁查詢的SQL是：

 

複製代碼代碼如下:
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而是有了新的文法，那麼查詢語句就是：

 

SELECT *
FROM PROJECT p
where p.IS_DELETED=0
ORDER BY p.PROJECT_ID 
OFFSET 10001 ROWS
FETCH NEXT 20 ROWS ONLY;

 

顯然使用了新的文法後代碼看起來更簡潔，意思表達也更明確。從執行效率上來講，試了一下，是一樣的。

三、一些新的系統函數。
3.1相當於C#中三目運算子的IIF函數

這個函數和VBA中的IIF函數相同，判斷第一個參數的運算式是否為真，真則返回第二個參數，假則返回第三個參數。

有了這個函數很多時候我們可以不用再使用複雜的case when文法了。比如我們判斷項目的大小以顯示對應的字串，那麼老的寫法是：

 

複製代碼代碼如下:
select p.CODE,case when p.SIZE>100 then ‘Big‘ else ‘Small‘ end as SIZE_STRING
from PROJECT p
where SIZE is not null

 

現在，我們可以簡單的寫成：

 

複製代碼代碼如下:
select p.CODE,IIF(p.SIZE>100,‘Big‘,‘Small‘) as SIZE_STRING
from PROJECT p
where SIZE is not null

 

3.2不用判斷類型和NULL的字串串連CONCAT函數

SQL Server本來對字串的串連很簡單，直接使用“+”號，但是需要注意兩個問題，一是必須類型都是字串類型，如果是數字類型那麼會報語法錯誤，所以必須把數字類型轉換為字串。二是如果其中的某個值為null，那麼整個串連的結果就是一個null字串，所以還需要判斷null，所以本來只是一個連接字串的查詢就會寫的很複雜：

 

複製代碼代碼如下:
select p.PROJECT_ID, p.CODE+‘,‘+p.NAME+‘,‘+ISNULL(p.NICK_NAME,‘‘)+‘,‘+ISNULL(CONVERT(varchar(50),p.SIZE),‘‘)
from PROJECT p

 

現在使用CONCAT函數，直接忽略其中的類型，忽略對NULL的檢查，直接連接成一個非空的字串：

複製代碼代碼如下:
select p.PROJECT_ID,CONCAT( p.CODE,‘,‘,p.NAME,‘,‘,p.NICK_NAME,‘,‘,p.SIZE)
from PROJECT p

 

可以明顯感覺到簡潔了很多。

3.3轉換成字串時設定格式的FORMAT函數。

以前要把數字或者日期轉換成字串，可以使用CONVERT函數並帶人第三個整數類型的參數指定轉換的格式，不過這種方法太麻煩，整數參數不容易理解和記憶，而且也不靈活。現在的FORMAT函數相當於C#中的String.Format函數，在第二個參數中可以想要輸出的格式。

 

複製代碼代碼如下:
select p.PROJECT_ID,FORMAT(p.CREATED_TIME,‘yyyy-MM-dd‘),CONVERT(varchar(50),p.CREATED_TIME,112)
from PROJECT p

 

3.4讓枚舉顯示更方便的CHOOSE函數。

在程式中經常使用枚舉值，在資料庫中使用tinyint來儲存枚舉值，但是在查看時卻不是很容易理解枚舉值的含義，必須查看代碼看1對應什麼，2對應什麼才知道。在顯示的時候如果要顯示成字串，那麼就需要使用case when進行判斷。現在可以使用CHOOSE函數，讓枚舉轉換成字串變得很簡單。比如要顯示項目的狀態，那麼我們的查詢就是：

 

複製代碼代碼如下:
select p.CODE,CHOOSE( p.STATUS,‘Plan‘,‘Exec‘,‘Complete‘,‘Abort‘,‘Fail‘)
from PROJECT p

 

CHOSSE函數比case when有幾個缺點，1是不支援0和負數，所以如果枚舉的值是0那麼就沒辦法顯示，2是枚舉值必須連續而且比較小，不能使用100、200等值，那要是用CHOOSE那得寫死人了。沒有default值，使用case when的時候，如果不匹配還有個else值可以顯示，而使用CHOOSE後如果沒有匹配的，那麼就是NULL值。所以個人覺得這個函數的使用面非常

3.5各種日期時間函數。

除了一個EOMONTH函數是返回給定日期的最後一天外，其他的新函數，都是把年月日作為參數傳進去，返回指定資料類型的對象，相當於就是CONVERT函數的變形。總體使用不多，在此不多介紹。

四、OVER子句的增強和新增一些分析函數。
之前OVER子句是用於RANK，ROW_NUMBER等次序函數，現在OVER子句得到了大大的增強， 可以將OVER子句應用到彙總函式中，也增加了一些分析函數。

比如我有一個項目和客戶表，一個客戶對於多重專案，現在需要知道客戶的資訊和每個客戶的最新項目Code，這個要是以前還不好實現，現在我們有了分析函數，可以使用FIRST_VALUE或者LAST_VALUE再配合OVER子句，得到我們想要的結果：

 

複製代碼代碼如下:
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

 

SQL Server2012在軟體開發中的一些新特性