|
|
Posted by Erland Sommarskog on 05/04/07 21:17
(rshivaraman@gmail.com) writes:
> Erland, i will post answers to your queries shortly.
> I was looking at a datatype of a column and it a numerica 9(15,0),
> occupies 9 bytes but allowed a max lenght of 15.
> This can also be a varchar field. So a varchar(15) compared to a
> numeric 9(15,0)
> Which occupies more space? or they are the same ?
They are not the same. Each numeric(15,0) value occupies nine bytes,
including NULL values. (Unless you are on SQL 2005 SP2 and use the
new vardecimal feature.)
A varchar(15) values can occupy anything from 2 to 17 bytes. That is, two
bytes for the length and then as many bytes as needed for the value.
I don't really understand why you are making these considerations, but
if you are choosing between the two for a key value, I would recommend
a numeric type (and rather bigint over numeric(15,0)), since varchar
is subject to more complex sorting and comparison rules (unless you
pick a binary collation.)
I looked at your SHOWCONTIG and spaceused data. The table has some
fragmentation, but it is not frightening. And the table certainly
calls for respect with its 125 GB.
I think you should examine exactly what queries that are run against
this table. With a table this size, you really to have indexes to
support all queries.
I guess that since the table has so low rate of fragmentation, that
data is only inserted, but never updated or deleted. Therefore it
may not be a pressing issue to add a clustered index - an operation
given the size of the table that will take some time.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|