Nov 8, 2015

SQL Server Interview Questions (Part-1)

Which TCP/IP port does SQL Server run on?
SQL Server by default runs on port 1433. However it can be changed from the Network Utility TCP/IP properties.

What are the authentication modes available in SQL Server?
  • Windows mode 
  • Mixed Mode - SQL and Windows. 

When UPDATE_STATISTICS command is used?
The indexes need to be updated when a large no. of DML operations like insertion, deletion and modifications are done.UPDATE_STATISTICS command performs this job.

What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.
Read more in detail...

Which command using Query Analyzer will give you the version of SQL server and operating system?
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').

Difference between char and varchar?
Char takes exact "n" bytes regardless of the data you store in it (with trailing spaces, if the data length is less than "n" where as Varchar takes "x" no. of bytes depending on the data + 2 bytes to store the length of the data.
Read more in detail...

What is the use of Set NOCOUNT ON?
By Default, When a command is executed it returns the number of affected record as result of the command. UsingSET NOCOUNT ON commands will no longer returns the number of affected records in the current scope.

What is an index?
An index is used to speed up the performance of queries. They are created for faster retrieval of data.

What are the difference between clustered and a non-clustered index?
A Clustered index is an index that sorts the data physically on the basis of columns used in clustered index and therefore only one clustered index is possible. The leaf nodes of a clustered index contain the data pages.
A Non Clustered index is an index in which the logical order of the index does not match the physical stored order of the rows. The leaf node of a non clustered index does not consist of the data pages, instead, the leaf nodes contain index rows.

What are Constraint?
SQL Server users constraints restricts only valid data to be inserted into column of a table.
There are following types of constraints.
Primary Key, Foreign Key, Unique, Default, Check, Not Null.

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

A FOREIGN KEY constraint allows to create relationship between tables. It ensures that data in FOREIGN KEY column should exists in the referring PRIMARY KEY column thus mainlining the referential integrity

What is UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the data in a set of columns, so no duplicate values are entered.

What is CHECK Constraint?
A CHECK constraint is used to restrict the set of values that can be entered in a column. The check constraints are used to enforce domain integrity.

What is NOT NULL Constraint?
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity.

WHAT is the difference between a Primary Key and a Unique Key?
Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where as unique creates a non-clustered index by default. Another major difference is that, primary key doesn't allow NULL, but unique key allows one NULL.

    Choose :
  • OR
  • To comment
No comments:
Write Comments