Wednesday 10 September 2014

SQL Server - All Database Mirroring Errors, Troubleshooting and Resolution

Error: When you configure mirroring you might receive the below errror, One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click start mirroring again

Solution: Assume that your principal server is A and mirror server is B and you have configured mirroring for Adventure Works database. The fully qualified computer name of each server can be found running the command prompt:  

IPCONFIG /ALL

Concatenate the "Host Name" and "Primary DNS Suffix". If you see something like:-

Host Name . . . : A            Primary Dns Suffix . . . .  . : corp.mycompany.com

Then the computer name is just A.corp.mycompany.com. Prefix 'TCP://' and append ':' and you then have the partner name.

-- Specify the partner from the mirror server
ALTER DATABASE [AdventureWorks] SET PARTNER = TCP://A.corp.mycompany.com:5022';

-- Specify the partner from the principal server

ALTER DATABASE [AdventureWorks] SET PARTNER = N'TCP://B.corp.mycompany.com:5022';


Error: 1412: Occurs if we have not restored T.Log backup along with FULL backup in mirror server before configuring mirroring.

Error: 1416, Database is not configured for database mirroring
You need to restore the Full backup from principal server using With NoRecovery option and also one T.log backup from principal server using With NoRecovery option and then start configuring mirroring.

Error: 1418(Can’t be reached) :Occurs due to the communication problems b/w the end points. Possible reasons are TCP/IP for the instance was not enabled, Duplicate end points exist, and Login was not created in their instance to mirroring.

Error: 1475: Occurs if any bulk operations are made in production server before enabling mirroring. To resolve this take T.Log backup from principal and restore in mirror then enable mirroring.

Error: 1478: Occurs If all T.Log Backups are not restored before starting mirroring

Error: 1498 This is a common error & everyone is known to this error. Database mirroring is officially supported from SQL Server 2005 SP1, hence in the RTM version database mirroring is disabled by default. You can use TRACE 1400 to enable in RTM version or else update your SQL Server with latest service pack.

Error: - The witness instance name must be distinct from both server instance that manage the database. The alter database set witness command failed (Microsoft sql server error:1429) 




Error: - If we select witness SQL Server instance in single machine. It does not allow to configure Witness server. Both Mirror and Witness server take same port as 5023 and the below error will come.



Error:- Occur when mirror DB is in In Recovery Mode. To Fix it Restore full backup and T. Log back up on Mirror Server.



Error: - After patching principal moved in suspended mode. And not coming in synchronized mode.

While patching patch, Mirroring failover must have done by SQL server Service account only. If DBA do failover by his/her own account before patching Mirroring will not move in suspended mode and will not come in synchronized mode. Then only reconfiguration is the option to run mirroring.

Error: principal moved in suspended mode and not move in synchronized mode.

If the log file size is full mirroring gets failed. To Fix it, release log file size.

Error: 1418- After configuring a mirroring. It asks to start mirroring. While starting Mirroring 1418 error occurs.
Bellow 3 instances are installed and all instance should run with same Service account (Not user account)



 Error: In My principal server while I start Mirror The server network address "TCP://NABONIDUS.infrasoftcorp.com:5022" cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. SQL Server 2008 - 1418.   

The solution to the above problem is very simple and as follows.

Fix/WorkAround/Solution: Try all the suggestions one by one.

Suggestion 1: Make sure that on Mirror Server the database is restored with NO RECOVERY option (This is the most common problem).

Suggestion 2: Make sure that from Principal the latest LOG backup is restored to mirror server. (Attempt this one more time even though the full backup has been restored recently).

Suggestion 3: Check if you can telnet to your ports using command TELNET ServerName Ports like “telnet SQLServerName 5023.

Suggestion 4: Make sure your firewall is turned off.

Suggestion 5: Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. You can start end point by executing an ALTER ENDPOINT statement.

Suggestion 6: Try the following command as one of the last options.

GRANT CONNECT ON ENDPOINT::Mirroring TO ALL

Suggestion 7: Delete the end points and recreate them.

Please comment if you know or faced any error of Mirroring.- Jainendra Verma


Tuesday 22 April 2014

SQL SERVER – Everything about Indexing with all details

Most important 60 Question and Answer on SQL Server Index-

Note:-  I have mentioned ready made indexing Script for all types of indexes. To use just copy and run in UAT or Production 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)
6.      Spatial Index
7.      XML 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 indexes out of 256 indexes. 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 | NON-CLUSTERED  ] 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 sub query, 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 query on index view it not allowed any sub queries 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?

(a)            To create a non-clustered  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.

(b)           To create a non-clustered  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 non-clustered  index.
8.      Click OK.
9.      In the New Index dialog box, click OK.

(c)            To create a non-clustered  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 non-clustered  index called IX_ProductVendor_VendorID
-- on the Purchasing.ProductVendor table using the BusinessEntityID column.
CREATE NON-CLUSTERED  INDEX IX_ProductVendor_VendorID
    ON Purchasing.ProductVendor (ProductId);
GO
13.  Non-clustered  indexes are implemented in the following ways.

1. UNIQUE constraints
When you create a UNIQUE constraint, a unique non-clustered  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
2. Index independent of a constraint
The maximum number of non-clustered  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.
3. Non-clustered  index on an indexed view
After a unique clustered index has been created on a view, non-clustered  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?

(d)           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.

(e)            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.

(f)             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
Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes. When we see an Index created with some where clause then that is actually a FILTERED INDEX.

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?

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

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

(i)                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 NON-CLUSTERED  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
Above Script 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 stored the Indexing information?
In the Fill factor stored the indexing information. Important Creating an 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. Limitation of index?
·         A 900-byte size limit on indexes
·         We can only a maximum of 16 columns can be named in the index
·         The following columns with data types like nvarchar(max), text, and ntext cannot be used in indexes

38. What are the difference between clustered and a non-clustered index?
Cluster Index
Non-Cluster Index
A clustered index is a special type of index that it physically sorts the rows in the table.
A non-clustered  index is a special type of index in  It cannot sort the rows physically.
Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
The leaf node of a non-clustered  index does not consist of the data pages. Instead, the leaf nodes contain index rows.
If table consists of clustered index, then index pages consist of index key columns and clusteredindexkeycolumns.
Always we have to place first clustered index then non-clustered .
Both data and indexes are merged and sorted at 3rd level (Leaf level).
Both data and Index pages are sorted separately.
Queries that return large dataset and Queries that return Ranges.
Queries that don’t return large dataset and Queries that return exact matches.
Create clustered index pid_indx on prods (pid)
These indexes are rebuilded when Cluster index created/dropped/modified.
We can have up to 249 non clustered indexes in a table.
You can have up to 999 non clustered indexes from SQL Server 2008 onwards.

39. What is the diff between re-building and re-organize? When your use.

                                Re-building
                                 Re-Organize
1. Complete index is dropped and recreated. Here you can specify FillFactor.
1. Just index pages are placed in order of index key column volume. Here you can not specify Fill Factor
2.Fragmentaion >30% or if scan density < 75%
2. .Fragmentaion >5% &<30% or if scan density
> 75% &<95%
3.Statistics are updated
3.Not Updated
4.Take Long time
4.Take less time
5.Table is block if the option “online processing of DML…” is not enabled
5.Allows
6.Use Tempdb space
6.Min.space
7.Alter index indexname/All on <tname> rebuild
7.Alter index indexname/All on <tname> reorganize
8. It is offline operation
8. It is online operation
9.Option is available in all SQL Server 2005 edition
9.Option is available in SS 2005 EE and DE

40. DMV - sys.dm_db_index_usage_stats:

Ø  The seeks refer to how many times an index seek occurred for that index.  A seek is the fastest way to access the data, so this is good.
Ø  The scan refers to how many times an index scan occurred for that index.  A scan is when multiple rows of data had to be searched to find the data.  Scans are something you want to try to avoid.
Ø  The lookups refer to how many times the query required data to be pulled from the clustered index or the heap(does not have a clustered index).  Lookups are also something you want to try to avoid.
Ø  The updates refer to how many times the index was updated due to data changes which should correspond to the first query above.

41. What are “Table Scan’s” and “Index Scan’s”?
A table scan is where the table is processed row by row from beginning to end.
An index scan is where the index is processed row by row from beginning to end.
If the index is a clustered index then an index scan is really a table scan.

42. Which is faster a Table Scan, or a Clustered Index Scan?
Same speed in case a table has a clustered index that it’s always show index scan instead of table scan.

43. Readymade indexing Script or Readymade 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 NON-CLUSTERED  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

44. Limitation to Index: A 900 byte size limit on indexes.
  1. A max.of 16 columns can be named in the index.
  2. Column with data type like nchar (max), text, ntext can’t be used in indexes
  3. Column defined in the include statement called non-key column.Max.of 1023 index with included column additional can be used as non-key columns
45. Difference between P.K and Clustered Index?
Primary key enforce uniqueness and allows to establish relationship. But by default clustered index cannot..


46. Types of Views? Explain about Indexed View?
There are two types of Views 1) Standard View 2) Indexed Views.
An indexed View (materialized view) is like a standard view, and it has a unique clustered index created on it. It stored physically just like a table.
Advantage: - Indexed views works best for queries that aggregates many rows or have joins that cause response time of the standard view to be slow.

47. What are the different index configurations a table can have?
No indexes                                      
A clustered index                              
A clustered index and many non-clustered indexes
A non-clustered index                   
Many non-clustered indexes

Indexes cannot created in LOB (Large Object data), in the columns with data types like:  text, ntext, varchar (max), nvarchar (max), varbinary (max), xml, image.

49. What is Heap table? A table without a clustered index is called as heap table.
 
50. What is B-Tree?    The database server uses a B-tree structure to organize index information. B-Tree generally has following types of index pages or nodes:
  • Root node: A root node contains node pointers to branch nodes which can be only one.
  • Branch nodes: A branch node contains pointers to leaf nodes or other branch nodes which can be two or more.
  • Leaf nodes: A leaf node contains index items and horizontal pointers to other leaf nodes which can be many.

51. What is the diff between re-building and re-organize? When your use.

                                Re-building
                                 Re-Organize
1. Complete index is dropped and recreated. Here you can specify Fill Factor.
1. Just index pages are placed in order of index key column volume. Here you can not specify Fill Factor
2.Fragmentaion >30% or if scan density < 75%
2. .Fragmentation >5% &<30% or if scan density
> 75% &<95%
3.Statistics are updated
3.Not Updated
4.Take Long time
4.Take less time
5.Table is block if the option “online processing of DML…” is not enabled
5.Allows
6.Use Tempdb space
6.Min.space
7.Alter index indexname/All on <tname> rebuild
7.Alter index indexname/All on <tname> reorganize
8. It is offline operation
8. It is online operation
9.Option is available in all SQL Server 2005 edition
9.Option is available in SS 2005 EE and DE

52. What is row locator? 
An index row of a non-clustered index contains a pointer to the corresponding data row of the table; this pointer is called row-locator.

53. What is page splitting?
SQL Server want to write data at a specific place (because of an index), but if there is not enough room in the current page it has to split it in two. Page splitting can impair performance and fragment the storage of the data in a table. When creating an index, you can specify a fill factor to leave extra gaps and reserve a percentage of free space on each leaf level page of the index to accommodate future expansion in the storage of the table's data and reduce the potential for page splits.

54. Explain what partitioning is and what its benefit is. Answer
Partitioning is a method of taking large tables and indexes and then splitting them into smaller and more manageable pieces.  And the Advantages are-
1. Maintenance: -Can Take Partitioned tables’ tablespace offline for recovery while other partitions are still available to users hence the Availability.
2. Performace: -As the Objects are placed in Different Tablespaces of Different Servers/locations.

 

55. What is Fill factor? How to assign Fill factor?                                                                                                                                  
A Fill factor is a reserved free space on each leaf level page which is used for future growth of data or index in a table and reduces the page splits by default it is 100%.
Assign Fill Factor: - Right Click on Server > Properties > Database Settings > Default Index Fill Factor > Provide the value.

56. What is the best fill factor value?
It depends on the ratio of reads to writes that your application makes to your SQL Server tables. As a rule of thumb, we might follow these guidelines:
Ø  Low Update Tables (100-1 read to write ratio): 100% fill factor
Ø  High Update Tables (where writes exceed reads): 50%-70% fill factor
Ø  Everything In-Between: 80%-90% fill factor.
For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

57. DMV - sys.dm_db_index_usage_stats:

Ø  The seeks refer to how many times an index seek occurred for that index.  A seek is the fastest way to access the data, so this is good.
Ø  The scans refer to how many times an index scan occurred for that index.  A scan is when multiple rows of data had to be searched to find the data.  Scans are something you want to try to avoid.
Ø  The lookups refer to how many times the query required data to be pulled from the clustered index or the heap(does not have a clustered index).  Lookups are also something you want to try to avoid.
Ø  The updates refer to how many times the index was updated due to data changes which should correspond to the first query above.

58. What are “Table Scan’s” and “Index Scan’s”?
A table scan is where the table is processed row by row from beginning to end.
An index scan is where the index is processed row by row from beginning to end.
If the index is a clustered index then an index scan is really a table scan.

59. Which is faster a Table Scan, or a Clustered Index Scan?
Same speed in case a table has a clustered index that it’s always show index scan instead of table scan.

60. What are data objects?
Tables, Views, clusters, index, cluster and sequences are called data objects for a database