次の方法で共有

Quick SQL Tips – Indexed Views

  • [アーティクル]

Although not a new feature, Indexed views can still be a useful tool for increasing query performance. Of course you have to be careful of the trade-offs. Just like indexing a table, indexing a view may speed up a query, but will increase your storage requirements and slow down insert and update operations. So make sure you benchmark performance before and after you add an index to a view.

With that caveat in mind, I’d like to do a review of Indexed Views because of a post I saw from a SQL User having trouble creating a Fulltext index on an indexed view.

When we create a view we simply specify the select statement that will return the data we want displayed in the view.

 CREATE VIEW OrderInfo 
AS SELECT od.SalesOrderId, od.productid, od.unitprice, od.orderqty, p.name
FROM Sales.SalesOrderDetail od
Production.product p
ON od.productid =p.productid

If you want to add indexes to a view you must make the view schema bound by adding the WITH SCHEMABINDING clause to the CREATE VIEW statement and you must specify the schema for each table specified in the select statement for the view.

 CREATE VIEW OrderInfo WITH SCHEMABINDING
AS SELECT od.SalesOrderId, od.productid, od.unitprice, od.orderqty, p.name
FROM Sales.SalesOrderDetail od
Production.product p
ON od.productid =p.productid

The first index you create on a view must be a unique clustered index. So in this example I create a unique clustered index on the combination of SalesOrderId and ProductId

 CREATE UNIQUE CLUSTERED INDEX idx_orderinfo_salesOrderid 
ON orderinfo(SalesOrderid,productid)

I can now add additional nonclustered indexes as desired to the view

 CREATE NONCLUSTERED INDEX idx_unitprice 
ON orderinfo(unitprice)

Now coming back to the question posted on reddit, can you create a fulltext index on the view? The steps for full text indexes changed quite a bit for SQL Server 2008 from SQL Server 2005. The steps listed below are for SQL Server 2008 and higher where full text indexing no longer requires a separate service and is enabled automatically.

In order to create a full text index you first need a full text catalog, unless you have already created one for other fulltext indexes in your database.

 CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT

Next I try to create a fulltext index on the product name column, you must specify the name of the column you wish to index and the name of the unique clustered index for the view.

 CREATE FULLTEXT INDEX ON dbo.orderinfo(name) 
KEY INDEX idx_orderinfo_salesorderid

At this point I receive an error message, because there are restrictions on the key indexes used for creating full text indexes. The key index must be:

  • Unique
  • Non-nullable
  • Single-column
  • Online
  • Cannot be on a non-deterministic column
  • Cannot be on a nonpersisted computed column
  • Cannot be a filtered index
  • Cannot be based on a column that exceeds 900 bytes

My key index is based on two columns, so I am unable to create a full text index for this view. So can you create a full text index on a view? It depends. If my view above had a key index that met the requirements listed above then yes! If my key index does not meet the requirements I may need to redesign my index or my view so that I can create a key index that meets the requirements.

So we finish with everyone’s favourite answer. It depends. Don’t forget if you know SQL you know SQL Azure, read about the differences between on premise SQL Server and SQL Azure database development and you will find it’s easier than you think.

Comments

  • Anonymous
    November 02, 2011
    If I have joined tables with no unique column, how do I create unique index, so it will work with full text index?
  • Anonymous
    November 02, 2011
    You can just add a unique clustered index with the CREATE INDEX commandCREATE UNIQUE CLUSTERED INDEX idx_orderinfo_salesOrderidON orderinfo(SalesOrderid,productid)If you don't have a column or combination of columns that is unique then you have a problem, You could create some sort of artifical key with a unique value across all rows, and then make a unique index on that column just so you can meet the requirement and create the full text index.