which is more speed cluster index or noncluster index and why ?

ahmed salah 3,216 Reputation points
2021-01-05T23:17:17.4+00:00

which is more speed cluster index or noncluster index and why ?

I make cluster index on temp table and noncluster index on same temp table

I notice that cluster index is more speed from noncluster index

are this correct and why ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,018 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,657 questions
{count} votes

3 answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,201 Reputation points
    2021-01-06T02:02:06.31+00:00

    Hi @ahmed salah ,

    I disagree that a clustered index is always faster than a non-clustered index.

    A clustered index may be the fastest for one SELECT statement but it may not necessarily be correct choice.

    SQL Server indices are b-trees. A non-clustered index just contains the indexed columns, with the leaf nodes of the b-tree being pointers to the approprate data page. A clustered index is different: its leaf nodes are the data page itself.

    53861-clustered-vs-nonclustered-indexes.png

    A clustered index determines the order in which records are physically stored (which is why you can only have one per table). So while it would be the fastest for one query, it may slow down other queries and could KILL updates and inserts if one of those columns was changing, which could mean that the record would need to be physically re-located.

    It could be better if we could find out a proper non-clustered index and get the performance to a reasonable level.

    Reference:Is a clustered index faster than a non-clustered index with includes?

    There is also a good article recommended to you and you could refer more details from it.

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Erland Sommarskog 112.7K Reputation points MVP
    2021-01-06T11:29:33.293+00:00

    There is a standard answer to these type of performance question: "it depends".

    All depending on your query one index may be faster than the other. There is no one simple answer that is always valid.

    0 comments No comments

  3. Dan Guzman 9,236 Reputation points
    2021-01-06T13:20:16.03+00:00

    Elaborating on what Erland's said, "it depends" much on the queries as to what type of index is most beneficial and why. The optimizer generally chooses the index and plan that can return results as efficiently as possible, considering the overall costs from the candidate plans.

    Consider this simple table and query examples.

    CREATE TABLE dbo.YourTable(
      YourTableID int NOT NULL
     CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED
     ,CreateTimestamp datetime2(7) NOT NULL
     INDEX idx_YourTable_CreateTimestamp NONCLUSTERED
     ,Name varchar(30) NOT NULL
    );
    GO
    WITH 
      t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
     ,t10k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS row_num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c CROSS JOIN t10 AS d)
    INSERT INTO dbo.YourTable(YourTableID, CreateTimestamp, Name)
     SELECT row_num, DATEADD(minute, (row_num -1) / 5, '2021-01-01T00:00:00'), 'Name' + CAST(row_num AS varchar(10))
     FROM t10k;
    UPDATE STATISTICS dbo.YourTable;
    GO
    

    Without a WHERE clause, SQL Server chooses the narrowest index that covers the query:

    --Clustered index scan to return all rows and columns. Clustered index scan avoids the key lookup for each row to retrieve the Name column if the non-clustered index were scanned.  
    SELECT YourTableID, CreateTimestamp, Name FROM dbo.YourTable;
    
    --Nonclustered index scan to return subset of columns because the leaf nodes include the CreateTimestamp index key plus the clustered index key as the row locator (covering index).
    SELECT YourTableID, Name FROM dbo.YourTable;
    

    When a WHERE clause is used, the optimizer estimates row counts and costs of candidate plans using avaialble indexes and chooses the one it estimates to be least costly:

    --Clustered index seek to return a single row with all columns by primary key. Clustered index seek avoids the key lookup to retrieve the Name column if the non-clustered index were used. 
    SELECT YourTableID, CreateTimestamp, Name FROM dbo.YourTable WHERE YourTableID = 1;
    
    --Clustered index scan to return a single row with all columns. No useful index exists on Name. Clustered index scan avoids the key lookup for each row to retrieve the Name column if the non-clustered index were scanned. 
    SELECT YourTableID, CreateTimestamp, Name FROM dbo.YourTable WHERE Name = 'Name1';
    
    --Non-clustered index seek plus key lookup to return 5 rows with all columns. The key lookup is less costly with few rows.
    SELECT YourTableID, CreateTimestamp, Name FROM dbo.YourTable WHERE CreateTimestamp = '2021-01-01T00:00:00';
    
    --Non-clustered index seek plus key lookup to return 10 rows with all columns. The key lookup is less costly with few rows.
    SELECT * FROM dbo.YourTable WHERE CreateTimestamp BETWEEN '2021-01-01T00:00:00' AND '2021-01-01T00:01:00';
    
    --Clustered index scan to return 30 rows with all columns. Scan avoids costly key lookup when more rows are returned.
    SELECT * FROM dbo.YourTable WHERE CreateTimestamp BETWEEN '2021-01-01T00:00:00' AND '2021-01-01T00:05:00';
    

    A good indexing strategy needs to consider commonly executed queries and the overall workload. There are tradeoffs involved in storage and maintenance costs. Again, "it depends".

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.