Best pratice to store dates group by months-year key par value

I am making one service which needs to hold data grouped and calculated by year-month combination. I know how to calculate data and put it on new table. But I am confused what kind of data type I should use for storing month - year value. Here is what I have considered.

• Two Integer columns one for year other for month (clear to understand and strong to manipulate (anybody who uses this table can easily use ranges and orders) but I think that would take more space than another solution.

• Use a Date field, and always store one date for entire month. (Hard to explain to users how it works and how to create WHERE clauses)

• One varchar(7) column and put there stings like 2012-02,2013-01. For me this is easy to understand but hard to manipulate.

Whick I should pick? or is there other solution. Can someone suggest according to performance which way is going to be good solution, Because most of queries is going to using data range in where solution and my new table will have around 2-5 million records.

I'd use a date field with the 1st of a month and a CHECK constraint to ensure it stays day 1.

This keeps it in the native date/time format (which is your observation about option 3)

Option 1 would requires less storage, but complicates comparisons. And 5 million rows isn't much: you use less storage but add code and query complexity

StackExchange Database Administrators Q#14800, answer score: 9