First, stop using SELECT * in your views. I talk about this quite a bit here:

• Bad habits to kick : using SELECT * / omitting the column list

Next, run sp_refreshview or sp_refreshsqlmodule against each view that references a table (or another view!) that you have changed, e.g.:

EXEC sp_refreshview N'dbo.viewname';

If you want to generate a script that refreshes all of the views in the system:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  EXEC sp_refreshsqlmodule N''' 
  + QUOTENAME(s.name) + '.' + QUOTENAME(v.name) + ''';'
FROM sys.schemas AS s 
INNER JOIN sys.views AS v 
ON s.[schema_id] = v.[schema_id]; 

EXEC sp_executesql @sql;

(You may want to run it twice in case there are circular or out-of-order dependencies.)

But this is a temporary fix. You will have to repeat this every time you change any table that might have views that reference it, then go find out which views are involved. This is not the right way to approach schema design. AT ALL. I wrote about a DDL trigger that would help automate this for you, but this is really the wrong approach - just stop using SELECT * in your views!

• Keeping sysdepends up to date in SQL Server 2008

You may also want to consider adding WITH SCHEMABINDING to the definition of all of your views. This does two things:

1. Prevents you from using SELECT * in the view. This is A GOOD THING™.
2. Prevents you from changing underlying tables without knowing exactly which views would be affected by the change. This is also A GOOD THING™.

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.

No, this isn't possible with a view. You could do something broadly along these lines with a multi-statement function (MSUDF), but:

1. This would still require a static schema definition for the table variable; and
2. Careful design would be required to avoid terrible performance; and
3. All potential base query predicates must be supplied as optional parameters

A multi-statement function would potentially materialize the entire 'view' result set in a table variable, before any predicates in the base query were applied. If the result is large, this overhead will likely be prohibitive.

You could push predicates into the MSUDF using parameters, but then the function body becomes a mess of conditional predicates of the form column = @value OR @value IS NULL, which would require OPTION (RECOMPILE) in the MSUDF to optimize well.

Note that even with the best possible outcome, the users of the view will have to change their queries from view references to MSUDF references (with all parameters specified, DEFAULT or NULL passed for those that are not needed). This may not be workable.

All that said, you need to be certain what is causing the performance problem in the first place. The question does not supply an example of a problematic query plan, so some of what follows is educated guesswork:

There are two features of the current scheme that leap out at me:

1. The use of ISNULL and calculated columns prevents the optimizer from pushing base-query predicates down into the underlying in-line functions and base tables. You might get better results for the ISNULL part by rewriting the LEFT JOIN as a UNION ALL of an inner join and an anti-semi-join of the two tables. Properly written, this would expose only base column names, allowing the optimizer to push predicates successfully.

2. The in-line functions use an MSUDF to split the supplied CSV into row values. This is a pattern that often causes optimization problems, because the size and distribution of the result is unknown. A query plan that is optimal for a CSV of '1,2,3' is likely not optimal for a NULL CSV. Again OPTION (RECOMPILE) could help with this. It will simplify the compiled logic for the NULL case, avoid parameter-sniffing issues, as well as providing cardinality information for the MSUDF result. Distribution statistics will still not be available though.