Error validating the default for column id newsequentialid
First, values of types other than string that are added to a Data Table string column will be converted to string (Date Time, Integer, GUID, etc.). This means a max length of over 4000 characters will cross the 8000 byte threshold for LOB data on the server side regardless of the table type on the server.Consequently, using the default Data Table column string data type for non-string types will: • increase client memory requirements compared to more compact native types • incur conversion overhead • prevent strong-typed data validation on client side • require date format aware formatting of date and datetime values • require using a period as decimal separators • increase network usage compared with smaller native types When String is the proper column data type, developers must be especially mindful of the max length specification when the Data Table is used as the TVP value. Avoid using long string columns in TVPs when many rows are passed and never use the default -1 length unless a MAX type is actually intended.Before a query or stored proc with a TVP starts executing, SQL Server creates a table in tempdb with the same schema as the parameter table type and uses bulk insert internally to efficiently load the table with TVP rows streamed by the client application.The size of a TVP is constrained only by available tempdb storage.
The application-specified TVP max column length can impact performance significantly, and in some cases dramatically, as illustrated by the performance tests shown earlier.
Table-valued parameters have important considerations that developers and DBAs need to be aware of.