SQLServerCentral / Discuss Content Posted by Christoffer Hedgate / Article Discussions / Article Discussions by Author / CLR Integration / Latest Posts

RE: CLR Integration

tengtium — Sat, 05 Sep 2009 04:10:45 GMT

good day sirs and madam,i badly needed help.. i have this regular expression^(?!.*--)[A-Za-z\d-]+$it accepts alphanumeric character optionally a dash (single dash only, not consecutive dash) for exampl:it accepts: 12a-3c-4f3fg12ertgg21-2-3-3-4-3dffgsfgd-f-f-g-s-f-git does not accept:12-3c-4f&3fg12e%rt-gg2d--f-f-g-s-f-g1-2-3-3-4--3now i have problem, i cant find any in the .net that accepts the above valid expression with space-a non consecutive space. meaning the it needs to accept alphanumeric with optionally a non-consecutive dash and optionally a non-consecutive space.can someone help please.

RE: CLR Integration

Adam Machanic — Sun, 22 Jun 2008 11:18:33 GMT

You add it to the SqlFunctionAttribute in the C# code:public partial class CTSStoredProcs{ [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true, IsPrecise=true)] public static SqlBoolean RegExValidate( SqlString expressionToValidate, SqlString regularExpression) { Regex regex = new Regex(regularExpression.Value); return regex.IsMatch(expressionToValidate.Value); }}... nothing different on the T-SQL side.

RE: CLR Integration

billross — Sun, 22 Jun 2008 11:16:05 GMT

Thanks. I wonder if you could show me how to do it. I tried this:CREATE FUNCTION dbo.RegExValidate(@expressionToValidate nVarChar(100), @regularExpressionparameter_name nVarChar(100))RETURNS bitWITH IsDeterministic, IsPreciseEXTERNAL NAME CTSWebStoredProcs.CTSStoredProcs.RegExValidateGOI didn't get squiggly lines, but when it ran it said that IsDeterministic is not a recognized option.

RE: CLR Integration

Adam Machanic — Sun, 22 Jun 2008 10:54:25 GMT

Looks good, but you might want to apply the IsDeterministic and IsPrecise options on the SqlFunctionAttribute so that you can use your function in a wider variety of scenarios (such as a persisted computed column or an indexed view).

RE: CLR Integration

billross — Sun, 22 Jun 2008 10:46:30 GMT

Thanks for the reply. I've been successful and for the benefit of others who want to do this, I illustrate your steps with an example from my application....First, I enable CLR:-- enable/disable the CLRsp_configure 'show advanced options', 1;GORECONFIGURE;GO--sp_configure 'clr enabled', 0; -- disablesp_configure 'clr enabled', 1; -- enableGORECONFIGURE;GO>>>>Compile your DLLOk, this is my DLL:CTSStoredProcs.csusing System.Data.SqlTypes;using System.Text.RegularExpressions;using Microsoft.SqlServer.Server;public partial class CTSStoredProcs{ [Microsoft.SqlServer.Server.SqlFunction] public static SqlBoolean RegExValidate( SqlString expressionToValidate, SqlString regularExpression) { Regex regex = new Regex(regularExpression.Value); return regex.IsMatch(expressionToValidate.Value); }}I got a clean build.>>>then use CREATE ASSEMBLY to register it with your database. Here is my Create Assembly script:CREATE ASSEMBLY CTSWebStoredProcsFROM 'C:\Projects\CTSWebClassLibrary\bin\Debug\CtsWebClassLibrary.dll'WITH PERMISSION_SET = SAFE;GOThis runs and I get an entry in my Assemblies of CTSWebStoredProcs. >>>>Once you've done that you can use CREATE PROCEDURE, CREATE FUNCTION, etc, with the EXTERNAL NAME option to map your procedures, functions, etc, to the methods in the assembly.Here is my CREATE FUNCTION:CREATE FUNCTION dbo.RegExValidate(@expressionToValidate nVarChar(100), @regularExpressionparameter_name nVarChar(100))RETURNS bitEXTERNAL NAME CTSWebStoredProcs.CTSStoredProcs.RegExValidateI now have an entry in my Scalar Functions of dbo.RegExValidate.When I call the example, it validates perfectly:DROP TABLE dbo.PEOPLEGOCREATE TABLE dbo.PEOPLE ( name VARCHAR(25) NOT NULL , emailaddress VARCHAR(255) NOT NULL , CONSTRAINT PEOPLE_ck_validemailaddress CHECK ( dbo.RegExValidate( emailaddress, N'^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$' ) = 1 ) ) goDECLARE @regex NVARCHAR(100) 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})(\]?)$' SELECT name, emailaddress, dbo.RegExValidate( emailaddress, @regex ) AS validemail FROM dbo.PEOPLE goFantastic - thanks!

RE: CLR Integration

Adam Machanic — Sun, 22 Jun 2008 08:07:43 GMT

[quote][b]billross (6/22/2008)[/b][hr]I realize that the Sql Server Project Template is only available in the Professional edition of VS but wouldn't it be possible to create a CLR proc by just creating a class library and somehow deploying it manually?[/quote]Absolutely. Compile your DLL, then use CREATE ASSEMBLY to register it with your database. Once you've done that you can use CREATE PROCEDURE, CREATE FUNCTION, etc, with the EXTERNAL NAME option to map your procedures, functions, etc, to the methods in the assembly.

RE: CLR Integration

billross — Sun, 22 Jun 2008 06:33:55 GMT

I realize that the Sql Server Project Template is only available in the Professional edition of VS but wouldn't it be possible to create a CLR proc by just creating a class library and somehow deploying it manually?

RE: CLR Integration

Adam Machanic — Mon, 26 Sep 2005 15:39:00 GMT

SqlFunctionAttribute is an attribute that marks a method as a function -- so that SQL Server knows how you intend to use the method.For more information on attributes, see:http://insight.zdnet.co.uk/software/applications/0,39020466,2118655,00.htm

RE: CLR Integration

Barkingdog — Mon, 26 Sep 2005 14:27:00 GMT

I thought the article and example are quite interesting...but... what is the significance of " [Microsoft.SqlServer.Server.SqlFunction]" in

public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction]

That, in summary, is a problem for me. I know sql better than .NET, be it VB.NET of C#.

Bill

RE: CLR Integration

David Scotland-132255 — Thu, 28 Jul 2005 00:44:00 GMT

My understanding from Ken Henderson's blog and is that this will still be an issue in 2005 but by the sound of slava's blog maybe not as bad hmm looks like we could be waiting on Ken's or Kalen's book on 2005 to clarify just what the performance issues are if any in using CLR for stored procs.Anyway ta for the link it was an interesting read.

RE: CLR Integration

Adam Machanic — Wed, 27 Jul 2005 19:11:00 GMT

David --Although SQL Server 2005 still schedules preemtively, UMS is gone (see: http://blogs.msdn.com/slavao/archive/2005/02/05/367816.aspx ). I believe SQLOS is supposed to handle context switching and other problems a lot more elegantly than UMS.

RE: CLR Integration

David Scotland-132255 — Wed, 20 Jul 2005 15:34:00 GMT

I think the one thing missing in this discussion is that although when used for the correct purposes CLR integration will prove addition to sql server it still falls foul of the same performance hit that you get with xprocs.This is because the UMS's in sql server works in a non-preemptive mode i.e. it is a co-operative scheduler and the functions to yield are not available to external applications that is if you could trust them to yield when they should so to schedule a CLR assembly sql passes the thread to to the windows scheduler so that it can be scheduled pre-emptively and then starts up a new thread to handle the work of the sscheduler object from which the thread was taken.This all involves context and keranl switching it's even worse if it then needs to acces the oDS layer as this involves more context switching etc.RegardsDavid

RE: CLR Integration

SQLPhil — Fri, 15 Jul 2005 04:56:00 GMT

I entirely agree. In fact, I've just returned from a T-SQL course with Learning Tree and so am very pro T-SQL at the moment. I think in the interim it'll be a case of finding my (and everybody else's) feet to discover which procedures are going to perform better depending on what language they're written in.

RE: CLR Integration

Jeff Gray — Wed, 13 Jul 2005 13:05:00 GMT

My opinion is that it will be beneficial to have a "procedural language" available to perform functions that TSQL is not as adept at. That is available today on the client side, obviously, but not quite so close to the data itself. This will most likely create opportunities to make faster data access available to the other application tiers.

Now, I'm not ready to go all chicken little and claim that C# will ruin my database, because it is not the best tool for getting data in and out of a database *in the general case*. I don't believe that the presentation tier guys are going to ask for CLR in the database because they are used to having the recordsets handed to them ready-to-go. What I do see is that I can find a better (faster) way to get them data in very specialized cases and for that I shall be eternally grateful.

We'll see what happens...

RE: CLR Integration

Chris Hedgate — Wed, 13 Jul 2005 08:56:00 GMT

Well, it installs on systems that do not have the .Net Framework installed already. But then it would be installed by the SQL Server 2005 installation. Note that it is the .Net Framework 2.0 (currently in beta) we are referring to here.Hopefully I will be exploring performance hits and requirements later, though I anticipate it will be a couple of articles from now.

RE: CLR Integration

Chris Hedgate — Wed, 13 Jul 2005 08:53:00 GMT

Even though I have some understanding of regular expressions, I did not write that one myself. I think I found it at Regular-Expressions.info, a very good resource.

RE: CLR Integration

Chris Hedgate — Wed, 13 Jul 2005 08:51:00 GMT

Very true, and that is one of the reasons I see why it is important for more or less every DBA to at least have a basic understanding of what CLR Integration is and how it works. The code itself is normally not that difficult to read, so if it is just a rev iew to allow the code in the database most should get by. Writing it is of course more difficult. I think one of the concerns will be just how to look at the code, using Visual Studio (or other tool) etc.Hopefully we will see developers and DBAs working closer to solve these issues.

RE: CLR Integration

David.Poole — Wed, 13 Jul 2005 08:17:00 GMT

How do I stop smilies appearing in my forum posts?

RE: CLR Integration

Jason Delaune — Wed, 13 Jul 2005 08:00:00 GMT

David, Thanks for the URL! That will save on the typing!Jason

RE: CLR Integration

J. Chris Gibson, CSDP — Wed, 13 Jul 2005 07:57:00 GMT

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

RE: CLR Integration

David.Poole — Wed, 13 Jul 2005 07:50:00 GMT

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})$

RE: CLR Integration

Jason Delaune — Wed, 13 Jul 2005 07:37:00 GMT

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

RE: CLR Integration

einman33 — Wed, 13 Jul 2005 07:19:00 GMT

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.

RE: CLR Integration

Chris Hedgate — Wed, 13 Jul 2005 03:53:00 GMT

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.

RE: CLR Integration

Chris Hedgate — Wed, 13 Jul 2005 03:53:00 GMT

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.

RE: CLR Integration

Chris Hedgate — Wed, 13 Jul 2005 03:50:00 GMT

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 TABLE statement 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!

RE: CLR Integration

Chris Hedgate — Wed, 13 Jul 2005 03:41:00 GMT

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).

RE: CLR Integration

David.Poole — Wed, 13 Jul 2005 03:04:00 GMT

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.

RE: CLR Integration

SQLPhil — Wed, 13 Jul 2005 02:51:00 GMT

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

RE: CLR Integration

Paul Snell — Wed, 13 Jul 2005 02:39:00 GMT

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.

RE: CLR Integration

David.Poole — Wed, 13 Jul 2005 01:51:00 GMT

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?

CLR Integration

Chris Hedgate — Wed, 06 Jul 2005 17:02:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/chedgate/clrintegration.asp