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

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.

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

How to check whether user defined function(UDF) is there in table or not?

Use below query to check for function:-

if exists(select name from sysobjects where name = 'your_function_name' and type = 'fn')

begin

  print 'exists';

end

else

begin

  print 'not exists';

end;

Alternative way of checking whether UDF exists or not in a Table?

By using count function we can check whether UDF exists or not.Refer below query:-

select count(*) 

from sysobjects 

where name = 'your_function_name' 

and type = 'fn';

Note:- If function exists in the table then count function will return 1 else 0.

Difference between IsNumeric and Bit in Sql Server?

Isnumeric is Sql Server Function whereas Bit is a DataType in sql Server.Both returns 0 or 1 value.But Bit datatype has additional value called Null.

For IsNumeric 1 means supplied value is integer value and 0 means value is not a numeric value.
For Bit 1 means True and 0 means False.

What will be the output of below Sql Statement? Select Convert(bit,1); Select Convert(bit,0);

Output would be 1 for First Sql Statement and 0 for Second Statement.Because we have converted 1 and 0 as Bit datatype using Convert function.So whatever datatype will be passed in convert function will be evaluated as supplied data type in Convert function.

Which operator is used with SQL wildcards?

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

Which of the following is not a part of Sql Server?

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

Where is the Group By clause placed in a Sql Select Statement?

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

Which of the following sign is used to connect a table name with a column name to create a fully qualified column name?

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

What does the ALTER TABLE clause do?

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

What will happen when we fire below query? select employee_id,first_name,middle_name,dob from employee_master order by 2,2;

It will throw an error saying that
Msg 169, Level 15, State 1, Line 1
A column has been specified more than once in the order by list. Columns in the order by list must be unique. It means we have to pass different column_id in order by clause.

What is the usage of AS keyword in Sql Server?

There are so many usage of AS keyword in Sql Server.

1). We use AS keyword as an alias shortcut name for a table when you have long queries or queries that have joins.

2). Another use is in assigning column names.

3). Creating SP,Views,CTE and Function and so on.

What will happen if we execute below query?Will below view be created or not? create view vw_emp_information as select * from employee_master order by employee_name;

No it will not execute and will give an error saying that:-

Msg 1033, Level 15, State 1, Procedure vw_test, Line 3
The order by clause is invalid in views,inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Means we can not write order by clause until and unless Top clause is specified in Select statement.

What will happen if we execute below query? Will below VIEW be created or not? create view vw_emp_information as Select TOP 50 * from employee_master order by employee_name;

Yes above VIEW will be successfully created because we have used Top keyword and order by clause inside View.
It is said that we can not use order by clause without top keyword inside View.

An we can access Above view as:-

Select * From vw_emp_information.

What is the use of CTE or Common Table Expression?

We can think a Common Table Expression Or CTE as a temporary table.A CTE is just like a derived table because CTE is not stored as an object and exists only for the duration of the query.In other words,a CTE is similar to VIEW also.

We can define or create a CTE as an expression,optional column list and a query.We have to specify WITH keyword and Semicolon(;) as prefix but Semicolon is optional.

Syntax:-

With CTE_As_Table(col1,col2....)

 AS

(Select Statement)

Here,CTE_As_Table will be treated as Table name as

Select * From CTE_As_Table;

Give an example of CTE?

Following is a very simple example of CTE as:-

With CTE_Employee_Master

As

(

  Select * From Employee_Master;

) 



;With CTE_Employee_Master(Emp_Id,Employe_Name,Pan_Number)

As

(

  Select Emp_Id,Employe_Name,Pan_Number From Employee_Master;

)

Can we write order by clause inside CTE(Common Table Expression)?

Yes,we can if Top keyword is specified in a Select Statement.Then we can use order by clause.As in case of View we can achieve the same thing if Top keyword is specified.

For Example:-

With CTE_Project_Info

As

(

   Select Top 10 * From Project_Master Order By Project_Name;

)

How to check if View is present in Database?

We can use the below Query to check whether particular view is present or not:-

if exists(select * 

from sys.views 

where object_id = object_id(n'[dbo].[vw_name]'))

   print 'view exists'

else

   print 'view does not exists'

What is an alternate way of checking View inside Database?

Write below query:-

select * from sys.views where object_id = object_id(n'[dbo].[vw_name]');

OR

select count(*) from sys.views where object_id = object_id(n'[dbo].[vw_test]')

What is the Difference between below queries? Select * from Employee_Master as Emp; Select * from Employee_Master Emp;

There is no difference between both statements above.
AS is just a more explicit way of mentioning an alias name.

Where we can use As keyword in Query or what's the main usage of As keyword?

Main uses of As keyword are:-

-Aliases for tables and columns

-Between Create and definition(procedure,view,function,trigger)

-Cast AS newtype

1
...
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
...
88

More SQL SERVER Exclusive Interview Questions & Answers here

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

GooglePlus

StumbleUpon

Delicious

Reddit

Email

Bookmark It

Interview Questions and Answers Categories

.NET Certifications

ASP.NET Web API

Data Structures & Algorithms

Design Pattern & Practices

DotNetFunda.Com

Entity Framework

Error and Solution

Function Points (FPA)

Interview Questions

Mobile Development

MSBI (SSIS, SSRS, SSAS)

News and Community

Product Reviews

Project Management

Regular Expressions

Web Services, Remoting