473,573 Members | 3,239 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Which is better? Int or smalldatetime(M SSQL)

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?

Jul 23 '05 #1
7 4677
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
Jul 23 '05 #2
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.

Jul 23 '05 #3
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)
Jul 23 '05 #4

"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.

Jul 23 '05 #5
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)
Jul 23 '05 #6

"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=*
Jul 23 '05 #7
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)
Jul 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1897
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
5
3177
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...
14
2456
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...
0
1082
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.
2
9286
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.
2
1919
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.)
0
1324
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...
2
2541
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
4
3847
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...
0
7661
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...
0
7977
Oralloy
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. ...
0
8165
jinu1996
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...
1
7730
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...
0
5252
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...
0
3692
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3686
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1256
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
984
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.