SQL Query - 20 Mile Entries - Best Practice for Returning Information

Question

I have an SQL database that has the following table:

Table: PhoneRecords
--------------
ID (identity seed)
Firstname
Lastname
Phone number
Zipcode

Very simple straight table. This table has over 20 million records. I'm looking for a better way to make queries that pull records based on area codes from a table. For example, here is an example of a request that I made.

SELECT phonenumber, firstname
FROM [PhoneRecords]
WHERE (phone LIKE '2012042%') OR
      (phone LIKE '2012046%') OR
      (phone LIKE '2012047%') OR
      (phone LIKE '2012083%') OR
      (phone LIKE '2012088%') OR
      (phone LIKE '2012841%')

As you can see, this is an ugly request, but it would do the job (if I hadn't worked in timeouts)

- / , ? 2 , 9- 1600- RAM, i7 930 quadcore OC'd 4.01ghz. , , , , .

Answer 1

, .

CREATE INDEX IX_PHONERECORDS_PHONENUMBER_FIRSTNAME 
  ON dbo.PhoneRecords (PhoneNumber) INCLUDE (FirstName)

, (CTRL+M).

Answer 2

phone. , .

, UNION ALL OR, . , , , , . , :

SELECT phonenumber, firstname FROM [PhoneRecords] WHERE phone LIKE '2012042%'
UNION ALL
SELECT phonenumber, firstname FROM [PhoneRecords] WHERE phone LIKE '2012046%'
UNION ALL
SELECT phonenumber, firstname FROM [PhoneRecords] WHERE phone LIKE '2012047%'
UNION ALL
SELECT phonenumber, firstname FROM [PhoneRecords] WHERE phone LIKE '2012083%'
UNION ALL
SELECT phonenumber, firstname FROM [PhoneRecords] WHERE phone LIKE '2012088%'
UNION ALL
SELECT phonenumber, firstname FROM [PhoneRecords] WHERE phone LIKE '2012041%'

.

, SCAN INDEX SCAN.

Answer 3

- ? PhoneNumber. ( ), "AreaCode", PhoneNumber. AreaCode.

Answer 4

: ? , , , . , , , , where, , , . , - , , .

Answer 5

: [ ], [ ]

, " " , / , CLUSTERED [Area Code], .

, .

Answer 6

" " " ".

, int; .

AreaCode = 2012042

,

PhoneNumber LIKE '2012042%'

Answer 7

( , , ), , 2 . , sql server, , , sqlserver . , , + , sql, .

Answer 8

http://igoro.com/archive/precomputed-view-a-cool-and-useful-sql-pattern

, n . . , . , . union, .

, 20 000 000 , x - , , . , , . O (log n) .