November 17, 2008 at 10:11 pm
i want to add indexes in my view .when use my view i goted time out expire . plz send me solution ......
thanks & regards
lav
November 17, 2008 at 10:34 pm
Index hints (a form of a table hint) are used to specify which index or indexes you want used when a query runs. When you specify one or more indexes, the Query Optimizer will use them when creating the execution plan for your query.
The syntax for a table hint is:
SELECT column_list FROM table_name WITH (INDEX (index_name) [, ...])
Where:
column_list is the list of columns you want SELECTed.
table_name is the name of the table with the relevant indexes.
index_name is the index name or index ID for the index or indexes you want used by the Query Optimizer.
Here's an example:
SELECT memberID, fname, lname FROM members WITH (INDEX(memberID_index))
While you can use an index ID instead of the index name, this is not a good idea. This is because index IDs can change if you drop and recreate the indexes differently than when you first created them.
If you want to specify multiple indexes, to force an index intersection or an index covering as a result of an index join, you can by adding each index name or ID, one after another, with each one separated by a comma.
If you want to force a table scan, use the hint: (INDEX(0)).
Index hints should only be used when the Query Optimizer, for whatever reason, does not select the available index, and if using the index provides a performance enhancement over not using an index.
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
November 17, 2008 at 10:36 pm
lav (11/17/2008)
i want to add indexes in my view .when use my view i goted time out expire . plz send me solution ......thanks & regards
lav
may be this will helpful:
Make sure that session properties are properly set.
Create a deterministic view with new SCHEMABINDING syntax.
Create unique Clustered Index.
Optionally, create additional nonclustered indexes.
Below you will find the code that you can paste into the Sql Server Query Analyzer to test this yourself. This example is based on the Northwind sample database.
-- Use the northwind database
USE NORTHWIND
GO
-- Make sure that all of the session settings are set properly
IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON
IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET CONCAT_NULL_YIELDS_NULL ON
IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON
IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON
IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON
IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON
IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF
go
-- Create the view, it must comply with the rules (deterministic)
CREATE VIEW PRODUCTS_BY_CUSTOMER WITH SCHEMABINDING AS
select customers.companyname,
products.productname,
sum(odetail.unitprice*odetail.quantity) as TotalPurchase,
count_big(*) as cnt
from dbo."order details" as odetail
inner join dbo.orders as omain
on omain.orderid = odetail.orderid
INNER join dbo.customers as customers
on customers.customerid = omain.customerid
INNER join dbo.products as products
on products.productid = odetail.productid
group by
customers.companyname,
products.productname
go
-- the following statement will cause an error if the view has not been
-- indexed
--EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'
--Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91
--Views do not have space allocated.
-- Check to see if the indexes can be created
if ObjectProperty(object_id('products_by_customer'),'IsIndexable') = 1
BEGIN
-- Create a clustered index, it MUST be unique
CREATE UNIQUE CLUSTERED INDEX PRODUCTS_BY_CUSTOMER_UNIQUE ON
PRODUCTS_BY_CUSTOMER(COMPANYNAME, PRODUCTNAME)
EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'
-- Create NonClustered Indexes
CREATE INDEX PRODUCTS_BY_CUSTOMER_1 ON
PRODUCTS_BY_CUSTOMER(COMPANYNAME)
EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'
-- Create NonClustered Indexes
CREATE INDEX PRODUCTS_BY_CUSTOMER_2 ON
PRODUCTS_BY_CUSTOMER(PRODUCTNAME)
EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'
END
Please note the ObjectProperty(object_id('products_by_customer'),'IsIndexable') = 1 in the above code listing. This command will tell you if all of the requirements for indexing a view have been met so that you can programmatically determine if a view can be indexed or not.
Also note that no space is allocated in the database for this view until the clustered index is created. If you try to use the SP_SPACEUSED stored procedure on a view that is not indexed, you get an error. The results of the SP_SPACEUSED commands that are sprinkled throughout the above code listing gives the following results on my test machine.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply