Re: SQL Server 2005: CLR functions vs SQL functions

Erland,

Actually not. You can load assemblies as hexstrings as well. Try
Script As in Mgmt Studio on an assembly to see what I'm talking about.

What would be very nice is something like:

CREATE PROCEDURE my_sharp_proc @a int, @b int WITH LANGUAGE C# AS

and the rest of the batch would be the procedure definition in C#. SQL
Server would compile it on the server, and create an assembly and all
that jazz for you.


Hey--that's great! Thanks for pointing that out to me. It will save
me from having to copy the assemblies to the production server. Your
modified CREATE PROCEDURE would be even better but I suppose we'll need
to wait for SQL Server 2010 (?) for that.

Yes, the error trapping is quite a nice addition. Perhaps your
standard error handler can write to your new XML log and send you an
e-mail if the severity level is a certain level.

I'm just now experimenting with all of the new XML capabilities.and I'm
trying to see where I might put them to use. I haven't yet covered the
Service Broker. I have written one SSIS package that is now running in
production, but I must admit that it was far more complicated to use
than DTS, at least for simple things.

One thing that disappointed me was the PIVOT function. It seems that
the column names must be hard coded. I suppose that I can get around
this by using dynamic SQL or by writing a CLR procedure that calculates
the column names, but this is what I was hoping to avoid. If you know
a clever way around this, I'd love to hear it.

There's so much new ground to cover, it's quite amazing.

Bill

Erland Sommarskog wrote:
(billmiami2@xxxxxxxxxxxx) writes:
I'm aware that the assemblies are stored in SQL Server upon running
CREATE ASSEMBLY so there is no direct dependency. However,the
assemblies must initially be made accessible to the SQL Server so that
it can bind to them. Also, if an assembly is changed, the revised file
must be again made available, so you're still having to manage files
that you wouldn't need to manage had you used T-SQL only.

Actually not. You can load assemblies as hexstrings as well. Try
Script As in Mgmt Studio on an assembly to see what I'm talking about.

What would be very nice is something like:

CREATE PROCEDURE my_sharp_proc @a int, @b int WITH LANGUAGE C# AS

and the rest of the batch would be the procedure definition in C#. SQL
Server would compile it on the server, and create an assembly and all
that jazz for you.

Can you foresee using some of the new T-SQL enhancements in your
product? I have a couple of projects that involve hierarchies and the
recursive Common Table Expressions and Cross/Outer Apply have made for
far simpler queries than I could have created in SQL 2000. I haven't
used them in production yet, but the ranking functions look very nice
too.

The absolutely biggest improvement in SQL 2005 is the error handling.
I'm considering modifying our load tool so that if there is no BEGIN
TRY in the code, the load tool would splice in BEGIN TRY in the
beginning of the procedure (it splices in SET NOCOUNT ON today), and
then tack on an standard error handler at the end.

We have also discussed improving our auditing (which is not very strong
today). To this end the xml data type can be very useful. You can write
standard trigger that transforms the contents of inserted to one XML
document per row, and the store that in a common log table. We would then
write a generic log reader on top of this.

I would also expect row_number() to very useful. We don't work that
much with hierarchies, but certainly there will be one or two CTEs.

I have hardly looked at Service Broker myself, but there are some
people who developing new stuff that will use Service Broker. (This is
for a new customer that will run SQL 2005 from day one.)

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: eliminate unnecessary joint in the view
    ... select val1 from view1x2x3 ... There are situations where SQL Server will realise that a table is not ... CREATE TABLE Table1(Key1 int NOT NULL PRIMARY KEY, ...
    (microsoft.public.sqlserver.programming)
  • Re: Executing Stored Proc from file in Ado .NET
    ... Here is a snippit of one of the SQL script: ... DECLARE @CORE_CORE3572301500_release_exists INT ... SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ... SO someone tell me what am i doing wrong, I'm talking directly to SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: outbound SOAP requests from native SQL
    ... webservice but for including whole dotnet framework functionalities. ... and register that assembly in sql server. ... assemblies... ...
    (microsoft.public.sqlserver.xml)
  • Re: Varchar as identity
    ... I tried the below solution but it fails in case of concurrency. ... number int NOT NULL, ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.server)
  • Re: Update Identity Column
    ... (not all of it SQL Server, mind you), so believe me, I know my way around. ... (x INT NOT NULL PRIMARY KEY CLUSTERED, ... INSERT INTO tbl VALUES; ...
    (microsoft.public.access.adp.sqlserver)