Q31. In a database we have a table. There are 1000 records in that table. employee id, fname, lname, I want to update the fname to lname and lname to fname. what would be your approach?
Q32. How can you find the duplicate records from a table? let say above table has multiple fname with same value?
Q33. Why we use the no-lock syntax while selecting data from tables?
Q34. What is difference between Row_Number(), Rank(), Dense_Rank() in sql?
Q35. What is cross apply in SSIS and SQL?
=========================================================================
Q31. In a database we have a table. There are 1000 records in that table. employee id, fname, lname, I want to update the fname to lname and lname to fname. what would be your approach?
Answer:
one way would be creating a temp table and then run the update query
select * into #temp
from Employees
update Employees
set Employees.lastname = t.firstname, Employees.firstname = t.lastname
from employees e
join #temp t on e.employeeID = t.employeeID
=========================================================================
Q32. How can you find the duplicate records from a table? let say above table has multiple fname with same value?
Answer:
SELECT fname, count(fname)
from employee
group by fname
having count(fname) > 1
=========================================================================
Q33. Why we use the no-lock syntax while selecting data from tables?
Answer:
1) The NOLOCK allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes.
2) This can improve query performance, but also introduces the possibility of dirty reads.
Real-life scenario --
let say a user started a transaction but not yet committed
BEGIN TRAN UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20 -- ROLLBACK or COMMIT
Now if we try below sql. it will be never ending execution as the commit is yet to done.
SELECT * FROM Person.Contact
But in case we use no lock. We might be reading a dirty data but we still able to execute the command.
SELECT * FROM Person.Contact WITH (NOLOCK)
=========================================================================
Q34. What is difference between Row_Number(), Rank(), Dense_Rank() in sql?
Answer:
Row_Number() will generate a unique number for every row, even if one or more rows has the same value.
RANK() will assign the same number for the row which contains the same value and skips the next number.
DENSE_RANK() will assign the same number for the row which contains the same value without skipping the next number.
for example for below query:
SELECT name,company, power,
Row_Number() OVER(ORDER BY power DESC) AS Row_Number,
RANK() OVER(ORDER BY power DESC) AS Rank,
DENSE_RANK() OVER(ORDER BY power DESC) AS DENSE_RANK
FROM Cars
=========================================================================
=========================================================================