Issue with the big tables ( no primary key availab

Question

Tabe1 has around 10 Lack records (1 Million) and does not contain any primary key. Retrieving the data by using SELECT command ( With a specific WHERE condition) is taking large amount of time. Can we reduce the time of retrieval by adding a primary key to the table or do we need to follow any other ways to do the same. Kindly help me.

Answer 1

Primarykey will not help if you don't have Primarykey in where cause.

If you would like to make you quesry faster, you can create non-cluster index on columns in where cause. You may want include columns on top of your index(it depend on your select cause)

The SQL optimizer will seek on your indexs that will make your query faster. (but you should think about when data adding in your table. Insert operation might takes time if you create index on many columns.)

Answer 2

It depends on the SELECT statement, and the size of each row in the table, the number of rows in the table, and whether you are retrieving all the data in each row or only a small subset of the data (and if a subset, whether the data columns that are needed are all present in a single index), and on whether the rows must be sorted.

If all the columns of all the rows in the table must be returned, then you can't speed things up by adding an index. If, on the other hand, you are only trying to retrieve a tiny fraction of the rows, then providing appropriate indexes on the columns involved in the filter conditions will greatly improve the performance of the query. If you are selecting all, or most, of the rows but only selecting a few of the columns, then if all those columns are present in a single index and there are no conditions on columns not in the index, an index can help.

Without a lot more information, it is hard to be more specific. There are whole books written on the subject, including:

• Relational Database Index Design and the Optimizers

Answer 3

One way you can do it is to create indexes on your table. It's always better to create a primary key, which creates a unique index that by default will reduce the retrieval time .........

The optimizer chooses an index scan if the index columns are referenced in the SELECT statement and if the optimizer estimates that an index scan will be faster than a table scan. Index files generally are smaller and require less time to read than an entire table, particularly as tables grow larger. In addition, the entire index may not need to be scanned. The predicates that are applied to the index reduce the number of rows to be read from the data pages.

Read more: Advantages of using indexes in database?

Answer 4

A primary key does not have a direct affect on performance. But indirectly, it does. This is because when you add a primary key to a table, SQL Server creates a unique index (clustered by default) that is used to enforce entity integrity. But you can create your own unique indexes on a table. So, strictly speaking, a primary index does not affect performance, but the index used by the primary key does.

WHEN SHOULD PRIMARY KEY BE USED?

Answer 5

Primary key is needed for referring to a specific record.

To make your SELECTs run fast you should consider adding an index on an appropriate columns you're using in your WHERE.

E.g. to speed-up SELECT * FROM "Customers" WHERE "State" = 'CA' one should create an index on State column.