In this post, I am going to share one of the important DBA script to find columns which have large object data types like VARBINARY, VARCHAR(MAX), NVARCHAR(MAX).
As a Database Administrator or Database Architecture, Why this is very important?
The best answer is: to avoid the problem of row overflow and internal page splitting in SQL Server.
The size of the default data page is 8KB and SQL Server stores this kind of large data types in separate page even if they are in the same table.
In one of our database, I have found many VARCHAR(MAX) columns even that columns never require more than 255 characters.
Most of the Database Developers are doing this kind of mistake without aware of row-overflow and data exceeding issue.
The difference between VARCHAR(MAX) and VARCHAR(N) does not impact to data storage, but it makes lot difference in performance.
Using below script, you can find a list of columns which have large object data types:
[s].[name] + '.' + [t].[name] AS TableName
FROM [sys].[tables] AS t
INNER JOIN [sys].[schemas] AS s
ON [t].[schema_id] = [s].[schema_id]
SELECT 1 FROM [sys].[columns] AS c
WHERE [c].[object_id] = [t].[object_id]
AND [c].[max_length] = -1
AND [c].[system_type_id] IN
165, -- VARBINARY
167, -- VARCHAR
231 -- NVARCHAR
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.