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) What is the difference between clustered and non-clustered index?
Clustered Index: A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
Non-clustered Index: A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
8) What is the difference between HAVING CLAUSE and WHERE CLAUSE in SQL Server?
HAVING CLAUSE: HAVING CLAUSE is used only with the SELECT statement. It is generally used in a GROUP BY clause in a query.
If GROUP BY is not used, HAVING works like a WHERE clause.
WHERE Clause: The WHERE clause is applied to each row before they become a part of the GROUP BY function in a query.
9) What is the recursive stored procedure in SQL Server?
TheRecursive stored procedure is defined as a method of problem solving wherein the solution is arrived repetitively. SQL Server supports recursive stored procedure which calls by itself. It can nest up to 32 levels.
10) What are the advantages of using stored procedures in SQL Server?
A list of advantages of Stored Procedures:
- Stored procedures help in reducing the network traffic and latency. It boosts up the application performance.
- Stored procedures facilitate the reusability of the code.
- Stored procedures provide better security to data.
- You can encapsulate the logic using stored procedures and change stored procedure code without affecting clients.
- It is possible to reuse stored procedure execution plans, which are cached in SQL Server's memory. This reduces server overhead.
11) Define one-to-one relationship while designing tables.
One-to-One relationship:It can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
12) How can you hide the SQL Server instances?
You have to make a change in SQL Server Configuration Manager to hide the SQL Server instances.
Follow the below instructions to launch SQL Server Configuration Manager and do the following:
- Select the instance of SQL Server.
- Right click and select Properties.
- After selecting properties you will just set Hide Instance to "Yes" and click OK or Apply.
- After the change is made, you need to restart the instance of SQL Server to not expose the name of the instance.
13) What is CHECK constraint in SQL Server?
A CHECK constraint is applied to a column in a table to limit the values that can be placed in a column. It enforces integrity.
14) What do you mean by SQL Server agent?
The SQL Server agent plays a very important role in day to day tasks of SQL server administrator (DBA). Server agent?s purpose is to implement the tasks easily with the scheduler engine which allows our jobs to run at scheduled date and time.
15) What is COALESCE in SQL Server?
COALESCE is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.
16) 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.
17) 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.
18) 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.
19) 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.
20) 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.
21) 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:
DBCC CHECKDB: It makes sure that table in the database and the indexes are correctly linked.
DBCC CHECKALLOC: It checks all pages in the database and make sure that all are correctly allocated.
DBCC CHECKFILEGROUP: It checks all table file group for any damage.
22) What command is used to rename the database?
sp_renamedb 'oldname', 'newname';
23) 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.
24) 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.
25) 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.
26) 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.
27) 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.
28) 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.
29) 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.
30) 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.
31) 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.
32) Which is the main third party tool used in SQL Server?
A list of third party tools used in SQL Server:
- SQL CHECK - Idera: It is used to monitor server activities and memory levels.
- SQL DOC 2 - RedGate: It is used to document the databases.
- SQL Backup 5 - RedGate: It is used to automate the Backup Process.
- SQL Prompt - RedGate: It provides IntelliSense for SQL SERVER 2005/2000.
- Lite Speed 5.0 - Quest Soft: It is used to Backup and Restore.
33) What are the advantages of using third party tools?
A list of advantages using third party tools:
34) What are the different types of collation sensitivity in SQL Server?
There are 4 types of collation sensitivity in SQL Server:
35) What are the Hotfixes and Patches in SQL Server?
Hotfixs are small software patches that are applied to live systems . A hotfix is a single, cumulative package that includes one or more files used to address a problem in a software product.
For example: a software bug
In Microsoft SQL Server, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes.
36) What is the most common trace flags used with SQL Server?
The most common trace flags used with SQL Server are:
- Deadlock Information: 1204, 1205, 1222
- Network Database files: 1807
- Log Record for Connections: 4013
- Skip Startup Stored Procedures: 4022
- Disable Locking Hints: 8755
- Forces uniform extent allocations instead of mixed page allocations 1118 ? (SQL 2005 and 2008)
37) How will you decide the active and passive nodes?
Open Cluster Administrator checks the SQL Server group where you can see the current owner. So current owner is the active node and other nodes are passive.
38) What is the use of FLOOR function in SQL Server?
FLOOR function is used to round up a non-integer value to the previous least integer.
For example:
Output:
7
39) What is the usage of SIGN function?
SIGN function is used to define whether the number specified is Positive, Negative and Zero. This will return +1,-1 or 0.
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.