Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

CLR Integration Expand / Collapse
Author
Message
Posted Wednesday, July 6, 2005 5:02 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/chedgate/clrintegration.asp



--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Post #197802
Posted Wednesday, July 13, 2005 1:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:44 AM
Points: 2,902, Visits: 1,817
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?


LinkedIn Profile
Newbie on www.simple-talk.com
Post #200345
Posted Wednesday, July 13, 2005 2:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 4, 2007 7:49 AM
Points: 10, Visits: 1
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.
Post #200352
Posted Wednesday, July 13, 2005 2:51 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:46 AM
Points: 1,442, Visits: 723

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

Post #200356
Posted Wednesday, July 13, 2005 3:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:44 AM
Points: 2,902, Visits: 1,817
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.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #200361
Posted Wednesday, July 13, 2005 3:41 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7
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).




--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Post #200369
Posted Wednesday, July 13, 2005 3:50 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7
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!




--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Post #200371
Posted Wednesday, July 13, 2005 3:53 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7
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.



--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Post #200373
Posted Wednesday, July 13, 2005 3:53 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7
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.




--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Post #200375
Posted Wednesday, July 13, 2005 7:19 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 8:14 AM
Points: 567, Visits: 512

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.

 

 

 

Post #200445
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse