Querying all records are true in sql-server - is casting expensive performance wise

sql-server, t-sql

Solution

I would do this with an exists statement as it will jump out of the query from the moment it finds 1 record where `IsCapped = 0` where as your query will always read all data.

CREATE FUNCTION dbo.fn_are_contracts_capped(@ContractVersionId int)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @return_value bit

    IF EXISTS(
      SELECT 1 
        FROM dbo.ContractCover cc
        JOIN dbo.ContractRiskVersion crv 
          ON cc.ContractRiskId = crv.ContractRiskId
       WHERE crv.ContractVersionId = @ContractVersionId
         AND cc.IsActive = 1
         AND IsCapped = 0)
      BEGIN
        SET @return_value = 0
      END
    ELSE
      BEGIN
        SET @return_value = 1
      END

    RETURN @return_value
  END

Compared to the IO required to read the data, the cast will not add a lot of overhead.

Edit: wrapped code in a scalar function.

Problem

I have a table with a column of bit values. I want to write a function that returns true if all records of an associated item are true. One way I found of doing it is: ``` Select @Ret = CAST(MIN(CAST(IsCapped as tinyInt)) As Bit) from ContractCover cc Inner join ContractRiskVersion crv on cc.ContractRiskId = crv.ContractRiskId WHERE crv.ContractVersionId = @ContractVersionId AND cc.IsActive = 1 return @ret ``` But is the casting to int to get the minimum expensive? Should I instead just be querying based on say: (count(Id) where IsCapped = 0 > 0) returning false rather than doing the multiple casts? In the execution plan it doesn't seem like calling this function is heavy in the execution (but I'm not too familiar with analysing query plans - it just seems to have the same % cost as another section of the stored proc of like 2%). Edit - when I execute the stored proc which calls the function and look at the execution plan - the part where it calls the function has a query cost (relative to the batch) : 1% which is comparable to other sections of the stored proc. Unless I'm looking at the wrong thing :) Thanks!!

Original source