Jeff Moden

Forum Replies Created

Viewing 15 posts - 49,186 through 49,200 (of 59,102 total)

RE: can i avoid cursor?

If you're really going to consider performance and storage, then stop thinking Cursors or While loops and stop thinking about a 5 million row temp table. Make a permanent...
--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 17, 2008 at 1:38 pm

#844638
RE: can i avoid cursor?

ALI (7/17/2008)
Thanks for your comments.
I did solve above issue and got stuck with other. As I have tight production deadlines, I am going for cursor in a stored procedure.
See...
--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 17, 2008 at 12:15 pm

#844583
RE: Need to remove duplicate records and get distinct values

karthikeyan (7/17/2008)
What size column do you thing NAME = '' will make?
Jeff,
Thanks for highlighting this area.
I have executed my previous code.
select distinct ID, Name =''
into #t1
from T
update #t1
set #t1.Name...
--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 17, 2008 at 12:11 pm

#844579
RE: How to Pivot a table

tony.sawyer (7/17/2008)
I find Jeffs answer one that works for this example - but I'm wondering how to do something a bit more...
The solution provided used a case statement where 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)

July 17, 2008 at 12:08 pm

#844576
RE: How to Pivot a table

I'm in the process of writing an article about Crosstabs and Pivots... hope to finish it soon but one of the things I'm finding out in testing is that, except...
--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 17, 2008 at 6:04 am

#844308
RE: Need to remove duplicate records and get distinct values

Jeff Moden (7/16/2008)
karthikeyan (7/16/2008)
Jeff,
how about my code ?
What size column do you thing NAME = '' will make? Also, look at the update... which NAME shall it use to...
--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 17, 2008 at 5:54 am

#844296
RE: How to Pivot a table

The best thing for this is a "Crosstab Report"... and it's easier than the PIVOT operator...
--===== Create and populate a test table
-- THIS IS 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)

July 16, 2008 at 9:17 pm

#844124
RE: can i avoid cursor?

This is right on the border line of me thinking this is some type of homework... please post a decent try!
I will give you a hint, though... lookup GROUP BY.
--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 16, 2008 at 5:35 pm

#844102
RE: Need to remove duplicate records and get distinct values

karthikeyan (7/16/2008)
which one is good when we think performance ?
Try it... let us know...
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has...
--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 16, 2008 at 8:45 am

#843828
RE: Need to remove duplicate records and get distinct values

karthikeyan (7/16/2008)
Jeff,
how about my code ?
What size column do you thing NAME = '' will make? Also, look at the update... which NAME shall it use to do 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)

July 16, 2008 at 8:40 am

#843823
RE: remove spaces more than double

RyanRandall (7/14/2008)
Another technique which can be useful in some circumstances...
update #TEST set Descr = replace(replace(replace(Descr, ' ', '¬ '), ' ¬', ''), '¬', '') where Descr like '% %'
(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)

July 16, 2008 at 8:07 am

#843790
RE: Need to remove duplicate records and get distinct values

Ok... now that I've had a bit of coffee... 😛
In the original problem posted, San didn't care about which name was returned so just use the MIN or Max name...
--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 16, 2008 at 7:37 am

#843755
RE: Need to remove duplicate records and get distinct values

Dugi (7/16/2008)
Hi Jeff!
Your code here just selecting and ordering asc the data inside the table it doesn't do any elimination of duplicate records!
Can you explain little bit this!
Dugi
I believe 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 16, 2008 at 7:17 am

#843745
RE: Need to remove duplicate records and get distinct values

Dugi (7/16/2008)
Hi Jeff!
Your code here just selecting and ordering asc the data inside the table it doesn't do any elimination of duplicate records!
Can you explain little bit this!
Dugi
Heh... Sure! ...
--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 16, 2008 at 7:15 am

#843741
RE: Split data

I wouldn't recommend that way... it has a While loop in it and a couple of other performance related problems. Try the following, please... it also explains how 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)

July 15, 2008 at 11:45 pm

#843521

Viewing 15 posts - 49,186 through 49,200 (of 59,102 total)