Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What a View


What a View

Author
Message
John Esraelo-498130
John Esraelo-498130
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 1030
Comments posted to this topic are about the item What a View

Cheers,
John Esraelo
Diogy
Diogy
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 29
I'm not sure of the purpose of building views out of main tables programmatically. Cool
Rune Bivrin
Rune Bivrin
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3770 Visits: 1484
And certainly not views that do SELECT *


Just because you're right doesn't mean everybody else is wrong.
John Esraelo-498130
John Esraelo-498130
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 1030
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
John Esraelo-498130
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 1030
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
John Esraelo-498130
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 1030
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
weirdbeard
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 6
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
Hans van Dam
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 172
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
simon.duckett
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 99
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
charles.byrne
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 172
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search