Showing posts with label storing. Show all posts
Showing posts with label storing. Show all posts

Wednesday, March 28, 2012

How much storage space does nvarchar() eats? Always max?

Hello, everybody,

I am in need of storing long pieces of text in database, length between 2000 and 4000 characters (nvarchar limit).

If I have a column in a table:

my_text nvarchar(2000)

does this mean that in database the size used will always be 2000*2=4000 bytes at least (+ rounding), independent on what the real length of the text stored is?

Anoter alternative - storing texts in files is expected to become unmanageable very soon, so I would like to keep to the db.

Or, would it be reasonable to have such a table:

my_text_1 nvarchar(500),
my_text_2 nvarchar(500),
my_text_3 nvarchar(500),
my_text_4 nvarchar(500)

Or declaration already means the space for a column is reserved even if it is nullable?

My DBs are MS SQL Server 2000/2005.As I understand it the amount of space used is limited to the size of the data you have in the nvarchar field. So "hello world" will take up the same amouint of space in an nvarchar(20) as it does in an nvarchar(200).|||But why use unicode? Do you really need it?

Sunday, February 19, 2012

How format the time

I am looking for a way to retrieve the time from a Sql 2000 database in the format of hh:mm AM or PM. I am storing the information as a "datetime" data type. If I only supply the time (such as 02:30 PM) then the date defaults to 12/30/1899. When I read the information from the database I get "1899-12-30 14:30:00:000".

If I enter 02:30 PM I would like to get 02:30 PM.

I have tried various convert functions in Sql, maybe there is a way to format with an ASP.Net datetime function but I need help to get the information back in the right format.
Thx In AdvanceYou would need to format as an nvarchar:

SELECT RIGHT(CONVERT(nvarchar(20),GetDate(),100),7)

This will be close (no space between minutes and AM or PM).

You can format easily in .NET

DateTime.Now.ToString("hh:mm tt")|||That worked!!!
I would also like to know what is going on in this peice of code, at your convenience.

Thank you so much|||In this code:

SELECT RIGHT(CONVERT(nvarchar(20),GetDate(),100),7)

I am CONVERTing the current datetime to an nvarchar() using format 100, which ends with the last 7 characters hh:mmtt

In .NET, the ToString() method is overloaded, and hh gets the hours in 12 hour format, mm gets the minutes, and tt gets the AM or PM.

DateTime.Now.ToString("hh:mm tt")