SQL Server Interview Questions and Answers

1. Define SQL server

SQL is abbreviated as Structured Query Language. The primary purpose of the language is communicating with the databases. SQL Server is a relational database management system that mainly works on storing and retrieving data as required. SQL is a language, whereas SQL server assists the SQL language.

2. Mention the authentication modes in the SQL server?

Mixed-mode and windows modes are the two authentication modes in the SQL server. Changing the modes is possible with the SQL server’s configuration properties and tool menu.

3. Talk about the new features of SQL Server 2019?

Spark built-in
Virtual and single data layer
Spark jobs
Data integration and virtualization
train machine learning simulations
Management of all data and no data replication
Big data analytics has a unified platform

4. Mention the method for creating a table in SQL?

Creating a table in SQL starts by providing keywords create a table followed by giving the table’s name. Braces are provided where columns are listed with data types.

5. Discuss clustered and a non-clustered index?

The rearrangement of the index in the indexes order itself is called a clustered index. The data pages reside in the leaf nodes. A single clustered index is present in a table. Index not rearranged in the table in the order of the index is mentioned as a non-clustered index. Index rows are found in lead nodes instead of data pages. There are multiple non-clustered indexes in a table.

6. Name the TCP/IP port that runs the SQL server?

Port 1433 is the default port on which the SQL server runs.

7. Talk about normalization

The organization of data for minimizing redundancy and guaranteeing data integrity is known as normalization. During the normalization process, the database is split into two or more tables with proper definitions about the relationship amongst the tables. The technique of normalization elevates the performance of the database. The various types of normalization are 1NF, 2NF, 3NF, BCNF, 4NF, 5NF.

8. Talk about SQL profiler?

For checking the events that happen in the SQL server, the system administrator utilizes the SQL profiler tool. The main intention of the SQL profiler is to capture and save data of every event of table or file for analysis.

9. Explain the recovery model and mention the types of recovery models in SQL servers?

The purpose of a recovery model is to state the SQL server about the period and data type to be maintained in a transaction log file. There is only one recovery model for a database. Simple, full, and bulk-logged are the three types of recovery models in SQL servers.

10. Talk about Collation sensitivity?

For comparison and sorting of the strings in character data, collation sensitivity is employed. Character sorting follows standard rules such as character width, perfect character sequence, case sensitivity, and accent marks. The various types of collation sensitivity are Kana sensitivity, case sensitivity, width sensitivity, and accent sensitivity.

11. What is a check constraint?

For limiting the values placed in the column of a table, a check constraint is employed. With the assistance of check constraints, integrity is enforced.

12. Detail about scheduled tasks in SQL server?

For automation of the process to run on particular time intervals, scheduled tasks play a significant role. Human intervention plays a minor part by employing scheduled tasks, especially during night times, and feed is also possible at a specific time.

13. Define “join” in SQL and its types

Merging records from two or more tables in the SQL database based on the associated column between two is possible with the SQL “join” clause. There are four SQL join clauses categories: left outer join, right outer join, full outer join, and inner join.

14. Explain the primary key?

By using a primary key, it uniquely detects each row in a table. A primary key contains implicit, NOT NULL constraints and mandates unique values. While any SQL table is considered, it must have one and only one primary key that holds multiple or single fields.

15. Define Coalesce in SQL server?

The duty of coalesce in SQL is returning initial non-null expression within the argument. The primary purpose of coalesce function is returning non-null from more than one column in the argument.

16. What is the use of the Floor function in SQL?

For knowing the largest integer value of a given number, a floor function is used.

17. Define OLTP?

Online transaction processing is the abbreviation for OLTP, which adheres to data normalization rules for data integrity. The rules help in breaking down complex information into a simple structure.

18. Explain identity in SQL?

The identity in SQL generates numerical values automatically. Identity is also described as the initial and increment value of the identity column. There is no index required for identity columns.

19. Are there possibilities to check logs in the database, and how?

Checking logs in the database is possible by employing an in-built stored procedure called sp_lock.

20. What is XML data type?

For storing XML documents in the SQL server database, XML data types play a significant role. Variables and columns are created, and XML instances are stored in the database.

Conclusion:

SQL is a significant and best programming language that is highly in demand. Job seekers preparing for SQL-based interviews mandate deep knowledge of SQL for better job opportunities. By brushing up on vital concepts of SQL, job seekers are sure to hit the jackpot. To excel in SQL language as the best candidate holding hands with GSS in the USA ( Online SQL Server Training ) is an ideal choice as they are leaders in educating enthusiasts with SQL server.