You can use Profiler to se how many reads, how much cpu etc.
MSDN: How To use SQL Profiler[
^]
If you just want to measure speed you can use this technique:
DECLARE @start DateTime
DECLARE @end DateTime
SET @start = getDate()
SET @end = getDate()
SELECT DATEDIFF(ms, @start, @end)
Another good tip is to turn on the execution plan:
SET SHOWPLAN_ALL ON
GO
That will give you a lot of interesting data about performance
Remember to turn it off after you are done:
SET SHOWPLAN_ALL OFF
GO
You can read more about what all those columns and numbers mean at MSDN:
http://msdn.microsoft.com/en-us/library/aa259203(v=sql.80).aspx[
^]