First Tally Table T-SQL

Question

Post reply

First Tally Table T-SQL

Viewing 6 posts - 16 through 20 (of 20 total)

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

Paul White SSC Guru Points: 150457 More actions · Answer 1

Paul White

SSC Guru

Points: 150457

June 29, 2010 at 7:34 am

#1186366

Agreed. One other important caveat - anyone running 32-bit SQL Server (why?) should go with a Tally solution too. SQLCLR and 32-bit servers are not a good fit - in my opinion.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 2

For sure nothing will touch the performance of C#/C++ for that type of function.

For use on a table, you would probably need a scalar function, and those don't perform that well anyway.

Fwiw, here's my take on it, using just T-SQL. My upper-casing rule is (1) the first character and (2) any other letter preceded by a character that is NOT a letter:

CREATE FUNCTION dbo.MixedCase (

@string varchar(8000)

)

RETURNS varchar(8000)

AS

BEGIN

DECLARE @byte int

DECLARE @prevByte int

SET @string = UPPER(LEFT(@string, 1)) + LOWER(SUBSTRING(@string, 2, 8000))

SET @prevByte = 0

SET @byte = PATINDEX('%[^a-z][a-z]%', @string)

WHILE @byte > 0

BEGIN

SET @string = STUFF(@string, @prevByte + @byte + 1, 1,

UPPER(SUBSTRING(@string, @prevByte + @byte + 1, 1)))

SET @prevByte = @prevByte + @byte

SET @byte = PATINDEX('%[^a-z][a-z]%', SUBSTRING(@string, @prevByte + 1, 8000))

END --WHILE

RETURN @string

END --FUNCTION

SELECT dbo.MixedCase(data)

FROM temp

Scott Pletcher, SQL Server MVP 2008-2010

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

scott.pletcher (6/29/2010)

Fwiw, here's my take on it, using just T-SQL. My upper-casing rule is (1) the first character and (2) any other letter preceded by a character that is NOT a letter:

I agree... those two rules are all that's needed and no special handling for rule 1 is necessary. As your code indicates, just do it.

You can certainly convert your code to an implementation of a Tally Table or Tally CTE so that you could convert the function to an iTVF for a huge performance improvement over a scalar function. In this case, it's not the While Loop that would be such a drag (mostly memory only code)... it's the fact that a While Loop would only work in a scalar or mlTVF (I know you know that... just saying it outloud for others that may read this).

--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)
Intro to Tally Tables and Functions

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

Paul White NZ (6/29/2010)

Agreed. One other important caveat - anyone running 32-bit SQL Server (why?) should go with a Tally solution too. SQLCLR and 32-bit servers are not a good fit - in my opinion.

Heh... yeah... my trusty 9 year old desktop at home is still 32 bit. "It takes a lickin' and keeps on tickin'!":-P It also helps me when I have the need to build something with blinding speed for work... if I can make it fly on my machine, on my ol' 32 bit, 1.8Ghz, single P4, just imagine what it does on a 4x64 or better. :w00t:

--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)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 5

Jeff Moden (6/29/2010)

Heh... yeah... my trusty 9 year old desktop at home is still 32 bit. "It takes a lickin' and keeps on tickin'!":-P It also helps me when I have the need to build something with blinding speed for work... if I can make it fly on my machine, on my ol' 32 bit, 1.8Ghz, single P4, just imagine what it does on a 4x64 or better. :w00t:

Yep I too only have 32-bit SQL Server at home, although I have the awesome power of a 2GHz single-core *mobile* P4 at my disposal :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150457 More actions · Answer 6

scott.pletcher (6/29/2010)

For sure nothing will touch the performance of C#/C++ for that type of function.

Any CLI language will do...let's not forget the VB guys out there (Barry) or those that prefer F# or IronRuby...http://en.wikipedia.org/wiki/List_of_CLI_languages

For use on a table, you would probably need a scalar function, and those don't perform that well anyway.

APPLY makes using an in-line TVF a theoretical possibility. CLR scalar functions perform much better than T-SQL scalar functions.

Fwiw, here's my take on it, using just T-SQL.

Have you tested that for performance against the other methods presented previously?

Don't forget the value of schema binding your scalar functions.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi