Jeff Moden

Forum Replies Created

Viewing 15 posts - 1,021 through 1,035 (of 59,091 total)

Reply To: What Exactly Is a CTE in T-SQL? A Comprehensive Guide with 7 Examples

It would appear that the author of the article has left the building. 🙁
--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)

October 24, 2023 at 6:25 pm

#4314087
Reply To: What Exactly Is a CTE in T-SQL? A Comprehensive Guide with 7 Examples

Jeffrey Williams wrote:
This isn't quite correct - neither CTEs or Views are executed (materialized). Both will be incorporated into the outer query - and then the execution plan is generated.
Now, if...
--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)

October 24, 2023 at 6:22 pm

#4314086
Reply To: What Exactly Is a CTE in T-SQL? A Comprehensive Guide with 7 Examples

YSLGuru wrote:
Glad to see Jeff is here, I know he'll have some interesting insight onnthis question. CTE or View, which to use and when as well as why?
Thank you 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)

October 24, 2023 at 1:07 am

#4313692
Reply To: What Exactly Is a CTE in T-SQL? A Comprehensive Guide with 7 Examples

p.s. CTEs are inherently NOT reusable anywhere except the query that they live in. They can be referenced as many times as you need to with the understanding that they...
--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)

October 24, 2023 at 1:04 am

#4313690
Reply To: Can Data Save the World?

Back to this older thread of "Can data save the world" and seeing what has been going on with the "climate change discussions" (and I hate to call it that)...
--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)

October 23, 2023 at 8:42 pm

#4313589
Reply To: how to calculate the RAM used while running a query

If you know the SPID for the query, you could also query the following DMV. Look for the word "memory" in the documentation.
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql
--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)

October 23, 2023 at 8:31 pm

#4313588
Reply To: inserting into varchar field doesn't error when > 8000 bytes

Jeffrey Williams wrote:
So - going back to the OP's original post we see that a string that is greater than 8000 characters gets inserted with truncation and is successful for columns...
--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)

October 23, 2023 at 8:29 pm

#4313560
Reply To: The Column Synonym

You CAN, however, create a simple "Pass through view" that will do the trick.
--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)

October 23, 2023 at 1:54 pm

#4313395
Reply To: Delete large number of rows help

Are the rows all at the "end" of the Clustered Index or are they scattered throughout?

--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)

October 23, 2023 at 1:51 pm

#4313394
Reply To: Regarding update stats

ScottPletcher wrote:
Compress the data. Look at both page compression and COMPRESS/DECOMPRESS for large char columns.
As a bit of a sidebar, be aware that Page Compression causes index rebuilds to take...
--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)

October 23, 2023 at 1:40 pm

#4313393
Reply To: Celebrate THEIR Accomplishments

Grant Fritchey wrote:
I sure don't mean for it to be false praise, or even praise for nothing. I've just seen a few people get... for want of a term, upset when...
--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)

October 23, 2023 at 4:28 am

#4313136
Reply To: inserting into varchar field doesn't error when > 8000 bytes

Steve Jones - SSC Editor wrote:
replicate returns 8k
https://learn.microsoft.com/en-us/sql/t-sql/functions/replicate-transact-sql?view=sql-server-ver16
Lordy... I've been bitten by that before. In this case, it returns 8K because it wasn't predefined as a MAX datatype.
Let run the test a bit...
--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)

October 23, 2023 at 4:18 am

#4313135
Reply To: Confidence

David.Poole wrote:
I feel that your most needed confidence is that needed for you to ask questions when you are not sure about something.
One of my biggest regrets is in not...
--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)

October 23, 2023 at 3:44 am

#4313109
Reply To: TRUNCATE an FK

Just to be absolutely correct, you might want to add a PK on EmpID in the Employee table. 😀
Otherwise, awesome question!
--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)

October 20, 2023 at 7:16 pm

#4312010
Reply To: Ola IndexOptimize - USER_DATABASES failed

Actually, REORGANIZE does a whole lot...
It causes log file explosions.
It perpetuates fragmentation because it doesn't clear the pages above the Fill FACTOR.
It combines pages at the worst time possible, which...
--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)

October 20, 2023 at 7:01 pm

#4312005

Viewing 15 posts - 1,021 through 1,035 (of 59,091 total)