sql server programming: Will there be any difference in performance if I use a NUMERIC (with
precision =0) datatype as Primary Key in place of an INT datatype .
I have tested this scenario with ample data but couldnt see any difference
in Performance. Though it is recommended that REAL or FLOAT should not be
used as Primary Keys, will NUMERIC data type with Precision 0 may hinder

Unless you have a real need to store more than 10 digits (actualy >2^31-1),
storing as numeric will cost more. Consider using bigint (8bytes) instead.

The bigger the column the more expensive it is to maintain (i.e. storage,
index).

http://msdn.microsoft.com/library/en-us/tsqlref/ts_de-dz_3grn.asp
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ia-iz_3ss4.asp



--
-oj



[quoted text, click to view]

To add to oj's reply, SQL Server stores all of it's data on 8k byte
"pages". The bigger the column(s) i n an index, the "wider" each entry in
the index will be, and the fewer index entries can be placed on each 8k Page
of storage on disk. This means more pages are required to store the index
on disk.
The pages in indices are structured in a kind of "pyramid" , or upside
down tree, with the entry point page at the top, or root, and the pages
filled with actual row data (or pointers to the row data) at the bottom or
leaf level. So,, the more index pages required, the deeper (More levels)
this structure may be, and the more Disk IOs may be required to "traverse" it
from top to bottom.

[quoted text, click to view]