Printable View
I need some advice on what type of DATA TYPE I should use for SQL Server 2005.
My source data is from an excel file and I will insert the excel data into sql server.
Excel Column Names and values are:
1. ShiftStart 8:00
2. ShiftEnd 5:00
3. TimeIn 7:30
4 TimeOut 5:30
Should I use "datetime" data type for all the fields above? I really have a hard time comparing hours and minutes. Is there an easy way to compare hours and minutes?
When you retrieve datetime values from SQL Server into VB you get DateTime objects, which have Hour and Minute properties.
EDIT: Hmmm... I'm so used to posting in the VB.NET forum I assumed that you were using VB.NET but I suppose you may well be using VB6. I believe VB6 has a Date data type too but I've never used it so I don't know how it compares.
I really hate working with Dates and Hours LOL. I was given a task to process a time sheet from an excel file (A file from another company). The problem is, the columns IMO is incomplete. ex. ShiftStart = 08:00. It would be nice if the columns where like this: ShiftStart = 8/13/2008 8:00:00 AM. Sigh! This is driving me nuts! LOL.
There is always an option that you can insert all the data in there as a standard varchar into a table. Then you can create your main table and insert that data into there doing any formatting required.
Its an extra step, but it sometimes useful when you have to do a lot of messing around with the Excel data.
Use the datetime data type; less storage consumption and you can use date functions (including formatting functions) on the column.
As to missing data. Business process should be clarified with relevant business units before automating the system. garbage-In, Garbage-Out, make sure they understand that.
Check if Excel file is a daily dump (and has been agreed upon with relevant business unit to contain only info for one day), if so require filename contain date info or similar work around.
I agree with leinad31 and jmc - use DateTime.
In SQL there are the functions to help, such as Hour(datetimevalue) and Minute(datetimevalue) which return the relevant part of datetimevalue as a number.Quote:
Originally Posted by coolwater
In VB (whichever version) there are similar functions.
Regarding use of string to store dates... DON'T
DateTime typically uses 7 or 8 bytes. Use of string in MMDDYYYY format (8 characters) can be more than 8 bytes depending on the encoding used, e.g. wide unicode formats. Formats that will include time info will be more than 8 characters long. If you have 1 million records then you will incur more than 1MB of storage overhead per extra byte (8MB for 8 extra bytes).
Sorting incurs conversion overhead since value needs to be converted to datetime equivalent unless you use YYYYMMDD format which can be sorted using string values. Arithmetic operations, e.g. such as next day, date difference, time elapsed, last day in month and so on, also incur conversion overhead. In terms of SQL tuning and optimization, use of strings is far from ideal.
Indexes are also affected; they will require more storage, require more disk I/O (due to more characters in index value) and index operations (e.g. range scan) are less effective (again due to size per value). Wider records from more characters means there are less records maintained in the database cache, which in turn means more disk I/O if data is not in cache. Indexes are by default maintained in ascending order, so use of YYYYMMDD instead of MMDDYYYY affects order maintained in index. Indexes speed up SELECT but slow down INSERT/UPDATE/DELETE due to index sort order maintenance so more characters in index value makes SELECT operations less efficient (due to aforementioned disk I/O related issues) and really degrades INSERT/UPDATE/DELETE performance.
Number of characters also affects joins... more characters means more bytes to be compared. Means more database memory needed for intermediate and result tables. In some databases, character columns must have same lengths in order to use indexes in joins, e.g. date in 8-char column related to datetime in 15 char column won't use index. SQL and transactional SQL operations can be optimized more with datetime compared to strings.
Numeric values are more CPU operation friendly compared to string which can only load several characters at a time to CPU register. DateTime fnuctions perform better compared to string functions; less CPU overhead. For reporting purposes, datetime values can simply be formated upon presentation, e.g. can be easily localized. Strings require more processing.
Hopefully the above is mentioned in the database tutorial... if not, hopefully it will be in the future.
EDIT:
Also please bear in mind that dates in strings cannot be validated by the database unless it converts the value to datetime (so why not use datetime in the first place, right?). You will rely on mid-tier/front-end components to ensure validity of value before insert/update. Rather than handling just two scenarios (valid datetime or NULL), you end up with additional scenarios (zero length and invalid value).
I also forgot to mention constraints and triggers, their performance penalty is per record inserted/updated. Unique constraints are based on primary/unique keys so performance penalty of index applies. If triggers perform operations on other tables based on your date in the string and those other tables (e.g. child tables related 1 to many) also have triggers that perform processes based on dates in strings then performance penalty cascades across tables (performance degradation explosion).
It wasn't in the FAQs before, but it is now - as you explained the issues so well, I've put in a link to your post. :)Quote:
Originally Posted by leinad31
Thanks. Edited post to include constraints and triggers.Quote:
Originally Posted by si_the_geek