'Does nvarchar(max) in SQL Server cause any perfomance overhead while inserting data into database?

I was using nvarchar(300) earlier in my SQL Server.

I was setting the attribute on the entity like this:

@Column(name="stud_desc" length= 300)
private String studentDescription;

Now because of some reasons, I have to remove the length=300 validation part and I set the column in the database to nvarchar(max).

Now while inserting data into the table, I have noticed that the insert query takes a little bit more time.

Although most of the character length is < 1k, some are also over 8k.

My question is: does using the above column datatype in SQL Server have any performance impact?

Also what would be the safer alternative? ( if anything)

sql-serverdatabase


Solution 1:[1]

yes, the server engine will begin to prepare to dedicate resources based on the potential bit length parameter.

Solution 2:[2]

Even Though You do not fill in the values to the maximum, due to the way SQL Server stores and pages data, it makes sure to dedicate the amount of space needed based on what's defined in the structure.

The alternative for this might be a dedicated 1:(0..1) relationship table, where You have the StudentID and Description. Thus, if the Description is not needed, it might be faster. Otherwise, it's all about managing data.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 David Brian
Solution 2 TheLJ

Related Questions