Tuesday, August 21, 2012

SQL SERVER – Everything about Indexing with all details


List of Question:-
  • What is indexing?
  • What types are indexing in SQL Server?
  • What is difference between Cluster and Non-Cluster Indexing?
  • What is the syntax of creation of index?
  • Can we use multiple indexes in one table?
  • Can we use index on View?
  • How to create index on View?
  • What types of permission are required to create or delete index?
  • What is Cluster Index?
  • What is Non-Cluster index?
  • How to create Non-cluster index?
  • Non-clustered indexes are implemented in the following ways.
  • What is Unique Index?
  • What is the restriction on unique index?
  • How to create unique index?
  •  What is filtered Index?
  • What are the benefits of Filtered Index?
  • How to create Filtered Index?
  • Can we create multiple Cluster index in one object?
  •  How to get Index in the table?
  • When we should rebuild and when you should organize the index?
  • How to Rebuild or Organize Existing Index?
  • How to enable or disable Index in Table?
  • Q14. How to enable or disable Index in an Instance?
  • How to modify Index?
  • How to Drop Index?
  • How to get script to which index should REBUILD or ORGANIZE?
  • Can we create multiple Cluster index in one table?
  • Can we use cluster and Non-Cluster index on same Column on single table?
  • Where we should not use indexing?
  • What are the Disadvantages of the index?
  • What are the benefits of Indexing?
  • What is the restriction on filtered index?
  • What is the difference between Indexing, Identity and Primary Key?
  • Readymade indexing Script or Readymade indexing example for you use  with all type of index. Just copy and run in AdventureWorks Database.

Question & Answer:-

1.      What is indexing?
Indexes of SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Indexes are of two types. There are two type of Indexing in SQL server.
2.      What types are indexing in SQL Server?

1.       Cluster index
2.       Non-Cluster Index
3.       Unique Clustered index
4.       Unique Non-Clustered Index
5.       Filtered index (based on Non-clustered index)

3.      What is difference between Cluster and Non-Cluster Indexing?
Clustered index exists as sorted row on disk.
Clustered index re-orders the table record.
Clustered index contains record in the leaf level of the B-tree.
There can be only one Clustered index possible in a table. We can apply on only one column in the table.

Non-clustered is the index in which logical order doesn’t match with physical order of stored data on disk.
Non-clustered index contains index key to the table records in the leaf level.
There can be one or more Non-clustered indexes in a table. In 2005 we can create 255 index out of 256 index. And in 2008 we can create 999 indexes out of 1000 index.
4.      What is the syntax of creation of index?
Syntax:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]

< index_option > :: =
{   PAD_INDEX |
1.       FILLFACTOR = fillfactor |
2.       IGNORE_DUP_KEY |
3.       DROP_EXISTING |
4.       STATISTICS_NORECOMPUTE |
5.       SORT_IN_TEMPDB
}

5.      Can we use multiple indexes in one table?

Yes, we can use multiple indexes on one table but Cluster index can only in one table.

6.      Can we use index on View?

Yes, we can create index on the view. Index created view is called ‘indexed view’.
A view with a clustered index is called an indexed view.

7.      How to create index on View?

--Run This script to create index on View

USE AdventureWorks
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO

--Create view.
CREATE   VIEW TestView
WITH   SCHEMABINDING
AS
   SELECT SUM(UnitPrice*OrderQty) AS Revenue, oh.ModifiedDate, ProductID, COUNT_BIG(*) AS COUNT
   FROM   Sales.SalesOrderDetail sod, Sales.SalesOrderHeader  oh
   WHERE   sod.SalesOrderID=oh.SalesOrderID
   GROUP BY   oh.ModifiedDate, ProductID
GO
--Create index on the view.
CREATE UNIQUE CLUSTERED INDEX IX_testView ON TestView (ModifiedDate, ProductID)
GO

8.      What are the restrictions on indexed views?

As we can create index on table same as we can create index on the View.
The SELECT statement defining an indexed view must not have the TOP, DISTINCT, COMPUTE, HAVING, and UNION keywords AVG function. It cannot have a subquery, asterisks (*), 'table.*' wildcard lists, DISTINCT, COUNT(*), COUNT(<expression>), computed columns from the base tables, and scalar aggregates, views, rowset functions, inline functions, or derived tables, float data type or uses float expressions,
it must contain COUNT_BIG(*). Other aggregate functions  MIN, MAX, STDEV, etc are not allowed.
We can use any joined tables but not allowed on OUTER JOIN operations .
When we run the quesy on index view it not allowed any subqueries or CONTAINS or FREETEXT predicates are allowed in the search condition.
CREATE UNIQUE CLUSTERED INDEX clause can accept COUNT_BIG(*) in select statement when we use Group by clause.
9.      What types of permission are required to create or delete index?
When you Create and Alter the index you should have permissions default to the sysadmin fixed server role and the db_ddladmin and db_owner fixed database roles and the table owner, and are not transferable.
10.  What is Cluster Index?
It’s creates an object where the physical order of rows is the same as the indexed order of the rows, and the leaf node contains the actual data rows.
A unique clustered index must be created on a view before any other indexes can be defined on the same view.
It for you good practice first creates the clustered index and than create the Non-clustered index on the table otherwise existing Non-clustered indexes on tables are rebuilt when a clustered index is created.
If CLUSTERED is not specified in the create statement of index than a Non-clustered index is created.
11.  What is Non-Cluster index?

After a unique clustered index has been created on a view, Non-clustered indexes can be created.

A Non-clustered index is an index structure separate from the data stored in a table that reorders one or more selected columns.

      Non-clustered index is not change the physical order of table. It’s based on the Logical arrangement of index and non-clustered index contain the non leaf node of the B-Tree.

12.  How to create Non-cluster index?

To create a nonclustered index by using the Table Designer

1.      In Object Explorer
2.      Expand the Tables folder.
3.      Right-click the table and select Design.
4.      On the Table Designer menu, click Indexes/Keys.
5.      In the Indexes/Keys dialog box, click Add.
6.      Select the new index in the Selected Primary/Unique Key or Index text box.
7.      In the grid, select Create as Clustered, and choose No from the drop-down list to the right of the property.
8.      Click Close.
9.      On the File menu, click Save table_name.

To create a nonclustered index by using Object Explorer

1.      In Object Explorer
2.      Expand the Tables folder.
3.      Expand the table.
4.      Right-click the Indexes folder, point to New Index, and select Non-Clustered Index.
5.      In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.
6.      Under Index key columns, click Add.
7.      In the Select Columns from table_name dialog box, select the check box or check boxes of the table column or columns to be added to the nonclustered index.
8.      Click OK.
9.      In the New Index dialog box, click OK.

To create a nonclustered index on a table


USE AdventureWorks;
GO
-- Find an existing index named IX_ProductVendor_VendorID and delete it if found.
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
-- Create a nonclustered index called IX_ProductVendor_VendorID
-- on the Purchasing.ProductVendor table using the BusinessEntityID column.
CREATE NONCLUSTERED INDEX IX_ProductVendor_VendorID
    ON Purchasing.ProductVendor (ProductId);
GO

13.   Nonclustered indexes are implemented in the following ways.

·         UNIQUE constraints
When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist
·         Index independent of a constraint
The maximum number of nonclustered indexes that can be created per table is 999. This includes any indexes created by PRIMARY KEY or UNIQUE constraints, but does not include XML indexes.
·         Nonclustered index on an indexed view
After a unique clustered index has been created on a view, nonclustered indexes can be created.

14.  What is Unique Index?

A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique.

15.  What is the restriction on unique index?

We cannot create a unique index on a single column if that column contains NULL in more than one row.
We cannot create a unique index on multiple columns if any of columns contains NULL in more than one row because it’s treated as duplicate values for indexing purposes.
A unique index, UNIQUE constraint, or PRIMARY KEY constraint cannot be created if duplicate key values exist in the data.
     A unique Non-clustered index can contain included Non-key columns.

16.  How to create Unique index?

To create a unique index by using the Table Designer

1.      In Object Explorer.
2.      Right-click the table and select Design.
3.      On the Table Designer menu, select Indexes/Keys.
4.      In the Indexes/Keys dialog box, click Add.
5.      Select the new index in the Selected Primary/Unique Key or Index text box.
6.      In the main grid, under (General), select Type and then choose Index from the list.
7.      Select Columns, and then click the ellipsis (…).
8.      In the Index Columns dialog box, under Column Name, select the columns you want to index. You can select up to 16 columns. For optimal performance, select only one or two columns per index. For each column you select, indicate whether the index arranges values of this column in ascending or descending order.
9.      When all columns for the index are selected, click OK.
10.  In the grid, under (General), select Is Unique and then choose Yes from the list.
11.  Optional: In the main grid, under Table Designer, select Ignore Duplicate Keys and then choose Yes from the list. Do this if you want to ignore attempts to add data that would create a duplicate key in the unique index.
12.  Click Close.
13.  On the File menu, click Save table_name.

Create a unique index by using Object Explorer

1.      Expand the table on which you want to create a unique index.
2.      Right-click the Indexes folder, point to New Index, and select Non-Clustered Index….
3.      In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.
4.      Select the Unique check box.
5.      Under Index key columns, click Add….
6.      In the Select Columns from table_name dialog box, select the check box or check boxes of the table column or columns to be added to the unique index.
7.      Click OK.
8.      In the New Index dialog box, click OK.

To create a unique index on a table


USE AdventureWorks;
GO
-- Find an existing index named AK_UnitMeasure_Name and delete it if found
IF EXISTS (SELECT name from sys.indexes
           WHERE name = N'AK_UnitMeasure_Name')
   DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
-- Create a unique index called AK_UnitMeasure_Name
-- on the Production.UnitMeasure table using the Name column.
CREATE UNIQUE INDEX AK_UnitMeasure_Name
   ON Production.UnitMeasure (Name);
GO

17.   What is filtered Index?

. A filtered index is based on Non-clustered index especially suited to cover queries that select from a conditional data

18.  What are the benefits of Filtered Index?
1.       Improved query performance and plan quality
2.       Reduced index maintenance costs
3.       Reduced index storage costs
19.  How to create Filtered Index?

   To create a unique index by using the Table Designer( Same as above)

   Create a unique index by using Object Explorer( Same as above)

   To create a unique index on a table


USE AdventureWorks;
GO
-- Looks for an existing filtered index named "FIBillOfMaterialsWithEndDate"
-- and deletes it from the table Production.BillOfMaterials if found.
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO
-- Creates a filtered index "FIBillOfMaterialsWithEndDate"
-- on the table Production.BillOfMaterials
-- using the columms ComponentID and StartDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

20.  Can we create multiple Cluster index in one object?

No

21.   How to get Index in the table?

Syntax:  SP_HELPINDEX <Tablename>
Example: SP_HELPINDEX 'Purchasing.ProductVendor'
---        Or –
---Get the all index in the instance
select OBJECT_NAME(object_id ), name from sys.indexes

22.  When we should Rebuild and when you should Organize the index?

When the avg. fragmentation percent is greater than 30% than we should Rebuild the index and if the Avg. fragmentation is below 30% than we should Organize to index.

Using this system DMV view sys.dm_db_index_physical_stats we can get the Avg. fragmentation percent of the table

23.  How to Rebuild or Organize Existing Index?
ALTER INDEX ALL ON PERSON.ADDRESS  REBUILD
ALTER INDEX ALL ON PERSON.ADDRESS  REBUILD WITH (ONLINE=ON)

24.  How to enable or disable Index in Table?
--- Get the Index in a table

USE AdventureWorks;
GO
EXEC sp_helpindex 'person.contact'
GO

25.  Q14. How to enable or disable Index in an Instance?

Some time it our need to Enable and Disable the Index such as Bulk Operation (Import & Export), BCP Operation, DTS Package etc.

The above following situation you can use the Enable/Disable on the Table.

---Get the all index in the instance

select OBJECT_NAME(object_id ), name from sys.indexes

Example –

USE AdventureWorks;
GO

----Diable Index
ALTER INDEX [IX_Contact_EmailAddress] ON person.contact DISABLE
GO
----Enable Index
ALTER INDEX [IX_Contact_EmailAddress] ON person.contact REBUILD
GO

26.  How to modify Index?

Using Alter index command you can modify or alter the index

27.  How to Drop Index?

USE AdventureWorks;
GO
-- Looks for an existing index named "FIBillOfMaterialsWithEndDate"
-- and deletes it from the table Production.BillOfMaterials if found.
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO

28.  How to get script to which index should REBUILD or ORGANIZE?

Just Run the below T-SQL Script and get all auto build query for Reorganize and Rebuild to all table in a single database.

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        PRINT (@command);
        PRINT  (@command);
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
Result:

ALTER INDEX [PK_Product_ProductID] ON [Production].[Product] REORGANIZE
ALTER INDEX [PK_Product_ProductID] ON [Production].[Product] REORGANIZE
ALTER INDEX [AK_Product_ProductNumber] ON [Production].[Product] REBUILD
ALTER INDEX [AK_Product_ProductNumber] ON [Production].[Product] REBUILD

After that select result and Run to Reorganize and Rebiuld all Tables of  a single Database.

ALTER INDEX [PK_Product_ProductID] ON [Production].[Product] REORGANIZE
ALTER INDEX [PK_Product_ProductID] ON [Production].[Product] REORGANIZE
ALTER INDEX [AK_Product_ProductNumber] ON [Production].[Product] REBUILD
ALTER INDEX [AK_Product_ProductNumber] ON [Production].[Product] REBUILD

Result:

Command(s) completed successfully.

29.  Can we create multiple Cluster index in one table?

No, Only One in One Table

30.  Can we use cluster and Non-Cluster index on same Column on single table?

Yes

31.  Where we should not use indexing?

A table are frequently inserting records, updating records and deleting records of a table on that table we should not use indexing.

32.  What are the Disadvantages of the index?
Some time uses of indexes slow down Data modification operations (such as INSERT, UPDATE, and DELETE).
Every time data changes in the table, all the indexes need to be updated.
Indexes need disk space, the more indexes you have, and more disk space is used.
33.  Where stoered the Indexing information?
In the Fill factor stored the indexing information. Important  Creating a index with a FILLFACTOR affects the amount of storage space the data occupies because SQL Server redistributes the data when it creates the index.


34.  What are the benefits of Indexing?
?
35.  What is the restriction on filtered index?
?
36.  What is the difference between Indexing, Identity and Primary Key?
?
37.  Ready made indexing Script or Ready made indexing example for you use  with all type of index. Just copy and run in AdventureWorks Database.


-- Readymade indexing  Examples
--A. Use a simple index
--This example creates an index on the AddressID column of the Address table.
SET NOCOUNT OFF
USE AdventureWorks
IF EXISTS (SELECT name FROM sysindexes
      WHERE name = 'IX_VendorAddress_AddressID')
   DROP INDEX Address.IX_VendorAddress_AddressID
GO
USE AdventureWorks
CREATE INDEX IX_VendorAddress_AddressID
   ON Person.Address(AddressID)
GO
--clear data
   DROP INDEX Address.IX_VendorAddress_AddressID
GO
--B. Use a unique clustered index
--This example creates an index on the AddressID column of the Person.Address table that enforces uniqueness. This index physically orders the data on disk because the CLUSTERED clause is specified.
SET NOCOUNT ON
USE AdventureWorks
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'Address')
   DROP TABLE Address
GO
USE AdventureWorks
IF EXISTS (SELECT name FROM sysindexes
      WHERE name = 'IX_VendorAddress_AddressID')
   DROP INDEX Person.Address.AddressID
GO
USE AdventureWorks
GO
CREATE TABLE Person.Address
(
 employeeID int NOT NULL,
 base_pay money NOT NULL,
 commission decimal(2, 2) NOT NULL
)
INSERT Person.Address
   VALUES (1, 500, .11)
INSERT Person.Address
   VALUES (2, 1050, .15)
INSERT Person.Address
   VALUES (3, 8500, .07)
INSERT Person.Address
   VALUES (4, 1510, .03)
INSERT Person.Address
   VALUES (7, 7550, .02)
GO
SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEX IX_VendorAddress_AddressID
   ON Person.Address (AddressID)
GO
--C. Use a simple composite index
--This example creates an index on the orderID and employeeID columns of the order_emp table.
SET NOCOUNT ON
USE AdventureWorks
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'SalesOrderDetail')
   DROP TABLE SalesOrderDetail
GO
USE AdventureWorks
IF EXISTS (SELECT name FROM sysindexes
      WHERE name = 'IX_sales_order')
   DROP INDEX SalesOrderDetail.emp_order_ind
GO
USE AdventureWorks
GO
CREATE TABLE SalesOrderDetail
(
 orderID int IDENTITY(1000, 1),
 employeeID int NOT NULL,
 orderdate datetime NOT NULL DEFAULT GETDATE(),
 orderamount money NOT NULL
)

INSERT SalesOrderDetail (employeeID, orderdate, orderamount)
   VALUES (5, '4/12/2012', 31.1)
INSERT SalesOrderDetail (employeeID, orderdate, orderamount)
   VALUES (5, '5/30/2012', 199.4)
INSERT SalesOrderDetail (employeeID, orderdate, orderamount)
   VALUES (1, '1/03/2012', 209.8)
INSERT SalesOrderDetail (employeeID, orderdate, orderamount)
   VALUES (2, '1/22/2012', 44.29)
INSERT SalesOrderDetail (employeeID, orderdate, orderamount)
   VALUES (3, '4/05/2012', 68.39)
INSERT SalesOrderDetail (employeeID, orderdate, orderamount)
   VALUES (4, '3/21/2012', 15.23)
INSERT SalesOrderDetail (employeeID, orderdate, orderamount)
   VALUES (7, '3/21/2012', 445.7)
INSERT SalesOrderDetail (employeeID, orderdate, orderamount)
   VALUES (7, '3/22/2012', 217)
GO
SET NOCOUNT OFF
CREATE INDEX IX_sales_order_de
   ON SalesOrderDetail (orderID, employeeID)
--D. Use the FILLFACTOR option
--This example uses the FILLFACTOR clause set to 100. A FILLFACTOR of 100 fills every page completely and is useful only when you know that index values in the table will never change.
SET NOCOUNT OFF
USE AdventureWorks
IF EXISTS (SELECT name FROM sysindexes
      WHERE name = 'IX_zip')
   DROP INDEX Address.IX_zip
GO
USE AdventureWorks
GO
CREATE NONCLUSTERED INDEX IX_zip
   ON Address (zip_code)
   WITH FILLFACTOR = 100
--E. Use the IGNORE_DUP_KEY
--This example creates a unique clustered index on the emp_pay table. If a duplicate key is entered, the INSERT or UPDATE statement is ignored.
SET NOCOUNT ON
USE AdventureWorks
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'Employee')
   DROP TABLE Employee
GO
USE AdventureWorks
IF EXISTS (SELECT name FROM sysindexes
      WHERE name = 'IX_employeeID')
   DROP INDEX Employee.IX_employeeID
GO
USE AdventureWorks
GO
CREATE TABLE Employee
(
 employeeID int NOT NULL,
 base_pay money NOT NULL,
 commission decimal(2, 2) NOT NULL
)
INSERT Employee
   VALUES (1, 5100, .10)
INSERT Employee
   VALUES (2, 1050, .05)
INSERT Employee
   VALUES (3, 8070, .07)
INSERT Employee
   VALUES (5, 1502, .03)
INSERT Employee
   VALUES (9, 7520, .06)
GO
SET NOCOUNT OFF
GO
CREATE UNIQUE CLUSTERED INDEX IX_employeeID
   ON Employee(employeeID)
   WITH IGNORE_DUP_KEY
--F. Create an index with PAD_INDEX
--This example creates an index on the author's identification number in the Contact table. Without the PAD_INDEX clause, SQL Server creates leaf pages that are 10 percent full, but the pages above the leaf level are filled almost completely. With PAD_INDEX, the intermediate pages are also 10 percent full.
--Note  At least two entries appear on the index pages of unique clustered indexes when PAD_INDEX is not specified.
SET NOCOUNT OFF
USE AdventureWorks
IF EXISTS (SELECT name FROM sysindexes
      WHERE name = 'Contact')
   DROP INDEX Contact.IX_ContactID
GO
USE AdventureWorks
CREATE INDEX IX_ContactID
   ON Contact (ContactID)
   WITH PAD_INDEX, FILLFACTOR = 10
  
--G. Create an index on a view
--This example will create a view and an index on that view. Then, two queries are included using the indexed view.
USE AdventureWorks
GO

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO

--Create view.
CREATE   VIEW TestView
WITH   SCHEMABINDING
AS
   SELECT SUM(UnitPrice*OrderQty) AS Revenue, oh.ModifiedDate, ProductID, COUNT_BIG(*) AS COUNT
   FROM   Sales.SalesOrderDetail sod, Sales.SalesOrderHeader  oh
   WHERE   sod.SalesOrderID=oh.SalesOrderID
   GROUP BY   oh.ModifiedDate, ProductID
GO
--Create index on the view.
CREATE UNIQUE CLUSTERED INDEX IX_testView ON TestView (ModifiedDate, ProductID)
GO

--This query will use the above indexed view.
SELECT SUM(UnitPrice*OrderQty) AS Rev, OrderDate, ProductID
FROM  Sales.SalesOrderDetail sod, Sales.SalesOrderHeader  oh
WHERE   sod.SalesOrderID=oh.SalesOrderID AND ProductID in (2, 4, 25, 13, 7, 89, 22, 34)
   AND sod.ModifiedDate >= '08/02/2012'
GROUP BY sod.ModifiedDate, ProductID
ORDER BY Rev DESC

--This query will use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty) AS Rev
FROM  Sales.SalesOrderDetail sod, Sales.SalesOrderHeader  oh
WHERE   sod.SalesOrderID=oh.SalesOrderID AND DATEPART(mm,sod.ModifiedDate)= 3
   AND DATEPART(yy,sod.ModifiedDate) = 2012
GROUP BY sod.ModifiedDate
ORDER BY sod.ModifiedDate ASC



Like and Share to SQL Integrity Blog

No comments:

Post a Comment

Thank You !!!!