SQL Important Query

Rows Count  Methods 
1. 
SELECT row_count

FROM sys.dm_db_partition_stats

WHERE object_id=object_id('emp_master') and index_id<2

2.

SELECT rows

FROM sysindexes

WHERE id=object_id('emp_master') and rows>0

3.

sp_spaceused 'table_nmae'

4.

select count(@@rowcount) from emp_master

Highest Salary

select max(Emp_Sal) from Employee_Test

3rd Highest Salary

select min(Emp_Sal) from Employee_Test where Emp_Sal in

(select distinct top 3 Emp_Sal from Employee_Test order by Emp_Sal desc)

Deleting duplicate rows from a table

with T as

(

select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank

from employee_test1

)

delete from T

where rank > 1

Query for Select Randam no. record from a Table
Select Top 1 *
from
(
Select EmpCode,EmpName
from Employee_Master
--Where EmpName like 'A%'
) abc
ORDER BY NEWID() 

How to know how much space is consumed by each table in your database

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'" 

Shrink Databse

DBCC SHRINKDATABASE(0)

Insert Vaues in Identity Column manually by user

SET IDENTITY_INSERT TableName ON

INSERT INTO EmployeeTable(EmpCode)
VALUES('1234')

SET IDENTITY_INSERT YourTableName OFF 

Query for find employee who is living in more than one citySELECT empname, fname, lname
FROM employee
WHERE  empid IN
(SELECT empid FROM city GROUP BY empid
  HAVING COUNT(empid) > 1))

Update  Employee Master for Salary hike in the following conditions

UPDATE emp_master
SET emp_sal =
CASE
WHEN emp_sal > 0 AND emp_sal <= 20000 THEN (emp_sal * 1.01) WHEN emp_sal > 20000 THEN (emp_sal * 1.02)
END 
Find top salary from two tables
SELECT TOP 1 sal FROM (
SELECT MAX(sal) AS sal FROM sal1
UNION
SELECT MAX(sal) AS sal FROM sal2
) a
ORDER BY sal DESC 
Find 6th highest salary
 SELECT TOP 1 salary FROM (
SELECT DISTINCT TOP 6 salary FROM employee
ORDER BY salary DESC) a
ORDER BY salary 

Find duplicate rows in a table?
SELECT sid, mark, COUNT(*) AS Counter
FROM marks
GROUP BY sid, mark
HAVING (COUNT(*) > 1)
How many tables contains empno as a column in a database?
SELECT COUNT(*) AS Counter
FROM syscolumns
WHERE (name = 'empno')
Query for grand total salary, and total salaries of individual employees in one query.
SELECT empid, SUM(salary) AS salary FROM employee
GROUP BY empid WITH ROLLUP
ORDER BY empid

Three tables – student , course, marks – how do go @ finding name
of the students who got max marks in the diff courses.

SELECT s.name, c.name AS coursename, m.sid, m.mark
FROM Marks m 
INNER JOIN Student  s ON m.sid = s.sid 
INNER JOIN Course c ON m.cid = c.cid
WHERE m.mark =(SELECT MAX(Mark)
FROM Marks mm
WHERE mm.cID = m.cID)

Inserted Keyword in Sql2008

create table personbackup

--person

(

  id int primary key,

  fname varchar(10) not null,

  lname varchar(10) not null,

  city varchar(10) not null

)

INSERT INTO person OUTPUT INSERTED.City

VALUES(3,'Ratan','Gupta','Jaipur')

If you want to try the examples in this article, you should first run the following script to create the Person table in a SQL Server database:

Output Keyword in Sql2008

 update person

set

    fname='atul',

    lname='Kumar'

output

    inserted.id,

    deleted.fname,

    deleted.lname,

    inserted.city

where

id=3

Output Keyword in Sql2008

delete from person

output deleted.id,deleted.fname,deleted.lname,deleted.city

into personbackup

where id=3

SElect * from person

select * from personbackup

 

THROW Keyword in Sql2008

Select * from Branch_master

SELECT * FROM Branch_master

WHERE brhcode = 22222

IF @@ROWCOUNT = 0

THROW 50001, 'No results found.', 1

delete duplicate row
set rowcount 1
delete a from emp a where (select count(empcode) from emp b where a.empcode = b.empcode)>1
while @@Rowcount>=1
delete a from emp a where (select count(empcode) from Emp b where a.empcode = b.empcode)>1
set rowcount 0

delete Nthe Max sal emp
Get Nth max salary
Select a.* from Emp a
where 1 = (select count(distinct b.Salary) from Emp b where a.salary<=b.salary) 


User Defined Data Types

CREATE TYPE dbo.EmpDet

AS TABLE

(

      Ids int,

      Name varchar(30),

      RegCode int

)    

GO

Alter proc dbo.RAM(@Emp dbo.EmpDet READONLY)

As

select b.regname,a.Ids,Name  from @Emp a

      inner join Reg_Master  b on a.regcode=b.regcode

order by regname,name

declare @Abc as dbo.EmpDet

Insert into @Abc(Ids,Name,regcode)

select brhcode,left(brhname,30),regcode

from br_master

Exec dbo.RAM @Abc

  

For Shrink Database

use RSMS_DB
alter database RSMS_DB set recovery simple
dbcc shrinkfile (RSMS_DB_log)
alter database RSMS_DB set recovery full  

delete duplicate

with abc as(
select row_number() over(partition by quota order by quota desc)qt,* from abc1
)
select * from abc where qt=1

for 2nd highest salary
 
with abc as(
select DENSE_RANK() over( order by quota desc)qt,* from abc1
)
select * from abc where qt=2

Another method for 2nd highest salary

select a.* from employee a
where 1= (select count(distinct salary) from employee b where a.salary<=b.salary) 

Cursor Example

declare @BrhCode varchar(5)
declare @BrhName varchar(50)
DECLARE TBName CURSOR
For
select BrhCode,BrhName from branch_master
Open TBName

FETCH NEXT FROM TBName into @BrhCode,@BrhName
WHILE @@FETCH_STATUS = 0
    BEGIN
        --print @BrhCode
        Insert into user_module_Detail values('Renewal Receipt', @BrhCode,'Transaction','A')
        FETCH NEXT FROM TBName into @BrhCode,@BrhName
    END

CLOSE TBName
DEALLOCATE TBName