Jeff Moden

Forum Replies Created

Viewing 15 posts - 8,701 through 8,715 (of 59,099 total)

Reply To: Finding records that a negative number with a corresponding positive number

@ Briceston,
Considering the brief interlude between Erland and myself as well as Scott's previous early warning, do you have another column that could act as a temporal tie-breaker? For example...
--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)

September 7, 2019 at 9:45 pm

#3678221
Reply To: Finding records that a negative number with a corresponding positive number
Erland Sommarskog wrote:
On SQL 2012 and later:
```
WITH CTE AS (
   SELECT AccountNumber, TxtAmount, PrevAmount = LAG(TxnAmount) OVER (PARTITION BY AccountNumber ORDER BY TransactionDate), TransactionDate
  ...
```
--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)
September 7, 2019 at 9:34 pm

#3678219
Reply To: Index Question

BWAAA-HAAAAA!!!!... I thought you said you had "a lot to think about". 😀 I didn't know you were going to jump right in and do it even on a dev...
--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)

September 7, 2019 at 9:26 pm

#3678218
Reply To: Select count(*) from table1 is taking time

fahey.jonathan wrote:
Jeff: Thanks for pointing out dbgaragedays's post; I skipped right over it.
To be sure, I meant it as a compliment to the both of you. The two posts serve...
--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)

September 7, 2019 at 12:10 am

#3678146
Reply To: Table size issue
Andrey wrote:
Sam,
what will the following command return?
exec sp_spaceused 'dbo.CONTROL_HIST'
It looks like you have BLOB data types, however I don't see it in the table definition.
With all 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)
September 6, 2019 at 2:22 pm

#3677971
Reply To: SSIS SQL 2016 failing to import date value into a timestamp column!

Thom A wrote:
Jeff Moden wrote:
Why do you have to write all the script components? I thought SSIS was supposed to make it so you basically wouldn't have to write anything.
I don't know...
--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)

September 6, 2019 at 12:34 pm

#3677940
Reply To: SSIS SQL 2016 failing to import date value into a timestamp column!

Jeffrey Williams wrote:
Just an FYI - but everything that batch files does can be done in SSIS.
For FTP/sFTP - you can use WinSCP and a script component to download files.
For ZIP...
--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)

September 6, 2019 at 11:41 am

#3677924
Reply To: Index Question
mike 57299 wrote:
Jeff,
Would it help to split the table into 2. One with all data except the varchar(max) and one with the same ID and the varchar(max) only?
Mike
Exactly BUT... you don't...
- This reply was modified 7 years, 1 months ago by Jeff Moden.
--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)
September 6, 2019 at 1:02 am

#3677826
Reply To: Select count(*) from table1 is taking time

fahey.jonathan wrote:
Rather than check the table, I check the main index on the table to see how many rows it has. In my experience, this is much faster than checking...
--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)

September 5, 2019 at 2:06 am

#3677466
Reply To: Problem with simple stored procedure

Steve Collins wrote:
If you run it a million times. In this case here the OP has no 'from' clause so it only executes once. I actually did read your article a...
--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)

September 5, 2019 at 1:55 am

#3677435
Reply To: Add working days but avoid holidays

habib wrote:
Sorry. I should withdraw that comment, at least temporarily.
The article (Script actually) has not been released for publication - i posted it just today.
Ah... got it. Nah... don't withdraw...
--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)

September 5, 2019 at 1:13 am

#3677430
Reply To: Index Question

That's a pretty strong indication that the problem may exist. All of those items are actually in the Clustered Index. With the large row count and the fact that "only"...
--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)

September 4, 2019 at 10:12 pm

#3677408
Reply To: Adding tempdb files to triage miscongfigured tempdb

ScottPletcher wrote:
Jeff Moden wrote:
webrunner wrote:
The server has a database that is several hundred GB in size, so it's amazing that the server is even running at all.
Heh... while the world insists...
--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)

September 4, 2019 at 9:59 pm

#3677404
Reply To: Problem with simple stored procedure

A simple Scalar Function will cause the code to take about 7 times longer than either inline code or an iTVF that acts like a scalar function. Please see 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)

September 4, 2019 at 9:54 pm

#3677402
Reply To: Add working days but avoid holidays

habib wrote:
i just posted an article that uses a tally table. Substitute the holiday table with a resource-specific holiday/Non-working table, if needed.
Congratulations but it doesn't help if we don't have...
--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)

September 4, 2019 at 9:34 pm

#3677389

Viewing 15 posts - 8,701 through 8,715 (of 59,099 total)