Re: SQL month riddle - most efficient way?

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 03/13/04 

Date: Sat, 13 Mar 2004 07:54:49 -0000

Several things wrong with this design. First your event date column is
non-atomic - a fundamental design flaw that makes it hard to maintain data
integrity or to search efficiently on this data. Second, by using a string
for the month you make it difficult or impossible to sort or compare the
dates or to do date arithmetic - things which are bound to be important in
future queries. Your VARCHAR column is storage-inefficient compared to the 4
or 8 bytes used by a DATETIME or SMALLDATETIME. Finally, you apparently
haven't stored the year number which will presumably become a problem once
the data spans a new year.

I suggest you either use DATETIME or SMALLDATETIME for this data. Add a
constraint that the date can only be 1 if you're only interested in the
month. Add a DEFAULT to and use CURRENT_TIMESTAMP to give you the current
month.

CREATE TABLE SomeTable (...
 dt DATETIME CHECK (DATEADD(MONTH,DATEDIFF(MONTH,0,dt),0)=dt)
 DEFAULT DATEADD(MONTH,DATEDIFF(MONTH,0,CURRENT_TIMESTAMP),0)
 )

You can use the DATENAME/DATEPART functions to return this as a month and/or
year number for display purposes.

Alternatively, store the year and month as integers with similar defaults:

CREATE TABLE SomeTable (CREATE TABLE SomeTable (
 yearno INTEGER NOT NULL CHECK (yearno BETWEEN 2000 AND 2100)
  DEFAULT YEAR(CURRENT_TIMESTAMP),
 monthno INTEGER NOT NULL CHECK (monthno BETWEEN 1 AND 12)
  DEFAULT MONTH(CURRENT_TIMESTAMP))

It's not clear if the multiple months are meant to represent a continuous
period of time or independent, non-continuous events. In the former case you
should create two sets of DATETIME or YearNo/MonthNo columns in your table.
In the case of multiple, independent events you should normalize them as
rows in a single table.

Hope this helps. 

-- 
David Portas
SQL Server MVP
--

Relevant Pages

  • Re: Sorting and non-unique keys
    ... strings, DateTime and Timespan). ... while at other times in TimeSpan order. ... Ideally I would like multiple keys - such as Timespan within DateTime ...
    (microsoft.public.dotnet.languages.csharp)
  • OOP question - multiple value properties
    ... I want to write a class that has multiple values associate to it ... public class Person { ... do I need to write a wrapper class for "string" and ... "DateTime"? ...
    (microsoft.public.dotnet.languages.csharp)