Jeff Moden

Forum Replies Created

Viewing 15 posts - 4,621 through 4,635 (of 59,089 total)

Reply To: Reducing Temp Table Scans when joining two temp tables using OR conditions

ScottPletcher wrote:
Yep, and when I look over both documents, they seem in some ways contradictory. The problem is that TABLOCK can lock up other metadata things in the db, so...
--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)

September 22, 2021 at 8:09 pm

#3931985
Reply To: Can't attach MDF and LDF files

Grant Fritchey wrote:
Almost sounds like they may be already attached.
+1000 Either that OF he has an explorer window open and maybe has even clicked on one of the files OR he...
--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)

September 22, 2021 at 8:05 pm

#3931983
Reply To: Reducing Temp Table Scans when joining two temp tables using OR conditions
ScottPletcher wrote:
https://techcommunity.microsoft.com/t5/sql-server/sql-server-2016-minimal-logging-and-impact-of-the-batchsize-in/ba-p/385537
```
Table IndexesRows in table   Hints               With or Without TF 610...
```
--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)
September 22, 2021 at 5:08 am

#3931715
Reply To: Second Friday of every month

lokesh97singh wrote:
solved!
1st friday : (SELECT DATEADD(DAY, DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
2nd friday : (SELECT DATEADD(DAY, 7 + DECODE(EXTRACT(DOW 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)

September 22, 2021 at 4:44 am

#3931711
Reply To: Second Friday of every month

Practically, methods like that work just fine. Technically, it bugs me because the "Tally" sequence generator had to build 366 values and then were filtered out to just 12 quite...
--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)

September 22, 2021 at 1:43 am

#3931689
Reply To: Reducing Temp Table Scans when joining two temp tables using OR conditions

As you said, the Order By will be ignored if it's not needed. It costs nothing to include it just to be sure.
As for TABLOCK not being required, the 2nd...
--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)

September 22, 2021 at 1:27 am

#3931681
Reply To: Second Friday of every month

Jeffrey Williams wrote:
Jeff Moden wrote:
Heh... Wecome aboard but... "Must Look Eye!"
You've posted what looks like Oracle code in an SQL Server forum and it's not going to work in SQL Server.
On a...
--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)

September 21, 2021 at 10:58 pm

#3931653
Reply To: How show only first friday of every month

Can you post your code, Scott?
--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)

September 21, 2021 at 10:53 pm

#3931648
Reply To: How show only first friday of every month

Steve Collins wrote:
Fwiw in 2021. In the FROM clause use dbo.fnTally and CROSS APPLY (both twice) to hierarchically generate the first 7 days of each month. Then in the WHERE clause...
--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)

September 21, 2021 at 9:16 pm

#3931624
Reply To: Second Friday of every month

lokesh97singh wrote:
solved!
1st friday : (SELECT DATEADD(DAY, DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
2nd friday : (SELECT DATEADD(DAY, 7 + DECODE(EXTRACT(DOW 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)

September 21, 2021 at 6:43 pm

#3931592
Reply To: Reducing Temp Table Scans when joining two temp tables using OR conditions

If you add the clustering before you add the data, make sure that you use WITH(TABLOCK) on the INSERT INTO statement to help take advantage of "Minimal Logging", which is...
--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)

September 21, 2021 at 6:38 pm

#3931591
Reply To: Metadata-Data-Ratio in a table

Oh no. You've miss understood a bit. I wouldn't force a GUID out of row.
--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)

September 21, 2021 at 3:56 pm

#3931540
Reply To: Metadata-Data-Ratio in a table

sean redmond wrote:
I'm not sure that I follow you regarding the comments column. The Comments column is a varchar(100). That the UpdatedBy column is a huge source of fragmentation 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)

September 21, 2021 at 2:50 pm

#3931519
Reply To: SSIS - Write table to multiple files

wrote:
Thanks for this case. Very interesting solution!
Sounds like a typical generic post as a precursor to SPAM. "Santa is watching"!
--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)

September 21, 2021 at 1:58 pm

#3931485
Reply To: Invalid cursor state

It's just an INSERT for C.R.U.D. I suspect that the inset is sometimes being blocked or failing DRI (FK's) or some other constraint and it's simply not being reported. 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)

September 21, 2021 at 1:54 pm

#3931483

Viewing 15 posts - 4,621 through 4,635 (of 59,089 total)