Sunday, May 27, 2012

How to find row size of the table in MS SQL

 

Some times you may required to know each row size in your table. Today I am going to present you a simple query which is helpful to find the row size of your table in MS SQL.

First we will see the design of the table,

CREATE TABLE [dbo].[Customer](
    [id] [int] NOT NULL,
    [name] [nchar](10) NULL,
    [Address] [nvarchar](50) NULL,
 ) ON [PRIMARY]
 
GO

Now we will insert some values to the table

INSERT INTO [Customer]([id] ,[name] ,[Address]) VALUES (1 ,'John','Spain')
INSERT INTO [Customer]([id] ,[name] ,[Address]) VALUES (1 ,'James','Wales')
INSERT INTO [Customer]([id] ,[name] ,[Address]) VALUES (1 ,'Michael','Scotland')
INSERT INTO [Customer]([id] ,[name] ,[Address]) VALUES (1 ,'Mark','United Kingdom')
GO

Now we will see the query to get the row size,

select
row_number() over (order by id desc) as [ROW],
ISNULL(datalength(id),0)+
ISNULL(datalength(name),0)+
ISNULL(datalength(Address),0) as RowSize
from Customer

Output of the above query will be

ROW    RowSize
1    52
2    40
3    34
4    34

Cheers!

No comments: