How to get a set of record from middle of the table, Say you need records from 5 to 15 from a table containing 100 records in SQL Server

Its a small query that will help you to find the records from from  n to m from a table having N records.

Where N :- total records in the table.
            n :- starting record number.
            m :- ending record number.

select top 20 * from School
except
select top (5) * from School

so, here it first select top 20 records and from it it discards the 1st top 5 records.

Enjoy!

How to get all the Emails as Comma separatesd string in Sql Server.

SQL Server provides a function named Coalesce to do the task to join all the column value by comma separated string.

The Query is below:
Go
Declare @strEmail varchar(max)

SELECT @strEmail=coalesce(@strEmail+',','')+[Email]
       FROM Users      
print @strEmail  

max salary,second max salary etc from the Employee table in Ms-Sql-Server

Here there are diffrent methods are used to find the second Highest salary, max salary, min salary from the Employee table in MS-SQL Server.


use EmpDB;
select DISTINCT salary from Employee group by salary ;
// Show all the Distinct salaries


SELECT Min(Salary ) FROM [EmpDB].[dbo].[Employee]
WHERE Salary IN (SELECT DISTINCT TOP 2 Salary FROM [EmpDB].[dbo].[Employee] order BY Salary desc) //  for 2nd highest salary( i.e. Top 2 is used), you can change the number as per to get the desired one.


SELECT MAX(Salary)  FROM Employee WHERE Salary<(SELECT MAX(Salary) FROM Employee) // only for 2nd highest salary

select distinct a.salary from Employee a where 2=( select count(distinct b.salary) from Employee b where a.salary<=b.salary) //  for 2nd highest salary( i.e. where 2= is used), you can change the number(3,4,5..)   as per your need to get the desired one.

SELECT * FROM (SELECT salary, ROW_NUMBER() OVER (order by Employee.Salary DESC) AS RANK FROM Employee  group by salary) v where RANK = 8 ;


/*---Finding Max and min salary from Emp Table*/
SELECT  a.[firstName], a.Salary AS Salary
FROM Employee AS a
WHERE a.Salary IN (
SELECT MIN(b.Salary)
FROM Employee AS b)
UNION
SELECT  a.[firstName], a.Salary AS Salary
FROM Employee AS a
WHERE a.Salary IN (
SELECT MAX(b.Salary)
FROM Employee AS b)
ORDER BY a.Salary


/*Selecting all the 2nd max salary here 2 represents the 2nd higest salary
  Here u can able to sell all the records with second highest salary

  Table:

CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1,'Vamshi', 1000)
INSERT INTO #T1 VALUES (2, 'xxxxx', 2000)
INSERT INTO #T1 VALUES (3, 'yyyyy', 3000)
INSERT INTO #T1 VALUES (4, 'zzzzz', 4000)
INSERT INTO #T1 VALUES (5, 'sssss', 5000)
INSERT INTO #T1 VALUES (6, 'ccccc', 6000)
INSERT INTO #T1 VALUES (7, 'ppppp', 2000)
INSERT INTO #T1 VALUES (8, 'aaaaa', 4000)
INSERT INTO #T1 VALUES (9, 'bbbbb', 5000)
INSERT INTO #T1 VALUES (10,'eeeee', 5000)


Select * from #t1 order by salary;
SELECT a.ID1, a.[Name], a.Salary
FROM #T1 AS a
WHERE (2) = (
SELECT COUNT(DISTINCT(b.Salary))
FROM #T1 AS b
WHERE b.Salary > a.Salary)

*/
/* Displaying all the records with max and min salary
select distinct * from Employee where salary in (select max(salary) from Employee
union
select min(salary) from Employee
);*/

/* finding all the 2nd higest Salary using Rank here 2 refers to the 2nd highest salary , here a means alias
Select *
from (Select *, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Ranks from Employee) a
WHERE Ranks = 2 */


/*
first method:
select top 1 salary from (
select top 6 salary from employee group by salary order by salary desc) a order by salary
go

2nd method:
with nthsalary as
(
select salary,row_number() over(order by salary desc) as row from employee group by salary
)
select salary from nthsalary where row=6
GO
3RD method:
select distinct * from employee a where 6=(select count(distinct salary)
from employee b where a.salary<=b.salary)

*/

/*
create table EmpDept(
empno int identity(1,1),
empname varchar(50),
sal numeric(18,2),
dep varchar(20)
)
go
insert into EmpDept(empname, sal, dep)
select 'A_123',12000,'BANK'
Union
select 'A_234',5000,'BANK'
Union
select 'A_345',10000,'BANK'
Union
select 'A_456',25000,'BANK'
Union
select 'A_567',8000,'BANK'
Union
select 'B_123',25000,'PROG'
Union
select 'B_234',27000,'PROG'
Union
select 'B_345',23000,'PROG'
Union
select 'B_456',13000,'PROG'
Union
select 'B_567',50000,'PROG'
Union
select 'C_123',11000,'TEST'
Union
select 'C_234',9000,'TEST'
Union
select 'C_345',22000,'TEST'
Union
select 'C_456',30000,'TEST'
Union
select 'C_567',8000,'TEST'

Select * from EmpDept
Select * from EmpDept a where empno in (select top 3 empno from EmpDept where dep=a.dep order by sal desc)*/

How to get all the Table Names and the Rowcount for each Table of a specified DataBase

Some times it is necessary to get all the table names and its row count of each table of a particular Database in SQL server 2008/2008 R2

 SELECT  [TableName] = so.name,  [RowCount] = MAX(si.rows)  FROM sysobjects AS so,
sysindexes AS si WHERE  so.xtype = 'U'  AND  si.id = OBJECT_ID(so.name)  GROUP BY
so.name ORDER BY  1 ASC

How to add a SQL Authenticated Login User in MS-Sql Server

In order to create a login user in sql server follow the following steps:-

step-1: Open MS-SQL Server  and click Security and then click Logins.

 

Step-2: Now right click on the Logins and click "New Login..."

 

Step-3: You will see a window below as :

 

Step-4: Now give the name for the login user & choose SQL Server Authentication and this will make the password fields enable, give the password and confirm password uncheck the Enforce password policy and click OK that all..

 Enjoy it..........