Rohit Dhand

In case we have a table that does have any primary key and contains two rows that have exactly same values. For e.g. consider a table named Dept which has two columns: a. Deptno b. Dname And the data in the table is: Deptno Dname 20         Marketing 20         Marketing We can see Deptno column contains two entries with value = 20 and Dname contains same values also. This happened as the record was inserted because, no primary key was present. We cannot delete the row with a delete command. In case we want to do the same we can use Common Table Expressions and Row_Number() function: With MyCTE as  ( Select row_number() over (order by deptno) as 'RowNumber', * from Dept ) Select * from MyCTE The above command will get the following result: RowNumber Deptno Dname 1                 20         Marketing 2                 20         Marketing Now as we have generated RowNumber for a run time column, we can use the sam

Sometimes we require to fetch a number of rows from a table but on a random basis. This can be achieved by using newid() function in SQL Server. For the explanation purpose I have taken the following table and columns to run the query. Table: Mydept Deptno Dname 10 Microsoft 20 Marketing 30 HR 40 SALES 60 LEGAL 80 MANAGEMENT 50 INFOTECH 70 PROMOTION 90 FACULTY Query to get 4 random rows from the MyDept Table Select top(4) deptno, Dname from MyDept order by newId() The result of the query is: deptno Dname 80 MANAGEMENT 30 HR 10 Microsoft 60 LEGAL We can pass on 3, 4, 5 or any number in the top keyword for fetching n number of random rows from the table.