Jeff Moden

Forum Replies Created

Viewing 15 posts - 8,671 through 8,685 (of 59,072 total)

Reply To: Index Question

For those that may be following this thread...
Mike provided the results for the code I asked for in a PM. He didn't want to reveal monthly counts, drive letters, etc,...
--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 8, 2019 at 7:31 pm

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

@ Briceston,
Please see the first link in my signature line below for why Erland is saying what most of us are thinking. We try to help a lot of people...
--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 8, 2019 at 2:54 pm

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

Briceston wrote:
Gents,
First, thank you all for chiming in with the provided insight.
I have since upgraded to SQL Server 2017 for those who mentioned the usage of the older SQL Server...
--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 8, 2019 at 2:27 am

#3678232
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 6 years, 10 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

Viewing 15 posts - 8,671 through 8,685 (of 59,072 total)