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

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

How to find SQL SERVER SCRIPTING DUPLICATES ?

/* Create Table with 7 entries - 3 are duplicate entries */
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
GO

/* It should give you 7 rows */
SELECT *
FROM DuplicateRcordTable
GO

/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

/* It should give you Distinct 4 records */
SELECT *
FROM DuplicateRcordTable
GO

what is Schemabinding View.

Schemabinding View Restirct you to made any change in tables you used in your View.
Example:

Suppose I have an table Employee(EmpID,EmpName, DOJ,Managerid,DepartID)

Now am Creating a view

Create View EmployeeDetails
with Schemabinding
as
Select EmPid, EmpName,DOj,ManagerId,DepartID from Employee

after it just try to execute delete table and alter table and delete column of employee table.

Sql server will not allow to change table schema. Because you are having Schema dependency.First you need to delete View then only database will allow to modify table.

If you are using Normal View. Sytem will Allow you to delete or modify table but when you run your View next time it will display error.

How many Foreign key can i have in my MS sql table ?

A Maximum of 253 Foreign Keys we can have in for a single table.

How many tables can be used in a single SELECT statement ? Have you tested that ?

It depends on Version

SQL Server 2005:
Maximum tables can be 256

SQL Server 2008:
Depends on resource availability

SQL Server 2008 R2:
Depends on resource availability

You can confirm by using script given below

/*Creating 300 Tables for testing*/

Use [Master]

Go

Declare @I Int, @Script Varchar(500)

Select @I = 1



While (@I <=300)

Begin

	Select @Script = 'Create Table Table' + CAST(@I as varchar) + '(Id Int)'

	Exec(@Script)

	Select @I = @I + 1

End	

Go

/*Using all the tables in SELECT statement*/

Use [Master]

Go

Declare @I Int, @Script Varchar(Max)

Select @I = 1

Select @Script = 'Select A1.* From '



While (@I <=299)

Begin

	if (@I >1)

		Select @Script =  @Script + ' Join Table' + CAST(@I+1 as varchar) + ' A' + CAST(@I+1 as varchar) + ' On (' + 'A' + CAST(@I+1 as varchar) + '.Id=' +  ' A' + CAST(@I as varchar) + '.Id)'

	else

		Select @Script =  @Script + 'Table' + CAST(@I as varchar) + ' A' + CAST(@I as varchar) + ' Join Table' + CAST(@I +1 as varchar) + ' A' + CAST(@I +1 as varchar) + ' On (' + 'A' + CAST(@I as varchar) + '.Id=' +  ' A' + CAST(@I+1 as varchar) + '.Id)'

		

	Select @I = @I + 1

End	



EXEC(@Script)

Go

The script will confirm the limitation of using tables in a SELECT statement.

In MSSQL What is ISNULL() function? how do we use it?

ISNULL() function is used to check the value is null or not in SQL Server. This
function also provides a way to replace a value with the null if the result is true.

Here @Param is a nullable parameter and isnull checks the parameter is a null value
or not. If it is a null, it replaces it with '' string.

ISNULL(@Param, '')

For execution of DML statement in a view, view need to contain ?

NOTE: This is objective type question, Please click question title for correct answer.

sql statement which returns all columns of table with no row(assuming that table has more than 3000 row).

NOTE: This is objective type question, Please click question title for correct answer.

What does this return ? declare @adress varchar = 'India' select @adress

NOTE: This is objective type question, Please click question title for correct answer.

What are Deterministic and Non deterministic Functions?

Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Non deterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.

Deterministic functions - SUM, AVG, DAY, ISNUMERIC, ISNULL, CONVERT
Non deterministic functions - GETDATE, RAND, @@ROWCOUNT. USER_NAME, IDENTITY

Can you set firing order in triggers and in instead of triggers?

sp_settriggerorder [triggername] first / last

example :

USE AdventureWorks2008R2;

GO

sp_settriggerorder @triggername= 'Sales.uSalesOrderHeader', @order='First', @stmttype = 'UPDATE';

For instead of trigger we cannot be set the order.

Can we alter the flow of the execution in SQL Server ?

Yes. We can alter the flow of the execution of the statements using "GOTO" and "Lable name"

Using GOTO statement, we can skip the flow of the execution pointing to Label name.

Declare @Input Int, @Status Varchar(5), @Result Varchar(100)

Select @Input = 10

Select @Result = 'The Input(' + Cast(@Input as varchar(5)) + ') is ' 

If ((@Input%2) = 0)

	Goto Even

Else

	Goto Odd

Even:

	Select @Status = '"Even"'

	Goto Result

Odd:

	Select @Status = '"Odd"'

	Goto Result	

Select @Status = '.....Test.....'

Result:

Select @Result = @Result + @Status

Select @Result

Goto Finish

Finish:

Reverse string with out using in build function

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(MAX))

INSERT INTO @t(data) SELECT 'Jacob'

INSERT INTO @t(data) SELECT 'Sebastian'



;WITH cteReverseRecur as (

    Select ID

     , RIGHT( data, 1 ) as RevStr

     , LEFT( data, LEN([data])-1 ) as RemStr

     From @t

  UNION ALL

    Select ID

     , RevStr + RIGHT( RemStr, 1 )

     , Left( RemStr, LEN(RemStr)-1 )

     From cteReverseRecur

     Where RemStr > '')

SELECT ID, RevStr as data

 From cteReverseRecur

 Where RemStr = '';

What is the purpose of "sp_resetstatus" system stored procedure ?

This system stored procedure is used to reset the database status from SUSPECT to normal.

The following are the considerations :
1. You should be under 'sysadmin' server role.
2. Should not be under Transaction. It will throw an err as follows
i.e: The procedure 'sp_resetstatus' cannot be executed within a transaction.
3. Database name should be valid and available
i.e: The database '<DatabaseName>' does not exist. Supply a valid database name. To see available databases, use sys.databases
4. The database should not be a snapshot. It should be a source database.
i.e: Cannot run sp_resetstatus against a database snapshot.
5. The database should be already in SUSPECT mode.
i.e: The suspect flag on the database "<DatabaseName>" is already reset.

What are all the ways to connecting Locally on the Server ?

Important: The Server and Client machine are the same machine

If the Server Name : SQLFunda
If the Instance Name : SQLBuddy

When we try to connect with the SQL Server from the Client, we can use either one of the ways...

1. Server Name\Instance Name
i.e: SQLFunda\SQLBuddy

(or)

2. (Local)\Instance Name
i.e: (Local)\SQLBuddy

(or)

3. Localhost\Instance Name
i.e: Localhost\SQLBuddy

(or)

4. .\Instance Name
i.e: .\SQLBuddy

How to force the protocol(NamedPipe or TCP/IP) connection when connect locally on Server ?

Normally, when we try to connect the Server from locally, It will connect with Shared Memory protocol.

If the Server Name : SQLFunda
If the Instance Name : SQLBuddy

But, we can force the connection either Named Pipe or TCP/IP

Forcing TCP/IP:
i.e: tcp:SQLFunda\SQLBuddy

Forcing NamedPipe:
i.e: np:SQLFunda\SQLBuddy

Once connected. we can verify that what kind of connection made ?

select s.session_id [Session ID], 

	e.Name [Protocol Used]

from sys.dm_exec_sessions s join sys.endpoints e

on (s.endpoint_id = e.endpoint_id)

In SQL Server, between Windows Authentication and SQL Server Authentication, which one is trusted and which one is untrusted?

Windows Authentication is trusted because the user name and password are checked with the Active Directory, the SQL Server authentication is untrusted , since SQL Server is the only verifier participating in the transaction.

Thanks and Regards
Akiii

Once we switched from SIMPLE Recovery model to FULL or BULK_LOGGED recovery model, what are all the recommendations ?

1. After switched from SIMPLE recovery model to FULL or BULK_LOGGED recovery model, We should take the Data backup either FULL or DIFFERENTIAL backup, then only the LSN resets and Log chain starts..

2. Transaction Log backup should be scheduled, then only the committed transactions will be removed from the transaction log otherwise the Log grows and grows....... till reaches the physical free space.

Difference between Composite , Candidate and alternate keys ?

Composite Key :
A composite key is a combination of more than one column to identify a unique row in a table.
Exp : EmpID, EmailID, SSN in Employee table and project ID in project table .
if EmpID and project id are put in projectionHours table then combination of empid and project ID called as composite key because combination of these two act as primary key in projectionHours table.

Candidate Key:
All keys in a table that become unique called as candidate key.
Exp : EmpID , EmailID and SSN all will be always unique for any employee in that case all these three columns called as candidate keys.

Alternate Key:
Among of candidate keys if any single key or combination of keys made as primary key then rest candidate key called as alternate key.
Exp : Suppose in employee table EmpID is primary key then Emailid and SSN are called as alternate key mean later on these key can be act as primary key without affecting existing data in table.

Delete duplicate rows on table with/without primary key in Sql Server 2005

Here below query that have multiple repeated records, my aim to delete duplicate records and keep one record for each unique row.

select * from employee



insert into employee(empid,empname,salary) 

select 1,'A',20000 union all

select 1,'A',20000 union all

select 1,'A',20000 union all

select 1,'A',20000 union all

select 2,'B',20000 union all

select 1,'C',20000 union all

select 2,'B',40000 



-- delete duplicate records based on name 

set rowcount 1

delete from employee  where ( select count(*) from employee ee where employee.Empname = ee.Empname) > 1



while (@@rowcount > 0)

begin

    delete from employee  where ( select count(*) from employee ee where employee.Empname = ee.Empname) > 1

end 



set rowcount 0

Output :
1 A 20000
1 C 20000
2 B 40000

Which character is said to be WildCard Character in SQL ?

Let assume we want a query which returns all the employee names starting with Ra then in SQL we generally use

 Select FirstName from Employee where FirstName LIKE 'Ra%'

here that % is said to be the Wild card character.

More SQL SERVER Exclusive Interview Questions & Answers here

Found this useful, bookmark this page to the blog or social networking websites.

Bookmark It

Interview Questions and Answers Categories

Data Structures & Algorithms

Design Pattern & Practices

DotNetFunda.Com

Entity Framework

Error and Solution

Function Points (FPA)

MSBI (SSIS, SSRS, SSAS)

Web Services, Remoting