Jeff Moden

Forum Replies Created

Viewing 15 posts - 5,431 through 5,445 (of 59,089 total)

Reply To: COUNT(ID COLUMN) not using Clustered Index

Although the PK is frequently also the clustered index, it's not necessarily so. The keys of non-clustered indexes include the keys of the clustered index, which isn't necessarily the column(s)...
--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)

April 8, 2021 at 1:57 am

#3866619
Reply To: Using REVERSE

ScottPletcher wrote:
You don't really need a pk or any other column from the main table. Also, I assumed you're rather see blank than NULL for missing levels, but if 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)

April 7, 2021 at 9:28 pm

#3866547
Reply To: Using REVERSE

Sorry... missed your previous post about the PK.
I have one in the code I posted using ROW_NUMBER() but that's not going to help you. Help me help you by 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)

April 7, 2021 at 5:55 pm

#3866484
Reply To: Using REVERSE

p.s. Like I said, STRING_SPLIT() isn't going to do the job correctly because it doesn't return the ordinal position of the elements nor does MS guarantee the sort order 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)

April 7, 2021 at 5:52 pm

#3866482
Reply To: Using REVERSE

sqldummy79 wrote:
Unfortunately, I can not use DelimitedSplit8K as I can not add functions on the server and I keep getting "'string_split' is not a recognized built-in function name." when 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)

April 7, 2021 at 5:49 pm

#3866481
Reply To: Using REVERSE
In the mean time, here's the code to do the given problem with the given data. There are some caveats, though...
```
   WITH 
 cteEnumerate AS...
```
Attachments:
You must be logged in to view attached files.
--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)
April 7, 2021 at 4:16 pm

#3866441
Reply To: Using REVERSE

@sqldummy79 ,
No matter what we use, the test table is incomplete. Each row needs to have something that uniquely identifies each row as it appears in the original source 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)

April 7, 2021 at 3:46 pm

#3866420
Reply To: Using REVERSE

Mr. Brian Gale wrote:
Since you are using SQL Server 2019, would "string_split()" function suit your needs?
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15
The trouble with STRING_SPLIT() is that it does not return the ordinal position of the elements 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)

April 7, 2021 at 3:15 pm

#3866403
Reply To: What to expect on first day of PL SQL at new firm?

ns550 wrote:
Hi All,
Looking for answer on what do I expect to happen on very first day for PL SQL job role, given 5 years of experience in resume. Because 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)

April 7, 2021 at 3:10 pm

#3866401
Reply To: Odd performance issues

What version of SQL Server were you using on the old server and what are you using on the new server? This is really important because a new "Cardinality Estimator"...
--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)

April 6, 2021 at 4:19 pm

#3866023
Reply To: Query optimisation/execution plan

No. Using (*) as a part of a COUNT is NOT discouraged. It will allow SQL Server to pick the best index to use to accomplish the count. The use...
--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)

April 6, 2021 at 4:14 pm

#3866021
Reply To: Big difference between Estimated and Actual Actual rows in execution Plan.

It's because <insert drumroll here>, it's an "Estimate". 😀
It's like asking someone how many wheels there are on their truck. Because of "statistics" on the subject and you've identified 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)

April 6, 2021 at 2:16 pm

#3865966
Reply To: admin account and pwd

Johan Bijnens wrote:
SSMS-Setup-ENU.18.8.exe /Passive DoNotInstallAzureDataStudio=1 /install /quiet /norestart /log SSMS_install.log
ref: last line of this paragraph
Awesome. Thanks, Johan.
--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)

April 6, 2021 at 2:10 pm

#3865965
Reply To: SQL Agent Job is stuck

Sergiy wrote:
I created [_sandbox] database for this purpose, deployed the script and scheduled it to run every minute.
It's been 15 or 20 executions since then - no issues.
I'm telling you...
--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)

April 6, 2021 at 2:09 pm

#3865964
Reply To: SQL Agent Job is stuck

Once I fixed all the < and > entitizations and changed the database name to one of the one I have, it ran first time with no issues. I wonder...
--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)

April 6, 2021 at 3:50 am

#3865797

Viewing 15 posts - 5,431 through 5,445 (of 59,089 total)

Jeff Moden

Forum Replies Created

Attachments: