Jeff Moden

Forum Replies Created

Viewing 15 posts - 19,756 through 19,770 (of 59,072 total)

RE: udf with tally table runs slower than while loop

You bet. It's one of my favorite charts because of the line for the rCTE. Absolutely amazing how comparatively slow those bad boys can be.
--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)

August 26, 2015 at 7:20 am

#1822459
RE: The Tally Table

dwain.c (8/25/2015)
BTW. Has anybody Googled "Tally Table" recently? Try hitting the I'm Feeling Lucky button.
Don't even need to hit the "Feeling Lucky" button. Your good article show...
--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)

August 26, 2015 at 1:51 am

#1822398
RE: The Tally Table

dwain.c (8/25/2015)
Thank the Buddha for whoever came up with the idea of a Tally table in the first place! I just love 'em (but probably everybody knows that).
In my...
--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)

August 26, 2015 at 1:41 am

#1822394
RE: Email Formatted HTML Table with T-SQL

mister.magoo (8/25/2015)
Jeff Moden (8/25/2015)
greg.bull (4/27/2015)
Just wondering if anyone has worked out how to control foreground AND background colours for cells in a formatted table?
In essence to combine these:
'font/@color' = case...(set...
--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)

August 25, 2015 at 5:03 pm

#1822353
RE: Removing null from Result set

To see more about how the CROSSTAB code above works, please see the following article...
http://www.sqlservercentral.com/articles/T-SQL/63681/
To see how to do it without having to know the number of years, please see...
--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)

August 25, 2015 at 5:01 pm

#1822352
RE: What is the timestamp data type for?

sgmunson (8/25/2015)
Rod at work (8/24/2015)
Hmmm. Thank you Grant, I really appreciate the straight forward explanation for what the rowversion/timestamp is all about. I'm working on archiving the data, but leaving...
--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)

August 25, 2015 at 4:57 pm

#1822350
RE: poor sql server performance

It may also be that the data has grown and there's simply not enough memory to do what you want.
--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)

August 25, 2015 at 4:32 pm

#1822348
RE: cross-database function call

rob.lewis 86087 (7/2/2015)
i have 2 databases on the same server
One has a function, the other has the tables and views
using dba
select dbo.function(t.column) as x from dbb.dbo.table as t
gives m an...
--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)

August 25, 2015 at 4:01 pm

#1822345
RE: udf with tally table runs slower than while loop

ChrisM@Work (8/25/2015)
Hi Josh
IIRC a hard tally table is usually slightly faster than an inline one. Google will tell you for sure.
No need to Google. Here's a chart from the...
--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)

August 25, 2015 at 9:40 am

#1822233
RE: Email Formatted HTML Table with T-SQL

greg.bull (4/27/2015)
Just wondering if anyone has worked out how to control foreground AND background colours for cells in a formatted table?
In essence to combine these:
'font/@color' = case...(set up to return...
--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)

August 25, 2015 at 9:26 am

#1822228
RE: Replace and Substring

Just an added tip...
This...
and DOB is not null
and DOB <>''
... is the same as this...
and DOB > ''
... and can sometimes be a fair bit faster. It works because...
--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)

August 24, 2015 at 7:34 pm

#1822125
RE: poor sql server performance

If the reboot fixes the problem, I strongly suspect some pretty nasty parameter sniffing. My first step would be to clear cache prior to doing the cube rebuilds. ...
--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)

August 24, 2015 at 7:29 pm

#1822124
RE: how to create/send HTML email in SQL and confirm READ without asking?

halifaxdal (9/15/2010)
Hello folks,
I know there are lots of useful script/approaches on how to send out HTML email from SQL, one particular situation I am having is: is it possible to...
--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)

August 24, 2015 at 7:22 pm

#1822123
RE: When index was created

MAK-1128556 (8/18/2009)
Thanks, so there is any script we have to find out how many indexes need to be defragment
Yes. Lookup [font="Arial Black"]sys.dm_db_index_physical_stats [/font]and [font="Arial Black"]ALTER INDEX[/font] in Books Online...
--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)

August 24, 2015 at 7:12 pm

#1822122
RE: Why Use Version Control?

djackson 22568 (8/24/2015)
<GRIN>
Except that to continue the analogy, you also have to worry about the people who keep walking under you, seeing the safety net and packing it up, moving...
--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)

August 24, 2015 at 5:25 pm

#1822118

Viewing 15 posts - 19,756 through 19,770 (of 59,072 total)