order by case value

[tshad]

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

[Plamen Ratchev]

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

[tshad]

Great.

Thanks,

Tom

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:bOKdnSZTjbGPDQvW...@speakeasy.net...