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.
QUESTION - What is a transaction and what are ACID properties?
ANSWER - Transaction encapsulates SQL commands and work as a single unit.
All the SQL statements defined in the transaction should work successfully.
Every transaction follow tranaction properties ACID i.eAtomicity, Consistency, Isolation, Durability to qualify as valid transaction.
QUESTION - Explain different isolation levels defined in SQL Sever.
ANSWER - Isolation levels determine the degree of isolation of data during concurrent access.
Read Uncommitted, Read Committed, Repeatable Read, Serializable are the different isolation levels defined in SQL Server.
The default SQL Server isolation level is Read Committed.
SET TRANSACTION ISOLATION LEVEL allows to define the isolation level at the connection level.
QUESTION - What is lock escalation?
ANSWER - Lock escalation is the process of converting low level locks (row locks, page locks) into higher level locks (table locks).
Each lock is a memory structure. Too many locks mean more memory being occupied by locks.
SQL Server escalates the many fine-grain locks to fewer coarse-grain locks.
QUESTION - Define SQL Profiler.
ANSWER - SQL Profiler is used to diagnose a problem by capturing events in a trace file.
It is useful to step through queries to find the reason of slow performance.
You can monitor SQL server performance in order to tune workload.
SQL profiler also supports auditing of actions on the server.
QUESTION - Explain SQL Server Service Manager.
ANSWER - SQL Server Service Manager is used to start, stop, and pause SQL Server services that exist as the separate components on the server.
The service components are as follows:
SQL Server service
SQL Server Agent service
Microsoft Search service
MSDTC service
MSSQLServerOLAPService service
No comments:
Post a Comment