Microsoft SQL Server 2000 New Features and Enhancements 10 June 2002 - PowerPoint PPT Presentation

About This Presentation
Title:

Microsoft SQL Server 2000 New Features and Enhancements 10 June 2002

Description:

Microsoft SQL Server 2000 New Features and Enhancements 10 June 2002 David Rodriguez Microsoft SQL Server Technology Specialist What You Will See Today New and ... – PowerPoint PPT presentation

Number of Views:75
Avg rating:3.0/5.0
Slides: 61
Provided by: OlegDi1
Category:

less

Transcript and Presenter's Notes

Title: Microsoft SQL Server 2000 New Features and Enhancements 10 June 2002


1
Microsoft SQL Server 2000New Features and
Enhancements 10 June 2002
David Rodriguez Microsoft SQL Server Technology
Specialist
2
What You Will See Today
  • New and Enhanced Features in SQL Server 2000,
    Including
  • Multiple instances
  • XML
  • Cascading RI
  • Replication
  • Analysis Services and Data Mining

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

4
SQL Server 2000A Major Release
  • 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
  • XML support
  • SELECT FOR XML
  • OpenXML
  • XML Views
  • XML Updategrams
  • XML View Mapper
  • 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
  • C2 security rating (NSA)
  • Installation disk imaging
  • Active Directory integration
  • Self-management and tuning
  • 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

5
SQL ServerProduct Roadmap
Since RTM
CY2002
SQL Server Yukon 2003
  • SQLXML 1.0
  • XML Updategrams
  • XML Bulk Load
  • XML View Mapper 1.0
  • Service Pack 1
  • SQLXML 2.0
  • Service Pack 2
  • Driver for JDBC
  • Web Services Toolkit
  • SQL Server CE 2.0
  • Notification Services
  • SQL Server 2000 64-bit
  • Programmability
  • Deep XML support
  • Common Language Runtime integration
  • Scalability
  • Self-tuning
  • Availability
  • Online operations
  • Business Intelligence
  • End to End data warehousing

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

7
Installation/UpgradeInstances
  • Default
  • Only one active
  • Version switch
  • Named
  • All instances can be active

SQL Server 6.5

SQL Server 7.0 or 2000
SQL Server 2000
SQL Server 2000
SQL Server 2000
Up to 15
SQL Server 2000
8
Installation/UpgradeA Default Instance
  • A Default Instance
  • Looks like SQL Server 7.0 did
  • Has an MSSQLServer and SQLServerAgent service
  • Registry keys are basically the same
  • HKLM/SW/Microsoft/MSSQLServer
  • Connects to the server with just the server name
  • osql -E -Sservername

9
Installation/UpgradeNamed Instances
  • A Named Instance
  • Additional SQL Server service on the same
    computer
  • For example, connect to instance SQL2000
  • osql -E -Sservername\SQL2000
  • Services reflect the instance name
  • MSSQLSQL2000, SQLAgentSQL2000
  • Registry keys reflect the name
  • HKLM/software/Microsoft/Microsoft SQL
    Server/SQL2000
  • File path reflects the name
  • C\Program Files\Microsoft SQL Server\MSSQLSQL200
    0

10
Installation/UpgradeServer Referencing
  • Services
  • Default net start MSSQLServer
  • Named net start MSSQLInstance1
  • Applications
  • Default ltComputerNamegt
  • Named ltComputerNamegt\Instance1

11
Installation/UpgradeServer Connectivity Changes
  • Server ABC, Instance SQL2000, Listens On
  • NP \\ABC\pipe\MSSQLSQL2000\sql\query
  • TCP ABC1600
  • UDP Port 1434 Is Used for the New Listener
    Service
  • Exposes which instances are installed, their
    network endpoints, etc., to clients
  • Any server on the system may be listening

12
Installation/UpgradeAdvanced Features
  • Registry Rebuild
  • Reinstallation of Windows
  • Rebuild Master
  • Specify a different code page
  • Severe damage to SQL Server installation
  • This should rarely be needed
  • Collation
  • Windows collation
  • SQL collation

13
Installation/UpgradeDeployment
  • Unattended Installation (.iss File)
  • Supplied or recorded
  • Remote Installation
  • Must be admin on remote machine
  • Mass Deployment Using SMS

14
Installation/UpgradeFailover Clustering
Installation
  • Improvements Over Prior Versions of SQL Server
  • No External Wizard
  • Just Click Next
  • Cluster Management
  • Add and remove nodes
  • Multiple IP addresses
  • Multiple virtual servers each with a single
    instance
  • No Unclustering in SQL Server 2000 Service Packs

15
Installation/UpgradeUpgrade Strategies
  • Versions Earlier than SQL Server 6.5
  • Migrate to SQL Server 6.5, install SQL Server
    2000, and then use the SQL Server 2000 Upgrade
    wizard
  • SQL Server 6.5
  • Install SQL Server 2000 and then use the SQL
    Server 2000 Upgrade wizard
  • SQL Server 7.0
  • Use the SQL Server 2000 setup program

16
Installation/UpgradeOverview of 6.5 Upgrade
Wizard
  • Data File Changes Beginning with SQL Server 7.0
  • Easy Simple Wizard
  • Fast Custom Export and Import
  • Reliable
  • Recovery
  • Object comparison
  • One or Two Machines

17
Installation/UpgradePre-Upgrade Checklist
  • Back Up All SQL Server 6.5 Data Files
  • SQL Server 6.5 Checks
  • Tempdb database at least 10 MB free
  • Master database at least 3 MB free
  • Server is usable
  • Make sure all logins are in master
  • DBCC on all databases
  • User Running the Wizard Must Be an Administrator
    on Both the Import Server and the Export Server

18
Installation/UpgradeSQL Server 7.0 to SQL Server
2000
  • Built into Setup Wizard
  • Effects on Program Files
  • System Data Files Are Updated
  • Unattended Upgrade Supported
  • Restartability
  • Detach SQL Server 7.0 and Reattach to SQL Server
    2000

19
Administration/ToolsPlatforms and Dependencies
  • Multi-Instance Support
  • Only one set of tools installed for all SQL
    Server instances per machine
  • Platform Support
  • Window 98, Windows 98 SE, Windows Me
  • Windows NT 4.0 SP5 and higher (including
    Embedded)
  • Windows 2000 (including Terminal Server)
  • IE 5 Required (for Unicode and XML parser)
  • MMC 1.2
  • MDAC 2.6

20
Administration/ToolsSQL Enterprise Manager
  • UI Support For
  • 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

21
Administration/ToolsCopy Database Wizard
  • Allows DBA to Copy or Move Databases Between SQL
    Server Computers or SQL Server Instances
  • Move or Copy 1 or N Database(s)
  • 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

22
Administration/ToolsCopy Database Wizard
23
Administration/ToolsData Transformation Services
  • 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

24
Administration/ToolsSQL Query Analyzer
  • Increased Usability / Authoring
  • Multiple result sets to grid
  • Editor enhancements
  • Block indent, block comment out
  • Bookmarks in editor
  • Object Browser
  • 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

25
Administration/ToolsSQL Query Analyzer
  • Integrated Statement Tracing
  • Basic statement, duration, CPU, I/O
  • Complete Integration of Tuning Wizard Instead of
    Index Analysis
  • Client Statistics (Client-Side Performance
    Counters)
  • Shortcut Queries
  • Assign SPs, XPs, short SQL Statements to
    pre-defined key combinations
  • Alt F1, Ctrl F1, Ctrl 1 0
  • Custom Commands
  • Basic shell out like Microsoft Visual Studio 6.0
  • Command-line version control integration

26
Administration/ToolsSQL Query Analyzer
  • Open Table with Updateable Grid
  • Need PK or unique index on table, using
    server-side keyset cursor
  • Integrated T-SQL Debugger
  • 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

27
Administration/ToolsIndex Tuning Wizard
  • 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
  • Works on current script file or
  • Highlighted selection within script
  • Display Indexes to Be Deleted on Recommendation
    Page

28
Administration/ToolsAuditing
  • Driven by C2 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
  • C2 auditing via switch on the server exec
    sp_configure 'c2 audit mode',1
  • Finer-level granularity available via auto
    starting traces

29
Administration/ToolsSQL Profiler
  • Trace Templates
  • 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

30
Administration/ToolsScripting Options
  • SQL-DMO
  • 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

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

32
Administration/ToolsActive Directory Search
33
Administration/ToolsDEMO 2 Administration Tools
  • Enterprise Manager
  • Active Directory
  • Copy Database wizard
  • Query Analyzer
  • Object browser, object search
  • Templates
  • Debugger

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

35
Engine EnhancementsCascaded DRI
  • 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))
36
Engine Enhancements User-Defined Functions
  • Multi-Statement T-SQL Routines
  • Scalar-Valued
  • Select f(c1)
  • Select where f2(c2)
  • Usable in any expression (Order By, Group By..)
  • Table-Valued (Also Called Relational)
  • Select c1 from f(arg)
  • Strongly Typed Input Args with Return Value
  • No output parameters
  • Inline Relational Functions
  • Effectively a parameterized view

37
Engine Enhancements Scalar UDF Example
  • CREATE FUNCTION ExtractNamePart(_at_InName
    varchar(100), _at_part tinyint)
  • RETURNS varchar(30) AS
  • BEGIN
  • DECLARE _at_offset tinyint
  • SET _at_offset charindex(' ', _at_InName)
  • RETURN CASE _at_part
  • WHEN 1 THEN substring(_at_InName, 1, _at_offset-1)
  • WHEN 2 THEN substring(_at_InName,_at_offset1,
    len(_at_InName))
  • ELSE NULL
  • END
  • END

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

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

40
Engine Enhancements Instead-Of Trigger Example
  • Updateable Partitioned View
  • Partitioning column Region
  • CREATE VIEW CustomersAll AS
  • SELECT CustomerID, CompanyName, Address,
    Region
  • FROM CustomerEast
  • UNION ALL
  • SELECT CustomerID, CompanyName, Address,
    Region
  • FROM CustomerCentral
  • UNION ALL
  • SELECT CustomerID, CompanyName, Address,
    Region
  • FROM CustomerWest

41
Engine Enhancements Instead-Of Trigger Example
  • CREATE TRIGGER IO_Trig_INS_CustomersAll ON
    CustomersAll
  • INSTEAD OF INSERT AS
  • BEGIN
  • INSERT INTO CustomersEast
  • SELECT CustomerID,CompanyName,Address,Region
  • FROM inserted WHERE Region East
  • INSERT INTO CustomersCentral
  • SELECT CustomerID, CompanyName, Address,Region
  • FROM inserted WHERE Region Central
  • INSERT INTO CustomersWest
  • SELECT CustomerID, CompanyName, Address,Region
  • FROM inserted WHERE Region West
  • END --trigger action

42
Engine Enhancements New Data Types
  • BigInt
  • 8-byte integer
  • SQL_Variant
  • Can store any base type (except LOB)
  • Can be used to implement an open schema
  • Table
  • Return type for table-valued UDFs
  • Allows easier programming of iterative operations
  • Rowversion (new alias for TimeStamp)

43
bigint Data Type
  • 8 byte integer
  • -263 (-9,223,372,036,854,775,808) through
  • 263-1 ( 9,223,372,036,854,775,807)
  • Example
  • DECLARE _at_MyBigintVar bigint
  • SET _at_ MyBigintVar 97876908907
  • sysindexes.rowcount is now a bigint rather than
    binary(8)

44
Using bigint data type
  • Conversion and Promotion
  • Conversion rules are similar to other integer
    data types
  • Default is still int no automatic promotion to
    bigint
  • power ( intx, 2 ) will error if int too large
  • power ( cast (intx as bigint), 2 ) gives bigint
    result
  • New Functions ROWCOUNT_BIG(), COUNT_BIG()
  • A large whole number constants are still
    interpreted as decimal / numeric, but are
    implicitly converted to bigint
  • Application usage
  • SQL Server 2000 OLE DB Provider uses DBTYPE_I8
    (not 18)
  • SQL Server 2000 ODBC Driver uses SQL_BIGINT
  • Converts to numeric (19,0) for 6.x, 7.0 OLE DB
    and ODBC driver
  • Converts to float for 4.21a or earlier DB-Library
    and ODBC driver

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

46
sql_variant Data Type Example A Sparse Table
  • CREATE TABLE ArtCollections ( ID int
    NOT NULL, PropertyName
    varchar(30) NOT NULL,
    PropertyValue sql_variant NOT NULL )
  • -- Creating this table returns this warning
  • The total row size (8075) for table
    'ArtCollections' exceeds the maximum number of
    bytes per row (8060). Rows that exceed the
    maximum number of bytes will not be added.
  • INSERT INTO ArtCollections VALUES ( 1,
    'CollectionType', 'Painting')
  • INSERT INTO ArtCollections VALUES ( 1,
    'PaintingType', 'Oil')
  • INSERT INTO ArtCollections VALUES ( 1, 'Value',
    2500)
  • INSERT INTO ArtCollections VALUES ( 1,
    'DateAcquired', CAST('10/10/1990' AS datetime) )
  • INSERT INTO ArtCollections VALUES ( 2,
    'CollectionType', 'Carving')
  • INSERT INTO ArtCollections VALUES ( 2,
    'Material', 'Stone')
  • INSERT INTO ArtCollections VALUES ( 2,
    'DateAcquired', CAST('1/30/1995' AS datetime) )

47
Using sql_variant Data Type
  • Data Type Rules
  • Cannot be used for identity column
  • Cannot be used in either a primary or foreign key
  • Cannot store BLOB data or a timestamp
  • Cannot be used in a computed column unless
    explicit CASTing is used
  • Many more rules for sql_variant usage see text
    and Books Online
  • Application Usage
  • SQL Server 2000 OLE DB Provider uses
    DBTYPE_SQLVARIANT
  • SQL Server 2000 ODBC Driver uses SQL_SS_VARIANT
  • SQL 7.0 OLE DB Provider, and ODBC Driver converts
    to nvarchar(4000)
  • ODBC driver 6.5 or earlier and DB-Library return
    varchar(255)
  • Use SQL_VARIANT_PROPERTY() to get the metadata
    information for any specific sql_variant instance

48
sql_variant Comparison Rules
  • sql_variant columns can contain values of several
    base data types and collations so special rules
    apply when comparing sql_variant operands
  • Rules apply to operations involving Transact-SQL
    comparison operators, GROUP BY and ORDER BY,
    indexes, max and min functions, UNIONs (without
    ALL), and CASE expressions
  • Data type precedence order is grouped into data
    type families
  • Not the same precedence order as used for other
    operations
  • sql_variant, datetime, approximate number, exact
    number, Unicode, character, binary
  • If families differ, comparison is based on the
    family, not the value
  • The CAST and CONVERT functions support
    sql_variant
  • Best practice is to explicitly convert the
    sql_variant to a base type before comparing (or
    operating) with base type

49
table Data Type
  • Can ONLY be used to define
  • Local variables
  • The return value of a user-defined function
  • Includes definitions of
  • 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

50
table Data Type Examples
  • Example 1
  • DECLARE _at_table_var table (ColA int PRIMARY KEY,
    ColB char(10) )
  • INSERT INTO _at_table_var VALUES (1, TESTING)
  • Example 2
  • CREATE FUNCTION MyFunc ( _at_var1 int )
  • RETURNS _at_table_var table (ColA int PRIMARY KEY,
    ColB char(10) )
  • AS
  • BEGIN
  • INSERT INTO _at_table_var VALUES (_at_var1, TESTING)
  • RETURN
  • END

51
Using table Data Type
  • Used ONLY in these statements
  • In the SELECT statement
  • except SELECT INTO table_var
  • In the INSERT statement
  • except INSERT table_var EXEC stored_procedure
  • UPDATE, DELETE, and DECLARE CURSOR statements
  • Rules
  • Local scope Not part of transaction
  • No assignment
  • No indexes or constraints after creation
  • Primary key Unique indexes only no statistics
    kept
  • Text in row option 256
  • No text operations such as READTEXT or WRITETEXT

52
Using table Data Type vs. Temporary Table
Temporary Table table Variable
Scope Entire session from CREATE Batch, sp, or function only
Multi-session Global temporary tables No
Recompile overhead Each DDL reference in sp None
Transaction / Logging Same as permanent tables Only for duration of statement
Indexes No limitations Only indexes for PK, Unique
Insert / Exec Yes No, but function can return table
Select Into Yes No
Index Statistics Yes No
Stored in tempdb Yes Yes
53
rowversion Data Type
  • Synonym / Replacement for timestamp
  • Otherwise currently identical to timestamp
  • From BOLThe 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 ExampleTable (PriKey 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.

54
Engine Enhancements Partitioned View Enhancements
  • Partitioned View A View of the Form
  • SELECT C1, C2,, Cn FROM T1
  • UNION ALL
  • SELECT C1, C2,, Cn FROM T2
  • UNION ALL
  • ....
  • SELECT C1, C2,, Cn FROM Tn
  • Where T1, Tn have disjoint constraints on C2

55
Engine Enhancements Partitioned View Enhancements
  • Query Processor Eliminates Member Tables Not
    Necessary for Given Query or Update
  • Can Be Used to Implement Partitioned Data Across
    Multiple Tables in the Same or Different Servers
  • Distributed Partitioned Views
  • Scale Out feature that allowed us to achieve
    the record TPC-C numbers announced at Windows
    2000 launch

56
Engine Enhancements Partitioned View Enhancements
Feature SQL Server 7.0 SQL Server 2000
Local-only versus distributed Local-only Distributed base tables from multiple SQL Server computers (Enterprise Edition only)
Updateable Not updateable Updateable
Member table elimination Compile-time only Both compile-time (constants) and run-time (parameters)
57
Engine Enhancements Indexes on Computed Columns
  • 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

58
Engine Enhancements Indexes on Views
  • 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

59
Engine Enhancements Indexes on Views
  • Creating an Index on a View Considerations
  • SET options consideration and DETERMINISM
  • CREATE VIEW with SCHEMABINDING
  • CREATE INDEX

60
XML SupportXML Query Results
  • SQL Language Extension

SELECT FROM WHERE ORDER BY FOR XML (raw
auto , ELEMENTS
explicit) , XMLData ,
BINARY base64)
61
XML SupportFOR XML Raw Mode
  • One ltRowgt Element Per Row in the Result Set
  • No Nested Elements
  • Columns / Values in the Result Set Are Attributes
    / Values on the ltRowgt
  • Similar to CSV but in XML Format

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

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

64
XML Support OpenXML
  • 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

65
XML Support HTTP Access URL Types
  • URL Query
  • http//server/vroot?sql
  • Direct Query
  • http//server/vroot/dbobject/xpath
  • Template
  • http//server/vroot/vname?params
  • XML View
  • http//server/vroot/vname/xpath?params

66
XML Support HTTP Access URL Query
  • http//server/vroot?params
  • Parameters
  • sqlselectfromCustomersFORXMLAuto
  • xsltable.xsl
  • contenttypeimage/gif
  • encodingUTF-8

67
XML Support HTTP Access Direct Query
  • http//server/vroot/dbobject/xpath
  • Singleton Select of Single Column
  • Value Returned in Native Format, Not XML
  • Enables Direct Retrieval of Objects from Database
  • Ex Images, OLE objects, etc.
  • XPath Syntax (xpath)
  • Specifies query in XPath treating tables / views
    as elements and columns as attributes
  • Ex http//server/vroot/dbobject/Employees_at_Employ
    eeID1/_at_Photo

68
XML Support HTTP Access - Template
  • http//server/vroot/vname?params
  • Virtual name (vname)
  • Direct reference to template file
  • Path to directory
  • http//server/vroot/vname/customers.xml?params
  • Two Types of Parameters
  • Implementation-defined
  • xsl, encoding, contenttype
  • User-Defined
  • Named parameters referenced within the templates

69
SQL XML 3.0
  • Web services (SOAP) support
  • Web services support offers SQL Server
    functionality to the client
  • can send SOAP HTTP requests to the server that is
    running SQLXML to executestored procedures
  • user-defined functions (UDFs)
  • XML templates
  • XML views using annotated XSD schemas
  • 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 client-side 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
    DiffGram) to apply the updates to SQL Server.
  • DiffGrams - DataSet component of the Microsoft
    .NET Framework.

70
ReplicationGeneral Enhancements
  • 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

71
ReplicationSchema Replication
  • Add / Drop Column
  • 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

72
ReplicationSnapshot Improvements
  • Parallelism / Performance improvements
  • Parallel index creation
  • BULK INSERT option (inproc loader)
  • Smarter Index Management
  • Highly Concurrent Snapshot
  • Reduces the time locks are held on tables
  • Include Triggers, Procedures, and Views in
    Snapshots for Publications
  • Pre- and Post-Snapshot Script Execution
  • New Snapshot ActiveX Control


73
ReplicationTransactional Replication
  • Changes Detected by Reading the Transaction Log
  • Changes Queued Once in Distribution Database, and
    Applied in Exact Sequence to Each Subscriber
  • Replication Is All or Nothing
  • A Subscribers Data Will Always Be Exactly What
    the Publisher Was at Some Instance
  • Changes Can Be
  • Pushed to subscribers in near real-time or pulled
    when subscriber requests changes

74
ReplicationTransactional Replication
  • Queued Updating Subscribers
  • Builds upon 7.0 updating subscribers
  • Provides offline updates with transactional
    semantics
  • Queue maybe either MSMQ 2.0 or SQL Table
    (publication option)
  • Update committed locally by offline subscriber
    and posted to queue
  • A new replication agent (QueueReader) submits
    transaction to publisher asynchronously


75
ReplicationMerge Enhancements
  • Parallelism for Improved Server-to-Server
    Performance
  • SQL Server 7.0 Merge agent is single-threaded
  • New Optimizations for Determining Data Changes
    Pertinent to a Subscribers Partition
  • More Efficient Initial Synchronization of
    Dynamic, Filtered Subscribers
  • Can optionally pre-process a customized bcp
    extract for the specific subscriber
  • Most Operations at Least 50 Faster
  • Many doubled (esp. if SMP)
  • Some 5x faster or more

76
ReplicationRemoving Merge Limitations
  • Added Vertical Partitioning Support
  • Auto-Assignment of Identity Ranges for
    Subscribers
  • Support for TIMESTAMP Columns
  • Destination Object Owner Support
  • Improved Offline Security
  • Better Conflict Retention and Cleanup
  • More Powerful Dynamic Filters (UDFs)

77
Analysis ServicesDimension Architecture
Manageability
  • No Re-Processing Required For
  • Modifying slowlychanging dimensions
  • Changing the number of levels
  • Adding virtual dimensions
  • Support for Hierarchies inVirtual Dimensions
  • Support for Very Large Dimensions
  • Use ROLAP for gt 10M members
  • Use automatic bucketing

78
Analysis ServicesDimension Architecture
Completeness
  • Ragged and Unbalanced Hierarchies
  • Custom Roll-ups
  • Write-Back to Dimensions
  • Easy to Define and Maintain Multiple Hierarchies
    in a Dimension
  • Multiple hierarchies now used indesigning
    aggregations
  • 128 Dimensions Per Cube
  • 128 Levels Per Dimensions - 256 Per Cube

79
Analysis ServicesRagged Dimensions
Country
No States!
State
City
  • Variable Depth

80
Analysis ServicesParent-Child Dimensions
81
Analysis ServicesSecurity
  • Authentication Mode
  • Windows NT integrated
  • Using Internet Information Server
  • Windows NT challenge/response Anonymous SSL
  • Cell Security
  • Declare any set of cell ranges in the cube as
    protected
  • Dimension Member Security
  • Hide parts (or all) of the dimension members
  • Shrinks the multi-dimensional space
  • Intuitive Visual Tools to Define Security
    Permissions

82
Analysis ServicesUsing Security
  • Security Based on MDX Expressions
  • Cell-Level, Member-Level
  • Extends Roles Concept, MDX Filters

Cell-level security
Sq Ft
Total Salary
Employees
Member-level security
16,500
N/A
East
50
Sq Ft
Employees
Central
20,625
7,600K
75
10,500
N/A
35
West
16,500
50
East
20,625
75
Central
10,500
35
West
83
Analysis ServicesData Mining Definition
  • Deducing Knowledge by Examining Data
  • 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

84
Analysis ServicesMaking Data Mining Easy
  • Direct Support for Relational and
    Multi-Dimensional (OLAP) Data
  • No more text import routines!
  • Wizards for Setting Model Parameters, Without
    Being an Algorithm Expert
  • Data Mining on OLAP Data Automatically Uses the
    Semantics Already in the Cube

85
Analysis ServicesData Mining Process Illustrated
DM Engine
DM Engine
86
Analysis ServicesA DMM Is a Table
  • A DMM Structure Is Defined as a Table
  • Training a DMM means inserting data into the
    table
  • Predicting from a DMM means querying the table
  • All Information Describing the Case Is Contained
    in Columns
  • All of the Operations (Creation, Training,
    Predictions) Will Treat the DMM As a Table

87
Analysis ServicesTraining a DMM
  • Training a DMM Means Passing in Data for Which
    the Attributes to Be Predicted Are Known
  • Multiple passes are handled internally by the
    provider
  • Use an INSERT INTO Statement
  • The DMM Will Not Persist the Inserted Data
  • Instead, It Will Analyze the Given Cases and
    Build the DMM Content (Decision Tree,
    Segmentation Model, Association Rules)

INSERT INTO ltmining model namegt (columns
list) ltsource data querygt
88
Analysis ServicesPrediction Query Syntax
  • SELECT ltcolumns to return or predictgt
  • FROM
  • ltdmmgt PREDICTION JOIN
  • ltinput data setgt
  • ON ltdmm columngt ltdmm input columngt

89
Analysis ServicesEnglish Query Integration
  • English Query Is a Component of SQL Server
  • Engine
  • 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

90
Analysis ServicesInternet Support on Client
  • 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

91
Analysis ServicesHTTP Connectivity Architecture
  • ConnectionString Provider MSOLAP
  • Data Source http//www.cubeserver.com

msolap.asp
92
Desktop and Windows CEWhats New on the Desktop
  • Multi-Instance Support
  • Windows Installer-Based Setup
  • Same Feature Set As the Server Version
  • T-SQL
  • XML
  • DTS
  • DMO
  • Replication enhancements
  • Some Limitations Based on OS Support (AD, etc.)

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

94
Desktop and Windows CE Windows CE Architecture
  • eVC or eVB to Develop Application
  • ADOCE 3.1 and/or OLEDBCE to Access SQL Server CE
  • Better Than the Competition
  • Rapid development
  • Utilizes existing data access strategies
  • Utilizes existing staff knowledge

95
Questions?
96
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

97
(No Transcript)
98
User Defined Functions Drill Down
  • User Defined Functions (UDF) Overview
  • Creating User Defined Functions
  • Referencing User Defined Functions
  • Deterministic and Non-deterministic Functions
  • Functions with SCHEMABINDING
  • User Defined Functions A Closer Look
  • User Defined Functions returning a table Variable
  • In-line User Defined Functions
  • Invoking a User Defined Function
  • Built-in Functions

99
User Defined Functions (UDF) Overview
  • Subroutine of one or more T-SQL statements
    returning a scalar or table
  • Strongly-typed parameters can be any data type
    except timestamp, cursor, or table
  • Return type can be any data type except text,
    ntext, image, cursor, or timestamp
  • Permissions
  • CREATE FUNCTION permission to create, alter, or
    drop
  • EXECUTE permission to use if a scalar data type
    is returned
  • SELECT, INSERT, UPDATE, DELETE permissions to use
    if a table is returned
  • REFERENCES permissions to use in CHECK or DEFAULT
    constraint
  • If UDF is referenced in a CREATE TABLE or ALTER
    TABLE statement, the table owner must also own
    the UDF
  • No user defined aggregate functions
  • No way to tie a function to a GROUP BY

100
Creating User Defined Functions - Syntax
  • CREATE FUNCTION owner_name. function_name
    ( _at_parameter_name scalar_data_type
    default ,...n ) RETURNS lt
    return_type_spec gt WITH ENCRYPTION
    SCHEMABINDING ,...n AS lt
    body_definition gt
  • lt return_type_spec gt scalar_data_type
    TABLE return_variable table_type_definitio
    n
  • lt body_definition gt BEGIN
    function_body END RETURN ( select-stmt
    )
  • lt table_type_definition gt TABLE (
    column_definition table_constraint ,...n
    )
  • No special naming rules or functionality
  • Some system functions use fn_, but master is not
    searched as with sp_

101
Referencing User Defined Functions
  • A UDF can be referenced any place an expression
    of the same data type returned by the function is
    allowed in T-SQL
  • Example
  • CREATE FUNCTION fn_Markup (_at_Cost money, _at_Rate
    numeric(3,2))RETURNS money ASBEGIN RETURN
    ( _at_Cost (1.0 _at_Rate) ) END
  • GO
  • SELECT dbo.fn_Markup (30, 0.2)
  • GO
  • CREATE TABLE TblPrice( Cost money, Rate
    numeric(3,2), Price AS (dbo.fn_Markup (Cost,
    Rate)) )

102
Deterministic and Non-deterministic Functions
  • All functions are either deterministic or not
  • A deterministic function always returns the same
    result for the same input
  • A non-deterministic function may return varying
    results for the same input
  • Example DATEADD is deterministic GETDATE is not
  • A user defined function is deterministic if the
    following are both true
  • There are no references to database objects,
    except deterministic functions
  • No reference to a table, view, stored procedure,
    or non-deterministic function
  • Table variables are okay
  • The function is schema-bound created WITH
    SCHEMABINDING
  • Any referenced objects cannot change
  • Determinism is checked and recorded at CREATE
    FUNCTION time
  • OBJECTPROPERTY ( object_id, 'IsDeterministic )
    returns 1 if deterministic
  • A function must be deterministic to be used in an
    indexed computed column or an indexed view.
    Determinism preserves semantics independent of
    the execution plan chosen guaranteed to return
    the same value for the same inputs.
  • Non-deterministic functions can be used as long
    as their results are never persisted in an index

103
Functions with SCHEMABINDING
  • Creating a function or view with SCHEMABINDING
    binds the function / view to any tables, views,
    stored procedures, or functions it references
  • If a table or view is referenced, the columns
    referenced cannot be altered
  • If a stored procedure or function is referenced,
    it cannot be altered
  • SCHEMABINDING requirements
  • All functions or views referenced must also be
    schema-bound
  • All objects referenced must reside in the same
    database as the function / view
  • References must use a two-part name avoids
    runtime owner checks / changes
  • A function owner must have REFERENCES permission
    on all objects referenced in the function. A view
    owner must own all functions in the view.
  • A schema-bound function is not necessarily
    deterministic, but a deterministic function is
    always schema-bound
  • A function that refers to tables can be
    schema-bound, but is not deterministic
  • Use OBJECTPROPERTY( object_id, IsSchemaBound)
    to find if a function or view was created with
    SCHEMABINDING

104
User Defined Functions A Closer look
  • A UDF cannot make permanent changes to the state
    of any resource outside the scope of the
    function. For example
  • Cannot modify tables, system catalog, send email,
    use transactions or DDL, etc.
  • Cannot return results set to users
  • Can make changes only to local variables no
    OUTPUT parameters
  • Can open only local cursors
  • Cannot access temporary tables avoids
    recompilation
  • Recursive calls are allowed
  • Maximum of 32 levels of nesting allowed
  • This nesting limit includes stored procedure and
    trigger levels
  • Several catalog objects report information on
    UDFs
  • sp_help and sp_helptext
  • INFORMATION_SCHEMA views
  • ROUTINES, PARAMETERS, ROUTINE_COLUMNS

105
Transact-SQL in User Defined Function
  • Legal Transact-SQL Statements in UDF
  • DECLARE
  • SELECT _at_variablevalue
  • SET
  • Local cursor operations
  • Control-of-flow statements
  • UPDATE, DELETE, INSERT to local table variables
  • Non-deterministic built-in functions are NOT
    allowed in a UDF
  • Cannot use EXEC or sp_executesql within a UDF

106
UDF Returning a table Variable
  • Powerful alternative to a view
  • Allows multiple T-SQL statements representing
    complex logic
  • Can replace stored procedures that return a
    single result set
  • Table returned by UDF can be used in the FROM
    clause while a stored procedure returning a
    results set can not
  • CREATE FUNCTION fn_SeniorEmployee ( _at_InHireDate
    datetime)RETURNS _at_funcTable TABLE ( EmpID int,
    EmpName varchar (31) )AS BEGIN INSERT INTO
    _at_funcTable SELECT EmployeeID, FirstName ' '
    LastName FROM Employees WHERE HireDate lt
    _at_InHireDateRETURN END
  • GO
  • SELECT FROM fn_SeniorEmployee ( '10/17/1993
    )WHERE EmpName LIKE 'N'

107
In-line User Defined Functions
  • A subset of UDFs that returns a table
  • No BEGIN / END just RETURNS table and RETURN
    with a SELECT
  • Can be used to achieve the functionality of a
    parameterized view
  • Instead of many views with just the WHERE clause
    changing
  • CREATE VIEW vw_CustomerNamesInWA AS SELECT
    CustomerID, CompanyName FROM Northwind.dbo.Custo
    mers WHERE Region 'WA'
  • One function with a parameter
  • CREATE FUNCTION fn_CustomerNamesInRegion (
    _at_RegionParameter nvarchar(30) ) RETURNS table
    - - No table structure specified AS
    RETURN ( SELECT CustomerID, CompanyName FROM
    Northwind.dbo.Customers WHERE Region
    _at_RegionParameter )
  • SELECT FROM fn_CustomerNamesInRegion ( N'WA )
  • In-Line because the function is optimized into
    a query just as a view would

108
Invoking a UDF Returning a Scalar Value
  • A UDF returning a scalar value can be called in
  • Queries
  • As an expression in a select_list WHERE, HAVING,
    GROUP BY, ORDER BY, SET, or VALUES clauses
  • Functions referenced in these locations are
    logically executed once per row
  • CHECK constraints DEFAULT definitions Computed
    columns
  • Right operand of an assignment operator CASE
    expressions
  • Boolean expression of control-of-flow statements
  • PRINT statements (with the UDF returning a
    string)
  • Function arguments
  • RETURN integer_expression in a stored procedure
  • A two part name is required for scalar functions
  • Always must be user_name.function_name

109
Invoking a UDF Returning a table Data Type
  • A UDF returning a table can be called in the FROM
    clause of a SELECT, INSERT, UPDATE, or DELETE
    statement
  • Can be followed by a table alias
  • SELECT OD.OrderID, OD.ProductID,
    fnPr.Price FROM OrderDetails as OD INNER
    JOIN fn_Products('Discontinued') AS fnPr ON
    OD.ProductID fnPr.ProductID ORDER BY
    OD.OrderID, OD.ProductID
  • When invoked in the FROM clause of a subquery,
    function arguments cannot reference columns from
    the outer query
  • Only static, read-only cursors can be opened on a
    SELECT statement whose FROM clause refers to a
    user-defined function that returns a table
  • The function is invoked only once
  • Not once per row as with scalar
  • A one-part name is sufficient for table functions
  • Use additional parts to clarify owner, database,
    and server as needed

110
Invoking a UDF Specifying Arguments
  • When scalar values are returned, and the function
    is invoked by an EXECute statement, the
    parameters can be specified in the same way as
    stored procedure parameters
  • Argument values are not enclosed in parentheses
  • Parameter names can be specified
  • If parameter names are used, argument values do
    not have to follow the order of defined
    parameters
  • Example
  • DECLARE _at_MyDecimalVar decimal(12,3)
  • EXEC _at_MyDecimalVar dbo.fn_CubicVolume _at_CubeLen
    gth 12.3, _at_CubeHeight 4.5, _at_CubeWidth
    4.5

111
Built-in Functions
  • The built-in functions are a subset of the
    system-defined functions
  • In master owned by a new master user,
    master.system_function_schema
  • Other functions in master, owned by master.dbo,
    are not built-in functions
  • Other functions, internal to the server, are not
    owned by either
  • Returning a Table sysobjects.xtype TF
  • Invoke the function with a leading double colon
    () to distinguish it from user defined
    functions with same name
  • Must also be specified as a one-part name with no
    database or owner
  • SELECT
  • FROM fn_helpcollations()
  • Returning a scalar value sysobjects.xtype
    FN
  • Invoke the function as a one-part name (do not
    specify database or owner)
  • Do NOT specify a leading double colon ()
  • Built-in functions execute in context of master,
    not like sp_ does
Write a Comment
User Comments (0)
About PowerShow.com