Introduction To Programming With SQL Server 2005 - PowerPoint PPT Presentation

1 / 43
About This Presentation
Title:

Introduction To Programming With SQL Server 2005

Description:

Snapshot Isolation Level. Scale Up Partitioning. VIA support. NUMA support ... Snapshot Isolation. Common base classes. MDAC dependency removal (SNAC) Provider ... – PowerPoint PPT presentation

Number of Views:72
Avg rating:3.0/5.0
Slides: 44
Provided by: Andrew754
Category:

less

Transcript and Presenter's Notes

Title: 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)
Write a Comment
User Comments (0)
About PowerShow.com