Delete Duplicate Rows in Sql Server

Article will cover some best practices on how to delete duplicate rows in sql server table.

We’ll be using a student table. Query for the table creation

Inserted 4 rows into the table, row with roll Number one is repeated twice.

delete duplicate rows in sql server
select * from tbl_Student
following query can be used to check duplicate rows based on rollNumber,

Query result will look like this

delete duplicate rows in sql server
after insertion of rows

and shows that rollNumber with ‘1’ is repeated 2 times.

here I will be explaining two methods for deleting duplicate rows.

Method 1 – by keeping original

In this method all duplicate rows will be deleted except the original copy. Check this query

after deletion, student table will be shown as below

delete duplicate rows in sql server : by keeping original

Method 2 – without keeping original

In this method all repeated rows including original copy will be deleted.

query

resulting table will be like this

delete duplicate rows in sql server

Difference Between String And StringBuilder In C#

Introduction

Difference between string and stringbuilder is one of the frequently asked question in all interviews and all of us answer something in common

String is Immutable and StringBuilder is Mutable

This is a one word answer. In this article I would like to dig things in more details

Don’t Ignore Basics 

             Both String and Stringbuilder represent sequence of characters. When we list out their differences some of us don’t remember basic difference. That is String Class is in the System Namespace While StringBuilder is in System.Text.

Sometimes Mute is Safe 

          Let us make the statement again “String is Immutable and StringBuilder is Mutable” [Don’t be Confused about which one is mutable and which one is not].

Immutability of string object Means, If any of your operation on the string instance changes it’s value, it will end up in the creation of new instance in a different address location with modified value. Mutability of StringBuilder is just opposite for this.It won’t create new instance when their content changes as string does.Instead it makes the new changes in the same instance.

Mutability and Immutability of these two can be understood from following C# Program.

difference between string and stringbuilder in c# : string immutability in c#
string instance change

in line 11 content of str changes, so new instance is created in a different memory location with new value as shown in above image.

Even Though line 14 changes the value of stringbuilder variable sbr it won’t create a new instead it will keep appending new strings to existing instance.see how it looks in terms of memory

difference between string and stringbuilder in c# : stringbuilder mutability in c#
stringbuilder instance changes

Because of this behaviour of StringBuilder it also known as Mutable String.

I am not convinced

If you say I’m not convinced. let us check these behaviours using  C# code snippet. For that I am using C# class ObjectIDGenerator(in System.Runtime.Serialization Namespace). Actually it will return an unique integer value for instances that we created in our programs.With the help of this class we can check whether new instance is created or not for various operations on string and stringbuilder .Consider following program


Output Will look like this

difference between string and stringbuilder in c# : mutability and immutability in c#
program output

Did you see that..?, Instance id for string get changed from 1 to 2 when str concatenated with “Hello World”.while instance id of sbr remains same as 3 after append operation also. This tells all about mutability and immutability. blStatus variable indicate whether the instance is new or not. Now you are convinced right?

Who Run Faster?

           Let’s discuss performance difference between string and stringbuilder.The following code box will explain things in a better way. Before that if you don’t know how to measure execution time in C# you can read my article from here .

Output of this program was

difference between string and stringbuilder in c# : performance difference between string and stringbuilder
who run faster ?

This output clearly shows their performance difference.StringBuilder is about 70X faster than String in my laptop. it might be different in your case but generally speaking stringbuilder gives 10x times speed than string.

One Last Thing

New Instance of string will be created only when it’s value changes

One more thing,If you do an operation on a string variable, Creation of new instance occurs only when it’s current value changes.try following code,

Output!

difference between string and stringbuilder in c# : mutability and immutability in c#

initial and final id is 1-means new instance is not created for these operation since it does not change the value of str.You may wonder about how compiler shows this much intelligence.

Shoot Your Doubts

»Should I Use StringBuilder Everywhere?

»Why .NET String is immutable?