SQL Server Interview Questions

A list of top frequently asked SQL Server interview questions and answers are given below.

1) What is SQL Server?

SQL Server is DBMS system provided by Microsoft. Sometimes it is mistakenly referred as SQL but both are totally different, as SQL is a language whereas SQL Server is a Microsoft product that supports SQL.

2) What is normalization?

In RDBMS, the process of organizing data to minimize redundancy is called normalization. In normalization database is divided in two or more tables and a relationship is defined among the tables.

3) What is De-Normalization?

It is a process of attempting to optimize the performance of a database by adding redundant data. Actually redundancy is introduced intentionally in a table to improve performance and it is called de-normalization.

4) What is the difference between function and stored procedure?

Function must return a value while stored procedure can return zero or n value.

Functions can have only input parameter while procedures can have input/ output parameters.

Functions take one mandatory input parameter while stored procedures may take 0 to n input parameter.

Exceptions can be handled by try-catch block in stored procedure while you can't use try-catch in functions.

5) What is collation sensitivity? Explain different types.

Collation sensitivity is used to define the rules for sorting and comparing the strings of character data.

Different types of collation sensitivity:

• Case sensitivity
• Accent sensitivity
• Kana sensitivity
• Width sensitivity

6) What is standby server?

Standby server is the type of server which is brought online when primary server goes offline and the application needs continuous availability of the server.

There are three types of standby servers:

Hot standby:Hot standby method is a method of redundancy in which the primary and secondary backup systems run simultaneously so the data also present in secondary server in a real time and this way both system contains identical information.

Warm standby:Warm standby is a method of redundancy in which the secondary system runs in the background of the primary system . data is mirrored in the secondary server at regular interval, so in this method sometimes both servers don't contain the exact same data.

Cold standby:Cold standby is the method of redundancy in which the secondary server is only called when the primary server fails. Cold standby systems are used in cases where data is changed infrequently or for nor critical applications.

7) In which TCP/IP port does SQL Server run? Can it be changed?

SQL Server runs on port 1433. Yes, it can be changed from the network utility TCP/IP properties.

8) What are the authentication modes in SQL Server? How can it be changed?

SQL Server supports two authentication modes:

Window authentication mode and mixed mode.

Window authentication mode: This authentication mode is used to connect through a Microsoft NT 4.0 or window 2000 user account.

Mixed mode: It is used to connect with the instance of SQL Server using window authentication or SQL Server authentication.

9) What is SQL Server Profiler?

Microsoft SQL Server profiler is a graphical user interface that allows system administrators to monitor events of database engine.

You can do the following things with a SQL Server Profiler -

You can create a trace.

You can watch the trace results when the trace runs.

You can store the trace results in a table.

If it is necessary, you can start, stop, pause and modify the trace results.

10) What is SQL Server agent?

SQL Server agent is a component of Microsoft SQL Server. It is a background tool of Microsoft SQL Server so it runs continuously in the background as a window service. SQL Server agent allows the database administrator to handles automated tasks and schedules jobs. It runs a window service so can start automatically when the system boots or you can start it manually.

11) What is scheduled jobs or scheduled tasks?

Scheduled tasks let you manage the tasks in an automated manner that runs on regular or predictable cycles. You can schedule administrative tasks and also determine the order in which tasks will run.

12) What is DBCC command and why is it used?

DBCC stands for database consistency checker. This command is used to check the consistency of the database. For example:

It makes sure that table in the database and the indexes are correctly linked.

It checks all pages in the database and make sure that all are correctly allocated.

It checks all table file group for any damage.

13) What command is used to rename the database?

sp_renamedb 'oldname', 'newname';

14) Can SQL Server be linked with other Servers like Oracle?

Yes, it can be linked to any Sever. It has OLE-DB provider from Microsoft which allow linking.

15) What is the difference between abstract and interface?

Abstract class:

It provides a set of rules to implement next class. Rules are provided through abstract methods.

Abstract method does not contain any definition.

When a class contains all functions without body, it is called as Fully Abstract Class.

Interface:

If a class contains all abstract methods then that class is called Interface. Interface support like multiple inheritance.

16)What is the difference between application object and session object?

The session object is used to maintain the session of each user. If a user enters into an application, he gets session id and when he leaves application then the session id is deleted. If he enters again in to the application he gets a different session id but for application object once ad id is generated it maintains whole application.

17)Is there any difference between primary key and unique with NOT NULL condition?

There is no difference between primary key and unique key with not null.

18)What is the difference between value type and reference type?

Value type and reference type may be similar in terms of declaration syntax and usage but their semantics are distinct

Value type are stored on stack while reference type are stored on heap.

Value type store real data while reference type store reference to the data.

Accessing is faster in value type on comparison to reference type.

Value type can contain null value while reference type can't contain null value.

Value types are derived from System.ValueType while Reference type is derived from System.Object.

19)What is the Boxing and Unboxing concept in .net?

Boxing:Implicit conversion of a value type (integer, character etc.) to a reference type (object) is called boxing. In boxing process a value type(which generally stores on stack) is being allocated on the heap rather than the stack.

Unboxing:explicit conversion of that same reference type (which is created by boxing process) back to a value type is known as unboxing. In unboxing process boxed value type is unboxed from the heap and allocated on the stack.

20)What is the difference between GET and POST methods?

GET and POST methods are form submission method. Both are used to send the data from client side to server side. These are some differences between GET and POST method -

In GET method caching is possible while it is not possible in POST method.

Only ASCII character data types are allowed in GET method while in POST method there is no restriction, it allows binary data also.

In GET method length of the string is restricted while in POST method length of the string is not restricted.

21)What is Log Shipping?

Log shipping is the process of automating the backup of a database and transaction log file on a primary database server, and then restoring them onto a standby server.

The primary purpose of log shipping is to increase database availability just like replication.

22)What are the different type of replication in SQL Server?

There are three type of replication in SQL Server.

1.snapshot replication:snapshot replication distributes data exactly as it appears at a specific moment. Snapshot replication is best method for replicating data that changes infrequently.

2.transactional replication:transactional replication is generally used in server to server environment. It is more appropriate when you want incremental change propagated to subscriber.

3.merge replication:snapshot replication is a process of distributing data from publisher to subscriber. It is generally used in server to client environment. Merge replication is appropriate when multiple subscribers might update the same data at various time.