CODEWITHSUNDEEP
performancesql-server-2008select
SaintJob 2.0
SaintJob 2.0

Reputation: 574

Fastest way to SELECT a row from a table in a database (Microsoft SQL server)

I have a huge table with one int PRIMARY KEY IDENTITY column.

I guess making the SELECT query using that primary key is the fastest way for the database to find the row in the table isn't it?

If that is true i still have a question. Is that query as fast as a call to a dictionary by key or the database still has to read all the rows from the beginning (the Primary Key column) till it finds the row itself?

Thanks in advance ^^

Upvotes: 2

Views: 1933

Answers (2)

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

Using primary key is obviously the fastest way to access a particular row.

If you want to understand how it works, you have to understand how index works.

In general it works like that :

Let's say you have a table t1(col1,col2...col10) and you have an index on col1. Index on col1 means that you have some data structure which contains pairs (col1, rec_id) and rec_id allows direct access to row with appropriate col1. The data structure is ordered by col1 and therefore allows efficient searching by col1.

Upvotes: 1

D.S
D.S

Reputation: 1150

I think searching in dictionary works per dictionary search algorithm which should be more like binary search kind.

When you declare a column as Primary key in table, then that column is indexed, hence it should be working based on hashing principle, so searching is definitely NOT row by row as you mentioned.

Finally, yes it is the common and fast way, but you should be selective about the number of columns and rows you need in your sql query. Avoid fetching large number of rows per select call.

Upvotes: 0

Related Questions