CLR Integration
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/chedgate/clrintegration.asp
-
Chris,
If you have a bog standard SQL2000 function, for the sake of argument it multiplies one argument by another and you write a query that applies that function to records in a table. You get a big performance hit because the function is applied row by row.
If you have your .NET assembly function does the same thing apply?
Also, does the CLR mean that extended stored procedures are now consigned to the history books?
-
I may have a rather simplistic view of CLR, but I think it is not just desirable, it is a must. I work in medical research and our regulations state that there must be a full data audit. Using triggers we can capture just about everything, but FDA stipulates that not only must you capture the changes but also WHY the changes are being made. At the application level we can do that, but NOT at the table level. If a database administrator wants to make changes at the table level, we can capture the changes made, but not the reason why. This can only be done (in my opinion) by providing a programmed interface. Now with CLR it sounds like we can really do this at the table level, so even our administrators will be unable to make sureptitious changes to data that would be lost to auditors. CLR may be a dangerous path, but given that Oracle has always allowed this to happen (capture why) then it helps SQL server face up to the future requirements and regulation. I can't wait to start playing with this.
-
I'm quite excited about the prospect of CLR integration. Although I work as a DBA and would like my career to progress down that route, I am currently studing for an MCSD. This will hopefully put me in a good position when SQL Server 2005 is on the market.
The only problem as I see it is that I work for a government agency in England, and these new technologies take time to creep through (I'm still battling trying to get some SQL 6.5 Servers upgraded to SQL2K !!!!). So who knows, maybe in 2010 I can get my teeth into it
-
To use an analogy the CLR is rather like electric power tools.
In the hands of a competent person they are a God send allowing jobs that would otherwise be difficult or time consuming to be completed both well and rapidly.
In the hands of others....well take a trip down to your local accident and emergency department.
One thing that made me prick up my ears was the MS Tech Ed presentation that said that the CLR does not site on top of the OS and SQL sits on top of that as for current .NET apps, as far as the CLR is concerned SQL is the OS and it truly is integrated into SQL.
If fact the entire Tech Ed presentation kept emphasising how the SQL 2000 bolt-ons such as notification services, analysis services, reporting services are now part of the integrated whole of SQL 2005.
-
If you have a bog standard SQL2000 function, for the sake of argument it multiplies one argument by another and you write a query that applies that function to records in a table. You get a big performance hit because the function is applied row by row.
If you have your .NET assembly function does the same thing apply?
In general, yes, the same performance hit would apply to CLR functions. The scalar function is called once for each row in the same way as a standard t-sql function.
Also, does the CLR mean that extended stored procedures are now consigned to the history books?
Extended stored procedures are still available. I do not think that many new ones will be created though. There are two factors to consider however. First, for backwards compatibility both the existing xprocs as well as the possibility to add xprocs to SQL Server is needed. Second, as I mentioned CLR Integration is not enabled by default. Therefore there is no CLR replacement for xp_cmdshell, for instance (note though that xp_cmdshell is also disabled by default).
-
Hmm, not sure I quite understand what you mean with the "why" when a DBA alters a table. Anyway I do not see how CLR Integration would change any of that. A DBA will still be able to execute an
ALTER TABLEstatement whether or not CLR Integration is enabled. Sure, you could create a CLR procedure to do the altering from and log stuff at the same time, but you could do the same using a t-sql proc. (As a side note, perhaps DDL triggers --also a new feature in SQL Server 2005-- might be helpful here.)Do not get me wrong though, I also look forward to being handed new possibilities with CLR Integration. I expect to be using it myself from time to time, but I am also very much anticipating seeing a lot of misuses of it.
I can't wait to start playing with this.
Download the June CTP right away!
-
I feel the same way. I am just as much a developer as I am a database professional, so I think I will have a very good perspective of this feature. I also hope it will be a helpful combination when SQL Server 2005 is out. But remember, just because it is possible it does not mean you should start writing every procedure in CLR instead of t-sql now.
-
One thing that made me prick up my ears was the MS Tech Ed presentation that said that the CLR does not site on top of the OS and SQL sits on top of that as for current .NET apps, as far as the CLR is concerned SQL is the OS and it truly is integrated into SQL.
That is a good part of the topic of my next article in this series.
-
One thing I am concerned with.
One of my job responsibilities is to peer review tsql code created by developers. I am currently very confident of noticing items that can be improved on or misuses of tsql. Now, it will be more difficult when a developer submits CLR source to the DBA team to be able to debug/review. So it seems in my scenario I will need to have a very good understanding of the framework.
You know everyone has been saying who needs development DBA's anymore with the CLR, heck now I say who needs developers anymore, I can do anything. 🙂 Just kidding.
-
Chris,
Definitely an interesting read. I'll look forward to your future articles on the CLR. In the meantime, I was just curious where you came up with that expression to validate an email address. Did you code that yourself, or is there a tool / function that will help you generate the validation expression?
SET @regex = N'^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$'
Jason
-
I find that the RegEx coach is a useful tool for checking RegEx.
Also try http://www.regexlib.com/
The following evaluates UK dates and takes account of leap years etc.
^(?
?:31(\/|-|\.)(?:0?[13578]|1[02]))\1|(??:29|30)(\/|-|\.)(?:0?[1,3-9]|1[0-2])\2))(??:1[6-9]|[2-9]\d)?\d{2})$|^(?:29(\/|-|\.)0?2\3(???:1[6-9]|[2-9]\d)?(?:0[48]|[2468][048]|[13579][26])|(??:16|[2468][048]|[3579][26])00))))$|^(?:0?[1-9]|1\d|2[0-8])(\/|-|\.)(??:0?[1-9])|(?:1[0-2]))\4(??:1[6-9]|[2-9]\d)?\d{2})$ -
MY biggest concern is for a potential Database engine performance hit, and will current SQL Server optimum memory requirements be increased - if so, how much? Also, will SQL Server 2005 install on systems that don't already have the .NET framework installed?
J. Chris Gibson
-
David,
Thanks for the URL! That will save on the typing!
Jason
-
How do I stop smilies appearing in my forum posts?
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply