Getting started with Microsoft SQL ServerOVER ClausePIVOT / UNPIVOTDatabase SnapshotsRetrieve information about the databaseThe STUFF FunctionFOR XML PATHCursorsJoinCommon Table ExpressionsMove and copy data around tablesDatesLimit Result SetRetrieve Information about your InstanceWith Ties Option VariablesJSON in Sql ServerWindow functionsPartitioningStored ProceduresGROUP BYGenerating a range of datesCOALESCESplit String function in Sql ServerINSERT INTOCREATE VIEWString FunctionsResource GovernorORDER BYWHILE loopSystem database - TempDbMigrationPrimary KeysMERGEFull-Text IndexingFOR JSONSELECT statementDBMAILIndexQueries with JSON dataStoring JSON in SQL tablesOPENJSONRanking FunctionsTriggerConverting data typesNULLsTransaction isolation levelsAdvanced optionsIF...ELSETRY/CATCHData TypesUser Defined Table TypesTable Valued ParametersIn-Memory OLTP (Hekaton)Temporal TablesInsertSequencesSCOPE_IDENTITY()ViewsUse of TEMP TableScheduled Task or JobIsolation levels and lockingSorting/ordering rowsPrivileges or PermissionsForeign KeysSQLCMDFile Groupcross applyBasic DDL Operations in MS SQL ServerComputed ColumnsUNIONSubqueriesLast Inserted IdentityCLUSTERED COLUMNSTOREParsenameInstalling SQL Server on WindowsAggregate FunctionsQuerying results by pageSchemasBackup and Restore DatabaseTransaction handlingNatively compiled modules (Hekaton)Database permissionsSpatial DataDynamic SQLPaginationQuery HintsModify JSON textRow-level securityDynamic data maskingExport data in txt file by using SQLCMDEncryptionManaging Azure SQL DatabaseCommon Language Runtime IntegrationDelimiting special characters and reserved wordsCASE StatementDBCCBULK ImportQuery StoreService brokerAnalyzing a QueryMicrosoft SQL Server Management Studio Shortcut KeysPermissions and SecurityPHANTOM readFilestreamDrop KeywordString Aggregate functions in SQL ServerSQL Server Evolution through different versions (2000 - 2016)SQL Server Management Studio (SSMS)Logical FunctionsDynamic SQL PivotAlias Names in Sql Serverbcp (bulk copy program) Utility

Index

Other topics

Create Clustered index

With a clustered index the leaf pages contain the actual table rows. Therefore, there can be only one clustered index.

CREATE TABLE Employees
(
    ID CHAR(900),
    FirstName NVARCHAR(3000),
    LastName NVARCHAR(3000),
    StartYear CHAR(900)
)
GO

CREATE CLUSTERED INDEX IX_Clustered 
ON Employees(ID)
GO

Create Non-Clustered index

Non-clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values and each key value entry has a pointer to the data row that contains the key value. There can be maximum 999 non-clustered index on SQL Server 2008/ 2012.

Link for reference: https://msdn.microsoft.com/en-us/library/ms143432.aspx

CREATE TABLE Employees
(
    ID CHAR(900),
    FirstName NVARCHAR(3000),
    LastName NVARCHAR(3000),
    StartYear CHAR(900)
)
GO

CREATE NONCLUSTERED INDEX IX_NonClustered
ON Employees(StartYear)
GO

Show index info

SP_HELPINDEX tableName

Index on view

CREATE VIEW  View_Index02
WITH SCHEMABINDING
AS 
SELECT c.CompanyName, o.OrderDate, o.OrderID, od.ProductID 
     FROM dbo.Customers C 
        INNER JOIN dbo.orders O ON c.CustomerID=o.CustomerID  
            INNER JOIN dbo.[Order Details] od ON o.OrderID=od.OrderID   
GO

CREATE UNIQUE CLUSTERED INDEX IX1 ON 
    View_Index02(OrderID, ProductID) 

Drop index

DROP INDEX IX_NonClustered ON Employees

Returns size and fragmentation indexes

sys.dm_db_index_physical_stats (   
    { database_id | NULL | 0 | DEFAULT }  
  , { object_id | NULL | 0 | DEFAULT }  
  , { index_id | NULL | 0 | -1 | DEFAULT }  
  , { partition_number | NULL | 0 | DEFAULT }  
  , { mode | NULL | DEFAULT }  
)  


Sample :

SELECT * FROM sys.dm_db_index_physical_stats  
    (DB_ID(N'DBName'), OBJECT_ID(N'IX_NonClustered '), NULL, NULL , 'DETAILED');  

Reorganize and rebuild index

avg_fragmentation_in_percent valueCorrective statement
>5% and < = 30%REORGANIZE
>30%REBUILD
ALTER INDEX IX_NonClustered ON tableName REORGANIZE;  


ALTER INDEX ALL ON Production.Product
 REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
          STATISTICS_NORECOMPUTE = ON);

Rebuild or reorganize all indexes on a table

Rebuilding indexes is done using the following statement

ALTER INDEX All ON tableName REBUILD;

This drops the index and recreates it, removing fragementation, reclaims disk space and reorders index pages.

One can also reorganize an index using

ALTER INDEX All ON tableName REORGANIZE;

which will use minimal system resources and defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes

Rebuild all index database

EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'

Index investigations

You could use "SP_HELPINDEX Table_Name", but Kimberly Tripp has a stored procedure (that can be found here), which is better example, as it shows more about the indexes, including columns and filter definition, for example:
Usage:

USE Adventureworks 
EXEC sp_SQLskills_SQL2012_helpindex 'dbo.Product'

Alternatively, Tibor Karaszi has a stored procedure (found here). The later will show information on index usage too, and optionally provide a list of index suggestions. Usage:

USE Adventureworks 
EXEC sp_indexinfo 'dbo.Product' 

Contributors

Topic Id: 4998

Example Ids: 17622,17623,17624,17625,17974,17975,17976,17977,17978,18853

This site is not affiliated with any of the contributors.