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

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 the datatype of REPLICATE function.

Return type of REPLICATE function is varchar data type.

How to Disable the Autocommit Mode in Sql Server?

As we know that,by default SQL Server Management Studio is in a Autocommit mode,which means whenever we run any transaction and execute then that transaction is committed by default.
If we want to disable Auto commit mode in SQL Server Management Studio or SSMS,then we have to follow below steps:-

1).Connect to SQL Server using SSMS
2).From the Menu bar, select Tools –> Options
3).Select Query Execution –> SQL Server –> ANSI
4).Make sure that you check the check box SET IMPLICIT_TRANSACTIONS
5).Click on OK
Now, open a new Query window and start executing the scripts.

How to escape single quote in SQL Server?

Suppose,we have a Test's value,which we have to insert on one column in a table,so if we write

INSERT INTO TEST1(NAME,ADDRESS) VALUES('TEST'S','ABC'D');

Then it will give error as
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'S'.

To overcome above problem,
We can escape the single quotes by doubling or inserting two single quotes as shown in the below query as

INSERT INTO TEST1(NAME,ADDRESS) VALUES('TEST''S','ABC''D');

Which statement is True about Triggers in Sql Server?

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

Which statement is False about Stored Procedures in Sql Server?

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

Which statement is False about User defined function in Sql Server?

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

Which statement is True about Stored Procedures in Sql Server?

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

Ho to Retrieving Table Row Count without using Count function?

Write below query:-

SELECT MAX(ROW) AS 'ROW COUNT' FROM 

(SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) Row FROM TEST1) A;

What is an alternative way of Retrieving Table Row Count without using Count function?

Write below query:-
Write below queries:-

select convert(bigint,rows)

from sysindexes

where id = object_id('Test1')

and indid < 2;



--OR-- 



select sum(row_count)

from sys.dm_db_partition_stats

WHERE object_id=OBJECT_ID('Test1')

AND (index_id = 0 or index_id = 1);

How to display Row count in a Select Query in Sql Server?

Write below query:-

select *,(select count(1) as row_count from test1) Row_Count from test1;

Which statements is correct about CTE (Common Type Expression)?

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

What do we mean by Alias in Sql Server?

Alias in Sql Server plays an important roles,as it can be used for showing display names and also used to create a temporary name for columns or tables.
Sometimes we need to assign column name to some different names to display as a header then we give aliases to any columns.It is used with both table as well as column names.
For Example:-

Select col1 as alias1,col2 as alias2 from Table_name;

Suppose,we have a First name,Middle name and Last name in the table,and we want to show only Full name as column name,then we can assign alias.

How many ways,we can create Aliases in Sql Server?

We can create Aliases in 2 ways:-
COLUMN ALIASES:- As the name implies, Column Aliases are created at column level,used so show column headings.
TABLE ALIASES:- As the name suggests, Table Aliases are created at Table level,used to shorten the long table names.Basically Table aliases are used for Self Join where we can differentiate same table name with different aliases.

For Example:-

Select First_Name as Name,Designation as Role From Employee_Master Emp;

Here,Name and Role are treated as Column Aliases and Emp will be treated as Table Alias.

Which statement is correct about Alias?

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

What do we mean by Set clause in Sql Server?

Set clause is used to set or assign any value.It's like assignning any values in Dot Net,Javascript or anything. It's same as setter properties or write only property in Dot Net.It is used in 2 levels:-
1). Variable level -> Declaring variables and then assign
2).Column level -> In Update statement

Give an Example of Set clause.

Variable Level:-

declare @date datetime;

set @date = getdate();

print @date;

Column level :-

Update table_name set col_name1 = 'val1',col_name2 = 'val2' where condition;

For Example:-

Update employee_master set modified_date = @date where employee_id = 1;

What do we mean by QUOTED_IDENTIFIER in Sql Server?

QUOTED_IDENTIFIER differentiates between single and double quotation marks when evaluating an expression.It has 2 options ON/OFF.By-default it OFF and when it is OFF,then single quotation mark is used and when set to ON,then double quotation mark is used.
Syntax:-