SQL Server 2000 Overview: SQL Server 2000 Overview: 新特性新特性

郝郝雪莹雪莹Microsoft CorporationMicrosoft Corporation

专注于质量专注于质量 SQL Server 2000 vs. SQL Server 7.0SQL Server 2000 vs. SQL Server 7.0

50% more functional tests50% more functional tests300% more stress tests300% more stress tests25% more early adopters25% more early adopters500% more beta users500% more beta users40 internal systems on SQL Server 40 internal systems on SQL Server

2000 before release2000 before release

More customers deployed earlierMore customers deployed earlier

AgendaAgenda

安装安装 // 升升级级 管理管理工具工具 引引擎功能的增强擎功能的增强 支持支持 XMLXML 复复制制 Analysis ServicesAnalysis Services Desktop Desktop 与与 Windows CEWindows CE

安安装装 // 升升级级多多实例实例 缺省实例缺省实例

Prior versions of SQL ServerPrior versions of SQL Server 多实例多实例

Multi-instance is a paradigmMulti-instance is a paradigm A named instance is an installationA named instance is an installation

SQL Server 2000 SQL Server 2000 安装程序可以建立缺省安装程序可以建立缺省实例和命名实例实例和命名实例

安装安装 // 升升级级实实例例 DefaultDefault

Only one activeOnly one active Version switchVersion switch

NamedNamed All instances All instances

can be activecan be active

SQL Server 2000SQL Server 2000SQL Server 2000SQL Server 2000

{{

SQL Server 2000SQL Server 2000SQL Server 2000SQL Server 2000

SQL Server 2000SQL Server 2000SQL Server 2000SQL Server 2000

SQL Server 2000SQL Server 2000SQL Server 2000SQL Server 2000

SQL Server 7.0 or 2000SQL Server 7.0 or 2000SQL Server 7.0 or 2000SQL Server 7.0 or 2000

SQL Server 6.5 SQL Server 6.5 SQL Server 6.5 SQL Server 6.5

Up to Up to 1515

安装安装 // 升升级级缺省实例缺省实例 缺省实例缺省实例

看起来象 看起来象 SQL Server 7.0 SQL Server 7.0 做的那样做的那样 Has an MSSQLServer and Has an MSSQLServer and

SQLServerAgent serviceSQLServerAgent service Registry keys are basically the sameRegistry keys are basically the same

HKLM/SW/Microsoft/MSSQLServerHKLM/SW/Microsoft/MSSQLServer

使用服务器名与服务器连接使用服务器名与服务器连接 osql -E -Sservernameosql -E -Sservername

安装安装 // 升升级级命名实例命名实例 命名实例命名实例

在同一台硬件服务器上的附加的在同一台硬件服务器上的附加的 SQL ServerSQL Server服务服务 例如，连接到实例例如，连接到实例 “ “ SQL2000”SQL2000”

osql -E -Sservername\SQL2000osql -E -Sservername\SQL2000 Services reflect the instance nameServices reflect the instance name

MSSQL$SQL2000, SQLAgent$SQL2000MSSQL$SQL2000, SQLAgent$SQL2000 Registry keys reflect the nameRegistry keys reflect the name

HKLM/software/Microsoft/Microsoft SQL HKLM/software/Microsoft/Microsoft SQL Server/SQL2000Server/SQL2000

File path reflects the nameFile path reflects the name C:\Program Files\Microsoft SQL Server\C:\Program Files\Microsoft SQL Server\

MSSQL$SQL2000MSSQL$SQL2000

安装安装 // 升升级级Server Server 引用引用 ServicesServices

Default: Default: net start MSSQLServernet start MSSQLServer Named: Named: net start MSSQL$Instance1net start MSSQL$Instance1

ApplicationsApplications Default: Default: <ComputerName><ComputerName> Named: Named: <ComputerName>\Instance1<ComputerName>\Instance1

安装安装 // 升升级级故障转移集群故障转移集群 在以前版本的在以前版本的 SQL ServerSQL Server 上有了很大提高上有了很大提高 不需要其它的外部向导不需要其它的外部向导

““Just Click Next”Just Click Next”

集群管理集群管理 Add and remove nodesAdd and remove nodes Multiple IP addressesMultiple IP addresses Multiple virtual servers – each with a single Multiple virtual servers – each with a single

instanceinstance

No Unclustering in SQL Server 2000 No Unclustering in SQL Server 2000 Service PacksService Packs

安装与升级安装与升级升级策略升级策略 Versions Earlier than SQL Server 6.5 Versions Earlier than SQL Server 6.5

Migrate to SQL Server 6.5, install Migrate to SQL Server 6.5, install SQL Server 2000, and then use the SQL Server 2000, and then use the SQL Server 2000 Upgrade wizardSQL Server 2000 Upgrade wizard

SQL Server 6.5SQL Server 6.5 Install SQL Server 2000 and then use the Install SQL Server 2000 and then use the

SQL Server 2000 Upgrade wizardSQL Server 2000 Upgrade wizard

SQL Server 7.0SQL Server 7.0 Use the SQL Server 2000 setup programUse the SQL Server 2000 setup program

AgendaAgenda

安装安装 // 升升级级 管理管理工具工具 引引擎功能的增强擎功能的增强 支持支持 XMLXML 复复制制 Analysis ServicesAnalysis Services Desktop Desktop 与与 Windows CEWindows CE

管理管理 // 工具工具平平台台 支持多实例支持多实例

在每台机器上所有的在每台机器上所有的 SQL Server SQL Server 实例只需要一实例只需要一套管理工具套管理工具

支支持的平台持的平台 Window 98, Windows 98 SE, Windows MeWindow 98, Windows 98 SE, Windows Me Windows NT 4.0 SP5 and higher (including Windows NT 4.0 SP5 and higher (including

Embedded)Embedded) Windows 2000 (including Terminal Server)Windows 2000 (including Terminal Server)

IE 5 Required (for Unicode and XML IE 5 Required (for Unicode and XML parser)parser)

MMC 1.2MMC 1.2 MDAC 2.6(MDAC 2.6( 微软数据访问组件微软数据访问组件 ))

管理管理 // 工具工具SQL SQL 企业管理器企业管理器 UI Support For UI Support For

Attach / Detach databaseAttach / Detach database Offline databaseOffline database Column-level permission (new to SQL Column-level permission (new to SQL

Server 2000)Server 2000) Server Network utilityServer Network utility Register server using AD browserRegister server using AD browser

Copy Database WizardCopy Database Wizard Visual Data Tools Full Unicode SupportVisual Data Tools Full Unicode Support Cluster-AwareCluster-Aware

管理管理 // 工具工具Copy Database WizardCopy Database Wizard 允许允许 DBA DBA 在在 SQLServer SQLServer 服务器或实例服务器或实例

的之间拷贝或者移动数据库的之间拷贝或者移动数据库 Move or Copy 1 or Move or Copy 1 or NN Database(s) Database(s)

From SQL Server 7.0 / 2000 to 2000From SQL Server 7.0 / 2000 to 2000 Copy and resolve logins if not present on Copy and resolve logins if not present on

destinationdestination Optionally copy global namespace Optionally copy global namespace

objects from master, error messages and objects from master, error messages and jobs from msdbjobs from msdb

Restartable OperationsRestartable Operations

管理管理 // 工具工具Copy Database WizardCopy Database Wizard

管理管理 // 工具工具与活动目录的集成与活动目录的集成

AgendaAgenda

安装安装 // 升升级级 管理管理工具工具 引引擎功能的增强擎功能的增强 支持支持 XMLXML 复复制制 Analysis ServicesAnalysis Services Desktop Desktop 与与 Windows CEWindows CE

Automatic Cascading of Deletes and Automatic Cascading of Deletes and Updates from PK to FK TablesUpdates from PK to FK Tables

ANSI Standard Restrict and Cascade ANSI Standard Restrict and Cascade SemanticsSemantics

CREATE TABLE country ( country_name NVARCHAR(75) NOT NULL PRIMARY KEY )

CREATE TABLE employee ( employee_name NVARCHAR(75) NOT NULL, country NVARCHAR(75) NOT NULL REFERENCES country ON UPDATE CASCADE ON DELETE NO ACTION, passport_number VARCHAR(25) NOT NULL, PRIMARY KEY (nationality, passport_number))

引引擎功能的增强擎功能的增强Cascaded DRICascaded DRI

引引擎功能的增强擎功能的增强User-Defined FunctionsUser-Defined Functions Multi-Statement T-SQL RoutinesMulti-Statement T-SQL Routines Scalar-ValuedScalar-Valued

Select f(c1) …Select f(c1) … Select … where f2(c2)Select … where f2(c2) Usable in any expression (Order By, Usable in any expression (Order By,

Group By..)Group By..)

Table-Valued (Also Called Relational) Table-Valued (Also Called Relational) Select c1 from f(arg)…Select c1 from f(arg)…

引引擎功能的增强擎功能的增强Column-Level CollationsColumn-Level Collations Multilingual Applications, Multilingual Applications,

Application Hosting, and Server Application Hosting, and Server ConsolidationConsolidation

Per-Database CollationsPer-Database Collations Multiple apps with different collationsMultiple apps with different collations

Per-Column CollationsPer-Column Collations Deeper multi-lingual applicationsDeeper multi-lingual applications

Attach and Restore Databases with Attach and Restore Databases with Different Collations from ServerDifferent Collations from Server

引引擎功能的增强擎功能的增强Instead-Of TriggersInstead-Of Triggers Trigger Executed Instead of Trigger Executed Instead of

INSERT, DELETE, or UPDATE INSERT, DELETE, or UPDATE OperationOperation

ApplicationApplication Allows any view to be updateableAllows any view to be updateable Implement before triggersImplement before triggers

Supported on View or TableSupported on View or Table Inserted / Deleted Tables AvailableInserted / Deleted Tables Available

引引擎功能的增强擎功能的增强新数据类型新数据类型 TableTable

Return type for table-valued UDFsReturn type for table-valued UDFs Allows easier programming of iterative Allows easier programming of iterative

operationsoperations

BigIntBigInt 8-byte integer8-byte integer

SQL_VariantSQL_Variant Can store any base type Can store any base type （（ except text, except text,

ntext, timestamp, sql_variant)ntext, timestamp, sql_variant) Can be used to implement an open schemaCan be used to implement an open schema

User DefineUser Define

引引擎功能的增强擎功能的增强Partitioned View EnhancementsPartitioned View Enhancements

Partitioned View: A View of the Partitioned View: A View of the FormForm

SELECT CSELECT C11, C, C22,…, C,…, Cnn FROM T FROM T1 1

UNION ALLUNION ALL

SELECT CSELECT C11, C, C22,…, C,…, Cnn FROM T FROM T2 2

UNION ALLUNION ALL

........

SELECT CSELECT C11, C, C22,…, C,…, Cnn FROM T FROM Tnn

引引擎功能的增强擎功能的增强Partitioned View EnhancementsPartitioned View Enhancements Query Processor Eliminates Query Processor Eliminates

Member Tables Not Necessary for Member Tables Not Necessary for Given Query or UpdateGiven Query or Update

Can Be Used to Implement Can Be Used to Implement Partitioned Data Across Multiple Partitioned Data Across Multiple Tables in the Same or Different Tables in the Same or Different ServersServers

Distributed Partitioned ViewsDistributed Partitioned Views ““Scale Out” feature that allowed us to Scale Out” feature that allowed us to

achieve the record TPC-C numbersachieve the record TPC-C numbers

引引擎功能的增强擎功能的增强Partitioned View EnhancementsPartitioned View Enhancements

FeatureFeature SQL Server 7.0SQL Server 7.0 SQL Server 2000SQL Server 2000

Local-only versus Local-only versus distributeddistributed

Local-onlyLocal-only Distributed: base Distributed: base tables from tables from multiple SQL multiple SQL Server Server computerscomputers

(Enterprise (Enterprise Edition only)Edition only)

UpdateableUpdateable Not updateableNot updateable UpdateableUpdateable

Member table Member table eliminationelimination

Compile-time onlyCompile-time only Both compile-Both compile-time (constants) time (constants) and run-time and run-time (parameters)(parameters)

引引擎功能的增强擎功能的增强在计算列上建索引在计算列上建索引 Computed Columns Were Computed Columns Were

Introduced in SQL Server 7.0Introduced in SQL Server 7.0 SQL Server 2000 Allows You to SQL Server 2000 Allows You to

Create Indexes on Computed Create Indexes on Computed ColumnsColumns

引引擎功能的增强擎功能的增强视图上建索引视图上建索引 Creating an Index on a View – Creating an Index on a View –

ConsiderationsConsiderations SET options consideration SET options consideration

and DETERMINISM and DETERMINISM CREATE VIEW with SCHEMABINDINGCREATE VIEW with SCHEMABINDING CREATE INDEXCREATE INDEX

AgendaAgenda

安装安装 // 升升级级 管理管理工具工具 引引擎功能的增强擎功能的增强 支持支持 XMLXML 复复制制 Analysis ServicesAnalysis Services Desktop Desktop 与与 Windows CEWindows CE

XML SupportXML SupportXML Query ResultsXML Query Results SQL Language ExtensionSQL Language Extension

SELECT…SELECT…

FROM…FROM…

WHERE…WHERE…

ORDER BY…ORDER BY…

FOR XML (raw | FOR XML (raw |

auto [, ELEMENTS] |auto [, ELEMENTS] |

explicit)explicit)

[, XMLData][, XMLData]

[, BINARY base64])[, BINARY base64])

XML SupportXML SupportFOR XML – Raw ModeFOR XML – Raw Mode Query:Query:

SELECT CustomerID, OrderIDSELECT CustomerID, OrderID FROM Customers, OrdersFROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerIDWHERE Customers.CustomerID = Orders.CustomerID FOR XML rawFOR XML raw

Result:Result: <row CustomerID=“ALFKI” OrderID=“10643”><row CustomerID=“ALFKI” OrderID=“10643”> <row CustomerID=“ALFKI” OrderID=“10643”><row CustomerID=“ALFKI” OrderID=“10643”> <row CustomerID=“ANATR” OrderID=“10308”><row CustomerID=“ANATR” OrderID=“10308”> …… <row CustomerID=“MAISD” OrderID=“11004”><row CustomerID=“MAISD” OrderID=“11004”>

XML SupportXML SupportFOR XML – Auto ModeFOR XML – Auto Mode Query:Query:

SELECT Customers.CustomerID, OrderIDSELECT Customers.CustomerID, OrderID FROM Customers, OrdersFROM Customers, Orders WHERE Customers.CustomerID = WHERE Customers.CustomerID =

Orders.CustomerIDOrders.CustomerID FOR XML autoFOR XML auto

Result:Result: <Customers CustomerID=“ALFKI”><Customers CustomerID=“ALFKI”> <Orders OrderID=“10643”><Orders OrderID=“10643”> <Orders OrderID=“10643”><Orders OrderID=“10643”> </Customers></Customers> <Customers CustomerID=“ANATR”><Customers CustomerID=“ANATR”> <Orders OrderID=“10308”><Orders OrderID=“10308”>

XML Support XML Support HTTP Access – URL TypesHTTP Access – URL Types URL QueryURL Query

http://server/vroot?sql=“…”http://server/vroot?sql=“…”

Direct QueryDirect Query http://server/vroot/dbobject/http://server/vroot/dbobject/xpathxpath

TemplateTemplate http://server/vroot/vname?http://server/vroot/vname?paramsparams

XML ViewXML View http://server/vroot/vname/http://server/vroot/vname/xpathxpath??paramsparams

AgendaAgenda

安装安装 // 升升级级 管理管理工具工具 引引擎功能的增强擎功能的增强 支持支持 XMLXML 复复制制 Analysis ServicesAnalysis Services Desktop Desktop 与与 Windows CEWindows CE

ReplicationReplicationGeneral EnhancementsGeneral Enhancements Attach and Go SubscriptionsAttach and Go Subscriptions

Copy a subscription database data fileCopy a subscription database data file Attach it and get a new subscriptionAttach it and get a new subscription Much easier to package and deploy Much easier to package and deploy

subscriptionssubscriptions In SQL Server 7.0 – each subscriber had to first In SQL Server 7.0 – each subscriber had to first

create a database for its subscriptioncreate a database for its subscription

Also acts as a container to make it easy to Also acts as a container to make it easy to create and deploy non-replicated objectscreate and deploy non-replicated objects

AgendaAgenda

安装安装 // 升升级级 管理管理工具工具 引引擎功能的增强擎功能的增强 支持支持 XMLXML 复复制制 Analysis ServicesAnalysis Services Desktop Desktop 与与 Windows CEWindows CE

Analysis ServicesAnalysis ServicesDimension Architecture: ManageabilityDimension Architecture: Manageability No Re-Processing Required ForNo Re-Processing Required For

Modifying slowlyModifying slowlychanging dimensionschanging dimensions

Changing the number of levelsChanging the number of levels Adding virtual dimensions Adding virtual dimensions

Support for Hierarchies inSupport for Hierarchies inVirtual Dimensions Virtual Dimensions

Support for Very Large DimensionsSupport for Very Large Dimensions Use ROLAP for > 10M membersUse ROLAP for > 10M members Use automatic bucketingUse automatic bucketing

Analysis ServicesAnalysis ServicesDimension Architecture: CompletenessDimension Architecture: Completeness Ragged and Unbalanced HierarchiesRagged and Unbalanced Hierarchies Custom Roll-upsCustom Roll-ups Write-Back to DimensionsWrite-Back to Dimensions Easy to Define and Maintain Multiple Easy to Define and Maintain Multiple

Hierarchies in a DimensionHierarchies in a Dimension Multiple hierarchies now used inMultiple hierarchies now used in

designing aggregationsdesigning aggregations 128 Dimensions Per Cube128 Dimensions Per Cube 128 Levels Per Dimensions - 256 Per 128 Levels Per Dimensions - 256 Per

CubeCube

Analysis ServicesAnalysis Services“Ragged” Dimensions“Ragged” Dimensions

S F L A

C A

R ed m on d

W A

U S A

Te l A viv H a ifa

Is rae l

A ll

CountryCountry

StateState

CityCity

Variable DepthVariable Depth

NoNoStates!States!

Analysis ServicesAnalysis Services“Parent-Child” Dimensions“Parent-Child” Dimensions

U nbalanced H ierarchy

L a ura C a lah an

Ja ne t Le verlingV P E a s te rn R e g ion

N a n cy D a vo lio

M ich ae l S uya m a R o b ert K ing A n n e D o d sw o rth

S tep h en B u ch an anV P C en tra l R e g ion

M a rg are t P ea co ckE xe cu tive A ss is ta n t

A n d re w F u lle rC E O

Emp ID Name Manager ID1 Andrew Fuller (NULL)2 Janet Leverling 13 Stephen Buchanan 14 Margaret Peacock 15 Laura Calahan 26 Michael Suyama 37 Robert King 38 Anne Dodsworth 39 Nancy Davolio 6

Analysis ServicesAnalysis ServicesMaking Data Mining EasyMaking Data Mining Easy Direct Support for Relational and Multi-Direct Support for Relational and Multi-

Dimensional (OLAP) DataDimensional (OLAP) Data No more text import routines!No more text import routines!

Wizards for Setting Model Parameters, Wizards for Setting Model Parameters, Without Being an Algorithm ExpertWithout Being an Algorithm Expert

Data Mining on OLAP Data Data Mining on OLAP Data Automatically Uses the Semantics Automatically Uses the Semantics Already in the CubeAlready in the Cube

Mining Model

Analysis ServicesAnalysis ServicesData Mining Process IllustratedData Mining Process Illustrated

DMEngine

Data to Predict

DMEngine

Predicted Data

Training Data

Mining Model

Mining Model

Analysis ServicesAnalysis ServicesA DMM Is a TableA DMM Is a Table A DMM Structure Is Defined as a TableA DMM Structure Is Defined as a Table

Training a DMM means inserting data into Training a DMM means inserting data into the tablethe table

Predicting from a DMM means querying Predicting from a DMM means querying the tablethe table

All Information Describing the Case Is All Information Describing the Case Is Contained in ColumnsContained in Columns

All of the Operations (Creation, All of the Operations (Creation, Training, Predictions) Will Treat the Training, Predictions) Will Treat the DMM As a “Table”DMM As a “Table”

Analysis ServicesAnalysis ServicesEnglish Query IntegrationEnglish Query Integration English Query Is a Component of English Query Is a Component of

SQL ServerSQL Server Engine Engine

Translates English to SQL or MDX Translates English to SQL or MDX

Modeling Tool Modeling Tool Development environment for defining Development environment for defining

semantics of a database (entities and semantics of a database (entities and relationships)relationships)

Now integrated with the Visual Studio shellNow integrated with the Visual Studio shell

Analysis ServicesAnalysis ServicesInternet Support on ClientInternet Support on Client Uses HTTP to Pass Through FirewallsUses HTTP to Pass Through Firewalls Uses IIS to Provide Authentication Over Uses IIS to Provide Authentication Over

the Internetthe Internet Great Scalability Great Scalability –– Middle Tier Is Not a Middle Tier Is Not a

BottleneckBottleneck Supports a “Thick Client Model” – Pivot Supports a “Thick Client Model” – Pivot

Table Service on ClientTable Service on Client

““All the power of the client is available”All the power of the client is available”

OLAPServer

IISIIS

“Data server in the sky”

Analysis ServicesAnalysis ServicesHTTP Connectivity: ArchitectureHTTP Connectivity: Architecture

ConnectionString = “Provider = MSOLAP; ConnectionString = “Provider = MSOLAP; Data Source = Data Source = http://www.cubeserver.comhttp://www.cubeserver.com; …; …

msolap.asp

PPTTSS

PPTTSS

AgendaAgenda

安装安装 // 升升级级 管理管理工具工具 引引擎功能的增强擎功能的增强 支持支持 XMLXML 复复制制 Analysis ServicesAnalysis Services Desktop Desktop 与与 Windows CEWindows CE

Desktop and Windows CEDesktop and Windows CEWhat’s New on the DesktopWhat’s New on the Desktop Multi-Instance SupportMulti-Instance Support Windows Installer-Based SetupWindows Installer-Based Setup Same Feature Set As the Server Same Feature Set As the Server

VersionVersion T-SQLT-SQL XMLXML DTSDTS DMODMO Replication enhancementsReplication enhancements

Some Limitations Based on OS Some Limitations Based on OS Support (AD, etc.)Support (AD, etc.)

Desktop and Windows CE Desktop and Windows CE Windows CE EditionWindows CE Edition Upward-Compatibility with SQL ServerUpward-Compatibility with SQL Server

Same resultsetsSame resultsets Compatible data typesCompatible data types

Bi-Directional Merge ReplicationBi-Directional Merge Replication Remote Data AccessRemote Data Access Right Footprint Size for DevicesRight Footprint Size for Devices

Approximately 1MBApproximately 1MB

OS SupportOS Support Windows CE 2.11 forwardWindows CE 2.11 forward

Questions?Questions?

More ResourcesMore Resources

msdn.microsoft.commsdn.microsoft.com

www.microsoft.com/sqlwww.microsoft.com/sql

msdn.microsoft.com/sqlservermsdn.microsoft.com/sqlserver

www.microsoft.com/technet/sqlwww.microsoft.com/technet/sql

msdn.microsoft.com/xmlmsdn.microsoft.com/xml