-2

I am making a database in MS SQL.

And have a question about the long term database space occupy and performance issue.

I have a table of user can have different type of meta values.

Table_User
PK UserID

Table_User_Meta
PK UserMetaID int
FK UserID int
FK CreatedBy int
   CreateDate Datetime
   TextValue varchar(500) NULL
   DecimalValue decimal(18,0) NULL

For different usage the user might have different type of Meta Value, so that either the TextValue or DecimalValue will be null. Since the TextValue can be maximum of 500, is it will occupy too much space when there are millions of rows are coming with only Decimal Value?

I thought about another way to do this.

Table_User
PK UserID

Table_User_Meta_Text
PK UserMetaTextID int
FK UserID int
FK CreatedBy int
   CreateDate Datetime
   MetaDescription varchar(500)

Table_User_Meta_Decimal
PK UserMetaTextID int
FK UserID int
FK CreatedBy int
   CreateDate Datetime
   DecimalValue decimal(18,0)

This way I am using two separate table which there will be no null field. But this way will force me to read two tables once I need to retrieve data.

My question is which way will be better? For both space usage and performance in a long term condition. Probably there will be at least 100 millions of Users in the Table_User, and each User can have maximum two Meta regardless of Text or Decimal.

Thanks everyone!

Edit:

Explain about the possible duplicate question:

Thank you guys for answer my question and I think my question is not the same as this one SQL Server 2008, how much space does this occupy?

My question is more of which way is a good way to balancing space occupy and read/write performance. Because I never handled a database with large amount of data so I was kind of not sure which way is better for implementation.

The answers below still helps so I will consider more of the I/O performance than the space occupy. Thank you guys.

Community
  • 1
  • 1
kim
  • 45
  • 1
  • 7
  • What did you learn from reading the hits from googling your title? What is the obvious usage if we assume the sizes described for values in the documentation? And if we include indexes? – philipxy Feb 14 '17 at 03:06
  • Possible duplicate of [SQL Server 2008, how much space does this occupy?](http://stackoverflow.com/questions/4614771/sql-server-2008-how-much-space-does-this-occupy) – philipxy Feb 14 '17 at 03:47
  • @philipxy I was thinking about consider more for performance or space occupied.. Pretty much of need some suggestion for handling large database. this link helps a little but not the same.. still , thank you for your answer – kim Feb 14 '17 at 16:02
  • You are now basically asking for a chapter or more of a book or books on database optimization. Moreover you haven't given enough detail about particular requirements, and you even need to learn what details are relevant. This is all is way [too broad](http://stackoverflow.com/help/closed-questions). ("There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow down the answer set or to isolate an issue that can be answered in a few paragraphs.") Please read [ask] and other related help. And read a book. – philipxy Feb 15 '17 at 01:14

1 Answers1

0
  • At first, VARCHAR data type does not store unnecessary spaces, unless you specify something like this: ABC DEF. So do not worry about the space in that case.
  • At second, if you worry about space, use BIGINT instead of decimal(18,0) and do not worry about NULLs, which might save you a lot of space.
Slava Murygin
  • 1,951
  • 1
  • 10
  • 10