I’ve built a stored procedure that analyses a source table or query with several characteristics per column among which the minimum length (min_len) and maximum length (max_len).
CREATE PROCEDURE [dbo].[sp_analysetable] (
@tableName varchar(8000),
@deep bit = 0
) AS
/*
sp_analysetable 'company'
sp_analysetable 'select * from company where name is not null'
*/
DECLARE @intErrorCode INT, @errorMSG VARCHAR(500), @tmpQ NVARCHAR(2000), @column_name VARCHAR(50), @isQuery bit
SET @intErrorCode=0
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
DROP TABLE ##tmpTableToAnalyse
END
IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
DROP TABLE ##tmpColumns
END
if CHARINDEX('from', @tableName)>0
set @isQuery=1
IF @intErrorCode=0 BEGIN
if @isQuery=1 begin
--set @tableName = 'USE '+@db+';'+replace(@tableName, 'from', 'into ##tmpTableToAnalyse from')
--replace only first occurance. Now multiple froms may exists, but first from will be replaced with into .. from
set @tableName=Stuff(@tableName, CharIndex('from', @tableName), Len('from'), 'into ##tmpTableToAnalyse from')
exec(@tableName)
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NULL BEGIN
set @intErrorCode=1
SET @errorMSG='Error generating temporary table from query.'
end
else begin
set @tableName='##tmpTableToAnalyse'
end
end
end
IF @intErrorCode=0 BEGIN
SET @tmpQ='USE '+DB_NAME()+';'+CHAR(13)+CHAR(10)+'
select
c.column_name as [column],
cast(sp.value as varchar(1000)) as description,
tc_fk.constraint_type,
kcu_pk.table_name as fk_table,
kcu_pk.column_name as fk_column,
c.ordinal_position as pos,
c.column_default as [default],
c.is_nullable as [null],
c.data_type,
c.character_maximum_length as length,
c.numeric_precision as [precision],
c.numeric_precision_radix as radix,
cast(null as bit) as [is_unique],
cast(null as int) as min_len,
cast(null as int) as max_len,
cast(null as int) as nulls,
cast(null as int) as blanks,
cast(null as int) as numerics,
cast(null as int) as distincts,
cast(null as varchar(500)) as distinct_values,
cast(null as varchar(50)) as remarks
into ##tmpColumns'
if @isQuery=1 begin
SET @tmpQ=@tmpQ+' from tempdb.information_schema.columns c, (select null as value) sp'
end
else begin
SET @tmpQ=@tmpQ+'
from information_schema.columns c
left join sysobjects so on so.name=c.table_name and so.xtype=''U''
left join syscolumns sc on sc.name=c.column_name and sc.id =so.id
left join sys.extended_properties sp on sp.minor_id = sc.colid AND sp.major_id = sc.id and sp.name=''MS_Description''
left join information_schema.key_column_usage kcu_fk on kcu_fk.table_name = c.table_name and c.column_name = kcu_fk.column_name
left join information_schema.table_constraints tc_fk on kcu_fk.table_name = tc_fk.table_name and kcu_fk.constraint_name = tc_fk.constraint_name
left join information_schema.referential_constraints rc on rc.constraint_name = kcu_fk.constraint_name
left join information_schema.table_constraints tc_pk on rc.unique_constraint_name = tc_pk.constraint_name
left join information_schema.key_column_usage kcu_pk on tc_pk.constraint_name = kcu_pk.constraint_name
'
end
SET @tmpQ=@tmpQ+' where c.table_name = '''+@tableName+''''
exec(@tmpQ)
end
IF @intErrorCode=0 AND @deep = 1 BEGIN
DECLARE
@count_rows int,
@count_distinct int,
@count_nulls int,
@count_blanks int,
@count_numerics int,
@min_len int,
@max_len int,
@distinct_values varchar(500)
DECLARE curTmp CURSOR LOCAL FAST_FORWARD FOR
select [column] from ##tmpColumns;
OPEN curTmp
FETCH NEXT FROM curTmp INTO @column_name
WHILE @@FETCH_STATUS = 0 and @intErrorCode=0 BEGIN
set @tmpQ = 'USE '+DB_NAME()+'; SELECT'+
' @count_rows=count(0), '+char(13)+char(10)+
' @count_distinct=count(distinct ['+@column_name+']),'+char(13)+char(10)+
' @count_nulls=sum(case when ['+@column_name+'] is null then 1 else 0 end),'+char(13)+char(10)+
' @count_blanks=sum(case when ltrim(['+@column_name+'])='''' then 1 else 0 end),'+char(13)+char(10)+
' @count_numerics=sum(isnumeric(['+@column_name+'])),'+char(13)+char(10)+
' @min_len=min(len(['+@column_name+'])),'+char(13)+char(10)+
' @max_len=max(len(['+@column_name+']))'+char(13)+char(10)+
' from ['+@tableName+']'
exec sp_executesql @tmpQ,
N'@count_rows int OUTPUT,
@count_distinct int OUTPUT,
@count_nulls int OUTPUT,
@count_blanks int OUTPUT,
@count_numerics int OUTPUT,
@min_len int OUTPUT,
@max_len int OUTPUT',
@count_rows OUTPUT,
@count_distinct OUTPUT,
@count_nulls OUTPUT,
@count_blanks OUTPUT,
@count_numerics OUTPUT,
@min_len OUTPUT,
@max_len OUTPUT
IF (@count_distinct>10) BEGIN
SET @distinct_values='Many ('+cast(@count_distinct as varchar)+')'
END ELSE BEGIN
set @distinct_values=null
set @tmpQ = N'USE '+DB_NAME()+';'+
' select @distinct_values=COALESCE(@distinct_values+'',''+cast(['+@column_name+'] as varchar), cast(['+@column_name+'] as varchar))'+char(13)+char(10)+
' from ('+char(13)+char(10)+
' select distinct ['+@column_name+'] from ['+@tableName+'] where ['+@column_name+'] is not null) a'+char(13)+char(10)
exec sp_executesql @tmpQ,
N'@distinct_values varchar(500) OUTPUT',
@distinct_values OUTPUT
END
UPDATE ##tmpColumns SET
is_unique =case when @count_rows=@count_distinct then 1 else 0 end,
distincts =@count_distinct,
nulls =@count_nulls,
blanks =@count_blanks,
numerics =@count_numerics,
min_len =@min_len,
max_len =@max_len,
distinct_values=@distinct_values,
remarks =
case when @count_rows=@count_nulls then 'all null,' else '' end+
case when @count_rows=@count_distinct then 'unique,' else '' end+
case when @count_distinct=0 then 'empty,' else '' end+
case when @min_len=@max_len then 'same length,' else '' end+
case when @count_rows=@count_numerics then 'all numeric,' else '' end
WHERE [column]=@column_name
FETCH NEXT FROM curTmp INTO @column_name
END
CLOSE curTmp DEALLOCATE curTmp
END
IF @intErrorCode=0 BEGIN
select * from ##tmpColumns order by pos
end
IF @intErrorCode=0 BEGIN --Clean up temporary tables
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
DROP TABLE ##tmpTableToAnalyse
END
IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
DROP TABLE ##tmpColumns
END
end
IF @intErrorCode<>0 BEGIN
RAISERROR(@errorMSG, 12, 1)
END
RETURN @intErrorCode
I store this procedure in the master database so that I can use it in every database like so:
sp_analysetable 'table_name', 1
// deep=1 for doing value analyses
And the output is:
column description constraint_type fk_table fk_column pos default null data_type length precision radix is_unique min_len max_len nulls blanks numerics distincts distinct_values remarks
id_individual NULL PRIMARY KEY NULL NULL 1 NULL NO int NULL 10 10 1 1 2 0 0 70 70 Many (70) unique,all numeric,
id_brand NULL NULL NULL NULL 2 NULL NO int NULL 10 10 0 1 1 0 0 70 2 2,3 same length,all numeric,
guid NULL NULL NULL NULL 3 (newid()) NO uniqueidentifier NULL NULL NULL 1 36 36 0 0 0 70 Many (70) unique,same length,
customer_id NULL NULL NULL NULL 4 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
email NULL NULL NULL NULL 5 NULL YES varchar 100 NULL NULL 0 4 36 0 0 0 31 Many (31)
mobile NULL NULL NULL NULL 6 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
initials NULL NULL NULL NULL 7 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
title_short NULL NULL NULL NULL 8 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
title_long NULL NULL NULL NULL 9 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
firstname NULL NULL NULL NULL 10 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
lastname NULL NULL NULL NULL 11 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
address NULL NULL NULL NULL 12 NULL YES varchar 100 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
pc NULL NULL NULL NULL 13 NULL YES varchar 10 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
kixcode NULL NULL NULL NULL 14 NULL YES varchar 20 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
date_created NULL NULL NULL NULL 15 (getdate()) NO datetime NULL NULL NULL 1 19 19 0 0 0 70 Many (70) unique,same length,
created_by NULL NULL NULL NULL 16 (user_name()) NO varchar 50 NULL NULL 0 13 13 0 0 0 1 loyalz-public same length,
id_location_created NULL FOREIGN KEY location id_location 17 NULL YES int NULL 10 10 0 1 1 0 0 70 2 1,2 same length,all numeric,
id_individual_type NULL FOREIGN KEY individual_type id_individual_type 18 NULL YES int NULL 10 10 0 NULL NULL 70 0 0 0 NULL all null,empty,
optin NULL NULL NULL NULL 19 NULL YES int NULL 10 10 0 1 1 39 0 31 2 0,1 same length,
I’ve built a stored procedure that analyses a source table or query with several characteristics per column among which the minimum length (min_len) and maximum length (max_len).
CREATE PROCEDURE [dbo].[sp_analysetable] (
@tableName varchar(8000),
@deep bit = 0
) AS
/*
sp_analysetable 'company'
sp_analysetable 'select * from company where name is not null'
*/
DECLARE @intErrorCode INT, @errorMSG VARCHAR(500), @tmpQ NVARCHAR(2000), @column_name VARCHAR(50), @isQuery bit
SET @intErrorCode=0
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
DROP TABLE ##tmpTableToAnalyse
END
IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
DROP TABLE ##tmpColumns
END
if CHARINDEX('from', @tableName)>0
set @isQuery=1
IF @intErrorCode=0 BEGIN
if @isQuery=1 begin
--set @tableName = 'USE '+@db+';'+replace(@tableName, 'from', 'into ##tmpTableToAnalyse from')
--replace only first occurance. Now multiple froms may exists, but first from will be replaced with into .. from
set @tableName=Stuff(@tableName, CharIndex('from', @tableName), Len('from'), 'into ##tmpTableToAnalyse from')
exec(@tableName)
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NULL BEGIN
set @intErrorCode=1
SET @errorMSG='Error generating temporary table from query.'
end
else begin
set @tableName='##tmpTableToAnalyse'
end
end
end
IF @intErrorCode=0 BEGIN
SET @tmpQ='USE '+DB_NAME()+';'+CHAR(13)+CHAR(10)+'
select
c.column_name as [column],
cast(sp.value as varchar(1000)) as description,
tc_fk.constraint_type,
kcu_pk.table_name as fk_table,
kcu_pk.column_name as fk_column,
c.ordinal_position as pos,
c.column_default as [default],
c.is_nullable as [null],
c.data_type,
c.character_maximum_length as length,
c.numeric_precision as [precision],
c.numeric_precision_radix as radix,
cast(null as bit) as [is_unique],
cast(null as int) as min_len,
cast(null as int) as max_len,
cast(null as int) as nulls,
cast(null as int) as blanks,
cast(null as int) as numerics,
cast(null as int) as distincts,
cast(null as varchar(500)) as distinct_values,
cast(null as varchar(50)) as remarks
into ##tmpColumns'
if @isQuery=1 begin
SET @tmpQ=@tmpQ+' from tempdb.information_schema.columns c, (select null as value) sp'
end
else begin
SET @tmpQ=@tmpQ+'
from information_schema.columns c
left join sysobjects so on so.name=c.table_name and so.xtype=''U''
left join syscolumns sc on sc.name=c.column_name and sc.id =so.id
left join sys.extended_properties sp on sp.minor_id = sc.colid AND sp.major_id = sc.id and sp.name=''MS_Description''
left join information_schema.key_column_usage kcu_fk on kcu_fk.table_name = c.table_name and c.column_name = kcu_fk.column_name
left join information_schema.table_constraints tc_fk on kcu_fk.table_name = tc_fk.table_name and kcu_fk.constraint_name = tc_fk.constraint_name
left join information_schema.referential_constraints rc on rc.constraint_name = kcu_fk.constraint_name
left join information_schema.table_constraints tc_pk on rc.unique_constraint_name = tc_pk.constraint_name
left join information_schema.key_column_usage kcu_pk on tc_pk.constraint_name = kcu_pk.constraint_name
'
end
SET @tmpQ=@tmpQ+' where c.table_name = '''+@tableName+''''
exec(@tmpQ)
end
IF @intErrorCode=0 AND @deep = 1 BEGIN
DECLARE
@count_rows int,
@count_distinct int,
@count_nulls int,
@count_blanks int,
@count_numerics int,
@min_len int,
@max_len int,
@distinct_values varchar(500)
DECLARE curTmp CURSOR LOCAL FAST_FORWARD FOR
select [column] from ##tmpColumns;
OPEN curTmp
FETCH NEXT FROM curTmp INTO @column_name
WHILE @@FETCH_STATUS = 0 and @intErrorCode=0 BEGIN
set @tmpQ = 'USE '+DB_NAME()+'; SELECT'+
' @count_rows=count(0), '+char(13)+char(10)+
' @count_distinct=count(distinct ['+@column_name+']),'+char(13)+char(10)+
' @count_nulls=sum(case when ['+@column_name+'] is null then 1 else 0 end),'+char(13)+char(10)+
' @count_blanks=sum(case when ltrim(['+@column_name+'])='''' then 1 else 0 end),'+char(13)+char(10)+
' @count_numerics=sum(isnumeric(['+@column_name+'])),'+char(13)+char(10)+
' @min_len=min(len(['+@column_name+'])),'+char(13)+char(10)+
' @max_len=max(len(['+@column_name+']))'+char(13)+char(10)+
' from ['+@tableName+']'
exec sp_executesql @tmpQ,
N'@count_rows int OUTPUT,
@count_distinct int OUTPUT,
@count_nulls int OUTPUT,
@count_blanks int OUTPUT,
@count_numerics int OUTPUT,
@min_len int OUTPUT,
@max_len int OUTPUT',
@count_rows OUTPUT,
@count_distinct OUTPUT,
@count_nulls OUTPUT,
@count_blanks OUTPUT,
@count_numerics OUTPUT,
@min_len OUTPUT,
@max_len OUTPUT
IF (@count_distinct>10) BEGIN
SET @distinct_values='Many ('+cast(@count_distinct as varchar)+')'
END ELSE BEGIN
set @distinct_values=null
set @tmpQ = N'USE '+DB_NAME()+';'+
' select @distinct_values=COALESCE(@distinct_values+'',''+cast(['+@column_name+'] as varchar), cast(['+@column_name+'] as varchar))'+char(13)+char(10)+
' from ('+char(13)+char(10)+
' select distinct ['+@column_name+'] from ['+@tableName+'] where ['+@column_name+'] is not null) a'+char(13)+char(10)
exec sp_executesql @tmpQ,
N'@distinct_values varchar(500) OUTPUT',
@distinct_values OUTPUT
END
UPDATE ##tmpColumns SET
is_unique =case when @count_rows=@count_distinct then 1 else 0 end,
distincts =@count_distinct,
nulls =@count_nulls,
blanks =@count_blanks,
numerics =@count_numerics,
min_len =@min_len,
max_len =@max_len,
distinct_values=@distinct_values,
remarks =
case when @count_rows=@count_nulls then 'all null,' else '' end+
case when @count_rows=@count_distinct then 'unique,' else '' end+
case when @count_distinct=0 then 'empty,' else '' end+
case when @min_len=@max_len then 'same length,' else '' end+
case when @count_rows=@count_numerics then 'all numeric,' else '' end
WHERE [column]=@column_name
FETCH NEXT FROM curTmp INTO @column_name
END
CLOSE curTmp DEALLOCATE curTmp
END
IF @intErrorCode=0 BEGIN
select * from ##tmpColumns order by pos
end
IF @intErrorCode=0 BEGIN --Clean up temporary tables
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
DROP TABLE ##tmpTableToAnalyse
END
IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
DROP TABLE ##tmpColumns
END
end
IF @intErrorCode<>0 BEGIN
RAISERROR(@errorMSG, 12, 1)
END
RETURN @intErrorCode
I store this procedure in the master database so that I can use it in every database like so:
sp_analysetable 'table_name', 1
// deep=1 for doing value analyses
And the output is:
column description constraint_type fk_table fk_column pos default null data_type length precision radix is_unique min_len max_len nulls blanks numerics distincts distinct_values remarks
id_individual NULL PRIMARY KEY NULL NULL 1 NULL NO int NULL 10 10 1 1 2 0 0 70 70 Many (70) unique,all numeric,
id_brand NULL NULL NULL NULL 2 NULL NO int NULL 10 10 0 1 1 0 0 70 2 2,3 same length,all numeric,
guid NULL NULL NULL NULL 3 (newid()) NO uniqueidentifier NULL NULL NULL 1 36 36 0 0 0 70 Many (70) unique,same length,
customer_id NULL NULL NULL NULL 4 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
email NULL NULL NULL NULL 5 NULL YES varchar 100 NULL NULL 0 4 36 0 0 0 31 Many (31)
mobile NULL NULL NULL NULL 6 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
initials NULL NULL NULL NULL 7 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
title_short NULL NULL NULL NULL 8 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
title_long NULL NULL NULL NULL 9 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
firstname NULL NULL NULL NULL 10 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
lastname NULL NULL NULL NULL 11 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
address NULL NULL NULL NULL 12 NULL YES varchar 100 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
pc NULL NULL NULL NULL 13 NULL YES varchar 10 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
kixcode NULL NULL NULL NULL 14 NULL YES varchar 20 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
date_created NULL NULL NULL NULL 15 (getdate()) NO datetime NULL NULL NULL 1 19 19 0 0 0 70 Many (70) unique,same length,
created_by NULL NULL NULL NULL 16 (user_name()) NO varchar 50 NULL NULL 0 13 13 0 0 0 1 loyalz-public same length,
id_location_created NULL FOREIGN KEY location id_location 17 NULL YES int NULL 10 10 0 1 1 0 0 70 2 1,2 same length,all numeric,
id_individual_type NULL FOREIGN KEY individual_type id_individual_type 18 NULL YES int NULL 10 10 0 NULL NULL 70 0 0 0 NULL all null,empty,
optin NULL NULL NULL NULL 19 NULL YES int NULL 10 10 0 1 1 39 0 31 2 0,1 same length,
CREATE TABLE dbo.CoolPeople(PersonName VARCHAR(20), PrimaryCar VARCHAR(20));
GO
INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)
VALUES ('Baby', '2006 Subaru Impreza WRX GD');
GO
Машина Baby длиннее, чем 20 символов, поэтому при выполнении оператора INSERT получаем ошибку:
Msg 8152, Level 16, State 30, Line 5
String or binary data would be truncated.
The statement has been terminated.
Это засада, поскольку у нас нет идей относительно того, какое поле вызвало проблемы! Это особенно ужасно, когда вы пытаетесь вставить множество строк.
Чтобы пофиксить ошибку, включите флаг трассировки 460
Флаг трассировки 460 был введен в SQL Server Sevice Pack 2, Cummulative Update 6, и в SQL Server 2017. (Вы можете найти и загрузить последние обновления с SQLServerUpdates.com.) Вы можете включить флаг на уровне запроса, например:
INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)
VALUES ('Baby', '2006 Subaru Impreza WRX GD')
OPTION (QUERYTRACEON 460);
GO
Теперь, если выполнить запрос, он покажет вам, какой столбец усекается, а также какая строка. В нашем случае мы имеем только одну строку, но в реальной жизни много полезней будет знать, какая строка вызвала ошибку:
Msg 2628, Level 16, State 1, Line 9
String or binary data would be truncated in
table 'StackOverflow2013.dbo.CoolPeople', column 'PrimaryCar'.
Truncated value: '2006 Subaru Impreza '.
Вы можете включить этот флаг трассировки как на уровне запроса (в нашем примере выше), так и на уровне сервера:
DBCC TRACEON(460, -1);
GO
Этот оператор включает его для всех, а не только для вас — поэтому сначала договоритесь со своей командой разработчиков, прежде чем включать его. Это изменит номер ошибки 8152 на 2628 (как показано выше), означающее, что если вы строили обработку ошибок на основании этих номеров, вы сразу получите другое поведение.
Я любитель включения этого флага трассировки на время отладки и изучения, но как только обнаруживаю источник проблем, выключаю его, снова выполнив команду:
DBCC TRACEON(460, -1);
GO
В нашем случае, как только мы идентифицировали избыточную длину машины Baby, необходимо либо изменить название машины, либо изменить тип данных в нашей таблице, чтобы сделать размер столбца больше. Можно также предварительно обрабатывать данные, явно отсекая избыточные символы. Мастерская по разборке данных, если хотите.
Не оставляйте этот флаг включенным
По крайней мере, имеется связанный с этим один баг в SQL Server 2017 CU13: табличные переменные будут выбрасывать ошибки, говорящие, что их содержимое усекается, даже если никакие данные не вставляются в них.
Вот простой скрипт, чтобы проверить, пофиксили ли это поведение:
CREATE OR ALTER PROC dbo.Repro @BigString VARCHAR(8000) AS
BEGIN
DECLARE @Table TABLE ( SmallString VARCHAR(128) )
IF ( 1 = 0 )
/* Это никогда не выполняется */
INSERT INTO @Table ( SmallString )
VALUES(@BigString)
OPTION (QUERYTRACEON 460)
END
GO
DECLARE @BigString VARCHAR(8000) = REPLICATE('blah',100)
EXEC dbo.Repro @BigString
GO
SQL Server 2017 CU13 всё еще сообщает об усечении строки, даже если строка не вставляется:
Переключение с табличной переменной на временную таблицу приводит к ожидаемому поведению:
Это замечательный пример, почему не следует использовать флаги трассировки по умолчанию. Конечно, они могут пофиксить проблемы, но они также могу вызвать непредсказуемое или нежелательное поведение. (И, вообще, я не фанат табличных переменных.)
First published on MSDN on Oct 24, 2018
In the recent announcement at Ignite 2018 on the release of SQL Server 2019 CTP 2.0, the new Big Data Clusters , data virtualization, support for UTF-8 , and Intelligent Query Processing were highlights. But we have also previewed work being done to address the infamous error message “String or binary data would be truncated”.
This error condition may happen for example when you implement an ETL between a source and a destination that does not have matching data types and/or length. In this context, the message «String or binary data would be truncated» is one of the most time-consuming troubleshooting processes to engage in, especially in large datasets. You probably know about and voted for this feedback item before.
Let’s see an example of what happens when you insert data into a column whose size is not big enough to store it:
USE [AdventureWorks2016CTP3]
GO
DROP TABLE IF EXISTS [Sales].[SalesOrderHeaderTest]
GO
CREATE TABLE [Sales].[SalesOrderHeaderTest](
[SalesOrderID] [INT] NOT NULL,
[CustomerID] [INT] NOT NULL,
[CreditCardApprovalCode] [nvarchar](13) NULL
)
GO
INSERT INTO [Sales].[SalesOrderHeaderTest]
SELECT [SalesOrderID], [CustomerID], [CreditCardApprovalCode]
FROM [Sales].[SalesOrderHeader]
GO
You receive following error message, which admittedly is not very helpful:
Msg 8152, Level 16, State 30, Line 13
String or binary data would be truncated.
The statement has been terminated.
We heard that. Which is why SQL Server 2019 introduces a new message , with additional context information. For the same operation, the new error message outputs the following:
Msg 2628, Level 16, State 1, Line 14
String or binary data would be truncated in table ‘AdventureWorks2016CTP3.Sales.SalesOrderHeaderTest’, column ‘CreditCardApprovalCode’. Truncated value: ‘1231736Vi8604’.
The statement has been terminated.
Ok, the new error message provides more context, and now I have the resulting truncated value (not the source value). This is simplifying the troubleshooting process, because now I know the truncated value starts with ‘1231736Vi8604′ – that’s 13 characters. And so I can go back to my data source, and locate the source record and its length:
SELECT [SalesOrderID], [CustomerID], [CreditCardApprovalCode], LEN([CreditCardApprovalCode])
FROM [Sales].[SalesOrderHeader]
WHERE CreditCardApprovalCode LIKE ‘1231736Vi8604%’
It’s 14 characters, and in my table definition I have a NVARCHAR(13). Well, I know what I need to do: change my table data type length.
This new message is also backported to SQL Server 2017 CU12 and in SQL Server 2016 SP2 CU6, but not by default. You need to enable trace flag 460 to replace message ID 8152 with 2628, either at the session or server level.
Note that for now, even in SQL Server 2019 CTP 2.0 the same trace flag 460 needs to be enabled. In a future SQL Server 2019 release, message 2628 will replace message 8152 by default.
EDIT (3/29/2019): For SQL Server 2019 CTP 2.4, Message 2628 becomes default under Database Compatibility Level 150. You can use the Database Scoped Configuration VERBOSE_TRUNCATION_WARNINGS to revert to back to Message 8152 as default. You can also use a lower Database Compatibility Level to revert back to Message 8152 as default.
Is there a limit to how much of my truncated string this error can return?
Let’s run a small test inserting a 123 character string into a VARCHAR(120):
CREATE TABLE myTable (myString VARCHAR(120));
GO
INSERT INTO myTable
VALUES (‘Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.’)
GO
Although my string gets truncated at 120 characters, the offending value that is shown is truncated to the first 100 characters:
Msg 2628, Level 16, State 1, Line 30
String or binary data would be truncated in table ‘AdventureWorks2016CTP3.dbo.myTable’, column ‘myString’. Truncated value: ‘Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore ‘.
Still plenty to find offending values in most data sources.
Pedro Lopes ( @SQLPedro ) – Senior Program Manager
- Remove From My Forums
-
Question
-
I have created a table function but When I try to test I get this error… From what I can tell its and issue with the data returned being larger than declared but I just cant see it! Please help!
Query:
CREATE FUNCTION dbo.[fn_ExecutionLog_Error] (@DateBegin_prt1 varchar(25))
RETURNS @t_Errors Table (
PackageID varchar (125) NOT NULL
, PackageName varchar (125) NOT NULL
, ErrorNumber BIGINT NOT NULL
, ErrorDiscription varchar (125) NOT NULL
)AS
BEGINDECLARE @space VARCHAR(1)
, @FullDateBegin varchar(50)
, @FullDateEnd varchar(50)
, @DateBegin_prt2 varchar(25) = ’00:22:00.000′
SET @space = ‘ ‘
SET @FullDateBegin = @DateBegin_prt1 + @space + @DateBegin_prt2
SET @FullDateBegin = cast(@FullDateBegin as datetime)
SET @FullDateEnd = DateAdd(hour,12,@FullDateBegin)INSERT INTO @t_Errors
SELECT DISTINCT PackageID, PackageName, ErrorNumber, ErrorDescription
from SSIS_ErrorLog
where LogTime > @FullDateBegin and LogTime < @FullDateEnd
group by PackageID, PackageName, ErrorNumber, ErrorDescriptionRETURN;
END;Testing with: SELECT * FROM dbo.[fn_ExecutionLog_Error] (‘2012-05-21’)
-
Moved by
Wednesday, March 13, 2013 4:14 PM
-
Moved by
Answers
-
HI
Is your error message show like below :
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.The statement has been terminated.
If above then reason is » At the time of 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.»
Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/
-
Marked as answer by
Jstrack13
Wednesday, March 13, 2013 6:21 PM
-
Marked as answer by
-
SET @space = ‘ ‘
SET @FullDateBegin = @DateBegin_prt1 + @space + @DateBegin_prt2can be
SET @FullDateBegin = @DateBegin_prt1 +’ ‘+ @DateBegin_prt2
Please Mark as Answer if my post works for you or Vote as Helpful if it helps you.
Junaid Hassan.-
Marked as answer by
Jstrack13
Wednesday, March 13, 2013 6:21 PM
-
Marked as answer by