Interview Questions → SQL SERVER → SQL Server Questionnaire I

SQL Server Questionnaire I

Can truncate be rollback in SQL Server?

Delete can be roll back because when the rows are deleted from the table, the row data is stored in the log file so that the data can be recovered during roll back.

But in Truncate, data is removed by deallocating data pages(data pages are used to store table data) and records only deallocations in transaction log.

Truncate can be rolled back if surrounded by Transaction

BEGIN TRAN
TRUNCATE TABLE Test_Match    
-- statement will return empty Test_Match table 
SELECT * FROM Test_Match    
ROLLBACK
--statement will return Test_Match table with original data
SELECT * FROM Test_Match

What is the difference between Primary key and Unique Key?

      • Column with Unique key can accept null value, but column with primary key doesn't accept null
      • There can only be one primary key on a single table, but there can be more than one unique key on the table.
      • If you go with the default options then primary key is created as a clustered index while Unique constraint is created as non clustered index

What are correlated queries?

A correlated subquery is an inner subquery which is referenced by the main outer query such that the inner query is considered as being executed repeatedly.

Let us consider an example

--    declaring 2 temp table #Temp and #TempAddress
Create Table #Temp
(
    PK_ID int identity(1,1),
    sName Varchar(50)
)

Create Table #TempAddress
(
    PK_ID int identity(1,1),
    PersonID int,
    Address Varchar(100)
)

-- Inserting the value in both the tables
Insert Into #Temp Values('Karan')
Insert Into #Temp Values('Gupta')
Insert Into #Temp Values('Karan')
Insert Into #Temp Values('Karan')

Values in table #Temp

PK_ID	sName
1	Karan
2	Gupta
3	Karan
4	Karan

Insert Into #TempAddress Values(1,'Address1')
Insert Into #TempAddress Values(2,'Address2')
Insert Into #TempAddress Values(3,'Address3')

Values in #TempAddress

PK_ID	PersonID	Address
1	1	Address1
2	2	Address2
3	3	Address3

Here is the correlated query

select Address from #TempAddress a where a.PersonID in (select PK_ID from #Temp b where b.PK_ID = a.PersonID)

As we can see inner query is referenced by the outer query

Output of the query is

Address
Address1
Address2
Address3

Finally Temp tables are dropped

Drop Table #Temp
Drop Table #TempAddress

What is a deadlock?Can you give an example of creating a deadlock?How do you detect deadlocks?

A deadlock occurs when two processes are waiting for a resource and neither process can advance because the other process is preventing it from getting the resource.

Example to create a deadlock

      • Begin transaction

      • Add update query

      • Don’t add commit or rollback statement.

Begin Transaction
update tblMessageCode Set MessageCode = 'tst'

Detect deadlock

      • Open a new instance of query analyzer

      • Write following query "select * from tblMessageCode".

You will see that this query kept on executing.

To avoid the dead lock. Add commit or rollback in the transaction.

What is the difference between varchar and nvarchar?

nvarchar →

      • Where "n" represents unicode. nvarchar can handle unicode characters. Unicode allows you to use multiple languages in databases.

      • nvarchar takes 2 bytes of space to store data

varchar →

      • varchar is good when you are using a single language

      • varchar takes 1 byte of space to store data.

What is the difference between varchar and char?

      • char is the fixed length datatype and varchar is variable length character data type

      • The storage size of char value is equal to the length of the column and the size of varchar value is equal to the length of data entered.

What are Temporary Stored procedure?

Temporary stored procedure starts are analogous to temporary tables.

Temporary stored procedure does not persists when SQL server shuts down.There are 2 ways of defining temporary stored procedure

Create Proc #test
as
select * from <table_Name>

And

Create Proc ##test
as
select * from <table_Name>

Procedure created by # is called local temporary stored procedure. This procedure is visible only for the current connection.

Procedure created by ## is called global temporary stored procedure. This procedure is visible to all the connections.

What is indexed/Materialized View?

An indexed view is a view that has a unique clustered index created on it

Implementation →

Create a table tbl_Test

PK_ID	sName	sAddress
1	Name1	Address1
2	Name2	Address2

Steps →

• Create a view

Create View v_tbl_Test
with schemabinding
as 
select PK_ID, sName, sAddress
from dbo.tbl_Test

• Create Index on View

Create Unique clustered index id_v_tbl_Test
on v_tbl_Test(PK_ID)

What are four isolation levels in sql server?

A → Atomicity
C → Consistency
I → Isolation
D → Durability

How do you suppress rows affected messages when executing an insert statement?

set nocount on

Posted By -

Karan Gupta

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**