Database design : preferred field length for file paths
database, database-design, sql-server
Solution
You can use `VARCHAR(MAX)` or `NVARCHAR(MAX)`.
These are variable length fields meaning they are designed to store values of different length. There is no extra overhead for longer values over shorter values.
Defining `MAX` means the field can be up to 2GB.
From MSDN (varchar), nvarchar has similar documentation:
Use varchar when the sizes of the column data entries vary considerably.
Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.
Problem
I have to store file paths in a DB field (`/tmp/aaa/bbb`, `C:\temp\xxx\yyy`, etc.). I can't really tell how long they could be. Given this http://en.wikipedia.org/wiki/Comparison_of_file_systems and that http://msdn.microsoft.com/en-us/library/aa365247.aspx, depending on the file system there could be theoretically no length limit for a path. I guess that defining this field as a `LONGBLOB` or `VARCHAR(very high value)` wouldn't be wise. I've thought about something like `VARCHAR(1024)` which should be suitable for most frequent (even if not all) cases, and not too big as a DB field. What would you recommend ? Thanks.