SQL Server Interview Questions

1) What is normalization? Explain different levels of normalization?

It is the way to eliminate redundant data

1. Reduces null value
2. Enables efficient indexing
3. 1NF — Removes duplicated attributes, Attribute data should be atomic, and attribute should be the same kind.
4. 2NF — This should be in 1NF and each non-key is fully dependent on the primary key.
5. 3NF — This should be in 2NF and all the non-key attributes which are not dependent on the primary key should be removed. All the attributes which are dependent on the other non-key attributes should also be removed. Normalization is done in. know more at SQL Server dba online training

2) What is denormalization and when would you go for it?

It is the reverse process of normalization. It increases query performance by reducing the joins. It is used for OLAP applications.

3) How do you implement one-to-one, one-to-many, and many-to-many relationships while designing tables?

Relationships in SQL server are explained below

1. One to One –It can be implemented as a single table. Rarely it is implemented in two tables. For each instance, in the first entity, there is one and only one in the second entity and vice versa.
2. One to Many –For each instance, in the first entity, there can be one or more in the second entity. For each instance, in the second entity, there can be one and only one instance in the first entity.
3. Many to Many –For each instance, in the first entity there can be one or more instances in the second entity, and for each instance, in the second entity there can be one or more instances in the first entity.

4) What is the difference between the Primary key and the Unique key.

Primary Key

• 1.Enforces uniqueness of the column in a table
• 2.Default clustered index
• 3.Does do not Allow nulls

Unique Key

• 1. Enforces the uniqueness of the column in a table.
• 2.Default non-clustered index.
• 3.Allows one null value

5) Define the following keys:

Candidate key, Alternate key, Composite key.

• 1.Candidate key –Key which can uniquely identify a row in the table.
• 2. Alternate key –If the table has more than one candidate key and when one becomes a primary key the rest becomes alternate keys.
• 3. Composite key –More than one key uniquely identifies a row in a table.

6) What are defaults? Is there a column to which a default can’t be bound?

1. It is a value that will be used by a column if no value is supplied to that column while inserting data. know more at SQL Server dba training
2. I can’t be assigned for identity and timestamp values.

7) What are user-defined data types and when you should go for them?

Lets you extend the base data types by providing a descriptive name and format to the database

E.g. Flight_num appears in many tables and all these tables have varchar(8)
Create a user-defined data-type

8) What is a transaction and what are ACID properties?

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the properties of a transaction.

9) What part does database design have to play in the performance of a SQL Server-based application?

It plays a very major part. When building a new system, or adding to an existing system, the design must be correct. Ensuring that the correct data is captured and is placed in the appropriate tables, that the right relationships exist between the tables, and that data redundancy is eliminated is an ultimate goal when considering performance. Planning a design should be an iterative process, and constantly reviewed as an application is developed. know more at SQL server dba online course

It is rare, although it should be the point that everyone tries to achieve, when the initial design and system goals are not altered, no matter how slightly. Therefore, a designer has to be on top of this and ensure that the design of the database remains efficient.

10) What can a developer do during the logical and physical design of a database to help ensure that their database and SQL Server-based application will perform well?

A developer must investigate volumes of data (capacity planning), what types of information will be stored, and how that data will be accessed. If you are dealing with an upgrade to an existing system, analyzing the present data and where existing data volumes occur, how that data is accessed and where the current response bottlenecks are occurring, can help you search for problem areas in the design.

A new system would require a thorough investigation of what data will be captured, and looking at volumes of data held in other formats also will aid design. Knowing your data is just as important as knowing the constituents of your data. Also, constantly revisit your design. As your system is built, check relationships, volumes of data, and indexes to ensure that the physical design is still at its optimum. Always be ready to check your system by using tools like the SQL Server Profiler.

11) What are the main steps in Data Modeling?

1.Logical — Planning, Analysis, and Design

2.Physical — Design, Implementation, and Maintenance

12) What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row processing of the result sets.

Types of cursors:

Static — Makes a temporary copy of the data and stores it in tempdb and any modifications on the base table do not reflect in data returned by fetches made by the cursor.
Dynamic — Reflects all changes in the base table.
Forward-only — specifies that the cursor can only fetch sequentially from first to last.
Keyset-driven — Keyset is the set of keys that uniquely identifies a row is built in a tempdb.

Disadvantages of cursors:

Each time you fetch a row from the cursor, it results in a network roundtrip, whereas a normal SELECT query makes only one roundtrip, however large the result set is. Cursors are also costly because they require more resources and temporary storage (which results in more IO operations). Further, there are restrictions on the SELECT statements that can be used with some types of cursors. know more at SQL Server dba training course
Most of the time set-based operations can be used instead of cursors.
Here is an example:
If you have to give a flat hike to your employees using the following criteria:

13) Write down the general syntax for a SELECT statement covering all the options.

Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax).

14) What is a Join? Explain Different Types of Joins

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS, and FULL OUTER JOINS.

15) Can you have a nested transaction?

Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN, and @@TRANCOUNT

16) What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?

An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement.
Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using the sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy.

17) What is the system function to get the current user’s user id?

USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

18) What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?

Triggers are special kinds of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE, and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there’s no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder.

Triggers can’t be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined. Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also — INSTEAD OF triggers.

Virtual tables — Inserted and Deleted from the basis of trigger architecture.

19) What is a self join? Explain it with an example.

Self-join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join. know more at SQL Server dba online training in Hyderabad

20) How can I enforce using of a particular index?

You can use index hint (index=index_name) after the table name. SELECT au_lname FROM authors (index=aunmind)

21) What is ORDER BY and how is it different than clustered index?

The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes. This will happen by the time when we retrieve data from the database. Clustered indexes will physically sort data while inserting/updating the table.