Wednesday, July 24, 2013

Column Length Distribution profile in SSIS Profiler

How does Column Length Distribution profile in SSIS Profiler work in SSIS ? Column Length Distribution profile get the length of all the columns to be profiled, and get the max and min out of it.
 E.g the below example


WITH cte
AS (
 SELECT LEN(NULL) AS [Column Length]
 
 UNION ALL
 
 SELECT LEN(100) AS [Column Length]
 
 UNION ALL
 
 SELECT LEN(101) AS [Column Length]
 
 UNION ALL
 
 SELECT LEN(102) AS [Column Length]
 
 UNION ALL
 
 SELECT LEN(103) AS [Column Length]
 )
SELECT max([Column Length]) AS [Max Column Length]
 ,MIN([Column Length]) AS [MIN Column Length]
FROM cte
OUTPUT OF the above query will be [Max Column Length] = 3
 ,[MIN Column Length] = 3


 So if the column value in your table is NULL, the lenght for NULL is always NULL.

No comments: