2008 indexed view r2 standard edition

Question

2008 indexed view r2 standard edition

timscronin

SSCertifiable

Points: 6851
More actions
September 10, 2012 at 2:01 pm

#281420

Not to be confused just to clairfy.
i have a 3rd pary db which our warhouse team goes after. Their extract would benefit from some indexes but 3rd party vendor would not allow. i was thinking creating an indexed view and adding heloing indexes and let them extract from that. With standard edition that is alowed, and I do not need to do anyting special to the view when underlying data changes (timecard system changes daily).

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's allowed, but you'll have to add the NoExpand hint to any query that you want to use the indexed view

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

timscronin SSCertifiable Points: 6851 More actions · Answer 2

what is the query is coming from another server (query is on sql 2008 server to a linked servr that has the indexed view)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Don't understand the question.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

timscronin SSCertifiable Points: 6851 More actions · Answer 4

I will create the indexed view on server a. The query comes from server B, can I still use the "NoExpand hint to any query that you want to use the indexed view " on the sql statement from server B?

--------------------------------------------------------------------------------

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

You would have to create the indexed view in the same database as the source table.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

If you are creating an indexed view on Standard edition, any query (from anywhere at all) that you want to use the indexed view must have the NoExpand hint on it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

timscronin SSCertifiable Points: 6851 More actions · Answer 7

THanks I do have a concern that I may be introducing extra overhead that may actually cost me, the data I am looking at making the indexed view on is a timecard database with millions of rows that are entered daily

sandeep rawat Ten Centuries Points: 1317 More actions · Answer 8

if your data is changing daily then it will cost u crating index.

for more detail there a link

http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/advantage-and-disadvantage-of-using-indexed-view-in-sql-server.aspx

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

sandeep rawat (9/11/2012)

if your data is changing daily then it will cost u crating index.

for more detail there a link

http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/advantage-and-disadvantage-of-using-indexed-view-in-sql-server.aspx%5B/quote%5D

Let us make this easier for others to follow:

http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/advantage-and-disadvantage-of-using-indexed-view-in-sql-server.aspx