How to 'thin' a database table out?

Question

I have a large DB table which I am using for testing. It contains 7.3m phone call records. I would like to delete many of those, but still maintain a good spread over phone numbers and dates. Is there any way of achieving this? Maybe something to do with table sample?

Answer 1

Delete where the id finishes in a 1 or 6? Or similar, depending on exactly how many you need to remove.

i.e. to keep just 10% of the records for testing delete all the records that don't end in (say) 7.

(Note that a delete like this could take a while. You might be better doing a CREATE TABLE AS with just the records you need.)

Answer 2

Copy the data you want to keep:

SELECT TOP 1000 * INTO dbo.Buffer FROM Data.Numbers ORDER BY NewID()

Delete all data:

TRUNCATE TABLE Data.Numbers

Move back the kept data

INSET INTO Data.Numbers(column list) SELECT FROM dbo.Buffer