This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing an interesting fact on the disk usage by extra tabs and spaces of SQL Server object’s definition.
Many of you don’t know that extra spaces and tabs are occupying the disk in SQL Server. People are creating their own coding standard and giving a huge number of extra spaces in the object definition.
Nowadays, the disk is not that much costlier so few extra KBs/MBs we can afford. But I saw few servers, where companies are creating a separate database for each client and one database contained thousands of stored procedures.
So I would suggest, please follow the best practices in your coding life.
From tomorrow, I am going to share few articles on SQL Server Coding Standard and best practices.
Here, I created two stored procedures where one has extra tabs and other has normal spaces. You can easily get the size difference between these two stored procedures.
Create sample Stored Procedure with extra Tabs and Spaces:
|
1 2 3 4 5 6 |
CREATE PROCEDURE usp_ABC AS BEGIN DECLARE @Name VARCHAR(20) DECLARE @Add VARCHAR(100) END |
Create sample Stored Procedure without extra Tabs and Spaces:
|
1 2 3 4 5 6 |
CREATE PROCEDURE usp_XYZ AS BEGIN DECLARE @Name VARCHAR(20) DECLARE @Add VARCHAR(100) END |
Compare the size of above SPs:
|
1 2 3 4 5 6 7 |
SELECT [p].[name] ,size = DATALENGTH([asm].[definition]) FROM sys.all_sql_modules AS asm INNER JOIN sys.procedures AS p ON [asm].[object_id] = [p].[object_id] WHERE [p].[name] IN ('usp_ABC','usp_XYZ') |
Result:
|
1 2 3 4 |
name size ----------- --------- usp_ABC 230 usp_XYZ 198 |
Leave a Reply