剛開始學習2008新特性~~~寫錯的地方各位大俠多多提醒,慢慢增加文章。如果您的測試結果和我的不一樣~也是正常，畢竟還是測試版本~~飛鴿傳書一切都是變數 --測試版本 Microsoft SQL Server 2008 (CTP) - 10.0.1442.32 (Intel X86)??
May 30 2008 00:02:15?? Copyright (c) 1988-2007
Microsoft Corporation? Enterprise Evaluation Edition on Windows NT 5.1
<X86> (Build 2600: Service Pack 3)
備份 USE master
GO
EXEC sp_configure 'backup compression default', '1'
GO
RECONFIGURE WITH OVERRIDE
GO sp_helpdb ReportServer? --10.50 MB
Backup Database ReportServer to disk ='d:/ReportServer_rar.bak' with COMPRESSION --用壓縮的方式進行備份
Backup Database ReportServer to disk ='d:/ReportServer_Full.bak'??? --默認壓縮方式,不壓縮,結果集大小一樣

--返回表的當前大小并估算表在請求的壓縮狀態下的大小
USE AdventureWorks
GO
EXEC sp_estimate_data_compression_savings 'Production', 'WorkOrderRouting', NULL, NULL, 'ROW' ;
GO

CREATE TABLE T (c1 int, c2 char(2000) )
WITH (DATA_COMPRESSION= ROW)? --行壓縮

CREATE TABLE T_Part(c1 int, c2 char(2000) )
ON somePartScheme(c1)? --somePartScheme為分區方案
WITH
(DATA_COMPRESSION = PAGE ON PARTITIONS (1-7),
DATA_COMPRESSION = NONE ON PARTITIONS (8) )

create index CI on T (c1)

ALTER INDEX CI ON T REBUILD? --頁壓縮
WITH (DATA_COMPRESSION=PAGE)?
ALTER INDEX CI ON T REBUILD PARTITION = 3? --對分區表頁壓縮
WITH (DATA_COMPRESSION=PAGE)?


結果發現兩個BAK文件大小差不多~~也許要更大的數據庫才行，有機會再測試一把。 新的時間數據類型 下列數據類型只在SSIS中支持。 DT_DBTIME2? --支持更大的秒小數位數。
DT_DBTIMESTAMP2? --支持用戶定義的精度。
DT_DBTIMESTAMPOFFSET? --支持時區偏移量。
新的Date/Time數據類型
DATE
?大年限(1~9999)
?節省存儲空間
?簡易開發和編程

TIME
用戶可選擇的百分秒比率,可精確到千萬分之一(0 ~ 100ns)
?簡易開發和編程

DATETIME2
?大年限(1~9999)
?用戶可選擇的百分秒比率,可精確到千萬分之一(0 ~ 100ns)

DATETIMEOFFSET?
?日期+ 時間+ 時區
?標準格林維治時間存儲,對比和排序
?簡易開發和編程

--drop table [online-purchase-order]
CREATE TABLE [online-purchase-order]
([item-id] int,[item-name]
VARCHAR(30),qty int,[purchase-time] datetimeoffset,
[purchase-timezone] varchar(10))

INSERT INTO [online-purchase-order] ([purchase-time]) VALUES ('1541-01-01')

select * from [online-purchase-order]

讀取當前系統日期,時間,時區別本地和標準格林維治時間的數據轉換
SELECT SYSDATETIME(),SYSDATETIMEOFFSET(),SYSUTCDATETIME()
SELECT DATENAME (tz, SYSDATETIMEOFFSET())

本地和標準格林維治時間的數據轉換
SELECT CONVERT (DATETIME2,SYSDATETIMEOFFSET(),0) as 'local';
select CONVERT (DATETIME2, SYSDATETIMEOFFSET(), 1) as 'UTC';

DECLARE @dtov DATETIMEOFFSET =TODATETIMEOFFSET(GETUTCDATE(),'+00:00')
DECLARE @local datetime=CONVERT(DATETIME, SWITCHOFFSET(@dtov, '-05:00'))

進行日期和時間轉換時對索引進行保留
CREATE TABLE dbo.Search(MyDate datetime2);
CREATE clustered index idx1 on dbo.Search(MyDate);
SELECT MyDate FROM dbo.Search? WHERE CONVERT(DATE,MyDate) = '2005-04-07';


定義
?可叧通過一個單獨的SQL語句來有效的執行多個SQL DML操作
?可有效實現對兩個表格(源和目標)的數據交換操作
?SQL-2006標準兼容(幵延伸對DELETE的支持)
--有則更新無則插入的代碼

UPDATE TGT SET TGT.quantity += SRC.quantity,
TGT.LastTradeDate= SRC.TradeDate FROM dbo.StockHolding AS?
TGT INNER JOIN dbo.StockTradingAS SRC ON TGT.stock= SRC.stock;

INSERT INTO dbo.StockHolding(stock, lasttradedate, quantity)
SELECT stock, tradedate, quantity FROM dbo.StockTrading AS SRC
WHERE NOT EXISTS(SELECT * FROM dbo.StockHolding AS TGT
?WHERE TGT.stock= SRC.stock);
?
?--如上代碼可以改成MERGE,會更加方便,大家可以根據需求自己修改表
MERGE INTO dbo.StockHolding AS TGT
USING dbo.StockTradingAS SRC ON TGT.stock= SRC.stock
WHEN MATCHED AND (t.quantity+ s.quantity= 0)
THEN DELETE WHEN MATCHED THEN
UPDATE SET t.LastTradeDate= s.TradeDate, t.quantity= s.quantity
WHEN NOT MATCHED THEN
INSERT VALUES (s.Stock,s.TradeDate,s.Quantity);


--類似可改代碼
BEGIN TRAN AAA
declare @pid int,@pcategory int ,@pdesc int
declare @pname varchar(20)
IF EXISTS (SELECT * FROM dbo.Products WHERE ProductID= @pid)
UPDATE Products
SET ProductName= @pname, ProductCategory= @pcategory, ProductDesc= @pdesc
ELSE
INSERT Products VALUES(@pid, @pname, @pcategory, @pdesc)


MERGE Products AS p
USING (SELECT @pidAS ProductID, @pnameAS ProductName, @pcategoryAS ProductCategory, @pdescAS pdesc) AS newp
ON(p.ProductID= newp.ProductID)
WHEN MATCHED THEN
UPDATE SET p.ProductName= newp.ProductName, p.ProductCategory= newp.ProductCategory, p.ProductDesc= newp.ProductDesc
WHEN NOT MATCHED BY TARGET THEN
INSERT Products VALUES(newp. ProductID, newp.ProductName, newp.ProductCategory, newp.ProductDesc)

新GROUPING SETs子句
?GROUP BY子句的延伸
?可在同樣的查詢語句中定義多個groupings
?可生成不UNION ALL同樣結果集
?可令聚合查詢和搜索變得簡便和快速
SELECT customerType,Null as TerritoryID,MAX(ModifiedDate)FROM Sales.Customer GROUP BY customerType
UNION ALL
SELECT Null as customerType,TerritoryID,MAX(ModifiedDate)FROM Sales.Customer GROUP BY TerritoryID order by TerritoryID
go
SELECT
customerType,TerritoryID,MAX(ModifiedDate)
FROM Sales.Customer
GROUP BY GROUPING SETS ((customerType), (TerritoryID))
order by customerType desc
--null 在前面
go
通過VALUE子句的表值結構
?運用VALUE子句來組建數據行
?通過單INSERT語句進行多行輸入
?SQL-2006標準兼容
select @@TRANCOUNT
rollback
begin tran aaa
INSERT INTO dbo.Customers(custid, companyname, phone, address)
VALUES(1, 'cust1', '(111) 111-1111', 'address 1'),
(2, 'cust2', '(222) 222-2222', 'address 2'),
(3, 'cust3', '(333) 333-3333', 'address 3'),
(4, 'cust4', '(444) 444-4444', 'address 4'),
(5, 'cust5', '(555) 555-5555', 'address 5');

SELECT *
FROM
(VALUES
(1, 'cust1', '(111) 111-1111', 'address 1'),
(2, 'cust2', '(222) 222-2222', 'address 2'),
(3, 'cust3', '(333) 333-3333', 'address 3'),
(4, 'cust4', '(444) 444-4444', 'address 4'),
(5, 'cust5', '(555) 555-5555', 'address 5')
) AS C(custid, companyname, phone, address);

新的表格類型?
我丌想重復編寫以下T-SQL語句…
DECLARE @NewCustomerTABLE
(
[CustomerID] intNULL,
[FirstName] varchar(50) NOT NULL,
[LastName] varchar(50) NOT NULL,
[CompanyName] [nvarchar](128) NULL
)

新的表格類型
自定義表格類型和現有表值變量定義方法兼容可用來定義表值變量可定義indexes and constraints會被定義在Sys.table_types
優點
?簡便, Type 匹配
CREATE TYPE myTAS table (a int, b varchar(100))

當前如何處理表值數據
運用臨時表影響I/O對臨時表的非自動操作丌可避免頻繁的存儲過程再編譯
--表變量不能用索引
--drop TYPE myTableType
CREATE TYPE myTableType AS TABLE(
STOCK VARCHAR(10) PRIMARY KEY CLUSTERED,
TradeDate DATE ,--CHECK (TradeDate< convert(date,SYSDATETIME())),
Quantity INT);

DECLARE @stockTrades myTableType;
INSERT INTO @stockTrades VALUES ('MSFT',convert(date,SYSDATETIME()),1);
--INSERT INTO @stockTrades
--SELECT Stock, MAX(CONVERT(DATE, TradeTime)), SUM(Quantity)
--FROM DailyTrades GROUP BY Stock
SELECT * FROM @stockTrades WHERE stock = 'MSFT'
UPDATE @stockTrades SET stock = 'microsoft' WHERE stock = 'MSFT'

新的表值參數(TVP)
--定義表類型
--DROP PROCEDURE myProc
--DROP TYPE myTableType
CREATE TYPE myTableType AS TABLE (id INT, name NVARCHAR(100),qty INT);

--用一個表值函數建立一個SP
CREATE PROCEDURE myProc(@tvp myTableType READONLY)
AS
UPDATE Inventory SET qty += s.qty
FROM Inventory AS iINNER JOIN @tvp AS tvp
ON i.id = tvp.id
GO

DECLARE @list AS myTableType;
INSERT INTO @list VALUES (1, 'Bicycle', 10),
(2, 'Roller blades', 5), (3, 'Soccer bal', 25);
SELECT * FROM @LIST

EXEC myProc @list;

+= -= /= *= 付值運算符號
DECLARE @v int= 5;
DECLARE @v1 INT=50;
SELECT @v1/=@V
SELECT @V1

數據庫對象目標附屬性
通過新創建系統catalog view和DMV來有效的查詢表格,存儲過程,視圖,函數等數據庫對象的附屬性查詢

-- sys.sql_expression_dependencies
-- sys.dm_sql_referenced_entities
-- sys.dm_sql_referencing_entities

主要用途
–發現一個數據庫對象所附屬的所有對象
–發現附屬于一個數據庫對象的所有對象
–發現附屬于另一個數據庫的所有對象

eg:
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('Production.Product', 'OBJECT');
GO

以上代碼我都測試過了,很長吧,謝謝各位客官,文中不當之處,懇請批評指正~

總結

以上是生活随笔為你收集整理的SQL2008学习小结的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。