SQL Server-Interview Questions

No.of Views1297
Bookmarked0
By Pankaj Kumar Gupta  On 24 Mar 2011
emailbookmarkadd commentsprint
 

1. What is Database Normalization?

In relational database theory, normalization is the process of restructuring the logical data model of a database to eliminate redundancy, organize data efficiently, and reduce repeating data and to reduce the potential for anomalies during data operations. Data normalization also may improve data consistency and simplify future extension of the logical data model. The formal classifications used for describing a relational database's level of normalization are called normal forms (abbrev. NF).

The transformation of conceptual model to computer representation format is known as Normalization.
       
The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). Here I am discussing 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely used.

1). First Normal Form (1NF) - sets the very basic rules for an organized database

  • Eliminate duplicative columns from the same table.
  • Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key)

2). Second Normal Form (2NF) - further addresses the concept of removing duplicative data

  • Meet all the requirements of the first normal form.
  • Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
  • Create relationships between these new tables and their predecessors through the use of foreign keys.

3). Third Normal Form (3NF) - goes one large step further

  • Meet all the requirements of the second normal form.
  • Remove columns that are not dependent upon the primary key.

4). Fourth Normal Form (4NF) - has one additional requirement

  • Meet all the requirements of the third normal form.
  • A relation is in 4NF if it has no multi-valued dependencies.

2. What is denormalization and when we go for it?

As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

3. What are indexes and their types?

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

Types of indexes: Indexes are of two types.

1). Clustered Indexes.

2). Non-Clustered Indexes.

When you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table.

Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

4. What is the difference between a Primary Key and a Unique Key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

5. What are Candidate Key, Alternate Key, and Composite Key?

A candidate key is one that can identify each row of a table uniquely.

Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called composite key

6. What are Stored Procedures?

Stored procedures in Microsoft SQL Server are similar to procedures in other programming languages in that they can:
       
Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
   
Contain programming statements that perform operations in the database, including calling other procedures.
Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

You can use the Transact-SQL EXECUTE statement to run a stored procedure.

7. What are the benefits of using the stored procedures?

The benefits of using stored procedures in SQL Server rather than Transact-SQL programs stored locally on client computers are:

  • They are registered at the server.
  • They can have security attributes (such as permissions) and ownership chaining, and certificates can be attached to them.
  • They can enhance the security of your application.
  • Parameterized stored procedures can help protect your application from SQL Injection attacks.
  • They allow modular programming.
  • You can create the procedure once, and call it any number of times in your program. This can improve the maintainability of your application and allow applications to access the database in a uniform manner.
  • They are named code allowing for delayed binding.
  • This provides a level of indirection for easy code evolution.
  • They can reduce network traffic

8. What is Trigger?

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. There are types of triggers: DML trigger and DDL trigger. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations. DML and DDL triggers can be created in the SQL Server 2005 Database Engine directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server. SQL Server allows for creating multiple triggers for any specific statement..

Example

CREATE TRIGGER trigger_name 
ON { table | view } 
[ WITH ENCRYPTION ] 
{ 
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } 
        [ WITH APPEND ] 
        [ NOT FOR REPLICATION ] 
        AS 
        [ { IF UPDATE ( column ) 
            [ { AND | OR } UPDATE ( column ) ] 
                [ ...n ] 
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) 
                { comparison_operator } column_bitmask [ ...n ] 
        } ] 
        sql_statement [ ...n ] 
    } 
}

Can we fire a trigger from c# program?

No, it is not possible.

Can we fire a trigger from stored procedure?

Yes, it is possible.

9. What is a transaction and how we use it?

A transaction is a logical unit of work in which, all the steps must be performed or none. Each transaction has four properties known as ACID (Atomicity, Consistency, Isolation, and Durability).

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.

Users can group two or more Transact-SQL statements into a single transaction using the following statements-

  1. Begin Transaction
  2. Rollback Transaction
  3. Commit Transaction

Example

USE pubs

DECLARE @intErrorCode INT

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END

10. What do you mean by Disconnected Database Architecture?

Microsoft designed ADO.NET to be able to handle the disconnected computing scenario required by Web-based applications. This disconnected design enables ADO.NET to be readily scalable for enterprise applications because an open connection isn’t maintained between each client system and the database. Instead, when a client connection is initiated, a connection to the database is briefly opened, the requested data is retrieved from the database server, and the connection is closed. The client application then uses the data completely independently from the data store maintained by the database server. The client application can navigate through its subset of the data, as well as make changes to the data, and the data remains cached at the client until the application indicates that it needs to post any changes back to the database server. At that point, a new connection is briefly opened to the server and all of the changes made by the client application are posted to the database in an update batch and the connection is closed.

The DataSet is the core component of the disconnected architecture of ADO.NET. The DataSet is explicitly designed for data access independent of any data source.

The other core element of the ADO.NET architecture is the .NET data providers, whose components are explicitly designed for data manipulation and fast, forward-only, read-only access to data. The Connection object provides connectivity to a data source. The Command object enables access to database commands to return data, modify data, run stored procedures, and sends or retrieves parameter information. The DataReader provides a high-performance stream of data from the data source. Finally, the DataAdapter provides the bridge between the DataSet object and the data source. The DataAdapter uses Command objects to execute SQL commands at the data source to both load the DataSet with data, and reconcile changes made to the data in the DataSet back to the data source. You can write .NET data providers for any data source. The .NET Framework ships with two .NET data providers: the SQL Server .NET Data Provider and the OLE DB .NET Data Provider.

Image Loading

11. How can we improve SQL performance?

To improve the SQL performance we can consider the following factors-

  1. Every index increases the time it takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.
  2. Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.
  3. Try to create indexes on columns that have integer values rather than character values.
  4. If you create a composite (multi-column) index, the orders of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the left most of the key.
  5. If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.
  6. Create surrogate integer primary key (identity for example) if your table will not have many insert operations.
  7. Clustered indexes are more preferable than non-clustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY.
  8. If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.
  9. You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.

12. What is Connection Pooling?

When a connection is opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created.

In the following example, three new SqlConnection objects are created, but only two connection pools are required to manage them. Note that the first and second connection strings differ by the value assigned for Initial Catalog.

SqlConnection conn = new SqlConnection();
	conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";
	conn.Open();      
	// Pool A is created.
	SqlConnection conn = new SqlConnection();
	conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=pubs";
	conn.Open();      
	// Pool B is created because the connection strings differ.
	SqlConnection conn = new SqlConnection();
	conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";
	conn.Open();      
	// The connection string matches pool A.

Once created, connection pools are not destroyed until the active process ends. Maintenance of inactive or empty pools involves minimal system overhead.

13. What are the basic principles of performance tuning?

  • Manage RAM caching
  • Create and maintain good indexes.
  • Partition large data sets and indexes
  • Monitor disk I/O subsystem performance.
  • Tune applications and queries.
  • Optimize active data.

14. What are User Defined Functions (UDFs)?

User Defined Functions are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table.

Advantages:

  • They can be used in a Select, Where, or Case statement
  • They also can be used to create joins.
  • User Defined Functions are simpler to invoke than Stored Procedures from inside another SQL statement.

Disadvantages

  • User Defined Functions cannot be used to modify base table information.
  • The DML statements INSERT, UPDATE, and DELETE cannot be used on base tables.
  • Another disadvantage is that SQL functions that return non-deterministic values are not allowed to be called from inside User Defined   Functions. GETDATE is an example of a non-deterministic function. Every time the function is called, a different value is returned. Therefore, GETDATE cannot be called from inside a UDF you create.
CREATE FUNCTION dbo.multi_test(@FirstLetter char(1)) RETURNS @Result TABLE 
( 
          fname varchar(20), 
          hire_date datetime, 
          on_probation char(1) 
)
AS
   BEGIN  
               INSERT INTO @Result(fname, hire_date) 
       	       SELECT fname, hire_date FROM employee WHERE LEFT(fname, 1) =  @FirstLetter 
               UPDATE @Result SET on_probation = 'N'  
               UPDATE @Result SET on_probation = 'Y'  WHERE hire_date < '01/01/1991'            
               RETURN
   END 

To use the new function, execute:     SELECT * FROM dbo.multi_test('A')

15. What is View and how we create it?

A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database.

Views ensure the security of data by restricting access to the following data-

  • Specific rows of the tables
  • Specific columns of the tables
  • Specific rows and columns of the tables
  • Rows fetched by using joins.
  • Statistical summary of data in a given tables.
  • Subsets of another view or a subset of views and tables

Creating Views: A view can be created by using the CREATE VIEW statement.
Example

CREATE VIEW view_name
[(column_name[,column_name]….)]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]

 

 

 
 
About Author
 
Pankaj Kumar Gupta
Occupation-Software Engineer
Company-Miri Infotech (P) Ltd, India
Member Type-Junior
Location-India
Joined date-27 Oct 2010
Home Page-www.codegain.com
Blog Page-codegain.com
- I am working with Miri Infotech (P) Ltd, India as Senior Software Engineer. - Having 5+ years experience in .NET Technologies (ASP.NET, C#.net, VB.net, SQL Server, XML, Web Services, MS Dynamics CRM, SharePoint). - I have masters M.Sc-Computer Science, M.Tech-Information Technology with Honours. - I have completed MCP, MCSD.NET, MCAD, MCDBA SQL Server, MCTS, MCPD-EAD - Microsoft Certified Professional & Technology Specialist - Obsessed in OOP, MVC, MVP style design and programming. - Designing and developing the client/server applications for a number of doimains. - Designing and implementing Business Planning Tools & Applications. - Good understanding of formal software engineering tools & technologies.
 
 
Other popular Interview Questions On Sql Servers
^ Scroll to Top