Jeff Moden

Forum Replies Created

Viewing 15 posts - 22,231 through 22,245 (of 59,072 total)

RE: Guid FK vs Int FK

JKSQL (12/9/2014)
I ran into a performance issue the other day and was really confused.
The stored procedure filled a temp table with the parent objects. If the particular...
--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 9, 2014 at 3:52 pm

#1763675
RE: Why “3653.0 and 36524.0”?

Ed Wagner (12/9/2014)
Jeff doesn't hit people with RBAR, he actively campaigns against it. High-velocity pork chops, on the other hand, are fair game. 😀
Hmmmm... maybe butterfly stuffing the pork...
--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 9, 2014 at 2:06 pm

#1763649
RE: Why “3653.0 and 36524.0”?

jshahan (12/9/2014)
The code exploits what many consider to be a "fault" of the date/time data-types. If you present the functions with a 4 character number, T-SQL will treat it as...
--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 9, 2014 at 2:04 pm

#1763648
RE: Load Folder name and Images into SQL Tables

Ok, here we go.
First, we need a couple of functions to make our lives easier. This first one is a modification of the DelimitedSplit8K function that I've been working...
--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 8, 2014 at 11:38 pm

#1763420
RE: Load Folder name and Images into SQL Tables

Got it. Working on 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 8, 2014 at 9:53 pm

#1763416
RE: Load Folder name and Images into SQL Tables

Yes. I'm curious though... why isn't the full path a file name contained in the table? Without esoteric controls, you wouldn't be able to work your way back...
--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 8, 2014 at 7:59 pm

#1763413
RE: Load Folder name and Images into SQL Tables

This would be super simple to do if you were allowed to use xp_CmdShell. Can we do that or do we need to avoid 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 8, 2014 at 7:36 pm

#1763411
RE: Load Folder name and Images into SQL Tables

Can we see that separate code that works for one image, please?
--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 8, 2014 at 6:47 pm

#1763408
RE: Removing a cursor

PearlJammer1 (12/8/2014)
Hi
This is a question from a high level view about the possibility of removing a cursor from some of our tsql code.
Basically the steps are:
We create a temporary...
--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 8, 2014 at 6:34 pm

#1763407
RE: Why “3653.0 and 36524.0”?

Eirikur Eiriksson (12/8/2014)
Quick joke, 36524 is the number of versions Jeff needs to upgrade on his old test box 😀
😎
Heh... better joke... that's the number of times I've heard 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)

December 8, 2014 at 4:46 pm

#1763405
RE: Why “3653.0 and 36524.0”?

jshahan (12/8/2014)
I see this syntax in Jeff M’s work and others all over the Internet.
select CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
Not difficult to see WHAT is happening but WHY are these particular constant...
--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 8, 2014 at 4:44 pm

#1763404
RE: 350GB Data File.. Is it an issue

You still need to find out about RPO, RTO, and what the Restore Plan is, though. While 380GB isn't a "HUGE" database, it can take a fair amount 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)

December 8, 2014 at 10:24 am

#1763324
RE: Log Bloat

mike 57299 (12/7/2014)
I have a database file that has a mdf of 100gb and a log of 400gb.
Facts:
Full recovery model
Mirrored to 2nd SQL server
full backup taken every morning...
--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 7, 2014 at 2:02 pm

#1763178
RE: Need help to filter based on From (Month and Year) and To (Month and Year)

This should do it and it will be SARGable...
--===== These would be parameters for a stored procedure.
DECLARE @pStartMonthDate DATETIME --Can be any date in the starting month
...
--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 7, 2014 at 1:37 pm

#1763175
RE: Need help to filter based on From (Month and Year) and To (Month and Year)

Eirikur Eiriksson (12/7/2014)
Quick suggestion
😎
SELECT
PT.idx
,PT.paymentDte
,PT.officialReceiptNo
,PT.sysReceiptNo
FROM dbo.paymentTrnx PT
WHERE (YEAR(PT.paymentDte) * 100) + MONTH(PT.paymentDte) BETWEEN 201201 AND 201203;
Careful...
--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 7, 2014 at 1:15 pm

#1763172

Viewing 15 posts - 22,231 through 22,245 (of 59,072 total)