Jeff Moden

Forum Replies Created

Viewing 15 posts - 49,201 through 49,215 (of 59,102 total)

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
RE: CPU 100% Lots of Loks. tempdb.dbo.#TMP_CLIENTS_PROCESSING

Riga,
I'm still looking at the code but I did find a problem that's prevalent throughout the code where there are updates...
See anything wrong with the following code?
[font="Courier New"]-- Update 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)

July 14, 2008 at 9:51 pm

#842791
RE: bulk insert

I think you're importing directly to the final table... why not import into a staging table and then you can do some validations and trimming before the data goes into...
--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 5:22 pm

#842760
RE: coalesce negative values

Ummmm, so convert the result to a Bit datatype... all that is being returned are 1's and 0's... no -1's...
--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 2:23 pm

#842683
RE: coalesce negative values

Just curious... the SIGN method with the subtraction from the ABS seems to work just fine as a negative number detector...
DECLARE @v-2 INT
SET @v-2 = -10
SELECT...
--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 2:17 pm

#842680
RE: Analytical Interview Questions

Do you still have the code for 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 14, 2008 at 1:55 pm

#842671
RE: how to Convert varchar to Numeric

GilaMonster (7/14/2008)
Actually, I did suggest ISNumeric, with the warning that it's not perfect. Does your like allow for - and .?
No... but those can easily be added while excluding a...
--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 8:00 am

#842391

Viewing 15 posts - 49,201 through 49,215 (of 59,102 total)