Practical usage of SQLCLR: Building QueryToHtml function

  • Jeff Moden

    SSC Guru

    Points: 994861

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Jeff Moden

    SSC Guru

    Points: 994861

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Jeff Moden

    SSC Guru

    Points: 994861

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Jeff Moden

    SSC Guru

    Points: 994861

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Jeff Moden

    SSC Guru

    Points: 994861

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    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: 994861

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    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: 3535

    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: 3535

    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: 3535

    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

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

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