Interview Questions

SQL Server Interview Questions and Answers For Freshers and Experienced

May 17, 2021

37338

SQL Server Interview Questions and Answers For Freshers and ExperiencedSQL Server Interview Questions

SQL Server Interview Questions

1. What is Database?

A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system. Databases can be vast and complex, and such databases are developed using fixed design and modeling approaches.

2. What is DBMS?

DBMS stands for Database Management System. DBMS is a system software responsible for the creation, retrieval, update, and management of the database. It ensures that our data is consistent, organized, and is easily accessible by serving as an interface between the database and its end-users or application software.

3. What is RDBMS? How is it different from DBMS?

RDBMS stands for Relational Database Management System. The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables. Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift are based on RDBMS.

4. What is SQL?

SQL stands for Structured Query Language. It is the standard language for relational database management systems. It is especially useful in handling organized data comprised of entities (variables) and relations between different entities of the data. This is most Important SQL Server Interview Questions

5. What is the difference between SQL and MySQL?

SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle, or IBM DB2, that is used to manage SQL databases.

6. What is RDBMS? How is it different from DBMS?

7. Explain how you will create a table in SQL Server?

We can create a table in SQL Server using the following code:

create table TableName (column1 datatype, column2 datatype,…, column N datatype)

For example, the following code:

create table Dummy
(
Name varchar(20), Address varchar(40), PhoneNo. nvarchar(12)
)

Will create a table Dummy with 3 columns; Name, Address, and PhoneNo.

8. What are the two authentication modes in SQL Server?

There are two authentication modes –

Windows Mode
Mixed Mode

Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose the security page.

9. Explain the CHECK constraint.

The CHECK constraint enforces integrity. It is applied to a column in a table for limiting the values that can be inserted in the same. A column upon which the CHECK constraint is applied can only have some specific values. Following is an example of applying the CHECK constraint in a SQL Server database:

CREATE TABLE Dummy
(
S.No. int,
Name varchar(255),
Age int,
CONSTRAINT CHK_Dummy CHECK (Age>17)
);

10. 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. This is most Important SQL Server Interview Questions

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 the third normal form.

11. 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 that can shift a primary server to a 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 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 the Primary server with the standby server occurs in cold standby.

12. What is a Trigger?

Triggers are used to execute a batch of SQL code when insert or update or delete commands are executed against a table. Triggers are automatically triggered or executed when the data is modified. It can be executed automatically on the insert, delete and update operations. This is most Important SQL Server Interview Questions

13.What are the types of Triggers?

There are four types of triggers and they are:

Insert
Delete
Update
Instead of

14. What is SQL injection?

SQL injection is an attack by malicious users in which malicious code can be inserted into strings that can be passed to an instance of SQL Server for parsing and execution. All statements have to checked for vulnerabilities as it executes all syntactically valid queries that it receives.

Even parameters can be manipulated by skilled and experienced attackers.

15. Explain relationships in SQL Server?

Relationships are used in SQL Server to link columns of different tables. These are of three types:

One-to-One – A single column in a table has one dependent column in some other table.
One-to-Many/Many-to-one – A single column in a table has more than one dependent column in the other table (One-to-many). More than one column in a table has a single dependent column in the other table (Many-to-one).
Many-to-Many – Multiple columns in a table have multiple dependent columns in some other table.

16. What are the various encryption mechanisms in the SQL Server?

Answer: SQL Server provides support for a range of encryption mechanisms to safeguard data. These are:

Asymmetric keys
Certificates
Symmetric keys
Transact-SQL functions
Transparent Data Encryption

17. How can you create a login?

You can use the following command to create a login

CREATE LOGIN MyLogin WITH PASSWORD = '123';

18. What is the ISNULL() operator?

ISNULL function is used to check whether the value is given is NULL or not NULL in the SQL server. This function also provides to replace a value with the NULL. This is most Important SQL Server Interview Questions

19. What is the use of FOR Clause?

FOR clause is mainly used for XML and browser options. This clause is mainly used to display the query results in XML format or in the browser.

20. What will be the maximum number of indexes per table?

For SQL Server 2008 100 Index can be used as the maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

1000 Index can be used as the maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

21. What is the difference between UNION and UNION ALL?

UNION: To select related information from two tables UNION command is used. It is similar to the JOIN command.
UNION All: The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. It will not remove duplicate rows, instead, it will retrieve all rows from all tables.

22. What is the primary key of a database?

A table column with this constraint is called the key column for the table. This constraint helps the table to make sure that the value is not repeated and also that there are no null entries.

Now, this column does not allow null values and duplicate values. You can try inserting values to violate these conditions and see what happens. A table can have only one Primary key. Multiple columns can participate in the primary key.

23. What is a foreign key of a database?

To define the relationship between two tables (one is called the parent and the other one is the child table) connected by columns, a foreign key constraint is used. In this constraint, the values of the child table must appear in the parent table, which means that for a foreign key, one table should point to a Primary Key in another table. A table can have multiple foreign keys and each foreign key can have a different referenced table.

24. What are the different types of joins in SQL Server?

Joins are useful for bringing data together from different tables based on their database relations. First, we will see how the join operates between tables. Then, we will explore the Order of Execution when both a join and condition exist. Finally, we will move our exploration to the importance of the Join order.

A Join condition defines the way two tables are related in a query by:

Specifying the column to be used for the Join from each table. In joining foreign keys in a table and its associated key in the other table.
To use the logical operator in comparing values from the columns.

There are three types of joins available based on the way we join columns of two different tables.

Full Join
Inner Join
Left outer join
Right outer join

Check out the Latest Jobs for SQL Developer: Link

Join WhatsApp and Telegram groups for daily job updates: Link

Prepare for Technical Round with Interview Questions: Link

Get insights into HR Interview Questions: Link

Craft a standout resume for Quick shortlisting: Link

Mock Test with Aptitude and Coding Assessment: Click Here

Understand Colgate Recruitment Process, Test, and Exam Pattern: Link

Explore Global Opportunities for Professionals & Freshers Abroad: Link

TCS NQT 2023 Careers: Graduate Trainee Hiring: Link

PwC Internships 2023: Opportunities in Zurich, Switzerland: Link

Chandrayaan 3: India’s Upcoming Lunar Mission Expectations: Link

Tableau Interview Questions And Answers | Tableau Developer Interview Questions