The CLR in SQL Server 2005
-
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} -
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.
-
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.
-
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
whoisactive -
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 -
..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...
Straegen,
I think your now starting to see what I was trying to point out earlier, that your orginal post(s) were not clearly distinguishing between SQL for set based operations and SQL for procedural based operations. I seriously doubt you'll find many here that disagree with your stance that T-SQL is inferior to CLR based langauges when it comes to procedural based operations. Your original posts made it sound like you were in favor of replacing all SQL with CLR langauges for all operations. As you pointed out yourself, T-SQL is good at what it does. Look at it this way, how would you repsond if some said in a post that T-SQL needed to be replaced with C# or VB.Net for Setbased operations like SELECT, INSERT & UPDATE. Would you find thier comment to be irational?
Kindest Regards,
Just say No to Facebook! -
Not saying anyone is right or wrong here because TSQL means different things to different people, but SQL to me has always meant DDL and DML. TSQL and PL/SQL are the extensions to SQL Server and Oracle that extend the basic SQL syntax to handle flow control and such. I think I was pretty clear in my posts about replacing TSQL with CLR and not SQL with CLR.
Anyway since the article was about using CLR versus TSQL, I just made an assumption that everyone distinguished the logical part of TSQL and the quasi univeral ANSI SQL. I am not sure how someone thought I was comparing COBOL to TSQL as a language either so it must be me.
-
Your arguement is certainly valid, but there are some very good reasons to abstract your middle tier from your database.
First one that jumps out is how poorly TSQL organizes logic. Since TSQL isn't an object based language, encapsulation and reuse make building a good business layer difficult. This does change with CLR in the mix for Yukon however.
Second, is the fact that a good middle tier can be ported to other databases and even operating systems. While often this isn't practical since larger apps quickly become inner-dependant on the SQL engine, keeping your business layer abstracted from your data layer does allow for quite a bit of flexibility when it comes to how you store data. If an app is well insulated, it can be switched from SQL Server to MySQL relatively quickly. Tieing yourself to a database vendor is generally not an issue, but for many apps closing yourself off can be closing down opportunities.
Third up is performance. When logic exists solely in your database, it becomes very difficult to optimize system logic. For example, rewritting your encryption/compression routines from DES to AES would likely require changing linked DLLs in SQL Server. If a true middle tier existed, it is likely as simple as changing a couple of methods on the connectivity object. Again CLR in the database addresses this issue to some degree.
Forth, security. By creating a middle tier, I can link my client to it via a public port then on an entirely seperate network link my data connection to my SQL Server. Thus for a hacker to gain access to data they would have to hack either the client/server interface which is usually highly encrypted and quite often proprietary or they would have to attack the middle tier server and then bust through a second layer of security to the data server. If the business logic were in the SQL Server, my clients would be connecting directly to SQL Server and I would have to worry about threats such as Slammer if my app was internet based.
Last but not least, because it is easier for most companies. A middle tier written in the same language as your client makes maintaining business logic easier out of the gate. Rather than having a DBA that does one side and a developer that does another or developers needing to know two programming languages, the database becomes nothing more than a repository of information. Documenting, source control, etc become simpler because the syntax is uniform. This can be a pretty lame reason, but since most companies go with what they know and databases usually being a mystery to everyone but a few this just seems natural for these companies.
-
With power and flexibility come responsibility.
The new features give companies, developers, DBA's new ways to do things. A lot will make the choice not to use them immediately. That's hopefully wise for them. Others will have been frustrated by the lack of an aggregate function for years and will cash in on that. I hope it works well.
The key thing is not to be pushed into choices either because that's the only way or just because you can. If the decision maker doesn't grok that!!!
In the long run this will help those who naturally want to broaden their skills and do a wider job. They will now have a more realistic chance. Those who want to specialise can still do that.
As to quality and approving code. Thats a sore issue. Looking at (code inspection) T-SQL or a .NET OO language is not enough. Tests are important, not just unit tests but tests that go across layers. The tools are emerging but in many cases are not yet used routinely (sad cause they often make all kinds of sense including economic sense). If you don't have code to test your T-SQL, C#, VB.NET but instead need a knowlegeable human tester are you still proud of the work you do?
Great debate.
-
Horses for courses... SQL will still be the language for manipulating sets but CLR opens up a whole load of opportunities to those who want to get involved.. I am a developer currently working in the field of GIS, using the CLR intergration i can create a basic spatial database with true representations of points, polygons, lines, etc and perform operations that can not be done with sql/tsql, without it I need expensive middleware and huge data sets to be passed around layers (or to move to oracle, postgis or mysql
).. I am sure many other people in many different fields have similar experience.. interesting debate though.. ps. I'm wondering when MS themselves will tackle spatial databases
fire extinguisher ready...
-
As always, there is no straight forward in a relational environment
If you use it in a well-considered way you'll gain from it, else it'll be a pain in the ... .
Remember the time when we first had triggers ? Sufferd them and then got a bit familiar and at ease because some major applications sufferd due to overuse and the dba finaly was no longer alone to "hold the horses" !
To most devs this is will be joyride until they hit something and finaly come to reason. The timegap for this event is what scares most dba's, because they will be in the line of fire !
I always like to read the comments on articles like this one
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I like it, but I have spent an equal amount of time being a developer as being a DBA so I have a reasonably idea of when it works and when it doesn't. I also fully understand the fear factor involved when you realise less experienced developers could by writing code directly on your database...
It's use would be when you are using cursors to do work as the .Net code has proven to be quicker than T-SQL (I will try to find a link to back that statement up) and also when you need to access components to do work. Trying to do that in T-SQL is possible but horrible. It is much better IMHO to use .Net procedural code to access components than SQL, if only for the error handling capabilities! T-SQL may now have try catch statements but they are not as good as the .Net equivalent.
-
I agree with Adam Machanic that the business logic can be very well located in a database. I also think that a 3-tier design is OK. So in my opinion if the application requires a distributed design for performance or security purposes, the next Microsoft recommendation should be to have a middle tier as a database as opposed to the application layer. One database on one server is for data only and the second database on the same or another server with all business rules, state management, user management, CLR, Future CLR etc...
Yelena
Regards,Yelena Varsha
-
Wow, this is a hot post!
I'm open to the opportunities the CLR offers, but it does place more responsibility on the lowly DBA (and job security for those who figure it out).
I appreciate the realistic and sarcastic comments in the article. It was a fun read.
-
A voice of reason, crying out in the dark...! Finally, someone with real-world experience speaks about the CLR...
Thanks for the great article, especially the line:
...And like MDX, there will be 7 people in their country that understand what they did and maybe a few more that care. But they'll be able to create code to perform complicated analyses that is impossible in SQL Server 2000 and when they have to explain the meaning to a business user, they'll wish they hadn't...
I believe the addition of the CLR into SQL Server is simply Microsoft keeping up with the Jonses. Will there be uses for it? Sure. Will it be over used, absolutely. I already have developers talking about rewriting all of their stored procedures in the CLR so that their procedures will run faster an be easier to manage... (insert loud scream and pulling out of hair here).
Francis
-----------------
SQLRanger.com
Viewing 15 posts - 16 through 30 (of 64 total)
You must be logged in to reply to this topic. Login to reply