Microsoft Technologies blogs(.net, Sql Server)

Delete Duplicate Records from sql server table

clock February 24, 2015 08:16 by author Arvind

This is very common question in interview how can delete duplicate records from database table in sql server and there is lots of way to achieve this goal. But some time much developer confused with some scenario. So here I am discuss solution based on scenarios. First of all there are two main thing one delete duplicate when the identity column are present and 2nd when the identity column are not in table.

When identity column exists in table than you just check this example (just copy and run you will see the result.). Yes if the identity column exists than this is simple task.

 

 

Create table tmpTable(tmpId int identity(1,1), Fullname varchar(100), Comments varchar(500))

insert into tmpTable values ( 'Amit', 'comment here')

insert into tmpTable values ( 'Amit', 'comment here')

insert into tmpTable values ( 'Suresh', 'comment here two')

insert into tmpTable values ( 'Jai', 'comment here three')

 

Select * from tmpTable

--Here is single statement which delete duplicate record

DELETE

FROM tmpTable

WHERE tmpId NOT IN

(

SELECT MAX(tmpId)

FROM tmpTable

GROUP BY Fullname, Comments)

--Here end

Select * from tmpTable

 

Drop Table tmpTable

 

2nd option when identity column are doesn’t exists in the table then

 

 

Create table tmpTable( Fullname varchar(100), Comments varchar(500))

insert into tmpTable values ( 'Amit', 'comment here')

insert into tmpTable values ( 'Amit', 'comment here')

insert into tmpTable values ( 'Suresh', 'comment here two')

insert into tmpTable values ( 'Jai', 'comment here three')

 

Select * from tmpTable

--Here is delete duplicate record

--In such case the simple way you just copy distinct result into another temp table and truncate original table

--and copy back distinct result from temp to original table and finally drop temp table

Select distinct * into #temp from tmpTable truncate table tmpTable insert tmpTable select * from #temp drop table #temp

 

--In such case this is also good 2nd query you can just use ROW_NUMBER and CTE(Common Table Expressions) and delete duplicate

;WITH CTE as(

SELECT      ROW_NUMBER() OVER(PARTITION BY Fullname, Comments ORDER BY (SELECT 1)) AS RowID,

*

FROM tmpTable

)

Delete

FROM CTE

WHERE RowID > 1

 

Select * from tmpTable

 

Drop Table tmpTable

 

Note:- Also some other option you can try yourself.

a)  You can add Identity Column and perform the operation based on identity and finally drop Identity Column.

b) You can use loop or cursor to do removing duplicate records.

c) You can use while loop as well do the same as cursor.

 

Ref:- http://support.microsoft.com/kb/70956

https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

http://support.microsoft.com/kb/139444

 

 



DefaultView of DataTable count showing 0 even if the datatable having rows

clock February 3, 2015 11:50 by author Arvind

Today I got little bit strange problem the DefaultView of DataTable (eg dt.DefaultView) is showing row count as 0 (eg dt.DefaultView.Count) where as datatable dt having rows and dt.Rows.Count showing greater than 0. So when I found such issue just thought as simple thing may be there are set RowFilterproperty which having zero result and most of the time this happened while coding. But interesting part this time when I checked RowFilter = "" set as empty and DefaultView count showing 0 where as DataTable rosw count showing greater than 0. My code as follows

//Here dt rosw count > 0 , checked RowFilter = "";

if(dt.Rows.Count > 0 )

{

DataView dv = dt.DefaultView;

      // here dv rows is showing 0

      if(dv.Count>0)

      {

            //doing other stuff here

      }

}

After some R&D we found the solution of this issue and change DefaultView as new DataView(dt); than it will work correctly.

//Here dt rosw count > 0

if(dt.Rows.Count > 0 )

{

      DataView dv = new DataView(dt);

      // Now here dv rows is showing > 0

      //dv.RowFilter = "";

      if(dv.Count>0)

      {

            //doing other stuff here

      }

}

 

So when we create new object the memory is allocated for that dataview and in that case such issue never comes again.



Calculated Running total in sql query

clock May 1, 2014 09:39 by author Arvind

There are lots of time in financial calculation the requeirment is get calculate the running total for each row. So I am writing the complete query with visible example.

Declare @TempTable Table

(

SNo int identity,

Total int

)

 

 

 

 INSERT INTO @TempTable(Total) Values(50)

 INSERT INTO @TempTable(Total) Values(60)

 INSERT INTO @TempTable(Total) Values(60)

 INSERT INTO @TempTable(Total) Values(10)

 INSERT INTO @TempTable(Total) Values(40)

 

 SELECT * FROM @TempTable

 

Select SNo, Temp.Total ,(select sum(Total) From @TempTable Where SNo <= Temp.SNo) 'Running Total'

From @TempTable Temp

 



Disabled button after click while processing in asp.net using javascript

clock April 7, 2014 11:59 by author Arvind

There is lots of way to prevent double clicking while postback process but this is the simple way to do that using window’s onbeforeunload eg. 

On aspx page your button control

<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Clicked" />

Javascript code for that

<script type = "text/javascript">

function DisableButton()

{

document.getElementById("btnUpdate").disabled = true;

 }

window.onbeforeunload = DisableButton;

</script>

 

Reference: http://msdn.microsoft.com/en-us/library/ie/ms536907(v=vs.85).aspx



Create database using SMO instead of DMO in Sql server in C#.net

clock December 31, 2013 10:04 by author Arvind

Microsoft introduces SMO as new feature in sql server 2005 onward version. This is the lots of advantages over old DMO for manage sql server using program. SQL Server Management Objects (SMO) is objects designed for programmatic management of Microsoft SQL Server. One of my old projects using the DMO to create database and update all script related to sp/view and master data records as well as jobs. Once running server upgraded the sql server 2000 to sql server2012 after that this feature is stop working because of DMO no longer supported in sql server 2012. So now I made change code using SMO instead of old DMO and I love new feature of SMO.

I create one web services for the creating database and run script/jobs etc. by simply calling that method into existing application.

First of all just add reference of SMO dll in to web service project and use these on top

 

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo.Agent;

 

After that wrtie web method to create database and runt the sql script file for all stored proc/view/table etc.

 

[WebMethod]

        public bool CreateDataBase(string strSQLServer, string strDBName, string SqlUserName, string Sqlpwd,string strSchemaScriptPath, string strDataScriptPath)

        {

            bool flag = false;

            //here first of all create connection object

            ServerConnection objSc = new ServerConnection(strSQLServer, SqlUserName, Sqlpwd);

            //get server object based on connection

            Server objSrv = new Server(objSc);

            //create database object to create desired database using code.

            Database objDB = new Database(objSrv, strDBName);

            try

            {

                objDB.Create();

                //After creation of database create schema script

                FileInfo fileSchema = new FileInfo(strSchemaScriptPath);

                string scriptSchema = fileSchema.OpenText().ReadToEnd();

                objDB.ExecuteNonQuery(scriptSchema);

                //After createion os schema need to inser datascript

                FileInfo fileData = new FileInfo(strDataScriptPath);

                string scriptData = fileData.OpenText().ReadToEnd();

                objDB.ExecuteNonQuery(scriptData);

                flag = true;

            }

            catch (Exception ex)

            {

                string str = ex.Message;

                flag = false;

            }

            finally

            {

                objDB = null;

            }

            return flag;

        }

 

In above web method only you need to pass the sql server instance name, database name, sql server uid/pwd and script for schema and data script if any.



Paging in T-Sql using OFFSET and FETCH NEXT in Sql server 2012

clock December 5, 2013 06:19 by author Arvind

Microsoft introduced lots of good feature in Sql Server 2012. One of them is pagination (paging with OFFSET and FETCH NEXT). Pagination in Sql server improves performance mainly when retrieving and displaying large set of records from Database.

The OFFSET and FETCH NEXT clause is basically an extension of existing ORDER BY clause in T-SQL. This feature is very helpful for developer to get only desired number of records which they would like to display in ASP.NET Data Grid or other control instead of getting the all records. So using this feature developer can implement the paging on page with faster and only get desired records. It can be used only with the ORDER BY clause.

Actually by using OFFSET the pointer set to that record to skip those and fetching the record after that using NEXT command.

 

Syntax and example:-

[ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n][<offset_fetch>] ]

AND

<offset_fetch> ::= {OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }    [FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY]}

 

 

We can use this feature in two different cases

Case 1: Skip first 100 rows from the result set and return the remaining rows

SELECT FirstName + ' ' + LastName FROM Employees ORDER BY FirstName OFFSET 100 ROWS

 

Case 2: Skip first 100 rows from the resultset and return next 50 rows.

SELECT FirstName + ' ' + LastName FROM Employees ORDER BY FirstName OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY

 

 

Limitations of OFFSET-FETCH clause:-

1. ORDER BY is mandatory to use OFFSET and FETCH clause.

2. OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.

3. TOP cannot be combined with OFFSET and FETCH in the same query expression.

4. The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries

 

I love this feature and hope you all enjoy this too………….

 

I also sharing one good query regarding the interview get nth highest salary of the employee with the help of OFFSET FETCH clause.

Eg- Suppose you have to write query for getting 51the highest salary of the employee you can write this simple query as follows

Select Salary From EmpSalDetails Order By Salary Desc OFFSET 50 ROWS FETCH NEXT 1 ROWS ONLY

 

It will return the 51st highest salary of the employee.

Ref:-http://technet.microsoft.com/en-us/library/gg699618.aspx



Create data script in sql server 2008/2012

clock December 2, 2013 12:48 by author Arvind

In new version of Sql server having good feature to generate data script automatically by selecting the option, this feature is very helpful when you are migrating data or updating production from test server. This is the simple step to generate data script of a table or whole database.

 

Step 1: Right click on database name and select “Tarsks” option in menu as in figure.

Step2: After selecting “Tasks” menu there are submenu comes up, in that submenu you can select “Generate script…” option.

Step3: Open “Generate and Publish Scripts” dialog box in that box click “Next” button to proceed.


Step4: After clicking “Next” button you can select the script option either one table or all table in that database or other database object eg views, stored procedures etc and click “Next” button.

Step 5: After selecting table you can click “Advanced” button to choose data script option.

Step 6: In “Advanced Scripting Options” you can select the “Type of data to script” either “Data only”, “Schema and data” and “Schema only” option as shown in figured.

Step 7 : If you selected “Data only” and click “Ok” and than “Next” and “Finish” button you will get all data script of the selected table.



Password for E-Aadhar card

clock November 13, 2013 04:54 by author Arvind

InIndianow a day everyone appling for Unique Identification Authority of India (UIDAI) and provide one option to get E-Aadhar card by downloading from there official website. But there are one problem facing lots of user after download the E-version of Aadhar Card that are not opening due to password protected. So I am writing this post to avoid the difficulty facing in opening the pdf e-aadhar card file.

 

Just open the E-Aadhar card pdf and the asking for password, your password for that pdf will be your postal address pin code/zip code(Which you given at the time of filling Aadhar card form).

Enjoy.......



Get two Database tables record within one stored procedure

clock August 6, 2013 11:44 by author Arvind

Sometime we need to get record from two different database tables record. So in such type of requirement if both databases are on same sql server than we can easily get two tables from different database within a stored procedure, that stored procedure may put in any one database where you want.

eg. Let consider two database db1 (table1) and db2 (table2) are on same sql server having two different table table1 and table2 respectively.

Now in db1 we write one stored procedure that need to fetch records from both tables as follows.

In db1, we can get data from table2 of db2 by using (double ..) as follows

Select * from db2..table2

If you want to get record from both tables using join condition than you store procedure should be looks like

Create Proc getDataFromTwoTable

AS

Begin

      Select * from db2..table2 T1 Inner Join table1 T2 ON T1.Id = T2.Id

 

End



How can toggle the word wrap in visual studio.net

clock August 2, 2013 06:29 by author Arvind

Once developer working in any environment there are some simple shortcut are more helpful.

Here just remind the shortcut or option for toggle the line in visual studio.net

Visual studio.net 2003 and 2005:-

Menu option: - Edit->Advanced->Word Wrap

Shortcut option:- ctrl+R+R Or ctrl+ R and ctrl+R (Two times)

Visual studio.net 2008, 2010 & 2012:- 

Menu option:- Tools -> Options -> Text Editor -> All Languages -> Word Wrap

 

Shortcut option:- ctrl+E+W



RecentComments

Comment RSS

Sign In

Akumar