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

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 blocking in SQL Server?

Blocking in SQL Server is a scenario where one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go away.

Blocking is not the same thing as a deadlock. For more details on SQL Server blocking visit this nice article
http://www.sql-server-performance.com/tips/blocking_p1.aspx

What is the use of bit data type and what kind of data is stored into it?

Bit datatype is used to store boolean information like 1 or 0 where 1 is considered as true and 0 is considered as false.

While querying the database we need to use 1 or 0 in the select statement to filter out the data instead of true and false.

What is user-defined data type in SQL Server?

User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and nullability.

For more details, visit http://msdn.microsoft.com/en-us/library/aa933121(SQL.80).aspx

Thanks

What is user-defined functions in SQL Server?

User-Defined Functions allows to define custom T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.

In terms of functionality it is similar to C# or VB.NET functions except the fact that code is written in T-SQL :)

For more visit http://www.sqlteam.com/article/user-defined-functions

Thanks

What is the use of Keyword WITH ENCRYPTION keyword in SQL Server?

WITH ENCRYPTION keyword is used to encrypt the text of the Stored Procedure.

Once SPs are encrypted, the original text of the SP are not visible. User who has created the SP will need to save the text to be used to create SP somewhere safe to reuse it again or Users that have access to system tables or database files can retrieve the encrypted text.

What is Distributed Queries in SQL Server?

Distributed queries access data from multiple heterogeneous data sources. These data sources can be stored on either the same or different computers. Microsoft SQL Server supports distributed queries by using OLE DB.

SQL Server users can use distributed queries to access the following:

#. Distributed data stored in multiple instances of SQL Server.
#. Heterogeneous data stored in various relational and nonrelational data sources accessed by using an OLE DB provider.

For more details visit http://msdn.microsoft.com/en-us/library/ms188721.aspx

Thanks

What is Subquery in SQL Server?

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Subquery is an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.

For example

SELECT CustName, CustOrderDate, (SELECT DateTimeOrdered FROM OrderDetails as ODetails WHERE Ord.SalesOrderID = ODetails.SalesOrderID) AS OrderedDateTime FROM Orders AS Ord

What are the restrictions of using Subquery in SQL Server?

Subquery can be used where an expression is possible. There are certain limitation of using Subquery, they are

A subquery is subject to the following restrictions:

#. The select list of a subquery introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operate on SELECT * or a list, respectively).

#. If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list.

#. The ntext, text, and image data types cannot be used in the select list of subqueries.

#. Because they must return a single value, subqueries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.

#. The DISTINCT keyword cannot be used with subqueries that include GROUP BY.

#. The COMPUTE and INTO clauses cannot be specified.

#. ORDER BY can only be specified when TOP is also specified.

#. A view created by using a subquery cannot be updated.

How many database(s) can have in an instance of SQL Server 2005 ?

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

A fill factor of 0% and 100% means the same thing to SQL Server

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

Root Cause for Increasing the Transaction Log.

1.Uncommitted Transactions.
2.Extremely Large Transactions.
3.Operations : DBCC DBREINDEX / CREATE INDEX.
4.Client Applications do not process all results.
5.While Restoring from Transaction Log Backups.
6.Queries Time-Out before a transaction log completes the expansion and you receive false 'Log Full' error message.

Default security model in Microsoft SQL Server ?

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

Microsoft SQL Server's default protocol ?

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

What is Two-Phase Commit ?

Two-phased commits are the way to keep multiple servers synchronized.
(Replication also can achieve this)
Two-phased commits are considered to be “Tight Consistency.”
In SQL Server 6.0, We must use the API functions and write some fairly advanced code.
In SQL Server 6.5, The DTC (Distributed Transaction Coordinator) makes the two-phase commit easy to implement.

What is Database snapshot ?

A read-only, static view of a database at the moment of snapshot creation.

What is Deferred transaction ?

A transaction that is not committed when the roll forward phase of recovery finishes, and that cannot be rolled back during database startup because the data required by rollback is offline. This data can reside in either a page or a file.

What is Dirty pages ?

Buffer pages that contain modifications that have not been written to disk.

What is DRI ?

Declarative Referential Integrity :
The DRI actions enforced by FOREIGN KEY constraints can be supplemented with additional referential integrity logic defined in triggers on a table.

What is ETL ?

Extraction, Transformation, and Loading.
The complex process of copying and cleaning data from heterogeneous sources. Important part of development projects for data warehousing and business intelligence(BI).

What is Fragmentation ?

A difference between the physical and logical ordering of index data that can occur when data modifications are made. We can reduce fragmentation and improve read-ahead performance by dropping and re-creating a clustered index (or) DBCC DBREINDEX.