CODEWITHSUNDEEP
c#sql-serverentity-frameworksqldatatypes
Krishna Sarma
Krishna Sarma

Reputation: 1860

INT vs TinyInt vs Char(1) in SQL when using with EF

I have a scenario to store small range of status values (N/0 - No Access, P/1 - Partial, F/2 - Full, O/3 - Other). What is the right field type in SQL Server to go with (while using in conjunction with EF 6.x?

As per my understanding, each of these options has their own merits and fall backs (though this is not a complete set - Probably someone can add to make it complete). What is the right choice in terms of better system (with a balance of performance and space). Possibly we need to derive two options, one for the table with a few thousand records and other for table with lot many records.

Char(1):

(+) uses less space on DB

(-) costly while performing comparison if not default collation
(-) converted to string with EF and comparison takes a lot

TinyInt/SmallInt

(+) uses less space on DB
(+) performs better in comparisons (in DB and code)

(-) when used with EF, explicit conversion needed in code
(-) may lead to confusions unless documented and understood clearly (the order)

Int

(+) uses default type system and no conversions required
(+) performs better in comparisons and indexes

(-) uses more space
(-) may lead to confusion

EDIT

For Int types, I tried using enums to resolve the usage issues:

    public enum StatusValues
    {
        NoAccess = 0,
        Partial = 1,
        Full = 2,
        Other = 3
    }

    public StatusValues StatusValue
    {
        get { return (StatusValues)Status.Value; }
        set { Status = (int)value; }
    }

Upvotes: 0

Views: 939

Answers (3)

Grantly
Grantly

Reputation: 2556

Usually the smaller the better. But you must allow for growth so a 16 bit int would be nicest. Also consider byte alignment and bus widths in the processor and probably 32 or 64 bit int would perform the same. Obviously the smaller size makes a difference with SQL. I think most users are used to type casting in EF, so that should not be a major issue. Using enums make the code more readable and documentation easier to find ... For each enum value of N, P, O, F, etc, and enums default to Int, so using that is easily understood

Upvotes: 0

Matthew
Matthew

Reputation: 25783

To me this sounds like you're worrying too much about the right data type. I doubt this will have a tangible difference in the grand scheme of things whichever option you pick.

Entity Framework supports using enums, and with the enum you can specify the underlying type (int16,32,64,byte, etc.). Using enums will give you readable code while being friendly for space.

Since your data is in the range of 0 to 3, picking an enum with an underlying size of byte would be my recommendation.

Upvotes: 0

Robert Harvey
Robert Harvey

Reputation: 180898

Unless space is at an absolute premium, normally you would always use the default size and type that corresponds to the auto-generated primary key type for your system. In SQL Server, this is usually an Int, unless you're dealing with more than two billion records in the same table.

Nobody will be confused if you use an int. This will be the data type they expect, if you decide to use a lookup table for the status values.

Upvotes: 1

Related Questions