With the DATALENGTH function, we can calculate the size of the data in a column in bytes. Usually, its useful for calculating the size of lob columns.
Let’s make examples for a better understanding of DATALENGTH Function.
Example:
First, we create a table with the help of the following script and add a few records into this table.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE [dbo].[Student]( [Id] [int] IDENTITY(1,1) NOT NULL, [StudentName] [varchar](50) NULL, [StudentSurname] [varchar](50) NULL, [StudentEmail] [varchar](50) NULL, [StudentScore] [int] NULL, CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] |
After creating the table, I will add 3 records to my table using the following script.
|
1 2 3 4 5 6 7 8 |
INSERT INTO [dbo].[Student]([StudentName],[StudentSurname],[StudentEmail],[StudentScore]) VALUES('Nurullah','CAKIR','nurullah.ckr@gmail.com',65) INSERT INTO [dbo].[Student]([StudentName],[StudentSurname],[StudentEmail],[StudentScore]) VALUES('FARUK','ERDEM','y@gmail.com',90) INSERT INTO [dbo].[Student]([StudentName],[StudentSurname],[StudentEmail],[StudentScore]) VALUES('DİLARA','AYDIN',' x@gmail.com ',90) |
DATALENGTH Function Usage:
|
1 2 3 4 5 |
SELECT DATALENGTH(StudentName) AS StudentName_Size_Byte, DATALENGTH(StudentSurname) AS StudentSurname_Size_Byte, DATALENGTH(StudentEmail) AS StudentEmail_Size_Byte, DATALENGTH(StudentScore) AS StudentScore_Size_Byte FROM [dbo].[Student] |
