Some Views Cannot be Matertialized

In the past weeks, I was asked to work on improving SQL server performance for a client application.  Basically, the application have some SQL template settings for retrieving data from SQL server databases.  I did this kind job last year. What I did was using materialized views, i.e., views cache static data if they are not changed.

However, this time I could not make my updated views as materialized ones. After further analysis, I realize that this is not SQL's fault. If a view has some dynamic components, such as columns, the view just cannot cache data. In this sense, the view cannot be materialized.

For the following SQL SELECT is one example:

SELECT ID, CAST(@StartDate + ' ' + @StartTime AS DATETIME) AS DT, VALUE
FROM MyTable;

Since the column DT is dynamically built by two variables, SQL server just cannot cache data.

My assignment actually is much more complicated than this case. The strategy I worked out was to stand back first. I tried to understand the application SQL templates. What kind of data are queried? Based on my understanding, I tried to create a set of database tables to meet the requirement.  Then I added triggers to source data tables to populate data from the source to the new tables. As a result, the views as SQL templates were much easy to build.

What I learned from this project is that you cannot always implement one strategy to solve all the problems, even though the implementation strategy has been successfully used before. If it is too hard to use the same strategy, you have to stand back, re-think the issue in a wide prospective view, and try to take alternative path. This may actually lead to find another good solution.