How to select true/false based on column value?

Question

I have a table with the following columns: EntityId, EntityName, EntityProfile, .................

I want to select the Id and Name and true/false column based on the value of entity profile, for example a returned result set like below, would mean that entities 1&2 have profiles while 3 not.

1 Name1 True
2 Name2 True
3 Name3 False
etc.....

I know I can do it using a function that return true/false based on the profile value like this: SELECT EntityId, EntityName, dbo.EntityHasProfile(EntityId) AS HasProfile FROM Entities

but I'm returning a large no. of records and with this function call for each record, the query is very slow, and when I remove the function call the query execution time drops significantly.

So is there another way of doing this? Thanks

Answer 1

You can try something like

SELECT  e.EntityId, 
        e.EntityName, 
        CASE 
            WHEN ep.EntityId IS NULL THEN 'False' 
            ELSE 'TRUE' 
        END AS HasProfile 
FROM    Entities e LEFT JOIN 
        EntityProfiles ep ON e.EntityID = ep.EntityID

Or

SELECT e.EntityId, 
        e.EntityName, 
        CASE 
            WHEN e.EntityProfile IS NULL THEN 'False' 
            ELSE 'TRUE' 
        END AS HasProfile 

FROM    Entities e

Answer 2

What does the UDF EntityHasProfile() do?

Typically you could do something like this with a LEFT JOIN:

SELECT EntityId, EntityName, CASE WHEN EntityProfileIs IS NULL THEN 0 ELSE 1 END AS Has Profile
FROM Entities
LEFT JOIN EntityProfiles
    ON EntityProfiles.EntityId = Entities.EntityId

This should eliminate a need for a costly scalar UDF call - in my experience, scalar UDFs should be a last resort for most database design problems in SQL Server - they are simply not good performers.

Answer 3

Use a CASE. I would post the specific code, but need more information than is supplied in the post - such as the data type of EntityProfile and what is usually stored in it. Something like:

CASE WHEN EntityProfile IS NULL THEN 'False' ELSE 'True' END

Edit - the entire SELECT statement, as per the info in the comments:

SELECT EntityID, EntityName, 
       CASE WHEN EntityProfile IS NULL THEN 'False' ELSE 'True' END AS HasProfile
FROM Entity

No LEFT JOIN necessary in this case...

Answer 4

If the way you determine whether or not an entity has a profile is a deterministic function, and doesn't require any access to another table, you could write a stored function and define a computed, persisted field which would store that value for you and not have to re-compute it over and over again.

If you need to query a separate table (to e.g. check the existance of a row), you could still make this "HasProfile" a column in your entity table and just compute that field on a regular basis, e.g. every night or so. If you have the value stored as an atomic value, you don't need the computation every time. This works as long as that fact - has a profile or not - doesn't change too frequently.

To add a column to check whether or not EntityProfile is empty, do something like this:

CREATE FUNCTION CheckHasProfile(@Field VARCHAR(MAX))
RETURNS BIT
WITH SCHEMABINDING
AS BEGIN
    DECLARE @Result BIT

    IF @Field IS NULL OR LEN(@Field) <= 0
        SET @Result = 0
    ELSE
        SET @Result = 1

    RETURN @Result
END

and then add a new computed column to your table Entity:

ALTER TABLE dbo.Entity
   ADD HasProfile AS dbo.CheckHasProfile(EntityProfile) PERSISTED

Now you have a BIT column and it's persisted, e.g. doesn't get computed every time to access the row, and should perform just fine!