Friday, August 31, 2012

SQL SERVER – Concept, Benefits and Use of Resource Governor in SQL Server


Concept Resource Governor in SQL Server
Benefits of Resource Governor in SQL Server 
Use of Resource Governor in SQL Server
Example of Resource Governor in SQL Server 
Resource POOL in Resource Governor in SQL Server  
Classification Resource Governor in SQL Server 
Workload Group Resource Governor in SQL Server   
How to Drop  Resource Governor in SQL Server   
How to  Disable Resource Governor in SQL Server
How to reset statistic Resource Governor in SQL Server   
Resource Governor in SQL Server  
Error: Cannot drop user-defined function dbo.ResourceClassifier. It is being used as a resource governor classifier.
 


SQL server 2008 has provided this feature to mange and workload of resources of SQL Server instance and system database.  Resource Governor can manage the resource utility for SQL Server and using it we can specify the memory and CUP limits for requesting application or task. It is available in SSMS, Object explorer -> Management tab.


There are only 3 concepts in Resource Governor in SQL Server

Resource POOL: When we install new SQL Server it’s create 2 Resource Pool that is Internal and Default. We can also add User Defined Resource Pool.
Workload Group: When we install new SQL Server it’s create 2 Resource Workload Group that is Internal and Default. We can also add User Defined Resource Workload Group. Work group are mapped with respective Resource Pool.
Classification: It is support is user defined function to classifier. Its work is to classify of incoming request and decides the way of internal rule.

Example:  I have to handle the work load of named MYPOOL application and this application work as Data mining which perform operation on huge databases. So now we can handle to define the limit of the resource and utilization of SQL Server.

Step 1: To Create POOL, Workload Group
--Resource pool to be used by named MyPool Application
CREATE RESOURCE POOL My_Pool
WITH
(
 MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100,
 MIN_MEMORY_PERCENT=50, MAX_MEMORY_PERCENT=100
)
GO
--create New resource pool to be used by named Reporting Application
CREATE RESOURCE POOL My_Report_Pool
WITH
(
 MIN_CPU_PERCENT=25, MAX_CPU_PERCENT=100,
 MIN_MEMORY_PERCENT=25, MAX_MEMORY_PERCENT=100
)
GO
--Workload Group to be used by named MyPool Application
CREATE WORKLOAD GROUP My_Group
    USING My_Pool ;
GO
--Workload Group to be used by named Reporting Application
CREATE WORKLOAD GROUP My_Report_Group
    USING My_Report_Pool ;
GO   

Step 2: To create User-Defined Function for User.

USE master;
GO
CREATE FUNCTION dbo.ResourceClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
 --Declare the variable to hold the value returned in sysname.
 DECLARE @My_Workload_Group AS SYSNAME
 --If the user login is 'bulk_admin', than only map the connection to ---the My_Group Group workload group.
 IF (SUSER_NAME() = 'bulk_admin')
  SET @My_Workload_Group = 'My_Group'
 --If the user login is 'sa',than only map the connection to
 --the My_Report_Group workload group.
 ELSE IF (SUSER_NAME() = 'sa')
  SET @My_Workload_Group = 'My_Report_Group'
 ELSE
  SET @My_Workload_Group = 'default'
 RETURN @My_Workload_Group
END
GO
Step 3: To register created function and reconfigure, Disable and Resetting the statistics of Resource governor.

--Now we have to register the classifier for user-defined function and -- update the in memory configuration.
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.ResourceClassifier);
GO
--Enabling Resource Governor(By default when you install
--SQL Server, Resource Governor is disabled)
--It loads the stored configuration metadata into memory
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
--To disabling whole Resource Governor
ALTER RESOURCE GOVERNOR DISABLE
GO
--The below T-SQL will used as resets statistics on all workload groups and resource pools.
ALTER RESOURCE GOVERNOR RESET STATISTICS
GO
Step 4: All data clean if it is not required
--NOTE- Clean all data after testing this example
Drop RESOURCE POOL My_Pool
drop WORKLOAD GROUP My_Group
DROP WORKLOAD GROUP My_Report_Group
DROP RESOURCE POOL My_Report_Pool
ALTER RESOURCE GOVERNOR WITH  (CLASSIFIER_FUNCTION=null)
DROP FUNCTION dbo.ResourceClassifier
--NOTE- if you will direct use the DROP FUNCTION <FUNCTION NAME> Commnad then it will return the following error so you have to use below commnad to resolve it.

Msg 10920, Level 16, State 1, Line 1
Cannot drop user-defined function
dbo.ResourceClassifier. It is being used as a resource governor classifier.

ALTER RESOURCE GOVERNOR WITH  (CLASSIFIER_FUNCTION=null)
DROP FUNCTION dbo.ResourceClassifier

Step 5: The Following snap shot shown the create POOL, Workload Group and Classification:-



This above example can be done using also Window wizards

Like and Share to SQL Integrity Blog

SQL SERVER - Explanation on Policy-Based Management (PBM) with example


SQL SERVER - Policy-Based Management (PBM)
  • What is Policy-Based Management (PBM)?
  • Explanation on Policy-Based Management (PBM)?
  • Concept of Policy-Based Management (PBM)? 
  • Describe on Policy-Based Management (PBM)? 
  • what is Facet in Policy-Based Management in SQL Server
  • what is Condtion Policy-Based Management in SQL Server
  • What is Traget in Policy-Based Management in SQL Server
  • What is Policy Policy-Based Management in SQL Server
  • What in on Demand and on schedule in Policy-Based Management in SQL Server
  • Example of Policy-Based Management in SQL Server
  • Step by Step example of Policy-Based Management in SQL Server
  • Question and Answer on Policy-Based Management in SQL Server 
Policy-Based Management is new feature available in Object explorer into the management Tab in SQL server Management Studio 2008. PBM allows creating and enforcing to configuring and managing SQL Server all the enterprise. PBM have some term to define and user to managing SQL Server.
  • Target – In the target means where we have to manage entity (like talbe, view, index etc) by using PBM.
  • Facet – It’s the system defined set of properties that can be manage. In simple way facets is the target type and each facet contains many property. Explore to facet tab you will get predefined facets and double click on facets.
  • Condition – Here we can create a property expression that evaluates to True or False and set the State of facet.
  • Policy – Here you have to select the condition to be check and/or forced and set the evaluation mode as on demand or on schedule.
 On Demand: you can right click on policy and click on evaluate.
 On schedule: it gives the option of SQL AGENT.
Example: Very Easy

We will configure and mange PMB for index facets. Just go into the facet tab you will find named index double click on it.
Now Right click on the condition table and click in ‘New condition’. Type the Name of Condition and select INDEX facet from below drop down list, than create expression as showing in below image.


 Than right click on Policy tab and click on ‘new policy’ and Give the name of Policy, than check to Checkbox in ‘Against targets’ to first option. Click on Ok button. Policy has created. See the bellow image:-


  Now we have set the evaluation mode as On demand. So that we have to go in policy tab and right click on the create policy named as My_Index_Policy’.


The symbols are showing state of policy. A Green Symbols means is condition is True and a Red symbols means condition is false on the table you can see the detail click on View Link. As shown in above screen.














Like and Share to SQL Integrity Blog

Thursday, August 30, 2012

SQL SERVER - What is user defined data type, and how to create


 It is very esay dont worry about how to use and create it below example just run only. 

It allows defining its own T- SQL User Defined Data Type and you can use this UDDT to entire Database.
See the following example-

-- TO CREATE USER DEFINED DATA TYPE WITH VARCHAR DATA TYPE

EXEC SP_ADDTYPE TYPE1, 'VARCHAR(50)','NULL'

-- TO CREATE USER DEFINED DATA TYPE WITH INT DATA TYPE 
--Example 2 (run this Full Script)

EXEC SP_ADDTYPE  @TYPENAME =TYPE2,

@PHYSTYPE= INT,

@NULLTYPE=NULL,

@OWNER= DBO

-- Declare the variable and data type is 'TYPE2'
DECLARE @MYVARIALBE TYPE2

-- Set the value in vairaible
SET @MYVARIALBE = 100

-- Get the value from varaible
SELECT @MYVARIALBE AS MYVALUE

-- Drop user defined data type
Drop  type  TYPE2


Like and Share to SQL Integrity Blog

Wednesday, August 29, 2012

SQL SERVER - What is the role of resource database WITH example


Before run below script run the detail  of resource database.
 
·        The resource database is read only database which contain the all Sys.Objects is SQL Server.
·        Resource database physical location is <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\. And the file name is always mssqlsystemresource.ldf.
·        This file cannot backup and restore. But if it required than you have to simple copy and paste option.
·        We cannot overwrite resource database.
·        Resource database can only modify by Microsoft customer support service specialist. Resource database ID is always 32767
·        To get the version number, last update date time and to access the sql server object definition of resource date base we can use  

SELECT SERVERPROPERTY('ResourceVersion');
GO
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));
GO

Like and Share to SQL Integrity Blog

SQL SERVER - What is transaction and What type of block of code you will use control transaction error and T-SQL Error


Before read this article i would like to say it best  to know for any any sql server user specially for sql server dba and developer.

This is very important is learn how to working transaction and how to use try and catch block in sql server within the transaction and how to get the run time error.

  1. What is Transaction?
A transaction is a batch of statements that are treated as a single event and Any changes made to the database by a transaction are guaranteed either to go to completion or to have no effect at all.
  1. What type of block of code you will use control transaction error and T-SQL Error?
To control transaction errors I will prefer use of begin tran commit tran, and rollback tran method and to control the T-SQL error I will use try and catch method.
Example:-(Must run this example)
USE AdventureWorks;
GO
BEGIN TRANSACTION; -- opening new transaction here

BEGIN TRY  -- try stmt started
 -- we are here writing error code as Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 677;
END TRY
BEGIN CATCH -- catch stmt strated
    SELECT
         ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity       
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_STATE() AS ErrorState
  ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO
OutPut:-


Like and Share to SQL Integrity Blog

Tuesday, August 28, 2012

SQL SERVER - Difference between RDBMS and DBMS


RDBMS and DBMS:-
1.      RDBMS DBMS + Referential Integrity
2.      An RDBMS is one that follows 12 rules of CODD.
DBMS:
·        In DBMS no relationship concept
·        It supports Single User only
·        It treats Data as Files internally
·        It supports 3 rules of E.F.CODD out off 12 rules
·        It requires low Software and Hardware Requirements.
·        FoxPro, IMS are Examples
RDBMS:
·        It is used to establish the relationship concept between Two database objects, i.e, tables
·        It supports multiple users
·        It treats data as Tables internally
·        It supports minimum 6 rules of E.F.CODD
·        It requires High software and hardware requirements.
SQL-Server, Oracle is examples

Like and Share to SQL Integrity Blog

SQL SERVER - What is Database


Database is object in SQL Server and we can create user defined database and there are also available system database. 
Database contains the table objects and some other type of object. 
Database is available in SQL server instance.

Like and Share to SQL Integrity Blog

SQL SERVER - What is DBMS


·        In DBMS no relationship concept
·        It supports Single User only
·        It treats Data as Files internally
·        It supports 3 rules of E.F.CODD out off 12 rules
·        It requires low Software and Hardware Requirements.

Like and Share to SQL Integrity Blog

SQL SERVER - What is basically RDBMS



RDBM is stand for Relational Database Management System, it is the basic of SQL and it provide the relation between table object which contain in the database object.

 RDBMS has provided referential integrity facility to SQL server to maintain the database.

 RDBMS follows 12 rules which were defined by E.F. Codd.

Like and Share to SQL Integrity Blog

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