Man, I hate being late for talks, especially one being given by Bob Beauchemin.
- Started with an example of a RegEx parser for Area code. A couple of key points came out.
- Indexes are limited to 900 bytes total
- To get a persistent computed column, use the keyword PERSISTED on the column definition
- Persisted computed columns can be used an an index
- Functions must be both deterministic and precise to be used an index and as persisted index
- SQL Server believes what you tell it for CLR functions based on attributes on the class
- Prefer CLR for scalar UDFs
- Use DataAccessKind and SystemDataAccessKind to tell SQL if you an object model to access data from tables or not. Set them on only is you need to do data access in a function.
- T-SQL UDFs perf is improved in SQL 2005
- Table Value Functions probably should be done in SQLCLR too.
- Inline TVFs have to be done in T-SQL
- Multistatement TVFs are better in CLR becuase it avoids an intermediate storage step
- VB implication for this is the modified value in VB.NET is reference System.RuntimeServices, then add to the ByRef
- Better choice than using sp_regread for for accessing file system, event log, web services and registry
- Showed reading a file using impersonation and SQLCLR
- Showed using System.Security.Principal and SqlContent.WindowsIdentity()
- Can't access data (from SQLServer) using an impersonated context in future betas.
- T-SQL is best for Data Access code
- Static SQL can be syntax checked when the object is created, not when its run
- T-SQL can be parellelized
- Determinism and Precision are checkable the query parser
- Sharing of stack frame and data buffers for T-SQL, transition for using CLR
- Break even point for to CLR transitions is about six integer operations
- Don't trust benchmarks unless you see the code being tested itself.
- Talked about inhertiance
- Need to include StructLayout attribute and System.Runtime.Interop
- T-SQL is blissfully unware of inheritance
- Discussion of IN and OUT parameters
- Talked about RETURNS NULL ON NULL INPUT
- Talked about System.Data.SqlTypes and the mapping of SQL Decimal which is different than .NET decimals
No, it didn't actually fall into a real black hole, rather, I feel
into the DAT cabana, and it's been a blast. But I have yet to go to a
single session. You might think I'd be kind of down about that, but
actually, it feels like I'm learning ton here.
Monday, a
wandered up to the staff overflow room to see the Steve Ballmer
keynote, then started by time here in the cabana. It seems that Euan
caught me on camera and that up on the Channel 4.5 website (here's the video in question). Did go the TechEd shop and get a few books including Roger Wolter's new Service Broker title, Donald Farmer's Integration Services title and something I've wanted in my collection for a while -- XML for Data Architects.
It's not like I'm going to have time to read them immediately, but they
are small enough to work really well for in-flight reading.
So what am I learning? Well, the list is pretty long, but here's some highlights:
- There are cases where SQL integration services are actually faster for bulk loading than BCP.
- Many of the folks I've talked to have had questions about
replication vs. data mirroring. The important thing to remember is that
replication is really mean to distribute copies of working data where
as DB mirroring is meant for disaster recovery. Maybe fellow SQLJunkies
blogger Rick Heiges will blog about that... :)
- As we now know, Reporting Services will be made available for all
SKUs, including SQL Server 2005 Express Edition (very cool IMHO), but
even better, we're getting Report Builder starting in WorkGroup
edition. That's a major change from when it was first announced that it
would be in Enterprise only.
- Doing a large volume of inserts (say, 1m rows) into a table with an
automatic identity field is understandably slower than -- when you are
letting SQL Server do both the insert and the identity vale generation
-- than BCP or other methods where you have a pre-generated identity.
That's fine -- you have to spend the time to do that work some place,
some time. One of the best ways around that is to use Integration
Services since has a faster identity generation methods that the SQL
engine does.
- The syntax for index (as Adam points out below, its not just index hints, its any hint)
hints has changed, at least for CTEs. The hint should be given in the
query immediately following the CTE, roughly as the example below shows.
It looks like I will get the chance to go at least one session today
since Bob Beauchemin is giving is CLR vs. T-SQL talk around 3:00 PM
today. That's a must see for me!
Example CTE hint syntax
with f(x) as (
select cast(1 as bigint)
union all
select x
from f)
select x from f
option(maxrecursion 255)
Note that the default MAXRECURSION value is 100.