SQL 2008 R2 Views

Posted on 2013-10-25
Medium Priority
Last Modified: 2013-11-04
If I have a table with 25 fields, 350,000 records and i only want to process the records that have a 5 in the field called Number, what is best to do:

Build a view over the table only selecting the 5 records  ?
Build a index over table (indexing Number field) ?  
Something else...

Other reports use the table and need to see the 5 records.
Question by:thayduck
LVL 23

Expert Comment

by:Steve Wales
ID: 39600744
An index is probably your best bet.

If you create a view, it won't make the select any better - it would perform the query using the same access path to the base table (providing there were no other filtering predicates or what not).

If you want to make the reporting easier so they don't always have to code WHERE NUMBER = 5, then you could present them with a view just showing that data.

But the index would seem to be the most efficient way of getting to the data based on what you've just told us.
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 99 total points
ID: 39600746
Define 'process'.  Does this mean just select those rows, or edit them in some fashion?

An index isn't going to work when there's an expression such as LEFT(Number, 5).

>Build a view over the table only selecting the 5 records  ?
This works.  Also works would be a stored proc that does this.

Also, is the 'Number' field numeric or text?

Author Comment

ID: 39600822
Lets just say the field is  5 (Char).

The field contains either the word YES or NO.

I only want to process the YES records in my SSRS report

Right now in my stored procedure I compare this field to a report parm.

They can either choose YES or NO.

I figured a view only selecting the YES records would speed things up.

Since there are only 100,000 YES records and 250,000 NO records I do not want to have to process 350,000 records to get the 100,000 YES records..
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

LVL 23

Assisted Solution

by:Steve Wales
Steve Wales earned 99 total points
ID: 39600847
If you're just comparing text and not using any functions on the left of the where (which invalidates use of an index anyway), run a couple of tests and check your execution plans for each.

Run the query with no index, compare execution time and search method
Run the query with an index, compare execution time and search method.

It's possible, based upon the way the data sits in the table that the optimizer may decide that it is more efficient to scan the entire table anyway, if the distribution of your YES/NO's are scattered all through the table.

But you'll need to look at the plans to see what it's going to do.
LVL 49

Accepted Solution

PortletPaul earned 102 total points
ID: 39604840
>>I figured a view only selecting the YES records would speed things up.

By itself, probably not. A view is a stored query, and while they don't need to be re-evaluated by the optimizer to determine an execution plan, they still perform close to the same work as an interactive query, e.g.

create my_view as select * from x where field_y = 'YES'; -- scans 350k rows : 100k output

from my_view; -- 100k rows to process

----------------- is very similar to:

from (
         select *
         where field_y = 'YES'; -- scans 350k rows : 100k output
        ) as my_nested_query -- 100k rows to process


i.e not a lot of difference but there might be a small gain.
Whereas an index can make a difference to "how" the 350k records get scanned
(but it can't be guaranteed as noted by comments made previously).

Is this YES/NO field the only thing that you need to filter by?

btw: this is NOT a criticism of views in general! They can be extremely useful.

Author Comment

ID: 39605370
I was just using the Yes or No field as an example.

From every ones comments, a view in this situation will not gain me much.

I do have index's built over the main fields I am selecting on, so I suppose that will be sufficient enough.

I will close this question and award points to all that have helped unless there is more input.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore 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.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

600 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