create index on view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

create index on view

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-03-03 : 03:50:39

To improve the performance, Is necessary to create index on view though the base tables under the same view is fully indexed?

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-03 : 04:42:04

the view should use the base tables index if properly coded.

Remember that if you use functions to compare columns (like datediff or convert) then you can't use an index on that column.

If that doesn't help post the VIEW and the structure of the BASE TABLES.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

David Singleton
Starting Member

35 Posts

Posted - 2010-03-03 : 10:41:44

quote:
Originally posted by Transact Charlie

the view should use the base tables index if properly coded.

Can you define what you mean by "Properly coded"?

David Singleton

Microsoft MVP Dynamics NAV

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-03 : 10:49:15

hi.

I meant that you avoid using function calls around columns otherwise you can't use an index on that column.

So for example if I wanted to compare a date against a variable then this can use an index


WHERE
    [datecol] > @startDate
    AND [dateCol] <= @endDate

But this can no


WHERE
    DATEDIFF(DAY, [dateCol], @startDate) > 2

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

David Singleton
Starting Member

35 Posts

Posted - 2010-03-03 : 11:01:52

Thanks.

That makes good sense, since the output is not actually stored in the database its not possible to index against. Same applies I assume to CAST.

David Singleton

Microsoft MVP Dynamics NAV

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources