Jeff Moden

Forum Replies Created

Viewing 15 posts - 41,086 through 41,100 (of 59,098 total)

RE: Convert searched String to number

Like I said... very cool that you busted a hump helping yourself, Andy.
Here's a slightly (radically, actually) different approach that uses "Divide'n'Conquer" methods... the details are in 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)

November 21, 2009 at 9:01 am

#1082649
RE: Convert searched String to number

Actually, thank YOU! It's really nice to see someone take a simple hint, do a bit of research, and then actually post a viable solution that's actually readable. ...
--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)

November 21, 2009 at 7:45 am

#1082641
RE: REPLACE Multiple Spaces with One

Joe Celko (11/21/2009)
Back in 2004, we found that a Fibonacci series ran better a binary nesting of REPLACE() functions. It was like an old polyphase merge on a tape...
--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)

November 21, 2009 at 7:36 am

#1082638
RE: REPLACE Multiple Spaces with One

Michael Meierruth (11/21/2009)
In SQL Server 2000 it's a real nightmare. Performance is fairly stable but for many situations the calls to replace actually don't work, in particular when the database...
--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)

November 21, 2009 at 7:28 am

#1082637
RE: Converting varchar to int

chandrashekar,
It would be much better if you talked to the owners of the original table and got them to normalize that terrible CSV column. If you can't do 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)

November 20, 2009 at 11:01 pm

#1082606
RE: Converting varchar to int

Nabha (11/20/2009)
MY bad! go with Vikas, that is the right solution for you.
Heh... no it's not...that function has a While Loop in it and it's going to be slow. ...
--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)

November 20, 2009 at 10:53 pm

#1082605
RE: running total

chandreshgeria (11/20/2009)
Sir ,
I think that what you have given is to lengthy process i give procedure of my way how i calculate running total
-------------------
drop...
--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)

November 20, 2009 at 10:46 pm

#1082603
RE: Rows to columns + count

BWAAA-HAAA!!!! :hehe: Thanks for posting the answer you got but if you had posted data that way in your original post, you'd have had an answer on this...
--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)

November 20, 2009 at 10:39 pm

#1082601
RE: Table insert performance ... please help!

Credits to Navy beans says that the clustered index is on something non-temporal and you're killing the system with page-splits. Page splits stop when you delete some of 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)

November 20, 2009 at 10:23 pm

#1082600
RE: Returning multiple variables from a function

You don't need a function for this... use the last query in your code example and mix the case statments into that so you do things quickly in a set...
--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)

November 20, 2009 at 10:16 pm

#1082598
RE: Urgent: Adding Primary Key to the 70 Million table

DBTeam (11/20/2009)
Thank u So much for all ur sugesstions,
i'll create unique index only
If you have no clustered index, index maintance won't be worth much on a 70 million row heap....
--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)

November 20, 2009 at 10:09 pm

#1082596
RE: Urgent: Adding Primary Key to the 70 Million table

Bru Medishetty (11/18/2009)
In your case, it is better to have a unique constraint enabled rather than having a primary key (Since it is nvarchar(255).
Ummmm.... not necessarily true. A non-clustered...
--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)

November 20, 2009 at 10:05 pm

#1082595
RE: General Question

You'd probably have better luck if you spent some time with Google...
--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)

November 20, 2009 at 9:55 pm

#1082593
RE: Can this query be improved?

AhTu_SQL2k+ (11/19/2009)
It takes about 38s to return 36,000+ rows at the moment and I expect it will become even slower as the tables size are increasing.
Heh...hold on a minute......
--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)

November 20, 2009 at 9:50 pm

#1082592
RE: Convert searched String to number

You've correctly identified the problem which is 90% of the solution... Take a peek at SUBSTRING and CHARINDEX in Books Online to get the rest. Heh... I'd show you...
--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)

November 20, 2009 at 9:47 pm

#1082591

Viewing 15 posts - 41,086 through 41,100 (of 59,098 total)