datatime or separate it to smallint, tinyint

Question

datatime or separate it to smallint, tinyint

Soonyu-163551

SSC Eights!

Points: 811
More actions
November 14, 2007 at 1:25 am

#95510

I want create new database
Option A
customer_id, int
process_year, smallint
process_month, tinyint
process_day, tinyint
a varhchar(20)
b varhchar(20)
c varhchar(20)
d varhchar(20)
with non_clustered index
customer_id, process_day, process_month, process_year
or
Option B
customer_id, int
process_date smalldatetime
a varhchar(20)
b varhchar(20)
c varhchar(20)
d varhchar(20)
with non clustered index
customer_id, process_date
I will search customer_id and process_date range,
which opition will you choose? A or B

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

option b sould be my choice. u can have a index on the datetime column. Also if time part is important then its better to use datetime. Having said this there are situations where option A might eb usefull.

As always it depends..

"Keep Trying"

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

Use Option B. There are functions to work with dates and you don't need to reinvent them. Too easy to make mistakes.

If you need to query on year or month often, you can use datepart (performance hit), but you could just store the year, month, or date separately for those times you need it.

If you give us more of a hint about what you're doing, we can help. Especially if you put it in the design forum.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

B

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Jeff Moden (12/2/2007)
A

Why would you say that?

I'd say B. Dates should be stored in datetime columns, not seperated out. There are a couple of good reasons

There are some quite powerful datetime functions in SQL, that you'll struggle to use if the day, month and year are split and stored seperatly.

You don't save any space storing them seperatly, and you'll be spending CPU cycles turning them into dates for any form of manipulation or comparison.

If the parts are stored separatly and you don't have some carefully coded check constraints, you can get strange things like the 31st November, 29th Feb in a non-leap year and all such messes.

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

~millhouse Ten Centuries Points: 1366 More actions · Answer 5

I would also select option B. If you ever need to get more granular with the date and need to see the time of day this won't be available with option A.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 6

Sorry, edited my post as well. I think B (using datetime) makes more sense. Include the other int columns if it helps performance.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 7

I'll go for option B..., makes sense querying a date directly rather than concatenating integers to make a date then query it!!!!

--Ramesh

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

GilaMonster (12/3/2007)
Jeff Moden (12/2/2007)
A
Why would you say that?
I'd say B. Dates should be stored in datetime columns, not seperated out. There are a couple of good reasons
There are some quite powerful datetime functions in SQL, that you'll struggle to use if the day, month and year are split and stored seperatly.
You don't save any space storing them seperatly, and you'll be spending CPU cycles turning them into dates for any form of manipulation or comparison.
If the parts are stored separatly and you don't have some carefully coded check constraints, you can get strange things like the 31st November, 29th Feb in a non-leap year and all such messes.

Oh bugger... mostly because I didn't have enough coffee when I read that... I was thinking that option "A" was the worst thing you could do, went to type "B" and, apparently, typed the wrong letter. Sorry folks...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)