Create Index Specifying Key List - TSQL

by DBOn   Last Updated September 24, 2017 10:06 AM

I have a table Tally (Heap Table) having no Index , No Key , No Constraint at all on a table .

Total count of rows is 11856655

  • Total time taken for execution is 2 Minutes 50 seconds .

Can anyone please help me understand below :

  1. First of all I cannot create a Primary Key because I have duplicates in SectionID , correct ?
  2. Why there is no query quick retrieval even after creating Cluster Index ?
  3. Can I create a Non-Clustered Index in this case ? Will it be helpful ?
  4. What can I do please help me to speed up my query execution ?

I am using below query to get the complete output of this table and below is the output .

select top 10  from table

enter image description here

As I mentioned it has no Index , No Primary Key Constraint .

enter image description here

In this case :

I am trying to create a Clustered Index / Primary Key but I am getting below Issues .

ALTER TABLE [abc].[Table]
ADD  CONSTRAINT [PK_SectionD] PRIMARY KEY CLUSTERED (   sectionID ASC) 
ON [PRIMARY]

Msg 1505, Level 16, State 1, Line 1 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'Table' and the index name 'PK_SectionID'. The duplicate key value is (24200901). Msg 1750, Level 16, State 0, Line 1 Could not create constraint or index. See previous errors. The statement has been terminated.

Also I created a Clustered Index on SectionID successfully but no use at all :

CREATE CLUSTERED INDEX ON Table (SectionID)

Still no use , it takes close to 2 Minutes and 55 seconds ...

Please help me what else I can do to speed up execution .



Related Questions


Clustered vs Non Clustered Indexes for a Primary Key

Updated August 29, 2018 23:06 PM