[SQL][MCSA]70-461 筆記 (一)

2012-08-31

8864
0
認證考試
2012-11-20

Create Database Objects (24%)

Create and alter tables using T-SQL syntax (simple statements).
- May include but not limited to: create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE
Create and alter views (simple statements).
- May include but not limited to: create indexed views; create views without using the built in tools; CREATE, ALTER, DROP
Design views.
- May include but not limited to: ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications
Create and modify constraints (simple statements).
- May include but not limited to: create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints
Create and alter DML triggers.
- May include but not limited to: inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers

建立 Table ( MSDN Link : http://msdn.microsoft.com/zh-tw/library/ms174979(v=sql.110).aspx )

SQL 2012 加入 FileTable ( http://msdn.microsoft.com/zh-tw/library/ff929144.aspx )
SQL 2012 強化了 Partition Schema 的數量 , 因此這個部分應該也要注意一下 ( http://msdn.microsoft.com/zh-tw/library/ms188730.aspx )
PRIMARY KEY 條件約束
- 一份資料表只能有一個 PRIMARY KEY 條件約束。
- PRIMARY KEY 條件約束所產生的索引，無法使資料表的索引數目超出 999 個非叢集索引和 1 個叢集索引。
- 如果未指定 PRIMARY KEY 是非叢集索引或是叢集索引時，且未指定 UNIQUE 條件約束的叢集索引，便使用叢集索引。
- PRIMARY KEY 條件約束內所定義的所有資料行，都必須定義成 NOT NULL。如果未指定 Null 屬性，參與 PRIMARY KEY 條件約束的所有資料行，其 Null 屬性都會設成 NOT NULL。
UNIQUE 條件約束
- 如果未指定 UNIQUE 條件約束非叢集索引或是叢集索引時，預設會使用非叢集索引。
- 每個 UNIQUE 條件約束都會產生一個索引。 UNIQUE 條件約束數目無法使資料表的索引數目超出 999 個非叢集索引和 1 個叢集索引。
FOREIGN KEY 條件約束
- 當在 FOREIGN KEY 條件約束的資料行中輸入 NULL 以外的值時，值必須在參考的資料行中；否則，會傳回外部索引鍵違規錯誤訊息。 ( PK 不可以有 NULL 值但 FK 可以有 NULL )
- FOREIGN KEY 條件約束只能參考在相同伺服器之相同資料庫內的資料表。
- FOREIGN KEY 條件約束可以參考相同資料表中的另一個資料行。
- 資料行層級 FOREIGN KEY 條件約束的 REFERENCES 子句只能列出一個參考資料行。這個資料行必須有定義了條件約束的資料行之相同資料類型。
- 資料表層級 FOREIGN KEY 條件約束的 REFERENCES 子句，必須有與條件約束資料行清單中的資料行一樣多的參考資料行。每個參考資料行的資料類型，也必須與資料行清單中的對應資料行相同。
- 暫存資料表不會強制執行 FOREIGN KEY 條件約束。
- FOREIGN KEY 條件約束只能參考在所參考的資料表中之 PRIMARY KEY 或 UNIQUE 條件約束中的資料行，或在所參考的資料表之 UNIQUE INDEX 中的資料行。
DEFAULT 定義
- 每個資料行只能有一個 DEFAULT 定義。
- DEFAULT 定義可以包含常數值、函數、SQL-92 niladic 函數，或 NULL。
- DEFAULT 定義中的 constant_expression 無法參考資料表中的另一個資料行，也無法參考其他資料表、檢視表或預存程序。
- 不能在含 timestamp 資料類型的資料行上，或在含 IDENTITY 屬性的資料行上建立 DEFAULT 定義。
CHECK 條件約束
- 資料行可以有任意數目的 CHECK 條件約束，且條件可以包括用 AND 和 OR 組合的多個邏輯運算式。資料行的多個 CHECK 條件約束是依照建立的順序來驗證的。
- 搜尋條件必須得出布林運算式，且不能參考其他資料表。
- 資料行層級 CHECK 條件約束只能參考受條件約束限制的資料行，資料表層級的 CHECK 條件約束只能參考相同資料表中的資料行。
- CHECK CONSTRAINTS 和規則會在 INSERT 和 UPDATE 陳述式期間，提供相同的資料驗證功能。
- 當一個或多個資料行有規則和一個或多個 CHECK 條件約束存在時，會評估所有限制。
- 在 text、ntext 或 image 資料行上，無法定義 CHECK 條件約束。

刪除 Table ( MSDN Link : http://msdn.microsoft.com/zh-tw/library/ms173790.aspx )

     -- 刪除資料表

    DROP TABLE ProductVendor1 ;

修改 Table ( MSDN Link : http://msdn.microsoft.com/zh-tw/library/ms190273.aspx )

    -- 增加 Column

    ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;

    -- 刪除 Column

    ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;

    -- 變更欄位型態

    ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;

    -- 加入 Constrain

    ALTER TABLE dbo.doc_exd WITH NOCHECK ADD CONSTRAINT exd_check CHECK (column_a > 1) ;

    -- 停用 Trigger

    ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ;

    -- Partition 資料移動

    CREATE TABLE PartitionTable (col1 int, col2 char(10))

    ON myRangePS1 (col1) ;

    GO

    CREATE TABLE NonPartitionTable (col1 int, col2 char(10))

    ON test2fg ;

    GO

    ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;

    GO

    -- 設定 Page 壓縮

    ALTER TABLE T1 REBUILD WITH (DATA_COMPRESSION = PAGE);

建立 View ( MSDN Link : http://msdn.microsoft.com/zh-tw/library/ms187956.aspx )

建立由查詢定義其內容 (資料行和資料列) 的虛擬資料表。您可以使用這個陳述式來建立資料庫中一個或多個資料表內資料的檢視。可用於下列目的：

對焦 (Focus)、簡化和自訂每位使用者查看資料庫的角度。
做為安全機制，讓使用者能夠透過檢視存取資料，但不將直接存取基底資料表的權限授與使用者。
提供回溯相容介面以模擬其結構描述已變更的資料表。

檢視定義中的 SELECT 子句不能包括下列項目：

ORDER BY 子句，除非 SELECT 陳述式的選取清單中也有 TOP 子句

INTO 關鍵字
OPTION 子句
指向暫存資料表或資料表變數的參考。

CHECK OPTION

強制執行所有針對檢視來執行的資料修改陳述式遵照 select_statement 內所設定的準則。當利用檢視來修改資料列時，WITH CHECK OPTION 可確保在認可修改之後，仍可以透過檢視見到資料。

SCHEMABINDING
- 當指定 SCHEMABINDING 時，無法依照會影響檢視定義的方式來修改一或多份基底資料表。您必須先修改或卸除檢視定義來移除對於要修改之資料表的相依性。

修改 View 和刪除 View 的限制條件與參數與建立 View 的相同，差異只有在於是使用 ALTER 還是 DROP

索引檢視表 ( MSDN Link : http://msdn.microsoft.com/zh-tw/library/ms191432.aspx )

在 SQL Server 2012 中建立索引檢視表。對檢視建立的第一個索引必須是唯一的叢集索引。建好唯一的叢集索引後，才可以建立其他非叢集索引。為檢視表建立唯一的叢集索引，可以提升查詢效能，因為檢視表儲存在資料庫中的方式與包含叢集索引之資料表的儲存方式一樣。

    -- 建立 View

    CREATE VIEW Sales.vOrders

    WITH SCHEMABINDING

    AS

        SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,

            OrderDate, ProductID, COUNT_BIG(*) AS COUNT

        FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o

        WHERE od.SalesOrderID = o.SalesOrderID

        GROUP BY OrderDate, ProductID;

    GO

    -- 建立唯一性的叢集索引的

    CREATE UNIQUE CLUSTERED INDEX IDX_V1 

        ON Sales.vOrders (OrderDate, ProductID);

    GO

條件約束 Constrain ( MSDN Link : http://msdn.microsoft.com/zh-tw/library/ms188066.aspx )

PRIMARY KEY
- 它會利用唯一索引來強制執行一個或多個指定之資料行的實體完整性。每份資料表都只能建立一個 PRIMARY KEY 條件約束。
- 不能有 NULL 值
UNIQUE
- 利用唯一索引來提供一個或多個指定之資料行的實體完整性。每份資料表都可以建立一個以上的 UNIQUE 條件約束
- 可以有 NULL 值
CLUSTERED | NONCLUSTERED
- 指定建立 PRIMARY KEY 或 UNIQUE 條件約束的叢集或非叢集索引。 PRIMARY KEY 條件約束預設為 CLUSTERED。 UNIQUE 條件約束預設為 NONCLUSTERED。
- 如果叢集條件約束或索引已在資料表中，就無法指定 CLUSTERED。如果叢集條件約束或索引已在資料表中，PRIMARY KEY 條件約束便預設為 NONCLUSTERED。
- 每個資料表最多只能建立一個 CLUSTERED 索引
INDEX

索引資料行不能指定 ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、xml 或 image 資料類型的資料行。

FOREIGN KEY REFERENCES

它提供資料行中之資料的參考完整性。 FOREIGN KEY 條件約束要求資料行中的每個值( 除非 NULL 例外 )都要存在於所參考之資料表的指定資料行中。

TRIGGER ( MSDN Link : http://msdn.microsoft.com/zh-tw/library/ms178110.aspx )

DML 觸發程序常會用於執行商務規則與資料完整性。 SQL Server 利用 ALTER TABLE 及 CREATE TABLE 陳述式提供宣告式參考完整性 (DRI)。不過，DRI 並不提供跨資料庫的參考完整性。參考完整性是指資料表主索引鍵和外部索引鍵之間的關聯性規則。若要強制執行參考完整性，請在 ALTER TABLE 和 CREATE TABLE 中，使用 PRIMARY KEY 和 FOREIGN KEY 條件約束。如果觸發程序資料表有條件約束，便會在執行 INSTEAD OF 觸發程序之後，執行 AFTER 觸發程序之前，檢查這些條件約束。如果違反條件約束，便會回復 INSTEAD OF 觸發程序動作，且不會引發 AFTER 觸發程序。
INSTEAD OF TRIGGER ( http://msdn.microsoft.com/zh-tw/library/ms175089.aspx )

會覆寫觸發陳述式的標準動作。因此它們可以用於對一個或多個資料行執行錯誤或值檢查，然後在插入、更新或刪除一個或多個資料列之前執行其他動作

AFTER TRIGGER

AFTER 觸發程序會在執行過 INSERT、UPDATE、MERGE 或 DELETE 陳述式的動作後才執行。發生強制違規時絕對不會執行 AFTER 觸發程序，所以，這些觸發程序無法用於可能妨礙強制違規的任何處理動作。

    CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader

    AFTER INSERT

    AS

    IF EXISTS (SELECT *

               FROM Purchasing.PurchaseOrderHeader p 

               JOIN inserted AS i ON p.PurchaseOrderID = i.PurchaseOrderID 

               JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = p.VendorID

               WHERE v.CreditRating = 5

              )

    BEGIN

    RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);

    ROLLBACK TRANSACTION;

    RETURN 

    END;

    GO

    CREATE TRIGGER INSTEADOF_TR_I_EmpQualification ON vw_EmpQualification

    INSTEAD OF INSERT AS

    BEGIN

        DECLARE @Code TINYINT

        SELECT @Code = qualificationCode FROM lib_Qualification L 

            INNER JOIN INSERTED I ON L.qualification = I.qualification

        IF (@code is NULL )

        BEGIN

            RAISERROR (N'The provided qualification does not exist in qualification library', 16, 1)

            RETURN

        END

        INSERT INTO employees (empcode, name, designation,qualificationCode,deleted) 

            SELECT empcode, name, designation, @code, 0 FROM inserted 

    END

    GO

回首頁

五餅二魚工作室

James Fu 的技術學習之路

[SQL][MCSA]70-461 筆記 (一)

系列文章

標籤雲