Jeff Moden

Forum Replies Created

Viewing 15 posts - 47,881 through 47,895 (of 59,099 total)

RE: The Weekly News for Oct 6, 2008 (Database Weekly)

:P;)
--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)

October 7, 2008 at 8:01 pm

#882077
RE: Optimizing a Stored Procedure

TenDayIT...
As it turns out, your original code had the same flaw as the modified code... bad Dynamic SQL if more than 1 parameter is provided.
I can help, but I need...
--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)

October 7, 2008 at 7:57 pm

#882076
RE: Optimizing a Stored Procedure

tendayit (10/7/2008)
Hi,
Please find attached the modified code.
Thanks
Tendayi
Your new proc delivers bad SQL if more than one input parameter is provided... here's what the Dynamic SQL it creates looks like when...
--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)

October 7, 2008 at 7:17 pm

#882072
RE: Computation for running Difference

Heh... I recognize that, Clint. And, I'm not being evasive about code. I just recognize that you're both intelligent as well as being a bit new to 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)

October 7, 2008 at 6:50 pm

#882070
RE: Date range to also compute previous date per no. of days

jcrawf02 (10/7/2008)
Jeff Moden (10/6/2008)
This forum is a bit odd... it sometimes turns left parenthesis into smiley faces.
I'm sorry, is that one statement or two? 😀
Heh... now that 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)

October 7, 2008 at 6:42 pm

#882068
RE: 'N' number of usage - Tally Table

jcrawf02 (10/7/2008)
Or are we waiting for Porkslinger 😛 to give us another followup article?
Heh... what an idea... maybe I'll title it "Porkslinger: Have Chops, Will Travel." 😛
--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)

October 7, 2008 at 8:22 am

#881701
RE: do operation on last inserted record by a trigger

... and, if you write it correctly, it will also do just one "record" correctly, as well.
--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)

October 7, 2008 at 8:15 am

#881690
RE: Avoiding Curosr - Yet Another Situation

Two set based "passes", if needed, will still be light years faster than nested loops.
I'm "old school", too, but I sure don't mind being called "Jeff" on these forums. ...
--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)

October 7, 2008 at 8:07 am

#881677
RE: do operation on last inserted record by a trigger

dr_csharp (10/7/2008)
thanks jack . i do this :
CREATE TRIGGER test ON CallRecorder
FOR INSERT
AS
DECLARE @RecId INT;
DECLARE @BeginTime NVARCHAR(8);
SELECT @RecId=RecId,@BeginTime=BeginTime FROM Inserted
set @BeginTime=substring(@BeginTime,1,3);
Update CallRecorder
...
--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)

October 7, 2008 at 8:03 am

#881672
RE: Few interview questions

GilaMonster (10/7/2008)
Jeff Moden (10/7/2008)
the limit on the number of indexes for a table is much less than that. (Teach a person to fish) Lookup "Maximum Capacity Specifications" in Books...
--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)

October 7, 2008 at 7:55 am

#881660
RE: Avoiding Curosr - Yet Another Situation

J (10/7/2008)
Rats !
Another application where I was dead sure the only way to do it was a double set of cursors: calculating the FIFO value of an inventory.
Outer loop: items...
--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)

October 7, 2008 at 7:49 am

#881651
RE: Avoiding Curosr - Yet Another Situation

J (10/7/2008)
Well, cursors are easy to code. Is there a place on this earth where they are the most appropriate solution ?
Actually, in SQL Server 2000, there are places where...
--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)

October 7, 2008 at 7:47 am

#881649
RE: Swapping Columns

Oh yeah... almost forgot... you could also use UNPIVOT... look it up. I don't use it... I normally do it the way you did 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)

October 7, 2008 at 7:37 am

#881639
RE: Swapping Columns

The way you did it is fine, Karthik... only change I would make is to change all the UNION's into UNION ALL for performance and data integrity reasons. UNION...
--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)

October 7, 2008 at 7:36 am

#881637
RE: 'N' number of usage - Tally Table

Dugi (10/7/2008)
Jeff Moden (10/7/2008)
I've used it for "cleaning" data... you know... remove all numbers, remove everything but the numbers, remove special characters, remove adjacent multiple spaces (although there are better...
--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)

October 7, 2008 at 7:32 am

#881630

Viewing 15 posts - 47,881 through 47,895 (of 59,099 total)