Need advice to design table

• Sharul Nizam

  Ten Centuries

  Points: 1302

  More actions

  January 10, 2010 at 9:58 am

  #215475

  I intent to store value year and month in table. Currently, my table as follow

  declare @tSample table

  (

  rYear varchar(4),

  rMonth varchar(2)

  )

  insert into @tSample values('2009','11')

  insert into @tSample values('2009','12')

  insert into @tSample values('2010','01')

  My question is, is that my table design is accurate?

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  January 10, 2010 at 10:25 am

  #1101523

  Firstly, why are you storing two numbers in varchar columns? Second, why are you storing a date split into two columns? What's wrong with a datetime column with a constraint ensuring that only the 1st day of the month can be inserted?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 10, 2010 at 10:52 am

  #1101527

  You might think about using a calendar table.

  Search this site for details.

  An example can be found for instance here .

  You could also have a look at the link in my signature regarding usage of a Tally table. There's an example on how to populate a calendar table in there, too.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]