t-sql

讓應用程式溝通的主要語言

T-SQL 即 Transact-SQL，是 SQL 在 Microsoft SQL Server 上的增強版，它是用來讓應用程序與 SQL Server 溝通的主要語言。T-SQL 提供標準 SQL 的 DDL 和 DML 功能，加上延伸的函數、系統預存程序以及程式設計結構(例如 IF 和 WHILE)讓程式設計更有彈性。

目錄

1新特色 2預存程序 3相關資訊 4預存描述

5系統資料注意函數 6資料型別

7陳述式 8執行還原 9命名交易 10數據分組

新特色

介紹 T-SQL 的新特色

Transact Structured Query Language 它是ANSI和ISO SQL 標準的Microsoft SQL Server方言或擴展，中文理解為：SQL SERVER專用標準結構化查詢語言增強版。在 Microsoft SQL Server 7.0 中 T-SQL 有著為數不少的新增功能，包括新的預存程序、系統資料表、函數、資料型別、陳述式以及現存陳述式中的選項。這些都被保留在 SQL Server 2000 中，因此我們在這裡要先回顧 (特別是在您還不很熟悉 SQL Server 7.0 中 T-SQL 的功能，這個溫習就很重要了)。因為有太多的新功能需要討論，在此我們僅為每個類別舉少數例子來加以介紹。

預存程序

系統預存程序

系統預存程序是 SQL Server 提供來執行管理與其他工作，包括了系統資料表更新以及從系統資料表中檢索資料等。系統預存程序是和 SQL Server 一起安裝，名稱以 sp_ (系統預存程序) 或 xp_ (延伸的預存程序)開頭。這些預存程序儲存在 master 資料庫中，許可權歸系統管理者所有。不過，許多程序可以從使用者自訂的資料庫執行，用來在特定資料庫中從系統資料表檢索資訊。當您執行一個系統預存程序，它是在目前資料庫的系統資料表中展開作業。

相關資訊

關於這類型程序的更多相關資訊，請參考《線上叢書》中「延伸預存程序」主題。

在 SQL Server 7 中新增了許多系統預存程序，如今在 SQL Server 2000 中同樣可以利用。表13-1列出了一些可能對您有所幫助的系統預存程序。

表13-1 Procedures Introduced....

預存描述

系統預存程序描述

sp_cycle_errorlog 關閉目前的錯誤日誌檔案，重新命名為errorlog.1(以及，如果需要的話，將舊的errorlog.1重新命名為errorlog.2，依此類推)，以及開始一個新的錯誤日誌檔案。

sp_helpfile 傳回與目前資料庫相關之檔案的名稱與屬性。

sp_helpfilegroup 傳回與目前資料庫相關之檔案群組的名稱與屬性。

sp_helprole 傳回與目前資料庫相關中角色的資訊。

sp_help_alert 報告伺服器定義的警示資訊。

sp_start_job 指示SQL Server代理程式開始執行一個工作。

並不是只有這些預存程序提供立即的資訊，不過它們在使用您的使用者資料庫時會相當有用。例如，當執行 T-SQL 指令碼並將輸出結果儲存到一個檔案時，提供使用者資訊的程序就會相當有幫助。您可以將 sp_helpfile、sp_helpfilegroup以及 sp_helpdb 的執行結果儲存在特定的資料庫中，畢竟當您需要重建資料庫時有必要了解檔案、檔案群組、資料庫選項在起初建立時的原始設定。在 SQL Server 7.0 新增並保留 SQL Server 2000 中的系統預存程序清單，可以在《線上叢書》「Transact-SQL新增與加強的功能」這個主題里找到。

系統資料

系統資料表用來儲存 SQL Server 中所有資料庫的設定資訊，以及物件、使用者和使用者存取許可權的定義。每個使用者資料庫有各自的系統資料表，其中保存資料庫的資訊。只有在 master 資料庫的系統資料表才握有伺服器層級的設定資訊。您應該利用系統預存程序來存取系統資料表而不是直接存取系統資料表。關於新的 SQL Server 7.0 新增的系統資料表清單可在《線上叢書》里的「Transact-SQL新增與加強的功能」這個主題里找到。底下列出一些有趣的新系統資料表：

backupfile這個資料表存放在 msdb 資料庫中。它記錄了所有記錄檔備份或資料庫檔案備份的相關資訊。這些資訊包括檔案識別碼、檔案歸屬的檔案群組，以及檔案存在的實體磁碟或資料分割的名稱。

restorehistory這個資料表存放在 msdb 資料庫中。它包含對應於各還原作業相關資訊的資料列，不論是檔案的還原或是資料庫的還原。這些資訊包括還原的日期與時間、目的資料庫、資料回復的時間點以及還原的類型。

sysfiles此資料表為一虛擬資料表，亦即無法直接更新或修改。它包含了每個資料庫檔案的相關資訊，諸如實體裝置及邏輯的檔案名稱、檔案大小及最大檔案大小以及資料庫增長大小（如果資料庫有所增長的話）。

注意

請永遠使用系統預存程序來存取系統資料表，系統預存程序提供一層隔離以確保您不會更動到不可更改的資料。如果您以手動方式存取系統資料表，可能會有變更重要系統資訊的風險。

函數

SQL Server 內建的函數提供了一個快速、簡單的方法來完成某些特定的工作。SQL Server 7.0 新增了幾個有用的函數，並且也包含在 SQL Server 2000 中。了解哪些函數是可用的，SQL Server 應用程式的設計工作可以變得稍為簡單。在《線上叢書》中的「Transact-SQL 新增與加強的功能」這個主題可以找到新增函數的完整清單。此處僅介紹一些對您可能很有幫助的函數：

NEWID 建立一個 uniqueidentifier 資料型別的全域唯一識別項（GUID）。您需要使用這個函數將值分派給該資料型別的資料行，使用方式為 NEWID()。（此函數不需引數）

YEAR 傳回指定日期年份部分的完整整數。使用方式為 YEAR(date)。例如：以下陳述式 SELECT YEAR('07/11/01') 將會傳回值 2007。

MONTH 傳回指定日期月份部分的整數。使用方式為 MONTH(date)。例如：以下陳述式 SELECT MONTH('07/11/01') 將會傳回值11。

DAY 傳回指定日期日期部分的整數。使用方式為 DAY(date)。例如：以下陳述式 SELECT DAY('07/11/01') 將會傳回值 1。

FILE_NAME 傳回檔案識別碼（ID）的邏輯檔案名稱。使用方式為 FILE_NAME (file_id_number)。例如：陳述式 SELECT FILE_NAME(4) 檔案識別碼為4的檔案邏輯名稱。如果資料庫中找不到有此識別碼的檔案，則傳回 NULL。

資料型別

在 SQL Server 7 中新增了幾種新的資料型別，一些已經有的資料型別的尺寸也有延伸。此外，SQL Server 2000 新增了三種資料型別，這些資料別多數在第10章已經討論過。此處僅列出 SQL Server 7 有改變，並且也包含在 SQL Server 2000 中的資料型別：

資料指標變數新增了一個 cursor 資料型別。關於指標的相關訊息，請參考《線上叢書》里「Cursors」這個主題。

新增了三種新的 Unicode 資料型別－nchar、nvarchar 以及 ntext。每一個 Unicode 字元使用兩個位元組，並支援所有國際字元。

新增了一種 uniqueidentifier 資料型別，用於儲存通用唯一識別碼（GUID）。

字元資料和二進位字串的最大長度擴展到8,000位元組。這一長度適用於 char、varchar、binary 以及 varbinary 等型別。

SQL Server 2000 則新增了下列資料型別：

bigint儲存8個位元組長度的整數。

sql_variant允許不同資料型別的值儲存於同一個資料行。此型別的資料行儲存資料本身的值並且加以描述－它的基本型別、小數位數、精確性、最大大小以及定序（collation）。

table運作方式與暫存資料表類似；其宣告包含資料行清單與資料型別。此資料型別可用來定義一個區域變數或是一個使用者自訂函數的傳回值。

陳述式

SQL Server 7.0 包括了許多新的 T-SQL 陳述式和已經存在陳述式的新選項。同樣的，這些也保留在S QL 2000 之中。這些陳述式對應 SQL Server 7.0 的一些新功能。例如，ALTER DATABASE 陳述式包含了下列一些針對檔案與檔案群組的新選項：MODIFY FILE、ADD FILEGROUP、MODIFY FILEGROUP、REMOVE FILE 以及 REMOVE FILEGROUP。特別是檔案群組，新的 DBCC CHECKFILEGROUP 陳述式會檢查檔案群組中所有資料表的分配和結構的完整性。

SQL Server 7.0 與 SQL Server 2000 包括兩個額外的 DBCC 陳述式、DBCC SHRINKFILE 與 DBCC SHRINKDATABASE。前者能縮減資料檔案的大小，後者可縮減資料庫的所有資料檔案並釋放未使用的磁碟空間。

SQL Server 7 與 SQL Server 2000 支援一種改進的備份和還原結構。新的BACKUP 陳述式允許全部或部份的資料庫備份和記錄檔備份。新的 RESTORE 陳述式允許使用全部或部份資料庫備份和記錄檔備份來還原。這些代替了 SQL Server 較早版本中的 DUMP 和 LOAD 陳述式。關於 SQL Server 7.0 與 SQL Server 2000 新增陳述式與選項的完整清單，請見《線上叢書》里「Transact-SQL 新增與加強的功能」這個主題。

執行還原

使用 T-SQL 執行還原

RESTORE T-SQL 命令和 BACKUP 命令類似（第 32 章中曾經討論過）。如同BACKUP 命令，第一次使用時可能相當困難，但是一些 DBA 還是喜歡將他們的管理程序放置到 SQL 命令檔中，以便於重複執行這些功能。和 BACKUP 命令一樣，RESTORE 命令提供了比 Enterprise Manager 更多的附加選項。

在本節中，我們將討論 RESTORE 命令的語法以及這個命令提供的多種選項。RESTORE 命令有兩種形式顯示如下：

RESTORE DATABASE 還原完整的資料庫、檔案或檔案群組。

RESTORE LOG 還原交易記錄檔。

如您所見，所用的命令要依照執行還原操作的類型而定。因為這些命令共用大部分的選項，所以我們稍候將在一個清單中討論這兩種還原類型（資料庫和記錄檔）的所有選項。

RESTORE 陳述式

執行完整資料庫還原時 RESTORE 陳述式的語法如下：

RESTORE DATABASE database_name

[ FROM backup_device ]

[ WITH options ]

這個陳述式只要求資料庫的檔案名稱和備份所在的位置。

執行檔案和檔案群組還原時陳述式的語法如下：

RESTORE DATABASE database_name

[ FILE = file_name ]

[ FILEGROUP = filegroup_name ]

[ FROM backup_device ]

[ WITH options ]

這個陳述式只要求資料庫名稱、檔案名稱或檔案群組名稱和備份所在的位置。

執行交易記錄還原時陳述式的語法如下：

RESTORE LOG database_name

[ FROM backup_device ]

[ WITH options ]

就如同其他的命令，database_name 是還原將要執行的資料庫名稱。backup_device 參數可以是邏輯備份裝置名稱或實體裝置的名稱。如果要指定一個實體裝置，必須限定它的裝置類型－也就是說，裝置名稱前面必須加上 DISK=、TAPE= 或 PIPE=。可以指定一個或多個裝置，中間以逗號隔開。

如果沒有提供 FORM 子句，還原將無法進行，而且仍然會執行回復（除非執行NORECOVERY 選項）。這項技術可以用來將資料庫設定為回復模式，而不還原其他附加資料。例如，可以執行多個差異還原操作，然後執行 RESTORE 陳述式，不需要 FORM 子句來將資料庫設定為回復模式，就能夠啟動回復過程。

RESTRICTED_USER 設定新還原資料庫的安全性，只有 db_owner、dbcreater 和 sysadmin 角色成員才能存取該資料庫。

FILE = file_number 如果在儲存媒體中有多個備份集，可用來識別所使用的備份集。例如設定該數值為2，則使用磁帶上的第二個備份集。

PASSWORD = password 指定儲存備份的密碼。

MEDIANAME =media_name 指定媒體的名稱。

MEDIAPASSWORD = password 指定儲存媒體的密碼。

MOVE 'logical_file_name'

TO 'OS_file_name'

更改還原檔案的位置，例如：MOVE 'Northwind' TO 'D:\data\Northwind.mdf'。如果舊的磁碟無法使用，而需要還原到新的磁碟上時，可以使用這個選項。

NORECOVERY |RECOVERY |

STANDBY = undo_file

NORECOVERY 指定在還原后，交易將無法退回或重新進行。如果將還原其他的備份（差異式備份或交易記錄檔備份），則需要用到這個選項。REVOVERY 為預設選項，它指定執行回復操作，並退回每個沒有提交的變更。STANDBY 指定在還原需要還原情況下，建立還原（undo）檔案。

KEEP_REPLICATION 指定當資料庫還原時複製設定要保存下來。

NOUNLOAD | UNLOAD NOUNLOAD 指定在還原完成後不卸載儲存媒體（例如，覆寫備份磁帶並彈出）。UNLOAD 為預設選項，指定在還原完成後卸載儲存媒體。

REPLACE 指出即使資料檔案已經存在，SQL Server 仍將還原這些檔案。現存的檔案將被刪除和覆寫。如果沒有指定 REPLACE，那麼 SQL Server 將會核對 database_name 是否已經存在。如果已經存在，那麼將終止還原操作。這個安全特性幫助避免無意地將還原覆寫正在工作的資料庫。

RESTART 指定 SQL Server 在還原操作被中斷後重新啟動還原操作。

STATS [ = percentage ] 在還原操作完成了指定的百分率后顯示訊息。如果需要監控操作的過程，這個選項將會很有用。

PARTIAL 指定執行部份還原。

STOPAT = date_time

(log restore only)

指定資料庫回復到 data_time 指定時刻的資料狀態（只對記錄檔還原有效）。

STOPATMARK = 'mark' 指定還原操作進行到標記處。

STOPBEFOREMARK = 'mark' 指定還原操作進行到標記之前。

命名交易

命名交易（Named transactions）是 SQL Server 2000 中的新功能。它們是使用命令 BEGIN TRANSACTION ... WITH MARK 的 mark_name 選項建立的，特點是可以使用 RESTORE 命令中的 STOPATMARK 和 STOPBEFOREMARK 功能。

真實世界使用RESTORE

這裡有幾個使用 RESTORE T-SQL 命令的例子。

這個陳述式還原 Example 資料庫的資料檔：

RESTORE DATABASE Example

FROM Backup_Dev_1, Backup_Dev_2

WITH

NORECOVERY,

STATS = 5

GO

這個陳述式還原 Example 資料庫的交易記錄檔：

RESTORE LOG Example

FROM Backup_Dev_3, Backup_Dev_4

WITH

NORECOVERY,

STATS = 5,

UNLOAD

GO

和還原的結果一樣，輸出將顯示操作完成的百分率。將通知還原了多少頁面、還原花了多少時間，以及還原執行的速度（MB/sec）。

現在可用以下的命令回復資料庫：

RESTORE LOG Example

WITH RECOVERY

GO

您將再次看到關於還原操作的統計。

數據分組

1.選前幾條數據

select top 10 orderid,amt=unitprice*quantity

from ordredetails

order by amt desc

2.利用group by 子句對select命令所選數據分組。分組后可以顯示出來

group by是除了不含匯總函數（sum,avg,count,min,max等以外的列)

select prod_id,sum(qty*unit_price) tot_amt

from sale_item

group by prod_id

order by tot_amt desc

3.group by 子句還可以加 with cube語句，加上with cube語句，其返回的數據除了和沒有with cube語句一樣外，另外它會得到一些加總列。

select sup_id,prod_id ,sum(qty*unit_price),sum(qty) tot_amt

from sale_item

groupby sup_id,prod_id with cube

將有對應每個sup_id的加總列，每個prod_id 的加總列，和null，null全部的加總列

4.with cube語句換成with rollup ,則只返回最高層次的group列（最靠近group by的列的加總）

上面的例子，只返回sup_id的加總列，每個sup_id後有個加總列

5.使用grouping區分空值

cube操作生成的空值null帶來一個問題，即如何區分cube操作生成的null值和實際數據中返回的null值。這個問題，可以用grouping函數解決。如果列中的值來自事實數據，則grouping函數返回0，如果列中的值是cube操作所生成的null，則返回1。在cube操作中，所生成的null代表全體值。可將select語句寫成使用grouping函數將null替換為字元串all。

因為事實數據中的null表名數據值未知，所以select語句還可以用字元串unknow替代來自事實數據的null。

select productid,grouping(productid),orderid,grouping(orderid),sum(quantity)

from order

group by productid,orderid

with cube

order by productid,orderid

6.group by 子句經常會和having子句搭配，用來找出每個組別中滿足指定條件的數據，以下命令由產品文件中找出代碼重複者，也就是同一型產品有兩家以上的供應商。

select prod_id,count(*) from stock

group by prod_id

having count(*)>1

order by prod_id

實踐

創建一個表的時候，必須決定欄位定義所要使用的數據類型。數據類型定義了可以存儲在一個欄位中的數據種類。DBA可以使用數據類型來定義變數和存儲過程的輸入和輸出參數。你必須為每個欄位或變數選擇一個數據類型以適配於存儲在相應欄位或變數中的數據。另外，還需要考慮存儲需求並選擇高效率存儲的數據類型。舉個例子，想要存儲介於0到255的正數時通常要用tinyint替代smallint，int或bigint。這是因為tinyint是一個固定的1位元組欄位，而smallint為2位元組，int為4位元組還有bigint為一個8位元組的固定欄位。

選擇正確的數據類型還可以改善數據完整性。例如，如果為一個日期欄位使用datetime數據類型，那麼只有日期才能存儲在此欄位中。然而，如果為此欄位使用字元或數字數據類型，那麼最終的結果就可以在此欄位中存儲任何字元和數字類型的數據值，而它們並不代表一個日期。

最後，選擇正確的數據類型會帶來正確的執行計劃，從而改善資料庫性能。

目錄