Friday, August 17, 2012 5:48 PM
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:
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.
Friday, August 17, 2012 6:32 PM
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
For ddata management reasons I would sue relatiosn.
id_computer(INTEGER PK), computernames
id_user(INTEGER PK),, username
EventType(INTEGER PK), Eventname
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.