Prev: Full outer join on 3 tables. How to?
Next: How to update the Year in a date field?
From: tshad on 9 Mar 2010 13:04
If I have the following:

SELECT
UserID as RID
,EnvironmentID as EnvID
,@authorType as RType
,'A' as Type
,'Author' as FullType
,Case
When (isnull(FirstName, '') <> '' AND isnull(LastName, '') <> '') then
LastName + ', ' + FirstName
When (isnull(FirstName, '') <> '' AND isnull(LastName, '') = '') then
FirstName
When isnull(FirstName, '') = '' then LastName
End as RName
,Email as EmailAddress
FROM User
WHERE EnvironmentID = @EnvironmentID

Now I want to only have rows that have values that start with a letter.

Do I have to do this with a derived table, such as:

SELECT RID, EnvID, RType, Type, FullType, RName, Email
FROM (
SELECT
UserID as RID
,EnvironmentID as EnvID
,@authorType as RType
,'A' as Type
,'Author' as FullType
,Case
When (isnull(FirstName, '') <> '' AND isnull(LastName, '') <> '') then
LastName + ', ' + FirstName
When (isnull(FirstName, '') <> '' AND isnull(LastName, '') = '') then
FirstName
When isnull(FirstName, '') = '' then LastName
End as RName
,Email as EmailAddress
FROM User
WHERE EnvironmentID = @EnvironmentID
) as a
WHERE RName LIKE @NameFilter + '%'

I am concerned with the performance hit, since I am using a derived table
(select of a select) because this is actually 4 select statements unioned
together that use different tables based on the RType.

INSERT INTO TABLE
SELECT...
UNION
SELECT...
UNION
SELECT...
UNION
SELECT...

That would mean I would have to have 4 different sets of derived tables.

Is there a big hit using derived tables?

Thanks,

Tom


From: Plamen Ratchev on 9 Mar 2010 13:14
Yes, using derived table or CTE will allow you to use the predicate on the expression column. Derived tables/CTEs have
no direct effect on performance because they are expanded in the query plan.

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 9 Mar 2010 13:24
Great.

Thanks,

Tom

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:bOKdnSZTjbGPDQvWnZ2dnUVZ_sYyAAAA(a)speakeasy.net...
> Yes, using derived table or CTE will allow you to use the predicate on the
> expression column. Derived tables/CTEs have no direct effect on
> performance because they are expanded in the query plan.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


 | 
Pages: 1
Prev: Full outer join on 3 tables. How to?
Next: How to update the Year in a date field?