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

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

MSDB Database is used to perform which operations?

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

How to get list of database objects for finding specific tables used in stored procedure or functions.

Write below Query.Below query will return list of user defined tables,stored procedures and functions.

select distinct B.name 

from syscomments A 

inner join sys.objects B 

on A.id = B.object_id 

where A.text like '%your_table_name%';

What are the necessary parameters that is required when using RAISEERROR statement in Sql Server?

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

How many types of UDFs in Sql Server?

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

How to check a Local Temporary Tables are present on Database?

We have to write tempdb.. followed by Table name in object_id because Temporary Tables are stored in TempDB database.

if(object_id('tempdb..#temp_table_name') is not null)

print 'Table Exists';

else

print 'Not Exists';

What do we mean by Ident_Seed Sql Server Function?

Ident_Seed is an in-built Sql Server function wich returns the Seed value Which is defined as an Identity Column we specify at the time of table creation.
Syntax:-

Ident_Seed('table_name');

For example:-

Select Ident_Seed('Table_Name');

It will return 1 because we have set up an Identity seed value to start from 1.But it may change.

What is the return type of Ident_Seed function?

Ident_Seed function always returns numeric values.

For Example:-

Select Ident_Seed('Table_Name');

Then it will always return the numeric values.

which of the following column require to provide a value when you issuing an Insert statement?

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

You have a database that has a productdetails table containing more than one rows for each product. each product assigned an productId to identify product. you excute the following query against above table: select productid,sum(price)as total from productdetails group by productid order by total desc offset 0 rows fetch next 10 rows only

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

When a view definition contains a join

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

In how many ways we can get a table's row count?

1. Using COUNT(*)

Select count(*) from employee_master;

2. using COUNT(1)

select count(1) from employee_master;

3. Using SUM() aggregate function

select sum(1) from employee_master;

4. Using sysindexes view

select object_name(id), rows from sys.sysindexes where object_name(id) = 'employee_master' and indid<2

5. Using sp_spaceused system SP

exec sp_spaceused 'employee_master;'

What do we mean by Pages in Sql Server and How many types of pages are there?

A Page in Sql Server is an 8 KB data storage area .
There are 8 types of pages:
1. Data
2. Index
3.Text/Image
4. Global Allocation Map, Shared Global Allocation Map
5.Page Free Space
6. Index Allocation Map
7. Bulk Changed Map
8. Differential Changed Map

How can we know the Index usage on Tables?

By using below technique, we can know the Index plan on any Tables:-
Execution plan
SET STATISTICS PROFILE ON

What do we mean by Cursor functions in Sql Server?

Cursor functions as the name implies used to get information about any cursors we are using in Stored Procedures.
All Cursor Functions are nondeterministic i.e.
we get different values every time when they are called with a specific set of values.

How many types of Cursor Functions are available in Sql Server?

There are three types of Cursor Function in Sql Server, these are used when dealing with Cursor:-

@@CURSOR_ROWS
CURSOR_STATUS
@@FETCH_STATUS

What do we mean by Rowset functions in Sql Server?

Rowset functions returns an object that can be used as if it were a table or a view.
All Rowset functions are also nondeterministic i.e. they return different value every time they are called with a specific set of values.

What are the types of Rowset Functions?

Below are the Rowset Functions available in Sql Server:-
CONTAINSTABLE
FREETEXTTABLE
OPENDATASOURCE
OPENQUERY
OPENROWSET
OPENXML

How to fetch the next Identity value from Sql Server?

We have to write below query:-

SELECT IDENT_CURRENT('table_name') + 1

This is correct, but when table is empty and next identity value is set to "1" returned "2" but result is "1"
When the IDENT_CURRENT value is NULL because the table has never contained rows or has been truncated,the IDENT_CURRENT function returns the seed value.

How to FETCH the second Last record in a table without any identity column?

We have to write below query:-

declare @count int; 

select @count = count(*) from tableName;



select * from tableName 

except 

select top(@count - 2) * from tableName;

without using any identity column and ORDER BY clause we can get last 2 records from a table by using above code.

What is Modulo Operator in Sql Server?

Modulo Operator also known as Percent Sign(%) in Sql Server produces the Remainder of One Number Divided by Another Number.It's a simple airthmetic operation where 1 value is divided by another value and reminder will be the output.
Syntax:

dividend % divisor

Where,Dividend is the numeric expression to divide.It must be any Numeric data type in Sql Server.
Divisor must also be the numeric value which will be used to divide the dividend.