Re: SQL 2005

There is a ton of info on this on the web, either community sites or msdn
however looking at this in comparison with XPs, Java Implementations and
other .Net implementations that I have seen(I have not seen Borlands for
NDatastore yet)

The SQL Server implementation is not just Stored Procs, it also supports
triggers, User Defined Types, Aggregates and Functions. SPs are most often
demo'd because they are easy to "get" in terms of the code required and the
ease, in many ways I think types and functions are going to be more heavily
used, in fact it was a function that was demo'd at Borcon last year. Today
in SQL Server 2000 there is no way to write UDAggs, the type extensibility
system is not that rich.

When you compare the SQLCLR implementation against other exensibility
systems, it works very tightly in proc with the SQL Server Engine(most
others run out of proc), this is possible because of the security model and
the new hosting APIs available as part of NetFX 2.0(this implementation
would not have been practical using NetFX1.1), these hosting APIs mean that
when the CLR demands memory, instead of getting it from the OS it gets it
from SQL Server's Memory Manager, the same is true of thread demands etc.
SQL Server also controls the GC so that it kicks when it makes sense from a
SQL Server perspective in terms of workload.

The security model allows SQL Server to verify what the code is going to do
using the metadata before registring the assembly, as its actually being
loaded in. Depending on the security level its possible for an admin to
prvent code running in the server that does "bad" stuff like synch
primitives, direct memory allocation, accessing the network etc, and in fact
some namespaces are banned from ever being used (System.Windows.Forms being
my fav, and yes I have seen an XP that tried to pop up UI...), thus by
protecting the system through rich metadata, its possible to allow SQLCLR
code to run in a much more trusted manner than XP code does.

One of the challenges with writing XPs is the programming model, having to
messa round with the SRV structure, ODS etc is not really easy, with SQLCLR
the programming model is ADO.Net(actually to be correct its SQLClient), the
provider knows when it is running in proc vs out of proc and changes some
behaviours(ie no network stack roundtrip in-proc) but almost all of the code
is the same.

Every XP thats written has to be written from scratch(there is an ATL
template now but not many folks use it), also debugging can be more than a
little challenging. With SQLCLR and VS you just do file new sql server
project and its just like another piece of .net cod eat this point, you can
set breakpoints cross step from clr code to t-sql etc. I'll poit out that
everything VS does is doc'd and should borland choose to they can do
everything as well.

Now with all that I have said T-SQL is not going away and is still a needed
skill, T-SQL is the best choice for complex data/set based logic, CLR is the
best for a range of other types of apps, in fact more investments have been
made in T-SQL in this release since at least SQL7 and maybe even more than
that.

-Euan


"James K Smith" <jksmith@xxxxxxxxxxxxxx> wrote in message
news:42c38ee2$1@xxxxxxxxxxxxxxxxxxxxxxxxx
> What kind of advantage would this provide over extended stored procedures?
>
>
> "Phil Barber" <pbarber1@xxxxxxxxxxxxxx> wrote in message
> news:42c3368b@xxxxxxxxxxxxxxxxxxxxxxxxx
> > I recently attended a VS2005 road show, they talked about how in SQL
2005
> > the CLR is now supported in stored procedures and triggers. it looks
like
> it
> > has real possibilities. the guy created a stored procedure from within
> the
> > IDE which had C# classes and procedures right in the stored proc code.
He
> > went on to say the Delphi.Net would support this as well. I was
wondering
> if
> > anyone has heard if Borland will have this feature in the next version
of
> > Delphi?
> > Phil.
> >
> >
>
>


.

Relevant Pages

  • Re: nested stored procedures and returning lots of rows
    ... I initially thought of using nested stored procedures and returning ... proc then the called stored procs can't see it ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Another Concurrency Issue!!!!
    ... > allocating in blocks, using tables with identities, etc. ... > Columnist, SQL Server Professional ... I have a proc which returns a value after some DML operations on ... >> TIA ...
    (microsoft.public.sqlserver.programming)
  • Re: How change SQL Server Authentication mode without EnterpriseManager?
    ... and rather said that there is no T-SQL command to do it. ... >requires the use of undocumented system extended stored procedures. ... if you use SQL Server Profiler you can capture the commands ... the standard warnings apply to using undocumented SQL Server ...
    (microsoft.public.sqlserver.security)
  • Re: Performance Problem Using ADO and Stored Procs
    ... >I have a stored proc that executes in < 15 seconds through Query Analyzer. ... If I execute this proc ... > of CPU activity on the server hosting SQL server for the SQL server ... > Private Function RunProc(vntDB As Variant, strProcName As String, ...
    (microsoft.public.data.ado)
  • Re: Stored proc timeout issue
    ... The program Opens the proc and times out after 2 minutes. ... proc from sql server mgmt studio, ... I began diagnosing the problem by commenting out various lines of code in the stored proc ... One thing that remained changed was the command timeout value. ...
    (borland.public.delphi.database.ado)