Translate function for SQL Server

Question

Post reply

Translate function for SQL Server

Alan Burstein

SSC Guru

Points: 61141
More actions
May 12, 2016 at 3:24 pm

#326307

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

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

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

h.tobisch SSCommitted Points: 1671 More actions · Answer 1

h.tobisch

SSCommitted

Points: 1671

June 1, 2016 at 8:09 am

#1882476

everything fine, but what's that checksum for ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

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: 61141 More actions · Answer 5

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: 186127 More actions · Answer 6

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!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 7

akljfhnlaflkj

SSC Guru

Points: 76202

September 1, 2016 at 1:23 pm

#1898871

This looks pretty cool, thanks.

Bill Talada SSChampion Points: 11954 More actions · Answer 8

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.