Jeff Moden

Forum Replies Created

Viewing 15 posts - 6,106 through 6,120 (of 59,089 total)

Reply To: Can we know the detailed information of a table ?

892717952 wrote:
Jeff Moden wrote:
Ah, but it appears that may be operating under a really bad premise. Changing a VARCHAR(200) to a VARCHAR(100) will, by itself, DO NOTHING to save on space. ...
--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)

November 14, 2020 at 2:25 am

#3807751
Reply To: Can we know the detailed information of a table ?

Ah, but it appears that may be operating under a really bad premise. Changing a VARCHAR(200) to a VARCHAR(100) will, by itself, DO NOTHING to save on space. The 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)

November 13, 2020 at 10:33 pm

#3807705
Reply To: Out of date Statistics

You, sir, are preaching to the choir. I wish your DBA understood the actual value of those system generated column stats.
--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)

November 13, 2020 at 9:37 pm

#3807689
Reply To: Can we know the detailed information of a table ?

892717952 wrote:
Jeff Moden wrote:
892717952 wrote:
I want if we can know how many pieces of data can be saved for each data page. thanks!
Thank you but the question above is basically...
--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)

November 13, 2020 at 1:06 am

#3807364
Reply To: Out of date Statistics

I hate blanket statements like what you say your DBA said. If he said that, he's wrong. Building enough indexes to negate the need for ANY such column statistics would...
--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)

November 12, 2020 at 6:48 pm

#3807268
Reply To: Table Performance Issues

Please see the following link...
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15
... and then search on that page for the words "doesn't reclaim" to answer your questions on why some of your actions made no difference.

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

November 12, 2020 at 2:57 am

#3807041
Reply To: Can we know the detailed information of a table ?

892717952 wrote:
I want if we can know how many pieces of data can be saved for each data page. thanks!
Thank you but the question above is basically just 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)

November 12, 2020 at 1:41 am

#3807030
Reply To: Exporting Data with PowerShell

BassmanDBA wrote:
Exactly what I needed for a DR query results export process I'm working on.
Thank you for this!
If you moving data between SQL Servers, the "native" mode of BCP will...
--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)

November 10, 2020 at 9:34 pm

#3806585
Reply To: Can we know the detailed information of a table ?

892717952 wrote:
we can use sp_spaceused to know how many space reservred by a table and know how many spaces for data space and index space, but if there is 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)

November 10, 2020 at 8:59 pm

#3806556
Reply To: Self-numbered primary key creation

Just to be sure... the CTE's that generate the sequence in the code are NOT "recursive" CTEs. They're "cascading" CTE's and there's a huge difference in both performance and resource...
--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)

November 10, 2020 at 8:50 pm

#3806551
Reply To: Is Index Fragmentation Still Relevant in a Cloud Base/Virtual SQL Server Setup

YSLGuru wrote:
Thanks Jeff & Scott for taking the time to reply. It may sound like a dumb question but I asked anyway because the hardware/network folks keeping trying to say...
--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)

November 10, 2020 at 8:26 pm

#3806543
Reply To: Is Index Fragmentation Still Relevant in a Cloud Base/Virtual SQL Server Setup

To answer your question, my answer would be "Probably even more so".
--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)

November 9, 2020 at 8:21 pm

#3806109
Reply To: How to optimize insert , delete and update sql statement

892717952 wrote:
Thank you Jeff Moden for you kind help!
you said "Understanding that there are at least 6 different Insert/Update patterns and the 4 of them are seriously affected by "ExpAnsive"...
--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)

November 9, 2020 at 5:54 pm

#3806048
Reply To: Building Bad Software Faster

+1 Million, Mr. Poole. Especially on that last thing. It's funny how people will change the tools (sometimes causing huge amounts of lost learning and functionality) in attempt to improve...
--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)

November 9, 2020 at 2:33 pm

#3805969
Reply To: How to optimize insert , delete and update sql statement
I think the 3 most important things are...
1. Correctly designed and indexed DRI.
2. Proper design of tables.
3. A serious understanding of page splits and all that goes with them including...
--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)
November 9, 2020 at 5:49 am

#3805824

Viewing 15 posts - 6,106 through 6,120 (of 59,089 total)