SQL_Server_2005新特性.pptVIP

SQL Server 2005新特性 王为 微软认证讲师 微软ESS护航工程师 wangwei@ Summary New Feature Tools and Utilities Enhancements Database Engine Enhancements New Notification Services New Reporting Services New Service Broker Analysis Services Enhancements Data Transformation Services Enhancements Replication Enhancements Data Access Interfaces Enhancements Overview of SQL Server 2005 What is SQL Server 2005? The Database Engine Analysis Services SQL Server Integration Services Notification Services Reporting Services Service Broker The Common Language Runtime Native HTTP Support Replication Full-Text Search SQL Server 2005 What Is SQL Server 2005? Relational Database Engine .NET CLR Analysis Services Native HTTP Support Service Broker Replication Reporting Services Full-Text Search Notification Services SQL Server Integration Services Enhanced Data Types Large value data types: varchar(max) nvarchar(max) varbinary(max) 8K—2G Max关键字指明最大值 xml – stores XML data with optional schema What Is a Partitioned Table? Table data distributed across multiple file groups Improved performance on multiprocessor/multidrive systems Easier management of large tables How to Partition a Table Enhanced Indexing Features The ALTER INDEX statement allows: Disabling Rebuilding Reorganizing Setting of options ALTER INDEX PK_CustId ON Sales.Customer REBUILD ALTER INDEX IX_CustDOB ON Sales.Customer DISABLE ALTER INDEX PK_CustId ON Sales.Customer REORGANIZE ALTER INDEX PK_CustId ON Sales.Customer SET(...) Use the OUTPUT Keyword DECLARE @InsertDetails TABLE (ProductID int, InsertedBy sysname) INSERT INTO Stock.ProductList OUTPUT inserted.ProductID, suser_name() INTO @InsertDetails VALUES (Racing Bike, 412.99) SELECT * FROM @InsertDetails Cust Bike Chain Mike 8 2 Lisa 3 7 PIVOT and UNPIVOT Operators PIVOT – converts values to columns Cust Prod Qty Mike Bike 8 Mike Chain 2 Lisa Bike 3 Lisa Chain 7 SELECT * FROM Sales.Order PIVOT (SUM(Qty) FOR Prod IN ([Bike],[Chain])) PVT SELECT Cust, Prod, Qty FROM Sales.Pivot