Jeff Moden

Forum Replies Created

Viewing 15 posts - 2,971 through 2,985 (of 59,086 total)

Reply To: SELECT * FROM ( ... ) X

p.s. Since you've not been around since the 90's, you might want to have a look at the introduction to a technique that is extremely useful for a whole lot...
--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 16, 2022 at 4:39 pm

#4077043
Reply To: SELECT * FROM ( ... ) X

FatsGordon wrote:
Thank you Jeff.
It was just an example code from a book I'm reading intended to exemplify the use of WHILE in TSQL. Maybe it's not the best example, but...
--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 16, 2022 at 4:26 pm

#4077042
Reply To: SELECT * FROM ( ... ) X

The reason for the Derived Table "X" is so that they can use a WHERE clause to control how may rows are returned much like would be done using 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 16, 2022 at 1:06 am

#4076753
Reply To: backup and storage questions

Ed, what's your take on using Column Store for column-oriented (they record the entity and the column that changed as well as the "OldValue" and the "NewValue {big mistake on...
--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 10:49 pm

#4076734
Reply To: backup and storage questions

frederico_fonseca wrote:
Jeff Moden wrote:
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...
--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 7:21 pm

#4076675
Reply To: Query Tuning, Why Bother?

Virshu wrote:
Now, I don't want to hijack this thread into EF tuning... but there are code smells in LINQ just like there are in T-SQL. My pet peeve is join...
--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 7:16 pm

#4076674
Reply To: SQL Question - Column References

So.. just to be sure from your example data...
101 has never been changed.
102 was replaced by 103 which was replaced by 104.
Is that correct?
If so, a hierarchical recursive CTE (rCTE)...
--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 7:07 pm

#4076671
Reply To: Database build artefacts: build script, dacpac or .bak file?

For me and just IMHO...
If it's a brand new database, I like to deploy using a restore.
If it's updates to a previously deployed database, then I like scripts because 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)

August 15, 2022 at 6:59 pm

#4076665
Reply To: loading data

The best way is to not read the same area of the table that he's writing to. It's also important that his "loading" should be fast. Some will suggest 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 15, 2022 at 6:39 pm

#4076664
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

Viewing 15 posts - 2,971 through 2,985 (of 59,086 total)