Data Type size

Question

Data Type size

kaladharreddy15

SSC-Addicted

Points: 476
More actions
March 9, 2010 at 10:01 pm

#156762

Why do we want to keep field datatypes as small as possible in SQL Server 2005?
Thanks
Kala

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GTR SSCrazy Eights Points: 8007 More actions · Answer 1

Because even if you don't use complete space for particular data types it will allocate and reserve the maximum size, you will be wasting resources.

EnjoY!

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

GTR (3/9/2010)
Because even if you don't use complete space for particular data types it will allocate and reserve the maximum size, you will be wasting resources.

I don't believe that's true for variable character length datatypes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 3

kaladharreddy15 (3/9/2010)
Why do we want to keep field datatypes as small as possible in SQL Server 2005?

We do this in order to fit the maximum number of fields on a data/index page.

Dense data can be processed more efficiently.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sanjay-940444 SSC-Addicted Points: 422 More actions · Answer 4

but if we are using varchar datatype sql s. will reserve space as much required for actual value.Any say??

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

Sanjay-940444 (8/5/2010)
but if we are using varchar datatype sql s. will reserve space as much required for actual value.Any say??

Yep... how many bytes does it take to store the number -2000000000 as a VARCHAR? As an INT?

How many bytes does it take to store 10 Jan 2010 15:30 as a VARCHAR. As a DATETIME?

And that's before we get into all of the other disadvantages.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)