How do I optimize the following SQL query for performance?

How do I optimize the foll开发者_Python百科owing SQL query for performance?

select * from Employee where CNIC = 'some-CNIC-number'

Will using alias help making it a little faster?

I am using Microsoft SQL Server.

This is better if you tell us what RDBMS you are using, but...

1 - Don't do SELECT *. Specify which columns you need. Less data = faster query

2 - For indexing, make sure you have an index on CNIC. You also want a good clustered index on a primary key (preferably something like an ID number)

3 - You put the number in single quotes ' ' which indicates you may have it as a varchar column. If it will always be NUMERIC, it should be an int/bigint data type. This takes up less space and will be faster to retrieve and index by.

Create an index on CNIC:

CREATE INDEX ix_employee_cnic ON employee (cnic)

First thing, as I see this column will be used for storing Id card nos, then you can make your coulmn of type int rather than varchar or nvarchar as searching will faster on an integer type as compared to varchar or nvarchar.

Second, use with (no lock), like

select * from Employee with (nolock) where CNIC = 'some-CNIC-number'

This is to minimize the chances of a deadlock.