Sql Server Interview questions ans Part-4

Q#51. What is SQL Server used for?

Ans. SQL Server is one of the very popular Relational Database Management Systems. This is a product from Microsoft to store and manage the information in the database.

Q#52. Which language is supported by SQL Server?

Ans. SQL Server is based upon the implementation of the SQL also known as Structured Query Language to work with the data inside the database.

Q#53. Which is the latest version of SQL Server and when it is released?

Ans. 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.

Q#54. What are the various editions of SQL Server 2017 that are available in the market?

Ans. 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 an 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.

Q#55. What are functions in the SQL Server?

Ans. 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.

Q#56. What is a User-Defined function in the SQL Server and what is its advantage?

Ans. 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.

Q#57. Explain the creation and execution of a user-defined function in the SQL Server?

Ans.  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.

 Q#58. What are the Pre-Defined functions in the SQL Server?

Ans. 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.

Q#59. Why are Views required in the SQL Server or in any other database?

Ans.  Views are very beneficial because of the following reasons:

• Views are required to hide the complexity that is involved in the database schema and also to customize the data for a particular set of users.
• Views provide a mechanism to control access to particular rows and columns.
• These help in aggregating the data to improve the performance of the database.

Q#60. What is TCL in SQL Server?

Ans. TCL is Transaction Control Language Commands which are used to manage the transactions in the SQL Server.

Q#61. Which TCL Commands are available on the SQL Server?

Ans.  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.

Q#62. What are the 2 types of classifications of constraints in the SQL Server?

Ans. 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.

Q#63. How is table type constraint applied to a table?

Ans. The Table Type Constraint is applied in the following way:

Alter Table Name of the Constraint

Alter Table Constraint_1

Q#64. What are the different types of Columns Types Constraints in the SQL Server?

Ans. 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

Q#64. What command is used to delete a table from the database in the SQL Server and how?

Ans. Delete Command is used to delete any table from the database in the SQL Server. Following is the way to use this command:

Delete Name of the table

Ex: If the name of a table is “employee” then delete command to delete this table can be written as Delete employee.

Q#65. Why is replication required on the SQL Server?

Ans. Replication is the mechanism which is used to synchronize the data among the multiple servers with the help of a replica set.

This is mainly used to increase the capacity of the reading and to provide an option to its users to select among various different servers to perform the read/write operations.

Q#66. What command is used to create a database in the SQL Server and how?

Ans. 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.

Q#67. What function does a database engine serve in the SQL Server?

Ans. Database Engine is a type of 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.

Q#68. What are the advantages of having an index on the SQL Server?

Ans. 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 dat