question

Brimstedt avatar image
Brimstedt asked

Is there any drawback to always use (N)VARCHAR(MAX)

For some strings, there are real world (or application) limits on number of characters.

Fields holding ISO codes, EAN numbers, etc, will always have their set length and nothing else.

But for other fields, like names, streets, product code, etc, we usually use (N)VARCHAR(X) where X is a well chosen number, enough to hold data.

Sometimes the designer is wrong or new requirements or changes in the world leads to X needing to be extended.

Perhaps originally NVARCHAR(32) was chosen for a name field, but then business moved into a new country and suddenly truncations appear :-)

Is there any drawbacks to always using (N)VARCHAR(MAX), when there is no definite max lenght?

Why not chose NVARCHAR(MAX) for "lastname" instead of NVARCHAR(128) for example..?

database-designschema
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

have you ever known anyone with a 128 character lastname?
0 Likes 0 ·
Thats exaclty the kind of reasoning that makes people chose too short varchars for fields ;-) I agree that 128 chars seems more than enough for names, and most fields are created with an adequate size - but still we choke on truncation errors from time to time (and sqlserver isnt exactly elaborate as to where the string is too long...)
0 Likes 0 ·
Please see my answer on this same thread and run the test code. You'll find out that using VARCHAR(MAX) will automatically mean that your code runs twice as slow. Take the time to size your variable and column datatypes correctly. Sure, it's OK to allow for a bit of "future growth" but VARCHAR(MAX) is NOT the answer to most of those "future growth" problems.
0 Likes 0 ·
I'll also add that proper sizing of columns is essential to the creation of "wide" indexes (many columns, typically found in a "star" schema") because you only get 900 bytes, period. While 128 characters may certainly seem safe for a last name, I've seen such a thing cause a problem with indexing long before a properly sized column is overrun by a long name.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

You can't include nvarchar(max) or varchar(max) columns as key columns in an index.

They can be INCLUDED however the index must be created or rebuilt offline.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

There is still the question of designing your system for the edge case. Yes, we all know about cases where the 50 length field really should have been 75. But as a percentage of fields across the life of the product, how many times does that happen? You're not only sacrificing the ability to add the column to an index as part of the key (which, I don't know, could be useful at some point), but you're sacrificing data integrity, ensuring that only the right number of characters is stored in a Social Security Number column, just as an example. If you put no limits, anywhere, in the system, then you can't guarantee data integrity and that is one of the core principals by which we operate.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Henrik Staun Poulsen avatar image
Henrik Staun Poulsen answered

I consider it best practice to

a) Always use NVarchar(n) when you are building a string for dynamic SQL

b) Not declare a variable whos lenght is dependent on a column in the database, except if the length is declared dynamicly, so that any changes to the underlying table will be reflected in the variable.
I only do this in CRUD procedures, and I have another bit of code that generate all CRUDs.

Just my two cents...

Henrik Staun Poulsen
www.stovi.com

5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

not sure it is best practice to define your interface incorrectly. If your table only stores varchar(50), don't let the calling app think that 100 chars will be stored.....
1 Like 1 ·
so do you check data lengths in the SP and error or just let truncation happen?
0 Likes 0 ·
iirc too long strings put in SP arguments are truncated silently anyway?
0 Likes 0 ·
See my answer on this same thread. Considering that the use of VARCHAR(MAX) will automatically cause your code to run twice as slow, you may want to seriously reconsider your "best practice".
0 Likes 0 ·
In a stored procedure I hate the idea of having a variable, whos length depends on the length of a column somewhere in the database. So I tried to search our code, to see how often this happens. Zero places! (except for CRUD which is autocreated) So I have edited my answer again ;-)
0 Likes 0 ·
Jeff Moden avatar image
Jeff Moden answered

As already pointed out, proper datatype sizing is critical for indexing. But, even if indexes aren't involved,

using VARCHAR(MAX) WILL automatically make your code twice as slow

even if it supposedly "fits in row". But, don't take my word for it... prove it yourself with the following code...
DECLARE @BitBucket1 VARCHAR(8000),
        @BitBucket2 VARCHAR(MAX);

    SET STATISTICS TIME ON;

  PRINT '********** Test VARCHAR(8000) Table Build **********';
 SELECT TOP 1000000
        CAST(ROW_NUMBER() OVER (ORDER BY ac1.Object_ID) AS VARCHAR(8000)) AS N
   INTO #Test1
   FROM Master.sys.All_Columns ac1
  CROSS JOIN Master.sys.All_Columns ac2;

  PRINT '********** Test VARCHAR(MAX) Table Build **********';
 SELECT TOP 1000000
        CAST(ROW_NUMBER() OVER (ORDER BY ac1.Object_ID) AS VARCHAR(MAX)) AS N
      INTO #Test2
   FROM Master.sys.All_Columns ac1
  CROSS JOIN Master.sys.All_Columns ac2;

  PRINT '********** Test VARCHAR(8000) SELECT **********';
 SELECT @BitBucket1 = N FROM #Test1;

  PRINT '********** Test VARCHAR(MAX) SELECT **********';
 SELECT @BitBucket2 = N FROM #Test2;

   SET STATISTICS TIME OFF;

--===== Cleanup
   DROP TABLE #Test1, #Test2;
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thanks! I think we can safely say that VARCHAR(MAX) should only be used when needed, and not always.
0 Likes 0 ·
+1 - You learn something new every day...
0 Likes 0 ·
dvroman avatar image
dvroman answered

MS Dynamics access puts in another limitation. Since it doesn't access the server normally, (it uses shortcuts that do not use parts of the SQL engine) all fields that Dynamics accesses must be fixed length.
Dynamics does a memory map of the data which precludes any use of NVARCHAR or VARCHAR or any of the variable length formats and all numeric fields are SMALLINT, INTEGER or FLOAT.
This program is very much an exception.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.