How to create an additional boolean column in a view whose value depends on a subquery?

Following is a view that I have created

CREATE VIEW [ForMasterTable] 
WITH SCHEMABINDING AS
SELECT
[MasterID],
[MasterColumnData],
[DetailID],
[DetailColumnData],
[SomeOtherID],
[SomeOtherColumnData]

FROM [MasterTable]
LEFT OUTER JOIN [DetailTable] ON [MasterTable].[DetailID]= [DetailTable].[DetailID]
LEFT OUTER JOIN [SomeOtherTable] ON [MasterTable].[SomeOtherID]= [SomeOtherTable].[SomeOtherID]

I want to add an additional boolean column to this view the value of which will depend on the result of a condition on a subquery

CASE 
WHEN EXISTS (
     SELECT ErrorID
     FROM [ErrorTable]
          WHERE DetailID IN 
                (SELECT [DetailID]
                 FROM [DetailTable]
                      WHERE MasterID = [MasterTable].[MasterID])  
            )
THEN
     1
ELSE
     0
END

How do I something like this in my view? Also I am assuming that a nested query would exponentially degrade the performance as the number of rows go up. Any help with this would be greatly appreciated.

The most important thing is that you don't need to assume that performance will get worse. SQL server can rewrite some subqueries to avoid the kind of row by row operation that I think you're assuming will happen. It's certainly possible that your subquery will slow down the view quite a bit but you can test this yourself. In fact, you can test this much more effectively than us because we have no idea about your table structures, indexes, statistics, size of the tables, etc.

Run queries that use the view with and without the bit column. Does the query with the new column get exponentially slower? Get an actual execution plan and analyze that. Run your queries after SET STATISTICS IO, TIME ON and see how IO, CPU time, and elapsed time change with different queries.

I'll give you one way to rewrite that query without a subquery. I'm assuming that [MasterID] is unique for [MasterTable].

Let's work through the logic of your subquery. One way to express it is you want to start with [MasterTable].[MasterID] and get all of the associated [DetailID] values from [DetailTable]. If there is at least one row in [ErrorTable] with a matching DetailID then the bit column has a value of 1. Otherwise it has a value of 0.

You can rewrite that to be a derived table. If the derived table has all of the distinct [MasterID] that have a corresponding entry in [ErrorTable] then you can do a LEFT OUTER JOIN to it. If the join column is not NULL then there was a match so your bit column has a value of 1. Otherwise it has a value of 0.

Here is one implementation:

SELECT
[MasterID],
[MasterColumnData],
[DetailID],
[DetailColumnData],
[SomeOtherID],
[SomeOtherColumnData],
CASE WHEN bool.[MasterID] IS NOT NULL THEN 1 ELSE 0 END
FROM [MasterTable]
LEFT OUTER JOIN [DetailTable] ON [MasterTable].[DetailID]= [DetailTable].[DetailID]
LEFT OUTER JOIN [SomeOtherTable] ON [MasterTable].[SomeOtherID]= [SomeOtherTable].[SomeOtherID]
LEFT OUTER JOIN 
(
    SELECT DISTINCT dt.[MasterID]
    FROM [DetailTable] dt
    WHERE EXISTS
    (
        SELECT 1
        FROM [ErrorTable] et
        WHERE dt.DetailID = et.DetailID
    )
) bool ON [MasterTable].[MasterID] = bool.[MasterID];

StackExchange Database Administrators Q#163104, answer score: 2