Translate function for SQL Server

  • Alan Burstein

    SSC Guru

    Points: 61067

    Comments posted to this topic are about the item Translate function for SQL Server

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • h.tobisch

    SSCommitted

    Points: 1671

    everything fine, but what's that checksum for ?

  • ChrisM@Work

    SSC Guru

    Points: 186054

    h.tobisch (6/1/2016)


    everything fine, but what's that checksum for ?

    Currently the fastest known method for converting a bigint to an int.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • ChrisM@Work

    SSC Guru

    Points: 186054

    Nice job Alan.

    A few years ago DC and I played with numerous variations of this method as a spinoff of the PatternSplit project. One of the most promising methods was a QU using an inline tally table, where the inline variable is the product, rather than values in the table. I remember it was very fast and made for nice slim maintainable code too.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Alan Burstein

    SSC Guru

    Points: 61067

    ChrisM@Work (6/1/2016)


    Nice job Alan.

    A few years ago DC and I played with numerous variations of this method as a spinoff of the PatternSplit project. One of the most promising methods was a QU using an inline tally table, where the inline variable is the product, rather than values in the table. I remember it was very fast and made for nice slim maintainable code too.

    Thanks Chris.

    I tried a similar method with the QU and inline tally table which did not perform well, it did very badly actually. I'll send it to you, I'd be interested to see where I went wrong.

    It's so cool that you guys played around with this, it's such a fun exercise and great way to sharpen your SQL skills.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Alan Burstein

    SSC Guru

    Points: 61067

    ChrisM@Work (6/1/2016)


    h.tobisch (6/1/2016)


    everything fine, but what's that checksum for ?

    Currently the fastest known method for converting a bigint to an int.

    I learned this technique from Eirikur Eiriksson. One of those super slick T-SQL tricks that makes SQLServerCentral awesome.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • ChrisM@Work

    SSC Guru

    Points: 186054

    Alan.B (6/1/2016)


    ChrisM@Work (6/1/2016)


    h.tobisch (6/1/2016)


    everything fine, but what's that checksum for ?

    Currently the fastest known method for converting a bigint to an int.

    I learned this technique from Eirikur Eiriksson. One of those super slick T-SQL tricks that makes SQLServerCentral awesome.

    EE has super slick beer tricks too - watch out!

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    This looks pretty cool, thanks.

  • Bill Talada

    SSChampion

    Points: 11956

    There is a fine line between "trick" and "hack".  This cool function is using lots of side effects which makes me wonder if it might become SQL Server version dependent at some point.

    I didn't know substring('abc', 4, 1) would return empty string instead of an error.  Maybe I'm too much of an academic purist but all this cleverness makes me feel uneasy.

Viewing 9 posts - 1 through 9 (of 9 total)

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