SQL SERVER date functions & some common points

1. To know current year

 

    select datepart("yy",getdate())

    select year(getdate())

2. To know current month

    

    select datepart("mm",getdate())

    select MONTH(GETDATE())

 

1. How to clear procedure cache
DBCC FREEPROCCACHE
we can use sp_recompile system procedure to recompile the stored procedure
at the time of creating the sp we can add a tag WITH RECOMPILE. This causes the stored procedure to be compiled every time when it is called.

2. How to view the contents of a stored procedure
sp_helptext

3. Difference between Logins and Users
Loing is to establish a connection to the database where as an user is to access a particular database in a sql server.

4. What is sql server Role
Roles contain specific properties & an user can belong to one or more roles.

5. delete duplicates: emp
ex: ID Name Value
1 a 10
2 b 20
3 c 30
4 b 20
5 d 40

DELETE FROM emp
where
ID NOT IN (SELECT MAX(ID) FROM emp e2 WHERE e2.Name = emp.Name AND e2.Value=emp.Value)

(or)

DELETE FROM emp
WHERE
ID > (
SELECT MIN(ID) FROM emp e2 WEHRE e2.Name = emp.Name AND e2.Value = emp.Value
)

(or)

DELETE FROM emp
WHERE
ID <
(
SELECT MAX(ID) FROM emp e2 WHERE e2.Name = emp.Name and e2.Value = emp.Value
)



case 2: If all the columns have same value for ex:
Name Value
1 10
1 10
2 20
2 20

in this scenario

select * into into #temp
(select distinct Name, Value FROM emp)
as T

delete from emp
insert into emp select * from #temp


solution2: Instead of using temp tables, we can do one more thing here.
create another table with this structure. Now add a new column called Id to this table with identity one.
Insert values from previous table to this new table. Drop the previous table. Now this case became case 1.


6. Retrieving Row number
SELECT ROW
select row_number() over(order by ename asc) as rownum from duptest


7. Retrieving Nth highest salary using co-related subquery:

SELECT * FROM emp e1
WHERE
0 = (select count(distinct sal) from emp e2 where e2.sal> e1.sal)