May 15, 2012 at 6:24 pm
Jeff Moden (5/15/2012)
dwain.c (5/14/2012)
Forgive me oh experts for I have sinned. I have not compared performance against other suggestions. 🙂Since you used a recursive CTE for this task, that may be a bigger problem than you imagined. 😉 Of course, only a test will tell for sure.
I have no illusions about its performance. Just having a little fun while I explore the world of recursive CTEs. 😉
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 15, 2012 at 6:39 pm
Thanks for the code examples for this task, folks. I've been kind of busy catching up at work because I got really sick two weeks ago. I'll put together a test and see what to make of it all. Yes, I'll also include my examples. Sounds like fun, actually.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2012 at 6:40 pm
dwain.c (5/15/2012)
Jeff Moden (5/15/2012)
dwain.c (5/14/2012)
Forgive me oh experts for I have sinned. I have not compared performance against other suggestions. 🙂Since you used a recursive CTE for this task, that may be a bigger problem than you imagined. 😉 Of course, only a test will tell for sure.
I have no illusions about its performance. Just having a little fun while I explore the world of recursive CTEs. 😉
Yeah... but you may have to turn in your "No RBAR" card if you keep it up. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2012 at 7:22 pm
Jeff Moden (5/15/2012)
dwain.c (5/15/2012)
Jeff Moden (5/15/2012)
dwain.c (5/14/2012)
Forgive me oh experts for I have sinned. I have not compared performance against other suggestions. 🙂Since you used a recursive CTE for this task, that may be a bigger problem than you imagined. 😉 Of course, only a test will tell for sure.
I have no illusions about its performance. Just having a little fun while I explore the world of recursive CTEs. 😉
Yeah... but you may have to turn in your "No RBAR" card if you keep it up. 😀
I wasn't expecting a penalty flag for that!
For the record, my explorations of rCTEs are in this vain:
“If you know the enemy and know yourself, you need not fear the result of a hundred battles. If you know yourself but not the enemy, for every victory gained you will also suffer a defeat. If you know neither the enemy nor yourself, you will succumb in every battle”
? Sun Tzu, The Art of War, Special Edition
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 15, 2012 at 8:27 pm
dwain.c (5/15/2012)
Jeff Moden (5/15/2012)
dwain.c (5/15/2012)
Jeff Moden (5/15/2012)
dwain.c (5/14/2012)
Forgive me oh experts for I have sinned. I have not compared performance against other suggestions. 🙂Since you used a recursive CTE for this task, that may be a bigger problem than you imagined. 😉 Of course, only a test will tell for sure.
I have no illusions about its performance. Just having a little fun while I explore the world of recursive CTEs. 😉
Yeah... but you may have to turn in your "No RBAR" card if you keep it up. 😀
I wasn't expecting a penalty flag for that!
For the record, my explorations of rCTEs are in this vain:
“If you know the enemy and know yourself, you need not fear the result of a hundred battles. If you know yourself but not the enemy, for every victory gained you will also suffer a defeat. If you know neither the enemy nor yourself, you will succumb in every battle”
? Sun Tzu, The Art of War, Special Edition
Heh.... horse muffins.
"Tested not, you have, metal of thy enemy thee covet." 😉
? Jeff Moden, Understanding The Art of BS, 60th Edition
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2012 at 10:27 pm
Jeff Moden (5/15/2012)
dwain.c (5/15/2012)
Jeff Moden (5/15/2012)
dwain.c (5/15/2012)
Jeff Moden (5/15/2012)
dwain.c (5/14/2012)
Forgive me oh experts for I have sinned. I have not compared performance against other suggestions. 🙂Since you used a recursive CTE for this task, that may be a bigger problem than you imagined. 😉 Of course, only a test will tell for sure.
I have no illusions about its performance. Just having a little fun while I explore the world of recursive CTEs. 😉
Yeah... but you may have to turn in your "No RBAR" card if you keep it up. 😀
I wasn't expecting a penalty flag for that!
For the record, my explorations of rCTEs are in this vain:
“If you know the enemy and know yourself, you need not fear the result of a hundred battles. If you know yourself but not the enemy, for every victory gained you will also suffer a defeat. If you know neither the enemy nor yourself, you will succumb in every battle”
? Sun Tzu, The Art of War, Special Edition
Heh.... horse muffins.
"Tested not, you have, metal of thy enemy thee covet." 😉
? Jeff Moden, Understanding The Art of BS, 60th Edition
If you're going to bust my chops, I shall have to eat my horse muffins (whatever those are dare I ask) and admit that the first rCTE was a dog.
This one's a little better.
DECLARE @t TABLE (ID INT IDENTITY, strcol VARCHAR(100))
INSERT INTO @t
SELECT 'here is some text'
UNION ALL SELECT ' and HERE is SOME more'
UNION ALL SELECT 'with, some. punctuation'
;WITH ProperCase(ID, sc, ec, strcol, rn) AS (
SELECT ID, 1, CAST(CHARINDEX(' ', strcol + ' ') AS INT), LOWER(strcol)
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM @t
UNION ALL
SELECT ID, ec + 1, CAST(CHARINDEX(' ', strcol + ' ', ec + 1) AS INT), strcol, rn
FROM ProperCase WHERE CHARINDEX(' ', strcol + ' ', ec + 1) <> 0 )
SELECT strcol, STUFF(
(SELECT ' ' + strcol
FROM (
SELECT ID
,CASE WHEN STUFF(SUBSTRING(strcol, sc, ec - sc),1,1,UPPER(SUBSTRING(strcol, sc, 1))) IS NULL THEN ' '
ELSE STUFF(SUBSTRING(strcol, sc, ec - sc),1,1,UPPER(SUBSTRING(strcol, sc, 1))) END AS strcol
FROM ProperCase
) t2
WHERE t1.ID = t2.ID
FOR XML PATH(''), root('M'), type).value('/M[1]','varchar(100)' )
, 1, 1, '') As DelimitedString
FROM @t t1
We can call it the best of the worst. 😀
"Imagination is more important than knowledge." -- Albert Einstein
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 15, 2012 at 11:15 pm
BWAAA-HAAA!!! Thanks for having a good sense of humor, Dwain! You've made my day! 🙂
Shifting gears back to the subject at hand... there's a While Loop solution to this problem that will beat all other T-SQL solutions... even the Tally Table and XML solutions. These types of "memory only" problems, when not given to a CLR, are actually best solved by those things we all normally try to avoid. While Loops and Scalar UDF's. 🙂 It's that 0.1% that doesn't apply to set based logic very well.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2012 at 11:36 pm
Jeff Moden (5/15/2012)
BWAAA-HAAA!!! Thanks for having a good sense of humor, Dwain! You've made my day! 🙂Shifting gears back to the subject at hand... there's a While Loop solution to this problem that will beat all other T-SQL solutions... even the Tally Table and XML solutions. These types of "memory only" problems, when not given to a CLR, are actually best solved by those things we all normally try to avoid. While Loops and Scalar UDF's. 🙂 It's that 0.1% that doesn't apply to set based logic very well.
You mean something like this?
CREATE FUNCTION dbo.ProperCase (@s VARCHAR(8000))
RETURNS VARCHAR(8000) WITH SCHEMABINDING AS
BEGIN
DECLARE @p INT;
SELECT @s-2 = UPPER(SUBSTRING(@s,1,1)) + LOWER(SUBSTRING(@s, 2, LEN(@s)))
,@p = CHARINDEX(' ', @s-2) + 1
WHILE @p > 1 AND @p <= LEN(@s)
SELECT @s-2 = STUFF(@s,@p,1,UPPER(SUBSTRING(@s,@p,1)))
,@p = CHARINDEX(' ', @s-2 + ' ', @p) + 1
RETURN @s-2
END;
May not be the best of the best but surely not the worst of the best either. 😀
I not only have a great sense of humor, I also learn my lessons!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 25, 2012 at 5:14 pm
dwain.c (5/15/2012)
Jeff Moden (5/15/2012)
BWAAA-HAAA!!! Thanks for having a good sense of humor, Dwain! You've made my day! 🙂Shifting gears back to the subject at hand... there's a While Loop solution to this problem that will beat all other T-SQL solutions... even the Tally Table and XML solutions. These types of "memory only" problems, when not given to a CLR, are actually best solved by those things we all normally try to avoid. While Loops and Scalar UDF's. 🙂 It's that 0.1% that doesn't apply to set based logic very well.
You mean something like this?
CREATE FUNCTION dbo.ProperCase (@s VARCHAR(8000))RETURNS VARCHAR(8000) WITH SCHEMABINDING AS
BEGIN
DECLARE @p INT;
SELECT @s-2 = UPPER(SUBSTRING(@s,1,1)) + LOWER(SUBSTRING(@s, 2, LEN(@s)))
,@p = CHARINDEX(' ', @s-2) + 1
WHILE @p > 1 AND @p <= LEN(@s)
SELECT @s-2 = STUFF(@s,@p,1,UPPER(SUBSTRING(@s,@p,1)))
,@p = CHARINDEX(' ', @s-2 + ' ', @p) + 1
RETURN @s-2
END;
May not be the best of the best but surely not the worst of the best either. 😀
I not only have a great sense of humor, I also learn my lessons!
THAT's the one. I don't know what other folks have been trying but I've not been able to make even a cteTally solution in an iTVF touch it.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2012 at 5:45 pm
Jeff Moden (5/25/2012)
dwain.c (5/15/2012)
Jeff Moden (5/15/2012)
BWAAA-HAAA!!! Thanks for having a good sense of humor, Dwain! You've made my day! 🙂Shifting gears back to the subject at hand... there's a While Loop solution to this problem that will beat all other T-SQL solutions... even the Tally Table and XML solutions. These types of "memory only" problems, when not given to a CLR, are actually best solved by those things we all normally try to avoid. While Loops and Scalar UDF's. 🙂 It's that 0.1% that doesn't apply to set based logic very well.
You mean something like this?
CREATE FUNCTION dbo.ProperCase (@s VARCHAR(8000))RETURNS VARCHAR(8000) WITH SCHEMABINDING AS
BEGIN
DECLARE @p INT;
SELECT @s-2 = UPPER(SUBSTRING(@s,1,1)) + LOWER(SUBSTRING(@s, 2, LEN(@s)))
,@p = CHARINDEX(' ', @s-2) + 1
WHILE @p > 1 AND @p <= LEN(@s)
SELECT @s-2 = STUFF(@s,@p,1,UPPER(SUBSTRING(@s,@p,1)))
,@p = CHARINDEX(' ', @s-2 + ' ', @p) + 1
RETURN @s-2
END;
May not be the best of the best but surely not the worst of the best either. 😀
I not only have a great sense of humor, I also learn my lessons!
THAT's the one. I don't know what other folks have been trying but I've not been able to make even a cteTally solution in an iTVF touch it.
I thought this one fell off your radar.
Not sure if you noticed the duration between your mention and my posting of it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 25, 2012 at 10:04 pm
Actually, my radar has been pretty much broken for the last week or two. I'm trying to catch up.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply