Rip's Domain

Why are you people forgetting about VIEWS?

Posted in ColdFusion, SQL by rip747 on December 4, 2006

I don’t know why the fullasagoog list has turned into the SQL newsgroup lately but there are some good articles coming our way nonetheless.

The one thing that is bothering me though is that every single person out there is forgetting about views and their importance in database design.

Views are basically a query that act like a table. That’s the best part about them. Using views you can do insert, update, delete statements against them, include them in other views, trigger and stored procedures. Some database even allow you to have indexes against (MSSQL 2000 and up).

So why use them? Take this post about bad column names. Why subject yourself to remembering to enclose a column name with []. Why not just create a view for the table and name the column what you want. For instance, take this crappy query:

SELECT
[user name],
[password],
[first name],
[last name]
FROM Users

You usually see this when someone upgrade a MSAccess database to MSSQL. Instead of trying to remember the column names and remembering to enclose the column name that have spaces in them within [], just write a view for the table like so:

CREATE VIEW vwUsers
AS

SELECT
[pk_User] AS userid,
[user name] AS username,
[password] AS password,
[first name] AS firstname,
[last name] AS lastname
[active user] AS active
FROM Users

Now when you need to get a record from the user table, you can use the view and have the column name already to you liking. Plus you can perform all insert, update, and delete statements using the view instead of the underline table:

INSERT INTO vwUsers
(
userid,
username,
password,
firstname,
lastname,
active
)
VALUES
(
newid(),
‘rip’,
‘mypass’,
‘Tony’,
‘Petruzzi’,
1
)

Another cool thing is that you can use views to filter other views, so that you have to write less SQL code for you application. Take for instance the table above, let’s say it had 10,000 users in it and 5,000 of those users are inactive. You want to retrive all of the active users from this view. You could write another view to do this for you

CREATE VIEW vwUsersActive
AS

SELECT *
FROM vwUsers
WHERE active = 1

Now inside you CF application all you have to do is write a CFQUERY against the view to retrieve all the active user:

<cfquery name=”q” datasource=”#mydsn#”>
SELECT * FROM vwUsersActive
<cfquery

Obviously this is a very basic query and not al that pratical. However normally when writing an application you can get pretty complex with your queries and views are there to simply them.

4 Responses

Subscribe to comments with RSS.

  1. Marcos Placona said, on December 5, 2006 at 8:31 am

    I don’t know about SQL Version 2005, but untill the version 200 the views are very dodgy, SQL “understands” that when you use a view, it needs to query itself for ull results and then filter it based on your criteria.

    So imagine it’ll be doing something like:

    First attempt:
    Select * from table

    Results for the view:
    Select field1, field2, field3 from table where field1 = 1

    Bring the results…

    it’ll take a lot more to do that than a stored procedure would take.

    Cheers

  2. Ben Nadel said, on December 5, 2006 at 9:53 am

    When I first started doing database programming, I learned about Views and thought they were totally awesome. Why do a filtering query when I can compile a view to do it for me? As a result, my early applications had plenty of Views.

    After a while though, it started to drive me crazy. Every time I made a change to the database I had to go back in an recompile the views. I am sure there is a better way to do this, but in MS SQL Enterprise Manager I had to open the view, add a space (or make some text change) and then resave. And, WORST, if you had views that used other views, you had to compile from the group up. If you compiled one view that used another view THEN compiled the base view, the original view would break (since it was compiled out of order or something).

    Anyway, after a project or two like that I just gave up. I starting doing all my filtering directly in the query. I have actually found that this forces me to think my queries out more and make them even more efficient. Also, with views, I found myself trying to force my code to use them (even if the view returned 10 columns and all I needed was two). My code became slow and crappy.

    My non-view code might still be crappy😉 but it is faster and easier to maintain.

  3. Mikey said, on December 5, 2006 at 12:22 pm

    You can also set security levels in view which is mighty handy. My first job was with a DBA who’s motto was “No one touches the base tables except God and his DBA.” I still live by that.

    Stored procedures are also mighty handy. Why you would use sql to do basic sql (like insert a new user) when an SP is so much faster and you can rollback on error?

    When my views are slow (this only happened once when I had stupidly built a view on a view on a view * about 10 more levels….nuff said), I checked the execution plan and made sure I have appropriate indexes and PKs. Yeah that was slow, about 250ms, but still useable, as I have patient users.

  4. ameerkhan said, on May 8, 2007 at 10:50 am

    Views are a great way to
    1. Hide tables
    2. Create a lens for reporting [which may include formatting as well such column names]

    However, I would like to point out that not all views can have data inserted into them. Only Simple views support DML operations on them. Complex Views that have outer joins or display aggregated functions as columns do not support the trio INSERT/UPDATE/DELETE.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: