What is the best SQL type to use for a large string variable?

Question

Apologies for the rather basic question.

I have an error string that is built dynamically. The data in the string is passed by various third parties so I don't have any control, nor do I know the ultimate size of the string.

I have a transaction table that currently logs details and I want to include the string so that I can reference back to it if necessary.

2 questions:

How should I store it in the database?
Should I do anything else such as contrain the string in code?

I'm using Sql Server 2008 Web.

nvarchar(max) msdn.microsoft.com/en-us/library/ms186939(v=sql.100).aspx, if its bigger than that you are logging too much error information. — Jodrell
– Jodrell, Commented Aug 15, 2013 at 9:09
@Jodrell - thats the thing - I don't know. Theoretically the third party could send an error message for every invalid input field I send them 35 * 100-150 chars — dotnetnoob
– dotnetnoob, Commented Aug 15, 2013 at 9:30
@dotnetnoob - So worst case would be 5,250 characters. Unicode or "plain vanilla" — Martin Smith
– Martin Smith, Commented Aug 15, 2013 at 9:34

Christian.K · Accepted Answer · 2013-08-15 09:43:00Z

1

If you want to store non unicode text, you can use:

varchar(max) or nvarchar(max)

Maximum length is 2GB.

Other alternatives are:

binary or varbinary

Drawbacks: you can't search into these fields and index and order them and the maximum size : 2GB.

There are TEXT and NTEXT, but they will be deprecated in the future, so I don't suggest to use them. They have the same drawbacks as binary.

So the best choice is one of varchar(max) or nvarchar(max).

edited Aug 15, 2013 at 9:43

Christian.K

49.6k10 gold badges108 silver badges156 bronze badges

answered Aug 15, 2013 at 9:24

speti43

3,0622 gold badges22 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Smith Over a year ago

[n]varchar(max) is 2GB not 4,000/8,000 characters. So supports 1 or 2 billion characters dependent on single or double byte. There is no need to store as binary.

Martin Smith Over a year ago

DV removed but you've still left in the bit about "If you want to store larger strings use bynary or varbynary". This is not correct except if using varbinary(max) in conjunction with filestream. varbinary(max) in the DB has the same 2GB limit.

Rahul Tripathi · Accepted Answer · 2013-08-15 09:09:48Z

1

You can use SQL Server nvarchar(MAX).

Check out this too.

answered Aug 15, 2013 at 9:09

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

3 Comments

Jodrell Over a year ago

When you say "Check this out" are you suggesting the use of ntext or drawing attention to the warning?

Rahul Tripathi Over a year ago

Yes actually thats what I wanted as you have already pointed the official link in the comments so I thought to go with this one ;)

Rahul Tripathi Over a year ago

I am just trying to draw the attention to the warning!! ;)

veljasije · Accepted Answer · 2013-08-15 09:14:28Z

1

Eventualy, you can enable and use a FILESTREAM feature of SQL Server 2008 (it's supported by WEB edition), and deal with extra large amount of data in sense of documents.

Of course, you need to be sure that you will use a benefit of this service.

answered Aug 15, 2013 at 9:14

veljasije

7,13213 gold badges53 silver badges82 bronze badges

Collectives™ on Stack Overflow

What is the best SQL type to use for a large string variable?

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related