Menu
I don't know if there's a limit to the number of characters if I choose nvarchar(MAX), and I'm not sure how many characters I would need anyways.
What's the standard data type to use here? I'm using SQL Server 2008 R2
284
ntext will always store its data in a separate database page, while nvarchar(max) will try to store the data within the database record itself. So nvarchar(max) is somewhat faster (if you have text that is smaller as 8 kB). I also noticed that the database size will grow slightly slower, this is also good.
Use nvarchar when the sizes of the column data entries vary considerably. Use nvarchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 4,000 byte-pairs.
If you anticipate data possibly exceeding 4000 character, nvarchar(MAX) is definitely the recommended choice. It is also NOT recomended if your data will never exceed 4000 characters as there are indexing issues.
estimates more memory grant and this value will be much higher than actual required and this would be a waste of a resource as precisous as memory. If you have couple of queries which are using nvarchar(amx) column and sorting is needed then server might have memroy related issues. This could be a big perf issue.
The ntext type is deprecated, as are text and image. Microsoft recommends replacing them with nvarchar(max), varchar(max) and varbinary(max) respectively.
Use nvarchar(max), therefore.
Reference:
187
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
