The Old Joel on Software Forum: Part 3 (of 5) - Why do I want to host managed code in SQL Server?

Why do I want to host managed code in SQL Server?

As we've all heard, Yukon will do this. I've tentatively filed this under "I can make toast in the oven, but the toaster is the right tool for the job."

What am I missing?

Ian Olsen
Friday, November 14, 2003

It's for poor schlubs like me who know C# really, really well, but don't know T-SQL.

It also allows you to call out to managed libraries very easily from within SQL Server. So you can, for example, send data over a socket on a table insert with little pain.

Chris Tavares
Friday, November 14, 2003

> What am I missing?

The right question: "Why would Microsoft want me to host managed code in SQL Server?"

There may be good technical reasons as well. Oracle 8i offers Java integration which allows you to write stored procedures and triggers in Java instead of PL/SQL.

While many eschew this approach, it seems like it could be powerful if used appropriately: you talk to the DB via stored procedures, and the stored procedures are written in a modern, high-level language instead of a language abortion straight out the 80s. (and you could unit test your stored procedures better, yadda yadda yadda). I don't see why it couldn't work with C# just as well.

Portabella
Friday, November 14, 2003

I too think this is a nice idea. Being both a T-SQL and C# jockey, I look forward to being able to consolidate. The interesting thing will be how it might affect speed and best practices in n-tier architectures.

m
Friday, November 14, 2003

"It's for poor schlubs like me who know C# really, really well, but don't know T-SQL."

No offense but a lot of dba's will hate people like you because you make some slow ass code because you don't understand set based languages like sql and will do things with c# or vb that make the server crawl that would ordinarily work well in your language. "The cursor seemed like the way to do it."

I don't think this is a great boon for SQL Server, I think this is do it because Oracle has Java and we don't want to be seen as behind.

Mike
Friday, November 14, 2003

Every so often I'll be writing a stored procedure and think "dammit, I know how to do this in a procedural language..."

IMHO with some minor exceptions it's not for the mainstream data tier stuff - it's for all the dozens of times you've had to do something with the database (import, export, thresh data, analysis, yadda, yadda, yadda) and ended up writing a ten-line console app to get the last mile.

Now you can fold those ten lines into the SP or DTS and you have a consolidated solution.

That's my initial reaction. I'm sure, like all things ("Why would anyone want to put scripting code in HTML?") it will evolve and provide opportunities we never would've even thought of.

Philo

Philo
Friday, November 14, 2003

> but a lot of dba's will hate people like you

Like EJBs, this may be yet another (probably futile) attempt to make DBAs irrelevant.

I haven't seen many people use the Oracle/Java integration to do things like notifying the application when data has changed instead of polling the database to figure it out. But they're certainly possible.

It tends to muddle the difference between tiers 2 and 3 in the classic 3 tier model, which is perhaps what Microsoft wants (that's certainly what Oracle intends).

Portabella
Friday, November 14, 2003

Mike,

SQL <> T-SQL If someone doesn't know the fundementals of SQL then it doesn't matter what langauge they use. Cursors are slow regardless of using T-SQL or C#. With T-SQL it was really, really hard to do stuff that you took for granted in other languages. I think it will be great to get rid of it.

DJ
Friday, November 14, 2003

Hmmm. If this is shoring up a feature list deficiency compared to Java/Oracle, that makes eminent sense. Extending all of the .NET languages to include syntax for querying/manipulating relational data, however, makes no sense at all.

I had assumed you would be able to call out to managed code and do generally managed code things. I did not think this meant they're going to make c# capable of doing, say:

SELECT Address
FROM People
WHERE ZipCode=12345

I mean, that's what SQL is for. And for "poor schlubs" who don't know T-SQL, would learning T-SQL be any harder than learning whatever they'd add to c#? This poor schlub says no.

Ian Olsen
Friday, November 14, 2003

That muddling of the 2nd and 3rd tiers is exactly why I posted, in fact. I'm frequently in disagreement with a coworker who likes to do damn near everything in SQL, despite my efforts to keep the application logic cleanly encapsulated in the application tier.

He's using this move to solidify his position in those disagreements, and I'm not happy! ;\

Ian Olsen
Friday, November 14, 2003

> I'm frequently in disagreement with a coworker who likes to do damn near everything in SQL,

For me the key would be whether the code runs outside the DB. This is obviously not what Oracle intends, but it's perfectly viable to have a 2nd tier -- which runs in the DB, but is developed standalone, and a thin DB layer.

But I'd bet that 99% -- including your coworker -- do it the other way.

Portabella
Friday, November 14, 2003

One of the great things about this is that you can now write your sotred procedures in COBOL!

http://www.netcobol.com/news/prelease/pr20031028.htm - "NetCOBOL for .NET Brings COBOL Stored Procedures to Microsoft SQL Server 'Yukon' Safely"

RocketJeff
Friday, November 14, 2003

In Access, it's very easy to drop into VBA, write some procedural code, and then use that directly in queries. I imagine that hosting managed code in SQL Server is partly inspired by Microsoft's ongoing effort to migrate people to MSDE / SQL Server from Access.

There are some occasions where a solution is best done or only possible outside of SQL (Philo listed some good examples -- I encounter this mostly with import/export and analysis). Today, you can call non-T-SQL code from T-SQL if you write extended stored procedures, or shell out to the command line, or use a COM component but there is a relatively large amount of plumbing overhead involved in doing any of these, as well as the maintenance overhead (is the COM component registered, is the EXE in the path, are permissions set correctly). Tight integration between SQL Server and .NET will greatly facilitate this external processing and code level security will relax some of the maintenance burden and risks.

So these two things, coupled with the Oracle/Java competition makes it a no-brainer. It probably won't provide anything that hasn't been possible in the past through other means but it will make it much easier (in the same manner that ASP.NET makes the intregration of the presentation and business layers much easier than with the classic ASP / COM components combo). I will say that I do think it's being overhyped but hype in the software industry isn't anything new.

SomeBody
Friday, November 14, 2003

To be honest, I look at this as mainly a replacement for extended stored procedures - those DLL's you had to write in C/C++. C#/VB.NET are *soo* much easier to code in.

As far as learning the "language extensions": not having read the docs I may be missing something, but wouldn't the so-called extensions be just another object model? Learning how to use a new object is a LOT easier than learning a new language with a different programming paradigm.

And personally, I'd much rather have a DBA do the database stuff, and do it right. I suck at DB stuff, I admit it. Unfortunately, many projects don't have the luxury of a dedicated DBA.

Chris Tavares
Friday, November 14, 2003

"Unfortunately, many projects don't have the luxury of a dedicated DBA"

Chris, I am looking for a new job now and have tremendous DB skills. I can write Stored Procedures in T-SQL until my fingers bleed. :P

I think a lot of this C#/SQL Server and Java/Oracle stuff comes about from company picnics when all the different developer teams get together and they get this great idea. Marketing probably likes it overall too as it gets all their solutions interweaved with one another. Certainly nobody looses in this, unless they charge more.

m
Friday, November 14, 2003

"Like EJBs, this may be yet another (probably futile) attempt to make DBAs irrelevant."

Development DBA's haven't been relevant for me for a long, long time. In fact, once I learned RDBMS basics (on Oracle 7.3), I never used a development DBA again (which is good, since all the ones I met after that were incompetent)

Philo

Phi1o
Friday, November 14, 2003

"Cursors are slow regardless of using T-SQL or C#. "

I agree about T-SQL, can't speak to C#. My point was I've seen a lot of procedural guys wanting to go with a cursor in T-SQL because they think like that.

Mike
Saturday, November 15, 2003

> Development DBA's haven't been relevant for me for a long, long time.

I can emphasize.

More than once I've been tempted to throw the DBA off the project so that I could actually get some work done. And often wondered why we need a guy to type "ALTER TABLE...." when I could do it just as well. (At one job, in fact, I used to send the DBA the SQL I wanted, and he would simply execute it).

But that said, DBA jobs don't seem to be hurting, not any more than the rest of tech sector, anyway, and *good* DBAs, as opposed to "I-must-control-the-database" ones, are still very valuable.

Portabella
Saturday, November 15, 2003

> If this is shoring up a feature list deficiency compared to Java/Oracle, that makes eminent sense.

Well, except for the fact that the Oracle/Java integration isn't exactly setting the world on fire. The preference in J2EE seems to be:

Web front-end + Java application server + RDBMS

with most of the weight in the middle.

Disrupting that model is probably more of a reason than competition with Oracle or Java.

Portabella
Saturday, November 15, 2003

I agree, nothing contribute to a project like a good DBA. The other 90% tend to slow things down because they can't make an easy transition from the production environment to the goal of getting a development task done. Two very different things to be fair.

In the IT biz, the single most valuable skill is DB design. But don't tell anybody!

m
Saturday, November 15, 2003

For those interested in the official word of learning more about the scenarios involved.

In no way is MS saying the C# will take over TSQL. Different languages for different jobs.

Go to: http://msdn.microsoft.com/events/pdc/agendaandsessions/sessions/default.aspx
and download slides for DAT400 from the PDC.

- Rick

Rick Watson
Monday, November 17, 2003

Personally, I would love it if this gives me, the the front-end and middle-tier developer, an easier way to call stored procedures.

You write the stored procedure (GenerateNewCustomerId) on the DB.

You add a reference to your DB package in Visual Studio.Next 200N.

You write code that calls your DB stored procedure, getting full type-safety and intellisense.

This would be instead of looking up the stored proc in the DB, manually creating a routine to call the stored proc and massage the .NET types into SQL types, open connection, call procedure, get values from out parameters, close connection.

Customer newCustomer = new Customer();
newCustomer.CustomerID = My.Database.Schema.GenerateNewCustomerID();

Richard Ponton
Monday, November 17, 2003

Sounds good to me.

I'd have been happy to just extend T-SQL to handle errors in a less verbose way than "if @@error <> 0" after every statement.

If we have to have all the rest of C# to get the try...catch...finally, then so be it.

Steve Jones (UK)
Tuesday, November 18, 2003