SQL SERVER – Hint on Max size in data type and about -1 length of column.

Question: How many Columns have VARCHAR (MAX), NVARCHAR (MAX), VARBINARY (MAX) and XML Data Type using INFORMATION_SCHEMA.COLUMNS & Value Character Maximum Length -1

 

Generally many SQL Users and Experts thinks about how to find out the how many columns have in the table that have MAX data type and most of the user thinks about that the length of any column can be 0  or more than Zero but VARCHAR(MAX), VARBINARY(MAX),NVARCHAR(MAX) and XML data type contain the -1 CHARACTER MAXIMUM LENGTH in SQL Server.

It is very important to know because MAX data type contain the large value but if you think about it is not necessary to be a MAX than you can reduce the length of your data type

To copy and run the following example in SSMS and see the how many columns are containing -1 length in one database?

Example:

Use AdventureWorks

GO

--For Single Database using INFORMATION_SCHEMA.COLUMN

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE CHARACTER_MAXIMUM_LENGTH=-1

--For Single Table using INFORMATION_SCHEMA.COLUMN

SELECT  TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME=OBJECT_NAME(OBJECT_ID('Sales.Store'))

AND CHARACTER_MAXIMUM_LENGTH= -1

Result: