Difference between null and coalesce

ISNULL

ISNULL function is used to replace the NULL value with specified value.
It contains only two arguments.
Same data type not compulsory.

Example -1:

SELECT ISNULL(NULL,'Raki') AS [ISNULL]

Output: Raki

Example -2:

DECLARE @name VARCHAR(10)

DECLARE @marks INT =500

SELECT ISNULL(@name,@marks) AS [ISNULL]

Output: 500

Example-3:

SELECT ISNULL(NULL,NULL,'Raki') AS [ISNULL]

Output:

Msg 174, Level 15, State 1,

The isnull function requires 2 argument(s).

COALESCE

Coalesce function is returns first non null value among arguments.
It contains multiple arguments.
Same data type compulsory for arguments or precedence data type order should follow.

Example-1:

SELECT COALESCE(NULL,NULL,'Raki') as [COALESCE]

Output: Raki

Example-2:

DECLARE @name VARCHAR(5)='Raki'

DECLARE @marks INT =500

SELECT COALESCE(@name,@marks) as [COALESCE]

Output:

Msg 245, Level 16, State 1, Line 3

Conversion failed when converting the varchar value 'Raki' to data type int.

Comments

Rank Functions in SQL SERVER

1 . ROW_NUMBER () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns the sequantial number of a row within the a partition of result set at 1 for the first row of the each partition. 2. RANK () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns rank for rows within the partition of result set. 3. DENSE_RANK () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns rank for rows within the partition of result set.With out any gaps in the ranking. 4. NTILE ( INTEGER_EXPRESSION ) OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Distributes the rows in an ordered partition into a specified number of groups. Examples: --create Employee table create table Employee ( EmpId int identity ( 1 , 1 ) primary key , ...

@@SERVERNAME

@@servername returns the name of the server currently running. Syntax: @@SERVERNAME SELECT @@SERVERNAME O/P sys

Difference between LEN and DATALENGTH

LEN: LEN function returns the number of characters in a variable .it also removes the trailing spaces and then then return the length. Example-1: DECLARE @Name VARCHAR ( 20 )= 'rakesh' SELECT LEN ( @Name ) as [len] Output: Example-2: DECLARE @Name VARCHAR ( 20 )= 'rakesh ' SELECT LEN ( @Name ) as [len] Output: When we observe above variable assigned 'rakesh ' string after that added 3 spaces . Len function removes trailing spaces not leading spaces. DATALENGTH : DATALENG function returns the number of bytes occupy in a variable .it also considered the spaces also. Example-1: DECLARE @Name VARCHAR ( 20 )= 'rakesh' SELECT DATALENGTH ( @Name ) as [DataLength] Output: Example-2: DECLARE @Name VARCHAR ( 20 )= ' rakesh ' SELECT DATALENGTH ( @Name ) as [DataLength] Output: In above example before ' r ' and after ...

Tech Learn

Search This Blog