Posts Tagged ‘best practice’

Why You Should Never Use DATETIME Again!

690px-Microsoft_SQL_Server_Logo.svgDates, we store them everywhere, DateOrdered, DateEntered, DateHired, DateShipped, DateUpdated, and on and on it goes. Up until and including SQL Server 2005, you really didn’t have much choice about how you stored your date values. But in SQL Server 2008 and higher you have alternatives to DateTime and they are all better than the original.

DATETIME stores a date and time, it takes 8 bytes to store, and has a precision of .001 seconds

In SQL Server 2008 you can use DATETIME2, it stores a date and time, takes 6-8 bytes to store and has a precision of 100 nanoseconds. So anyone who needs greater time precision will want DATETIME2. What if you don’t need the precision? Most of us don’t even need milliseconds. So you can specify DATETIME2(0) which will only take 6 bytes to store and has a precision of seconds. If you want to store the exact same value you had in DATETIME, just choose DATETIME2(3), you get the same precision but it only takes 7 bytes to store the value instead of 8. I know a lot of you are thinking, what’s one byte, memory is cheap. But it’s not a question of how much space you have on your disk. When you are performance tuning, you want to store as many rows on a page as you can for each table and index. that means less pages to read for a table or query, and more rows you can store in cache. Many of our tables have multiple date columns, and millions of rows. That one byte savings for every date value in your database is not going to make your users go ‘Wow everything is so much faster now’, but every little bit helps.

If you are building any brand new tables in SQL Server 2008, I recommend staying away from DATETIME and DATETIME2 altogether. Instead go for DATE and TIME. Yes, one of my happiest moments when I first started learning about SQL Server 2008 was discovering I could store the DATE without the time!! How many times have you used GETDATE() to populate a date column and then had problems trying to find all the records entered on ‘05-JUN-06’ and got no results back because of the time component. We end up truncating the time element before we store it, or when we query the date to ignore the time component. Now we can store a date in a column of datatype DATE. If you do want to store the time, store that in a separate column of datatype TIME. By storing the date and time in separate columns you can search by date or time, and you can index by date and or time as well! This will allow you to do much faster searches for time ranges.

Since we are talking about the date and time datatypes, I should also mention that there is another date datatype called DATETIMEOFFSET that is time zone aware. But that is a blog for another day if you are interested.

Here is a quick comparison of the different Date and Time Data types,

Datatype Range Precision Nbr Bytes User Specified Precision
SMALL DATETIME 1900-01-01 to 2079-06-06 1 minute 4 No
DATETIME 1753-01-01 to 9999-12-31 .001 seconds 8 No
DATETIME2 0001-01-01 to 9999-12-31 23:59.59.9999999 100 ns 6-8 Yes
DATE 0001-01-01 to 9999-12-31 1 day 3 No
TIME 00:00:00.0000000 to 23:59.59.9999999 100 ns 3-5 Yes
DATETIMEOFFSET 0001-01-01 to 9999-12-31 23:59.59.9999999 100 ns 8-10 Yes

Today’s My 5 is of course related to the Date and Time datatypes.

My 5 Important Date functions and their forward and backwards compatibility

  1. GETDATE() – Time to STOP using GETDATE(), it still works in SQL Server 2008, but it only returns a precision of milliseconds because it was developed for the DATETIME datatype.
  2. SYSDATETIME() – Time to START using SYSDATETIME(), it returns a precision of nanoseconds because it was developed for the DATETIME2 datatype and it also works for populating DATETIME columns.
  3. DATEDIFF() – This is a great little function that returns the number of minutes, hours, days, weeks, months, or years between two dates, it supports the new date datatypes.
  4. ISDATE() – This function is used to validate DATETIME values. It returns a 1 if you pass it a character string containing a valid date. However if you pass it a character string representing a datetime that has a precision greater than milliseconds it will consider this an invalid date and will return a 0.
  5. DATEPART() – This popular function returns a portion of a date, for example you can return the year, month, day, or hour. This date function supports all the new date datatypes.

Also one extra note, because I know there are some former Oracle developers who use this trick. If you have any select statements where you select OrderDate+1 to add one day to the date, that will not work with the new date and time datatypes. So you need to use the DATEADD() function.

This blog is also posted to the Canadian Solution Developer

Another Meeting! Who Really Needs to Attend?

imageHave you ever been to a meeting where part way through you thought to yourself “I do not need to be here”? It’s happened to all of us. I once asked my husband exactly what he does at work. He thought about it for a minute and answered “I go to meetings.” Meetings are everywhere! Now don’t get me wrong, meetings can be very effective. We want to keep everyone informed, and we do need to consult with different team members when we are making decisions. But how much thought do we put into who to invite to meetings? Most of us err on the side of caution and invite everyone who may have an opinion or wants to be kept informed. After all haven’t we been told over and over again the importance of communication? But sometimes we forget meetings are not the only way to communicate.

I have done a lot of work with the Information Technology Infrastructure Library, better known as ITIL. Don’t worry I am not going to go into a diatribe on the benefits of MOF (Microsoft Operational Framework) and ITIL here and now (that’s a blog for another day). The reason I bring it up is that in ITIL I came across a wonderful model I have applied very successfully. Its called the RACI model (pronounced racey). RACI is an acronym for Responsible, Accountable, Consulted, Informed. This model can be used to help you run more successful meetings. As they say in Kinect Dance Central…let’s break it down.

Responsible –  Who are the people responsible for doing the work? The people responsible should definitely be in the meeting. For example, if you are holding a meeting to discuss a bug fix, you definitely want the programmer who is making the bug fix present to explain the effort required to fix the bug, or to explain the cause of the bug.

Accountable Who is the person who is ‘on the hook’ for the work? This is often the supervisor of the people responsible. Sometime it helps to think of the A as standing for Authority, as in decision making authority. Who has the authority to make decisions? There should only be *one* person identified as accountable. Otherwise you run into problems. There is a Danish proverb that roughly translated says “When you have one clock in the house you know what time it is, as soon as you have two clocks you are are never sure.” You want the person accountable in the meeting, because if they aren’t there you may find the meeting going in circles because there is no-one in the room with the authority to make a final decision on how to move forward. If the person accountable cannot attend, see if they can appoint someone to attend to act on their behalf with their authority. Otherwise, you may want to reschedule the meeting to a time when the person accountable can attend. For the bug fix meeting example, the operations manager or project manager might be accountable. We want them in the meeting because they will have the final decision on if or when we fix the bug based on the effort required, the impact on the users, and the other team priorities.

ConsultedWho are the people who will want a say in the decision or discussion but are not doing the work itself? Often you can provide these individuals with a summary of the topic to be discussed by email and follow up with them before and after the meeting. They may give you questions that they want answered, but that doesn’t mean they need to attend the meeting itself. Often you can ask the questions on their behalf. If the question is too complicated, then you may invite them to the meeting so they can explain and elaborate as necessary. For example, when you are making a bug fix you may need to consult with the security team to ensure that the fix does not violate security policy. Most of the time, if you let security know what you are going to discuss in the meeting, they will tell you the concerns they need addressed. After the meeting you can let them know the outcomes and give them an opportunity to identify any outstanding issues.

InformedWho are the people who need to know what is going on? These are the people who need to be kept in the loop, but do not need to attend the meeting. For example, your user community, your client, or the testing team may need to know about the progress on a bug fix but that doesn’t mean they need to attend all the meetings. Often e-mail or a collaboration, project tool like SharePoint or Visual Studio Team System is sufficient to keep them up to date.

So the next time you are invited to a meeting, ask yourself where you fit in the model, if you are not responsible or accountable, do you really need to attend? If you are holding a meeting, complete the RACI model for the topic being discussed. On some projects, I have documented the RACI model for each programming module, or bug. That way when a change or issue was identified we immediately knew who was responsible, who was accountable, who needed to be consulted, and who needed to be kept informed.

So in keeping with the meeting theme, here are

My 5 Tips to successful meetings (there are many more, but I’ll stick to 5 for now)

  1. Complete the RACI model for the meeting.
  2. Email your agenda, or questions to be discussed one to three days before the meeting. That way everyone has a chance to arrive in the meeting prepared. Don’t send it out to far in advance or they will forget about it before the meeting.
  3. Book a suitable meeting room with the required amenities. Make sure you have enough chairs, water, a projector if necessary. It is very frustrating for meeting attendees to spend 20 minutes sitting unproductive while you run around the building trying to find a projector or whiteboard markers.
  4. Use a parking lot! Meetings frequently go off track, do not be afraid to have a flipchart or corner of the whiteboard where you can park issues for follow up later. It is important if you use a parking lot that you do leave a few minutes at the end of the meeting to discuss how you will follow up on each item in the parking lot. A parking lot is not meant to be a black hole.
  5. Do not deliver surprises in a meeting and expect useful feedback. When someone tells us about a new initiative or change, our first reaction is emotional: I love the idea or I hate the idea. It will take me a little while to absorb what you have told me and give you useful feedback. All too often we walk into a meeting and are informed of a major change, then while we are still reacting to the news we are expected to provide constructive criticism. If you tell me today by email or in person, and give me some time to absorb the idea and mull it over I would be in a much better position to discuss it rationally tomorrow in a meeting.

This post also appears in the Canadian Solution Developer Blog