#眉標=特別報導
#副標=SQL Server 2011初探(1)
#大標=揭露下一代資料庫平台─SQL Server 2011
#作者=文／圖 胡百敬


===========程式1 <BOX>================
USE tempdb
GO
-- 建立 Sequence
CREATE SEQUENCE MySequence
AS INT MINVALUE 1 NO MAXVALUE START WITH 1 INCREMENT BY 1 CYCLE;
-- 建立資料表
CREATE TABLE TSeq1 (col1 int, col2 varchar(50));
CREATE TABLE TSeq2 (col1 int, col2 varchar(50));
GO
INSERT TSeq1 (col1, col2) VALUES (NEXT VALUE FOR MySequence, 'test')
INSERT TSeq2 (col1, col2) VALUES (NEXT VALUE FOR MySequence, 'test')
GO 10

SELECT * FROM TSeq1
SELECT * FROM TSeq2
SELECT * FROM sys.sequences
=========<END>======================





==============程式2 <BOX>=======================
--將 Sequence Object 的值重新設定為 10
ALTER SEQUENCE MySequence RESTART WITH 10;

--直接傳回 Sequece Object 的值
SELECT NEXT VALUE FOR MySequence

--組織成特定格式的流水號
SELECT '2011_' + convert(varchar(10),NEXT VALUE FOR MySequence)

--建立資料表時，賦予欄位預設值為特定流水號格式
create table t(c1 varchar(20) 
default ('2011_' + convert(varchar(10),NEXT VALUE FOR MySequence)))

DROP TABLE TSeq1
DROP TABLE TSeq2
DROP SEQUENCE MySequence
========<END>===================





==============程式3 <BOX>=======================
USE AdventureWorks2008R2
GO

DECLARE @pageSize int = 10;
DECLARE @pageNumber int = 2;
-- 較耗資源，不建議使用
SELECT TOP (@pageSize * @pageNumber) BusinessEntityID,NameStyle,Title
FROM Person.Person
EXCEPT
SELECT TOP (@pageSize * (@pageNumber - 1)) BusinessEntityID,NameStyle,Title
FROM Person.Person;

--透過 row_number 搭配 CTE 取回某一頁內的紀錄
WITH tb(ID,BusinessEntityID,NameStyle,Title)
AS
(SELECT ROW_NUMBER() OVER(Order by BusinessEntityID) ID,
BusinessEntityID,NameStyle,Title
FROM Person.Person)
SELECT BusinessEntityID,NameStyle,Title FROM tb
WHERE ID BETWEEN (@pageSize * (@pageNumber - 1))+1 AND (@pageSize * @pageNumber)
========<END>===================



==============程式4 <BOX>=======================
SELECT BusinessEntityID,NameStyle,Title
FROM Person.Person
ORDER BY BusinessEntityID DESC
OFFSET @pageSize * (@pageNumber - 1) ROWS
FETCH NEXT @pageSize ROWS ONLY;
========<END>===================




==============程式5 <BOX>=======================
USE Northwind
GO
CREATE PROC spGetCustOrders @Date DATETIME
AS
SELECT CompanyName,OrderID,OrderDate FROM
Customers c JOIN Orders o ON c.CustomerID=o.CustomerID
WHERE OrderDate < @Date

SELECT o.OrderID,Sum(UnitPrice*Quantity*(1-Discount)) SumSales 
FROM Orders o JOIN [Order Details] d ON o.OrderID=d.OrderID 
WHERE o.OrderDate<@Date
GROUP BY o.OrderID
GO

--執行預存程序時，指定回傳的資料結構
EXEC spGetCustOrders '19960731'
WITH RESULT SETS
(
　([公司名稱] nvarchar(100) not null,
　 [訂單編號] int not null,
　 [訂單日期] date),
　([訂單編號] int not null,
　 [銷售金額] money
　)
)
========<END>===================




==============程式6 <BOX>=======================
CREATE TABLE myDocumentStore AS FileTable 
WITH FileTable_Directory myDocument
FILESTREAM_ON myFILESTREAMGroup1;
========<END>===================