Microsoft SQL Server 2000 New Features and Enhancements

Microsoft SQL Server 2000 New Features and Enhancements 10 June 2002 David Rodriguez Microsoft SQL Server Technology Specialist

What You Will See Today u New and Enhanced Features in SQL Server 2000, Including Ø Ø Ø Multiple instances XML Cascading RI Replication Analysis Services and Data Mining

Agenda u u u u Product Roadmap Installation/Upgrade Administration/Tools Engine Enhancements XML Support Replication Analysis Services Desktop and Windows CE

SQL Server 2000 A Major Release n n n XML support n SELECT … FOR XML n Open. XML n XML Views n XML Updategrams n XML View Mapper n XML Bulk Load URL and HTTP db access HTTP access to cubes Multi-instance support Integrated Data Mining Full-Text Search in formatted docs English Query for the Web C 2 security rating (NSA) Installation disk imaging Active Directory integration Self-management and tuning n n n n n Distributed Partitioned Views Log Shipping Parallel CREATE INDEX Parallel scan Parallel DBCC Failover clustering Failover cluster management 32 CPU SMP system support 64 GB RAM support VI SAN support Indexed views ROLAP dimension storage Distributed Partitioned Cubes Online index reorganization Differential backup User-defined functions Server-less snapshot backup SQL Query Analyzer debugger n n n n New data types Column-level collations Virtual Cube Editor Linked cubes MDX Builder Dimensions Security in Analysis Services OLAP Actions Custom rollups Cascading referential integrity and actions INSTEAD OF triggers Indexes on computed columns Queued replication DTS enhancements Online reorg Copy Database Wizard

SQL Server Product Roadmap Since RTM CY 2002 SQLXML 1. 0 v XML Updategrams v XML Bulk Load Driver for JDBC XML View Mapper 1. 0 SQL Server CE 2. 0 Web Services Toolkit SQL Server “Yukon” 2003 Programmability v Deep XML support v Common Language Runtime integration Service Pack 1 Notification Services Scalability v Self-tuning SQLXML 2. 0 SQL Server 2000 64 -bit Availability v Online operations Service Pack 2 Business Intelligence v End to End data warehousing

Installation/Upgrade Multi-Instances u Default Instances Ø u Multi-Instances Ø Ø u Prior versions of SQL Server Multi-instance is a paradigm A named instance is an installation SQL Server 2000 Setup Can Create a Default Instance and Named Instances

Installation/Upgrade Instances u Default Ø Ø u Only one active Version switch Named Ø { SQL Server 6. 5 SQL Server 7. 0 or 2000 { All instances can be active SQL Server 2000 Up to 15 SQL Server 2000

Installation/Upgrade A “Default” Instance u A “Default” Instance Ø Looks like SQL Server 7. 0 did Ø Ø Ø Has an MSSQLServer and SQLServer. Agent service Registry keys are basically the same Ø HKLM/SW/Microsoft/MSSQLServer Connects to the server with just the server name Ø osql -E -Sservername

Installation/Upgrade “Named” Instances u A “Named” Instance Ø Additional SQL Server service on the same computer Ø Ø Services reflect the instance name Ø Ø MSSQL$SQL 2000, SQLAgent$SQL 2000 Registry keys reflect the name Ø Ø For example, connect to instance “SQL 2000” Ø osql -E -SservernameSQL 2000 HKLM/software/Microsoft SQL Server/SQL 2000 File path reflects the name Ø C: Program FilesMicrosoft SQL ServerMSSQL$SQL 2000

Installation/Upgrade Failover Clustering Installation u u Improvements Over Prior Versions of SQL Server No External Wizard Ø u Cluster Management Ø Ø Ø u “Just Click Next” Add and remove nodes Multiple IP addresses Multiple virtual servers – each with a single instance No Unclustering in SQL Server 2000 Service Packs

Installation/Upgrade Strategies u Versions Earlier than SQL Server 6. 5 Ø u Migrate to SQL Server 6. 5, install SQL Server 2000, and then use the SQL Server 2000 Upgrade wizard Install SQL Server 2000 and then use the SQL Server 2000 Upgrade wizard SQL Server 7. 0 Ø Use the SQL Server 2000 setup program

Administration/Tools SQL Enterprise Manager u UI Support For Ø Ø Ø u u u Attach / Detach database Offline database Column-level permission (new to SQL Server 2000) Server Network utility Register server using AD browser Copy Database Wizard Visual Data Tools Full Unicode Support Cluster-Aware, Incl. Service Manager

Administration/Tools Copy Database Wizard u u Allows DBA to Copy or Move Databases Between SQL Server Computers or SQL Server Instances Move or Copy 1 or N Database(s) Ø Ø Ø u From SQL Server 7. 0 / 2000 to 2000 Copy and resolve logins if not present on destination Optionally copy global namespace objects from master, error messages and jobs from msdb Restartable Operations

Administration/Tools Data Transformation Services u u u Multi-Phase Data Pump (Package / Step Restart Ability, Aggregation) DTS Package – Save as VB Support for Run-Time Parameters Improved Usability / Performance New Custom Tasks New Custom Transformations

Administration/Tools SQL Query Analyzer u Increased Usability / Authoring Ø Ø Multiple result sets to grid Editor enhancements Ø Ø u Object Browser Ø Ø u u Block indent, block comment out Bookmarks in editor Drag / drop scripting, editing Open Table (table editor) Extended Property Editor Browser docked left or right in shell Object Search Templates and Parameter Search / Replace

Administration/Tools SQL Query Analyzer u Integrated Statement Tracing Ø u u u Complete Integration of Tuning Wizard Instead of Index Analysis Client Statistics (Client-Side Performance Counters) Shortcut Queries Ø Ø u Basic statement, duration, CPU, I/O Assign SPs, XPs, short SQL Statements to predefined key combinations Alt + F 1, Ctrl + 1 … 0 Custom Commands Ø Ø Basic shell out like Microsoft Visual Studio 6. 0 Command-line version control integration

Administration/Tools SQL Query Analyzer u Open Table with Updateable Grid Ø u Integrated T-SQL Debugger Ø Ø u u Need PK or unique index on table, using server-side keyset cursor Stored procedure debugging Global, local variables, call stack Version-Dependent and Case-Sensitive Color Coding for SQL Server 6. 5, 7. 0, and 2000 Manage Indexes, Index Distribution Information

Administration/Tools Index Tuning Wizard u u u Added Support for Indexed Views and Indexes on Computed Columns Projected Volumes (Via Scaling Factor) Index Analysis in Query Analyzer Replaced With Full Index Tuning Wizard Ø Ø u Works on current script file or Highlighted selection within script Display Indexes to Be Deleted on Recommendation Page

Administration/Tools Auditing u u Driven by C 2 Security Implemented Using SQL Trace Ø Ø Ø File roll-over on size Shut down SQL Server when out of space SQL Profiler can be used to read audit trail No separate auditing UI in this release C 2 auditing via switch on the server exec sp_configure 'c 2 audit mode', 1 Finer-level granularity available via auto starting traces

Administration/Tools SQL Profiler u Trace Templates Ø Ø u u u Replaces Create Trace wizard and Trace definitions saved in the Registry Canned scenario-based traces, like Dead. Lock, Replay, Tuning, Stored Procedure (steps), etc. Support Server-Side Trace File Creation Table and File as Trace Destination Support for Large Traces / Replays

Administration/Tools Scripting Options u SQL-DMO Ø Ø u Introduced in SQL Server 6. 0 (SQL OLE) Abstract server changes WMI (Windows Management Instrumentation) Ø Ø Ø Core component of Windows 2000 and available for all other Windows platforms “The Way” to manage everything SQL Server is just one of many providers

Administration/Tools Active Directory Integration u Service Publication for SQL Server Ø u Domain-Wide “Locator” Service Ø u Ø 1. Directory lookup 2. Location transparency – “HR” Server Generic AD SQL Server Browser UI Ø u “What SQL Servers are on the network? ” Objectives Ø u Also Databases, Replication Publications Integrated within all management tools Schema Ships with Windows 2000

Administration/Tools DEMO 2: Administration Tools u Enterprise Manager Ø Ø u Active Directory Copy Database wizard Query Analyzer Ø Ø Ø Object browser, object search Templates Debugger

Agenda u u u u Installation/Upgrade Administration/Tools Engine Enhancements XML Support Replication Analysis Services Desktop and Windows CE

Engine Enhancements Cascaded DRI u u Automatic Cascading of Deletes and Updates from PK to FK Tables ANSI Standard Restrict and Cascade Semantics 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))

Engine Enhancements User-Defined Functions u u Multi-Statement T-SQL Routines Scalar-Valued Ø Ø Ø u Table-Valued (Also Called Relational) Ø u Select c 1 from f(arg)… Strongly Typed Input Args with Return Value Ø u Select f(c 1) … Select … where f 2(c 2) Usable in any expression (Order By, Group By. . ) No output parameters Inline Relational Functions Ø Effectively a parameterized view

Engine Enhancements Scalar UDF Example CREATE FUNCTION Extract. Name. Part(@In. Name varchar(100), @part tinyint) RETURNS varchar(30) AS BEGIN DECLARE @offset tinyint SET @offset = charindex(' ', @In. Name) RETURN CASE @part WHEN 1 THEN substring(@In. Name, 1, @offset-1) WHEN 2 THEN substring(@In. Name, @offset+1, len(@In. Name)) ELSE NULL END

Engine Enhancements Column-Level Collations u u Multilingual Applications, Application Hosting, and Server Consolidation Per-Database Collations Ø u Per-Column Collations Ø u u Multiple apps with different collations Deeper multi-lingual applications Attach and Restore Databases with Different Collations from Server Full Backward-Compatibility

Engine Enhancements Instead-Of Triggers u u Trigger Executed Instead of INSERT, DELETE, or UPDATE Operation Application Ø Ø u u Allows any view to be updateable Implement before triggers Supported on View or Table Inserted / Deleted Tables Available

Engine Enhancements New Data Types u Big. Int Ø u SQL_Variant Ø Ø u Can store any base type (except LOB) Can be used to implement an open schema Table Ø Ø u 8 -byte integer Return type for table-valued UDFs Allows easier programming of iterative operations Rowversion (new alias for Time. Stamp)

bigint Data Type u u u 8 byte integer -263 (-9, 223, 372, 036, 854, 775, 808) through 263 -1 ( 9, 223, 372, 036, 854, 775, 807) Example: DECLARE @My. Bigint. Var bigint SET @ My. Bigint. Var = 97876908907 u sysindexes. rowcount is now a bigint rather than binary(8)

sql_variant Data Type u u Can store any of the base SQL Server types except BLOB data or timestamp Similar to VB variant data type Ø u A single column, parameter, or variable storing data values of different data types Each instance stores two items: Ø Ø Data value Metadata describing the value Ø u u Base data type, scale, precision, and collation Maximum length is 8016 bytes sysproperties. value is the only system table sql_variant column

table Data Type u Can ONLY be used to define: Ø Ø u Includes definitions of: Ø Ø u u u Local variables The return value of a user-defined function Columns, their data type, precision, and scale Optional PRIMARY KEY, UNIQUE, and CHECK constraints New way to work with temporary result sets Can be used to replace temporary tables Scope is the batch, stored procedure, or function Ø Not affected by transaction rollback or commit

Using table Data Type vs. Temporary Table Scope Multi-session Recompile overhead Transaction / Logging Indexes Temporary Table Entire session from CREATE Global temporary tables Each DDL reference in sp Same as permanent tables No limitations Insert / Exec Yes Select Into Index Statistics Stored in tempdb Yes Yes table Variable Batch, sp, or function only No None Only for duration of statement Only indexes for PK, Unique No, but function can return table No No Yes

rowversion Data Type u u u Synonym / Replacement for timestamp Otherwise currently identical to timestamp From BOL: The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type. A future release of Microsoft® SQL Server™ may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type. Microsoft® SQL Server™ 2000 introduces a rowversion synonym for the timestamp data type. Use rowversion instead of timestamp wherever possible in DDL statements. rowversion is subject to the behaviors of data type synonyms. For more information, see Data Type Synonyms. In a CREATE TABLE or ALTER TABLE statement, you do not have to supply a column name for the timestamp data type: CREATE TABLE Example. Table (Pri. Key int PRIMARY KEY, timestamp) If you do not supply a column name, SQL Server generates a column name of timestamp. The rowversion data type synonym does not follow this behavior. You must supply a column name when you specify rowversion.

Engine Enhancements Partitioned View Enhancements u Partitioned View: A View of the Form SELECT C 1, C 2, …, Cn FROM T 1 UNION ALL SELECT C 1, C 2, …, Cn FROM T 2 UNION ALL. . SELECT C 1, C 2, …, Cn FROM Tn Ø Where T 1, … Tn have disjoint constraints on C 2

Engine Enhancements Indexes on Computed Columns u u Computed Columns Were Introduced in SQL Server 7. 0 SQL Server 2000 Allows You to Create Indexes on Computed Columns Ø Ø The expression defining the computed column must be DETERMINISTIC Certain SET options must be specified

Engine Enhancements Indexes on Views u Defining an Index on a View Ø Ø Ø A. k. a. , Materialized views The view may be a join, an aggregation, or their combination Once the index is created, the contents of the view are persisted The index is maintained automatically, as with any other index The optimizer may use the index on a view even if the view is not directly referenced in the query

XML Support XML Query Results u SQL Language Extension SELECT… FROM… WHERE… ORDER BY… FOR XML (raw | auto [, ELEMENTS] | explicit) [, XMLData] [, BINARY base 64])

XML Support FOR XML – Raw Mode u u One <Row> Element Per Row in the Result Set No Nested Elements Columns / Values in the Result Set Are Attributes / Values on the <Row> Similar to CSV but in XML Format

XML Support FOR XML – Auto Mode u u u Table / View Name in the Database Used for the Element Name Column Names Used for Attributes Supports Nested XML Output Ø Ø u Nesting determined by ordering of columns in SELECT clause Sibling relationships not supported Change Names Using Table and Column Aliases

XML Support FOR XML – Explicit Mode u u Provides Complete Control Over Format of XML Result Columns Can Be Individually Mapped to Attributes or Sub-Elements Ø Ø u u Supports arbitrary nesting Sibling relationships supported Collapses / Hoists Hierarchy Constructs ID/IDREF Relationships CDATA Sections in XML Output Stores XML Annotations

XML Support Open. XML u u Used with T-SQL Stored Procedures Provides a Relational View on XML Specifies Row and Column Selectors Using an XPath Supports Ø Ø Ø Attribute and element-centric mappings XML annotation / overflow column Hierarchy support

XML Support HTTP Access – URL Types u URL Query Ø u Direct Query Ø u http: //server/vroot/dbobject/xpath Template Ø u http: //server/vroot? sql=“…” http: //server/vroot/vname? params XML View Ø http: //server/vroot/vname/xpath? params

SQL XML 3. 0 u u Web services (SOAP) support Web services support offers SQL Server functionality to the client Ø Ø Ø u XML views using annotated XSD schemas Ø u u can send SOAP HTTP requests to the server that is running SQLXML to execute stored procedures user-defined functions (UDFs) XML templates Create XML views of relational data by using annotated XML Schema Definition language (XSD) schemas. Client-side XML formatting In SQL Server 2000 Data access components Ø Ø SQLXMLOLEDB Provider This OLE DB provider supports clientside and server-side XML formatting of the rowsets that are generated by SQL Server. SQLXML Managed Classes exposes the functionality of SQLXML within the Microsoft. NET Framework. Ø Ø can write a C# application to access XML data from an instance of SQL Server, bring the data into the. NET environment, process the data, and then send the data back in a XML document (the Diff. Gram) to apply the updates to SQL Server. Diff. Grams - Data. Set component of the Microsoft. NET Framework.

Replication General Enhancements u Attach and Go Subscriptions Ø Ø Ø Copy a subscription database data file Attach it and get a new subscription Much easier to package and deploy subscriptions Ø Ø In SQL Server 7. 0 – each subscriber had to first create a database for its subscription Also acts as a container to make it easy to create and deploy non-replicated objects

Replication Schema Replication u Add / Drop Column Ø Ø u Automatically added to published articles Supported by replication stored procedures No need to Drop/Add publications because a new column is added to a table One of the top requests today On-Demand Script Execution Ø Post a general SQL script for execution at all subscribers

Analysis Services Dimension Architecture: Manageability u No Re-Processing Required For Ø Ø Ø u u Modifying slowly changing dimensions Changing the number of levels Adding virtual dimensions Support for Hierarchies in Virtual Dimensions Support for Very Large Dimensions Ø Ø Use ROLAP for > 10 M members Use automatic bucketing

Analysis Services Dimension Architecture: Completeness u u Ragged and Unbalanced Hierarchies Custom Roll-ups Write-Back to Dimensions Easy to Define and Maintain Multiple Hierarchies in a Dimension Ø u u Multiple hierarchies now used in designing aggregations 128 Dimensions Per Cube 128 Levels Per Dimensions - 256 Per Cube

Analysis Services Security u Authentication Mode Ø Ø Windows NT integrated Using Internet Information Server Ø u Cell Security Ø u Declare any set of cell ranges in the cube as protected Dimension Member Security Ø Ø u Windows NT challenge/response; Anonymous; SSL Hide parts (or all) of the dimension members “Shrinks” the multi-dimensional space Intuitive Visual Tools to Define Security Permissions

Analysis Services Data Mining Definition u Deducing Knowledge by Examining Data Ø Ø u “Knowledge” = patterns, clusters, rules, decision trees, association rules “Examining data” = scanning samples of known facts about “cases” Once the “Knowledge” Is Extracted, It Is Used To Ø Ø Ø Make predictions about new data Explain existing data Visualize data to aid humans

Analysis Services Data Mining Process Illustrated Training Data DM Engine Mining Model Data to Predict DM Engine Predicted Data

Analysis Services English Query Integration u u English Query Is a Component of SQL Server Engine Ø u Translates English to SQL or MDX Modeling Tool Ø Ø Development environment for defining semantics of a database (entities and relationships) Now integrated with the Visual Studio shell

Analysis Services Internet Support on Client u u Uses HTTP to Pass Through Firewalls Uses IIS to Provide Authentication Over the Internet Great Scalability – Middle Tier Is Not a Bottleneck Supports a “Thick Client Model” – Pivot Table Service on Client “All the power of the client is available”

Analysis Services HTTP Connectivity: Architecture P Connection. String = “Provider = MSOLAP; T Data Source = http: //www. cubeserver. com; S … IIS OLAP Server msolap. asp “Data server in the sky” P T S

Desktop and Windows CE What’s New on the Desktop u u u Multi-Instance Support Windows Installer-Based Setup Same Feature Set As the Server Version Ø Ø Ø u T-SQL XML DTS DMO Replication enhancements Some Limitations Based on OS Support (AD, etc. )

Desktop and Windows CE Edition u Upward-Compatibility with SQL Server Ø Ø u u u Bi-Directional Merge Replication Remote Data Access Right Footprint Size for Devices Ø u Same resultsets Compatible data types Approximately 1 MB OS Support Ø Windows CE 2. 11 forward

Questions?

More Resources msdn. microsoft. com www. microsoft. com/sql msdn. microsoft. com/sqlserver www. microsoft. com/technet/sql msdn. microsoft. com/xml www. microsoft. fr/olymars/webupdate. xml