How to improve performance of a table that does not have a Primary key

_{Monday, June 15, 2015 11:52 AM}

Hi Guys,

I have a table with 1 million rows. I developed a system using a subset of this data for a demo. Now I need to use the full record set. My table did not have a primary key and I was not keen in adding any column as one since it was a small subset of data.

Now when I try to add a primary key I am unable to add one. I tried different combinations and even tried a composite key but nothing worked. Looking at the data I guess I would have to work without a primary key.

Any suggestions how I could improve query performance?

Please let me know

Thanks

Lucki

_{Wednesday, June 17, 2015 6:03 AM ✅Answered | 1 vote}

Sure, here you go:

Assuming your table name is tblTransactions having several columns & you want to create indexes so this is what your query should look like (Samples, you can pick the relevant one for you):

-- Creating Multi-Column Clustered Index
CREATE CLUSTERED INDEX IDX_State_City ON tblTransactions ( State, City )

-- OR Creating Single-Column Clustered Index
CREATE CLUSTERED INDEX IDX_State ON tblTransactions ( State )

-- OR Creating Single Column Non-Clustered Index
CREATE INDEX IDX_State ON tblTransactions ( State )

-- OR Creating Multi-level Column Non-Clustered Index
CREATE INDEX IDX_State ON tblTransactions ( State, City )

Note: You need to define/change the above code to meet your table structure design

Hope this helps !

Good Luck!
Please Mark This As Answer if it solved your issue.
Please Vote This As Helpful if it helps to solve your issue

_{Monday, June 15, 2015 12:14 PM}

It will help us help you if you post your CREATE TABLE DDL and query. You didn't mention what error you are getting when attempting to add a primary key but I assume the error is due to duplicate values. The primary key must be unique.  One or more columns may be specified as the primary key.  If no set of columns uniquely identifies a row, that's an indicator of a design flaw.

In a relational database, all tables should typically have a primary key.  In SQL Server, tables should also generally have a clustered index, which may or may not be the primary key index.  The best choice of the clustered index depends on your data access patterns.

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

_{Monday, June 15, 2015 12:25 PM | 2 votes}

Hello - Indeed it is always ideal to have PRIMARY KEY but even then you have following choices to make:

• You can still add CLUSTERED Index (Composite or Single column ) to your table. This should be based on your potential Search criteria. This will optimize your searches and help you optimize to retrieval of data. I advise you tp NOT to make it too wide. Prefer the columns that will be form your most search queries and the one that forms the group of rows
• Since having clustered index is NOT always enough, you should also consider adding Secondary Indexes to cover other queries that are not Searching based on your clustered Index.

Hope this helps

Good Luck!
Please Mark This As Answer if it solved your issue.
Please Vote This As Helpful if it helps to solve your issue

_{Monday, June 15, 2015 12:46 PM}

Hi Guys,

My data doesn't have any columns that uniquely identifies the rows. This is data dump from a data source. So I cant get the exact structure of it. The error message is duplicate values.

If I don't have a Primary key defined, can I still create a Clustered Index?

Thanks

Lucki

_{Monday, June 15, 2015 1:00 PM}

You can add a column as an IDENTITY property and declare it as a primary key (unique clustered index)

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Monday, June 15, 2015 5:01 PM}

Yes why not, clustered index does not enforce uniqueness. You can definitely create clustered index. Primary key is just an example of UNIQUE CLUSTERED INDEX that is it

Good Luck!
Please Mark This As Answer if it solved your issue.
Please Vote This As Helpful if it helps to solve your issue

_{Wednesday, June 17, 2015 5:56 AM}

Hi Manu,

When I tried to create the clustered index based on set of columns from the designer, it seems this option is disabled. Is this because I do not have a primary key defined?

Can I create the clustered key using SQL scripts? If so cn u please provide me an example.

Thanks

Lucki