Jeff Moden

Forum Replies Created

Viewing 15 posts - 42,016 through 42,030 (of 59,098 total)

RE: query optimisation

ramuvanparti (9/9/2009)
select ltrim(rtrim(col1)) from tab
where col2 = 12
or
select col1 from tab
where col2 = 12
clustered index is present on col2 and non-clustered on col1
which query is more optimal.
I'd recommend...
--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)

September 9, 2009 at 10:27 pm

#1051554
RE: How can I count any character in a column?

Tejas Shah (9/9/2009)
Hi,
You can achieve the same by this query.
DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(100))
INSERT INTO @t(data) SELECT 'tejas@tejas.com;tejas@shah.com;tejas@SQLY@oga.com'
INSERT INTO @t(data) SELECT 'tejas@tejas.com;tejas@shah.com;'
;WITH CTE AS(
SELECT ID,
SUBSTRING(data,0, LEN(data)+1)...
--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)

September 9, 2009 at 10:14 pm

#1051552
RE: how to cut the data with based on comma

dastagirid (9/9/2009)
i have a table like
this is users table
id 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)

September 9, 2009 at 10:11 pm

#1051551
RE: Can I use a cursor Inside of a Function

Then you've done something wrong with the idea. Post the code and let's have a peek.
--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)

September 9, 2009 at 9:40 pm

#1051547
RE: SQL SubQuery Help

Heh... I figured. If it helps, I almost did the same thing until I realized there was no correlation between the sub-queries.
--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)

September 9, 2009 at 9:31 pm

#1051545
RE: Pivot table

Sure... but, again, I wouldn't use PIVOT to do this. I would use pre-aggregated cross-tabs, instead. And the following article explains how to make them dynamically...
http://www.sqlservercentral.com/articles/Crosstab/65048/
My apologies 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)

September 9, 2009 at 9:29 pm

#1051543
RE: computed column from date range

Seth is spot on... here's an article that explains the performance advantage of the method he used...
http://www.sqlservercentral.com/articles/T-SQL/63681/
--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)

September 9, 2009 at 8:56 pm

#1051534
RE: SQL SubQuery Help

Like this...
SELECT e.*
FROM dbo.Employees e
INNER JOIN dbo.Students s
ON e.Name = s.Name
AND e.Age =...
--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)

September 9, 2009 at 8:52 pm

#1051532
RE: SQL SubQuery Help

The current query (either one) will return all employees who's name and age are in the student table.... they just might not be on the same row, though because there...
--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)

September 9, 2009 at 8:47 pm

#1051531
RE: Pivot table

The PIVOT code in the following article will do it...
http://www.sqlservercentral.com/articles/T-SQL/63681/
... and, with any luck at all, it will convince you to never use PIVOT to do such a thing.
--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)

September 9, 2009 at 8:33 pm

#1051530
RE: Reasons to use PRIMARY KEY?

Ummm... I really want my doctor to know the differerence between a transverse colon and a descending colon even though they're both just a part of the same large intestine...
--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)

September 9, 2009 at 8:19 pm

#1051527
RE: Currency Format

Ah dang... thanks for the feedback, Yumi. I've gotta remember to read the name of the forum I'm trying to answer the question on. I was thinking pure...
--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)

September 9, 2009 at 8:04 pm

#1051524
RE: Are the posted questions getting worse?

Grant Fritchey (9/9/2009)
WHOOP!
I'm sitting the same room as Jeff Moden. My IQ has gone up. Who needs a problem sovled?
:blush: Heh... that's odd. I was going to say...
--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)

September 9, 2009 at 7:53 pm

#1051521
RE: Performance issue with tally solution

Eggsactly...
--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)

September 9, 2009 at 1:06 pm

#1051376
RE: Using the Best

Actually, we've gotten a bit off track. I don't mind people asking questions on forums... in fact, I recommend it because there are so many great folks out there...
--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)

September 9, 2009 at 1:02 pm

#1051372

Viewing 15 posts - 42,016 through 42,030 (of 59,098 total)