Tuesday, March 24, 2015

What data type we should preferably use for storing flag values in sql server

This question came to my mind some time back, when I was working on a project with a database having billions of records, spanned accross 20 financial years. I saw that for most of the flag fields Int data type was used. I was curious to know that if size of field impacts the performace of database opeartions. I posted a question for the same on stackoverflow and finally I found the solution by the help of a link provided by a stack overflow member.

What I found as answer is that we should consider using smallest data type sufficient enough to store flag values required. This is with respect to the performance of database as storage size is not a big concern these days.

Have a look on this link for more insights.

Rows and index entries are stored in 8k pages. So a million rows at 3 bytes per row isn't 3 MB on disk: it affects the number of rows per page ("page density").

The same applies to nvarchar to varchar, smalldatetime to datetime, int to tinyint etc

This article describes how the dataype and rowsize matters for DML operations in a database.

No comments:

Post a Comment