We help IT Professionals succeed at work.

Is indexing possible in snapshot Database (SQL Server 2005)

211 Views
Last Modified: 2012-05-07
I am planning on using a Snapshot Database for a read-only database that is being used for reporting, using the SNAPSHOT OF command.

Is it possible to create index on this snapshot database, to make the reports more efficient?

Thanks
Comment
Watch Question

Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
so the indexes carry over to the snapshot database?

But can we create any new index on the snapshot database?
CERTIFIED EXPERT
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>But can we create any new index on the snapshot database?<<
Let's try it this way:  No.

Author

Commented:
acperkins,
very sorry.. that was a big oversight on my part.. Thanks for your patience and clarrifying that in simplest terms.

I had read that article when you mentioned it first, and observed the heart of what this feature is all about.

You have convinced me of snapshot databases, and i have been playing with it some, and i was thinking hard before moving on, this topic being new to me, if i am missing any other details, and that is where this oversight came into the picture..

aneeshattingal,
Thanks for mentioning the moving of the indexes into the destination Snapshot database. That makes perfect sense.

Author

Commented:

in my test, the Snapshot database queries, some complex, were much faster than in the source database. What feature of snapshot database would you attribute this to?

Author

Commented:
The question was due to an oversight, but the answers gave me a good perspective, understanding, and also reading the article again, re-affirmed what i have been learning for the past 2 weeks.
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
may be it is using a differebt execution plan, check and compare the execution plans
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.