LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1882
Members Online:
2
Guests Online:
99

Total Post History
Posts:
81456
Topics:
18718

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
justanails28

Other

FAQ
All times are UTC [ DST ]

Documentation on filtering date vs date table

Please wait...

Documentation on filtering date vs date table

Postby Thirster42 on Wed Apr 11, 2012 4:59 pm

I'm building a new database, and part of the core data are values broken up by month/year. I'm thinking about setting up a dates table, something like:

  1. create table dbo.Dates
  2. (
  3. PK_daID int identity(1,1) primary key(PK_daID),
  4. daDate date
  5. )


The data would look like this:

  1. 1     1/1/2012
  2. 2     2/1/2012
  3. 3     3/1/2012
  4. 4     4/1/2012


My question is, would it be more efficient to filter the date table and inner join to my distribution table, or would it be better to just filter the distribution table on the date?
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: Documentation on filtering date vs date table

Postby Thirster42 on Wed Apr 11, 2012 5:02 pm

Also, to throw some numbers out, each month could have up to 12000 records. That's a worse case example though, i suspect that on average it'll be around 4000.
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: Documentation on filtering date vs date table

Postby gmmastros on Wed Apr 11, 2012 5:29 pm

Is there a specific reason for having the identity column? I mean... in the fact table, would you be storing the date, or would you be storing the id (identity column) value?

I suspect that it won't really make much difference. Date and Int both require 4 bytes, so the storage space doesn't matter. Both can be indexed, so that doesn't matter. Personally, I think I would forget about the separate table and just have the dates in the main table.

Your performance will be dictated by the indexes you have on the table more so than having a separate table.
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2369
Joined: Tue Oct 09, 2007 5:19 pm
Unrated

Re: Documentation on filtering date vs date table

Postby SQLDenis on Wed Apr 11, 2012 5:56 pm

I would just store the date. It is only 3 bytes compared to an int which is 4
what are you trying to accomplish here....a calendar table?
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Documentation on filtering date vs date table

Postby Thirster42 on Wed Apr 11, 2012 7:21 pm

SQLDenis wrote:I would just store the date. It is only 3 bytes compared to an int which is 4
what are you trying to accomplish here....a calendar table?


yeah, a calendar table, except it would only have one record per month. i think i'm just going to store the date in the distribution table.
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated