Use of large field definitions for small values

Hi
This is a question of "what does it cost me".

Lets say I have an integer value which would fit into a smallint field
but the field is actually defined as int or even larger as bigint.
What would that "cost" me ? How would definitions larger than I need for
the values in the field affect me ?
Its obvious that the volume of the database would grow but with the size
of resources etc that we have nowadays disc space isn't a problem like
it used to be and i/o is much faster and many people would tell me "who
cares" , or IS it a problem ?
How does it affect performance of data retrieves ? Searches ? Updates
and inserts ? How would it affect all db access if tables are pointing
at each other with foreign keys ?

Thanks !

David Greenberg

Re: Use of large field definitions for small values

On Aug 2, 1:46 pm, David Greenberg wrote:
> Hi
> This is a question of "what does it cost me".
>
> Lets say I have an integer value which would fit into a smallint field
> but the field is actually defined as int or even larger as bigint.
> What would that "cost" me ? How would definitions larger than I need for
> the values in the field affect me ?
> Its obvious that the volume of the database would grow but with the size
> of resources etc that we have nowadays disc space isn't a problem like
> it used to be and i/o is much faster and many people would tell me "who
> cares" , or IS it a problem ?
> How does it affect performance of data retrieves ? Searches ? Updates
> and inserts ? How would it affect all db access if tables are pointing
> at each other with foreign keys ?
>
> Thanks !
>
> David Greenberg

Less data can fit in a page....This will degrade performance.

Let say we have a single column table which is BigInt which you could
have declared as INT

over the period the data grown up to several hundered pages....say
10000 pages

If you could have used int instead of bigint it woluld have only
consumed 5000 pages for the same amount of data.


This is about storage. and caching

Now abt the CPU.
suppose you run a sum() on a coloumn of bigint it will require more
than twice the time of CPU as of Int. as CPU has to manupulate 8 bytes
instead of 4 bytes.


So your select will be slow,update/insert will be slow (more chances
of page split). Delete will be slow.
cache hits will be low (as low page fit on Memory).
CPU consumption will be high.
Backup/Restore would have been faster with the less pages.
That all I can think of now there may be more downsides

Hope it helps
Thanks
Shiju Samuel

Re: Use of large field definitions for small values

>> This is a question of "what does it cost me". <<

Data integrity. If you allow a NVARCHAR(50) for a ZIP code, like a
lot of newbies do, you will get a 50 character ZIP code in Chinese one
day. The cost in storage and access time is minimal compared to
getting the wrong answers.

Re: Use of large field definitions for small values

Hi David,

A lot of people who dev think single user when they are writing queries; say
a query costs 10,000 logical reads thats around 78MBytes of data SQL Server
needs to process (through memory and CPU); that is fine for a single
concurrent user but what happens when 100 people run it and now the box has
to process 78MBytes x 100 = 7,800MBytes? Memory speed becomes a limiting
factor, memory is only good for a few GBytes per second of data transfer.

So, to answer your question, if your data volumes are such and you just
bloat column sizes out then you will reduce your scalability threshold, you
will also increase the size of backups, the size of data transmitted between
server and clients (again, cost that out for the number of users you have
concurrently).

Anyway, this is academic because you should size columns acording to the
data that will fit in there; for instance if you had a column that held the
number of minutes in a day then we know that is finite and is between 0 and
1,440 so that will fit in smallint so that's all you need; no point in
making it int or especially not bigint.

Hope that helps David.

Tony

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"David Greenberg" wrote in message
news:46B199ED.5030205@iba.org.il...
> Hi
> This is a question of "what does it cost me".
>
> Lets say I have an integer value which would fit into a smallint field but
> the field is actually defined as int or even larger as bigint.
> What would that "cost" me ? How would definitions larger than I need for
> the values in the field affect me ?
> Its obvious that the volume of the database would grow but with the size
> of resources etc that we have nowadays disc space isn't a problem like it
> used to be and i/o is much faster and many people would tell me "who
> cares" , or IS it a problem ?
> How does it affect performance of data retrieves ? Searches ? Updates and
> inserts ? How would it affect all db access if tables are pointing at each
> other with foreign keys ?
>
> Thanks !
>
> David Greenberg
>