Sergiy (2/11/2016)
Junglee_George (2/11/2016)
The data type of @Partners and @Container is NVARCHAR(MAX)Partners value can be like 'EUADVE', 'EUUSEB', 'EUBGLE'
Containers value can be like 'LOC-DENI-000012', 'LOC-DEST-000002', 'LOC-BRSP-000025'
Never ever use NVARCHAR(MAX) for column data types.
Never!
I once managed to improve overall performance of a database by about 70 times simply by replacing all NVARCHAR(MAX) with NVARCHAR(500) and NVARCHAR(100) in all tables where it was used.
7 times.
I did not even read a single line of code.
A pretty dangerous thing to do, and an incredibly irresponsible advice to put out on forums.
Replacing the max datatypes with shorter versions is fine if you know for sure that you will never need a larger length. But that is in most cases impossible to know without reading the code and talking to users and managers.
Never, ever, ever shorten a data type without analyzing the code, verifying your assumptions, and setting up a good test.