What data type is best for storing comments in SQL Server?

329

+1 Q:

What is an effective datatype in SQL 2005 to store a comments field?

That depends on whether or not you limit the comment length. On SO, nvarchar(600) does it. On a blog, you probably want nvarchar(max).

Eric 2009-08-09 04:06:24

Thanks, Eric

Csharp 2009-08-09 04:07:29

`varchar(max)` or `nvarchar(max)` would be more appropriate than `text` on SQL Server 2005.

John Saunders 2009-08-09 04:07:57

@John: You're right, I always forget that `text` was deprecated.

Eric 2009-08-09 04:09:23

i think you always want a reasonable upper bound for any text field. nvarchar(max) on a public field opens your db for abuse.

Paul Sasik 2009-08-09 04:18:05

@psasik: And if your reasonable upper bound is OVER 9000?!

Eric 2009-08-09 04:21:20

+3 A:

If the comment will always fit in 8000 chars then varchar(8000) (or nvarchar(4000)).

Otherwise a varchar(max)

Mitch Wheat 2009-08-09 04:07:00

Apparently SQL 2005 only supports:

varchar(max) Variable-length non-Unicode data with a maximum length of 2^31 characters.

If you need unicode character strings,

nvarchar(max) Variable-length Unicode data with a maximum length of 2^30 characters.

text is also supported as a datatype. Use this site if you need more reference:

ninumedia 2009-08-09 04:10:16

text will be deprecated in later versions and is nothing like as flexible as varchar(max).

gbn 2009-08-09 08:27:39

This was mentioned for Eric's answer below too

gbn 2009-08-09 08:28:16

ansaurus