What is Normalization?
The process of table design to minimize the data redundancy is called normalization. We need to divide a database into two or more table and define relationships between them. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.
List the different normalization forms?
The Different normalization forms are:
1NF (Eliminate Repeating Groups): Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF (Eliminate Redundant Data): If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF (Eliminate Columns Not Dependent On Key): If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
BCNF (Boyce-Codd Normal Form): If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF (Isolate Independent Multiple Relationships): No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF (Isolate Semantically Related Multiple Relationships): There may be practical constrains on information that justifies separating logically related many-to-many relationships.
ONF (Optimal Normal Form): A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF (Domain-Key Normal Form): A model free from all modification is said to be in DKNF.
What is De-normalization?
De-normalization is the process of adding redundant data to a database to enhance the performance of it. It is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
What is Trigger and types of a trigger?
The trigger allows us to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table). Triggers are stored in and managed by DBMS. It can also execute a stored procedure.
2 types of triggers that are available in the SQL Server are as follows:
DML Triggers: DML or Data Manipulation Language triggers are invoked whenever any of the DML commands like INSERT, DELETE or UPDATE happens on the table or on the view.
DDL Triggers: DDL or Data Definition Language triggers are invoked whenever any changes occur in the definition of any of the database objects instead of actual data. These are very helpful to control the production and development database environments.
Logon Triggers: These are very special triggers which fire in case of the logon event of the SQL Server. This is fired before setup of a user session in the SQL Server.
What is the Subquery?
A Subquery is subset of select statements whose return values are used in filtering conditions of the main query. It can occur in a SELECT clause, FROM clause and WHERE clause. It nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.
Types of Sub-query:
Single-row sub-query: where the sub-query returns only one row
Multiple-row sub-query: where the subquery returns multiple rows, and
Multiple column sub-query: where the sub-query returns multiple columns
What is SQL Server used for?
SQL Server is one of the very popular Relational Database Management Systems. This is a product from the Microsoft to store and manage the information in the database.
Which language is supported by SQL Server?
SQL Server is based upon the implementation of the SQL also known as Structured Query Language to work with the data inside the database.
Which is the latest version of SQL Server and when it is released?
SQL Server 2017 is the latest version of SQL Server that is available in the market and Microsoft launched this on 2 October 2017 with the support of the Linux O/S.
What is TCL in SQL Server?
TCL is Transaction Control Language Commands which are used to manage the transactions in the SQL Server.
Which TCL Commands are available on the SQL Server?
There are 3 TCL Commands in the SQL Server. These are as follows:
Commit: This command is used to save the transaction permanently in the database.
Rollback: This is used to roll back the changes that are done i.e. to restore the database in the last committed state.
Save Tran: This is used for saving the transaction so as to provide the convenience that the transaction can be rolled back to the point wherever required.
What are the 2 types of classifications of constraints in the SQL Server?
Constraints are classified into the following 2 types in the SQL Server:
Column Types Constraints: These constraints are applied to the columns of a table in the SQL Server. Definition of these can be given at the time of the creation of a table in the database.
Table Types Constraints: These constraints are applied on a table and these are defines after the creation of a table is completed. Alter command is used to apply the table type constraint.
How is table type constraint applied to a table?
The Table Type Constraint is applied in the following way:
Alter Table Name of the Constraint
Alter Table Constraint_1
What are the different types of Columns Types Constraints in the SQL Server?
SQL Server provides 6 types of Constraints. These are as follows:
Not Null Constraint: This puts a constraint that the value of a column cannot be null.
Default Constraint: This constraint provides some default value that can be inserted in the column if no value is specified for that column.
Check Constraint
Unique Constraint
Primary Key Constraint
Foreign Key Constraint
What are the various editions of SQL Server 2017 that are available in the market?
SQL Server 2017 is available in 4 editions. These are as follows:
Enterprise: This supports in leading the high performance for the Tier 1 database along with the capability of supporting business intelligence and workloads of advanced analytics.
Standard: This supports mid-tier applications to achieve fast performance. This can be easily upgraded to enterprise edition also without having any changes in the coding part.
Express: This is available for free and supports the building of web and mobile applications up to 10 GB in the size.
Developer: This supports building, testing and demonstrating applications in a non-production.
What are functions in the SQL Server?
Functions are the sequence of the statements which accepts inputs, process the inputs to perform some specific task and then provides the outputs. Functions should have some meaningful name but these should not start with a special character such as %,#,@, etc.
What is a User-Defined function in the SQL Server and what is its advantage?
User-Defined Function is a function which can be written as per the needs of the user by implementing your own logic. The biggest advantage of this function is that the user is not limited to pre-defined functions and can simplify the complex code of pre-defined function by writing a simple code as per the needs.
This returns Scalar value or a table.
Explain creation and execution of a user-defined function in the SQL Server?
A User-Defined function can be created in the following way:
Create Function fun1(@num int)
returns table
as
return select * from employee where empid=@num
This function can be executed as follows:
select * from fun1(12)
So, in the above case, a function with the name of ‘fun1’ is created to fetch employee details of an employee having empid=12.
What are the Pre-Defined functions in the SQL Server?
These are Built-In functions of the SQL Server like String functions which are provided by SQL Server like ASCII, CHAR, LEFT, etc. string functions.
What is SQL Server Agent?
SQL Server agent allows us to schedule the jobs and scripts. It helps is implementing the day to day DBA tasks by automatically executing them on a scheduled basis.
What is the PRIMARY KEY?
Primary key is column whose values uniquely identify every row in a table. Primary key values can never be reused.
What is a UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
What is Stored Procedure?
A stored procedure is a set of SQL queries which can take input and send back output. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
List the advantages of using Stored Procedures?
Advantages of using Stored procedures are:
Stored procedure boosts application performance.
Stored procedure execution plans can be reused as they cached in SQL Server’s memory which reduces server overhead.
Stored procedures can be reused.
Stored procedures can encapsulate logic. You can change the stored procedure code without affecting clients.
Stored procedures provide better security for your data.
What is identity in SQL?
An identity column in the SQL automatically generates numeric values. We can be defined as a start and increment value of the identity column. Identity columns do not need to be indexed.
Why Would You Use Sql Agent?
SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.
What Happens On Checkpoint?
Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database.
One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.
What Is Dbcc?
DBCC statements are Database Console Commands and come in four flavors
Maintenance, Informational, Validation, and Miscellaneous. Maintenance commands are those commands that allow the DBA to perform maintenance activities on the database such as shrinking a file. Informational commands provide feedback regarding the database such as providing information about the procedure cache. Validation commands include commands that validate the database such as the ever-popular CHECKDB. Finally, miscellaneous commands are those that obviously don’t fit in the other three categories. This includes statements like DBCC HELP, which provides the syntax for a given DBCC command.
How Can You Control The Amount Of Free Space In Your Index Pages?
You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy rows from one index page to another to make room for an inserted row) because there is room for growth built in to the index.
What Types Of Replication Are Supported In Sql Server?
SQL Server has three types of replication: Snapshot, Merge, and Transaction. Snapshot replication creates a snapshot of the data (point-in-time picture of the data) to deliver to the subscribers. This is a good type to use when the data changes infrequently, there is a small amount of data to replicate, or large changes occur over a small period of time.
Merge replication uses a snapshot to seed the replication. Changes on both sides of the publication are tracked so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication is in a client and server scenario. A server would act as a central repository and multiple clients would independently update their copies of the data until connected. At which time, they would all send up their modifications to the central store.
Transaction replication also begins with a snapshot only this time changes are tracked as transactions (as the name implies). Changes are replicated from publisher to subscriber the same as they occurred on the publisher, in the same order as they occurred, and in near real time. This type of replication is useful when the subscriber needs to know every change that occurred to the data (not point-in-time), when the change volume is high, and when the subscriber needs near real-time access to the changes.
The process of table design to minimize the data redundancy is called normalization. We need to divide a database into two or more table and define relationships between them. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.
List the different normalization forms?
The Different normalization forms are:
1NF (Eliminate Repeating Groups): Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF (Eliminate Redundant Data): If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF (Eliminate Columns Not Dependent On Key): If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
BCNF (Boyce-Codd Normal Form): If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF (Isolate Independent Multiple Relationships): No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF (Isolate Semantically Related Multiple Relationships): There may be practical constrains on information that justifies separating logically related many-to-many relationships.
ONF (Optimal Normal Form): A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF (Domain-Key Normal Form): A model free from all modification is said to be in DKNF.
 |
| Red Hat Frequently Asked SQL Server Interview Questions Answers |
What is De-normalization?
De-normalization is the process of adding redundant data to a database to enhance the performance of it. It is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
What is Trigger and types of a trigger?
The trigger allows us to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table). Triggers are stored in and managed by DBMS. It can also execute a stored procedure.
2 types of triggers that are available in the SQL Server are as follows:
DML Triggers: DML or Data Manipulation Language triggers are invoked whenever any of the DML commands like INSERT, DELETE or UPDATE happens on the table or on the view.
DDL Triggers: DDL or Data Definition Language triggers are invoked whenever any changes occur in the definition of any of the database objects instead of actual data. These are very helpful to control the production and development database environments.
Logon Triggers: These are very special triggers which fire in case of the logon event of the SQL Server. This is fired before setup of a user session in the SQL Server.
What is the Subquery?
A Subquery is subset of select statements whose return values are used in filtering conditions of the main query. It can occur in a SELECT clause, FROM clause and WHERE clause. It nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.
Types of Sub-query:
Single-row sub-query: where the sub-query returns only one row
Multiple-row sub-query: where the subquery returns multiple rows, and
Multiple column sub-query: where the sub-query returns multiple columns
What is SQL Server used for?
SQL Server is one of the very popular Relational Database Management Systems. This is a product from the Microsoft to store and manage the information in the database.
Which language is supported by SQL Server?
SQL Server is based upon the implementation of the SQL also known as Structured Query Language to work with the data inside the database.
Which is the latest version of SQL Server and when it is released?
SQL Server 2017 is the latest version of SQL Server that is available in the market and Microsoft launched this on 2 October 2017 with the support of the Linux O/S.
What is TCL in SQL Server?
TCL is Transaction Control Language Commands which are used to manage the transactions in the SQL Server.
Which TCL Commands are available on the SQL Server?
There are 3 TCL Commands in the SQL Server. These are as follows:
Commit: This command is used to save the transaction permanently in the database.
Rollback: This is used to roll back the changes that are done i.e. to restore the database in the last committed state.
Save Tran: This is used for saving the transaction so as to provide the convenience that the transaction can be rolled back to the point wherever required.
What are the 2 types of classifications of constraints in the SQL Server?
Constraints are classified into the following 2 types in the SQL Server:
Column Types Constraints: These constraints are applied to the columns of a table in the SQL Server. Definition of these can be given at the time of the creation of a table in the database.
Table Types Constraints: These constraints are applied on a table and these are defines after the creation of a table is completed. Alter command is used to apply the table type constraint.
How is table type constraint applied to a table?
The Table Type Constraint is applied in the following way:
Alter Table Name of the Constraint
Alter Table Constraint_1
What are the different types of Columns Types Constraints in the SQL Server?
SQL Server provides 6 types of Constraints. These are as follows:
Not Null Constraint: This puts a constraint that the value of a column cannot be null.
Default Constraint: This constraint provides some default value that can be inserted in the column if no value is specified for that column.
Check Constraint
Unique Constraint
Primary Key Constraint
Foreign Key Constraint
What are the various editions of SQL Server 2017 that are available in the market?
SQL Server 2017 is available in 4 editions. These are as follows:
Enterprise: This supports in leading the high performance for the Tier 1 database along with the capability of supporting business intelligence and workloads of advanced analytics.
Standard: This supports mid-tier applications to achieve fast performance. This can be easily upgraded to enterprise edition also without having any changes in the coding part.
Express: This is available for free and supports the building of web and mobile applications up to 10 GB in the size.
Developer: This supports building, testing and demonstrating applications in a non-production.
What are functions in the SQL Server?
Functions are the sequence of the statements which accepts inputs, process the inputs to perform some specific task and then provides the outputs. Functions should have some meaningful name but these should not start with a special character such as %,#,@, etc.
What is a User-Defined function in the SQL Server and what is its advantage?
User-Defined Function is a function which can be written as per the needs of the user by implementing your own logic. The biggest advantage of this function is that the user is not limited to pre-defined functions and can simplify the complex code of pre-defined function by writing a simple code as per the needs.
This returns Scalar value or a table.
Explain creation and execution of a user-defined function in the SQL Server?
A User-Defined function can be created in the following way:
Create Function fun1(@num int)
returns table
as
return select * from employee where empid=@num
This function can be executed as follows:
select * from fun1(12)
So, in the above case, a function with the name of ‘fun1’ is created to fetch employee details of an employee having empid=12.
What are the Pre-Defined functions in the SQL Server?
These are Built-In functions of the SQL Server like String functions which are provided by SQL Server like ASCII, CHAR, LEFT, etc. string functions.
What is SQL Server Agent?
SQL Server agent allows us to schedule the jobs and scripts. It helps is implementing the day to day DBA tasks by automatically executing them on a scheduled basis.
What is the PRIMARY KEY?
Primary key is column whose values uniquely identify every row in a table. Primary key values can never be reused.
What is a UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
What is Stored Procedure?
A stored procedure is a set of SQL queries which can take input and send back output. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
List the advantages of using Stored Procedures?
Advantages of using Stored procedures are:
Stored procedure boosts application performance.
Stored procedure execution plans can be reused as they cached in SQL Server’s memory which reduces server overhead.
Stored procedures can be reused.
Stored procedures can encapsulate logic. You can change the stored procedure code without affecting clients.
Stored procedures provide better security for your data.
What is identity in SQL?
An identity column in the SQL automatically generates numeric values. We can be defined as a start and increment value of the identity column. Identity columns do not need to be indexed.
Why Would You Use Sql Agent?
SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.
What Happens On Checkpoint?
Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database.
One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.
What Is Dbcc?
DBCC statements are Database Console Commands and come in four flavors
Maintenance, Informational, Validation, and Miscellaneous. Maintenance commands are those commands that allow the DBA to perform maintenance activities on the database such as shrinking a file. Informational commands provide feedback regarding the database such as providing information about the procedure cache. Validation commands include commands that validate the database such as the ever-popular CHECKDB. Finally, miscellaneous commands are those that obviously don’t fit in the other three categories. This includes statements like DBCC HELP, which provides the syntax for a given DBCC command.
How Can You Control The Amount Of Free Space In Your Index Pages?
You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy rows from one index page to another to make room for an inserted row) because there is room for growth built in to the index.
What Types Of Replication Are Supported In Sql Server?
SQL Server has three types of replication: Snapshot, Merge, and Transaction. Snapshot replication creates a snapshot of the data (point-in-time picture of the data) to deliver to the subscribers. This is a good type to use when the data changes infrequently, there is a small amount of data to replicate, or large changes occur over a small period of time.
Merge replication uses a snapshot to seed the replication. Changes on both sides of the publication are tracked so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication is in a client and server scenario. A server would act as a central repository and multiple clients would independently update their copies of the data until connected. At which time, they would all send up their modifications to the central store.
Transaction replication also begins with a snapshot only this time changes are tracked as transactions (as the name implies). Changes are replicated from publisher to subscriber the same as they occurred on the publisher, in the same order as they occurred, and in near real time. This type of replication is useful when the subscriber needs to know every change that occurred to the data (not point-in-time), when the change volume is high, and when the subscriber needs near real-time access to the changes.