Task 15: Delete Duplicate records

      Methods to remove duplicate records in SQL Server:

1. When we receive more than one rows of records, we need to remove the row which is not the first one and we can do this like below:


/* Delete Duplicate records */
WITH CTE (col1,col2,col3, DuplicateCount) 
AS 
(
SELECT col1,col2,col3, ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1) AS DuplicateCount
FROM table_name 
)
DELETE FROM CTE 
WHERE DuplicateCount > 1

Task 14: Pagination

I’m sharing a pagination method that I used in my scripts with a Common Table Expression and the RowNumber function and this seems to be the more elegant method to display a number of lines per page. You can integrate the CTE in a stored procedure or add it as a dynamic script.

DECLARE @PageIndex INT = 1, @Pagesize INT = 10

;With CTEPage

AS

(

SELECT 

ROW_NUMBER() OVER(ORDER BY PageName,PageId) as RW,

PageId, 

PageName 

FROM PaginationTable

) 

SELECT PageId, PageName FROM CTEPage

WHERE RW BETWEEN (@PageIndex - 1) * @Pagesize AND ((@PageIndex - 1) * @Pagesize) + @Pagesize

GO

The PageSize and PageIndex can also be added as parameters in a stored procedure:

CREATE PROCEDURE [dbo].[Report_pagination]
   @PageSize INT = 10, @Page INT =1
AS
SET NOCOUNT ON;
CREATE TABLE #temp ( ...)

;WITH PageNumbers AS
(
SELECT 
  columns,
          ROW_NUMBER() OVER (ORDER BY col_name) AS rownum

FROM #temp
 LEFT JOIN table_name
)

SELECT  *
FROM    PageNumbers
WHERE   rownum  BETWEEN ((@Page - 1) * @PageSize + 1)
        AND (@Page * @PageSize)
ORDER BY col_name

Recent books

A lot of changes in the last time but here I am:

 
  I read about some interesting books based on SQL Server 2012\ BI and I taught it would be nice sharing them: