Impact of views on performance

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?
pax

 
cpeters5Asked:
Who is Participating?
 
udaya kumar laligondlaConnect With a Mentor Technical LeadCommented:
View will not store any data. when ever you query the view it will fetch the data from table.
0
 
chapmandewConnect With a Mentor Commented:
Right...views are just a "view of the data"...not the data itself (unless the view is indexed, but that is a different story).  You can certainly use a view for this purpose.  Another way is to disallow the viewing of that column's data through permissions.  Personally, I would use the views.
0
 
cpeters5Author Commented:
Thanks for the quick response.  I am not familiar with database theory that much coming from different background.  What I understand, a view is sort of a persistent query.  So while database is online, each view would occupy a chumk of memory.  Is this a correct understanding?  And if so, how big is that memory relative to the size of the query that defines the view?
0
 
chapmandewConnect With a Mentor Commented:
That is not a correct understanding.  A view is a stored query...but stored in a sense that it is ready to run...it is a definition of a way to pull data, not the actual data itself.  Does that make sense?  for example, this does NOT store any data.

create view viewname
as
select * from tablename

0
All Courses

From novice to tech pro — start learning today.