SQL Server2000 重溫筆記

iSQlServer發表於2009-07-23
SQLServer筆記
一、頁面（Page）和盤區（Extent）：
Sql Server2000中所有的資訊都儲存在頁面上，除了事務日誌（事務日誌是由一系列記錄組成的）頁面是資料庫中使用的最小資料單元，每個頁面儲存8K的資訊。Sql Server2000中有以下幾種型別的頁面：
  • 資料頁面：用於儲存資料庫資料，但不包括text,ntext,image型別的資料
  • 索引頁面：用於儲存索引資料
  • 文字/影像頁面：用於儲存text,ntext,image型別的資料
  • 全域性分配頁面：用於儲存盤區分配的資訊
  • 頁面剩餘空間頁面：用於儲存頁面剩餘空間的資訊
  • 索引分配頁面：用於儲存頁面被表或索引使用的盤區的資訊。
盤區是由8個連續頁面組成的資料結構，它是Sql 2000中為表和索引分配空間的最小單位，也就是說當建立一個表時，Sql會自動地以盤區為單位給它分配空間。Sql 2000中設計兩種盤區：
  • 單一盤區：只被單一的資料庫物件使用，所有8各頁面都用於這個物件。
  • 混合盤區：被最多8個物件共同使用，一個新建的表或索引首先從混合盤區分配頁面，當表或索引中的資料量增大到8個頁面時，再將它轉化為單一盤區。
二、特殊的資料庫型別：
1、文字和影像資料，在SQL Server中用text, ntext和image資料型別表示，因為它們的資料量往往較大，所以不像表中其他型別的資料那樣一行一行地存放在資料頁中，而是被儲存在專門的頁中，在資料行的相應位置只儲存指向這些資料實際儲存位置的指標。但在SQL 2000中可以指定將text, ntext和image資料型別的資料在行中儲存，但要滿足2各條件：
  • 文字和影像資料的大小不超過7000位元組。
  • 資料行有足夠的空間存放這些資料。
具體設定：sp_tableoption [table],'text in row','ON'或者'OFF'或者是上限值如'1000'
此類型別資料的修改要用到WriteText,被更新的文字或影像被完全覆蓋，預設狀態下，WriteText語句的操作不被記入事務日誌。
語法：writetext table.column text_ptr(指向要更改的文字或影像資料的指標，其型別必須是Binary(16)) data. 
eg:
首先學習下sp_dboption,

顯示或更改資料庫選項。不能在 master 或 tempdb 資料庫上使用 sp_dboption。向後相容性支援 sp_dboption。使用 ALTER DATABASE 設定資料庫選項。
語法
sp_dboption [ [ @dbname = ] 'database' ]
    [ , [ @optname = ] 'option_name' ]
    [ , [ @optvalue = ] 'value' ]
select into/builcopy 選項名, 當為true時，允許使用 SELECT INTO 語句和快速大容量複製

不在行記憶體儲text資料：
Exec sp_dboption 'Market','select into/bulkcopy','true'
GO
Declare @ptrval binaty(16)
select @ptrval = textptr(description) -- 獲取指標
from Goods
where Name ='Battery'
writetext Goods.description @ptrval 'The descriotion of Battery, just for test.'
在行記憶體儲text資料：
Exec sp_tableoption 'Goods','text in row','on'
GO
Begin tran
Declare @ptrval binaty(16)
select @ptrval = textptr(description) -- 獲取指標
from Goods
where Name ='Battery'
writetext Goods.description @ptrval 'The descriotion of Battery, just for test.'
commit
若要修改部分資料就要用Updatetext

例如，以下查詢用字母 z 取代 New Moon Books 中 text 列的第 88 個字元（Moon 中的第二個字母 o）：
USE pubs
sp_dboption 'pubs', 'select into/bulkcopy', 'true'
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info) 
    FROM pub_info pr INNER JOIN publishers p
        ON p.pub_id = pr.pub_id 
        AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @ptrval 88 1 'z' 
sp_dboption 'pubs', 'select into/bulkcopy', 'false'
2、使用者自定義型別，是資料庫的一種物件，就像table, view。。。。。。它一般是基於基礎型別的，比如定義了一個email型別，新增預設值，規則檢查'@'，當建立表email欄位就可以用這個自定義型別。
新增自定義型別：exec sp_addtype enail,'varchar(10)','not null'

三、強制資料完整性機制：
1、主鍵（PRIMARY KEY）約束，主健可以強制實體完整性。
2、外來鍵（FOREIGN KEY）約束，SQL 2000中增加了級聯修改和級聯刪除的功能，作為外來鍵的列的值可以是空值。
3、惟一性（UNIQUE）約束，和主鍵約束的區別：一個表上只能定義一個主鍵約束，但可以定義多個惟一性約束；定義了惟一性約束的列上的資料庫可為空。都可以被外來鍵約束引用。
4、檢查（CHECK）約束：檢查約束可以用來限制列上可以接受的資料值，使用用邏輯表示式來限制列上可以接受的資料。可以在一列上使用多個檢查約束，也可以在表上建立一個可以在多個列上使用的檢查約束。
5、預設值（DEFAULT）:預設值也是一種資料庫物件。可以在建立表時設定，也可以在資料庫中建立一個預設值物件，繫結到指定的列。
Create default goodDesp as 'common goods'
exec sp_bindefault 'goodDesp','Goods.Description'
6、空值（NULL）

以上約束實施的完整性稱為宣告型資料完整性，他們是作為表和列定義的一部分在語法中實現的，還有一種資料完整性的實施方法是過程型資料完整性，它的實現是獨立於表存在的，需要與表繫結，如預設值，規則，觸發器等資料庫物件，這些資料完整性不是一個表獨有的，可以應用於資料庫中不同的表。

四、在表的操作中應用宣告型資料完整性：
1、命名約束：用Constraint  約束名 約束類別，如果是命名約束就可以做更改或刪除等操作。如：
Create table Goods
(
　　name varchar(10) 
　　Constraint Pk_GoodName Primary Key,
　　...
　　status tinyint default(0)
)

2、非命名約束：
Create table Goods
(
　　name varchar(10) Primary Key,
　　...
　　status tinyint default(0)
)

3、建立外來鍵約束：

  • References table(field) on delete no action(sql 7.0 沒有級聯刪除)/cascade(sql 2000級聯刪除) ,沒有Foreign Keny 子句
  • Foreign Key (field) References table(field) on delete no action(sql 7.0 沒有級聯刪除)/cascade(sql 2000級聯刪除)
Create tabel orders
(
　　orderId int Indentity(1,1) constraint PK_OrderID primary key,
　　goodName varchar(20) not null references Goods(name) on delete cascade，
　　customerID int not null foreign key(custimerID) references Customers(customerid) on delete no action,
　　quantity int not null constraint CK_Quantity check(quantity > 0)
)

4、修改，新增，刪除列
Alter table customers alter column name varchar(40) not null
Alter table customers add email varchar(50) null constraint CK_EA check(email like '%@%')
Alter table customers drop column email

5、修改約束：'with check or nocheck'表示加約束時，檢查還是不檢查表中已有的資料。'nocheck'使約束無效，'check'使約束重新有效。 
Alter table Goods with nocheck add/drop constraint pk_Name Primary Key(name)
Alter table Goods nocheck/check constraint pk_Name 

6、where和having的區別：
  • where針對單行，having針對一組行
  • 在Group by子句分組前去掉不滿足where條件的行，而having子句中的條件在分組之後被應用。
  • having子句可在條件中包含聚合函式，而where不行
select name, sum(Quantity) as sumQuty from orders 
group by name having sum(Quantity)>500

7、computer 返回所有行的集合，computer ... by 子句返回一個分組後記錄的集合
select name Quantity from orders
computer sum(Quantity) [by name]
返回兩個結果

8、可以用with encryption 給檢視，儲存過程，觸發器加密，加密後任何許可權的人都看不到具體的內容。

五、規則：是資料庫物件之一，它的作用與check約束的部分功能相同，在向表的某列插入或更新資料時，用它來限制輸入值得取值範圍。規則和約束的不同之處在於：
1、check約束是建表時指定的，規則需要作為單獨的資料庫物件來實現
2、在一列上只能使用一個規則，但可以使用多個check約束
3、規則可以應用於多個列，還可以用於自定義型別，而check只能用於定義它的列
應用規則需要首先定義規則，在繫結：
Create rule rule_name as @value >0
sp_bindrul/sp_unbindrule rule_name field 
eg: exec sp_bindrule 'range_rule','orders.Quantity'

六、客戶程式訪問資料庫過程：
1、查詢語句被髮送到伺服器。如果是儲存過程，則傳送的sql很少，減少頻寬壓力。使用者看不到表，所以儲存過程可以作為安全機制。
2、伺服器編譯T_SQL語句
3、優化產生查詢計劃。 2，3這兩步都在儲存過程中已經做了，所以儲存過程速度比較快。
4、資料庫引擎執行查詢
5、執行結果發回客戶程式

儲存過程最好不要用sp_作為其名稱的字首，因為sql server是以下面的順序來查詢以sp_開頭的儲存過程：
1、在master資料庫查詢
2、如果儲存過程指定了資料庫和所有者，則查詢基於指定資料庫和所有者的儲存過程
3、如果沒有制定儲存過程的資料庫和所有者，則以DBO作為所有者來查詢
4、若使用者定義的儲存過程和系統儲存過程同名，使用者定義的儲存過程永遠不會被執行

七、觸發器：觸發器是一種特殊的儲存過程。觸發器和引起觸發器執行的T_SQL語句被當作一次事務處理，因此可以在觸發器中回滾這個事務。如果發現引起觸發器執行的T_SQL語句執行了一個非法操作，則可以通過回滾事務使語句不能執行，回滾後SQL Server會自動返回到此事務執行前的狀態。
1、Inserted表和Deleted表在觸發器執行過程中的作用?
SQL Server為每個觸發器都建立了Inserted和Deleted表，這是兩個邏輯表，由系統來維護，使用者不能對它們進行修改。它們存放在記憶體而不是資料庫，它們的表結構與被該觸發器作用的表結構相同。觸發器執行完成後，這兩個表也會被刪除。
  • Deleted表存放由於執行Delete或Update語句而要從表中刪除的所有行。也就是說在執行Delete或Update時，被刪除的行從啟用觸發器的表中被移到Deleted表，這兩個表不會有共同的行。
  • Inserted表存放由於執行Insert或Update語句而要象表中插入的所有行。在Insert或Update事務中，新的行同時新增到啟用觸發器的表和Inserted表中，Inserted表的內容是啟用觸發器的表中新行的拷貝。
一個Update事務是首先執行一個Delete操作，在執行一個Insert操作，舊行首先被移到Deleted表，然後新行同時插入啟用觸發器的表和Inserted表。
Create trigger goodsDelete on Goods after delete as 
delete from orders where goodsName in (select name from deleted)
Create trigger OrderInsert on Orders after insert as 
if(select status from Goods,inserted where Goods.name = inserted.goodsName)=1
begin
　　print 'The goods is being processed, the order cannot be committed'
　　rollback transaction 
end

八、索引：索引是一種物理結構，它能夠提供一種以一列或多列的值為基礎迅速查詢表中行的能力。索引中記錄了表中的關鍵值，提供了指向表中行的指標。索引使得資料庫應用程式能夠不掃瞄全表而找到想要資料。
1、無索引和有索引的查詢過程：
如果某個欄位沒有索引，要按此欄位查詢時，就要按照儲存順序一行一行的查詢所有記錄，看對應欄位是否符合條件。如果在對應列上建立了索引，那麼SQL Server就首先搜尋這個索引，在索引中找到符合條件的值，再按照索引中記錄的相應行在表中的位置找到表中的行。由於索引進行了分類，且索引包含的行和列比全表少的多，所以索引搜尋是很快的。
2、索引越多越好嗎？
不是，一是因為建立索引要花費時間和佔用儲存空間，二是因為索引雖然加快了搜尋速度，卻減慢了資料修改和插入的速度。因為每當執行一次資料修改（插入，刪除，更新）時，就要進行索引的維護，對建立了索引的列執行操作要比未建立索引的列執行修改操作所花的時間長，修改的資料越多，涉及維護索引的開銷就越大。
3、索引的分類：
按索引的結構分聚集索引和非聚集索引，按索引實現的功能分惟一性索引和非惟一性索引。惟一性索引能保證在建立索引的列或多列組合上不包括重複的資料，聚集和非聚集索引都可以是惟一性索引。在建立主鍵約束和惟一性約束的列上會自動建立惟一性索引。
  • 聚集索引：在聚集索引中，行的物理儲存順序與索引順序相同，即索引的順序決定了表中行的儲存順序，因為行是經過排序的，所以每個表只能有一個聚集索引。由於聚集索引的順序與資料行存放的物理順序相同，找到一個範圍的開始的行，就很容易找到範圍結束的行，所以聚集索引有利於範圍搜尋。如果表中沒有建立聚集索引，則會自動在表的主鍵列上建立聚集索引。

         Create unique clustered index ix_orderid on Orders(OrderID) desc

  • 非聚集索引：非聚集索引並不在物理儲存上排列資料，即索引的順序並不等同於表中行的物理儲存順序，索引僅僅記錄指向表中行的位置指標，這些指標本身是有序的，通過這些指標可以在表中快速的定位資料。可以為表中每一個常用查詢的列定義非聚集索引。非聚集索引的特點決定了它很適合直接匹配單個條件的查詢。為表建立的索引，預設都是非聚集索引，在某列上設定惟一性約束也自動在該列上建立了非聚集索引。

        Create unique [nonclustered] index ix_name on Customers(LastName,FirstName)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-610008/，如需轉載，請註明出處，否則將追究法律責任。

相關文章