Post reply

views

  • May 11, 2009 at 8:49 pm

    #204776

    i would like to know how many views can be created for a table and can we create views for system tables

  • May 11, 2009 at 9:34 pm

    #992612

    there is no limit to the number of views you can create, even if they reference the same object over and over.

    you can create views against system tables, but they will not allow updates like other updatable views...

    try it yourself:

    create view MyView As Select * from sysobjects where xtype='U'

    no problem creating or selecting from the view.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 12, 2009 at 6:31 am

    #992793

    I agree 100% with what Lowell has said.

    I will add some caveats though. Be careful about nesting views. It can lead to all kinds of performance issues as well as problems with documentation and troubleshooting. I've outlined one of the performance issues in this blog post[/url]. Also, I'd be very cautious about referencing system tables at all. Microsoft is moving away from letting us access them like it used to. Even now what we're seeing are views, not the tables themselves. Instead of reading directly from system tables (except the information_schema ones) try using the dynamican management views and functions. They provide more information in better formats than the system tables do anyway.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 12, 2009 at 6:56 am

    #992835

    Thanks a lot ppl 🙂 for clarifying my doubt.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply