I have an application that uses T-SQL views. A number of these views are quite complex, joining data from many tables. To avoid column-name collisions, the views often employ table-scalar functions to return table data under a modified/prefixed schema.
For example, I have two tables (parent
ACTION table and child
ACTIONSUB table), both with many columns, and both with identical columns names --
RowGUID UID Ref Name LastUpdate etc...
RowGUID UID fkUID Ref Name LastUpdate etc...
As part of our build process, we regenerate our library of table-access functions, one function per table. These functions can accept a delimited list of
UID values, or will return all rows if passed
NULL. As stated above, these functions' main purpose is to return all column data from the table with a modified/pre-fixed schema, so that we can
JOIN them together into a single View. So, using our table-access functions, our tables above would return --
AC_RowGUID AC_UID AC_Ref AC_Name etc...
ACSUB_RowGUID ACSUB_UID ACSUB_fkUID ACSUB_Ref ACSUB_Name etc...
This approach works, until we have very large tables and/or join data from many tables (functions); then our View performance can seriously degrade.
Our views are simple query statements, usually with calculated columns added to the select list. A simple example --
CREATE VIEW vw_ActionSub2Action_01 AS SELECT acsub.*, ac.*, IsNull(ac.AC_Date1, ac.AC_Date2) AS [AC_ComboDate], ac.AC_Ref +'.'+ acsub.ACSUB_Ref AS [ComboRef], etc... FROM fn_TAF_ACTIONSUB(null) acsub LEFT JOIN fn_TAF_ACTION(null) ac ON acsub.ACSUB_fkUID = ac.AC_UID
I'm wondering if these Views could be re-written using CTEs, to somehow 'pre-fetch' the modified/pre-fixed schemas using the functions, and then query the tables with
SELECT INTO to populate data into the View.
Is it possible?
Something like --
-- first, setup View schema using TAF functions to return empty data set SELECT acsub.*, ac.*, '' AS [AC_ComboDate], '' AS [ComboRef], etc... FROM fn_TAF_ACTIONSUB(0) acsub LEFT JOIN fn_TAF_ACTION(0) ac ON acsub.ACSUB_fkUID = ac.AC_UID -- then, populate the View by querying the tables SELECT acsub.*, ac.*, IsNull(ac.Date1, ac.Date2) AS [AC_ComboDate], ac.Ref +'.'+ acsub.Ref AS [ComboRef], etc... FROM ACTIONSUB acsub LEFT JOIN ACTION ac ON acsub.fkUID = ac.UID
Is there a way to combine the above process into a single view definition?
I realize our above use of Views may be inherently non-performant; however, our system uses these Views, and performance is reasonable in most cases, so pushing a 're-design' of our processing logic is not feasible.
Our Table-Access-Functions (TAFs) are Table-valued Functions that return a table with a modified schema. They do not use BEGIN...END blocks. Here is an example...
CREATE FUNCTION [dbo].[fn_TAF_ACTION] (@UID varchar(max)) RETURNS TABLE AS RETURN (SELECT AC_RowGUID = RowGUID, AC_UID = UID, AC_Ref = Ref, AC_Name = Name, ...etc... FROM Action WHERE UID IN (SELECT IntValue FROM dbo.CsvToInt(@UID)) OR @UID IS NULL) -- CsvToInt function simply allows us to pass a string of comma-delimited UIDs