Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The CLR in SQL Server 2005


The CLR in SQL Server 2005

Author
Message
Francisco Lopez
Francisco Lopez
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 45
I have yet to see a good example of what can be done with the SQLCLR that can't be accomplished with some T-SQL savvy.

I think the idea of the CLR in SQL Server was one of those ideas someone brings up in a staff meeting, sounds great to management, and sounds even better to marketing.

In addition, I believe the SQLCLR is a gross violation of the DRY principle.

What's next? - the SMSCLR, the VSCLR, the POWERPOINTCLR, the COM+CLR, *8,.,

While a developer myself, I have never blamed a DBA for being territorial about the Back End. However, since technology drives new features; and vendors decide how they implement them, chances are, in the end, we will be puting up with crap in the Back End (no pun intended =).


{Francisco}
YSLGuru
YSLGuru
SSC Eights!
SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)

Group: General Forum Members
Points: 959 Visits: 1659

Straegen, I believe that you recieved the responses you did because your first few posts indicated that you believed that T-SQL was headed for the grave in it's entirety and rightfully so. In the lats post you somehwat contreadicted your early posts by stating

'I agree queries will still be run, but that is what they will be... queries'

I'm not so sure that the rest of us understand exactly what your stance is, I know I don't and maybe it's just me. In one post you say that it's time for T-SQL to head towards the grave and in another you state that it will still be used for queries. So what exactly is your stance on this issue? Are you saying that it's time for T-SQL to disappear from the face of the earth or are you saying that it's time for T-SQL to be used strinctly for working directly with relational data?

Thanks

Ed



Kindest Regards,

Just say No to Facebook!
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36066 Visits: 18736
Francisco,

I haven't seen may good cases, but a few interesting ones were check constraints on tables. Trying to be sure that an IP address was valid, or that a phone number conformed to a valid format (either hyphens or dots), etc. These types of string manipulations are very complex in T-SQL and trivial in C# or VB.NET. I also suspect that some financial or complex Analysis services rollups will be much easier in a CLR languange than T-SQL or MDX.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Straegen
Straegen
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 3

No contridiction from my viewpoint. SQL and T-SQL aren't the same thing. I don't think SQL is going anywhere since it does what it does quite well, but the way we access and manage the results from queries, loops, variables, error handling, transactions, etc (i.e. T-SQL) will likely shift to CLR in the long term. If you haven't programmed C# or VB.net, you will likely fall in love with them once you see how much easier data manipulation can be in a language that was designed for it.

I don't have a problem with someone disagreeing with me since my points are only speculation, but that first poster who called me silly either didn't read my post well and missed the point or has a screw loose. I am not being irrational. My point is that anyone who uses both TSQL and some other next gen language will tell you that TSQL is weak in many areas and it is time to get something better. It isn't marketing or some spitball idea (at least not completely). Programmers have been asking for a real language in SQL for quite a while.

The original author of the story made it sound like CLR wasn't "all bad" and I believe that CLR integration is great. That is where our points divulge. My additional thoughts about TSQL going the way of dino are just thoughts that reminded me of the old COBOL switch (back when more lines of code were written in COBOL than any other language).





Straegen
Straegen
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 3

This gets back to my COBOL arguement. COBOL could do almost all the things that next gen languages could do, but it wasn't adept at it. I believe T-SQL suffers from the same problem. Complex loops, variable management, inline procedures, ability to create and manage objects can all be done or simulated in T-SQL. It is just easier to do in other next gen languages.

This does get into another discussion flying around. When I refer to SQL, I am referring to the set based SELECT, UPDATE, INSERT, DELETE statements. When I refer to T-SQL being replaced by CLR, I am referring to the logical part of SQL such as flow control, variables, etc that normally reside in stored procedures. I know they are the same animal inside SQL Server and that seems to be leading to some confusion.





Francisco Lopez
Francisco Lopez
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 45
Steve,
I would not consider those examples "GOOD".

-- i.e. check constraint
([Telephone] like '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' or
[Telephone] like '[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9][0-9]')

You can do similar things for IPs. I guess it is a bit complex but C# or VB code is not going to be, by any order of magnitude, any rosier.


{Francisco}
Straegen
Straegen
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 3

The ability to import an assembly and reuse code gives CLR a huge advantage. Think about it... one unified language in development, application automation such as Excel and Office, extensions in products like Exchange and finally integrated into systems such as SQL Server. How long do you think it is before any technical person can go without knowing CLR? In ten years, knowing C# or VB.net will be common even for many savy end users. By contrast I still know developers who use SQL Server who couldn't do a FETCH in SQL without googling it? MS is clearly pushing CLR to replace the logical aspects of TSQL.





Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36066 Visits: 18736
Your example can handle some things, but it's not as clean, not does it easily work with non-US phones, etc. Using a CLR language and regular expressions, it's more flexible and clean. Arguable, less intuitive for a DBA, which is why I'm not sold, but there are functions that are easier in another language.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Adam Machanic
Adam Machanic
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 714
True business logic has only one place, and that's the database. Databases are designed to be shared amongst applications across an enterprise, and to be the final arbiter of good and bad data. Why do developers insist that logic should be duplicated (re-programmed!) again and again whenever a new application is created? If something is truly a business rule -- and not an application rule -- that logic belongs in the database, where it can be constrained properly, exactly once.

--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
ewilson10
ewilson10
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 22
Just off the top of my head, the power of placing an application on an end user's desktop via web standards is reason enough to program business logic in JavaScript.

Talk about a liberating experience.



Everett Wilson
ewilson10@yahoo.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search