October 1, 2012 at 9:54 pm
Comments posted to this topic are about the item What a View
Cheers,
John Esraelo
October 2, 2012 at 12:29 am
I'm not sure of the purpose of building views out of main tables programmatically. 😎
October 2, 2012 at 12:36 am
And certainly not views that do [font="Courier New"]SELECT *[/font]
October 2, 2012 at 12:38 am
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
October 2, 2012 at 12:40 am
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
October 2, 2012 at 12:46 am
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
October 2, 2012 at 12:51 am
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.
October 2, 2012 at 12:56 am
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
October 2, 2012 at 1:28 am
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.
October 2, 2012 at 6:17 am
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
October 2, 2012 at 6:37 am
sp_MSforeachtable will loop through all the tables in a db
October 2, 2012 at 7:22 am
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
October 2, 2012 at 7:28 am
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.
October 2, 2012 at 7:36 am
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. 😛
October 2, 2012 at 8:01 am
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 31 total)
You must be logged in to reply to this topic. Login to reply