Jeff Moden

Forum Replies Created

Viewing 15 posts - 3,241 through 3,255 (of 59,098 total)

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
Reply To: How to send data from table to text file by date

Just in case you're still around, the reason I'm asking is because, if the ultimate use is to import the data into another SQL Server, using BCP Out in 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 4:54 pm

#4058551
Reply To: Employee Hierarchy Recursive CTE Different Top Level Directors Overtime

In other words, please read and heed the article at the first link in my signature line below for one of many methods to provide some "Readily Consumable Test Data"...
--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 3:54 pm

#4058545
Reply To: How to export SQL database tables into multiple XML Files?

Please go back and read the reply from Scott Pletcher and myself.
I agree that it sounds like you're trying to do a server to server transfer. With the exception of...
--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 3:48 pm

#4058543

Viewing 15 posts - 3,241 through 3,255 (of 59,098 total)