Length Vs Data Length Vs Column Length

                                                                    Part - 7

(You Tube Video Answer)

Q1. What is Length function in SQL Server? (LEN())

Returns the number of characters of the specified string expression, excluding trailing spaces.

Syntax: LEN (string_expression )

Q2. What is the output of the below query? (one leading space) 

Declare @len1 nvarchar (30) = ' Peter'

SELECT LEN(@len1) as Length 

Q3. What is the output of the below query? (one trailing space)

Declare @len2 nvarchar (30) = 'Peter '

SELECT LEN(@len2) as Length

Q4. What is DataLength function in SQL Server? (DATALENGTH())

Returns the number of bytes used to represent any expression including trailing spaces.

Syntax: DATALENGTH (string_expression )

Q5. What is the output of the below query? (one leading space)

Declare @len3 nvarchar (30) = ' Peter'

SELECT DATALENGTH(@len3) as Length 

Q6. What is the output of the below query? (one trailing space)

Declare @len4 nvarchar (30) = 'Peter '

SELECT DATALENGTH(@len4) as Length

Q7. What is the output of below queries?

Declare @len5 Varchar (7) = NULL

SELECT LEN(@len5) as Data_Length

SELECT DATALENGTH(@len5) as Data_Length

Q8. What length would be taken by below datatypes when explicit length of conversion is not mentioned? (1 followed by 35 0's in below example)

SELECT LEN(CONVERT(VARCHAR,'100000000000000000000000000000000000'))

SELECT LEN(CONVERT(NVARCHAR,'100000000000000000000000000000000000'))

SELECT LEN(CONVERT(CHAR,'100000000000000000000000000000000000'))

SELECT LEN(CONVERT(NCHAR,'100000000000000000000000000000000000'))

Q9. What length would be taken by below data types and what would be the output?

DECLARE @CHAR CHAR = 'TESTING'

SELECT @CHAR

Below table is used to represent employee table

DROP TABLE IF EXISTS [dbo].[tblEmployee]

CREATE TABLE [dbo].[tblEmployee](

[EmpName] [varchar](30) NOT NULL, -- With Varchar, With Nvarchar

ManagerName [Nvarchar](40) NOT NULL,

) 

GO

INSERT INTO [dbo].[tblEmployee]

           ([EmpName],ManagerName)

           VALUES

           ('smith','Robert') ,('Peter','Smith') ,('Mark','John') ,(' John','Peter')

GO

Q10. What is Col_Length function in SQL Server? (COL_LENGTH())

Returns the defined length of a column, in bytes

Syntax: COL_LENGTH ('table name','column name') 

Q11. What is the output of the below query?

USE SqlWithRaviMartha

GO

SELECT COL_LENGTH('dbo.tblEmployee','EmpName') AS EmpName_ColLength

Q12. What is the output if the user is not having permission to view the object / if we provide wrong column value or wrong database context?

Answer: It will return NULL