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)
GO
EXEC sp_configure 'backup compression default', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
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)
DT_DBTIMESTAMP2 --支持用户定义的精度。
DT_DBTIMESTAMPOFFSET --支持时区偏移量。
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
以上代码我都测试过了,很长吧,谢谢各位客官,文中不当之处,恳请批评指正~