CODEWITHSUNDEEP
sqlsql-server-2005
remo
remo

Reputation: 3484

sql server: storing datetime vs long text

I have a situation which I can resolve by adding a column which would be a datetime or a ntext type.

In terms of performace, which of these would be better to use. The number of records are more than 60000. I was thinking datetime since I could index on it, but not sure if ntext can be...any suggestion or discussion on which would handle memory,speed better...or any other performance issues?

Update: the column i will add are independent- one is date time and other is text, i can resolve the issue by having anyone, NOTE: I am not trying to store datetime as ntext here.

Upvotes: 0

Views: 521

Answers (3)

Christian Specht
Christian Specht

Reputation: 36421

60000 records is nothing for SQL Server.
So there shouldn't be any noticeable difference. Maybe there would be a difference if it was a REALLY big table (hundreds of millions of records, and above...), but not with your amount of data.

However, as the others already said: your statement that you can either use datetime or ntext sounds very strange to me. If it's really date and/or time values, use datetime and not ntext!!!

EDIT:

Now that you clarified that you don't want to store date values in a text column:
I would suggest that you use the datetime column. It's better than ntext performance wise.

As a side note: if you prefer to use the text column, you should use nvarchar(max) instead of ntext. ntext is slower and deprecated.

Upvotes: 2

JonH
JonH

Reputation: 33143

These are two completely different fields. If you need to store dates use datetime, if you need to store text use varchar or long text. Do not store dates as text!

Upvotes: 1

Oded
Oded

Reputation: 498972

If the data is a DATETIME, use a DATETIME.

You will have problems querying a text field when you need to do date and time operations.

Performance wise - DATETIME is 8 bytes and NVARCHAR for storing a date will be longer. Operations that require date/time work will require conversions with an NVARCHAR field, which will be more expensive than simply using a DATETIME column.

Upvotes: 2

Related Questions