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 amp 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

User · Answer

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

User · Answer

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   lt   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

User · Answer

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

User · Answer

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

User · Answer

At least in Postgres you can use the following statement   SELECT EntityID  EntityName  EntityProfile IS NOT NULL AS HasProfile FROM Entity

User · Answer

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

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

Examples related to sql-server