SQL Server 2005 CLR Integration - PowerPoint PPT Presentation

1 / 16
About This Presentation
Title:

SQL Server 2005 CLR Integration

Description:

Programming environment for Functions, Stored Procedures, Triggers ... Well-formed and validation checks. Methods on XML data type. Query(), value(), exist() & modify ... – PowerPoint PPT presentation

Number of Views:42
Avg rating:3.0/5.0
Slides: 17
Provided by: aseelm
Category:

less

Transcript and Presenter's Notes

Title: SQL Server 2005 CLR Integration


1
SQL Server 2005CLR Integration Programmability
  • Aseel Mansour
  • Product Technology Specialist
  • Microsoft - South Gulf

2
  • They say
  • A picture is worth
  • a thousand words

3
  • We say
  • A demo is worth
  • a thousand slides ?

4
Agenda
  • Programmability
  • .NET Framework Integration
  • Stored Procedures
  • User Defined Types
  • Aggregates
  • Web Services Support
  • XML Support
  • Summary

5
.NET Framework Integration
  • Programming environment for Functions, Stored
    Procedures, Triggers
  • User Defined Types, Aggregates
  • In-Proc Data Access (ADO.NET V2)
  • Symmetric data access
  • Mid-tier/data tier deployment decision
  • Security
  • Integration of SQL and CLR security
  • Three levels of code access security
  • Safe, External-Access (verifiable), Unsafe
  • Tight integration with Visual Studio
  • Authoring, debugging, deployment, profiling

6
The Developer Experience
VS .NET Project
Runtime hosted by SQL (in-proc)
7
Authoring/Debugging/Deploying
  • Server debug integration
  • Set breakpoints anywhere
  • Full debugger visibility
  • Single step support
  • Between languages T-SQL, C, VB, C
  • Between deployment tiers
  • E.g. Mid-tier, through SQL Server stored proc
    call back to mid-tier

8
.NET Integration Choices
  • Choice of where to run logic
  • Database, for logic that runs close to data
  • Mid-tier, for logic that scales out
  • Symmetric programming model
  • Leverage skills mid-tier server
  • Choice of programming language
  • C, VB.NET, Managed C, for a safe, modern
    execution environment
  • T-SQL continues to be supported enhanced
  • Remains good choice for data-intensive procedures
  • Safe extended stored proc replacement

9
CLR Integration DemoStored ProceduresUser
Defined TypesAggregates
10
HTTP/SOAP Features
  • SQL stored proc calls via HTTP/SOAP
  • Easy, standards based connectivity from Unix
    platforms to SQL Server
  • Provides native HTTP listening
  • HTTP endpoint specifying URL, port, reqs
  • Publish WSDL for endpoints
  • Standard-based
  • SOAP 1.1 and 1.2, WSDL 1.1, inlined XSD
  • Windows and SQL authentication (SSL only)
  • Stored Proc can return result as DataSet
  • Compatible with mid-tier prog. model

11
SOAP Demo
12
XML Support
  • XML data type XML index support
  • Unified XML relational store
  • Both SQL XQuery supported by same industrial
    strength infrastructure
  • Leverages existing SQL engine optimizer
  • XQuery with data modification extensions
  • XML views
  • Declarative Mapping Files
  • XML schema enforcement
  • Client access using ADO.NET SOAP

13
Native XML Store
  • XML data type
  • Native SQL type
  • Use for column, variable or parameter
  • CREATE TABLE docs (id INT PRIMARY KEY, xDoc XML
    NOT NULL)
  • Store un-typed or typed XML instances
  • Well-formed and validation checks
  • Methods on XML data type
  • Query(), value(), exist() modify()

14
XML Indexes
  • Create XML index on XML column
  • CREATE XML INDEX idx_1 ON docs (xDoc)
  • Creates indexes on tags, values paths
  • Speeds up queries
  • Entire query is optimized
  • Same award winning cost based optimizer
  • Indexes are used as available

15
XML Demo
16
(No Transcript)
Write a Comment
User Comments (0)
About PowerShow.com