Matt Whitfield
CLR can do user-defined aggregates.
Do
1. Know your transactions
TransactionScope() class.
single connection = local transaction
different connection = MSDTC = two-phase commit
2. Know structures v. classes
Reference types are passed around via their reference (classes) ABCDEI ->F all gets passed back
Value types are passed in their entirety. (structures) ABCDEI -> F, leaves two different lists, with only the changed value in the function.
3. Dispose your iDisposables
if an object implement IDisposable, it will want you to Dispose() on it explicitly, releasing resources before GC
Guarantee disposal by using() {} blocks <– this will always dispose on exit of the block
4. Aim to write SAFE code
SAFE is a restrictive permission set. can\’t affect the SQLServer process or anything on the outside.
EXTERNAL_ACCESS allows access to resources outside of SQLServer (ie web service call in a trigger) <– not good 😦
UNSAFE – just don\’t. OK ?
5. Understand the GAK-lack
\’blessed\’ assemblies can be loaded from the GAC. And there aren\’t many of them in the GAC
Other assemblies need to be loaded using CREATE ASSEMBLY
6. Understand managed memory
come from the MemToLeave address space (kind of goes away)
multiple pages can be requested.
Native memory is single-page requests only
7. Use the context connection
use \’context connection = true\’
standard connection requires elevation to EXTERNAL_ACCESS
8. Write efficient code
It makes your own life easier.
If it\’s complex, put it into a profiler and see what it does first..
9. Understand \’boxing\’
take a value type, and send it to something that expects a reference type – it\’s put into a \’box\’, and is then treated as a reference type
but remember that 1==1, but (object)1 != (object)1
10. Use dictionaries
finding an object by key rather than looping is the same as an index seek rather than an index scan.
need to override the GetHashCode and Equals method of Object
-note: this makes is unsuitable for fuzzy matching
Don\’t
1. Add strings together
strings are immutable (can\’t change)
when you add 2 strings together, you actually create a 3rd string
use StringBuilder
2. Go overboard
You can do good stuff and bad stuff.
Don\’t wrap t-sql in CLR – doesn\’t add value
3. Forget that data scales
don\’t implement anything that requires a fixed data size
don\’t assume that the fastest access method on small data sets is still as fast on large ones.
4. Treat null as an afterthought
Always assume you receive NULLs as a either a parameter or returned data
Nullable type represent NULL with the HasValue property – SQL uses ISNULL property
5. Use finalisers
GC calls the finalisers (destructor) for you, so you don\’t have to.
Finalise makes the object a Generation 1 object for GC (look this up)
6. Re-invent the wheel
take time to research what you want to do. The function may actually already exist.
7. Access data in scalar functions
String manipulation is OK
Accessing data in scalar functions is NOT cool. Trust me.
8. Over optimise
simple, well structured code often has suitable performance characteristics
9. Move the middle tier in
it will make it far more difficult to scale out when you need to.
10. Call Environment.Exit
this just bins you out of SQLServer, sometimes VERY ungracefully – won\’t roll back, won\’t finish your sp call…..
The Modern Dataist 