SQL Server 總結複習 （二）

1. 次序函數與PARTITION BY
複製代碼 代碼如下:--所有資料
SELECT * FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid
WHERE scorename = '語文'
-------------------------------------------
--ROW_NUMBER() 的使用 產生列從1開始依次增加
-------------------------------------------
SELECT ROW_NUMBER() OVER (ORDER BY B.SCORE DESC) AS ROWNUMBER ,A.NAME, B.SCORE, a.Id
FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid
WHERE scorename = '語文'
--也可以在後面再加一個order by，則表示前面產生後的全部列又被以最後的列重新排列（排名列值不變）
SELECT ROW_NUMBER() OVER (ORDER BY B.SCORE DESC) AS ROWNUMBER ,A.NAME, B.SCORE, a.Id
FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid
WHERE scorename = '語文' ORDER BY a.Id
--要在分組統計後使用次序函數，則先進行分組，用cte或巢狀查詢表整出結果集，再用row_number函數處理
WITH b AS
(
SELECT stuid, SUM(score) AS score FROM ScoreTB GROUP BY stuid
)
SELECT * ,ROW_NUMBER() OVER (ORDER BY b.score desc) AS rownumer
FROM dbo.student AS a INNER JOIN b ON a.id = b.stuid
----------------------------------------------------------------------------
--RANK() 用法與ROW_NUMER函數想同，只是在出現order by同級時，排名會設定成一樣，而下一個會根據之前的記錄數產生序號
--例如前面三個是一樣的，那麼都是1，下一個則是4，樣本略
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--DENSE_RANK() 密集排名 用法與ROW_NUMER、RANK函數相同，只是在產生序號時是連續的，而rank函數產生的序號有可能不連續
--例如前面三個是一樣的，那麼都是1，下一個則是2，樣本略
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--ntile函數可以對序號進行分組處理。這就相當於將查詢出來的記錄集放到指定長度的數組中，每一個數組元素存放一定數量的記錄。
--為每條記錄產生的序號就是這條記錄所有的數組元素的索引（從1開始）。也可以將每一個分配記錄的數組元素稱為“桶”。
--它有一個參數，用來指定桶數，例如
----------------------------------------------------------------------------
SELECT ntile(2) OVER (ORDER BY B.SCORE DESC) AS GROUPID ,A.NAME, ISNULL(B.SCORE,0) SCORE, a.Id
FROM dbo.student AS a LEFT JOIN dbo.ScoreTB AS b ON a.Id = b.stuid AND scorename = '語文'
--------------------------------------------------------------------------
--PARTITION BY 類似於向次序函數應用一個group by，分組後對每一個組單獨排名
--------------------------------------------------------------------------
--統計各個學科的排名依次為：
SELECT RANK() OVER (PARTITION BY b.scorename ORDER BY B.SCORE DESC) AS ROWNUMBER,b.scorename,
b.score, A.NAME, a.Id FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid ORDER BY SCORENAME

2. TOP 新用法 複製代碼 代碼如下:DECLARE @num INT = 101
SELECT TOP (@num) * FROM Student ORDER BY Id --必須用括弧括起來
SELECT TOP (@num) percent * FROM Student ORDER BY Id --只接受float並且1-100之間的數，如果傳入其他則會報錯

3. group by all 欄位 / group by 欄位
前者有點像left join ,right join的感覺，兩者的主要區別體現在有where條件被過濾的彙總函式，會重新抓取出來放入查詢的資料表中，只是彙總函式會根據傳回值的類型用預設值0或者NULL來代替彙總函式的傳回值。
當然從效率上來說，後者優於前者，就像inner join 優於left join一樣
4. count(*)/count(0) 與 count(欄位)
如果查詢出來的欄位中沒有NULL值，那麼倆種查詢條件無任何區別，如果有NULL，後者統計出來的記錄則是 總記錄數 - NULL記錄數
從效能上來說，前者高於後者，因為後者會漸進式掃描欄位中是否有NULL值，有NULL則不加以統計，減少了邏輯讀的開銷，從而效能達到提升
5. top n With ties 的用法
詳見 http://www.cnblogs.com/skynet/archive/2010/03/29/1700055.html
舉個例子
select top 1 with ties * from student order by score desc
等價於
select * from student where score=(select top 1 score from student order by score desc)
6. Apply運算子
複製代碼 代碼如下:View Code
--準備資料
CREATE TABLE [dbo].[Student](
[Id] [int] NULL,
[Name] [varchar](50) NULL
)
go
INSERT INTO dbo.Student VALUES (1, '張三')
INSERT INTO dbo.Student VALUES (2, '李斯')
INSERT INTO dbo.Student VALUES (3, '王五')
INSERT INTO dbo.Student VALUES (4, '神人')
go
CREATE TABLE [dbo].[scoretb](
[stuId] [int] NULL,
[scorename] [varchar](50) NULL,
[score] INT NULL
)
go
INSERT INTO [scoretb] VALUES (1,'語文',22)
INSERT INTO [scoretb] VALUES (1,'數學',32)
INSERT INTO [scoretb] VALUES (1,'外語',42)
INSERT INTO [scoretb] VALUES (2,'語文',52)
INSERT INTO [scoretb] VALUES (2,'數學',62)
INSERT INTO [scoretb] VALUES (2,'外語',72)
INSERT INTO [scoretb] VALUES (3,'語文',82)
INSERT INTO [scoretb] VALUES (3,'數學',92)
INSERT INTO [scoretb] VALUES (3,'外語',72)
--建立資料表值函式
CREATE FUNCTION [dbo].[fGetScore](@stuid int)
RETURNS @score TABLE
(
[stuId] [int] NULL,
[scorename] [varchar](50) NULL,
[score] INT NULL
)
as
BEGIN
INSERT INTO @score
SELECT stuid,scorename,score FROM dbo.scoretb WHERE stuId = @stuid
RETURN;
END
GO
--開始使用
SELECT A.id,A.name,B.scorename,B.score FROM [Student] A
CROSS APPLY [dbo].[fGetScore](A.Id) B --相當於inner join效果
SELECT A.id,A.name,B.scorename,B.score FROM [Student] A
OUTER APPLY [dbo].[fGetScore](A.Id) B --相當於left join效果
--而不能這樣使用
--SELECT A.id,A.name,B.scorename,B.score FROM [Student] A
-- INNER JOIN [dbo].[fGetScore](A.Id) B ON A.Id = B.stuid
-- SELECT A.id,A.name,B.scorename,B.score FROM [Student] A
-- INNER JOIN (SELECT * FROM [dbo].[fGetScore](A.Id)) B ON A.Id = B.stuid

7. INTERSECT和EXCEPT運算子
EXCEPT 只包含excpet關鍵字左邊而且右邊的結果集中不存在的那些行 INTERSECT 只包含兩個結果集中都存在的那些行
往往EXISTS關鍵字可以代替上面的關鍵字，並且從效能中可以看到比他們更好，但EXCEPT/INTERSECT更便於閱讀和直觀。還是建議從效能更優入手。
8. 索引提高查詢效率的原理
索引與EXISTS運算子在處理方式上很像，它們都可以在找到匹配值後立即退出查詢運行，從而提高了查詢效能
9. 表變數與暫存資料表
主要區別： 1表變數不寫日誌，沒有統計資訊，頻繁更改不會造成預存程序重新編譯，不能建索引和統計資訊，但是可以建立主鍵，變通實現索引尋找，表變數不只是在記憶體中操作，資料量大的情況也會寫tempdb，即物理磁碟的IO操作。 2.交易回復對錶變數無效（原因沒有統計資訊）
一般來說，資料量大，臨時結果集需要和其他表二次關聯用暫存資料表 資料量小，單獨操作臨時結果集用表變數
10. 指令碼和批處理
Go不是一條T-SQL命令，他只能被編譯工具Management Studio, SQLCMD識別，如果用第三方工具，不一定支援GO命令。例如ADO.NET,ADO。
11. SQLCMD的運用
SQLCMD -Usa -Psa -Q "SELECT * FROM TESTDB.dbo.mytable"
SQLCMD -Usa -Psa -i testsql.sql 運行檔案裡的SQL語句
12. EXEC 使用說明
在執行過EXEC之後，可以使用類似@@ROWCOUNT這樣的變數查看影響行數；不能在EXEC的參數中，針對EXEC字串運行函數，例如cast(XX AS VARCHAR)，對於EXEC的參數，只能用字串相加，或者是整體的字串。
13. WAITFOR 的含義
WAITFOR TIME <'TIME'> 定時執行; WAITFOR DELAY <'TIME'> 順延強制
14. 預存程序 總結
1）用TRY/CATCH 替代 @@ERROR這種更科學，其一@@ERROR沒有TRA/CATCH直觀，其二遇到錯誤層級在11-19的錯誤，錯誤會使運行直接中斷，導致@@ERROR判斷錯誤與否無效。
2）使用RAISERROR 拋錯
WITH LOG，當嚴重層級大於等於19時，需要使用這個選項
WITH SETERROR，使其重寫@@ERROR值，方便外部調用
WITH NOWAIT 立刻將錯誤通知給用戶端
15. 遊標的複習
遊標主要部分包括：1）聲明 2）開啟 3）使用或導航 4）關閉 5）釋放
嵌套使用遊標樣本 複製代碼 代碼如下:DECLARE BillMsgCursor CURSOR FOR
SELECT TypeNo,TabDetailName FROM dbo.BillType
OPEN BillMsgCursor
DECLARE @TypeNo CHAR(5)
DECLARE @DetailName VARCHAR(50)
FETCH NEXT FROM BillMsgCursor INTO @TypeNo,@DetailName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DataFieldName VARCHAR(50)
DECLARE ColumnName CURSOR FOR
SELECT name FROM syscolumns WHERE id = OBJECT_ID(@DetailName)
OPEN ColumnName
FETCH NEXT FROM ColumnName INTO @DataFieldName
PRINT '單據編號:' + @TypeNo
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ListDetailDataFiled.Add('''+@DataFieldName+''');'
FETCH NEXT FROM ColumnName INTO @DataFieldName
END
CLOSE ColumnName
DEALLOCATE ColumnName
FETCH NEXT FROM BillMsgCursor INTO @TypeNo,@DetailName
END
CLOSE BillMsgCursor
DEALLOCATE BillMsgCursor

@@fetch_status值的意義：0 FETCH 語句成功；-1 FETCH 語句失敗或此行不在結果集中；-2 被提取的行不存在
FETCH [NEXT/PRIOR/FIRST/LAST] FROM ... INTO 針對遊標為SCROLL類型的
16. 遊標的分類
1）靜態資料指標（static）：相當於暫存資料表，會儲存在tempdb裡的私人表中，猶如快照表複製一份
a.一旦建立了遊標，它就與實際記錄相分離並不再維持任何鎖
b.遊標就是獨立的，不再以任何方式與未經處理資料相關聯
2）鍵集驅動的遊標（keyset）：需要在一定程度上感知對資料的修改，但不必瞭解最新發生的所有插入
a.表必須具有唯一索引
b.只有鍵集在tempdb中，而非整個資料集，對整個伺服器效能產生有利的影響
c.能感知到對己是鍵集一部分的行所做的修改（改刪），不能感知新增
3）動態資料指標（DYNAMIC）
a.完全動態，非常敏感，對底層資料做的所有事情都會影響，效能當然也是最差的
b.它們會帶來額外的並發性問題
c.每發出一次FETCH，都要重建遊標
d.可允許運行沒有唯一索引的表中，但弊端會造成SQLSERVER無法追蹤它在遊標的位置造成死迴圈，應避免這樣使用
4）快進遊標（FAST_FORWARD）
在許多情況下，FAST_FORWARD遊標會隱式轉換為其他遊標類型