Jeff Moden

Forum Replies Created

Viewing 15 posts - 2,026 through 2,040 (of 59,082 total)

Reply To: How to append data to the previous row

Can you post the actual question that you asked it? I'm curious as to how much it needed to 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)

February 6, 2023 at 6:27 pm

#4146209
Reply To: How to append data to the previous row

jay-125866 wrote:
It is nice to see so many posts that offering different solutions, as well as the performance analysis.
Regarding the real case scenario in my situation, I would expect 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)

February 6, 2023 at 3:09 am

#4145875
Reply To: How to append data to the previous row

That was going to be one of my other tests (and still will be to just to see what happens on different machines). Like I said, this has turned out...
--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)

February 5, 2023 at 9:08 pm

#4145833
Reply To: How to append data to the previous row

So it would seem! Thanks, Jonathan. I'll check later for things like CPU, Reads, Parallelism, and what happens with different indexes.
I take it that the PK you used was keyed...
--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)

February 5, 2023 at 8:37 pm

#4145825
Reply To: How to append data to the previous row

Jonathan AC Roberts wrote:
Here is something to set up about 500k rows of test data
Then some tests:
You're saving me lot's of time, Jonathan. What are the conclusions of your tests?
p.s. I ask...
--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)

February 5, 2023 at 7:34 pm

#4145814
Reply To: How to append data to the previous row
@ jay-125866 ,
This thread has really gotten interesting with some great alternatives to the classic CROSSTAB method.
Can you provide some additional info about your real table?
1. Number of EventID's
2. Average...
--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)
February 5, 2023 at 6:05 pm

#4145803
Reply To: How to append data to the previous row

Jonathan AC Roberts wrote:
Or maybe even using LEAD:
Yep. I suggested that above as a possible alternative. Thanks for taking the time to write the code for 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)

February 5, 2023 at 5:58 pm

#4145795
Reply To: wrong index name

thbaig wrote:
thank you All for valuable directions. I will try to follow correct path as suggested.
one more question about locking, OFFLINE rebuild lock table for upcoming transactions. What about current...
--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)

February 5, 2023 at 5:44 pm

#4145793
Reply To: How to append data to the previous row

Jonathan AC Roberts wrote:
Yes, nice solution Jeff.
Here is an alternative solution:
That might be the way to go instead of using the CROSSTAB method on this one, Jonathan.
--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)

February 5, 2023 at 5:40 pm

#4145792
Reply To: How to append data to the previous row

@ jay-125866 ,
Just to be sure, there are some possibly significant differences between CROSSTAB and the SELF-JOIN methods that have been offered above. The CROSSTAB method does a single pass...
--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)

February 5, 2023 at 5:19 pm

#4145787
Reply To: Replacement for char and ascii functions in natively compiled stored procedures

@mullern,
I guess at this point, I have to ask, why does this need to be done as a Natively Compiled Proc to begin with. The use of a Tally table...
--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)

February 5, 2023 at 5:51 am

#4145659
Reply To: Replacement for char and ascii functions in natively compiled stored procedures

ScottPletcher wrote:
So go ahead and do it. Not sure specifically how a tally table is going to help here. Besides, a tight loop should likely perform well enough on 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)

February 5, 2023 at 5:48 am

#4145658
Reply To: Replacement for char and ascii functions in natively compiled stored procedures

Can you use a Tally function in natively compiled procs? If not, can you build an inline Tally cte? If so, I'd get rid of that bloody while loop.
--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)

February 5, 2023 at 3:37 am

#4145644
Reply To: How to append data to the previous row

I love it when someone posts "Readily Consumable" data along with a crystal clear example of the output that actually contains the same result data that's in the example data......
--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)

February 5, 2023 at 1:41 am

#4145624
Reply To: Replacement for char and ascii functions in natively compiled stored procedures

MullerN wrote:
I actually didn't think of the tally table which will speed up performance, no doubt. My problem is that I need to identify all the non-printable characters and replace...
--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)

February 5, 2023 at 1:10 am

#4145617

Viewing 15 posts - 2,026 through 2,040 (of 59,082 total)