Testing SQL queries on large datasets

I usually test with 1,000,000 records. That enough data to see how different of query in 0.001 sec but you should tried at least 3 times for see an average time. (you should test on not index data and index data to see how different)

You're not going to get realistic information testing with a smaller data set.

The reason is, that a smaller data set will fit better into the ram.

Try a full-size (possibly simulated) data set, on non-production production-grade hardware. Sadly setting this up is often expensive (the hardware isn't too expensive; developer time to write software to generate a production-size production-like data set IS)

Having a non-production server on proper hardware with a production-like data set has been invaluable to me; we simply can't go around testing things in production.

NOTE:

• Production-grade server - say $20k one-off
• Developer time - > $100k per year, plus the cost of having them not working on "real" software.

Normalized is probably your better bet, but only a simulate work load will know for sure. You're comparing 50 increasingly sparse indexes of 1 million rows each vs 1 index of 50 million rows.ugg slippers I suspect that if I was a genius at MS writing a algorithm to search one index, I would pick up the values I was looking for as I went a long in one pass.