![Page 1: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/1.jpg)
Programming the CLR in SQL Server 2005
Mark BlomsmaDevelop-One
Session Code: DB.08
![Page 2: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/2.jpg)
• Introductions• The End of T-SQL• How does it work?
– Hosting Layer, SQL OS
• How do I use it?– Stored procedures, Functions, Triggers, Custom aggregations,
User Defined Type
• Deployment• Monitoring• Questions
Programming the CLR in SQL Server 2005
![Page 3: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/3.jpg)
Mark Blomsma
• Professional developer since 1992• Microsoft Certified Professional• Microsoft Most Valuable Professional
(MVP) for three years running• Current employment
– Software Architect : Develop-One– Consultant : Omnext.NET– Sales Manager North America : MRA Group
![Page 4: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/4.jpg)
The end of T-SQL?
• No! Managed code will not solve all your problems.
– Select/Insert/Update/Deleteare here to stay!
– CLR is an alternative to the procedural portion of T-SQL
![Page 5: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/5.jpg)
So why put C# in the database?
• Complex string manipulation• Encryption• XML manipulation• No more need for external procedures• Tooling support for managed languages• Leverage .NET FCL & third party libraries• For everything that you cannot do with T-SQL
![Page 6: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/6.jpg)
Show me the magic
• Stability is everything for a database• CLR 2.0 offers more control to host environment through
“HostProtection” attribute – No thread creation– No listening on sockets– No finalizers– Allow/deny allocation of memory– Deny killing the host process– CLR is sandboxed through CAS
• Checked during assembly creation process
• CLR 2.0 AppDomain is hosted in SQL Server– Loading and unloading of assemblies is controlled via SQL
Server
![Page 7: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/7.jpg)
Limited FCL support
• Not available– Environment.Exit()– System.Console– System.Windows.Forms– System.Drawing– System.Web– …
![Page 8: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/8.jpg)
SQL OS
• CLR uses SQL OS for:– Memory– Threads/fibers– Synchronization
SQL EngineSQL Engine
WindowsWindows
SQL OSSQL OS
CLRCLR
HostingHostingLayerLayer
![Page 9: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/9.jpg)
Hosting layer
• Hosting layer provides coordination of:– Assembly Loading– Memory management– Security Model– Reliability– Threads & Fibers– Deadlock detection– Execution context
SQL EngineSQL Engine
WindowsWindows
SQL OSSQL OS
CLRCLR
HostingHostingLayerLayer
![Page 10: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/10.jpg)
How do I use it?
• Stored Procedures• Functions• Triggers• Custom Aggregations
• SqlContext– SqlExecutionContext– SqlTriggerContext
![Page 11: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/11.jpg)
Stored procedures
• Public class• Pubic method• Method must be static
• Use [SqlProcedure] attribute
• Use in, out inout and return parameters
![Page 12: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/12.jpg)
Stored procedures
![Page 13: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/13.jpg)
Functions
• Public class• Public method• Method must be static
• Use [SqlFunction] attribute
• Must return a value
![Page 14: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/14.jpg)
Functions
![Page 15: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/15.jpg)
Triggers
• Public class• Public method• Method must be static
• Use [SqlTrigger] attribute– [SqlTrigger(Event=“FOR INSERT”)]
• Use SqlTriggerContext for getting at the data related to the trigger.
![Page 16: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/16.jpg)
Triggers
![Page 17: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/17.jpg)
Custom Aggregations
• Public struct
• Use [SqlUserDefinedAggregate] attribute
• Implement:– Init– Accumulate– Merge– Terminate
![Page 18: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/18.jpg)
Custom Aggregations
![Page 19: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/19.jpg)
User Defined Type
• Public struct
• Use [SqlUserDefinedType] attribute
• 8KB size limit.• Do not use for business objects.
![Page 20: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/20.jpg)
Deployment
• Use sp_configure ‘clr enabled’, ‘1’to enable sqlclr– Off by default
• Use create assembly
• SqlClr uses 3 CAS permission buckets– Safe– External access– Unsafe
![Page 21: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/21.jpg)
Deployment
![Page 22: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/22.jpg)
Monitor assemblies
• Profiler trace events: – CLR:load assembly monitors assembly load requests (successes and failures) – SQL:BatchStarting, BatchCompleted – SP:Starting, Completed, StmtStarting, StmtCompleted monitor execution of
Transact-SQL and CLR routines • Performance counters:
– SQL Server: Total CLR time – .NET CLR Memory – Processor
• DMVs and catalog views: – sys.assembly* shows basic information about the assemblies stored – sys.dm_os_memory_clerks – sys.dm_clr* – sys.dm_exec_query_stats – sys.dm_exec_requests – sys.dm_exec_cached_plans
![Page 23: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/23.jpg)
Monitoring
![Page 24: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/24.jpg)
Tips
• Database server CPU is valuable• SQLCLR is about making DB
programming easier• Managed code has a performance
advantage over T-SQL with respect to most procedural computation, but for data-access T-SQL generally fares better.
![Page 25: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/25.jpg)
Questions?
![Page 26: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/26.jpg)
References
• SQL Server 2005 distilled by Eric Brown, ISBN: 0321349792
• http://msdn.microsoft.com/sql/learning/prog/clr/default.aspx?pull=/library/en-us/dnsql90/html/sqlclrguidance.asp• http://blog.develop-one.com• This presentation on:
– www.sdn.nl – www.develop-one.com
![Page 27: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/27.jpg)
AddendumNew SQL Server 2005 features
• Presentation by Dinesh Chandrasekhar
• www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx• http://msdn2.microsoft.com/en-us/library/ms170363(SQL.90).aspx
Adobe Acrobat 7.0 Document
![Page 28: SDC - Programming the CLR in SQL Server 2005.ppt (1.51 MB)](https://reader034.vdocuments.us/reader034/viewer/2022042516/5599694a1a28ab046a8b4674/html5/thumbnails/28.jpg)
Evaluatieformulier
Vul je evaluatieformulier in en maak kans op een van de prachtige prijzen!!
Session Code: DB.08