String or binary data would be truncated (VERBOSE_TRUNCATION_WARNINGS ON)

This error is usually encountered when inserting a record in a table where one of the columns is a VARCHAR or CHAR data type and the length of the value being inserted is longer than the length of the column. Before SQL 2019, it is really frustrated with this error message because you cannot find which column is the offending column. The error message is informative now in SQL Server 2019, which tells you which column is causing the issue.

We have a table before 2019 version of sql server

create table table_test (Id int identity (1,1),  Name varchar(10),  Address varchar(10),  Mobile varchar(10))

Now insert some records

Insert into table_test (Name,Address,Mobile) values ('Bagesh Kumar Singh','Noida','888880XXXX')

Getting bellow error 

It is very default to find out in which column it throws this error.

In sql server 2019, this error message has been improved. Here we will get the column name with truncate value.

Let’s see this example in sql server 2019

Insert this record

    

Oooooo  again we are getting same error!

Don’t warry

We need to set the VERBOSE_TRUNCATION_WARNINGS ON.

Now I am altering this database and setting VERBOSE_TRUNCATION_WARNINGS on.

ALTER DATABASE SCOPED CONFIGURATION   SET VERBOSE_TRUNCATION_WARNINGS = on;

   

Now I am inserting this record.

    

With the help of this error message, we can easily find out the truncated column and value. Now I am correcting it and inserting now.

  

Record inserted successfully.

Get the max length of the data in each column form the table in sql server

With the help of below SQL script we will get the max length of the data of the table.

declare @sql varchar(8000),                     @table_name varchar(50)='Employee',                     @table_schema varchar(50)='HumanResources'; set @sql = 'select ' select @sql +=   'max(len( ' + column_name+ ')) AS ' + column_name + ',' from information_schema.columns where table_name = @table_name and TABLE_SCHEMA= @table_schema and data_type in('varchar','char','nvarchar','nchar') set @sql = left(@sql,len(@sql) -1) set @sql +=' from '+ @table_schema+'.'+@table_name exec (@sql)

Basically this script create a dynamic sql script to get the max length of the column.

Here I am getting only varchar, nvarchar, char or nchar data type column.

 

The below script is generating this script.

  

select  max(len( NationalIDNumber)) AS NationalIDNumber,             max(len( LoginID)) AS LoginID,             max(len( JobTitle)) AS JobTitle,             max(len( MaritalStatus)) AS MaritalStatus,            max(len( Gender)) AS Gender from HumanResources.Employee

We will get the same result as above.

 

 

User Defined Table Types and Table Valued Parameters in sql server

Table-Valued Parameters is a new feature introduced in SQL SERVER 2008. In earlier versions of SQL SERVER, it is not possible to pass a table variable in a stored procedure as a parameter, but now in SQL SERVER 2008, we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.

Table-valued parameters (TVPs) are a great way to move chunks of data between our application and SQL Server, while still retaining the ability to abstract database functionality with stored procedures and functions.

Let’s see a simple example where we use the user-defined table types and table-valued parameters.

I am taking the AdventureWorks database for this demo.

We have below table and data product category, product subcategory and product details.

·         ProductCategory

·         ProductSubcategory

·         Product

 

There is two way to insert these records into the table. Either we can insert this records one by one or we can insert these records in one shot. We can use a user-defined table to insert it into one shot.

Let’s see how to create user defined table type

Syntax of the user-defined table type.

CREATE TYPE user_defined_table_name AS TABLE (                 --List of columns )

 See example

CREATE TYPE udt_Product_sub_category AS TABLE ( ProductCategoryID int, Name varchar(50) ) CREATE TYPE udt_Product AS TABLE ( ProductSubcategoryID int, Name varchar(50), ProductNumber varchar(20), StandardCost decimal(18,10), ListPrice decimal(18,10) )

We can see these on below

    

DatabaseàProgrammabilityàTypeà User-Defined Table Type.

Now I am creating an SP to insert the records into these three tables.

CREATE PROCEDURE p_Insert_Product_details (     @Product_Category varchar(50),     @Product_sub_category  udt_Product_sub_category READONLY,                 @product udt_Product READONLY ) AS BEGIN    INSERT INTO Production.ProductCategory (name) values (@Product_Category);       INSERT INTO Production.ProductSubcategory (ProductCategoryID,Name)    select ProductCategoryID,Name from @Product_sub_category;    INSERT INTO Production.Product (                                 ProductSubcategoryID,                                 Name,                                 ProductNumber,                                 StandardCost,                                 ListPrice)                 select                 ProductSubcategoryID,                                 Name,                                 ProductNumber,                                 StandardCost,                                 ListPrice                                 from @product udt_Product END;

SP is created successfully.

Now we need to execute this SP.

Declare @Product_Sub_category_Details as udt_Product_sub_category; Declare @Product_Details as udt_Product Declare @Product_category varchar(50); set @Product_category='Electronics'; --Inserting the records into the Product Sub category insert into @Product_Sub_category_Details (ProductCategoryID,Name) select 9,'Mobile' union all select 9,'Mobile Accessories' union all select 9,'Televesion' union all select 9,'Laptop' union all select 9,'Tablets' union all select 9,'Laptop Accessories' --Inserting the records into the Product insert into @Product_Details (ProductSubcategoryID,Name,ProductNumber,StandardCost,ListPrice,SafetyStockLevel) Select 54,'Mi','P1',10000,10000,1  union all Select 54,'Vivo','p2',20000,20000,1 union all Select 54,'Nokia','p3',3000,3000,1 union all Select 54,'Oppo','p4',40000,4000,1 union all Select 54,'Apple','p5',5000,5000,1 union all Select 54,'Samsung','p6',60000,6000,1 execute p_Insert_Product_details @Product_category,@Product_Sub_category_Details,@Product_Details

SP executed successfully.

  

See the records into the table

  

Important points to remember:

•  Table-valued parameters must be passed as READONLY parameters to SQL routines. You cannot perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
• You cannot use a table-valued parameter as the target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.
• We can’t drop the user-defined table type if it is referred to in any SP or function. First, we need to remove the reference and after that we and drop it.