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 the RDBMS system provided by Microsoft which functions mainly as retrieving and storing the data as per user request. Sometimes it is mistakenly referred as SQL, but both are 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 and surety of logical data integrity is called normalization. In normalization, the database is divided into two or more tables, and a relationship is defined among the tables. Normalization technique increases performance for the database.
Types of Normalization
There are types of normalization used, which are given below.
- 1NF
- 2NF
- 3NF
- BCNF
- 4NF
- 5NF
However, the first three types are only frequently used, where “NF” stands for normal form. The originator of the RD model “E.F Codd” has proposed the process “normalization” with the first “normal form” and continued till third normal form.
3) What is De-Normalization?
It is a process of attempting to optimize the performance of a database by adding redundant data. Redundancy is introduced intentionally in a table to improve performance, and it is called de-normalization. The de-Normalization process enhances the read performance while some degradation occurs in write performance. It can be achieved by making a group of data in the redundant form. The un-normalized and de-Normalized database are completely different from each other. Before the process of de-normalization of any database, that should be normalized firstly.
4) What is the difference between function and stored procedure?
- A 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.
- Try-catch block can handle exceptions. In the 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. The basic rule for sorting a character data are correct character sequence, Case-sensitivity, character width, and accent marks, etc.
Different types of collation sensitivity:
- Case sensitivity
- Accent sensitivity
- Kana sensitivity
- Width sensitivity
Case Sensitivity: Case sensitivity defines every character with a unique value, as alphabet characters A and a are treated individually, as they have different ASCII values for a computer language
Accent sensitivity: Accent sensitivity is related that whether the accent is off or not, as a and á both should be treated differently
Kana sensitivity: Kana sensitivity defines the difference between two Japanese words: Hiragana and Katakana
Width sensitivity: It differentiates between a single-byte character (half- width) and representation of the double-byte character of the same character
6) What is the standby server?
The Standby server is the type of server which is brought online when the primary server goes offline, and the application needs continuous availability of the server. The requirement for a mechanism which can shift a primary server to secondary or standby server is always there.
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 the secondary server in a real-time and this way both systems contain 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 a regular interval, so in this method sometimes both servers don’t contain the 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. The physical replacement of Primary server with standby server occurs in cold standby.
7) What is the difference between clustered and non-clustered index?
Clustered Index: A clustered index is a particular type of index that reorders the way records in the table are physically stored. It gives a sequence of data which is physically stored in the database. Therefore a table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. Index id of the clustered index is 0. So a primary key constraint automatically creates a clustered index.
Non-clustered Index: A non-clustered index is a particular type of index in which the logical order of the index does not match the physically stored order of the rows on disk. In non-clustered index data and indexes are stored in different places. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Index id of non-clustered indexes is greater than 0.
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. HAVING clause can be used with the aggregate function.
Syntax:
WHERE Clause: The WHERE clause is applied to each row before they become a part of the GROUP BY function in a query. ‘WHERE’ clause cannot use with the aggregate function.
Syntax:
9) What is the recursive stored procedure in SQL Server?
The Recursive stored procedure is defined as a method of problem-solving wherein the solution arrives repetitively. SQL Server supports recursive stored procedure which calls by itself. It can nest up to 32 levels. It can be called by itself directly or indirectly
There are two ways to achieve recursion in the stored procedure:
- Mutual Recursion: By Using mutually recursive stored procedure, indirect recursion can be achieved
- Chain Recursion: If we extend mutual recursion process then we can achieve chain recursion.
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 for 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.
- It provides modularity of application.
11) Define the 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.
One to one relationship exists if an entity in one table has a link with only one entity on another table. Let?s take an example of the employee and their employee id so that a unique employee id will be there for a particular employee at another table.
12) How can you hide the SQL Server instances?
You have to make a change in the 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, not to 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. After using the check constraint on the single column, we can only give some specific values for that particular column. Check constraint apply a condition for each column in the table.
EXAMPLE:
14) What do you mean by SQL Server agent?
The SQL Server agent plays a vital role in day to day tasks of SQL server administrator (DBA). It is one of the essential parts of the Microsoft’s SQL server. 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. SQL server agent store scheduled administrative tasks information using SQL server.
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. COALESCE accepts all the values but it only returns non-null value present in the expression.
Syntax:
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?
Authentication mode is used for authentication of the user in SQL server, and it can be selected at the time of setup of the database engine.
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. In Windows authentication server take computer’s username and password for authentication purpose. SQL server authentication mode is disabled in this mode.
Mixed mode: It is used to connect with the instance of SQL Server using window authentication or SQL Server authentication. In SQL server authentication mode a unique username and password are required for a particular database, as it will not depend on windows account.
18) What is SQL Server Profiler?
Microsoft SQL Server Profiler is a graphical user interface that allows system administrators to monitor events of the database engine. SQL server profiler trace monitor every event to a file. SQL profiler can be used for real-time monitoring of data or also for future analysis of data.
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.
You can find out the bugs in queries and diagnose it.
If it is necessary, you can start, stop, pause and modify the trace results.
19) What is the 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 the DBCC command and why is it used?
DBCC stands for database consistency checker. This command is used to check the consistency of the database. DBCC command help to review and monitoring the maintenance of tables, database, and for validation of operations done on the database, etc. 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 makes sure that all are correctly allocated.
DBCC CHECKFILEGROUP: It checks all table file group for any damage.
IF the user executes the above commands, a database snapshot is created through the database engine, and it continues in the consistent transactional state. After that, It runs the checks against stored database snapshot, and after the completion of the command, it dropped the snapshot.
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 Server. 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.
- An Abstract method does not contain any definition.
- When a class contains all functions without the body, it is called as Fully Abstract Class.
- Another class can inherit only one abstract class.
Interface:
- If a class contains all abstract methods, then that class is called Interface.
- Interface support like multiple inheritances.
- An Interface does not contain any implementation
- We can only use public or abstract modifiers.
25) What is the difference between the 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 a session id, and when he leaves application, then the session id is deleted. If he enters again into the application, he gets a different session id, but for application object, once ad id is generated, it maintains the whole application.
26) Is there any difference between primary key and unique with the NOT NULL condition?
There is no difference between primary key and unique key but, a unique key will allow single NULL, but in the primary key, no NULL is accepted.
27) What is the difference between value type and reference type?
Value type and reference type may be similar regarding declaration syntax and usage, but their semantics are distinct. Value type and reference type differ with the memory area, where it stored.
The Value type is stored on the stack while reference type is stored on the heap.
The Value type stores real data while reference type stores reference to the data.
Accessing is faster in the value type on comparison to reference type.
The value type can contain null value while reference type can’t hold a null value.
The value types are derived from System. Value Type while Reference type is derived from System Object. Means value type stores a particular value but a reference type stores a reference or address to the object
String, Object, array are the reference type, as they are stored in heap and dynamic in nature
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 the stack) is being allocated on the heap rather than the stack. Boxing wraps a value inside the object which can be stored in the heap section.
Example
Unboxing: explicit conversion of that same reference type (which is created by boxing process) back to a value type is known as unboxing. In the unboxing process, a boxed value type is unboxed from the heap and allocated on the stack.
Example
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 limited.
Get method is not secured as it can be bookmarked but, post method is secured as it cannot be bookmarked
Get method is stored in browser history but post method is not stored in browser history
30) What is the 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 on a standby server. Many servers support this technique for maintaining a backup server, such as Microsoft SQL Server, 4D server, MySQL, and PostgreSQL.
The primary purpose of log shipping is to increase database availability just like replication. On each of secondary database, log backups are applied particularly.
Steps for log shipping process:
- Firstly take a backup of transaction log file on Primary SQL server instance
- Copy the log file on secondary SQL server instance
- Restore the Log backup file onto secondary SQL Server instance
31) What are the different type of replication in SQL Server?
There are three types of replication in SQL Server.
1) Snapshot replication: Snapshot replication distributes data exactly as it appears at a specific moment. Snapshot replication is the best method for replicating data that changes infrequently. Snapshot replication is the easiest way to maintain.
2) Transactional replication: Transactional replication is a process of distributing data from publisher to subscriber. Transactional replication is generally used in the “server to server” environment. It is appropriate when you want incremental change propagated to the subscriber.
3) Merge replication: Merge replication grouped the data from various sources to a single centralized database. It is generally used in the server to the client environment. Merge replication is appropriate when multiple subscribers might update the same data at the 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 for Backup and Restore.
33) What are the advantages of using third-party tools?
A list of advantages of using third-party tools:
- Third party tools provide faster backups and restore.
- They provide flexible backup and recovery options.
- They provide secure backups with encryption.
- They provide the enterprise view of your backup and recovery environment.
- Easily identify optimal backup settings.
- Visibility into the transaction log and transaction log backups.
- Timeline view of backup history and schedules.
- Recover individual database objects.
- Encapsulate a complete database restore into a single file to speed up restore time.
- When we need to improve upon the functionality that SQL Server offers natively.
- Save time, better information or notification.
- Third party tools can put the backups in a single compressed file to reduce the space and time.
34) What are the different types of collation sensitivity in SQL Server?
There are four types of collation sensitivity in SQL Server:
- Case sensitivity
- Accent sensitivity
- Kana Sensitivity
- Width sensitivity
Explained in detail in Question number 5
35) What are the Hotfixes and Patches in SQL Server?
Hotfixes 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
A patch is a program installed in the machine to rectify the problem occurred in the system and ensured the security of that system. The hotfix is a Kind of Patches provided by the Microsoft.
In Microsoft SQL Server, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes. Hotfix response proactively against any bug
36) What is the most common trace flags used with SQL Server?
Trace flag in SQL server sets the specific characteristic of the server. It works as an “IF” condition for the 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
- Do Force 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 the current owner is the active node, and other nodes are passive. Because at one time only one node can be active and must be in the passive mode in a two node.
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. Floor expression returns a unique value after rounding down the expression.
SYNTAX:
For example:
FLOOR (7.3)
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. SIGN function returns the value with its sign.
SYNTAX:
40) What is sub-query in SQL server? Explain its properties.
In SQL Server, a query within the main query like Select, Update, Insert or Delete, is termed as sub-query. It is also called as INNER Query.
A subquery can be Added to WHERE clause, the FROM clause, or the SELECT clause.
Some properties of the subqueries are given below:
- A subquery must be enclosed in parenthesis
- A sub-query can add WHERE, GROUP BY, and HAVING CLAUSE but it’s optional.
- SELECT clause and a FROM clause must be included a subquery.
- A User can include more than one query
41) How can we delete a table in SQL Server?
A user can delete a TABLE from the database by using SQL Server Management Studio or by Transact-SQL in SQL Server
Following are the steps for deleting a table using SQL Server Management
- Select a Table(wanted to remove) in object explorer
- Choose DELETE from the shortcut menu by right- click on the table
- Click on the ‘yes’ to confirm the deletion of the table
42) What are the encryption mechanisms in SQL server?
We can use encryption for security of data in the database in SQL Server. Following are the encryption mechanism used in SQL server:
- Transact-SQL functions
- Asymmetric keys
- Symmetric keys
- Certificates
- Transparent Data Encryption
43) Define Magic Tables in SQL server?
A Table which is automatically created and managed by SQL server internally to store the inserted, updated values for any DML (SELECT, DELETE, UPDATE, etc.) operation, is called as Magic tables in SQL server. The triggers preferably use it.
44) What is CDC in SQL Server?
CDC is termed as “Change Data Capture.” It captures the recent activity of INSERT, DELETE, and UPDATE, which are applied to the SQL Server table. It records the changes made in the SQL server table in a compatible format.
45) How many types of database relationship in SQL server?
There are three types of relationship exist in SQL server:
- One to One Relationship
- Many to Many relationship
- One to One relationship