What type of data is used for year and month?

Question

I need to store the year and month in my table with two other columns next month, the topic and the subject of the detail, what data type should I use

should I use smalldatetime and store them by date, e.g. 09/01/2011 ?

should I use two varchar or int columns to store a separate year and month, for example 2011 09 ?

should I use a single varchar column that stores them with their concatenation for example 201109 ?

EDITED

one thing i also want to add is that i have to use these columns when searching as well, so i have to use them in the place where the sentence so in response to this thing is also important

Answer 1

As a rule, I would recommend using date types, regardless of your restrictions, as this allows you to do date manipulations and comparisons. Then you can use triggers to limit dates to the first month. You will need triggers so that you cannot get two rows in one month / year.

Actually, since Damian_The_Unbeliever rightly points out in the comment, you do not need triggers if you intend to allow users to only insert dates when the day of the month is 1. In this case, restrictions will probably suffice. This is only the case when you want users to try to insert any date, but actually forced it to become the first day of this month, these triggers will be required.

However, in this case, I would be pleased with the biennial setting of the year / month based on your use case.

Using two integer-type columns, you don’t have to worry about triggers, and you don't seem to need to bulk process the contents of the table based on the column specifications.

I would not save them as a single integer column if you ever foresee the need for data processing for a given year (regardless of the month). Having a separate year will allow an excellent index, which is likely to be faster than getting a range of YYYYMM values.

Seriously, choose the one that, in your opinion, will be the easiest to encode (and meets the functional requirements). Then, if and only if you find a performance problem, take a look at the reorganization scheme. Databases are not set and do not forget about things, you must constantly monitor them for problems and, if necessary, change things.

Answer 2

If you need to do a search in these fields, I would use the date format. Two ints will use more space, varchar should be replaced with char, because the size will be fixed. But, as I mentioned, I kept to the date format.

Answer 3

I suggest having two seprate columns for a month and a year:

 Month TINYINT Year INT