Sql-server – Programmatic way to rewrite a query with the view definitions as sub query

Short answer: you'll have to do your own dirty work. Like @mrdenny says, there is no way of automating this task.

Very long answer: SQL Server doesn't have an easy way of determining unique columns in a view, in part because of how complex (and dynamic) views can be. There are two methods to make a first approximation, however, and these methods can work in concert. The first is what you already have: performing queries against the view to see if you can find anything which already violates uniqueness constraints. If it does, you can throw that column (or set of columns) out immediately. If it doesn't, that combination might be a valid unique key, but there is no guarantee.

The second method is to reason from the data model, starting with unique constraints (assuming you have those on your tables!). There are three nice ways that you can have enforced uniqueness on a table: a primary key, a unique key constraint, or a unique index. All three of them show up in the sys.indexes system table and have the is_unique property set to 1. There are also some not-so-nice ways like using triggers to enforce uniqueness.

You could try messing around with sys.dm_sql_referenced_entities and a query like this might be helpful to give you a starting point:

declare @ViewName sysname = 'MYVIEW';

with viewcolumns as
(
    select * from sys.dm_sql_referenced_entities(@ViewName, 'OBJECT') dsre /* where is_selected = 1   --uncomment if using SQL 2012 */
),
uniquereferences as
(
    select
        i.object_id,
        object_schema_name(i.object_id) as SchemaName,
        object_name(i.object_id) as TableName,
        i.name as IndexName,
        c.name as ColumnName,
        case when vc.referenced_entity_name IS NOT NULL then 1 else 0 end as HasReference
    from
        sys.indexes i
        inner join sys.index_columns ic 
            on i.index_id = ic.index_id
            and i.object_id = ic.object_id
        inner join sys.columns c
            on c.column_id = ic.column_id
            and c.object_id = ic.object_id
        left outer join viewcolumns vc
            on vc.referenced_id = c.object_id
            and vc.referenced_minor_id = c.column_id
    where
        i.is_unique = 1
),
sufficientreferences as
(
    select
        object_id,
        IndexName,
        min(HasReference) as HasReference
    from
        uniquereferences
    group by
        object_id,
        IndexName
    having
        min(HasReference) = 1
)
select
    ur.object_id,
    ur.SchemaName,
    ur.TableName,
    ur.IndexName,
    ur.ColumnName
from
    uniquereferences ur
    inner join sufficientreferences sr 
        on ur.IndexName = sr.IndexName
        and ur.object_id = sr.object_id
where
    ur.HasReference = 1
    /* Optional:  remove referenced tables; if you have a 1:1 reference, leave this bit out */
    and not exists
    (
        select
            *
        from
            sys.foreign_keys fk
            inner join viewcolumns vc 
                on fk.parent_object_id = vc.referenced_id
        where
            vc.referenced_minor_id = 0
            and fk.referenced_object_id = ur.object_id

    );

For SQL 2008, the is_selected flag is not available, so there's no way to tell if the column returned by that function is actually part of the SELECT clause or if it is is used in a join or filter. With SQL 2012, you could at least limit your query to the columns that actually are part of the SELECT clause.

What you get from this is not a set of unique keys for the view. What you get is a set of columns which make up unique keys on their underlying tables. The difference is that you could have a reference table with a unique key constraint on the Name column, and that Name column would show up in the above query even if the view joins the reference table to the base table (thereby causing repeated use of the reference table's Name column). To help alleviate that, I have a NOT EXISTS clause which removes cases in which the object is the referenced table in a foreign key relationship with another table in the view, so our unique index for the reference data table should not show up.

What this does allow you to do is reduce your possible answer space. But even then, you'll be doing a lot of spadework. The more complex your views get, the less valuable this is. For example, if you have a UNION ALL in your query, the statement above might show you a candidate column set which is wrong, because those columns might be duplicated in the other half of the UNION ALL. Or if you have cross-server queries, sys.dm_sql_referenced_entities might not even show you any column names. In other words, the query above is a semi-functional aid and certainly not a method of automating the process.

Note that this does depend upon having unique constraints specified. If your only unique keys are surrogate primary keys, it might not be quite as easy to find a candidate column set because not even SQL Server knows that the column combination is supposed to be unique.

a couple of questions, as my rep is too low to post them via comments

1. What are you trying to do with the output and to what extent can you change the data model?

   • If both the table layout and output has to be exactly as in your example, then I don't see any major improvements. Outer join + isnull isn't too bad.

     • To improve performance, you could:

       • ensure there are indexes on the keys you're joining on
       • start using synthetic keys for plans (should be better then a varchar)

     • If changing the DDL or data is ok, you can also:

       • create a dedicated table to contain the default values (with no field for tariff_plan). let's call this one the "default_tariff" table and the other one "specific_tariff_types"

2. About the last requirement, I'm guessing this means its possible to have tariff plans for which no default exists? A full outer join should work too.

3. I guess you're using a TVF to sidestep the headaches of putting this in a normal view ? I have a feeling it should be possible to use a normal view without performance penalties, though the query would become more complex.

here is a proposal:

CREATE FUNCTION get_tariffplan_for
(   
    @customerplan as varchar(50)
)
RETURNS TABLE 
AS RETURN 
(
    select p.tariff_type,
        ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo, 
        ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,
        ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,
        CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback
    from default_tariff_values as p
    full outer join specific_tariff_values s
        on (p.tariff_type = s.tariff_type) and (s.tariff_plan = @customerplan)
);

edit: here is a proposal (without changing the data model)

CREATE FUNCTION get_tariffplan_for
(   
    @customerplan as varchar(50)
)
RETURNS TABLE 
AS RETURN 
(
    select p.tariff_type,
        ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo, 
        ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,
        ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,
        CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback,
        CASE WHEN p.id IS NULL then 1 else 0 END as isexclusive
    from 
    (select tariff_type,tariff_foo,tariff_bar,tariff_plan 
    from tariff_values where tariff_plan = 'default') 
    as p
    full outer join 
        (select tariff_type,tariff_foo,tariff_bar,tariff_plan 
        from tariff_values where tariff_plan = @customerplan) 
        as s
        on (p.tariff_type = s.tariff_type)
);