You may need to index this view later to improve performance. What changes, if any, do you need to make to this code to be able to index the view later?

You may need to index this view later to improve performance. What changes, if any, do you need to make to this code to be able to index the view later?

You need to create a new view, and you are planning to use this code:

CREATE VIEW Contacts_in_222 AS SELECT c.ContactID, title as [Title], lastname as [Last Name], firstname as [First Name], phone as [Phone Number], c3.cardtype as [Card Type] FROM Person.Contact c JOIN Sales.ContactCreditCard c2 on c.ContactID = c2.ContactID JOIN Sales.CreditCard c3 on c2.CreditCardID = c3.CreditCardID WHERE phone LIKE ‘222%’

You may need to index this view later to improve performance. What changes, if any, do you need to make to this code to be able to index the view later?


Answer – Change the code to this:
CREATE VIEW Contacts_in_222 WITH INDEXABLE AS SELECT c.ContactID, title as [Title], lastname as [Last Name], firstname as [First Name], phone as [Phone Number], c3.cardtype as [Card Type] FROM Person.Contact c JOIN Sales.
ContactCreditCard c2 on c.ContactID = c2.ContactID JOIN Sales.CreditCard c3 on c2.CreditCardID = c3.CreditCardID WHERE phone LIKE ‘222%’
(To index a view, you must use the SCHEMABINDING option, which prevents the underlying table from being changed, unless the schema bound view is dropped first. Also note, TABLEBINDING and INDEXABLE are not actual options.)