Jeff Moden

Forum Replies Created

Viewing 15 posts - 6,991 through 7,005 (of 59,098 total)

Reply To: Explanation of Page Splits

Mr. Brian Gale wrote:
Fill factor only comes into play when the index is NEW or REBUILT. Basically, it is just telling SQL that the data is either changing frequently or the inserts...
--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)

June 4, 2020 at 4:36 pm

#3760573
Reply To: DB Design/Normalization Question

Heh... Most people have a rule of "Normalize 'til it hurts then denomalize 'til it works". I disagree with that. My rule of thumb is "Normalize 'til it hurts... then...
--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)

June 4, 2020 at 4:03 pm

#3760560
Reply To: Data moth wise

Thanks for the feedback and you're welcome. The key now is, since you're the one that has to support the code, do you understand how it works? Especially the DATEDIFF/DATEADD...
--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)

June 4, 2020 at 3:59 pm

#3760555
Reply To: Is it acceptable to use the presence of a value in a NULLable column as a flag?

Mr. Brian Gale wrote:
I think either are acceptable, but I think one will have better performance. I am pretty sure "NOT NULL" is not sargable so you may have a performance hit...
--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)

June 4, 2020 at 3:56 pm

#3760554
Reply To: Is it acceptable to use the presence of a value in a NULLable column as a flag?

Generally, I try to avoid all "IsPresent", "IsCurrent", "IsActive", and, for sure "IsInactive" and "IsNotCurrent" columns, etc, etc. The data tells it all.
--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)

June 4, 2020 at 3:32 pm

#3760543
Reply To: Data moth wise
Try this...
```
 SELECT  Name
        ,country
        ,avg_amount = AVG(amount)
    ...
```
--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)
June 4, 2020 at 3:30 pm

#3760539
Reply To: Data moth wise

Since your result set that shows what you want to display doesn't contain results that match with your sample data in the 3rd column, what are you looking for in...
--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)

June 4, 2020 at 3:16 pm

#3760534
Reply To: Query single column data into three columns

Nothing like stating the obvious, Joe.
--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)

June 3, 2020 at 4:29 pm

#3760174
Reply To: Custom Pivot Table
SQL Espo wrote:
My solution was
```
select AllergyName,
MAX(CASE WHEN ScaleValue = 'Low' THEN ScaleStart else 0 end) AS [LowStart],
MAX(CASE WHEN ScaleValue = 'Low' THEN ScaleEnd else 0 end) AS [LowEnd],
MAX(CASE...
```
--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)
June 3, 2020 at 2:47 pm

#3760129
Reply To: Are the posted questions getting worse?

Lordy... there's a "bot" running with the handle of "Stanley" and it's posting spam every 5 minutes. Between that and another bot that was active only for a while, I'll...
--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)

June 3, 2020 at 4:32 am

#3759808
Reply To: Query single column data into three columns

stephen.aa wrote:
I played around with the query a little and added percentage columns.
The math works, but it seems to default to six decimal places. 100% - 100.000000. Is there 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)

June 3, 2020 at 1:53 am

#3759708
Reply To: Custom Pivot Table

Mine is similar but I took advantage of IIF. It still renders out as CASE expressions behind the scenes so no performance gain or anything like that. I also think...
--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)

June 3, 2020 at 12:46 am

#3759681
Reply To: Integer convert to percent

nymets86 wrote:
Your suggestion worked. Thanks. I am just getting the hang of this but will post the working formula even though there is probably a better way to do this.
CONVERT(DECIMAL...
--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)

June 2, 2020 at 10:07 pm

#3759615
Reply To: Custom Pivot Table

SQL Espo wrote:
Thank you both so much! I was trying to be cute and figure out how to use the PIVOT operation, but going back to the basics and using 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)

June 2, 2020 at 10:04 pm

#3759614
Reply To: SQL account for restoring database

These probably shouldn't be "scripts". They should be stored procedures (possibly in a permanent "utility" database) and then you can do what Scott has suggested to keep things safe.

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

June 2, 2020 at 5:49 pm

#3759539

Viewing 15 posts - 6,991 through 7,005 (of 59,098 total)