Sql server interview questions and answers-Part-1

QUESTION - Define SQL Server.
ANSWER - SQL server is an RDBMS that uses Transact-SQL to interact with the client application. It includes database, database engine and the application that are used to manage data. Data is organizes into a table with rows and columns.

QUESTION - Define Database.
ANSWER - A database stores data and it is just like a data file. Database systems are more powerful than data files because the data is more highly organized. In a well-designed database, there is no scope for duplicate pieces of data. Related pieces of data are grouped together in a single structure or record.

QUESTION - What is denormalization? 
ANSWER - Denormalization is the reverse process of normalization.
It is controlled introduction of redundancy to the database design.
Database design is denormalised to improve the query performance.
It is done to reduce number of complex joins in the query. 

QUESTION - How do you implement one-to-one in SQL Server? 
ANSWER - One to one is implemented using single table by establishing relationship between same type of columns in a table.  

QUESTION - How do you implement one-to-many in SQL Server?
ANSWER - Implemented using two tables with primary key and foreign key relationships.

 

QUESTION - How do you implement many-to-many SQL Server?

ANSWER - Implemented using a junction table.

The keys from both the tables form composite primary key of the junction table.

 

QUESTION - What are User Defined Datatypes?

ANSWER - It extends base type datatypes functionality.

It is create when particular datatype format has to be used across many tables in the database.

If a column called EmpId appears in many tables of int type, create EmpId  of int and use it across all your tables. 

  

QUESTION - Difference between a primary key and a unique key.

ANSWER - Both  enforce uniqueness of the column.

By default primary key creates a clustered index. 

Unique creates a nonclustered index by default. 

Primary key doesn't allow NULLs. 

Unique key allows NULL.
 

QUESTION - What is bit datatype?          

ANSWER - Bit datatype is used to store boolean information like 1 or 0 (true or false).          

Bit datatype can represent a Null state.
 

QUESTION - Define candidate key, alternate key, composite key.         

ANSWER - Candidate key: A column or a set of columns can be called as candidate key if they i dentify each row of a table uniquely. A table can have multiple candidate keys. One of them is specified as Primary key and rest of them can be called as alternate key. 

Alternate key: There can be more than one keys which can identify each row of the table uniquely. One of them is defined as primary key and rest of them is called alternate keys of the table. 

Composite Key:A key formed by combining at least two or more columns.      
 

QUESTION - Define composite key.          

ANSWER - A key formed by combining at least two or more columns.

 

QUESTION - Define default constraint? 

ANSWER - Default constraint is used to fill column with default value defined during creation of table if nothing is supplied while inserting data.          

IDENTITY columns and timestamp columns can't be associated with default constraint.