Jeff Moden

Forum Replies Created

Viewing 15 posts - 43,906 through 43,920 (of 59,063 total)

RE: Constraints

Florian Reischl (5/2/2009)
You can create a scalar UDF which gets the ProductId, DateFiel1 and DateField2 and checks if another Product already exists. Then you can use the UDF in your...
--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)

May 2, 2009 at 8:39 pm

#987784
RE: Filtered index with IS NULL predicate

Heh... I'd recommend that you may need to actually put some data in the table before you even think of trying to optimize a query.
--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)

May 2, 2009 at 8:37 pm

#987783
RE: Calculating monthly billing total

dave b (5/2/2009)
Jeff...Thank you so much! This works great.
BTW...I just started reading your post about the "Tally" table.
I am now officially on a quest to eliminate my cursors 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)

May 2, 2009 at 8:27 pm

#987782
RE: Join problem when convert joins with non ansi to ANSI format

Heh... it could simply be that the non-Ansi join was returning the incorrect row count to begin with. From BOL...
In earlier versions of Microsoft® SQL Server™ 2000, left 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)

May 2, 2009 at 8:22 pm

#987781
RE: Unsubscribe from Topic

I agree. It would be real handy to click on Topic Options and be able to select "Unsubscribe from this topic only".
Of course, it would also be nice if...
--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)

May 2, 2009 at 8:07 pm

#987779
RE: Calculating monthly billing total

Your criteria is wrong. You shouldn't add minimum to the total and the price because in cases there there is anything >0, it will always exceed the minimum which...
--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)

May 2, 2009 at 7:48 pm

#987776
RE: Performance issue with tally solution

Paul White (5/2/2009)
...as I say, it's a shame about the compilation time.
Heh... yeah, I agree... for a million rows, it's a shame. For more common numbers like 8000, all...
--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)

May 2, 2009 at 7:01 pm

#987774
RE: Performance issue with tally solution

Paul White (5/2/2009)
Jeff Moden (5/2/2009)
Yes... but then you must also account for the run being against an NVARCHAR and divide by 4 instead of just 2 in Phil's example... 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)

May 2, 2009 at 5:56 pm

#987772
RE: Performance issue with tally solution

Paul White (5/2/2009)
Well spotted! I must admit I looked at it and gave up after a few minutes...
I guess replacing LEN with DATALENGTH might fix it up too?
Paul
Yes... but...
--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)

May 2, 2009 at 5:36 pm

#987769
RE: BCP command using TSQL without xp_cmdshell

george sibbald (5/2/2009)
that would upset the heck out of most auditors unfortunately.
Why? I would think that the lack of security setup by the DBA that allowed it's misuse would...
--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)

May 2, 2009 at 5:11 pm

#987765
RE: Performance issue with tally solution

Phil Factor (5/2/2009)
They were rather interesting. I was when doing a 'quirky update' from a join with a temporary table and a table variable. It worked fine on the simple...
--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)

May 2, 2009 at 5:00 pm

#987764
RE: OPENQUERY

For the most part, if you have defined a linked server, there is usually no need at all for OPENQUERY or anything like it. Just use a 4 part...
--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)

May 2, 2009 at 1:27 pm

#987747
RE: SET vs SELECT

Heh... I'm definitely NOT an ANSI purest and I definitely DON'T believe in the myth of portable code especially when it comes to high performance batch code.
For all the advantage...
--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)

May 2, 2009 at 1:03 pm

#987745
RE: BCP command using TSQL without xp_cmdshell

george sibbald (5/2/2009)
as part of the same job enable xp_cmdshell just before the bcp and disable it again immediately afterwards
That would certainly work... I guess my question would be...
If...
--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)

May 2, 2009 at 12:43 pm

#987744
RE: I've tried this on 2 other forums, does anyone know the answer?

troy.hodge (5/1/2009)
I want to confirm that is infact correct and a good Idea. Are their short comings to this approach? Any reason we would not want to do this?
Some 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)

May 2, 2009 at 12:21 pm

#987743

Viewing 15 posts - 43,906 through 43,920 (of 59,063 total)