Views for Abstraction

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Views for Abstraction

13 posts, Page 1 of 2

1 2 »»

Views for Abstraction

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Wednesday, October 24, 2007 9:47 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Comments posted to this topic are about the item Views for Abstraction

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #414746

Christian Buettner-167247

Posted Thursday, October 25, 2007 1:08 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:12 AM
Points: 2,526, Visits: 3,620

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

Post #414800

bur

Posted Thursday, October 25, 2007 7:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 25, 2007 2:25 PM
Points: 1, 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

Post #414938

Andy Warren

Posted Thursday, October 25, 2007 7:26 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #414946

Larry Aue

Posted Thursday, October 25, 2007 7:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:41 PM
Points: 142, Visits: 525

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

Post #414957

Charles Kincaid

Posted Thursday, October 25, 2007 8:15 AM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772, Visits: 1,825

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.

ATB

Charles Kincaid

Post #414978

DTERRELL

Posted Friday, October 26, 2007 5:23 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 06, 2007 2:22 PM
Points: 2, 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.

Post #415307

Andy Warren

Posted Friday, October 26, 2007 5:39 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #415314

Carl Uman

Posted Thursday, November 01, 2007 8:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 15, 2013 1:54 PM
Points: 113, 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

Post #417517

DTERRELL

Posted Thursday, November 01, 2007 8:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 06, 2007 2:22 PM
Points: 2, 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.

Post #417530

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions