Sign in
 
HomeLibraryLearnDownloadsRepositoryCommunityForumsBlog
Script Center >
> 
> 
Which SQL data type to use?

Answered Which SQL data type to use?

  • Friday, August 17, 2012 5:48 PM
     
     
    Sign In to Vote
    0

    I posted this in the SQL forum, but then noticed that they have very little activity over there and I need an answer pretty fast.  Besides, this isn't life or death, I just need some opinions and I respect you guys.

    I have a SQL 2008 database that will hold user names, computer names, and logon/logoff datetimes.  Please help me choose which data types to use for each.  Here's a sample of the data that each field will hold:

    UserName: a12345b2
    ComputerName: abcdefgh123456z
    Logon/Logoff times will be taken from system time or maybe a timestamp will be used.  Still undecided...

    UserName will always be 7 or 8 characters.  ComputerName will never be longer than that listed above but might be considerably shorter.

    Knowing that there will be considerable activity based around UserName and ComputerName, what data types would you choose for each field?

    I'm the most humble person you've ever met.

     

All Replies

  • Friday, August 17, 2012 6:32 PM
     
     Answered
    Sign In to Vote
    2

    It seems like varchar(x) (where x is the maximum size of the values you might expect to see) should do fine for all your strings and for the timestamp use datetime2.   Personally I would not stress on data types since unless you see transaction numbering in the millions per second you will likely see no difference in performance (but I am sure a DBA would scold me for making this point).

    As a side note char(x) dedicate storage which is not necessary and nchar(x) and nvarchar(x) are meant to store Unicode.

    You might also want to collect information like for instance logontype http://technet.microsoft.com/en-us/library/cc787567(v=ws.10).aspx

    This is just my opinion so take it for what it’s worth you might get more details from a true DBA

    • Marked As Answer by James Keeler Monday, August 20, 2012 1:34 PM
    •  
     
  • Friday, August 17, 2012 8:15 PM
     
     Answered
    Sign In to Vote
    0

    For ddata management reasons I would sue relatiosn.

    table1-
              id_computer(INTEGER PK), computernames

    table2 - 
             id_user(INTEGER PK),, username

    table3
             EventType(INTEGER PK), Eventname 

    datatable -
         id_user(INTEGER(FK), id_computer(INTEGER FK), EventTime (DATETIME) , EventType(INTEGER FK)

    This would bemost efficient an d allow for very efficint queries.  ALl recxords in actuvity table would be fixed in size because no strings wuold be present. 

    This is the use of data normalization which wil limprove performance although this table is so small that it would hardly present an issue.  I have stired reords like this into teh millions with no issue.

    The "fly' or 'leaf' tables caan bepopulaed via a trigger on the main table which would add new accunts to the key tables automatically.  The information would never get lost becuse there could never be  atext confilct.  We can also port other master info into the key tables to enhance reports.

    ¯\_(ツ)_/¯