Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Views for Abstraction Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2007 9:47 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, November 17, 2014 2:20 PM
Points: 6,800, Visits: 1,914
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
Post #414746
Posted Thursday, October 25, 2007 1:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Posted Thursday, October 25, 2007 7:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, October 25, 2007 7:26 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, November 17, 2014 2:20 PM
Points: 6,800, Visits: 1,914
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
Post #414946
Posted Thursday, October 25, 2007 7:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 12:14 PM
Points: 148, Visits: 655
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.




Post #414957
Posted Thursday, October 25, 2007 8:15 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 808, Visits: 1,993
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
Posted Friday, October 26, 2007 5:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 6, 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
Posted Friday, October 26, 2007 5:39 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, November 17, 2014 2:20 PM
Points: 6,800, Visits: 1,914
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
Post #415314
Posted Thursday, November 1, 2007 8:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, November 1, 2007 8:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 6, 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 »

Add to briefcase 12»»

Permissions Expand / Collapse