Jeff Moden

Forum Replies Created

Viewing 15 posts - 39,466 through 39,480 (of 59,072 total)

RE: CLR, to be or not to be, that is the question!

Paul White (3/6/2010)
Hey Jeff,
There's a faster method than that in 2005 (and I don't mean CLR):
USE tempdb;
GO
IF OBJECT_ID(N'tempdb..#Binary', N'U')
...
--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 6, 2010 at 11:11 am

#1129651
RE: FizzBuzz

steve-893342 (3/6/2010)
Trillion Row Base Table - Pushing Multiple Unpivots to the Limit
So what do you think folks? Will this one go fast?
Sorry Jeff, no cross joins!
WITH cte1 AS...
--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 6, 2010 at 9:19 am

#1129635
RE: always use incorrect index to run the query by sql server

Colin-232036 (3/5/2010)
One table has many fields. And in the table,it has a clustered index on a fieldA, and a nonclustered index on a fieldB. I have a query...
--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 5, 2010 at 11:28 pm

#1129572
RE: impact of storing the null values in a table

dba-vb (3/5/2010)
hi,
thanks for ur response,
what is "three state logic"?
True, False, and "Unknown".
The real affect of having NULLs in a table is usually (not always) a thing called "Denormalization" 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 5, 2010 at 11:14 pm

#1129571
RE: Are the posted questions getting worse?

arun.sas (3/5/2010)
Hi Jeff,
You are good,
“You are not responsible for what people think about you,
But you are responsible for what you give them to think about you” – Stanley Ferrard.
Heh... thanks...
--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 5, 2010 at 10:59 pm

#1129570
RE: Very complex - How to count difference data

Thanks, Arun. You're a gentleman and a scholar especially for giving me the heads up on the OP's additional response.
--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 5, 2010 at 10:57 pm

#1129569
RE: FizzBuzz

steve-893342 (3/5/2010)
THE TRILLION
Well the TRILLION did run to completion OK. A little over 69 hours which ain't bad for a modest spec VM with only 2GB RAM. ...
--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 5, 2010 at 10:46 pm

#1129567
RE: Are the posted questions getting worse?

arun.sas (3/5/2010)
Hi Jeff,
Thanks,
Follow your suggestion.
Very glad to explained in both the versions.
😀
You bet, Arun. Thanks for the heads up on that 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 5, 2010 at 10:37 pm

#1129565
RE: Are the posted questions getting worse?

Lynn,
You have 9841 posts (which doesn't include QOD points, etc)... Only 159 more to be only the 4th person (not including Steve) in the history of SSC to break 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)

March 5, 2010 at 10:13 pm

#1129563
RE: Correlated Subquery with MIN()

lmu92 (3/5/2010)
If you'd run your subquery separately you'd see that it will return only one row.
Assuming RowNumber is a normal column the you simply could use the GROUP BY function....
--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 5, 2010 at 10:02 pm

#1129562
RE: Cross Apply, Help me ?

To learn more about how a Tally table can be used as a set based replacement for certain While Loops, please see the following article...
http://www.sqlservercentral.com/articles/T-SQL/62867/
--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 5, 2010 at 9:57 pm

#1129561
RE: Very complex - How to count difference data

On the outside chance that there's anyone out there that thinks this type of thing can't be done in SQL Server 2000...
SELECT original.Col1, original.Col2, original.Col3,
...
--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 5, 2010 at 9:48 pm

#1129559
RE: Very complex - How to count difference data

nguyennd (3/5/2010)
Thank Jeff Moden 😀
I were try this SQL but it complex, any one else?
SELECT
Col1,
Col2,
Col3,
Max(Rnk2) OVER (PARTITION BY Col1) as C_col21,
Max(Rnk3) OVER (PARTITION BY Col1) as C_col31
FROM
(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)

March 5, 2010 at 9:38 pm

#1129558
RE: Consulting needed

mike 57299 (3/5/2010)
Huntington Beach California USA
a.k.a. Surf City USA
😀
BWAA-HAA!!! I thought they passed a referendum so that it was against the law to have to work there. 😛
I'll...
--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 5, 2010 at 9:28 pm

#1129557
RE: Very complex - How to count difference data

Divide'n'Conquer...
WITH
cteCounts AS
(
SELECT Col1,
COUNT(DISTINCT Col2) AS C_col21,
COUNT(DISTINCT Col3) AS C_col31
...
--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 5, 2010 at 9:13 pm

#1129555

Viewing 15 posts - 39,466 through 39,480 (of 59,072 total)