DATALENGTH () function in sql server

It returns the number of bytes which are used or required to represent an expression.

Syntax:

DATALENGTH(expression)

See the below example

 

Keep in mind it will return the bytes which take to store this data. In the above example I used varchar so it returned 6 if we use name as nvarchar then see what it will return.

 

If expression return NULL value then it will return null.

 

It includes the trailing blank spaces while calculating the number of bytes used/required to represent an expression. See below example

declare @name varchar(50); declare @name1 nvarchar(50); set @name ='    Bagesh  '; set @name1='    Bagesh  '; select DATALENGTH(@name) as varchar_length,        DATALENGTH(@name1) as Nvarchar_lenght

           

\   

It support Text, NText and Image data type. See the below example.

                        

LEN () function in sql server

This function is used to get the length of the string. It returns the number of characters in a variable. It also removes the trailing spaces and then returns the length.

Syntax

LEN(expression)

See the example

declare @name varchar(50); set @name='Bagesh'; select LEN(@name)

It will return 6

 

It excludes the trailing blank spaces while calculating the number of characters in the specified string.

See this example

 

Keep in mind: it excludes trailing black space only as we see above.

If the string is followed by blank spaces then it will be countable.

If we want to remove the blank spaces we need to use LTRIM() function after that we use the Len function to get the length of this string.

 

If the expression return NULL then LEN function will return NULL value.

 

If we want to return 0 if the expression return NULL then we need to use ISNULL function see below.

Read: Different ways to replace NULL value in sql server

https://bageshkumarbagi-msbi.blogspot.com/2015/12/different-ways-to-replace-null-in-sql.html

It will return the number of character only does not mean which data type we are using.

It does not support Text, NText and Image data type. See the below example.

 

Sort the data based on the case sensitive (COLLATION)

SQL Server is, by default, case insensitive; however, it is possible to create a case-sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine if a database or database object is to check its "COLLATION" property and look for "CI" or "CS" in the result.

• CI = Case Insensitive
• CS = Case Sensitive

We can check our sql server

  

It is case insensitive.

My requirement is to sort the data based on the case sensitive.

Let see below example

I am using “AdventureWorks2012” database and writing the below sql script

Normal sorting

SELECT p.Name, h.EndDate, h.ListPrice FROM Production.Product p INNER JOIN Production.ProductListPriceHistory h ON p.ProductID = h.ProductID ORDER BY p.Name ASC

“All-Purpose Bike Stand” is first record.

Now I am doing the sorting bases on the case sensitive (Capital and small letter)

SELECT p.Name, h.EndDate, h.ListPrice FROM Production.Product p INNER JOIN Production.ProductListPriceHistory h ON p.ProductID = h.ProductID ORDER BY p.Name COLLATE Latin1_General_BIN ASC

   

This sorting do first capital letter after that small.

Which is better to use IN clause or inner join with variable table

When we are using the IN clause, we experienced that it is taking too much time.

Read about IN clause in SQL server

 https://bageshkumarbagi-msbi.blogspot.com/2019/10/in-clause-in-sql.html

For better performance, we need to avoid the IN clause. In place of in clause, we can use variable table.

Let’s see the demo.

 I have a table DimProduct_test in this we have approx. 4 million records.

     

Now we need to get the product details where ProductAlternateKey in

('BA-8327','BC-M005'

,'BK-M18B-44','BK-M18B-52'

,'BK-M38S-42','BK-M68B-38'

,'BK-M68B-42','BK-M68B-46'

,'BK-M68S-38','BK-M82B-42')

and Color in ('Black','Blue','Grey','Multi','Red','Silver'

,'Silver/Black','White','Yellow')

Using IN clause

   

For the 77.5k records, it is taking approx. 7 seconds.

Now I am using variable table

  

It is taking approx. 4 seconds.

This is simple sql script. If we are working with high volume of data then we will see the execution time differences on both.

IN clause in sql

The SQL IN condition (sometimes called the IN operator or clause) allows us to easily test if an expression matches any value in a list of values. It is used to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE or DELETE statement.

We can use a subquery in the IN condition

Let’s see the syntax

IN (v1,v2,v3,….vn) or IN (select C1 from t1)

See below example

I have a table DimProduct_test in this we have approx. 4 million records. Now we need to get the product details where ProductAlternateKey in

('BA-8327','BC-M005'

,'BK-M18B-44','BK-M18B-52','BK-M38S-42','BK-M68B-38','BK-M68B-42','BK-M68B-46'

,'BK-M68S-38','BK-M82B-42')

SElect * from DimProduct_Test where ProductAlternateKey in ('BA-8327','BC-M005' ,'BK-M18B-44','BK-M18B-52' ,'BK-M38S-42','BK-M68B-38' ,'BK-M68B-42','BK-M68B-46' ,'BK-M68S-38','BK-M82B-42')

See the result

   

We can also use the not in IN clause

Not IN (v1,v2,v3,….vn) or Not IN (select C1 from t1)

See the example

Get the product details where ProductAlternateKey not in

('BA-8327','BC-M005'

,'BK-M18B-44','BK-M18B-52','BK-M38S-42','BK-M68B-38','BK-M68B-42','BK-M68B-46'

,'BK-M68S-38','BK-M82B-42')

 

Insert Null Value in Unique column (Enforcing Uniqueness with NULL)

In a table ( Emp)  we have a unique column  PAN number. PAN Number is unique for every employee. Our requirement is that we need to insert null value those don’t have PAN number. All we know that in the unique column it allows only one null value.

Read: Difference between PRIMARYKEY and UNIQUE KEY

http://bageshkumarbagi-msbi.blogspot.com/2017/05/difference-between-primary-key-and.html

See the demo

Creating the emp table with unique key index

CREATE TABLE emp(                 ID int IDENTITY(1,1) NOT NULL,                 E_name varchar(50) NULL,                 E_mobile varchar(10) NULL,                 E_add  varchar(50) NULL,                 PAN_Number     varchar(10)                 ) CREATE UNIQUE INDEX UX_emp_PAN_Number ON emp (PAN_Number) ;

Table and index created successfully.

 

Now I am inserting some records in this table

Insert into emp (E_name,E_mobile,E_add,PAN_Number)values ('Bagesh','888880XXXX','Greater Noida','PAN123BAGI') Insert into emp (E_name,E_mobile,E_add,PAN_Number)values ('Rajesh','888880AAAA','Noida','PAN123RAJE') Insert into emp (E_name,E_mobile,E_add,PAN_Number)values ('Kumar','888880KKKK','Delhi','PAN123KUMA') Insert into emp (E_name,E_mobile,E_add,PAN_Number)values ('Ravi','888880RRRR','Pune','PAN123RAVI') Insert into emp (E_name,E_mobile,E_add)values ('Rakesh','888880RRRR','Pune')

See records in the table

  

Now I am inserting one more record which PAN number is null.

Insert into emp (E_name,E_mobile,E_add)values ('Mohan','888880MMMM','Pune')

Running this query

 

Oooo it does not allow us to insert another record.

But our business requirement is to allow these records in the table. To overcome this issue, when we are creating the unique key index we need to allows filtered indexes where the index is created only for a subset of the data in the table.

Now I am dropping the existing index.

 

Now I am creating the filtered indexes

CREATE UNIQUE INDEX UX_emp_PAN_Number ON emp (PAN_Number) WHERE PAN_Number IS NOT NULL

 

Unique key index is created successfully. Now I am inserting above record.

 

Record inserted successfully with NULL value. See the records in the table.

Keep In Mind only it allow null value. If we try to insert some other duplicate Pan number it will not allow us to insert. See below

Hope this will help on such type of scenarios.

Thanks!  Keep reading!!!!!!!!!!!!!

Conditional Aggregation in sql server

Sometimes we need to get the aggregation value of the column like get the number of a male and female employee or we need to get the average salary of the male and female employees. In this case, we can use CASE expression to get the expected result.

Let’s see this example.

I have an employee table and I want to get the count of the male and female employees.

declare @emp table (emp_id int,  emp_name varchar(100),  gender char(1)) insert into @emp select 1,'Bagesh','M' union all select 2,'Kumar','M' union all select 3,'Reshama','F' union all select 4,'Naresh','M' union all select 5,'Mahesh','M' union all select 6,'Pari','F' union all select 7,'Sonnam','F'

Here I am using the CASE Expression

select sum(case when gender='M' then 1 else 0 end) as male_count, sum(case when gender='F' then 1 else 0 end) as female_count from @emp

Running this script

           

Hope this will help!