Jeff Moden

Forum Replies Created

Viewing 15 posts - 35,236 through 35,250 (of 59,099 total)

RE: Can anybody give a hand with this?

You asked for suggested performance improvements and I can only go by the code you posted. Removing the ISNULLs in the fashion I suggested and for the reason 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)

January 25, 2011 at 6:37 am

#1277792
RE: An Introduction to Database Design

kenambrose (1/24/2011)
You're other point is too silly to merit a response...
Not need for that type of attack. Stick to the subject. Either respond to a point or 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)

January 25, 2011 at 6:27 am

#1277784
RE: Can anybody give a hand with this?

Also, you can greatly reduce the number of ISNULL calculations simply because you cannot compare against a NULL. For example, the following snippet from your code...
case when ISNULL(L.AEProductVol,0) >...
--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)

January 24, 2011 at 7:44 pm

#1277582
RE: Filter a table to get unique rows in a certain logic

Agh! Sorry, Lynn, I missed where you said...
!!!! Nevermind, doesn't work !!!!
... at the bottom of your post.
--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)

January 24, 2011 at 7:28 pm

#1277573
RE: Filter a table to get unique rows in a certain logic

Lynn Pettis (1/24/2011)
How about this as a solution?
SET NOCOUNT ON
CREATE TABLE #Sample
...
--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)

January 24, 2011 at 7:18 pm

#1277572
RE: Random ordered groups

Indeed. Happy New Year to you, as well. It's going to be a great year. 🙂
--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)

January 24, 2011 at 7:13 pm

#1277571
RE: swap columns and rows using pivot concept in sql server 2005

Hi Sandeep and welcome aboard!
It's good that you posted data for an example but most people like to test their code before posting it as an answer. The problem...
--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)

January 24, 2011 at 7:08 pm

#1277569
RE: Please help with SQL code

pwalter83 (1/18/2011)
HI Jeff,
Sorry about the mistake in the DDL, I have corrected the same in my earlier post...it should be:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NCV_BL_DHL_TEMP](
[BL_ID] [decimal](10, 0) NOT NULL,
[BL_NUM]...
--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)

January 24, 2011 at 7:02 pm

#1277566
RE: Random ordered groups

ColdCoffee (1/24/2011)
One advice (i know people don't like advices, but i will have to tell it out ), Please do not use a code in LIVE environment if u r...
--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)

January 24, 2011 at 7:00 pm

#1277565
RE: Query Help

moorthy.jy (1/24/2011)
Thanks Lutz. At last, i need to create a UDF by passing CUST_ID
The table contains CRD_X column related to BID_X column.
If any CRD_X contains value "CR" then 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)

January 24, 2011 at 6:57 pm

#1277562
RE: Query to tune

Adding indexes won't help, either. Take a look at your ON and WHERE clauses... pretty sure both are non-SARGable.
...
--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)

January 24, 2011 at 6:02 pm

#1277556
RE: Simple Query Question

tfifield (1/24/2011)
Does a person only clock in and out once per work day? Can someone clock in for work, clock out for lunch, clock back in again after lunch...
--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)

January 24, 2011 at 5:54 pm

#1277555
RE: An Introduction to Database Design

SQLkiwi (1/23/2011)
I have made a number of small changes to the published text based on comments received so far. Thanks to everyone for their opinions and insights.
You've folded 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)

January 24, 2011 at 5:52 pm

#1277553
RE: Creating a comma-separated list (SQL Spackle)

UMG Developer (1/24/2011)
You said 10,000 accounts, but your code only creates 1,000. I just wanted to make sure 1,000 is what you ran with so my results would be comparable.
Agh!...
--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)

January 24, 2011 at 3:17 pm

#1277493
RE: Filter a table to get unique rows in a certain logic

mister.magoo (1/24/2011)
Dave,
I haven't tested yours, but can see that it does not meet with Sir Jeff's strict guidelines....
BWAA-HAA!!!... I'm just the humble tester here. The OP posted the original...
--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)

January 24, 2011 at 3:14 pm

#1277489

Viewing 15 posts - 35,236 through 35,250 (of 59,099 total)