Check sql server version in Sql Server
Ans-select @@version
check how many tables in Sql Server
Ans-select * from sys.tables
Show complete structure of table in Sql Server
Ans-EXEC sp_help TableName;
How to show all temp tables in sql server
Ans-select * from tempdb.sys.objects
List all indexes in Sql Server
SELECT T.Name, I.name FROM sys.indexes I INNER JOIN Sys.tables t ON t.object_id =
I.Object_ID WHERE index_id >= 1 -- skip heep
List all indexes with all details in Sql Server
select * from sys.indexes
Get first and last date of month in Sql Server
DECLARE @Month int
DECLARE @Year int
set @Month = 2
set @Year = 2005
select DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) /*First*/
select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) /*Last*/
Display every Nth row in Sql Server
SELECT id FROM employees GROUP BY id HAVING MOD(id, N) = 0;
Age in years in Sql Server
DECLARE @dob datetime
SET @dob='1992-01-09 00:00:00'
SELECT DATEDIFF(hour,@dob,GETDATE())/8766.0 AS AgeYearsDecimal
,CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0)) AS AgeYearsIntRound
,DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc
Date diffrence in Sql Server
DATEDIFF(datepart,startdate,enddate)
Update Table using Join in Sql Server
UPDATE employee e LEFT JOIN employee_salary s ON e.id=s.employee_id
SET s.salary='50000'
where e.first_name='Admin'
.
DELETE Table using Join in Sql Server
Delete employee_salary FROM employee_salary LEFT JOIN employee ON employee.id=employee_salary.employee_id
WHERE employee.first_name='Admin'
Select first 3 characters of FIRST_NAME from EMPLOYEE
select substring(FIRST_NAME,0,3) from employee
Get position of 'L' in name 'Gulshan' from employee table
Select CHARINDEX('L',FIRST_NAME,0) from employee where first_name = 'Gulshan'
Replacing 'L' with '$'
select REPLACE(FIRST_NAME,'L','$') from employee
Get FIRST_NAME ,Joining year,Joining Month and Joining Date from employee table
select SUBSTRING (convert(varchar,joining_date,103),7,4) , SUBSTRING (convert(varchar,joining_date,100),1,3) , SUBSTRING (convert(varchar,joining_date,100),5,2) from EMPLOYEE
Get employee details from employee table whose joining year is “2013”
Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,103),7,4) = '2013'
Get employee details from employee table whose joining month is “January”
Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,100),1,3) = 'Jan'
Get employee details from employee table who joined before January 1st 2013
Select * from EMPLOYEE where joining_date < '01/01/2013'
Get employee details from employee table who joined between January 1st and January 31st
Select * from EMPLOYEE where joining_date between '01/01/2013' and '01/31/2013'
Get Joining Date and Time from employee table
Select convert(varchar(19),joining_date,121) from EMPLOYEE
Get Joining Date,Time including milliseconds from employee table
select convert(varchar,joining_date,121) from EMPLOYEE
Get difference between JOINING_DATE and INCENTIVE_DATE from employee and incentives table
Select FIRST_NAME,INCENTIVE_DATE - JOINING_DATE from employee a inner join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID
Get names of employees from employee table who has '%' in Last_Name.
Select FIRST_NAME from employee where Last_Name like '%[%]%'
Get Last Name from employee table after replacing special character with white space
Select REPLACE(LAST_NAME,'%',' ') from employee
Get department,no of employees in a department,total salary with respect to a department from employee table order by total salary descending
Select DEPARTMENT,count(FIRST_NAME),sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending
Select Last Name from employee table which contain only numbers
Select * from EMPLOYEE where lower(LAST_NAME) = upper(LAST_NAME)
Insert into employee table Last Name with ''' (Single Quote - Special Character, Use single quote before special character )
Insert into employee (LAST_NAME) values ('Test''')
Select Nth Highest salary from employee table
select min(SALARY) from (select top N * from employee) a
select min(SALARY) from (select top 2 * from employee) a
Select max incentive with respect to employee from employee and incentives table using sub query
select DEPARTMENT,(select ISNULL(max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID = EMPLOYEE_ID) Max_incentive from EMPLOYEE
Select first_name, incentive amount from employee and incentives table for all employees who got incentives using left join
Select FIRST_NAME, isnull(INCENTIVE_AMOUNT,0) from employee a right join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID
Select first_name, incentive amount from employee and incentives table for all employees even if they didn't get incentives and set incentive amount as 0 for those employees who didn't get incentives.
Select FIRST_NAME, ISNULL(INCENTIVE_AMOUNT,0) from employee a left join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID
