Sql Server Interview Questions and Answers (1758) - Page 32

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.

To Resolve the deadlock, the following way is used :

Transaction A :

RETRY: -- Label RETRY

BEGIN TRANSACTION

BEGIN TRY

	UPDATE Customer SET LastName = 'John' WHERE CustomerId=111

	WAITFOR DELAY '00:00:05'  -- Wait for 5 ms

	UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221

	COMMIT TRANSACTION

END TRY

BEGIN CATCH

	PRINT 'Rollback Transaction'

	ROLLBACK TRANSACTION

	IF ERROR_NUMBER() = 1205 -- Deadlock Error Number

	BEGIN

		WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms

		GOTO RETRY -- Go to Label RETRY

	END

END CATCH

Transaction B :

RETRY: -- Label RETRY

BEGIN TRANSACTION

BEGIN TRY

	UPDATE Orders SET ShippingId = 12 Where OrderId = 221

	WAITFOR DELAY '00:00:05' -- Wait for 5 ms

	UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111

	COMMIT TRANSACTION

END TRY

BEGIN CATCH

	PRINT 'Rollback Transaction'

	ROLLBACK TRANSACTION

	IF ERROR_NUMBER() = 1205 -- Deadlock Error Number

	BEGIN

		WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms

		GOTO RETRY -- Go to Label RETRY

	END

END CATCH

Here I have used Label RETRY at the beginning of both the transactions. The TRY/CATCH method is used to handle the exceptions in the transactions. If the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back, and if the exception is occurred due to deadlock, the transaction waits for 5 milliseconds. The delay is used here because the other transaction (which is not aborted) can complete its operation within delay duration and release the lock on the table which was required by the aborted transaction. You can increase the delay according to the size of your transactions. After the delay, the transaction starts executing from the beginning (RETRY: Label RETRY at the beginning of the transaction).

Now Execute the Transaction A and Transaction B at the same time. Both the transactions will execute successfully.

A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

Example:

This is explained clearly from the below example:

--1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT * FROM authors



--2

UPDATE authors 

SET au_lname = 'X'

WHERE au_id = '238-95-7766'



--3

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT * FROM authors



--4

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT * FROM authors



--5

EXEC sp_who2

You will see that 2 is blocked by 1, 3 is blocked by 2 and 4 is blocked by 3. Theoretically, 3 and 4 could execute (there's only a shared lock), but queuing makes 3 and 4 to wait. This is how livelocks are avoided.

It is a series of operations performed by SQL Server Agent sequentially .

It can do a wide range of activities, including running T-SQL scripts and ActiveX scripts, Integration Services packages, Analysis Services commands and queries, or Replication tasks.

Jobs can run repetitive or schedulable tasks, and they can automatically notify users of job status by generating alerts, thereby greatly simplifying SQL Server admin.

A job can be edited only by its owner or members of the sysadmin role

Database mail :
Based on SMTP (Simple Mail Transfer Protocol).
Introduced in Sql 2005.
No need to install Outlook.
Depend on Service Broker service.
More secure than Sqlmail.


SQLMail :
Based on MAPI (Messaging Application Programming Interface).
Used prior versions of Sql server 2005.
Require Outlook to be installed.
Leass secure than Database mail.

It is a new feature introduce in sql server 2012. It is a lightweight version of SQL Server that has many programmability features of a SQL Server database like stored procedures, user-defined functions and aggregates, .NET Framework integration,
spatial types and others that are not available in SQL Server Compact.

It runs in user mode and any database or T-SQL code can be moved from SQL Server Express LocalDB to SQL Server and SQL Azure without any upgrade steps.

--Create Table

CREATE TABLE EMPMaster(
[EMP_Name] [varchar](50) ,
Gender Char(1)
)

--Insert Record
Insert INTO EMPMaster Values('AA','M')
Insert INTO EMPMaster Values('BB','M')
Insert INTO EMPMaster Values('CC','F')
Insert INTO EMPMaster Values('DD','M')
Insert INTO EMPMaster Values('EE','F')
Insert INTO EMPMaster Values('FF','F')

--Update Query

Update EMPMaster SET Gender=Case When Gender='M' Then 'F'
Else 'M' END

The Subquery is one which Produces output based on the inner query values.

Whereas, Co-related subquery is one which the output based on the values of outer query.

E-g

SELECT e.EmployeeID

FROM HumanManangement.Employee e

WHERE e.ContactID IN

(

SELECT c.ContactID

FROM Employee_Person.Contact c

WHERE MONTH(c.ModifiedDate) = MONTH(e.ModifiedDate)

)

Suppose, we have some data in a table as shown under

Data
----------
Hello,
How
Are
You

We need to write a SQL Query to bring the following output

ConcatenateData
-----------------------
Hello,How Are You

Solution

DECLARE @t TABLE(Data Varchar(20))

INSERT @t SELECT 'Hello,' UNION ALL SELECT 'How' UNION ALL SELECT 'Are' UNION ALL SELECT 'You'



SELECT

		ConcatenateData

FROM

    (

		SELECT ' ' + CAST(Data AS varchar(8000))

		FROM  @t        

		FOR XML PATH ('')

    ) X(ConcatenateData)

Given a list of values and a position, the Choose function will return the value at the indicated position.

The syntax is

Choose ([Position], [Value1],[Value2],…,[ValueN])

e.g.

Select Choose (1,'Value1', 'Value2','Value3') As [Choose Demo]



/*

	Choose Demo

	------------

	Value1

*/

In this example, we have specified the position as 1 and hence out of the two values, the first appears as the result.

As the name suggests, it concatenates strings. In earlier versions of Sql Server we have the option of performing concatenation using the '+' symbol. But the overhead was that if the types that are participating in the concatenation are not of varchar type, then we had to do explicit conversion else it was resulting in error. However, the new Concat() function takes care of this explicit conversion.

e.g.

Select Concat('Sql',12, Null, 'Code Name', ' Denali') As [MultipleField Concat]



/* Output

MultipleField Concat

---------------------

Sql12Code Name Denali

*/

This function is use to format the value.
The syntax is as under

Format (expression, format [, culture]) 

e.g.

Select FormattedCurrency = FORMAT(50,'c','ru') ,Dateformat = FORMAT('07/14/2012','yyyy/mm/dd','fr')



/*

	FormattedCurrency	Dateformat

	-----------------	---------	

	50,00p.		2012/07/14	

*/

This function is use to find out the last day of the month.
e.g.

Select LastDayOfcurrentMonth = CONVERT(varchar(10),EOMonth(GETDATE()),110)

/*

	LastDayOfcurrentMonth	

	---------------------------------	

	07-31-2012

*/

- Eliminate the limit of maximum allowed column in SQL Server 2008 per table which is 1024.The maximum limit of SPARSE column is 100,000.So we can have 1024 + 100,000 columns.

- When there is a need to save 20-40% of space or a significant percentage of the rows to have a Zero or NULL value we can go for Sparse column.

- Sparse columns works well with filtered indexes because we create index for dealing with the non-empty attributes in the column.

- It was developed to be use in Content management systems like SharePoint which is one of the key drivers for File Stream project.

a) Microsoft recommends to use sparse columkn if there is a need to save space by at least 20 to 40%.

b) For Sparse column to act on a specific column, it must be nullable and cannot be configured with the ROWGUIDCOL or IDENTITY properties.

c) Sparse Columns cannot include Default value.

d) We cannot make a sparse column for TEXT, IMAGE, or TIMESTAMP datatypes.

e) I cannot be part of a clustered index.

f) It cannot be a unique primary key index.

A few key differences is listed here

a) View can be materialized (indexed view) and hence performs better. But Inline Table Valued functions cannot be indexed and performance decreases when number of rows increases.

b) Views can have triggers since they can be used to change underlying tables (INSTEAD OF triggers) but not Inline Table Valued functions.

c) We can use CROSS APPLY with the Inline Table Valued function but not with a view.

d) Views don't accept parameter but Inline Table Valued function does so.

DECLARE @date varchar(10) 

SET @date = '6/12/2012'  -- mm/dd/yyyy

SELECT EOM = DATEADD(month, ((YEAR(@date) - 1900) * 12) + MONTH(@date), -1)

We can use the DATENAME function for accomplishing the task.This function is used to
return a single string part of a date/time.

The general syntax is :

DATENAME ( datepart , date )

So if we specify the datepart as month number, we will get the month name component from this function.

Now, given any month number, we will first construct the first day of the month as under

CAST('10' + '/1/1900' AS DATETIME)

where 10 is month number

So, since we have now constructed the date, now we can easily apply the DATEPART function to obtain the month number

DECLARE @MonthNumber INT = 10



SELECT [Month Name] = DATENAME(MONTH,CAST('10'  + '/1/1900' AS DATETIME))





/* Result */

Month Name

----------

October

We can use the DATEPART function for accomplishing the task.This function is used to
return a single part of a date/time.

The general syntax is :

 DATEPART(datepart,date)

So if we specify the datepart as month, we will get the month component from this function.

e.g.

SELECT [MonthNumber] = DATEPART(mm,getdate())

will give the current month number since we are interested only in the month datepart (mm).

Now, given any month, we will first construct the first day of the month as under

Declare @monthname Varchar(20) = 'October'

Select CompleteDate = CAST(@monthname + ' 1, 1900' AS DATETIME)



/* Output */

CompleteDate

----------------------

1900-10-01 00:00:00.000

So, since we have now constructed the date, now we can easily apply the DATEPART function to obtain the month number

Declare @monthname Varchar(20) = 'October'

Select [MonthNumber] = DATEPART(mm,CAST(@monthname + ' 1, 1900' AS DATETIME))





/* Result */

[MonthNumber]

------------

10

Declare @t Table(StudentName Varchar(50),Marks int) 

Insert Into @t Values

('Name1',98),('Name2',78),('Name3',77),('Name4',67),('Name5',99),('Name6',99),

('Name7',93),('Name8',91),('Name9',69),('Name10',89)



Select

	StudentName

	,Marks

	,PercentileRank = NTILE(100) OVER (Order By Marks)  

From @t



/* Result */



StudentName	Marks	PercentileRank

Name4	67	1

Name9	69	2

Name3	77	3

Name2	78	4

Name10	89	5

Name8	91	6

Name7	93	7

Name1	98	8

Name5	99	9

Name6	99	10

The Foreign key value which exist in child table, without existing in primary key column in the parent table is known as an Orphan.

Example for finding an Orphan:

An orphan record exists when there is a Contact2 record but NOT a Contact1 record

SELECT * FROM Contact2 WHERE AccountNo NOT IN (SELECT AccountNo from Contact1)