Jeff Moden

Forum Replies Created

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

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
RE: Need to remove duplicate records and get distinct values

There are a couple of ways... I believe the fastest for large data sets in SQL Server 2000 would be in the following...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
--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:39 pm

#843519
RE: CPU 100% Lots of Loks. tempdb.dbo.#TMP_CLIENTS_PROCESSING

riga1966 (7/15/2008)
Hi Jeff,
I think you are right.
I replaced UPDATE code and ran it.
But it now gets stuck inside a loop.
I run this SP a a script. I put lots 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)

July 15, 2008 at 1:45 pm

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

Heh... you guys are missing the real problem! 😉 The real problem is why does the OP NOT care about which data is returned? If you don't care,...
--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 12:12 pm

#843315
RE: Split data

Heh... I feel the same... but I said "Today, just this one day, I shall not rant." 😛
--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 12:08 pm

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

karthikeyan (7/15/2008)
use distinct keyword
Let's see how you would do that, Karthik... 😉
--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 8:55 am

#843169
RE: Split data

I know disk space is relatively cheap... I just can't bring myself to suffer the overhead of storing XML in a database anywhere except a temporary staging table. With...
--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 7:06 am

#843066
RE: how to overcome sql injection

paulhunter (7/15/2008)
In the .NET world you don't grant access to the internal code and data structures except thru properties and methods, so don't treat your database differently.
THAT is one 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)

July 15, 2008 at 7:01 am

#843060
RE: CPU 100% Lots of Loks. tempdb.dbo.#TMP_CLIENTS_PROCESSING

After you fix the updates and try the run (please let me know how that goes) again, then next thing I'd try to do is get rid of the While...
--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 14, 2008 at 10:11 pm

#842794
RE: CPU 100% Lots of Loks. tempdb.dbo.#TMP_CLIENTS_PROCESSING

Yep... I found that undocumented alias form of update, too. While it seems to work quite well, you may not want to take a chance on it...
[font="Courier New"]...
--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 14, 2008 at 10:00 pm

#842792

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