I have a table that contains transactional data. Such as site view by
whom, when, which template, etc, etc...
Everytime when I pulled the report, hh:mm:ss never matters. Only
breakdown by dates, not time.
Having read some chapters of Ralph Kimball's book, I am inspired to
build "date" table with integer as primary key.
Here's what I have for schema of transactional table.
- viewed_customer _id int (4bytes)
- template_id uniqidentifier (16 bytes)
- viewed_on datetime (8 bytes)
And here's the version I am thinking of building
- viewed_customer _id int (4bytes)
- template_id uniqidentifier (16 bytes)
- viewed_date_key int (4 bytes)
- seconds int (4 bytes)
* I put seconds just in case I need to retrieve hour based or minute
based report.
Here's my question. I've also noticed that smalldatetime is also 4bytes
of memory but it consists of 2 sets of 2 bytes.
When I index, would there be significant performance difference between
indexing 4 byte of column and indexing 2bytes x 2 of column? 7 4691
Bostonasian (ax****@gmail.c om) writes: I have a table that contains transactional data. Such as site view by whom, when, which template, etc, etc... Everytime when I pulled the report, hh:mm:ss never matters. Only breakdown by dates, not time. Having read some chapters of Ralph Kimball's book, I am inspired to build "date" table with integer as primary key.
Here's what I have for schema of transactional table.
- viewed_customer _id int (4bytes) - template_id uniqidentifier (16 bytes) - viewed_on datetime (8 bytes)
And here's the version I am thinking of building
- viewed_customer _id int (4bytes) - template_id uniqidentifier (16 bytes) - viewed_date_key int (4 bytes) - seconds int (4 bytes)
The problem with using int for dates is that you might run into confusion
what your zero date is. In SQL Server the base date is 1900-01-01. In
Visual Basic etc it's 1899-12-30. In Unix it's 1970-01-01.
While datetime is not ideal for dates-only data, it's fairly easy
to use. In our system we actually have a type for it:
EXEC sp_addtype 'aba_date', 'datetime'
go
CREATE RULE aba_date_rule AS convert(char(8) , @x, 112) = @x
go
EXEC sp_bindrule 'aba_date_rule' , 'aba_date'
go
So it's not even possible to sneak in any seconds there. To strip
Hours and seconds from a value, this is the deal:
convert(char(8) , date_value, 112)
(It's important to use format 112, as the resulting string will
always be converted back to datetime correctly.)
Here's my question. I've also noticed that smalldatetime is also 4bytes of memory but it consists of 2 sets of 2 bytes.
When I index, would there be significant performance difference between indexing 4 byte of column and indexing 2bytes x 2 of column?
4 bytes is 4 bytes. While smalldatetime may be described as 2+2, it's
nevertheless just another 32 bits when it comes to indexing.
As for whether smalldatetime is an option to datetime, it depends on
your business requirements. If all you use it for is tracking events,
then it might do. We have abandoned smalldatetime in our system, save
for some auditing columns, because we have encountered real-world
data beyond 2076.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Thanks for the advice.
I got that "date" table from Ralph Kimball's "Data Warehouse tookit",
from "Retail" chapter.
I guess in the retail. It's crucial to know sales date was weekday or
weekend? Was is holiday? Which fiscal month/year it was, etc, etc...
Beyond reporting by only dates, there are occasions where I need to
come up with analysis(though it was not often).
Thanks for the tips.
On 30 Mar 2005 07:54:35 -0800, Bostonasian wrote: Thanks for the advice. I got that "date" table from Ralph Kimball's "Data Warehouse tookit", from "Retail" chapter. I guess in the retail. It's crucial to know sales date was weekday or weekend? Was is holiday? Which fiscal month/year it was, etc, etc... Beyond reporting by only dates, there are occasions where I need to come up with analysis(though it was not often).
Thanks for the tips.
Hi Bostonasian,
Having a table with all dates and various properties of each date (such
as weekend, public holiday, reporting period, etc.) is good. Using an
integer to represent the data is not.
To save myself the typing, I'll just refer you to www.aspfaq.com/2519,
where it is all explained in full detail.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
news:jh******** *************** *********@4ax.c om... On 30 Mar 2005 07:54:35 -0800, Bostonasian wrote:
Thanks for the advice. I got that "date" table from Ralph Kimball's "Data Warehouse tookit", from "Retail" chapter. I guess in the retail. It's crucial to know sales date was weekday or weekend? Was is holiday? Which fiscal month/year it was, etc, etc... Beyond reporting by only dates, there are occasions where I need to come up with analysis(though it was not often).
Thanks for the tips.
Hi Bostonasian,
Having a table with all dates and various properties of each date (such as weekend, public holiday, reporting period, etc.) is good. Using an integer to represent the data is not.
To save myself the typing, I'll just refer you to www.aspfaq.com/2519, where it is all explained in full detail.
Best, Hugo --
(Remove _NO_ and _SPAM_ to get my e-mail address)
While I agree that datetypes should be as close to what they represent,
in the book he refers to the fact table with the detailed data, has an
integer as a key ID to a seperate date table.
The actual datetime value, and all the necesary flags and groupings ( fiscal
quarter, date of week, holiday, etc..) is stored in that side car or
outrigger table.
Since current production versions of SQL don't have a seperate date
datatype, I tend to use smalldatetime instead.
With the side car table, a text representation can also be done to make
reporting easier
i.e. FullDateName column could be "Wednesday, February 31st 2007" , but
sorting is done by actual datetime datatype column.
On Thu, 31 Mar 2005 04:34:02 GMT, David Rawheiser wrote:
(snip quoteback) While I agree that datetypes should be as close to what they represent, in the book he refers to the fact table with the detailed data, has an integer as a key ID to a seperate date table.
The actual datetime value, and all the necesary flags and groupings ( fiscal quarter, date of week, holiday, etc..) is stored in that side car or outrigger table.
(snip)
Hi David,
I don't have this book, but what I'd like to know is what advantage the
integer ID key of this side car table has over a similar side table with
a datetime (or smalldatetime) key, with a CHECK constraint to ensure
that only the default time portion (midnight) is allowed.
Since current production versions of SQL don't have a seperate date datatype, I tend to use smalldatetime instead.
As long as you're aware that smalldatetime has a time portion as well
(though with less precision) and that dates after June 6, 2079 can't be
represented, this is fine.
With the side car table, a text representation can also be done to make reporting easier i.e. FullDateName column could be "Wednesday, February 31st 2007" , but sorting is done by actual datetime datatype column.
I agree - but that can equally well be done if the side car table uses a
[small]datetime column os primary key.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
news:je******** *************** *********@4ax.c om... On Thu, 31 Mar 2005 04:34:02 GMT, David Rawheiser wrote:
(snip quoteback)While I agree that datetypes should be as close to what they represent, in the book he refers to the fact table with the detailed data, has an integer as a key ID to a seperate date table.
The actual datetime value, and all the necesary flags and groupings ( fiscal quarter, date of week, holiday, etc..) is stored in that side car or outrigger table. (snip)
Hi David,
I don't have this book, but what I'd like to know is what advantage the integer ID key of this side car table has over a similar side table with a datetime (or smalldatetime) key, with a CHECK constraint to ensure that only the default time portion (midnight) is allowed.
Since current production versions of SQL don't have a seperate date datatype, I tend to use smalldatetime instead.
As long as you're aware that smalldatetime has a time portion as well (though with less precision) and that dates after June 6, 2079 can't be represented, this is fine.
With the side car table, a text representation can also be done to make reporting easier i.e. FullDateName column could be "Wednesday, February 31st 2007" , but sorting is done by actual datetime datatype column.
I agree - but that can equally well be done if the side car table uses a [small]datetime column os primary key.
Best, Hugo --
(Remove _NO_ and _SPAM_ to get my e-mail address)
I depends on how you are using the date.
If you don't need to present the items by grouping by month, sales period,
fiscal quarters, day of week, etc.
or if you don't want to include weekends or holidays or full moons, etc...
in your reports,
you either need to:
a) put a boat load of complex code in your procedures or functions to
determine these things.
b) create a date calendar table, add columns and set the flags on each of
these atributes.
Thus the resulting query is a simple join from the fact table to this
calendar table and include/exclude in the where clause or groupby those you
need.
You can use the date datatype itself as the keys, and get the same results
but in the book it tends to use abstract datatypes for the fact table
attributes. http://www.amazon.com/exec/obidos/tg...&s=ebooks&st=*
On Fri, 01 Apr 2005 04:30:36 GMT, David Rawheiser wrote:
(snip) I depends on how you are using the date.
If you don't need to present the items by grouping by month, sales period, fiscal quarters, day of week, etc. or if you don't want to include weekends or holidays or full moons, etc... in your reports, you either need to: a) put a boat load of complex code in your procedures or functions to determine these things. b) create a date calendar table, add columns and set the flags on each of these atributes.
Hi David,
I fully agree - and in most cases, the calendar table is the better
option.
Thus the resulting query is a simple join from the fact table to this calendar table and include/exclude in the where clause or groupby those you need.
Yep.
You can use the date datatype itself as the keys, and get the same results but in the book it tends to use abstract datatypes for the fact table attributes.
http://www.amazon.com/exec/obidos/tg...&s=ebooks&st=*
And this is where I disagrgee with the advise given in the book. Sure,
there are situations where a surrogate key is better than a natural key,
but in this specific case, I fail to see any advantages.
Like I said - I don't have this book (and I don't intend to buy it
either :-P) Do the authors give any reasons for their choice to use an
artificial key here? If so, I'd be interested to hear it!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Uli |
last post by:
Dear group,
I have to replicate remote data to a SQL Server in the headquarter.
The data are on a site which does not have permanent online-connection
to the headquarter.
I have written a script which replicates the data and i want to set up
a process / batch on the headquarter-machine which roughly does the
following
|
by: Bradley.. .. . . .. .. |
last post by:
Requirement: managers can login and only see employee data for the
department they manage and any sub-ordinate departments.
The departments are in a table that defines a simple tree structure with
DepartmentID and ParentDepartmentID.
The Employee table holds the DepartmentID that the employee belongs to.
First issue is how to tie an Access or MSSQL login to an employee
record. In Access this is fairly simple as you just make it part of...
|
by: Kukurydz |
last post by:
I've got such problem:
My database is stored on MSSQL Server. I have to write reports for it in
MSAccess. I've got a problem with creating a query which will select records
from MSSQL table with date and time values from given range. The difficulty
is mainly caused by the fact, that Date and Time is stored in separate rows
as String . I've tried to use CDate() function in my query, but it worked
well only during tests on small table (about...
|
by: Rookie Card |
last post by:
Issue - Reformating the Dates in ASP.NET from a MSSQL Database that has
<NULL> values in a SmallDateTime Field
to read "Dec 8, 2000" instead of "12/8/2000 12:00:00 AM"
(As you might already know "DateTime.Parse" will throw an exception
when it comes accross a <NULL> date value so some logic has to be
written.
|
by: jblankenburg |
last post by:
Please help!
I am hunting high and low for an equivalent function for MSSQL's
DATENAME function. Here's the spec on the function from MSSQL's Books
Online:
DATENAME
Returns a character string representing the specified datepart of the
specified date.
| |
by: wildfyre53207 |
last post by:
Here is our problem...
We are doing a lot of selects against a table that has one large field
in it.
If we do a select against all the fields except for description, the
query comes back relatively quickly. If we add that last field (768
chars) to the query, our query takes 10x longer (5 seconds vs 56
seconds.)
|
by: c3q8 |
last post by:
I have a batch system where the user enter suppliers invoices to a batch table (SupVchBat)
then selectively update certain invoice to outstanding invoice table (SupVch) and updated
suppliers (Supplier.sup_mtdpur) current balance and warehouse (warehouse.WHS_ MTD_SALE).
Such is done currently in the front end using VB, but the process is very slow hence I need
help to write batch update in back end using mssql. You help is highly appreciated....
|
by: Igal |
last post by:
I'm trying to insert a date value into MSSQL, the type of the sql filed
is: "smalldatetime"
and i'm trying to insert a text Variable that looks like this:
"19/02/2006".
.... SET update_date='" & Update_Date & "' ...
i get the message:
The conversion of char data type to smalldatetime data type resulted in
|
by: Normann |
last post by:
I am creating a Stored Proc and I need to be able to select the last added row, now this should be made easier by the fact that I have a smalldatetime column in the table that is added every time a new row is inserted. I use MSSQL 2005 and the construction of the table is this:
CREATE TABLE temp_sensor_table
(
temp_id Int Identity PRIMARY KEY,
device_id Int NOT NULL
REFERENCES Devices(device_id),
temp_value NVARChar(10) NOT NULL,...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |