Managed Stored Procedures

8 Sep 2006 10:45 AM

rocket salad

I would appreciate hearing some different points of views on this subject.

Are / how are you using them? Are you avoiding them? Why?

Are these so different to the extended stored procedures of 2000?

I'm looking into what's new in 2005 before we start migrating our software -
I've found it hard to find any objective views so far.

All comments are appreciated.

8 Sep 2006 11:19 AM

Sreejith G

Use Transact-SQL when the code will mostly perform data access with little or
no procedural logic.

Use managed code for CPU-intensive functions and procedures that feature
complex logic, or when you want to make use of the BCL of the .NET Framework.

--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]

Show quoteHide quote

"rocket salad" wrote:

> I would appreciate hearing some different points of views on this subject.
>
> Are / how are you using them? Are you avoiding them? Why?
>
> Are these so different to the extended stored procedures of 2000?
>
> I'm looking into what's new in 2005 before we start migrating our software -
> I've found it hard to find any objective views so far.
>
> All comments are appreciated.

8 Sep 2006 11:37 AM

Hilary Cotter

There are some functions which require significant computational or non set
based processing. These could be best handled by the clr.

We use it for regex's.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Show quoteHide quote

"rocket salad" <rocketsa***@discussions.microsoft.com> wrote in message
news:8C76AC99-2677-46BD-B435-0348AC737361@microsoft.com...
>I would appreciate hearing some different points of views on this subject.
>
> Are / how are you using them? Are you avoiding them? Why?
>
> Are these so different to the extended stored procedures of 2000?
>
> I'm looking into what's new in 2005 before we start migrating our
> software -
> I've found it hard to find any objective views so far.
>
> All comments are appreciated.

8 Sep 2006 2:30 PM

David Browne

<DIV>"rocket salad" <rocketsa***@discussions.microsoft.com>
wrote in message

news:8C76AC99-2677-46BD-B435-0348AC737361@microsoft.com...</DIV>>I would

appreciate hearing some different points of views on this subject.

>
> Are / how are you using them? Are you avoiding them? Why?
>
> Are these so different to the extended stored procedures of 2000?
>
> I'm looking into what's new in 2005 before we start migrating our
> software -
> I've found it hard to find any objective views so far.
>
> All comments are appreciated.

First off, CLR Functions are extremely useful for extending SQL. They do
things you just can't do, or can't do well in TSQL. And they are usable
from plain SQL. So absolutely using CLR functions.

Second, Extended Stored Procedures are so troublesome, so dangerous that
replacing them with CLR procedures is a no-brainer.

CLR Stored Procedures for routine data access are more controvercial. If
you have a shop with strong .NET developers and no TSQL developers, you
might code all your data access in .NET and consider running this logic in
the database. But today it's a tradeoff, as TSQL is a better language for
pure data manipulation than the .NET languages. But Data Access from CLR
languages is about to get a whole lot better with LINQ
(http://msdn.microsoft.com/data/ref/linq/). In the future .NET languages
should provide the best of both worlds.

David

8 Sep 2006 3:12 PM

rocket salad

Thanks so far, everyone - some good comments.

The Linq project looks very interesting - I'd not come across that before,
but it is a logical step.

My mind at the moment is set on trying to identify where we would benefit
from using the CLR rather than standard T-SQL procedures. We do a lot of data
manipulation and moving of data but I'm unsure we have a current need for
more procedural based logic in most of what we do.

There is also the issue of SQL developers not necessarily being able to
program C# or VB.NET, so managing the code after a developer has moved on may
get difficult.

I can see the benefits of this in the right environment, but unless my
client's requirement's change, I can't see it being used here for more than a
couple of procedures.

Thanks for the input,
Pete.

Show quoteHide quote

"David Browne" wrote:

>
>
> <DIV>"rocket salad" <rocketsa***@discussions.microsoft.com>
> wrote in message
> news:8C76AC99-2677-46BD-B435-0348AC737361@microsoft.com...</DIV>>I would
> appreciate hearing some different points of views on this subject.
> >
> > Are / how are you using them? Are you avoiding them? Why?
> >
> > Are these so different to the extended stored procedures of 2000?
> >
> > I'm looking into what's new in 2005 before we start migrating our
> > software -
> > I've found it hard to find any objective views so far.
> >
> > All comments are appreciated.
>
> First off, CLR Functions are extremely useful for extending SQL. They do
> things you just can't do, or can't do well in TSQL. And they are usable
> from plain SQL. So absolutely using CLR functions.
>
> Second, Extended Stored Procedures are so troublesome, so dangerous that
> replacing them with CLR procedures is a no-brainer.
>
> CLR Stored Procedures for routine data access are more controvercial. If
> you have a shop with strong .NET developers and no TSQL developers, you
> might code all your data access in .NET and consider running this logic in
> the database. But today it's a tradeoff, as TSQL is a better language for
> pure data manipulation than the .NET languages. But Data Access from CLR
> languages is about to get a whole lot better with LINQ
> (http://msdn.microsoft.com/data/ref/linq/). In the future .NET languages
> should provide the best of both worlds.
>
> David
>
>
>
>

9 Sep 2006 12:32 AM

Steve Dassin

"David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in
message news:urMqdN10GHA.2196@TK2MSFTNGP03.phx.gbl...
> .
> But Data Access from CLR languages is about to get a whole lot better
> with LINQ (http://msdn.microsoft.com/data/ref/linq/). In the future .NET
> languages should provide the best of both worlds.

From:
'The LINQ Project'
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/linqprojectovw.asp

> It's important to note that by building query capability directly into the
> local programming language.

A long time in coming. A database with an imperative language.

>, developers get the full power of the relational model without having to
>statically bake the relationships into the CLR type.

Absolutely not! They have no clue what they're talking about.
They are serving up the same sql food in a fancier dish.
I suggest the MS team (and everyone else) take a look at "Dataphor" for a
'relational'
model within an imperative framework.
www.alphora.com

The more things change, the more they remain the same:(

best,
http://racster.blogspot.com
Nip nonsense before it gets Tucked in your head.

11 Sep 2006 2:57 AM

Stu

The application developer in me can appreciate the beuty of having a
single syntax for accessing "data" beyond that which is stored in a
relational database, but the DBA side realizes that it's a long way
off. One of the reasons we have proprietary SQL dialects is that
different engines interpret SQL commands differently. LINQ currently
doesn't address those issues.

Now, for most simple queries, LINQ will probably be OK; but in
high-transaction, maximum-performance SQL Server applications, I don't
think LINQ will be ready for a long time.

Stu

David Browne wrote:
Show quoteHide quote

> <DIV>"rocket salad" <rocketsa***@discussions.microsoft.com>
> wrote in message
> news:8C76AC99-2677-46BD-B435-0348AC737361@microsoft.com...</DIV>>I would
> appreciate hearing some different points of views on this subject.
> >
> > Are / how are you using them? Are you avoiding them? Why?
> >
> > Are these so different to the extended stored procedures of 2000?
> >
> > I'm looking into what's new in 2005 before we start migrating our
> > software -
> > I've found it hard to find any objective views so far.
> >
> > All comments are appreciated.
>
> First off, CLR Functions are extremely useful for extending SQL. They do
> things you just can't do, or can't do well in TSQL. And they are usable
> from plain SQL. So absolutely using CLR functions.
>
> Second, Extended Stored Procedures are so troublesome, so dangerous that
> replacing them with CLR procedures is a no-brainer.
>
> CLR Stored Procedures for routine data access are more controvercial. If
> you have a shop with strong .NET developers and no TSQL developers, you
> might code all your data access in .NET and consider running this logic in
> the database. But today it's a tradeoff, as TSQL is a better language for
> pure data manipulation than the .NET languages. But Data Access from CLR
> languages is about to get a whole lot better with LINQ
> (http://msdn.microsoft.com/data/ref/linq/). In the future .NET languages
> should provide the best of both worlds.
>
> David

14 Sep 2006 9:37 PM

Brian Gideon

Stu wrote:

> Now, for most simple queries, LINQ will probably be OK; but in
> high-transaction, maximum-performance SQL Server applications, I don't
> think LINQ will be ready for a long time.
>
> Stu

Stu,

I think you're right. But, to me anyway, the place where LINQ really
excels is in post processing a resultset after it has been returned
from the database. We've never really had the ability to take any
collection class and "query" them using strongly-typed idioms to get a
subset. It's an incredibly useful feature even outside the realm of
database processing.

Brian