SQL2008一些学习小结,不断总结

yp1234

　　新的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>　　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
　　以上代码我都测试过了,很长吧,谢谢各位客官,文中不当之处,恳请批评指正~