Introduction To Programming With SQL Server 2005

1
Introduction To Programming With SQL Server 2005

• Dr Greg Low
• Readify
• Greg.Low_at_readify.net

2
What We Will Cover

• T-SQL Enhancements
• SQL CLR Integration
• Security Enhancements
• Client (ADO.NET) Enhancements
• Miscellaneous Enhancements

3
Who Am I?

• SQL Server .NET Consultant with Readify
• Director of White Bear Consulting
• Microsoft MVP for .NET
• INETA User Group Relations Chair for Asia-Pacific
• President of Qld MSDN User Group
• President of Qld SQL Server User Group

4
Prerequisite Knowledge

• Knowledge of
• Microsoft SQL Server 2000 or
• Microsoft SQL Server 7.0
• And VB.NET or C

Level 200
5
Agenda

• Introduction
• T-SQL Enhancements
• SQL CLR Integration
• Security Enhancements
• Client (ADO.NET) Enhancements
• Miscellaneous Enhancements
• Session Summary

6

• Replication
• Auto-tuning Replication Agents
• Oracle Publication
• Improved Blob Change Tracking
• Replication Monitor
• OLAP and Data Mining
• Analysis Management Objects
• Windows Integrated Backup and Restore
• Web Service/XML for Analysis
• DTS and DM Integration
• New Data Mining Algorithms
• Auto Packaging and Deployment
• Data Transformation Services
• New Architecture (DTR DTP)
• Complex Control Flows
• Control Flow Debugging
• For Each Enumerations
• Property Mappings
• Full Data Flow Designer

• .NET Framework
• Common Language Runtime Integration
• User-defined Aggregates
• User-defined Data Types
• User-defined Functions
• SQL Server .NET Data Provider
• Extended Triggers
• Data Types
• Managed SQL Types
• New XML Datatype
• Varchar (MAX) Varbinary (MAX)
• SQL Server Engine
• New Message Service Broker
• HTTP Support (Native HTTP)
• Database Tuning Advisor
• Multiple Active Result Sets
• Persisted Computed Columns
• Snapshot Isolation Level
• Scale Up Partitioning

• Database Maintenance
• Backup and Restore Enhancements
• Checksum Integrity Checks
• Dedicated Administrator Connection
• Dynamic AWE
• Fast Recovery
• Highly-available Upgrade
• Online Index Operations
• Online Restore
• Parallel DBCC
• Parallel Index Operations
• Management Tools
• New Management Studio
• MDX Query Editor
• Version Control Support
• XML/A
• SQLCMD Command Line Tool
• Performance Tuning
• Profiler Enhancements

7
Agenda

• Introduction
• T-SQL Enhancements (T-SQL IS NOT DEAD)
• SQL CLR Integration
• Security Enhancements
• Client (ADO.NET) Enhancements
• Miscellaneous Enhancements
• Session Summary

8
T-SQL Enhancements

• Ranking Functions
• Row_Number()
• Rank()
• Dense_Rank()
• NTile()

9
T-SQL Enhancements

• New Data Types
• Varchar(max)
• NVarchar(max)
• VarBinary(max)
• XML

10
T-SQL Enhancements

• Common Table Expressions
• Now allow recursive evaluation
• Operators
• SOME, ANY, ALL, EXCEPT, INTERSECT
• Pivot/Unpivot
• Insert with merge
• (ie UPSERT in other dbs)

11
T-SQL Enhancements

• TOP
• Now allows variable count
• OUTPUT Clause
• Can return values from statement
• TABLESAMPLE
• Automatic sampling of table data

12
T-SQL Enhancements

• Exception Handling
• TRY
• CATCH

13
T-SQL Enhancements

• OPENXML
• XQuery support

14
demonstration
T-SQL Enhancements
15
Agenda

• Introduction
• T-SQL Enhancements
• SQL CLR Integration
• Security Enhancements
• Client (ADO.NET) Enhancements
• Miscellaneous Enhancements
• Session Summary

16
SQL CLR Integration

• Why integrate the CLR?
• Familiar coding experience in all tiers
• Allows simple implementation of messy T-SQL
• Possible to share business logic (desirable??)
• Create new data types

17
SQL CLR Integration

• How is it integrated?
• CLR is hosted within SQL Server
• CLR thinks SQL O/S is its O/S
• Deep integration (memory allocations are
  performed by SQL Server)
• Assemblies loaded into/from database tables
• Safety levels (SAFE / EXTERNAL / UNSAFE)
• Code access security
• Tightly integrated with Visual Studio (SQL Server
  project types)

18
SQL CLR Integration

• What can be achieved?
• User-defined functions (fast!)
• Stored Procedures
• Triggers
• User-defined data types (new type of object)
• User-defined aggregates (new type of object)

19
demonstration
CLR Integration
20
Agenda

• Introduction
• T-SQL Enhancements
• SQL CLR Integration
• Security Enhancements
• Client (ADO.NET) Enhancements
• Miscellaneous Enhancements
• Session Summary

21
Security Enhancements

• CREATE/ALTER/DROP
• clean new syntax
• Used for users, schemas and many other new object
  types

22
Security Enhancements

• User Schema separation
• 4 part names still used but 3rd part now Schema
  not Owner
• Users have a default schema
• Avoids orphaned objects

23
Security Enhancements

• EXECUTE AS
• SELF, CALLER, OWNER, Specified User (requires
  Impersonate permission)
• Can avoid many issues with ownership chaining and
  dynamic SQL within stored procedures
• End-point security
• End-points are now a securable item
• Users can be granted permissions on particular
  protocols and end-points

24
Security Enhancements

• Sys views
• Master vs resource database
• Dynamic management views
• Peek into SQL Servers operation ( memory
  clerks)
• Catalog security
• See only items that you have some permission to
• Granular permissions
• Much more fine-grained
• Encryption
• Detailed support

25
demonstration
Security Enhancements
26
Agenda

• Introduction
• T-SQL Enhancements
• SQL CLR Integration
• Security Enhancements
• Client (ADO.NET) Enhancements
• Miscellaneous Enhancements
• Session Summary

27
Client Enhancements

• Support for new data types
• Varchar(max), XML, UDTs
• Asynchronous command execution
• BeginExecuteNonQuery()
• EndExecuteNonQuery()

28
Client Enhancements

• MARS
• Multiple Active Result Sets
• Multiplexes result sets on a single connection
• Not a good combination with asynchronous command
  execution
• Allows executing commands on a connection while a
  DataReader is still open

29
Client Enhancements

• DataSet
• Insert and indexing performance
• Binary dataset serialization
• UpdateBatchSize on DataAdapter
• Standalone DataTable (and serializable)
• DataRow settable rowstate
• GetDataReader() and Load()
• NewRowArray(size) on DataTable
• GetDataTableSchema() returns XMLSchemaSet

30
Client Enhancements

• SQLDependency
• SQLBulkCopy
• Snapshot Isolation
• Common base classes
• MDAC dependency removal (SNAC)
• Provider factory model
• Server enumeration
• Provider enumeration

31
Client Enhancements

• Connection statistics
• FireInfoMessageEventOnUserErrors
• Database schema discovery API
• Tranparent failover for mirrored servers
• StatementCompleted event
• Strongly typed schema inference
• ConnectionStringSettings

32
Agenda

• Introduction
• T-SQL Enhancements
• SQL CLR Integration
• Security Enhancements
• Client (ADO.NET) Enhancements
• Miscellaneous Enhancements
• Session Summary

33
Miscellaneous Enhancements

• SMO RDO
• Replace DMO
• Native SOAP Interface
• Expose stored procs as web services
• Service Broker
• Message queuing system
• Reporting Services
• Standalone Winform controls

34
Session Summary

• T-SQL is more powerful than ever
• Great new options for developers
• Higher performance out of the box
• Even more secure

35
For More Information

• Visit MSDN at msdn.microsoft.com (international)
  or
• www.microsoft.com/australia/msdn (Australian
  content)

36
Microsoft LearningTraining Resources for IT
Professionals
To see the detailed syllabus or to locate a
training provider,visitwww.microsoft.com/learnin
g
37
SQL Server 2005 2 Day Workshops

• Microsoft SQL Server 2005 Database Infrastructure
  Scalability,
• presented by Brent Challis, DDLS
• Microsoft SQL Server 2005 Business Intelligence,
• presented by Peter Myers. Tenix Connections
• Microsoft SQL Server 2005 Development,
• presented by Greg Low, White Bear Consulting

38
SQL Server 2005 Workshops

• Cost 650 and 575 for early birds.  
• For Schedule, Registration and further
  information
• Partners only http//www.microsoft.com/australia/
  partner/training/
• Customers and Partners http//www.microsoft.com/a
  ustralia/events/sql2005 (This page will be
  available later this month)

39
SQL Server 2005 Workshops

• Register Now for Yukon Workshops
• Microsoft Partner can register for all 3
  workshops at www.microsoft.com/australia/partner/t
  raining/default.aspx
• Microsoft Customers can register for all 3
  workshops at
• com.au/YukonCusBI
• com.au/YukonCusDev
• com.au/YukonCusAdmin
• Workshops run nationally and are scheduled
  between 7 March and 30 June 2005, places are
  limited so be quick.

40
User Community

• SQLServer
• SQLServer.org.au (Australian SQL Server User
  Group)
• MSDN ( .NET)
• www.microsoft.com/australia/msdn/events/usergroups
  .aspx
• MSDN Connection
• www.microsoft.com/australia/msdn/connection/defaul
  t.aspx
• Get Involved!

41
Code Camp

• Code Camp Oz is happening 23/24th April at
  Charles Sturt University in Wagga Wagga
• Be there!
• www.codecampoz.com

42
The SQL Server 2005 University Masters

• Fully accredited, masters level degree from
  Charles Sturt University
• SQL Server 2005 MCDBA certification included as
  an integral component
• Graduates eligible for full professional level
  membership of the Australian Computing Society
• Part time study delivered via Distance Education
• Qualifies for Government FEE-HELP program
• Places available for experienced applicants
  without previous qualifications

First intake commences September 2005Register
your interest at www.itmasters.info
43
(No Transcript)