Solved

Impact of views on performance

Posted on 2009-03-31
4
226 Views
Last Modified: 2012-05-06
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

 
0
Comment
Question by:cpeters5
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
udaya kumar laligondla earned 200 total points
ID: 24027933
View will not store any data. when ever you query the view it will fetch the data from table.
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 300 total points
ID: 24027977
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
 

Author Comment

by:cpeters5
ID: 24028026
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
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 300 total points
ID: 24028043
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question