What command is used to create a database in the SQL Server and how?
CREATEDATABASE Command is used to create any database in the SQL Server. Following is the way to use this command:
CREATEDATABASE Name of the Database
Ex: If the name of a database is “employee” then create command to create this database can be written as CREATEDATABASE employee.
What function does a database engine serve in the SQL Server?
Database Engine is a type of a service in the SQL Server which starts as soon as the Operating System starts. This may run by default depending upon the settings in the O/S.
What are the advantages of having an index on the SQL Server?
The index has the following advantages:
Index supports the mechanism of having faster data retrieval from the database.
This forms a data structure in a way which helps in minimizing the data comparisons.
This improves the performance of the retrieval of the data from the database.
What is the SQL Profiler?
SQL Profiler provides a graphical representation of events in an instance of SQL Server for the monitoring and investment purpose. We can capture and save the data for further analysis. We can put filters as well to captures the specific data we want.
What do you mean by authentication modes in SQL Server?
There are two authentication modes in SQL Server.
Windows mode
Mixed Mode – SQL and Windows.
How can we check the SQL Server version?
By running the following command:
SELECT @@Version
Is it possible to call a stored procedure within a stored procedure?
Yes, we call a stored procedure within a stored procedure It is called recursion property of SQL server and these type of stored procedures are called nested stored procedures.
What are the common performance issues in SQL Server?
Following are the common performance issues:
Deadlocks
Blocking
Missing and unused indexes.
I/O bottlenecks
Poor Query plans
Fragmentation
List the various tools available for performance tuning?
There are various tools available for performance tuning:
Dynamic Management Views
SQL Server Profiler
Server Side Traces
Windows Performance monitor.
Query Plans
Tuning advisor
What is FOREIGN KEY
When a one table’s primary key field is added to related tables in order to create the common field which relates the two tables, it called a foreign key in other tables.
Foreign Key constraints enforce referential integrity.
What is a CHECK Constraint?
A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.
What are a Scheduled Jobs?
Scheduled job allows a user to run the scripts or SQL commands automatically on the scheduled basis. User can determine the order in which commands need to execute and the best time to run the job to avoid the load on the system.
What is a heap?
A heap is a table that does not contain any clustered index or non-clustered index.
What is BCP?
BCP or Bulk Copy is a tool by which we can copy a large amount of data to tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.
What is a Linked Server?
Linked Servers is a concept by which we can connect another SQL server to a Group and query both the SQL Servers database using T-SQL Statements
sp_addlinkedsrvloginisssed to add link server.
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.
What is a View?
A view is a virtual table which contains data from one or more tables. Views restrict data access of table by selecting only required values and make complex queries easy.
Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database
Where SQL server usernames and passwords are stored in SQL server?
They get stored in System Catalog Views sys.server_principals and sys.sql_logins.
What are the properties of a transaction?
Generally, these properties are referred to as ACID properties. They are:
Atomicity
Consistency
Isolation
Durability
Define UNION, UNION ALL, MINUS, INTERSECT?
UNION – returns all distinct rows selected by either query.
UNION ALL – returns all rows selected by either query, including all duplicates.
MINUS – returns all distinct rows selected by the first query but not by the second.
INTERSECT – returns all distinct rows selected by both queries.
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
CREATEDATABASE Command is used to create any database in the SQL Server. Following is the way to use this command:
CREATEDATABASE Name of the Database
Ex: If the name of a database is “employee” then create command to create this database can be written as CREATEDATABASE employee.
What function does a database engine serve in the SQL Server?
Database Engine is a type of a service in the SQL Server which starts as soon as the Operating System starts. This may run by default depending upon the settings in the O/S.
What are the advantages of having an index on the SQL Server?
The index has the following advantages:
Index supports the mechanism of having faster data retrieval from the database.
This forms a data structure in a way which helps in minimizing the data comparisons.
This improves the performance of the retrieval of the data from the database.
 |
| Cognizant Frequently Asked SQL Server Interview Questions Answers |
SQL Profiler provides a graphical representation of events in an instance of SQL Server for the monitoring and investment purpose. We can capture and save the data for further analysis. We can put filters as well to captures the specific data we want.
What do you mean by authentication modes in SQL Server?
There are two authentication modes in SQL Server.
Windows mode
Mixed Mode – SQL and Windows.
How can we check the SQL Server version?
By running the following command:
SELECT @@Version
Is it possible to call a stored procedure within a stored procedure?
Yes, we call a stored procedure within a stored procedure It is called recursion property of SQL server and these type of stored procedures are called nested stored procedures.
What are the common performance issues in SQL Server?
Following are the common performance issues:
Deadlocks
Blocking
Missing and unused indexes.
I/O bottlenecks
Poor Query plans
Fragmentation
List the various tools available for performance tuning?
There are various tools available for performance tuning:
Dynamic Management Views
SQL Server Profiler
Server Side Traces
Windows Performance monitor.
Query Plans
Tuning advisor
What is FOREIGN KEY
When a one table’s primary key field is added to related tables in order to create the common field which relates the two tables, it called a foreign key in other tables.
Foreign Key constraints enforce referential integrity.
What is a CHECK Constraint?
A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.
What are a Scheduled Jobs?
Scheduled job allows a user to run the scripts or SQL commands automatically on the scheduled basis. User can determine the order in which commands need to execute and the best time to run the job to avoid the load on the system.
What is a heap?
A heap is a table that does not contain any clustered index or non-clustered index.
What is BCP?
BCP or Bulk Copy is a tool by which we can copy a large amount of data to tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.
What is a Linked Server?
Linked Servers is a concept by which we can connect another SQL server to a Group and query both the SQL Servers database using T-SQL Statements
sp_addlinkedsrvloginisssed to add link server.
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.
What is a View?
A view is a virtual table which contains data from one or more tables. Views restrict data access of table by selecting only required values and make complex queries easy.
Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database
Where SQL server usernames and passwords are stored in SQL server?
They get stored in System Catalog Views sys.server_principals and sys.sql_logins.
What are the properties of a transaction?
Generally, these properties are referred to as ACID properties. They are:
Atomicity
Consistency
Isolation
Durability
Define UNION, UNION ALL, MINUS, INTERSECT?
UNION – returns all distinct rows selected by either query.
UNION ALL – returns all rows selected by either query, including all duplicates.
MINUS – returns all distinct rows selected by the first query but not by the second.
INTERSECT – returns all distinct rows selected by both queries.
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
https://mytecbooks.blogspot.com/2019/01/cognizant-frequently-asked-sql-server.html