Jeff Moden

Forum Replies Created

Viewing 15 posts - 24,751 through 24,765 (of 59,102 total)

RE: Extent Scan Fragmentation

From what I understand, fragmentation calculations at the extent level were actually flawed in DBCC SHOWCONTIG and that fragmentation detection in sys.dm_db_index_physical_stats is actually more accurate.
--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)

January 17, 2014 at 2:41 pm

#1681271
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Crud. I just noticed a limitation and I need to post another correction to the article. I tested for all dates from 1900-01-01 00:00:00.000 through 9999-12-31 23:59:59.997. ...
--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)

January 16, 2014 at 10:07 pm

#1680954
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Since there was some interest in correctly calculating the duration even if the start and end dates were reversed, here's a function that straightens that out. Personally, I'd much...
--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)

January 16, 2014 at 9:40 pm

#1680953
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Thomas Abraham (1/16/2014)
I meant to post this earlier, but got caught up in some of the tangent. Thanks to Jeff for providing this spackle. Great job, as always.
Thanks for 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)

January 16, 2014 at 9:07 pm

#1680951
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

rho_pooka (1/16/2014)
Thank you for article, enjoyed the read!
Thank you very much for the feedback. I appreciate it.
--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)

January 16, 2014 at 9:04 pm

#1680950
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

dwain.c (1/16/2014)
I must say, I love the technique Jeff! Very simple, straightforward and clean with good performance. Nothing more straightforward comes immediately to mind (issues noted above aside).
Normally...
--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)

January 16, 2014 at 8:53 pm

#1680947
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Peter E. Kierstead (1/16/2014)
[h4]Try this...[/h4]
Create Function dbo.ElapsedTime
(
@StartTime DateTime,
@EndTime DateTime
)
Returns VarChar(20)
As
Begin
Declare
...
--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)

January 16, 2014 at 8:35 pm

#1680945
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

mayor 76640 (1/16/2014)
My specific use of the datetime field is to see if it occurs
within a user specified date range.
The examples I received with the stored procedures (from software application...
--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)

January 16, 2014 at 8:08 pm

#1680942
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Thomas Abraham (1/16/2014)
Oliver Gugolz (1/16/2014)
Here is my simple method to format the output to a more readable format:
CAST(GETDATE() - @StartDT AS TIME(2)) AS Duration
But that only displays the fractional hours...
--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)

January 16, 2014 at 8:06 pm

#1680941
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Eirikur Eiriksson (1/16/2014)
Thank you for the spackle Jeff but it looks like there is a small crack in it as it makes the assumption that the end time is always...
--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)

January 16, 2014 at 8:03 pm

#1680940
RE: BCP export to csv

cdutre (1/16/2014)
I hate replying to an old thread but I ran into the same problem and was able to resolve it.
Once I eliminated any field values that could...
--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)

January 16, 2014 at 1:45 pm

#1680852
RE: Deprecated]Function call can be extracted from the WHERE clause to avoid unnecessary table scan.

Welsh Corgi (1/16/2014)
Lowell (1/16/2014)
dateadd is not deprecated.
I run it through ubitsoft's T-SQL Analyzer and I get the following:
SA0028 : [Deprecated]Function call can be extracted from the WHERE clause...
--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)

January 16, 2014 at 12:06 pm

#1680814
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

I can't spend much more time on the discussions that are unfolding until I get home from work tonight but the folks at SQLServerCentral have posted the correction to 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)

January 16, 2014 at 9:00 am

#1680706
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

cready27 (1/16/2014)
I'm guessing the @EndDT for the first code example should have been.
@EndDT = '2000-01-02 12:34:56.789'
That gives the same answer as the example.
Correct. I've submitted a correction. ...
--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)

January 16, 2014 at 8:36 am

#1680694
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Mikael Eriksson SE (1/16/2014)
There is one more thing to notice about the first query.
You assign the value "2000-02-01 12:34:56.789" to @EndDT but the query returns "2000-01-02 12:34:56.789".
Either make sure you...
--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)

January 16, 2014 at 8:21 am

#1680686

Viewing 15 posts - 24,751 through 24,765 (of 59,102 total)