What a View

  • John Esraelo-498130

    SSCertifiable

    Points: 5894

    Comments posted to this topic are about the item What a View

    Cheers,John Esraelo

  • Diogy

    SSC Journeyman

    Points: 97

    I'm not sure of the purpose of building views out of main tables programmatically. 😎

  • Rune Bivrin

    SSCertifiable

    Points: 7860

    And certainly not views that do [font="Courier New"]SELECT *[/font]


    Just because you're right doesn't mean everybody else is wrong.

  • John Esraelo-498130

    SSCertifiable

    Points: 5894

    This is a very good question.

    Just recently we have hired a company to create intelligent KPI (key performance indicators) set for us. In addition to the NDA (non-disclosure agreement), there are still some pieces of data that need to remain confidential especially when the data is not useful to the vendor(s).

    Therefore, to make the short story longer, I have created schemas and roles, removed some of the columns (after the creation of the views, like SSN). Thus, the programmer(s) in the new role and schema will not be able to see the table design, references, definitions, and various security objects. Of course you as an admin would be able to modify the settings to your needs.

    The new role and schema will let them create the new objects and execute the DBO objects but not modify nor view.

    Hope this helps.

    Cheers,John Esraelo

  • John Esraelo-498130

    SSCertifiable

    Points: 5894

    Actually the * is going to be replaced and will published shortly with selective columns.

    However, you can always modify the new views, add, remove calculated field or like I mentioned earlier remove certain data fields that do not wish to share with certain programmers or groups.

    Cheers,John Esraelo

  • John Esraelo-498130

    SSCertifiable

    Points: 5894

    This is just a training piece for those that are new in security of schema and role buildings. There is nothing wrong with the select * .. as long as you don't use the select * in your codes to write and update data. This is not a writing and updating piece.. this is just a method of keeping certain hands off of the data structure by creating an additional layer.

    In fact, professional contractors do prefer working with views than tables.

    Cheers,John Esraelo

  • weirdbeard

    SSC Rookie

    Points: 28

    I followed the link from the SqlErvercentral email, which described this article as

    "This article talks about a few issues with using views too extensively in your design."

    Which it clearly doesn't do, in fact it does the opposite.

  • Hans van Dam

    SSC-Addicted

    Points: 436

    Nice example for juniors, however i stronly agree with Rune Birvin about the select * in a view. If the underlying structure changes that could result in invalid or unpredictible results.

    Furthermore I suggest to always use the sysname type for SQL objects.

    Kind regards,

    Hans

  • simon.duckett

    SSC Enthusiast

    Points: 100

    My biggest peeve with SQL developers who publish articles (in general, not just this author) is the laziness shown by not matching the case of all object names.

    Please take the time to get the case right!

    Even if I am developing on a case-insensitive installation, I am meticulous in this regard. Code will transport to any installation.

  • charles.byrne

    SSC Veteran

    Points: 254

    Besides the .* issue which sounds like you plan on fixing. What was also odd was the naming prefix for the views being 'tbl_' for a view. When I see that prefix on a database object I think that it is a table not a view. If you were to prefix a view shouldn't you use 'vw_' or 'v_' or even 'qry_'?

    To follow your intent of a base view you could even prefix 'vw_base_' or something similar.

    If I see [Employee] and [tbl_Employee] in sql or code I would make the assumption that tbl_Employee is actually the table and Employee is a view.

    It would certainly make things less confusing for a DBA or a programmer not familiar with the database. Just another suggestion.

    "There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker

  • calvindlm

    Newbie

    Points: 3

    sp_MSforeachtable will loop through all the tables in a db

  • Charles Kincaid

    SSChampion

    Points: 13593

    Diogy (10/2/2012)


    I'm not sure of the purpose of building views out of main tables programmatically. 😎

    There are environments where direct access to tables by user application is prohibited but read only access through views is permitted. If your database has a few hundred tables then creating these manually can be a challenge.

    ATBCharles Kincaid

  • dan trate

    Old Hand

    Points: 352

    I agree with a lot already mentioned. One additional comment is that in my experience, a view created as "*" will need to be altered should the underlyinig table schema change. Consequently I would rather take the effort to have views that specify columns and calculated data in the column order that makes sense for the users or developers, rather than a view that is just a window into the table itself.

  • sshores

    Newbie

    Points: 3

    weirdbeard (10/2/2012)


    I followed the link from the SqlErvercentral email, which described this article as

    "This article talks about a few issues with using views too extensively in your design."

    Which it clearly doesn't do, in fact it does the opposite.

    I'm with weirdbeard, I was hoping for a discussion on view performance issues, as I use them extensively in some of my databases. The article itself is fine for what it is, but the email was misleading. 😛

  • Sean Lange

    SSC Guru

    Points: 286536

    calvindlm (10/2/2012)


    sp_MSforeachtable will loop through all the tables in a db

    Of course this is nothing more than a cursor. Take a look at the code.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 15 posts - 1 through 15 (of 32 total)

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