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

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

What is Coalesce?

Coalesce is a function tat returns first non-null value within the given list of values.

Example:

select param1, param2, Coalesce(param1*param2) from tableName;

It will receive only non-null value from param1 and param2

Is it possible to write more than 2 sql statements in a stored procedure like select * from tablename select * from tablename select * from Tablename

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

What is the difference between primary key and unique key?

Difference between Primary Key and Unique Key

1) Primary key creates a clustered index on column whereas unique key creates a non-clustered index on column.

2) NULL is not allow in case of primary key but in unique key one null is allowed.

Unique key can have two null values?

No, unique key can have only one null value because unique key enforces uniqueness of the column. So column can not have more than one NULL value.

What is check constraint?

It is used to enforce domain integrity. Check constraints are used to limit the values of a column.

Example:

CREATE ABLE tblTest

(

ID INT NOT NULL,

NAME VARCHAR(20),

CITY VARCHAR(20),

CONSTRAINT chk_Test CHECK (ID>0 AND CITY='DEHI')

)

How to drop a constraint?

using following line of code:

ALTER TABLE <tablename>

DROP CONSTRAINT <constrain name>



Example: There is a table named tblTest and having a constraint "test_Constraint'



ALTER TABLE tblTest 

DROP CONSTRAINT test_Constraint

How to create a local temporary able?

Using # with table name, we can create a local temporary table.

CREATE TABLE #tempTable

( 

    id INT, 

    name VARCHAR(50) 

)

How can we create a global temporary table?

Using ## with table name, we can create a global temporary table.

CREATE TABLE ##globalTempTable 

( 

    id INT, 

    name VARCHAR(50) 

)

What is limiation of #TEMP table?

You can give table name upto 116 characters only including # sign.

Create table #<more than 116 characters table name>

(

 ID int,

NAME varchar(50)

)

You will get the following error.

Error: "The object or column name starting with '#<long string here>' is too long. The maximum length is 116 characters."

How a procedure can be encrypted?

A procedure can be encrypted using WITH ENCRYPTION

CREATE PROCEDURE SP_TESTPROC

WITH ENCRYPTION

AS

BEGIN

 SELECT * FROM TABLE_NAME

END

Use of encrypting a stored procedure is to secure your procedure code, if you want to deploy your procedure at client server, encrypt it so that no one can read your procedure code, it is just similar as wrapped a procedure in oracle.

It is not advisable to encrypt a stored procedure because there is no way to decrypt your procedure, it is one way call only.

If your requirement to encrypt a procedure then keep your source code copy of procedure at some location for further use.

How Many Parameters per stored procedure in Sql Server?

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

What is the full form of SQL?

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

What is sp_config command?

sp_config is a system stored procedure which is used to displays or changes global configuration settings for the current server.

Syntax:

sp_configure [ [ @configname = ] 'option_name' 

    [ , [ @configvalue = ] 'value' ] ]

Example:

--change configuration option 0 to 1

EXEC sp_configure 'show advanced option', '1';

How to get record of nth row of a table ?

Problem : I have an employee table , and id is the primary key ,then how to find out third employee record

For the above problem I have found the below solutions :

How to get
CREATE TABLE [dbo].[Employee](
[Id] [int] NOT NULL PRIMARY KEY,
[Name] [varchar](50) NULL,
[Age] [int] NOT NULL,
[Photo] [image] NULL,
[Salary] [numeric](10, 2) NULL,
)
INSERT INTO Employee values (101,'James Clerk',29,NULL,1000.00);
INSERT INTO Employee values (102,'Steve Proell',40,NULL,60000.00);
INSERT INTO Employee values (103,'Matt Mcnair',35,NULL,5000.00);
INSERT INTO Employee values (104,'Amit Kr',29,NULL,1000.00);
INSERT INTO Employee values (105,'Jeff Yeary',32,NULL,1000.00);

# Example 1 : Using Max()

select  * from employee where id in (

select  MAX(Id)  from Employee 

where Id  in (select top(3) ID from Employee ))

# Example 2 : Using Top() ,asc , desc

select top 1 *

from employee

where Id in (select top 4 Id from employee order by Id asc)

order by Id desc

#Example 3 : Using ROW_NUMBER()

SELECT * FROM

(SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum, * FROM Employee) sub

WHERE RowNum =4



Please suggest if any more solution is there for this.

How to get the nth row value of a table.

Problem : I have a employee table then how to find out nth employee detail with out using the actual id.

For the above question I have found the below solution.

The below solution I have wrote the query for , getting the record of third employee.

CREATE TABLE [dbo].[Employee](
[Id] [int] NOT NULL PRIMARY KEY,
[Name] [varchar](50) NULL,
[Age] [int] NOT NULL,
[Photo] [image] NULL,
[Salary] [numeric](10, 2) NULL,
)

INSERT INTO Employee values (101,'James Clerk',29,NULL,1000.00);
INSERT INTO Employee values (102,'Steve Proell',40,NULL,60000.00);
INSERT INTO Employee values (103,'Matt Mcnair',35,NULL,5000.00);
INSERT INTO Employee values (104,'Amit Kr',29,NULL,1000.00);
INSERT INTO Employee values (105,'Jeff Yeary',32,NULL,1000.00);

# Solution 1

select  * from employee where id in (

select  MAX(Id)  from Employee 

where Id  in (select top(3) ID from Employee ))

# Solution 2

select top 1 *

from employee

where Id in (select top 3 Id from employee order by Id asc)

order by Id desc

# Solution 3

SELECT * FROM

(SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum, * FROM Employee) sub

WHERE RowNum = 3

Please suggest if any other solution are there for the above problem

Is there a way to decrypt stored procedure?

No, there is no way to decrypt a stored procedure, once you have encrypted your stored procedure, you can not get your code.

So better to create your procedure without encryption and save script file in some location then alter procedure with encryption.

What is Collation ?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana character types and character width.

What is Identity?

Identity is column that automatically generates numeric values, it is increamented by 1 by default but it can be set also.

Example:

Create table tabName

(

ID INT IDENTITY(1,1) NOT NULL,

NAME VARCHAR(20) NULL

)

What is the difference between SQL and SQL Server ?

SQLServer is an RDBMS just like oracle,DB2 from Microsoft.

Structured Query Language (SQL), pronounced "sequel", is a language that provides an interface to relational database systems.SQL is used to perform various operations on RDBMS.

What is diffrence between Co-related sub query and nested sub query ?

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Example:

select e1.empname, e1.basicsal, e1.deptno from emp e1 

where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

Example:

select empname, basicsal, deptno from emp 

where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

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