Jeff Moden

Forum Replies Created

Viewing 15 posts - 3,226 through 3,240 (of 59,086 total)

Reply To: First responders kit - BlitzIndex question

ScottPletcher wrote:
If "silly" people are writing such queries -- and no one in IT can tell you to ignore them ...
My problem is that those two groups are frequently 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)

July 7, 2022 at 2:51 am

#4059889
Reply To: Performance problem

wrote:
How fix SQL performance issues? Start by checking your wait stats
1-Ensure your TempDB database is configured optimally.
2-Make sure you're running index maintenance frequently.
3-Implement indexes that provide a benefit to...
--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)

July 7, 2022 at 2:44 am

#4059887
Reply To: Quick SQL challenges for mid-experienced+ DBAs (and advanced T-SQLers)
Steve Collins wrote:
```
select cast(year(v.sort_mo) as smallint), input_str.mo
from (values ('Jan'),('Feb'),('Mar'),
             ('Apr'),('May'),('Jun'),
      ...
```
--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)
July 6, 2022 at 2:50 pm

#4059635
Reply To: Quick SQL challenges for mid-experienced+ DBAs (and advanced T-SQLers)

There's something crazy going on with Jonathan's machine, Scott. Look at the results of your code in his run... it's all over the place.
I'll also say that on both my...
--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)

July 6, 2022 at 2:13 am

#4059379
Reply To: Quick SQL challenges for mid-experienced+ DBAs (and advanced T-SQLers)

Steve Collins wrote:
Jeff Moden wrote:
Steve Collins wrote:
Here's 1, 3, and 4
I was going to test this for performance but then noticed that it's not a complete answer 😮
The data types were added. 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)

July 6, 2022 at 2:06 am

#4059377
Reply To: Quick SQL challenges for mid-experienced+ DBAs (and advanced T-SQLers)

Oh my! That's a crazy amount of difference, Jonathan... even from the runs for the same code. What else is running on your machine?
I'm running SQL Server 2017 Developer's Edition
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)

July 6, 2022 at 2:02 am

#4059374
Reply To: Quick SQL challenges for mid-experienced+ DBAs (and advanced T-SQLers)
For Question #1, Scott's method is the fastest. Don't take my word for it, though. Here's the test table...
```
--===== If the test table exists, drop it just...
```
--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)
July 6, 2022 at 12:42 am

#4059350
Reply To: Quick SQL challenges for mid-experienced+ DBAs (and advanced T-SQLers)
Steve Collins wrote:
Here's 1, 3, and 4
--(1)
select v.sort_mo, input_str.mo
from (values ('Jan'),('Feb'),('Mar')) input_str(mo)
cross apply (values (parse(concat(input_str.mo, '-01-1900') as date))) v(sort_mo)
order by v.sort_mo;
I was going...
--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)
July 6, 2022 at 12:01 am

#4059336
Reply To: Quick SQL challenges for mid-experienced+ DBAs (and advanced T-SQLers)
Jonathan AC Roberts wrote:
ScottPletcher wrote:
Jonathan AC Roberts wrote:
ok, that's not what I thought you meant. I thought you meant flip all the bits.
This works for 0 and 1:
```
DECLARE @smallint smallint =...
```
--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)
July 5, 2022 at 11:24 pm

#4059326
Reply To: Quick SQL challenges for mid-experienced+ DBAs (and advanced T-SQLers)
ScottPletcher wrote:
Jonathan AC Roberts wrote:
ok, that's not what I thought you meant. I thought you meant flip all the bits.
This works for 0 and 1:
```
DECLARE @smallint smallint = 0
select...
```
--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)
July 5, 2022 at 11:13 pm

#4059315
Reply To: Quick SQL challenges for mid-experienced+ DBAs (and advanced T-SQLers)

What are the datatypes for the two columns in Question #1?
--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)

July 5, 2022 at 10:32 pm

#4059295
Reply To: Removing UNION and getting the same result

Probably so he can get away from having to run two separate queries and then merge the results with a distinct UNION. That may or may not be faster, though. ...
--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)

July 5, 2022 at 2:41 am

#4058876
Reply To: Do you dream about SQL Server and things IT?

skeleton567 wrote:
I wonder if it is because in our dreams our minds are more focused on one thing at a time and it is easier without so many distractions.
I 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)

July 5, 2022 at 2:39 am

#4058875
Reply To: Working with T-SQL function AVG ()

Guy Stephens wrote:
Perhaps more important than the DISTINCT keyword is the AVG() function's handling of NULL values.
NULLs behave similarly to DISTINCT - regardless of how AVG() is called. That is, 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)

July 4, 2022 at 5:52 pm

#4058573
Reply To: Removing UNION and getting the same result

Heh... there is an EXCEPT, though... same "class" of method.
--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)

July 4, 2022 at 5:09 pm

#4058571

Viewing 15 posts - 3,226 through 3,240 (of 59,086 total)