Jeff Moden

Forum Replies Created

Viewing 15 posts - 2,956 through 2,970 (of 59,067 total)

Reply To: Would like to understand a better way of updating data that a cursor if possible

p.s. If the table isn't too wide, consider the use of a "System Versioned Temporal Table" which is all of that done auto-magically for you.
https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables
https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-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)

August 15, 2022 at 6:33 pm

#4076661
Reply To: Would like to understand a better way of updating data that a cursor if possible

A far better method would be to write a trigger to do the copy to the history table. Make sure you write it to handle more than one row at...
--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 15, 2022 at 6:30 pm

#4076641
Reply To: Need to rollup some rows to columns

sqlfriend wrote:
Thank you Kaj, that was a great and easy trick.
Thank you so much!
The method Kaj wrote about is call a "CROSS TAB". You can read more about how 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)

August 15, 2022 at 6:22 pm

#4076640
Reply To: How could I have written this script better?

JaybeeSQL wrote:
The table structures are identical across the estate, so I had wondered if they could - or should - be consolidated somehow.
Possibly a Partitioned View. If it's across servers,...
--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 15, 2022 at 6:16 pm

#4076634
Reply To: backup and storage questions

So that means that the application could be modified to look in a different database for a different table, then... correct?
--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 15, 2022 at 6:08 pm

#4076629
Reply To: backup and storage questions

Bruin wrote:
Is there a script that will report savings for Page Compression?
Thanks.

Better than that, there's a system stored procedure that will estimate the savings as bytes before'n'after. You can find...
--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 15, 2022 at 6:04 pm

#4076627
Reply To: backup and storage questions

frederico_fonseca wrote:
Bruin wrote:
Is there a script that will report savings for Page Compression?
Thanks.
yes - its called "read the manual"
Sometimes, that's difficult because a lot of technical manuals aren't written for...
--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 15, 2022 at 6:01 pm

#4076626
Reply To: backup and storage questions

Bruin wrote:
Thanks...
The monthly tables are queried often..
Ok... how are people doing that when there's one table per month? Are you already using partitioning and, if so, what kind?

--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 15, 2022 at 5:58 pm

#4076624
Reply To: Help me design a correct simple table

That certainly a possibility. That's more of a "bridge" table. I don't know what else you're trying to track, though.
--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 15, 2022 at 3:12 am

#4076352
Reply To: backup and storage questions

Bruin wrote:
I have a 1.3 terabyte database and growing, in which 1 table a month collects all of the new data... The process is collecting machine data and creates 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)

August 15, 2022 at 2:15 am

#4076350
Reply To: Help me design a correct simple table

How do you intend to track "transactions"? I seem to be that the operator gets a "batch" of fuel and then the operator give that fuel to a single machine. ...
--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 15, 2022 at 2:07 am

#4076347
Reply To: Using LAG to return prior non null value

Ok... I believe I've got it this time. To summarize, the code solves the problem of NULLs being returned in the "Lag'n'Lead" columns if there is data outside of 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 14, 2022 at 11:54 pm

#4076314
Reply To: Consistent Time

My rub on the subject is concerning portable devices. It's not like people religiously change the timezone on their machine and what does datetimeoffset in EF use as a reference...
--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 14, 2022 at 5:52 pm

#4076272
Reply To: Using LAG to return prior non null value

p.s. I'm thinking that the LAST_VALUE and FIRST_VALUE functions may be a better choice here. I'll give that a try.
EDIT: I just tried them... Prior to SQL Server 2022, LAST_VALUE...
--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 14, 2022 at 2:52 am

#4076162
Reply To: Using LAG to return prior non null value

Crud... just not having a good week here. I just found a bug in my code and had to take it down. 🙁
--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 14, 2022 at 2:22 am

#4076161

Viewing 15 posts - 2,956 through 2,970 (of 59,067 total)