SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CLR Integration


CLR Integration

Author
Message
Chris Hedgate
Chris Hedgate
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10085 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/
Dave Poole
Dave Poole
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26570 Visits: 3542
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
www.simple-talk.com
Paul Snell
Paul Snell
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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.
SQLPhil
SQLPhil
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2378 Visits: 740

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


Dave Poole
Dave Poole
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26570 Visits: 3542
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
www.simple-talk.com
Chris Hedgate
Chris Hedgate
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10085 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/
Chris Hedgate
Chris Hedgate
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10085 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/
Chris Hedgate
Chris Hedgate
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10085 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/
Chris Hedgate
Chris Hedgate
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10085 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/
einman33
einman33
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1339 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.


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