Jeff Moden

Forum Replies Created

Viewing 15 posts - 2,296 through 2,310 (of 59,098 total)

Reply To: Using SQL - Convert column to rows - not a straight forward pivot

abi wrote:
Thank you.
I tweaked it a little and implemented this for my case and it worked.
Can you post your "tweaked" code? Others may learn from 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)

December 18, 2022 at 7:02 am

#4127053
Reply To: What's the logic of the NOT IN clause?

I agree... since NULL is not "nothing" nor even equal to another NULL, I don't know why they made it evaluate for NULL behind the scenes at all or why...
--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)

December 16, 2022 at 7:44 pm

#4126684
Reply To: get a substring value from string

carsten.saastamoinen wrote:
As expected!!!! On my desk it takes only 14 seconds to process 131,000 rows. Hardly not a statement that will be executed several times every minute or several times...
--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)

December 16, 2022 at 6:32 pm

#4126664
Reply To: What's the logic of the NOT IN clause?

The problem is documented (well, kind of) in the Microsoft Documentation. They "cop" out on explaining exactly what could happen by "only" saying that having a NULL in the object...
--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)

December 16, 2022 at 5:42 pm

#4126638
Reply To: how to check the table is being used(selecting/inserting/updating/deleting/locki

I'll also tell you to never just up and delete a table. Instead, rename it and see if the proverbial phone rings. The job you save will be your own.
After...
--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)

December 16, 2022 at 4:47 pm

#4126628
Reply To: get a substring value from string

@carsten.saastamoinen
That "split" contains an incremental recursive CTE and it's going to be absolutely terrible for performance. See here...
https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes
--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)

December 16, 2022 at 4:43 pm

#4126626
Reply To: Struggle Bus - Varbinary(max) Performance Suggestions?

beantownace wrote:
Wow these are awesome replies and super helpful thank you both so much. It has existed for some time but it stored encrypted data then uses a decryptkey with...
--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)

December 16, 2022 at 4:33 pm

#4126623
Reply To: Struggle Bus - Varbinary(max) Performance Suggestions?

How deep does identical data occur from the left side of the VARBINARY(MAX)? You might be able to speed things up a bit by sorting, say, only the left 50...
--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)

December 16, 2022 at 5:52 am

#4126343
Reply To: A question about unique keys

wrote:
How to convert long data to wide data in SQL without using hard coding?
Regards
Like I suggested on the other thread, Manish, open a separate thread for your own question.
[EDIT]...
--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)

December 16, 2022 at 5:47 am

#4126341
Reply To: record loading

Jeffrey Williams wrote:
Correct, you convert the local time to UTC then perform the conversion to Unix timestamp.
In fact, the numeric timestamp is only UTC by convention. There is nothing inherent 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)

December 16, 2022 at 5:36 am

#4126335
Reply To: record loading

Manish_Bose wrote:
Have a doubt regarding converting long data to wide data in SQL with out using hard coding?
Regards
Manish Bose
You should open your own post, Manish, so that it doesn't "get...
--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)

December 16, 2022 at 5:17 am

#4126330
Reply To: Are You Still Using Portable Drives?

Back in the '90's at work, I had to have a reel to reel tape reader, a CD reader, a special optical reader, and multiple floppy drives, and a special...
--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)

December 16, 2022 at 5:12 am

#4126329
Reply To: record loading

"It Depends" on what you're talking about. If you're on Eastern Time and you want the correct data based on a UNIX timestamp, then you need to convert your local...
--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)

December 16, 2022 at 4:47 am

#4126319
Reply To: record loading

Jeffrey Williams wrote:
I am not even sure what your function is trying to do here - or why you would even care about UTC. The UNIX timestamp is an offset 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)

December 16, 2022 at 2:47 am

#4126290
Reply To: get a substring value from string

The new STRING_SPLIT with the 3rd operand is great for this. The old STRING_SPLIT without it is not. Even MS has stated that the order of the output is NOT...
--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)

December 15, 2022 at 9:27 pm

#4126236

Viewing 15 posts - 2,296 through 2,310 (of 59,098 total)