sql server interview questions

Verifying updated values in SQL SERVER

Posted on Updated on

In most cases after table is updated we fire a select statement to check whether the value is updated correctly or not.

We can accomplish the same task by using OUTPUT clause in SQL server. Using Output clause we can verify the original value and updated values.

Here I’ll update column  MaritalStatus of employee table of AdventureWorks2008 database.

Before doing update we’ll check the values of MaritalStatus Employee table.

SELECT   BusinessEntityID
       , MaritalStatus

FROM  [HumanResources].[Employee]

WHERE BusinessEntityID IN ( 1,2,4)


BusinessEntityID MaritalStatus
1 S
2 S
4 S


In below query we are updating MaritalStatus from ‘S’ to ‘M’

UPDATE [HumanResources].[Employee]

SET MaritalStatus ='M'

OUTPUT  deleted.BusinessEntityID
       ,deleted.MaritalStatus AS BeforeUpdateMaritalStatus
       ,inserted.MaritalStatus AS AfterUpdateMaritalStatus
WHERE  MaritalStatus='S'
AND BusinessEntityID IN ( 1,2,4)


After executing the query we’ll get below  output which shows values before and after update.

BusinessEntityID BeforeUpdateMaritalStatus AfterUpdateMaritalStatus
1 S M
2 S M
4 S M

We again cross verify by running below query.

SELECT  BusinessEntityID
FROM  [HumanResources].[Employee]
WHERE BusinessEntityID IN ( 1,2,4)
BusinessEntityID MaritalStatus
1 M
2 M
4 M

So now we can use output clause directly instead of using select statements to verify updated values and original values.


How to get working days between two dates in SQL Server

Posted on Updated on

This post is about how to find the working day between Two Dates.

Consider the working days from Monday to Friday. We know that DATEDIFF function is used to calculate the Difference between two dates.

CREATE FUNCTION [dbo].fn_CountOfWorkingDays
( @StartDate DATETIME
  ,@EndDate  DATETIME
     DECLARE @CountOfWorkingDays INT
     SELECT @CountOfWorkingDays= ((DATEDIFF(dd, @StartDate, @EndDate) + 1) 
 -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
 -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
 -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END))
RETURN @CountOfWorkingDays


SELECT [dbo].[fn_CountOfWorkingDays] ( '2017-01-01','2017-01-31') CountOfWorkingDays 

In above function  DATEDIFF(dd,@StartDate,@EndDate ) + 1 gives us  the number of days between the two dates and  DATEDIFF(wk,@StartDate,@EndDate ) this gives the number of weeks between dates and * 2 gives us the weekend (Saturday and Sunday) count.CASE statement will excludes the day if it’s a Saturday or Sunday.

In case you want to calculate working days from Monday to Saturday then just remove last case statement and * 2 from  (DATEDIFF(wk, @StartDate, @EndDate) * 2)

ALTER FUNCTION [dbo].fn_CountOfWorkingDays
( @StartDate DATETIME, 
 @EndDate    DATETIME )
 DECLARE @CountOfWorkingDays INT
 SELECT @CountOfWorkingDays=
             ((DATEDIFF(dd, @StartDate, @EndDate) + 1)
 -(DATEDIFF(wk, @StartDate, @EndDate) )
 -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) )
RETURN @CountOfWorkingDays


SELECT [dbo].[fn_CountOfWorkingDays] ( '2017-02-01','2017-02-28') CountOfWorkingDays 


Whats the Difference Between Detaching And Taking Database offline?

Posted on Updated on

Once you detach your database there will not be an entry in the sysdatabases system table.
i.e you can query and confirm the same as

 Select * from master..sysdatabases

In the end result of the above query you cannot see the db you have detached whereas
if you bring the database offline, there will be an entry present in the sysdatabases system table;
You can verify the same by running the above query and you can see that the db you took OFFLINE will be present in the sysdatabases table !

Basically, detaching/attaching is used to move database files between instances(servers) where as in Offline you can not move files. After detaching, the database will be removed from SQL Server but will be intact within the data and transaction log files that compose the database. The purpose of these commands are totally different though it looks like same.