what type of data type use for year and month?

Question

i have to store year and month in my table with two other columns next month theme and theme details what data type should i use

should i use smalldatetime and store them by making a date like 09/01/2011 ?

should i use two columns of varchar or int to store separate year and month like 2011 09 ?

should i use one column varchar in which store them with concatenating them like 201109 ?

EDITED

one thing i also want to add that i have to use these columns in searching also so i have to use them in where clause so in answer this thing is also important

Answer 1

Normally, I would advise to use the date date types no matter what your restrictions, since it allows you to do date manipulations and comparisons. You could then use triggers to restrict dates to the first of the month. You'd need triggers to ensure that you couldn't get two rows for a single month/year combo.

Actually, as Damian_The_Unbeliever rightly points out in a comment, you don't need triggers if your intent is to only allow users to attempt insertion of dates where the day of the month is 1. In that case, constraints will probably be enough. It's only the case where you want to allow users to attempt to insert any date but actually force it to become the first day of that month, that triggers would be required.

However, in this case, I'd be quite happy with a two-column year/month setup, based on your use case.

By using two integer-type columns, you don't need to worry about triggers, and you don't seem to have a need for massive processing of the table contents, based on your column specifications.

I wouldn't store them as a single integer-type column if you ever foresee the need to process data for a given year (independent of the month). Having the year separate will allow a distinct index which will probably be faster than getting a range of YYYYMM values.

Seriously, choose the one that you think will be easiest to code up (and meets the functional requirements). Then if, and only if, you discover a performance problem, look into a schema re-org. Databases are not set-and-forget things, you should be constantly monitoring them for problems and, if necessary, changing things.

Answer 2

If you ever need to make lookups on these fields I would use a dateformat. Two int will use more space, varchar should be replaced with char, cause you size is fix. But as I mentioned, I would stick to a dateformat.