SQL Server 2000與2005歸納

自動編號：設欄位型別為：int ，然後列屬性中 （是標識）選是，標識種子選1。

用varchar(max)代替text。varchar的最大長度為8000，但是varchar(max)則可以儲存多達2G的資料，因此其作用相當 於SQL 2000中的text。但是微軟可能會後續的SQL Server版本中移除text型別，從現在就應該用varchar(max) 來代替text。

用nvarchar(max)代替ntext，用binary(max)代替image.

為XML資料選擇xml型別。在SQL Server 2005中，為XML資料新增了相應的資料型別，因此儲存XML資料的列不需要用 varchar(max)或nvarchar(max)，而應當用xml資料型別，以利用T-SQL中專門針對xml資料列的新命令，以及針對xml列的 索引。

易混淆的資料型別
(1)char、varchar、text和nchar、nvarchar、ntext
char和varchar的長度都在1到8000之間，它們的區別在於char是定長字元資料，而varchar是變長字元資料。所謂定長就是長度固定 的，當輸入的資料長度沒有達到指定的長度時將自動以英文空格在其後面填充，使長度達到相應的長度；而變長字元資料則不會以空格填充。 text儲存可變長度的非Unicode資料，最大長度為2^31-1(2,147,483,647)個字元。
後面三種資料型別和前面的相比，從名稱上看只是多了個字母"n"，它表示儲存的是Unicode資料型別的字元。寫過程式的朋友對Unicode應該很了 解。字元中，英文字元只需要一個位元組儲存就足夠了，但漢字眾多，需要兩個位元組儲存，英文與漢字同時存在時容易造成混亂，Unicode字符集就是為了解決 字符集這種不相容的問題而產生的，它所有的字元都用兩個位元組表示，即英文字元也是用兩個位元組表示。nchar、nvarchar的長度是在1到4000之 間。和char、varchar比較：nchar、nvarchar則最多儲存4000個字元，不論是英文還是漢字；而char、varchar最多能存 儲8000個英文，4000個漢字。可以看出使用nchar、nvarchar資料型別時不用擔心輸入的字元是英文還是漢字，較為方便，但在儲存英文時數 量上有些損失。

(2)datetime和smalldatetime
datetime：從1753年1月1日到9999年12月31日的日期和時間資料，精確到百分之三秒。
smalldatetime：從1900年1月1日到2079年6月6日的日期和時間資料，精確到分鐘。

(3)bitint、int、smallint、tinyint和bit
bigint：從-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型資料。
int：從-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型資料。
smallint：從-2^15(-32,768)到2^15-1(32,767)的整數資料。
tinyint：從0到255的整數資料。
bit：1或0的整數資料。

(4)decimal和numeric
這兩種資料型別是等效的。都有兩個引數：p（精度）和s（小數位數）。p指定小數點左邊和右邊可以儲存的十進位制數字的最大個數，p必須是從 1到38之間的值。s指定小數點右邊可以儲存的十進位制數字的最大個數，s必須是從0到p之間的值，預設小數位數是0。

(5)float和real
float：從-1.79^308到1.79^308之間的浮點數字資料。
real：從-3.40^38到3.40^38之間的浮點數字資料。在SQL Server中，real的同義詞為float(24)。[@more@]關於SQL SERVER 2005 與SQL SERVER 2000比較

這個題目太大了,這裡只能大體介紹一下，細節方面還需大家共同研究！希望能使大家對SQL SERVER 2005 快速入手,另外sql server 2005 的介面風格有越來越像 .net了,趕快安裝一個感受一下吧!進去之後不要再去找查詢分析器了,它和企業管理器都被整合在”Microsoft SQL Server Management studio”了,這個工具佔記憶體較大80M多(再加上sql server 的例項程式80M,就160M了),而以前的企業管理器只需20M左右,加上sql server 的例項程式10M多一共才30M多.有得必有失~!

一、資料庫設計方面
1、欄位型別。
SQL Server 2005引入了一系列 新的被稱為MAX的資料型別。這是VARCHAR，NVARCHAR和VARBINARY型別的擴充套件，這幾種型別 以前被限制在8000位元組以下。MAX可以容納高達2GB的資料，與TEXT和IMAGE一樣。
可以使用字串函式對CLOB型別進行操作。但是這就引發了對varchar和char效率討論的老問題。到底如何分配varchar的資料，是否會出現大規模的碎片？是否碎片會引發效率問題？這都是需要進一步探討的東西。
資料型別
Sql server2000 Sql server2005
text 最大2GB varchar(max) 最大2GB(相當於oracle中的CLOB型別)
ntext 最大2GB nvarchar(max) 最大2GB
image 最大2GB varbinary(max) 最大2GB（代替image也讓SQL Server的欄位型別更加簡潔統一）
無 XML XML 資料被作為二進位制大型物件 (BLOB) 儲存於內部，可有效地進行重新分析和壓縮
其它資料型別保持不變。
2、外來鍵的級聯更能擴充套件
新版本中外來鍵級聯加入了SET NULL 和 SET DEFAULT 屬性，能夠提供能好的級聯設定。(有點像oracle了)語法如下(引用sql server 2005 help來說明):
CREATE TABLE 和 ALTER TABLE 語句的 REFERENCES 子句支援 ON DELETE 和 ON UPDATE 子句：
? [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
? [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
如果沒有指定 ON DELETE 或 ON UPDATE，則預設為 NO ACTION。
NO ACTION
指定如果試圖刪除/修改某一行，而該行的鍵被其他表的現有行中的外來鍵所引用，則產生錯誤並回滾 DELETE/UPDATE語句。
CASCADE、SET NULL 和 SET DEFAULT
允許透過刪除或更新鍵值來影響指定具有外來鍵關係的表，這些外來鍵關係可追溯到在其中進行修改的表。如果為目標表也定義了級聯引用操作，那麼指定的級聯操作也將應用於刪除或更新的那些行。不能為具有 timestamp 列的外來鍵或主鍵指定 CASCADE。
ON DELETE CASCADE
指定如果試圖刪除某一行，而該行的鍵被其他表的現有行中的外來鍵所引用，則也將刪除所有包含那些外來鍵的行。
ON UPDATE CASCADE
指定如果試圖更新某一行中的鍵值，而該行的鍵值被其他表的現有行中的外來鍵所引用，則組成外來鍵的所有值也將更新到為該鍵指定的新值。 (如果 timestamp 列是外來鍵或被引用鍵的一部分，則不能指定 CASCADE。 )
ON DELETE SET NULL
指定如果試圖刪除某一行，而該行的鍵被其他表的現有行中的外來鍵所引用，則組成被引用行中的外來鍵的所有值將被設定為 NULL。目標表的所有外來鍵列必須可為空值，此約束才可執行。
ON DELETE SET NULL
指定如果試圖更新某一行，而該行的鍵被其他表的現有行中的外來鍵所引用，則組成被引用行中的外來鍵的所有值將被設定為 NULL。目標表的所有外來鍵列必須可為空值，此約束才可執行。
ON DELETE SET DEFAULT
指定如果試圖刪除某一行，而該行的鍵被其他表的現有行中的外來鍵所引用，則組成被引用行中的外來鍵的所有值將被設定為它們的預設值。目標表的所有外來鍵列必須具 有預設值定義，此約束才可執行。如果某個列可為空值，並且未設定顯式的預設值，則會使用 NULL 作為該列的隱式預設值。因 ON DELETE SET DEFAULT 而設定的任何非空值在主表中必須有對應的值，才能維護外來鍵約束的有效性。
ON UPDATE SET DEFAULT
指定如果試圖更新某一行，而該行的鍵被其他表的現有行中的外來鍵所引用，則組成被引用行中的外來鍵的所有值將被設定為它們的預設值。目標表的所有外來鍵列必須具 有預設值定義，此約束才可執行。如果某個列可為空值，並且未設定顯式的預設值，則會使用 NULL 作為該列的隱式預設值。因 ON UPDATE SET DEFAULT 而設定的任何非空值在主表中必須有對應的值，才能維護外來鍵約束的有效性。


3、索引附加欄位
即在索引中儲存一些常用欄位以提高查詢速度,這是一個不錯的新特性。雖然索引的附加欄位沒有索引鍵值效率高，但是相對對映到資料表中效率還是提高了很多。在實驗環境中會比對映到表中提高30%左右的效率。例:
CREATE INDEX ix_CustomerPostalcode
On Sales.Customer(PostalCode)
INCLUDE (AddressLine1,AddressLine2,City)
索引會提高查詢(select)語句的效能,但建有大量索引會影響 INSERT、UPDATE 和 DELETE 語句的效能，因為在表中的資料更改時，所有索引都須進行適當的調整。
4、計算欄位的持久化
原來的計算欄位其實和虛擬欄位很像。只是管理方面好了而已，效能方面提高不多。但是SQL2005提供了計算欄位的持久化，這就提高了查詢的效能，但是會加重insert和update的負擔。OLTP慎用。OLAP可以大規模使用。
使用 ORDER 排序和虛擬欄位 虛擬欄位完成的是類似 自增長 ID 的任務
select identity(int,1,1) ID ,hymc into #temp
from hybm
order by hymc
(注: 在ORACLE中，語句: select rownum from USERTABLE order by USERNAME; 得到的rownum還是沒有排過序時的ROWNUM，根本不是已經排過序的ROWNUM。也就是說，有沒有ORDER BY一個樣。)
5、分割槽表
分割槽表是個亮點！從分割槽表也能看出微軟要做大作強SQL Server的信心。資料很多，這裡不詳細說。但是重點了解的是：現在的SQL Server2005的表，都是預設為分割槽表的。因為它要支援滑動視窗的這個特性。這種特性對歷史資料和實時資料的處理是很有幫助的。但是需要注意的一 點，也是我使用過程中發現的一個問題。在建立function->schema->table後，如果在現有的分割槽表上建立沒有顯式宣告的聚 集索引時，分割槽表會自動變為非分割槽表。這一點很讓我納悶。如果你覺得我的非分割槽索引無法對起子分割槽，你可以提醒我一下呀！沒有任何的提醒，直接就變成了非 分割槽表。不知道這算不算一個bug。大家也可以試試。
分割槽表效率問題肯定是大家關心的問題。在我的試驗中，如果按照分割槽欄位進行的查詢（過濾）效率會高於未分割槽表的相同語句。但是如果按照非分割槽欄位進行查 詢，效率會低於未分割槽表的相同語句。但是隨著資料量的增大，這種成本差距會逐漸減小，趨於相等。（500萬數量級只相差10%左右）
6、CLR型別
微軟對CLR作了大篇幅的宣傳，這是因為資料庫產品終於融入.net體系中。最開始我們也是狂喜，感覺物件資料庫的一些概念可以實現了。但是作了些試驗， 發現使用CLR的儲存過程或函式在達到一定的閥值的時候，系統效能會呈指數級下滑！這是非常危險的！只使用幾個可能沒有問題，當一旦大規模使用會造成嚴重 的系統效能問題！
其實可以做一下類比，Oracle等資料庫產品老早就支援了java程式設計，而且提供了java池引數作為使用者配置介面。但是現在有哪些系統大批使用了java儲存過程？！連Oracle自己的應用都不用為什麼？！還不是效能有問題！否則物件導向的資料庫早就實現了！
建議使用CLR的地方一般是和應用的複雜程度或作業系統環境有很高的耦合度的場景。如你想構建複雜的演算法，並且用到了大量的指標和高階資料模型。或者是要和作業系統進行Socket通訊的場景。否則建議慎重！
7、索引檢視
索引檢視2k就有。但是2005對其效率作了一些改進但是schema.viewname的作用域真是太限制了它的應用面。還有一大堆的環境引數和種種限制都讓人對它有點卻步。
8、語句和事務快照
語句級快照和事務級快照終於為SQL Server的併發效能帶來了突破。個人感覺語句級快照大家應該應用。事務級快照，如果是高併發系統還要慎用。如果一個使用者總是被提示修改不成功要求重試時，會殺人的！
9、資料庫快照
原理很簡單，對要求長時間計算某一時間點的報表生成和防使用者操作錯誤很有幫助。但是比起Oracle10g的閃回技術還是細粒度不夠。可惜！
例子：
CREATE DATABASE demo2
GO
USE demo2
ALTER DATABASE demo2 SET allow_snapshot_isolation ON --啟動快照功能
CREATE TABLE test
(
tid INT NOT NULL primary key,
tname VARCHAR(50) NOT NULL
)
INSERT INTO test VALUES(1,'version1')
INSERT INTO test VALUES(2,'version2')

--連線一

USE demo2
BEGIN TRAN
UPDATE test SET tname='version3' WHERE tid=2
SELECT * FROM test

--連線二
USE demo2
SET transaction isolation level snapshot
SELECT * FROM test

10、Mirror
Mirror可以算是SQL Server的Data guard了。具說它切換速度可以達到秒級,這個功能能否被使用者認可還有待時間的考驗. 這個操作起來比較簡單不在詳細介紹。

二、開發方面
參考頁面：
1、Ranking函式集
其中最有名的應該是row_number了。這個終於解決了用臨時表生成序列號的歷史，而且SQL Server2005的row_number比Oracle的更先進。因為它把Order by整合到了一起，不用像Oracle那樣還要用子查詢進行封裝。但是大家注意一點。

如下面的例子：
USE demo
GO
CREATE TABLE rankorder
(
orderid INT,
qty INT
)
GO
INSERT rankorder VALUES(30001,10)
INSERT rankorder VALUES(10001,10)
INSERT rankorder VALUES(10006,10)
INSERT rankorder VALUES(40005,10)
INSERT rankorder VALUES(30003,15)
INSERT rankorder VALUES(30004,20)
INSERT rankorder VALUES(20002,20)
INSERT rankorder VALUES(20001,20)
INSERT rankorder VALUES(10005,30)
INSERT rankorder VALUES(30007,30)
INSERT rankorder VALUES(40001,40)
GO
SELECT orderid,qty,
ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
RANK() OVER(ORDER BY qty) AS rank,
DENSE_RANK() OVER(ORDER BY qty) AS denserank
FROM rankorder
ORDER BY qty
還有一點要說明：
select ROW_NUMBER() OVER (order by aa)
from tbl
order by bb
會先執行aa的排序，然後再進行bb的排序。
可能有的朋友會抱怨整合的order by，其實如果使用ranking函式,Order by是少不了的。如果擔心Order by會影響效率，可以為order by的欄位建立聚集索引，查詢計劃會忽略order by 操作（因為本來就是排序的嘛）。
2、top
可以指定一個數字表示式，以返回要透過查詢影響的行數或百分比，還可以根據情況使用變數或子查詢。可以在DELETE、UPDATE和INSERT查詢中使用TOP選項。可以動態傳入引數，省卻了動態SQL的拼寫。
例:
--宣告個變數
DECLARE @a INT
DECLARE @b INT
DECLARE @c INT

--賦值
SET @a = 10
SET @b = 5
SELECT @c = @a/@b

--使用計算表示式
SELECT TOP(@c) * FROM toptest
--使用SELECT語句作為條件
SELECT TOP(SELECT COUNT(*) FROM toptest2) *
FROM toptest

--指出top
DELETE TOP(2) toptest where column1>'t6'

--更新top
UPDATE TOP(2) toptest SET column1 = 'hi' where column1<='t2'

3、Apply
對遞迴類的樹遍歷很有幫助。
CROSS APPLY : 得到和FUNCTION 結果相匹配的記錄
OUTER APPLY: All rows , regardless of matching function results
一個有代表性的例子：
USE demo
GO
CREATE TABLE Arrays
(
aid INT NOT NULL IDENTITY PRIMARY KEY,
array VARCHAR(7999) NOT NULL
)
GO
INSERT INTO Arrays VALUES('')
INSERT INTO Arrays VALUES('10')
INSERT INTO Arrays VALUES('20,40,30')
INSERT INTO Arrays VALUES('-1,-3,-5')
GO
CREATE FUNCTION function1(@arr AS VARCHAR(7999))
RETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL)
AS
BEGIN
DECLARE @end AS INT, @start AS INT, @pos AS INT
SELECT @arr = @arr + ',', @pos = 1,
@start = 1, @end = CHARINDEX(',', @arr, @start)
WHILE @end > 1
BEGIN
INSERT INTO @t VALUES(@pos, SUBSTRING(@arr, @start, @end - @start))
SELECT @pos = @pos + 1,
@start = @end + 1, @end = CHARINDEX(',', @arr, @start)
END
RETURN
END

--測試
SELECT * FROM function1('200,400,300')
GO

SELECT A.aid, F.*
FROM Arrays AS A
CROSS APPLY function1(array) AS F
GO
SELECT A.aid, F.*
FROM Arrays AS A
OUTER APPLY function1(array) AS F
GO
查詢結果:


4、CTE(Common Table Expression 通用表示式)
它是一個可以由定義語句引用的臨時表命名的結果集，要用到WITH 關鍵字。例：
WITH SalesCTE(ProductID,SalesOrderID)
AS
(SELECT ProductID,COUNT(SalesOrderid)
FROM Sales.SalesOrderDetail GROUP BY ProductID)
SELECT * FROM SalesCTE
5、try/catch
先說明一下事務相關語法:
BEGIN TRAN[SACTION] [transaction_name]
COMMIT [TRAN[SACTION]] [transaction_name]
ROLLBACK [TRAN[SACTION] [transaction_name]
SAVE TRAN[SACTION] [savepoint_name]
上面這幾很簡單不解釋了,下面看 SET XACT_ABORT ON/OFF,當 SET XACT_ABORT 為 ON 時，如果 Transact-SQL 語句產生執行時錯誤，整個事務將終止並回滾。為 OFF 時，只回滾產生錯誤的 Transact-SQL 語句，而事務將繼續進行處理。所以在sql server 2k裡使用事務就必須SET XACT_ABORT ON或者設定儲存點。有了try/catch sql server2005 就不一樣了!! 演示程式碼如下表。

USE demo
GO
CREATE TABLE student --建立工作表
( stuid INT NOT NULL PRIMARY KEY,
stuname VARCHAR(50) )
CREATE TABLE score
( stuid INT NOT NULL REFERENCES student(stuid),
score INT )
GO
INSERT INTO student VALUES (101,'zhangsan')
INSERT INTO student VALUES (102,'wangwu')
INSERT INTO student VALUES (103,'lishi')
INSERT INTO student VALUES (104,'maliu')
2K事務的打操作 2005 使用TRY...CATCH構造擴充了錯誤處理能力
--呼叫一個執行時錯誤
SET XACT_ABORT ON(若為OFF 事務不會回滾)
BEGIN TRAN
INSERT INTO score VALUES (101,90)
INSERT INTO score VALUES (102,78)
INSERT INTO score VALUES (107,76) /*外來鍵錯誤*/
INSERT INTO score VALUES (103,81)
INSERT INTO score VALUES (104,65)
COMMIT TRAN
GO
SET XACT_ABORT OFF
BEGIN TRY
BEGIN TRAN
INSERT INTO score VALUES (101,90)
INSERT INTO score VALUES (102,78)
INSERT INTO score VALUES (107,76) /*外來鍵錯誤*/
INSERT INTO score VALUES (103,81)
INSERT INTO score VALUES (104,65)
COMMIT TRAN
PRINT '事務提交'
END TRY
BEGIN CATCH
ROLLBACK
PRINT '事務回滾'
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_MESSAGE() as ErrorMessage;
END CATCH
GO

6、pivot/unpivot
實現行列互轉。2K 中這個功能用case實現，不過好像沒有case 直觀。而且預設的第三欄位（還可能更多）作為group by欄位很容易造成新手的錯誤。（有圖我就不說了）
7、OUTPUT 關鍵字
在下面的例子中只看藍色的部分就是正常的insert into … values 語句,透過output 可以將中間值輸出 到臨時表,也就是不能過觸發器也可以實現對中間資料的處理。例：
DECLARE @InsertDetails TABLE (ProductID int, Insertedby sysname)
INSERT INTO Stock.ProductList
OUTPUT inserted.ProductID, suser_name() INTO @InsertDetails
VALUES (‘Racing Bike’,412.99)



MSSQL Server 2000系統資料型別：
(來源於SQL查詢分析器)
1bigint 從 -2^63 到 2^63-1 之間的 integer (整數)資料
2binary 定長的binary資料，最長為8,000位元組
3bit integer資料，值為1或0
4char 定長的非unicode character資料，長度為8,000個字元
5cursor 含有對遊標的引用的變數或儲存過程OUTPUT引數所採用的資料型別
6datetime date和time資料，從1753年1月1日到9999年12月31日
7decimal 定點精度和小數的numeric資料，從-10^38-1到10^38-1之間
8float 浮點精度數字資料，從-1.79E+308到1.79E+308之間
9image 長度可變的binary資料，最長為2^31-1位元組
10int 從-2^31到2^31-1之間的integer(整數)資料
11money monetary資料值，從-2^63到2^63-1，準確度為貨幣單位的千分之一
12nchar 定長的unicode資料，長度為4,000個字元
13ntext 長度可變的unicode資料，最長為2^30-1個字元
14numeric decimal的同義詞
15nvarchar 長度可變的unicode資料，最長為4,000字元
16real 浮點精度數字資料，從-3.40E+38到3.40E+38之間
17rowversion 資料庫範圍內的唯一號
18smalldatetime date和time資料，從1900年1月1日到2079年6月6日
19smallint 從-2^15到2^15-1之間的integer資料
20smallmoney monetary資料值，-214,748.3648到+214,748.3647之間
21sql_variant 可儲存多種SQL Server支援的資料型別的值的資料型別，
22 但不儲存text, ntext, timestamp和sql_variant型別的值
23sysname 系統提供的使用者定義的資料型別，為nvarchar(128)的同義詞
24table 一種特殊的資料型別，可用於為以後進行處理而儲存結果集
25text 長度可變的非unicode資料，最長為2^31-1個字元
26timestamp 資料庫範圍內的唯一號
27tinyint 從0到255之間的integer資料
28uniqueidentifier全域性唯一識別符號(GUID)
29varbinary 長度可變的binary資料，最長為2^31-1位元組
30varchar 長度可變的非unicode資料，最長為8,000個字元