[sql-server] How to select true/false based on column value?

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

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!

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.

Maybe too late, but I'd cast 0/1 as bit to make the datatype eventually becomes True/False when consumed by .NET framework:

SELECT   EntityId, 
         EntityName, 
         CASE 
            WHEN EntityProfileIs IS NULL 
            THEN CAST(0 as bit) 
            ELSE CAST(1 as bit) END AS HasProfile
FROM      Entities
LEFT JOIN EntityProfiles ON EntityProfiles.EntityId = Entities.EntityId`

At least in Postgres you can use the following statement:

SELECT EntityID, EntityName, EntityProfile IS NOT NULL AS HasProfile FROM Entity

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