Jeff Moden

Forum Replies Created

Viewing 15 posts - 23,221 through 23,235 (of 59,099 total)

RE: use trigger or better option?

KoldCoffee (7/12/2014)
because I explained to you at least a couple of times, I can't use the ssis package because it is a slow solution. Therefore I'm not using SSIS.
Heh... OK....
--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 13, 2014 at 5:00 pm

#1729004
RE: How to call a stored proc once per each row of a table, without using CURSOR

autoexcrement (7/10/2014)
I thought that went without saying, but yes I should have specified! 🙂 Table-Valued Functions will be many times faster than Scalar Functions!
Apologies. I used an abbreviation 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)

July 11, 2014 at 8:54 am

#1728780
RE: How to call a stored proc once per each row of a table, without using CURSOR

autoexcrement (7/10/2014)
It may also be possible to re-write your SP as a Function and use CROSS APPLY. Just thinking out loud.
That's actually a good idea if it can be written...
--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 10, 2014 at 9:36 pm

#1728593
RE: Executing dynamic SQL string within a function

Robert Davis (5/24/2006)
Try adding a USE statement to the dynamic SQL.<DT>set @sql = N' USE [OtherDatabase] <DT>create function dbo.fn_tot_alldbs(@yourparameter int) <DT>returns int...
--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 10, 2014 at 9:31 pm

#1728592
RE: Is It Time To Clear Out Those Quirky Functions?

aochss (7/10/2014)
Please, please make the error/warning messages better and more precise...Nothing worse than something like this:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement...
--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 10, 2014 at 9:23 pm

#1728588
RE: Is It Time To Clear Out Those Quirky Functions?

jgoodwin 14842 (7/10/2014)
I understand it is designed to cover all the data types, which is sometimes misleading, since values like: 1e2 don't translate into all the datatypes, but 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)

July 10, 2014 at 12:04 pm

#1728483
RE: Splitting time span into multiple entries

sgmunson (7/10/2014)
...but as code like this often ends up much later in the hands of someone with no idea about how it was constructed or why, and then they apply...
--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 10, 2014 at 12:01 pm

#1728482
RE: How to call a stored proc once per each row of a table, without using CURSOR

There's no need to avoid cursors for such a thing. They're not the issue in such instances. The issue would be, what does the stored procedure do and...
--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 10, 2014 at 11:58 am

#1728481
RE: Is It Time To Clear Out Those Quirky Functions?

Alan.B (7/10/2014)
Phil, you can make this more than twice as fast by turning it into an inline table value function like this:
To that, I say... are you sure? 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)

July 10, 2014 at 11:23 am

#1728467
RE: When I script out a Stored Procedure it encloses stings in Double Quotes?

Welsh Corgi (7/10/2014)
All of a sudden hen I script out a Stored Procedure it encloses stings in Double Quotes?
For example
ANDPromo.[Group] IN (''FL_Small'',''FL_Large'')
Also it generates this code that I do...
--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 10, 2014 at 11:19 am

#1728463
RE: Splitting time span into multiple entries

sgmunson (7/10/2014)
There's a much simpler query:
DECLARE @DATE AS datetime = '07/10/2014';
DECLARE @TIME_START_INT AS int = 820;
DECLARE @TSV AS varchar(4) = RIGHT('00' + CAST(@TIME_START_INT AS varchar(4)), 4);
DECLARE @TIME_END_INT AS int =...
--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 10, 2014 at 10:37 am

#1728455
RE: Is It Time To Clear Out Those Quirky Functions?

To be honest, MS spends a shedload of time deprecating and removing things, some of which are incredibly useful. For example, just because many folks don't understand what ISNUMERIC...
--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 10, 2014 at 10:33 am

#1728454
RE: What if interviewer is wrong?

GilaMonster (7/10/2014)
Jeff Moden (7/9/2014)
hoping that you'd explain how your previous company managed to make it so on a 1TB database.
Maybe I'm missing something, but the way I did it (SQL...
--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 10, 2014 at 10:25 am

#1728449
RE: What if interviewer is wrong?

dan-572483 (7/10/2014)
How can you tell if it creates a hidden snapshot - given that it's hidden?
Whether it did or not, it still runs in under 5 hours and there were...
--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 10, 2014 at 9:34 am

#1728437
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Eirikur Eiriksson (7/9/2014)
More for fun but fitting the discussion, kind of before and after:-D
😎
Before
create function sys.fn_numberOf1InBinaryAfterLoc (@byte binary, @loc int)
returns int
as
begin
declare @index int, @mark-3 int, @counter...
--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 10, 2014 at 12:12 am

#1728270

Viewing 15 posts - 23,221 through 23,235 (of 59,099 total)