Here I’m going to show you the query to get all the duplicate records from a table. Below is a sample table Employee which has the duplicate data “Bangalore” in the Location column.
You can use the query mentioned below in order to get the duplicate records.
One of the ways to improve performance is to fetch only the required data from the database. By implementing a pagination technique in the stored procedure, it would help us in getting only the required set of records. This can be made possible by making use of ROW_NUMBER() which was introduced in SQL Server 2005. Below is a sample query which would fetch 10 records for each page.
ROW_NUMBER() requires the ORDER BY clause inorder to determine the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. Hence as you see in the above query, I’ve used the “Name” column. By passing the @index value as 0, 10, 20…you can get the pagination effect for your data.
I have a table EmpData that has a column EmpData which is of xml data type. Below shown is the data of the table.
Now in case you would want to search the xml for a specific data. The query for that would be as shown below
In situations where you need to find the size of a particular data stored in one of the fields in your database, especially if you have binary data like images stored, this query would be helpful.
SELECT DATALENGTH(coloumnName) FROM tableName