China Finance

Sep 21 2017

SQL Server Interview Questions #sql #server #inetrview, #sql #server #joins,interview #question #s #answer, #sql #server


#

The Full Outer Join logical operator returns each row satisfying the join predicate from the first (top) input joined with each row from the second (bottom) input. It also returns rows from:

The first input that had no matches in the second input.

The second input that had no matches in the first input.

The input that does not contain the matching values is returned as a null value.

What is the view in SQL Server. **
What are the updated views. ***
What are the materialized views or Indexed Views in SQL Server. ****
What is view with check option? ****
The with check option causes the where clause of the view to check the data being inserted or updated through the view in addition to the data being retrieved. In a sense, it makes the where clause a two-way restriction.
This option is useful when the view should limit inserts and updates with the same restrictions applied to the where clause.
Write the Create syntax for Views. **
What is common table expression (CTE)? ***
What is difference between CTE and View? ****

What is the cursor in SQL server? **
What are the different types of cursor in SQL Server? ***
When to use Cursor in SQL Server? ***

What is Index in SQL Server? ***
Explain different types of index in SQL Server? ***
What is filter index in SQL Server. ****
What is Covering Index? ****
What is index scan and index seek. ****
What is B+ tree index? ****
Explain the architecture of the index? ****
Explain the performance impact of index? ****
Can we create clustered index on null value column? ****
What are indexes in SQL Server? ****
How many clustered index can be created on the table? ****
How to enforce index on a query? ****

What are the different index configurations a table can have?
A table can have one of the following index configurations:
No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes

What is Database Tuning Advisor.
What is Execution Plan?
How to Read Execution Plan? What are the statistics and the impact on SQL Server?
What are the table hint and how we can provide it on table?
How to read execution plan?
What are the algorithms does SQL Server used to fetch data?
What is bookmark lookup?
How do you tell SQL Server to preserve the join order in the FROM clause during
optimization?
You can use OPTION (FORCE ORDER)

What is Transaction Server Implicit?

Implicit: when the transaction is in implicit mode, a new transaction starts automatically after the current transaction is committed or rolled back. Nothing needs to be done to define the start of the transaction. It generates continues chain of transactions.

Write a query to find the nth minimum and maximum.
Write a query to get the last record of the table.
Write a query to get the no rows of a table without using count or any other clause.
How many columns can be used with roll up or with cube command?
10 columns
How many columns can be selected in a select statement?
4096 columns can be selected.
How many columns can be selected in an insert statement?
4096 columns can be selected.
What level of nesting is possible for sub Queries?
32 level of nesting.
What level of nesting is possible for Triggers?
32 level of nesting.
How many non-clustered indexes can be created on a table?
Only one clustered index can be created
How many parameters can be provided to a stored procedure?
2100 parameters can be provided to a SP.
How many parameters can be provided to a UDF?
2100 parameters can be provided to a UDF.
How many columns can be taken in update statement?
4096 columns can be updated in an update statement
What is NO COUNT used for?
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. It is used with any SELECT, INSERT, UPDATE, DELETE statement. The setting of SET NOCOUNT is set at execute or run time and not at parse time

What is SET ANSI NULL ON used for?
What is arithmetic abort used for?
What is SET ROW COUNT used for?
What is SET ANSI padding?
What is SET ANSI WARNING on?
What is SET NO EXEC?
Which command using Query Analyzer will give you the version of SQL server and operating system?
How to delete duplicate records from table using query?
What are different types of Collation Sensitivity.

OLDER LINKS

SQL Server TSQL Interview Questions Part 1

SQL Server TSQL Interview Questions Part 2

SQL Server TSQL Interview Questions Part 3

SQL Server TSQL Interview Questions Part 4


Written by admin


Leave a Reply

Your email address will not be published. Required fields are marked *