Whats New with the SQL Server 2000 Database Engine

1
Whats New with the SQL Server 2000 Database
Engine?

• Trip Ottinger, MCSD
• EnterSolve, Incorporated

2
State of the Union

• SQL Server 7.0 A New Foundation
• SQL Server 2000 Leverages this Foundation
• Watch Out Oracle, SQL Server is the Fastest
  Database in the World!
• Transaction Processing Performance Council
  (www.tpc.org)

3
User Defined Functions

• T-SQL Only
• Two Types of UDFs
• Return a Scalar Value
• Return a Table
• Return Anything But BLOB, CURSOR, TIMESTAMP

4
New Built-in Functions

• GetUTCDate()
• IDENT_CURRENT(TABLE)

5
Cascading Declarative Referential Integrity (DRI)

• Its So Simple!
• VERY Powerful
• Cascade UPDATEs and DELETEs
• Must Have Primary Key and Foreign Key Between
  Tables

6
Indexed Views

• Results are Persisted in Database
• Results are Indexed
• FAST !
• Schema Binding Helps for Table Modifications
• Cant Use When Defining Columns
• Cant Index a View When
• You Have to be the Owner
• Has to be a Clustered Index

7
Indexed Views, The Good Stuff

• Easy to create
• Automatically used by Query Optimizer
• Speeds up decision support apps
• Index Tuning Wizard will make suggestions

8
New Data Types

• SQL_Variant
• Store Anything But Image, Text, Ntext or
  Timestamp
• BigInt
• Storage Size is 8 Bytes
• - 9,223,372,036,854,775,807
• Thats 263 for All You Rocket Surgeons

9
Integrated XML Support

• SQL Server 2000 is an XML-enabled Database Server
• Access SQL Server using HTTP
• Retrieve XML Data Using FOR XML
• Retrieve XML Data using XML Templates
• XML Views Using XDR Schemas

10
Last But Not Least

• Federated Database Support
• Distributed Partitioned Views
• Partition Tables Horizontally Across Multiple
  Servers
• Scale Out
• Multiple Instance Support

11
Last But Not Least

• Index Enhancements
• Computed Columns
• Specify Ascending or Descending Order
• Tempdb Database as a Work Area
• Faster Index Creation
• More Likely Index Pages Allocated in Contiguous
  Strip

12
Last But Not Least

• Collation Enhancements
• Not Just Server Level
• Database Level
• Column Level
• Different Column Diffent Collations
• Change the Default Collation