Practical usage of SQLCLR: Building QueryToHtml function

Question

Practical usage of SQLCLR: Building QueryToHtml function

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 1

Orlando Colamatteo - Monday, June 5, 2017 6:40 AM
Well done. I appreciate the effort required to put an article like this together let alone the coding to build the tool itself.I know a guy that hangs on this site whose name rhymes with Reff Snowden who would appreciate a full-featured replacement for the web task sp. There is a reason why Microsoft dropped it, however. There are some other pieces of the language that are remnants of its client-server roots that I'd wish they'd drop as well.In a sense this brings back a popular tool but in a sense it's regressive in terms of where the platform and the industry are going.

Thanks for the honorable mention, OrBlando Colatomato. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 2

Darko Martinovic - Tuesday, June 6, 2017 12:42 AM
Theo Ekelmans - Monday, June 5, 2017 1:20 AM
Thank you Darko for this most excellent idea to build upon.
This kind of article is why i love sqlservercentral so much !
Thank you Theo. Negative comments are welcome too.

I'm pretty sure that wasn't a negative comment.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 3

Sergiy - Monday, June 5, 2017 5:50 AM
Once again - Thank you so much Microsoft for ditching sp_makewebtask.
12 years on - people are still trying to reinvent it.

Ok... where's the "+1 Million" button?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 4

Darko Martinovic - Tuesday, June 6, 2017 12:45 AM
Orlando Colamatteo - Monday, June 5, 2017 6:40 AM
Well done. I appreciate the effort required to put an article like this together let alone the coding to build the tool itself.I know a guy that hangs on this site whose name rhymes with Reff Snowden who would appreciate a full-featured replacement for the web task sp. There is a reason why Microsoft dropped it, however. There are some other pieces of the language that are remnants of its client-server roots that I'd wish they'd drop as well.In a sense this brings back a popular tool but in a sense it's regressive in terms of where the platform and the industry are going.
Thank you Orlando 🙂 ! You said everything exactly. I have nothing to add. Thanks for the engagement.

So you think I actually like having my name rhymed with the likes of Snowden?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 5

Darko Martinovic - Tuesday, June 6, 2017 12:49 AM
Sergiy - Monday, June 5, 2017 5:50 AM
Once again - Thank you so much Microsoft for ditching sp_makewebtask.
12 years on - people are still trying to reinvent it.
Thank you Sergiy for your response 🙂!
Something like that. By the way, we are trying to detect the SQLCLR that has been there for so many years and is not so popular.

Have you ever used sp_MakeWebTask, Darko?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

Jeff Moden - Tuesday, August 22, 2017 9:43 PM
Darko Martinovic - Tuesday, June 6, 2017 12:45 AM
Orlando Colamatteo - Monday, June 5, 2017 6:40 AM
Well done. I appreciate the effort required to put an article like this together let alone the coding to build the tool itself.I know a guy that hangs on this site whose name rhymes with Reff Snowden who would appreciate a full-featured replacement for the web task sp. There is a reason why Microsoft dropped it, however. There are some other pieces of the language that are remnants of its client-server roots that I'd wish they'd drop as well.In a sense this brings back a popular tool but in a sense it's regressive in terms of where the platform and the industry are going.
Thank you Orlando 🙂 ! You said everything exactly. I have nothing to add. Thanks for the engagement.
So you think I actually like having my name rhymed with the likes of Snowden?

Apologies, Jeff. No hurtful tones were intended from my end. I have no reason to connect you with the actions or likeness of Edward Snowden. It was just the first rhyming name that came to mind...

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 7

Darko Martinovic - Tuesday, June 6, 2017 11:22 AM
the sqlist - Tuesday, June 6, 2017 8:34 AM
Darko Martinovic - Tuesday, June 6, 2017 3:21 AM
In the introduction I forgot to mention that dynamic T-SQL is one of the methods to get valid HTML.
My opinion is that string manipulation is not a T-SQL advantage. This is the area where .NET takes precedence.
I am convinced that all these solutions are presented by colleagues through dynamic T-SQL's good.
But I will repeat it is not the topic of this article, nor would it ever be my choice.
We understand that, with the reserve that string manipulation in SQL server is not a problem at all, but at the same time I think people deserve to know that the same things can be achieved in a pure SQL approach, which is also much, much simpler than using CLR feature. If, however, the things become over complicated the CLR method might be preferable although in that case one should leave altogether the SQL Server environmant and bult an external module using a programming language of their choice.
As a general idea the CLR processes are using a lot of memory and CPU power, which in some cases are in the detriment of performace.
You would be surprised! In many situation, spetially when parallel execution has involved, CLR performs better.
I'm using QueryToHtml function during years and it performs well, it is easy to use and adopt.
When you are in house DBA, probably you will not use CLR. But, if you are software vendor with thousend of customers and thousend of installations, CLR is much better solution.

To be clear, I'm not a hater of SQLCLR. And I don't hate what you wrote in your good article as SQLCLR. But...

I've had quite the opposite experience with SQLCLR triggers for audit tables. Someone copied a bunch of them to multiple tables (they were generic and would work on any table you copied them to). For tables with (way to) many columns, it was taking more than 4 minutes to update just 4 columns on just 10,000 rows. I know I'm slipping but after I converted them to T-SQL, they generally took less than 400 milliseconds.

If I had sent out the database with those original SQLCLR triggers on them, then I have had thousands pissed off customers. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 8

Jeff Moden - Tuesday, August 22, 2017 9:41 PM
Darko Martinovic - Tuesday, June 6, 2017 12:42 AM
Theo Ekelmans - Monday, June 5, 2017 1:20 AM
Thank you Darko for this most excellent idea to build upon.
This kind of article is why i love sqlservercentral so much !
Thank you Theo. Negative comments are welcome too.
I'm pretty sure that wasn't a negative comment.

Pretty sure it wasn't taken in a negative way. Sounds like a welcoming of constructive criticism for future posts.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Darko Martinović Hall of Fame Points: 3545 More actions · Answer 9

Jeff Moden - Tuesday, August 22, 2017 9:41 PM
Darko Martinovic - Tuesday, June 6, 2017 12:42 AM
Theo Ekelmans - Monday, June 5, 2017 1:20 AM
Thank you Darko for this most excellent idea to build upon.
This kind of article is why i love sqlservercentral so much !
Thank you Theo. Negative comments are welcome too.
I'm pretty sure that wasn't a negative comment.

Thank you Jeff. 🙂

Darko Martinović Hall of Fame Points: 3545 More actions · Answer 10

Jeff Moden - Tuesday, August 22, 2017 10:06 PM
Darko Martinovic - Tuesday, June 6, 2017 11:22 AM
the sqlist - Tuesday, June 6, 2017 8:34 AM
Darko Martinovic - Tuesday, June 6, 2017 3:21 AM
In the introduction I forgot to mention that dynamic T-SQL is one of the methods to get valid HTML.
My opinion is that string manipulation is not a T-SQL advantage. This is the area where .NET takes precedence.
I am convinced that all these solutions are presented by colleagues through dynamic T-SQL's good.
But I will repeat it is not the topic of this article, nor would it ever be my choice.
We understand that, with the reserve that string manipulation in SQL server is not a problem at all, but at the same time I think people deserve to know that the same things can be achieved in a pure SQL approach, which is also much, much simpler than using CLR feature. If, however, the things become over complicated the CLR method might be preferable although in that case one should leave altogether the SQL Server environmant and bult an external module using a programming language of their choice.
As a general idea the CLR processes are using a lot of memory and CPU power, which in some cases are in the detriment of performace.
You would be surprised! In many situation, spetially when parallel execution has involved, CLR performs better.
I'm using QueryToHtml function during years and it performs well, it is easy to use and adopt.
When you are in house DBA, probably you will not use CLR. But, if you are software vendor with thousend of customers and thousend of installations, CLR is much better solution.
To be clear, I'm not a hater of SQLCLR. And I don't hate what you wrote in your good article as SQLCLR. But...
I've had quite the opposite experience with SQLCLR triggers for audit tables. Someone copied a bunch of them to multiple tables (they were generic and would work on any table you copied them to). For tables with (way to) many columns, it was taking more than 4 minutes to update just 4 columns on just 10,000 rows. I know I'm slipping but after I converted them to T-SQL, they generally took less than 400 milliseconds.
If I had sent out the database with those original SQLCLR triggers on them, then I have had thousands pissed off customers. 😉

Thank you Jeff for sharing your experience. 🙂

I understand what you are writing. SQLCLR exists since 2005, but it is behavior as like currently released.

During years, I did not notice many useful pattern of using SQLCLR. Maybe Adam Machanic is an exception ( His code has a lot of sentence that contains "law words", so I am very careful with that 😎).

In my opinion there is a plenty of room for SQLCLR improvement. By that, I mean memory management, security model design, ability to compile .NET code on-fly etc.

Working as a software developer for 25 years, I concluded in early days of .NET that T-SQL knowledge is as important as .NET knowledge.

In other words, T-SQL is irreplaceable and only in some situations SQLCLR can help it.

So, again thank you for your valuable comment Jeff! 🙂

Darko Martinović Hall of Fame Points: 3545 More actions · Answer 11

Jeff Moden - Tuesday, August 22, 2017 9:47 PM
Darko Martinovic - Tuesday, June 6, 2017 12:49 AM
Sergiy - Monday, June 5, 2017 5:50 AM
Once again - Thank you so much Microsoft for ditching sp_makewebtask.
12 years on - people are still trying to reinvent it.
Thank you Sergiy for your response 🙂!
Something like that. By the way, we are trying to detect the SQLCLR that has been there for so many years and is not so popular.
Have you ever used sp_MakeWebTask, Darko?

Andrew Clarke has helped me in writing this article with his valuable comments. 🙂

One of them was to mention sp_makewebtask.

I am hoping that QueryToHtml will help to replace sp_makewebtask. I hope that by the end of this year, will be QueryToExcel as well.

And currently there is another article about SQLCLR publishing on Simple Talk.

SQLCLR in Practice: Creating a Better Way of Sending Email from SQL Server

The most recent code could be found on GitHub.
GitHub