Jeff Moden

Forum Replies Created

Viewing 15 posts - 7,861 through 7,875 (of 59,072 total)

Reply To: Surprised that Row_Number() has better performance than IDENTITY

I'm thinking that SEQUENCE is going to be about 4 times slower. That's just a guess.
Sounds crazy but this is fascinating to me because I work with some really large...
--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)

January 17, 2020 at 4:04 pm

#3716356
Reply To: How Do We Prove Expertise?

BAAA-HAAAA!!! Step 1 is to ask them how to get the current date and time in T-SQL. 😀
Of course, when you do find an expert, can you actually use them...
--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)

January 17, 2020 at 2:20 am

#3716185
Reply To: Autogrowth for data file

No. I'm NOT recommending that you set a MB value the same as the MDF/NDF for LDF although they can be set the same. "It Depends". On what?
The LDF file...
--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)

January 17, 2020 at 12:36 am

#3716179
Reply To: Max function and indexes

Craig Smith wrote:
Thanks everyone. I have added a clustered primary key in our test environment but can't compare the performance as the configuration is totally different. When I do the 300...
--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)

January 17, 2020 at 12:33 am

#3716178
Reply To: Calculating totals based on a column flag

This is really the same as a "Catch-All" query. Why not write the dynamic SQL to do 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)

January 16, 2020 at 11:05 pm

#3716166
Reply To: Why is this query slow?
kfrancis25 wrote:
It's all in that link: https://www.brentozar.com/pastetheplan/?id=SJF21m0lU
```
INSERT INTO @retTable
SELECT c.ClaimID, cd.ServiceCode, c.ServiceDate, cd.ServiceQty, c.CareProviderID, ISNULL(cd.DiagnosticCode, c.DiagnosticCode), c.SpecialtyCode AS Speciality, 
   sg.GroupCategory AS ServiceCodeGroupCategory, 0, NULL
FROM...
```
--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)
January 16, 2020 at 11:00 pm

#3716161
Reply To: SELECT FROM sys tables does not work with 3 part database naming?
I think something like this is what you're looking for...
```
 SELECT * 
   FROM sys.dm_db_index_usage_stats x
   JOIN VendorDB.sys.indexes i
    ...
```
--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)
January 16, 2020 at 10:53 pm

#3716160
Reply To: SELECT FROM sys tables does not work with 3 part database naming?

sys.dm_db_index_usage_stats covers all of the databases no matter which database you call it from. You have to identify the database_id in your query.

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

January 16, 2020 at 10:49 pm

#3716159
Reply To: Max function and indexes

Craig Smith wrote:
ScottPletcher wrote:
Hmm, maybe I misunderstand, I thought the OP said that it was just that one query for the max date, ~ every hour, in order to provide 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)

January 16, 2020 at 9:19 pm

#3716141
Reply To: Finding experation date

As a bit of a sidebar, why not just use the Developer's Edition for evals? So long as you never use the Developers Edition for production, it's legal.
--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)

January 16, 2020 at 9:09 pm

#3716140
Reply To: Float Issues

Rune Bivrin wrote:
Yes, because MONEY is a precise data type with 4 decimal places. It's implemented as a 64-bit integer, scaled down by 10000. That and DATETIME must be the two...
--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)

January 16, 2020 at 7:30 pm

#3716099
Reply To: Max function and indexes

We're getting a bit lost here because I wasn't clear my last post. I'm thinking this isn't the only query that will be played against the column.
--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)

January 16, 2020 at 7:20 pm

#3716096
Reply To: Max function and indexes

ScottPletcher wrote:
Given that the table had no index at all, I didn't think in this case that making the clus index unique was necessary, particularly to the extent of adding...
--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)

January 16, 2020 at 4:24 pm

#3716034
Reply To: Max function and indexes

That being said, I'd love to see the schema of the table and what the real use of finding the max date would be for this 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)

January 16, 2020 at 4:15 pm

#3716031
Reply To: Max function and indexes

Adding to Lynn's post, I will say that having a UNIQUE Clustered Index is usually better because it gets used in all Non-Clustered indexes, as well.
To wit, I do have...
--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)

January 16, 2020 at 3:38 pm

#3716025

Viewing 15 posts - 7,861 through 7,875 (of 59,072 total)