I have a design and performance question. Considering a SQL Server 2008 database with 10 large tables (10 - 100 millions records). These tables are dimensional with a number of small dimension tables. Each table is largely denormalized for performance purposes.
Here is the situation, we plan to add a column to hold an encrypted key to each table. To make sure noone can access this key by any mean/ways they connect to the database. One way is to create a view over each table to hide the key and perhaps a few more sensitive columns and only allow read-only users to access the views instead of the tables directly.
The question is: How much resource used by each view? For instance, if a table is 20 GBs is size, would the view also occupy about 10 GBs in memory in general?