disadvantage of views? 1

[sds814]

There was a production change that changed a field from char(2) to char(3). I didn't realize that this field is being used by views. When the view was called by a stored proc, an error came up saying that "The (maximum) expected data length is 2, while the returned data length is 3.". So since views are static and need to be recompiled is this a disadvantage of using a view? Could an alternative be to use functions?

Thanks for the help!

 

[flapeyre]

In my opinion, the one-time inconvenience of having to fix the view is better than the performance hit from a function.

I use functions sparingly, and try not to use any tables in them, if at all possible.

-- Francis
Francisus ego, sed non sum papa.

 

[SQLScholar]

Instead of a view or indeed a function you may be better off with a procedure.

Procedures can be very useful as they can be good for plan caching

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

There is a system function that you can use to refresh the meta data associated with views.

http://msdn.microsoft.com/en-us/library/aa238895(v=sql.80).aspx

[tt]sp_refreshView NameOfViewHere[/tt]

Personally, I like views, but I don't go crazy over them either. For example, I have a relatively complicated view that returns important information that is used by several (similar) stored procedures. The code behind the view is relatively complicated so I wouldn't want to use the base code in a lot of procedures because if a mistake is discovered in the code, many stored procedures would need to be updated instead of just one view.

Like anything else, views can be over-used and abused. In my opinion, the biggest problem with views occurs when you start nesting views within views within views, etc... This can cause rather dramatic performance problems.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sds814]

Thanks everyone!

So is a view a virtual table that lies in memory? And therefore the advantage of a function? Can a view be indexed?

gmmastros, thanks for the system sp. Never thought of doing nested views.

 

[gmmastros]

In some respects, you can think of a view as a virtual table, but it is not *necessarily* stored in memory.

In most situations, views are really nothing more that a query that is stored.

You can index a view, but in order to do this, you need to use schema-binding. Basically, schema-binding is will prevent you from changing the underlying structure of the tables until you drop the index on the view and then drop the view.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sds814]

if a view isn't stored in memory then is its performance better than a function?

 

[gmmastros]

Often times, views have better performance than functions. In fact, the only time a function performs better than a view is when you write an in-line function. Functions will often calculate row-by-row instead of in a set-based manner.

Here's what I usually do....

95% of my database interaction (front end to DB) occurs through stored procedures. I only use views when a query is re-usable and sufficiently complex that I don't want to repeat the logic all over the place. I have the same rules for functions... they must be re-usable and sufficiently complex that I don't want to repeat the code for calculations in many procedures.

This is a break down on the objects in my database.

Object type                     Count    Percent
--------------------------      ------   -------
SQL_STORED_PROCEDURE	         2681       69.3
USER_TABLE	                  264        6.8
PRIMARY_KEY_CONSTRAINT	          231        5.9
FOREIGN_KEY_CONSTRAINT	          150        3.8
SQL_SCALAR_FUNCTION	          142        3.7
DEFAULT_CONSTRAINT	          109        2.8
SQL_TRIGGER	                   74        1.9
VIEW	                           66        1.7
CHECK_CONSTRAINT	           51        1.3
SQL_TABLE_VALUED_FUNCTION          42        1.1
UNIQUE_CONSTRAINT                  41        1.1
SYNONYM	                           13        0.3
SQL_INLINE_TABLE_VALUED_FUNCTION    2        0.1

Nearly 70% of my objects are stored procedures. When you only consider code, that number would increase significantly.

I should also mention that I have a fair amount of "dead code" in my database since I have been developing in this database for over 10 years and 8 versions of my application.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sds814]

Thanks George. Sorry I'm a still little confused on how views work behind the scenes. Is the view (query) stored with the execution plan? And therfore the performance save?

 

[gmmastros]

No. The query plan is not stored along with the view. The query plan is stored for Procedures and functions, but not for views.

This can be good, and this can be bad. For example, if you are joining 10 tables in a view, but the query you write against the view only uses 3 tables, then the execution plan will ignore the 7 unused tables, allowing the query to run faster.

Really... there is no performance advantage of views. Sometimes you will see a slight performance gain, but this has more to do with data caching than anything else. The way I see it, the ONLY benefit of a view is to hide rather complex queries that can be re-used by several (or many) other queries.

I'll give you an example from my own application. My application optimizes school bus routes to minimize number of buses, times, and miles. This is a lot more complicated than most people give it credit for. Anyway... I have many customers, each with their own reporting requirements. You can have different routes depending on the weekday. Some students transfer buses to get to/from school. Some students attend 1/2 day at one school and another 1/2 day at another school. There are about a dozen normalized tables to store all of this data. I created a view named ReportStudent that flattens all the data and returns about 200 columns (pickup bus, pickup location, pickup time pickup driver, dropoff stuff, each for different days of the week, etc...).

This view is extremely complicated and joins/self joins about 50 tables (at least). It is rather slow to run the view when returning all of the data (about 30 seconds for most customers). The advantage is that they can then create their own reports from this data, to do whatever they want.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

The basic purpose of a view is to 'hide' information. Let's say you have this table structure:

Lname Fname CreditCard DateOfBirth StreetAddress City State ZipCode

Now anyone who has access to query the table can see and query those columns. But let's say you have users who shouldn't see the CreditCard column; you can create a view and give them access to that. The view would be:

SELECT Lname, Fname, DateOfBirth, StreetAddress, City, State, Zipcode
FROM tablea

then your user could only see/query those columns and wouldn't be aware of the CreditCard column. You can also use views to alias column names...if a column name would give away proprietary information, you can set up a view where the column has a different name.

Those are just the basic reasons for using a view.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[sds814]

Makes sense. Thanks George and Bill!

Very cool app, George! Outside of topic, but do you use the Dijkstra algorithm to optimize the bus route?

 

[gmmastros]

do you use the Dijkstra algorithm to optimize the bus route?

Yes, but heavily modified to accommodate various requirements like speed limits by time of day and turn restrictions by vehicle size.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom