SQL Server Interview Questions    

Personal Interview - Answers to frequently asked questions Tips to earn promotions and perks 8 things you should NOT do in a Group Discussion How to write a good CV? How to write a good Cover Letter? Tips to become boss's favorite Soft skills every professional must have

SQL Server Interview Questions & Answers part 5

---

<<Previous  Next>>

By Nishant Kumar

Part 1 | Part 2 | Part 3 | Part 4 | part 5 | part 6 | part 7 | part 8 | part 9  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 QUESTION - What is SQL Query Analyzer?           ANSWER - SQL Query Analyzer is a tool that are used for various purposes such as Creating and executing queries. Creating database objects. Executing stored procedure. Analyzing query performance. Inserting, updating, or deleting rows in a table   QUESTION - Define DTS service in SQL Server.           ANSWER -DTS, Data Transformation Services is used to import and export data between heterogeneous data source. A DTS package can not only transfer data from a table to another, but can also specify query as source of data.   QUESTION - What is Replication in SQL Server?           ANSWER -Replication allows creating copies of data in separate databases along with keeping all databases synchronized by replicating modification in one copy to all the copies. Data can be effectively distributed over network using replication.   Question - Define Bulk Copying and Distributed Queries of SQL Server. Answer - Bulk copying is used to transfer large amount of data. Distributed Queries in SQL server are used to reference heterogeneous data source such as a Microsoft Access database or Oracle database.   Question - Define SQL Server Agent. Answer - SQL server agent is important service of the server where instances of SQL server are running. The agent is responsible to run tasks that are scheduled to occur at specific times or intervals. The agent is also responsible to run replication task set by the administrators. Part 1 | Part 2 | Part 3 | Part 4 | part 5 | part 6 | part 7 | part 8 | part 9 <<Previous  Next>>