SQL_Server_2005新特性

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

SQL Server 2005新特性

王为 微软ESS护航工程师 微软认证讲师 微软 护航工程师 wangwei@

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

Summary New FeatureTools 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

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

Overview of SQL Server 2005What 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新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

What Is SQL Server 2005?SQL Server Integration Services Analysis Services Notification Services

SQL Server 2005Full-Text Search Relational Database Engine .NET CLR Reporting Services

Replication Native HTTP Support

Service Broker

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

Enhanced Data TypesLarge value data types:varchar(max) nvarchar(max) varbinary(max) 8K—2G 8K 2G Max关键字指明最大值

xml – stores XML data with optional schema

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

What Is a Partitioned Table? distributed across multiple file Table datagroups Improved performance on multiprocessor/multidrive systems Easier management of large tables

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

How to Partition a Table1Create the partition functionCREATE PARTITION FUNCTION emailPF (nvarchar(30)) AS RANGE RIGHT FOR VALUES ('G', 'N')

2

Create the partition schemeCREATE PARTITION SCHEME emailPS AS PARTITION emailPF TO (fg1, fg2, fg3)

3

Create the partitioned tableCREATE TABLE Sales.CustomerEmail (CustID int, email nvarchar(30)) ON EMailPS (email)

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

Enhanced Indexing FeaturesThe ALTER INDEX statement allows: DisablingALTER INDEX IX_CustDOB ON Sales.Customer DISABLE

RebuildingALTER INDEX PK_CustId ON Sales.Customer REBUILD

ReorganizingALTER INDEX PK_CustId ON Sales.Customer REORGANIZE

Setting of optionsALTER INDEX PK_CustId ON Sales.Customer SET(...)

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

Use the OUTPUT Keyword1 2Declare a table-valued variable Use OUTPUT to return details to the variable

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

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

PIVOT and UNPIVOT OperatorsPIVOT – converts values to columnsCust Mike Mike Mike Lisa Lisa Lisa Prod Bike Chain Bike Bike Chain Chain Qty 3 2 5 3 3 4

Cust

Bike

Chain

SELECT * FROM Sales.Order PIVOT (SUM(Qty) FOR Prod IN ([Bike],[Chain])) PVT

Mike Lisa

8 3

2 7

UNPIVOT – converts columns to valuesCust Cust Mike Lisa Bike 8 3 Chain 2 7 Prod Bike Chain Bike Chain Qty 8 2 3 7

SELECT Cust, Prod, Qty FROM Sales.PivotedOrder UNPIVOT (Qty FOR Prod IN ([Bike],[Chain])) UnPVT

Mike Mike Lisa Lisa

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

Demo1

PIVOT and UnPIVOT

PIV_UnPIV.sql

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

Storing XML in the Database Benefits:Single store for structured and semistructured data Define variable content in a relational model Choose the most appropriate data model Functiona

lity: XML Indexes XQuery-based data retrieval XQuery-based modifications XML schema support: Typed XML: Validated by a schema Untyped XML: Nonvalidated XML

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

Demo2

存储XML数据类型

StoringXML.sql

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

How to Manage XML IndexesCreate a primary XML indexCREATE PRIMARY XML INDEX xidx_Item ON Sales.Invoices(ItemList)

Create secondary PATH XML indexCREATE XML INDEX xidx_ItemPath ON Sales.Invoices(ItemList) USING XML INDEX xidx_Item FOR PATH

Create secondary PROPERTY XML indexCREATE XML INDEX xidx_ItemProp ON Sales.Invoices(ItemList) USING XML INDEX xidx_Item FOR PROPERTY

Create secondary VALUE XML indexCREATE XML INDEX xidx_ItemVal ON Sales.Invoices(ItemList) USING XML INDEX xidx_Item FOR VALUE

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

The Database EngineDatabase engine enhancements:Partitioning tables and indexes DDL triggers and event notifications New data types Multiple Active Result Sets (MARS) Enhancements to Transact-SQL Improved security architecture Native support for XML and XQuery .NET Framework integration Enhancements to replication Improved maintainability

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

The Common Language RuntimeDatabase objects implemented in managed codeStored procedures User-defined data types User-defined functions Triggers User-defined aggregates.NET Assembly

SP UDT UDF Trgr

Agg

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

SQL Server 2005 CLR IntegrationRun managed code within a database by using in-process assemblies Create managed stored procedures, triggers, user-defined functions, userdefined types, and aggregates Integration benefits:Enhanced programming model Enhanced safety and security Common development environment Performance and scalability

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

Managed Code vs. Transact-SQLUse managed code for:Procedures that feature complex logic Access to the .NET Framework class library CPU intensive functions

Use Transact-SQL for data access with only basic procedural logic

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

SQL Server Projects in Visual Studio 2005Project for creating managed database objects Automatically includes necessary referencessqlaccess.dll System.Data.dll

Includes templates for each object typeStored procedure Trigger User-defined function User-defined type Aggregate

Allows immediate deployment and debugging

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

Demo3 使用 2005定义 SQLServer用户自定义函数 2005.txt

SQL_Server_2005新特性,SQL Server 2005 新特性,SQLServer2005新特性,Microsoft SQL server SQL Server 2005新特性,微软2005数据库新特性

Notification ServicesSubscription Management Objects Event APIs Set-based subscription processing Notification formatting and delivery

Subscribers

Events

Notification Services

Notifications

本文来源：https://www.bwwdw.com/article/iy24.html