Jeff Moden

Forum Replies Created

Viewing 15 posts - 44,461 through 44,475 (of 59,101 total)

RE: Convert alphanumeric to BigInt

And, yes... there's a few more tricks we can try.
--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)

March 24, 2009 at 4:29 pm

#965710
RE: Convert alphanumeric to BigInt

jchandramouli (3/23/2009)
Jeff,
I have used your script both in UDF and as a query. But the problem is when the table records exceeds 10000, there is a performance issue. It takes...
--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)

March 24, 2009 at 4:29 pm

#965709
RE: Convert alphanumeric to BigInt

Chirag (3/23/2009)
Jeff
I mistook this (1230-544,15C5487,132DE78) for a single value rather than 3 values that they are. I assume the op wanted to process one value.
Using a number table would...
--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)

March 24, 2009 at 4:26 pm

#965704
RE: is possible thro a single query

S (3/23/2009)
Jeff, may i know what is the tweak and the failed scenario plz.
I had summed the column that came from the CTE... it caused wrong answers almost as if...
--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)

March 23, 2009 at 10:22 pm

#964943
RE: is possible thro a single query

ramu.valleti (3/22/2009)
Yes Jeff Moden,
I'm back..!
The query i was thinking is similar to your query, and here is mine..
SELECT t1.zone,t1.Class,COUNT(Item) Cnt
FROM
(
SELECT zone,Class,Item,SUM(sales) sales
FROM sales
GROUP BY zone,Class,Item
)t1 INNER JOIN
(
SELECT zone,Class,SUM([sales]) sales...
--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)

March 23, 2009 at 10:20 pm

#964942
RE: A Better English Query

and there will be plenty of model design and development for storing data, performing ETL work,
Shoot... they can't even get it right with simple pretty pictures... DTS, SSIS, and...
--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)

March 23, 2009 at 9:32 pm

#964937
RE: Is it possible to simulate the cross apply function?

vwilliams (3/23/2009)
It is taking parameters from an application and getting information to display to an aspx report. The problem is one of the fields (phra_request_querystring) is a string 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)

March 23, 2009 at 9:23 pm

#964936
RE: Which country is best for Outsourcing?

Heh... Detroit... where weak developers are killed and eaten and there is no night when there's a deadline. 😉
--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)

March 23, 2009 at 9:12 pm

#964933
RE: It Depends

In other words, what we do "depends" on what situation. And that's how it should be. We need some rules, we need to enforce them, but we also need exceptions...
--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)

March 23, 2009 at 9:06 pm

#964932
RE: SMO performance issues!

Heh... what??? You guys didn't know that "SMO" stood for "[font="Arial Black"]S[/font]low [font="Arial Black"]M[/font]oving [font="Arial Black"]O[/font]bjects"? 😛
--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)

March 23, 2009 at 8:58 pm

#964930
RE: Convert alphanumeric to BigInt

Mouli,
I believe this will solve your problem... no UDF overhead... no need for CROSS-APPLY. Just use the whole thing as another derived table. I suppose it could be...
--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)

March 23, 2009 at 8:52 pm

#964928
RE: Convert alphanumeric to BigInt

Sorry... went to edit and ended up double posting, instead... please see below in my next 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)

March 23, 2009 at 8:50 pm

#964927
RE: Convert alphanumeric to BigInt

Chirag (3/23/2009)
Have a look at this
declare @str varchar(100)
select @str = '1230-544,15C5487,132DE78'
;With Breakdown as
(
Select
SubString(@Str,1,1)[Chr],
1[Idx]
Union All
Select
SubString(@Str,Idx+1,1),
Idx+1
from Breakdown
where (Idx+1)<=Len(@Str)
)
select chr from Breakdown where isnumeric(chr) = 1
Edit:- This will return ',' and...
--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)

March 23, 2009 at 5:24 pm

#964899
RE: Extremely slow View - can I 'preprocess' the data?

Like derived tables and CTE's, every time you reference a view in the from clause of query causes the view to be "re-executed" (results materialized) and that's gonna make for...
--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)

March 23, 2009 at 5:20 pm

#964896
RE: How do i set identity column to zero?

polo_sport09 (3/22/2009)
I have drop all rows in my table... when i insert them back aggain the identity starts at 50 instead of 1.
i think i need to set identity 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)

March 23, 2009 at 5:10 pm

#964887

Viewing 15 posts - 44,461 through 44,475 (of 59,101 total)