SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Views for Abstraction


Views for Abstraction

Author
Message
Andy Warren
Andy Warren
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Moderators
Points: 40400 Visits: 2770
Comments posted to this topic are about the item Views for Abstraction

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7155 Visits: 3889
Hi Andy,

thanks for this nice article.

Just as additional information, a link to BOL which explains when views are updatable and when not.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/aecc2f73-2ab5-4db9-b1e6-2f9e3c601fb9.htm

Best Regards,

Chris Büttner
bur
bur
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 4
Hi Andy,

With reference to your example of using tableA and tableB and a view to do a mass truncate and insert whilst keeping the data available for access. Would snapshot isolation which is available from SQL 2005 not be a neater alternative?

Kind regards,

Will - BuR
Andy Warren
Andy Warren
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Moderators
Points: 40400 Visits: 2770
That's interesting. A case of using the tools I know more than the tools I don't know as well. I'd want to give it a try to see how it held up, we might be talking in some cases a 100 million rows or more, and copying all that to the version store might get pricey, but it does have some potential elegance to it!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Larry Aue
Larry Aue
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 668
I'm dealing with a lot of cross-server queries. My answer was to make the data access layer in the apps deal with it. Hard-coded SQL just isn't good. Synonyms in 2005 would work, but there's just too many different tables to make it manageable. I'd hoped to remove the owner complexity of JDE tables by creating a synonym for server.jde.proddta (say, as JDE) and then referencing tables as JDE.f0101, but it won't let me cheat that way. Angry



Charles Kincaid
Charles Kincaid
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4875 Visits: 2384
Cool article. Gave me an idea for my data archive procedures. I can create a TransactionHistory table with the year appended to the name. The TransactionHistory view can UNION ALL. I could then migrate the older years info to another sever.

ATBCharles Kincaid
DTERRELL
DTERRELL
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: 9
Nice article. Very thought-provoking. I do have a concern. Back in the days of SQL 6.5, views were a big performace issue (then again most things in 6.5 were a performance concern). I've done very little with them over the years since. May be time to revisit them since abstraction is the norm in our world.
Andy Warren
Andy Warren
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Moderators
Points: 40400 Visits: 2770
Im still cautious about piling views on top of views, though in practice it seems to work more often than not. With performance, it always depends!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Carl Uman
Carl Uman
SSC Eights!
SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)

Group: General Forum Members
Points: 819 Visits: 109
Good article.

I have a case where I need to pull data from another DB and created view on the same DB as the SP but I get an error "The SELECT permission was denied on the object 'abc', database 'bcd', schema, 'cde'." The user has select permission to the view but not the base table in the other DB. Anyway around this that I'm not seeing? I really didn't want to give select access to the base table but I did grant the public role in the other DB.

Thanks
Carl



DTERRELL
DTERRELL
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: 9
I don't know if this applies, but I thought I would throw it out there anyway. If you are using Dynamic SQL to generate the select statement then you may want to read these articles.

http://www.sommarskog.se/dynamic_sql.html#EXEC4
http://www.sommarskog.se/grantperm.html#EXECASstatement

With Dynamic SQL and using the EXEC() statement, the caller must have explicit SELECT rights on the table (view) being reference. We've gotten around this (at least for the moment) by adding "WITH EXEC AS OWNER" to the SP and granting execute rights to the SP specifically to a select group. We are then able to insulate the table from the users directly.

Hope this helps.
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