獨孤雲異想世界

Asymmetric Encryption

獨孤雲 — Sun, 07 Jan 2007 09:46:00 GMT

--測試非對稱鑰匙對所有的加/解密,簽章與驗證
use tempdb
go

--建立非對稱鑰匙對

create asymmetric key keyRSA
with Algorithm=RSA_2048
encryption by password=N'password';

--建立存放加密與簽章資料的資料表
create table tblTest(c1 int identity(1,1) primary key , Org nvarchar(100),
Encrypt Varbinary(max),Signature varbinary(max))
go

--將資料透過鑰匙對加密與簽章後放入資料表
declare @str nvarchar(100)
set @str = N'Hello RSA 2048'
insert into tblTest values(@str,
EncryptByAsymKey(AsymKey_ID('keyRSA'),@str),
SignByAsymKey(AsymKey_Id('keyRSA'),@str,N'password')
)
go

select * from tblTest

--解密，並驗證簽章
select org,convert(nvarchar(100),
DecryptByAsymKey(AsymKey_Id('keyRSA'),CAST(Encrypt as Nvarchar(MAX)),
N'password')) as Decrypt,
CASE when VerifySignedByAsymKey(AsymKey_Id('keyRSA'),Org,Signature)=1
then
N'資料正確'
else
N'資料被改過'
end
as IsSignatureValid
from tblTest

--故意修改原始資料
update tblTest set Org=Org+'a'

--再驗證一次

select org,convert(nvarchar(100),
DecryptByAsymKey(AsymKey_Id('keyRSA'),CAST(Encrypt as Nvarchar(MAX)),
N'password')) as Decrypt,
CASE when VerifySignedByAsymKey(AsymKey_Id('keyRSA'),Org,Signature)=1
then
N'資料正確'
else
N'資料被改過'
end
as IsSignatureValid
from tblTest

--清除
drop table tblTest
drop asymmetric key keyRSA

Symmetric Encryption

獨孤雲 — Sun, 07 Jan 2007 09:44:00 GMT

use AdventureWorks
go

drop table tab1

create MASTER KEY
encryption by password='some56*z'
go

create certificate foo with subject='foo'
go

dump certificate foo to file ='c:\foo.cer'
go

create SYMMETRIC KEY fookey with ALGORITHM =DES
encryption by certificate foo
go

-- 設定為 varbinary欄位
create table tab1(id int,cardno varbinary(100))
go

-- 開啟對稱式 key
OPEN SYMMETRIC KEY fookey DECRYPTION BY CERTIFICATE foo;
go

insert tab1(id,cardno) values(1,encryptByKey(Key_GUID('fookey'),'12345678'))

-- 取得加密值
select * from tab1

-- 取得解密值
select convert(char(8), decryptbykey(cardno)) from tab1

-- 關掉對稱式 key之後,得到 null值
close symmetric key fookey
select convert(char(8), decryptbykey(cardno)) from tab1

-- drop everything
drop table tab1
drop symmetric key fookey
drop certificate foo
drop master key

Restore database example

獨孤雲 — Sun, 31 Dec 2006 16:57:00 GMT

USE [master]
GO

--
CREATE DATABASE [AWData] ON
( FILENAME = N'C:\myAdmin\DB\AWData_P.mdf' ),
( FILENAME = N'C:\myAdmin\DB\AWData_log.ldf' ),
( FILENAME = N'C:\myAdmin\DB\AWData_A.ndf' ),
( FILENAME = N'C:\myAdmin\DB\AWData_B.ndf' ),
( FILENAME = N'C:\myAdmin\DB\AWData_C.ndf' )
FOR ATTACH

-- 各資料表所屬的 Filegroup
SELECT objs.schemaN N'結構描述', objs.tableN N'資料表', das.name N'檔案群組'
FROM AWData.sys.data_spaces das INNER JOIN
(
  SELECT DISTINCT object_id,data_space_id
  FROM AWData.sys.indexes
  WHERE type BETWEEN 0 AND 1
) idx
ON das.data_space_id = idx.data_space_id
INNER JOIN
(
  SELECT sys.objects.name 'tableN', object_id, sys.schemas.name 'schemaN'
  FROM AWData.sys.objects INNER JOIN AWData.sys.schemas
  ON sys.objects.schema_id = sys.schemas.schema_id
  WHERE type='U'
) objs
ON objs.object_id = idx.object_id
ORDER BY 3

-- 分析各個資料檔案的屬性
SELECT name, physical_name, state_desc, is_read_only , read_only_lsn
FROM AWData.sys.database_files

-- 分析資料
SELECT * FROM AWData.dbo.tbl_P
UNION ALL
SELECT * FROM AWData.dbo.tbl_A
UNION ALL
SELECT * FROM AWData.dbo.tbl_B
UNION ALL
SELECT * FROM AWData.dbo.tbl_C

-- INSERT DATA
INSERT AWData.dbo.tbl_P VALUES(2,'P')
INSERT AWData.dbo.tbl_A VALUES(2,'A')
INSERT AWData.dbo.tbl_B VALUES(2,'B')
INSERT AWData.dbo.tbl_C VALUES(2,'C')

-- 使用 SQLCMD 模式
!!net stop mssqlserver /Y

-- 破壞 C:\myAdmin\DB\AWData_B.ndf

!!ren "C:\myAdmin\DB\AWData_B.ndf" *.BAD

!!net start mssqlserver /Y

-- 檢查 SQL Server 記錄檔
/*
訊息
錯誤: 17207，嚴重性: 16，狀態: 1。
FileMgr::StartSecondaryDataFiles: Operating system error 2(系統找不到指定的檔案。) occurred while creating or opening file 'C:\myAdmin\DB\AWData_B.ndf'. Diagnose and correct the operating system error, and retry the operation.

訊息
錯誤: 5120，嚴重性: 16，狀態: 5。
無法開啟實體檔案 "C:\myAdmin\DB\AWData_B.ndf"。作業系統錯誤 2: "2(系統找不到指定的檔案。)"。

-- 分析資料，Error
SELECT * FROM AWData.dbo.tbl_P

-- 檢查資料庫的狀態 -- RECOVERY_PENDING
SELECT name, state_desc
FROM sys.databases

-- OFFLINE
ALTER DATABASE AWData
MODIFY FILE
(name = N'AWData_B', OFFLINE)
go

-- Database are Online
ALTER DATABASE AWData
SET ONLINE
go

-- 分析各個資料檔案的屬性 -- OFFLINE
SELECT name, physical_name, state_desc, is_read_only , read_only_lsn
FROM AWData.sys.database_files

-- 分析資料
SELECT * FROM AWData.dbo.tbl_P
SELECT * FROM AWData.dbo.tbl_A
SELECT * FROM AWData.dbo.tbl_B -- ERROR
SELECT * FROM AWData.dbo.tbl_C

/*
訊息 8653，層級 16，狀態 1，行 1
查詢處理器無法產生資料表或檢視 'tbl_B' 的計劃，因為資料表存在於非線上的檔案群組中。
*/

-- Backup LOG --> tail-log backup
/*
記得先將 M0337_PiecemealBackup.bak，存放到 C:\myAdmin\Backup 中。
*/

BACKUP LOG AWData
TO DISK ='C:\myAdmin\Backup\M0337_PiecemealBackup.bak'
WITH NAME = 'LOG_backupset',
NO_TRUNCATE

--
RESTORE HEADERONLY
FROM DISK ='C:\myAdmin\Backup\M0337_PiecemealBackup.bak'

--
RESTORE FILELISTONLY
FROM DISK ='C:\myAdmin\Backup\M0337_PiecemealBackup.bak'
WITH FILE=1

-- RESTORE DATABASE -- File
RESTORE DATABASE AWData
FILE = N'AWData_B'
FROM DISK ='C:\myAdmin\Backup\M0337_PiecemealBackup.bak'
WITH RECOVERY

/*
已處理資料庫 'AWData' 的 16 頁，檔案 1 上的檔案 'AWData_B'。
向前復原目前的起始點位於記錄序號 (LSN) 21000000030800001。
必須有超過 LSN 21000000035600001 的其他向前復原，才能完成還原順序。
RESTORE DATABASE ... FILE=<name> 已於 0.057 秒內成功處理了 16 頁 (2.299 MB/sec)。
*/

-- 分析各個資料檔案的屬性 -- RESTORING
SELECT name, physical_name, state_desc, is_read_only , read_only_lsn
FROM AWData.sys.database_files

--
RESTORE LOG AWData
FROM DISK ='C:\myAdmin\Backup\M0337_PiecemealBackup.bak'
WITH FILE = 2,
RECOVERY

-- 分析各個資料檔案的屬性 -- ONLINE
SELECT name, physical_name, state_desc, is_read_only , read_only_lsn
FROM AWData.sys.database_files

-- 分析資料
SELECT * FROM AWData.dbo.tbl_P
UNION ALL
SELECT * FROM AWData.dbo.tbl_A
UNION ALL
SELECT * FROM AWData.dbo.tbl_B
UNION ALL
SELECT * FROM AWData.dbo.tbl_C

Configuration commands

獨孤雲 — Sun, 31 Dec 2006 13:29:00 GMT

sp_configuration (server configuration)

sp_dboption (database configuration)

set xxx on / off (connection configuration)

Rename database name

獨孤雲 — Sun, 31 Dec 2006 11:57:00 GMT

1. sp_dropserver @@servername

2. sp_addserver 'vs99',local

3. shutdiwb with nowait

4. net start SQL server service

Integrity & Constraint Type

獨孤雲 — Sun, 24 Dec 2006 15:36:00 GMT

1. Domain Integrity

(1). Default Constraint

(2). Check Constraint

(3). Foreign Key Constraint

(4). Null Constraint

2. Entity Integrity

(1). Primary Key Constraint

(2). Unique Constraint

3. Referential Integrity :

(1). Foreign Key Constraint

(2). Check Constraint

Secondary XML Index

獨孤雲 — Sun, 24 Dec 2006 14:49:00 GMT

* Secondary XML Index is created based on Primary XML Index

* Secondary XML Index Type:

(1). PATH

(2). PROPERTY

(3). VALUE

Create XML Index

獨孤雲 — Sun, 24 Dec 2006 14:44:00 GMT

1. 建立 XML Index 的 Table 上要有定義 Clustered Primary Key

2. 每個 Table 第一個建立的 XML Index 必須維 Primary XML Inddex

3. SET ARITHABORT ON

Defragmentation

獨孤雲 — Sun, 24 Dec 2006 14:36:00 GMT

1. <= 30% fragmentation -> Reorganize

2. > 30% fragmentation -> Rebuild

Options to create indexes on computed columns

獨孤雲 — Sun, 24 Dec 2006 13:30:00 GMT

Set to "ON":

1. ANSI_NULLS

2. ANSI_PADDING

3. ANSI_WARNINGS

4. CONCAT_NULL_YIELDS_NULL

5. QUOTED_IDENTIFIER

6. ARITHABORT

Set to "OFF":

1. NUMERIC_ROUNDABORT

Nonclustered Index

獨孤雲 — Sun, 24 Dec 2006 11:43:00 GMT

* Nonclustered index is slower than clustered index

* (1). index key value + data page pointer

(2). index key value + clustered index key

* Every table can create at most 249 nonclustered indexes (Index ID : 2 ~ 250)

* Nonclustered index is default index type

* Time to rebuild nonclustered index :

(1). Existing clustered index is dropped

(2). Clustered index is created

(3). The columns that define the clustered index are changed

Clustered Index

獨孤雲 — Sun, 24 Dec 2006 11:30:00 GMT

* 3 Levels of clustered index

1. Root Index

2. Intermediate Level (Index Page) -> suitable for large range data searching

3. Leaf Level (Data Page)

* One clustered index per table

* Implicit : key + 4 byte integer

Explicit : Unique

XQUERY

獨孤雲 — Sun, 24 Dec 2006 10:59:00 GMT

USE AdventureWorks
go

CREATE TABLE Stores
(StoreID integer IDENTITY PRIMARY KEY,
StoreName nvarchar(40),
Manager nvarchar(40),
Invoices xml)
go

INSERT INTO Stores
VALUES
('Astro Mountain Bike Company', 'Jeff Adell', '<InvoiceList xmlns="http://schemas.adventure-works.com/Invoices">
                                                 <Invoice InvoiceNo="1000">
                                                   <Customer>Kim Abercrombie</Customer>
                                                   <Items>
                                                     <Item Product="1" Price="1.99" Quantity="2"/>
                                                     <Item Product="3" Price="2.49" Quantity="1"/>
                                                   </Items>
                                                  </Invoice>
                                                  <Invoice InvoiceNo="1001">
                                                    <Customer>Sean Chai</Customer>
                                                    <Items>
                                                      <Item Product="1" Price="2.45" Quantity="2"/>
                                                    </Items>
                                                  </Invoice>
                                                </InvoiceList>')

INSERT INTO Stores
VALUES
('Clocktower Sporting Goods', 'Karen Berge', '<InvoiceList xmlns="http://schemas.adventure-works.com/Invoices">
            <Invoice InvoiceNo="999">
                                                   <Customer>Sarah Akhtar</Customer>
                                                   <Items>
                                                     <Item Product="8" Price="2.99" Quantity="3"/>
                                                   </Items>
                                                </Invoice>
                                                <Invoice InvoiceNo="1000">
                                                    <Customer>Bei-Jing Guo</Customer>
                                                    <Items>
                                                      <Item Product="1" Price="1.95" Quantity="7"/>
                                                      <Item Product="100" Price="112.99" Quantity="1"/>
                                                    </Items>
                                                </Invoice>
                                              </InvoiceList>')

INSERT INTO Stores
VALUES
('HiaBuy Toys', 'Scott Cooper', NULL)

-- Use the query method
SELECT StoreName, Invoices.query('declare default element namespace "http://schemas.adventure-works.com/Invoices";
                                  <SoldItems>
                                      for $i in /InvoiceList/Invoice/Items/Item
                                      return $i
                                   </SoldItems>') AS SoldItems
FROM Stores

SELECT StoreName, Invoices.query('declare default element namespace "http://schemas.adventure-works.com/Invoices";
                                      for $i in /InvoiceList/Invoice
                                      where count($i/Items/Item) > 1
                                      return $i
                                   ') AS SoldItems
FROM Stores

-- Use the value method
SELECT StoreName, Invoices.value('declare default element namespace "http://schemas.adventure-works.com/Invoices";
(InvoiceList/Invoice/@InvoiceNo)[1]', 'int') AS FirstInvoice
FROM Stores

-- Use the exist method
SELECT Invoices.exist('declare default element namespace "http://schemas.adventure-works.com/Invoices";
/InvoiceList/Invoice[@InvoiceNo=1000]') As Existence
FROM Stores

SELECT StoreName StoresWithInvoices
FROM Stores
WHERE Invoices.exist('declare default element namespace "http://schemas.adventure-works.com/Invoices";
InvoiceList/Invoice') = 1

-- Bind a relational column
SELECT Invoices.query('declare default element namespace "http://schemas.adventure-works.com/Invoices";
                                      <Invoices>
                                        <Store>{sql:column("StoreName")}</Store>
                                        {
                                         for $i in /InvoiceList/Invoice
                                         return $i
                                        }
                                        </Invoices>') AS InvoicesWithStoreName
FROM Stores

--
declare @invoiceNo int
set @invoiceNo = 1000

SELECT Invoices.query('declare default element namespace "http://schemas.adventure-works.com/Invoices";
                                      <Invoices>
                                        {
                                         for $i in /InvoiceList/Invoice
           where $i/@InvoiceNo = sql:variable("@invoiceNo")
                                         return $i
                                        }
                                        </Invoices>') AS InvoicesWithStoreName
FROM Stores

-- Use the modify method to insert XML
UPDATE Stores
SET Invoices.modify('declare default element namespace "http://schemas.adventure-works.com/Invoices";
insert element SalesPerson {"Alice"}
as first
into (/InvoiceList/Invoice)[1]')
WHERE StoreID = 1

SELECT Invoices.query('declare default element namespace "http://schemas.adventure-works.com/Invoices";
(InvoiceList/Invoice)[1]') AS InsertedSalesPerson
FROM Stores
WHERE StoreID = 1

-- Use the modify method to update XML
UPDATE Stores
SET Invoices.modify('declare default element namespace "http://schemas.adventure-works.com/Invoices";
replace value of (/InvoiceList/Invoice/SalesPerson/text())[1]
with "Holly"')
WHERE StoreID = 1

SELECT Invoices.query('declare default element namespace "http://schemas.adventure-works.com/Invoices";
(InvoiceList/Invoice)[1]') UpdatedSalesPerson
FROM Stores
WHERE StoreID = 1

-- Use the modify method to delete XML
UPDATE Stores
SET Invoices.modify('declare default element namespace "http://schemas.adventure-works.com/Invoices";
delete (/InvoiceList/Invoice/SalesPerson)[1]')
WHERE StoreID = 1

SELECT Invoices.query('declare default element namespace "http://schemas.adventure-works.com/Invoices";
(InvoiceList/Invoice)[1]') DeletedSalesPerson
FROM Stores
WHERE StoreID = 1

-- Use the nodes method to extract relational data
declare @Invoices xml
SELECT @Invoices = Invoices
FROM Stores
WHERE StoreID = 1
SELECT nCol.value('@Product', 'int') AS ProductID,
nCol.value('@Price', 'money') AS Price
FROM @Invoices.nodes('declare default element namespace "http://schemas.adventure-works.com/Invoices"; /InvoiceList/Invoice/Items/Item') as nTable(nCol)

SELECT nCol.value('../../@InvoiceNo[1]', 'int') AS InvoiceNo,
       nCol.value('@Product[1]', 'int') AS ProductID,
       nCol.value('@Price[1]', 'money') AS Price,
       nCol.value('@Quantity[1]', 'int') AS Quantity
FROM Stores CROSS APPLY Invoices.nodes('declare default element namespace "http://schemas.adventure-works.com/Invoices"; /InvoiceList/Invoice/Items/Item') AS nTable(nCol)
ORDER BY InvoiceNo

OPENXML

獨孤雲 — Sun, 24 Dec 2006 09:46:00 GMT

DECLARE @docHandle int
declare @xmlDocument nvarchar(max) -- or xml type
set @xmlDocument = N'<ROOT><Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1"><Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/><Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/></Customers><Customers CustomerID="XYZBB" ContactName="Steve" CompanyName="Company2">No Orders yet!</Customers></ROOT>'

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers
SELECT *
FROM OPENXML(@docHandle, N'/ROOT/Customers')
WITH Customers

-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders
SELECT * FROM OPENXML(@docHandle, N'//Orders') WITH Orders

-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders')
WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime)

-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle

select * from Customers
select * from Orders

Partitioned Tables

獨孤雲 — Sat, 23 Dec 2006 16:29:00 GMT

-- Create partition function
USE AdventureWorks
GO

CREATE PARTITION FUNCTION pf_OrderDate (datetime)
AS RANGE RIGHT
FOR VALUES ('01/01/2004', '01/01/2005')

-- Add filegroups and create partition scheme
ALTER DATABASE AdventureWorks ADD FILEGROUP fg1
ALTER DATABASE AdventureWorks ADD FILEGROUP fg2
ALTER DATABASE AdventureWorks ADD FILEGROUP fg3
ALTER DATABASE AdventureWorks ADD FILEGROUP fg4
GO

ALTER DATABASE AdventureWorks
ADD FILE
( NAME = data1,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AWd1.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg1
GO

ALTER DATABASE AdventureWorks
ADD FILE
( NAME = data2,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AWd2.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg2
GO

ALTER DATABASE AdventureWorks
ADD FILE
( NAME = data3,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AWd3.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg3
GO

ALTER DATABASE AdventureWorks
ADD FILE
( NAME = data4,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AWd4.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg4
GO

CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
TO (fg1, fg2, fg3, fg4)
GO

-- Create partitioned table
CREATE TABLE dbo.PartitionedTransactions
(
TransactionID int IDENTITY(1,1) NOT NULL,
ProductID int NOT NULL,
TransactionDate datetime NOT NULL DEFAULT (getdate()),
TransactionType nchar(1) NOT NULL
)
ON ps_OrderDate(TransactionDate)
GO

-- Insert data
INSERT INTO dbo.PartitionedTransactions
SELECT ProductID, TransactionDate, TransactionType
FROM Production.TransactionHistory
GO

INSERT INTO dbo.PartitionedTransactions
VALUES
(1, '01/01/2005', 'S')
GO

-- View partition metadata
SELECT * FROM sys.Partitions
WHERE [object_id] = OBJECT_ID('dbo.PartitionedTransactions')

-- View data with partition number
SELECT TransactionID, TransactionDate, $Partition.pf_OrderDate(TransactionDate) AS PartitionNo
FROM dbo.PartitionedTransactions

-- Verify lowest value in each partition
SELECT MIN(TransactionDate) AS FirstTran, $Partition.pf_OrderDate(TransactionDate) AS PartitionNo
FROM dbo.PartitionedTransactions
GROUP BY $Partition.pf_OrderDate(TransactionDate)
ORDER BY PartitionNo