Microsoft SQL Server Interview Questions

SQL Server Interview Questions has lots of latest and important SQL interview questions which consist of not only theoretical explanations but most essentially its practical implementations.

This book is an indispensable resource for Microsoft .Net Developers. It will serve as a useful resource to anyone who faces interviews in SQL (developers of any platform - JAVA, C++, etc), as well as Database Administrators.

The list of topics included in the very first edition of the E-Book is as given below:

• Differentiate between SQL Server 2008 and SQL Server 2012
• Differentiate between Data Definition Language (DDL) and Data Manipulation Language (DML)
• What are the six main categories of SQL commands?
• What are unique identifiers in SQL Server?
• What is Null Constraint?
• What is check constraint in SQL SERVER?
• What is default constraint?
• What is a database key?
• Explain the different types of keys in SQL
• Differentiate between Primary Key and Unique Key
• Differentiate between Primary Key and Candidate Key
• Differentiate between Primary Key and Foreign Key
• What is Identity Column in Table?
• How can you insert explicit values in an identity column of a table?
• What is the syntax to check current identity of a table?
• Can we change identity key values for a table or reset the identity key value?
• What is the maximum allowable size of a row in SQL SERVER?
• Explain the Sparse Columns in SQL Server 2008
• Describe the characteristics of Sparse Columns
• List down the limitations for using SPARSE Columns
• How a column is changed from sparse to nonsparse or nonsparse to sparse?
• What are the data types which cannot be specified as SPARSE?
• Explain the COLUMNS_UPDATED function and its relation to Sparse Column
• What are the different types of data types in SQL Server?
• What is the use of different types of integer data types in SQL?
• What is times stamp data type is SQL Server?
• How can you generate duplicate rowversion values?
• Explain Where Clause?
• What is select statement in TSQL?
• Explain the Logical Processing Order of the SELECT statement
• What is the Join in SQL Server? What are the different types of join?
• What is the basic difference in joins in SQL Server 2000 and 2005?
• What is a view in SQL server?
• What is common table expression (CTE)?
• When should you use a Common Table Expression?
• Define the Structure of a CTE with example
• Explain recursive Common Table Expressions
• What is a table variable?
• Differentiate between local and global temporary tables in SQL
• Explain the Ranking Functions in SQL in detail
• What is stored procedure?
• Is nesting possible in stored procedure? If yes how many number of times?
• What is the maximum amount of parameters a Stored Procedure can have?
• How can you improve performance of a Stored Procedure?
• Differentiate between a Stored Procedure and a User-defined Function
• Differentiate between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT in SQL
• Explain the Inclusive nature of BETWEEN operator in SQL
• Differentiate between DELETE and TRUNCATE commands in SQL
• Explain Indexes in SQL Server
• What are the data types on which indexes cannot be created?
• Explain the Index Structures in SQL Server
• Explain the Index Types in SQL Server
• Explain the circumstances when Indexes should be avoided / used
• What is Normalization?
• Explain the various Normal forms
• What is a SQL Transaction?
• What are the SQL Server transaction modes?
• Explain the properties of transactions.
• What are the four Transactional Control Commands (TCC)?
• What is a Shadow table?
• How do you create a Shadow table in SQL?
• What are the drawbacks of a SQL Cursor?
• Write a SQL query to list all the tables in a database.
• Write a SQL query to list all the stored procedures in a database.
• Write a SQL query to list all the user defined functions in a database.
• Write a SQL query to list all the views in a database.
• Write a SQL query to list all the constraints in a database.
• Write a SQL query to list all the indexes created for a particular table (say common_user).
• Explain PIVOT and UNPIVOT operators in SQL
• What is an Inline View?
• Explain the SQL CASE expression in detail.
• How do you tune SQL Indexes for better performance?
• How do you tune SQL queries for better performance?
• Explain the use of FOR XML in SQL Queries
• What is the use of TYPE directive in FOR XML queries?
• What is the use of ROOT directive in FOR XML queries?
• Frequently asked SQL Queries in interviews

SQL Server Interview Questions E-Book is now available for sale.

INSTANT DOWNLOAD AFTER FULL PAYMENT

Details of the EBook

Name: SQL Server Interview Questions

Author: Chandan Sinha

Format: PDF

Language: English

Price: $2.99 (Exclusively on this website)

(Limited Period Offer - Rates slashed from $3.99 to $2.99)
Focus Area: Technical Interview in Microsoft SQL Server

SQL Server Interview Questions

[Kindle Edition]

Ebook Link on Amazon Kindle - http://www.amazon.com/gp/product/B00ONQGUF6

PIVOT - UNPIVOT operator

Explain PIVOT and UNPIVOT operators in SQL

PIVOT and UNPIVOT are relational operators which were introduced in SQL Server 2005. These operators allow easy transformation of table-valued expression into another table.

PIVOT allows you to rotate rows into columns during the execution of an SQL Query. It performs an aggregation, and merges possible multiple rows into a single row in the output.

UNPIVOT does almost the opposite of PIVOT operator by rotating columns into rows, but it is not exactly opposite. PIVOT merges multiple rows into single while UNPIVOT fails to reproduce the original table due to the merged rows. Null input values to UNPIVOT disappear in its output.

A simple example of PIVOT operator

Suppose we have a dbo.currency_rate table which consists of three columns - in_currency_id, mn_rate and dt_currency.

-- Create table

CREATE TABLE dbo.currency_rate(in_currency_id int, mn_rate money, dt_currency datetime)

-- Insert data in table

INSERT INTO dbo.currency_rate VALUES (1, 60.15, '2015-06-15 09:59:16.640')

INSERT INTO dbo.currency_rate VALUES (2, 35.57, '2015-06-15 09:59:16.640')

INSERT INTO dbo.currency_rate VALUES (3, 78.90, '2015-06-15 09:59:16.640')

INSERT INTO dbo.currency_rate VALUES (4, 87.15, '2015-06-15 09:59:16.640')

INSERT INTO dbo.currency_rate VALUES (1, 10.18, '2015-06-16 09:59:16.640')

INSERT INTO dbo.currency_rate VALUES (2, 11.25, '2015-06-16 09:59:16.640')

INSERT INTO dbo.currency_rate VALUES (3, 08.48, '2015-06-16 09:59:16.640')

INSERT INTO dbo.currency_rate VALUES (4, 18.31, '2015-06-16 09:59:16.640')

-- Select and confirm the data before any operation

SELECT * FROM dbo.currency_rate

in_currency_id mn_rate dt_currency

1 60.15 2015-06-15 09:59:16.640

2 35.57 2015-06-15 09:59:16.640

3 78.90 2015-06-15 09:59:16.640

4 87.15 2015-06-15 09:59:16.640

1 10.18 2015-06-16 09:59:16.640

2 11.25 2015-06-16 09:59:16.640

3 8.48 2015-06-16 09:59:16.640

4 18.31 2015-06-16 09:59:16.640

Use PIVOT operator to transform rows into columns:

-- Pivot table with two rows and five columns

SELECT *

FROM

[dbo].[currency_rate]

PIVOT

(

AVG(mn_rate)

FOR [in_currency_id] IN ([1], [2], [3], [4])

) AS pivot_table;

Output:

dt_currency 1 2 3 4

2015-06-15 09:59:16.640 60.15 35.57 78.90 87.15

2015-06-16 09:59:16.640 10.18 11.25 8.48 18.31

A simple example of UNPIVOT operator

In continuation with the PIVOT operator example, save the result of the PIVOT operator in a temporary table:

SELECT *

INTO #tmp_pivot_table

FROM

[dbo].[currency_rate]

PIVOT

(

AVG(mn_rate)

FOR [in_currency_id] IN ([1], [2], [3], [4])

) AS pivot_table;

Use UNPIVOT operator to transform rows into columns:

SELECT [dt_currency], [in_currency_id], [mn_rate]

FROM #tmp_pivot_table

UNPIVOT

(

mn_rate

FOR in_currency_id IN ([1], [2], [3], [4])

) AS unpivot_table

Output:

dt_currency in_currency_id mn_rate

2015-06-15 09:59:16.640 1 60.15

2015-06-15 09:59:16.640 2 35.57

2015-06-15 09:59:16.640 3 78.90

2015-06-15 09:59:16.640 4 87.15

2015-06-16 09:59:16.640 1 10.18

2015-06-16 09:59:16.640 2 11.25

2015-06-16 09:59:16.640 3 8.48

2015-06-16 09:59:16.640 4 18.31

.Net Framework - Quick Reference Guide

The .Net Framework E-Book by the .Net Interview Cracker is a very useful resource for developers and serves well as a quick reference guide. This E-Book prepares them for technical interviews in Microsoft .Net Framework..

The list of topics included in the very first edition of the E-Book is as given below:

• What is .NET Framework?
• Explain the architecture of .Net Framework
• Common Language Runtime (CLR)
• Features of the Common Language Runtime
• Dynamic Language Runtime
• Common Language Infrastructure (CLI)
• What is Intermediate Language (IL)?
• How is a C# program compiled to a processor specific native code?
• Explain the services provided by the .Net Framework
• Assembly
• Properties of an Assembly
• Components of an Assembly
• What is an Assembly Manifest?
• Functions of the Assembly Manifest
• Contents of the Assembly Manifest
• Differentiate between Single-file assembly and Multifile assembly
• What are the different types of assemblies in .NET
• Friend Assemblies
• Differentiate between Static and Dynamic Assemblies
• Hub-and-Spoke model & Satellite Assemblies
• What is DLL Hell?
• Application Domains
• Garbage Collection
• Garbage Collection in detail
• Conditions for a garbage collection
• Benefits provided by Garbage Collection
• Is the execution of the Garbage Collector non-deterministic? If yes, why?
• How can you instruct the garbage collector to collect unreferenced data?
• How can you force Garbage Collection?
• Global Assembly Cache (GAC)
• Mention the demerits of installation of an assembly in the GAC
• Mention the benefits of installation of an assembly in the GAC
• Mention the ways to deploy an assembly into the GAC
• Where is the GAC located?
• Enumerate the Steps for creating a Shared Assembly

The .Net Framework E-Book is now available for sale.

INSTANT DOWNLOAD AFTER FULL PAYMENT

Details of the EBook

Name: .Net Framework - Quick Reference Guide

Author: Chandan Sinha

Format: PDF

Language: English

Price: $2.99 (Exclusively on this website)

(Limited Period Offer - Rates slashed from $3.99 to $2.99)
Focus Area: Technical Interview in Microsoft .Net

.Net Framework - Quick Reference Guide [Kindle Edition]

Ebook Link on Amazon Kindle - http://www.amazon.in/gp/product/B00KKD2PPA

ASP.NET Page Life Cycle

When an ASP.NET application is executed, the web page goes through a life cycle in which it performs a series of processing steps. When a web page is requested, it is loaded into the Web Server memory, processed and sent to the web browser. Then it (the object) is unloaded from the memory. At each of these steps, methods and events are available, ready to be overridden according to the need of the application.

The Page class creates a control tree of all the controls on the page. All the components on the page, except the directives are part of this control tree. 

ASP.NET Page Life Cycle Phases

• Initialization
  
• Instantiation of controls on the web page
  
• Restoration and Maintenance of state
  
• Execution of the Event Handler code
  
• Page Rendering
  

ASP.NET Page Life Cycle Stages

• Page Request – The ASP.NET page life cycle (PLC) begins after the page is requested by a user. ASP.NET then determines whether the web page needs to be parsed and compiled, or a cached version of the page can be sent in response without running the page.
  
• Start – The page properties such as Request and Response are set at the Start stage. It is determined whether the request is a postback (old request) or a new request and IsPostBack property of the Page is set accordingly. The UICulture property of the Page is also set at this stage.
  
  Events
  
  a) PreInit – Raised after the Start stage is complete and before the Initialization stage begins 
• Initialization – The controls on the web page are available during page initialization, and UniqueID property of each control is set. If applicable, master page and themes are also applied to the page during initialization. If the current request is a postback, the postback data has not yet been loaded and control property values have not been restored to the values from view state.
  
  Events
  
  a) Init – Raised after all controls have been initialized and any skin settings have been applied. The Init event of individual controls occurs before the Init event of the page.

b) InitComplete – Raised at the end of the page's initialization stage. Only one operation takes place between the Init and InitComplete events: tracking of view state changes is turned on.
View state tracking enables controls to persist any values that are programmatically added to the ViewState collection. Until view state tracking is turned on, any values added to view state are lost across postbacks. Controls typically turn on view state tracking immediately after they raise their Init event.

• Load – During load, if the current request is a postback, control properties are loaded with information recovered from view state and control state.
  
  Events
  
  a) PreLoad – Raised after the page loads view state for itself and all controls, and after it processes postback data that is included with the Request instance. 
   
  b) Load – The Page object calls the OnLoad method on the Page object, and then recursively does the same for each child control until the page and all controls are loaded. The Load event of individual controls occurs after the Load event of the page.
• Postback event handling – If the request is a postback, the event handlers of the controls are called. After that, the Validate method of all validator controls is called, which sets the IsValid property of individual validator controls and of the page. (There is an exception to this sequence: the handler for the event that caused validation is called after validation.)
  
  Events
  
  a) Control Events – These events should be used to handle specific control events, such as a Button control's Click event or a TextBox control's TextChanged event.
  In a postback request, if the page contains validator controls, check the IsValid property of the Page and of individual validation controls before performing any processing.
  
  b) Load Complete – Raised at the end of the event-handling stage.
  
  c) PreRender – Raised after the Page object has created all controls that are required in order to render the page, including child controls of composite controls. To do this, the Page object calls EnsureChildControls for each control and for the page.
  The Page object raises the PreRender event on the Page object, and then recursively does the same for each child control. The PreRender event of individual controls occurs after the PreRender event of the page.
  
  d) PreRender Complete – Raised after each data bound control whose DataSourceID property is set calls its DataBind method.
  
  e) SaveStateComplete - Raised after view state and control state have been saved for the page and for all controls. Any changes to the page or controls at this point affect rendering, but the changes will not be retrieved on the next postback.
• Rendering – The view state is saved for the page and all controls before rendering. During the rendering stage, the page calls the Render method for each control, providing a text writer that writes its output to the OutputStream object of the page's Response property.
  
  Events
  
  a) Render - This is not an event; instead, at this stage of processing, the Page object calls this method on each control. All ASP.NET Web server controls have a Render method that writes out the control's markup to send to the browser. A user control (an .ascx file) automatically incorporates rendering, so you do not need to explicitly render the control in code. 
   
• Unload - The Unload event is raised after the page has been fully rendered, sent to the client, and is ready to be discarded. At this point, page properties such as Response and Request are unloaded and cleanup is performed.
  
  Events
  
  a) Unload - Raised for each control and then for the page. During the unload stage, the page and its controls have been rendered, so you cannot make further changes to the response stream. If you attempt to call a method such as the Response.Write method, the page will throw an exception

ASP.NET HttpHandlers – Are they really required?

ASP.NET HttpHandler is a process that runs in response to a request that is made to an ASP.NET Web application. Almost everything we do in an HttpHandler, can be done in a normal .aspx page. So, why do we need HttpHandlers? Are they really required?

Given below is the explanation of why HttpHandlers are really required:

1. Reusability & Portability – HttpHandlers are more reusable and portable than normal .aspx pages. Since there are no visual elements in an HttpHandler, they can be easily placed into their own assembly and reused from project to project.
2. HttpHandlers are relatively less expensive than the PageHandler. A page goes through a set of events to be processed at the server (such as onInit, onLoad, etc.), ViewState and postbacks or simply the complete Page Life Cycle. When you really have nothing to do with the complete page life cycle (like displaying images), HttpHandlers are very useful.

Handlers in ASP.NET

Handler is an agent of communication or a medium which is responsible for communication in the absence of the actual user. Technically, a Handler is a class which is responsible for Instantiation of a class i.e; allocation of memory.

Console applications or Windows applications do not have handlers. We declare a class and create an object of that class in the Main() method. We as a developer are solely responsible for handling the class – specifically - instantiation of the class.

However, Web applications have handlers. In GUI Web Applications we never create an object of the “_default” page which inherits the Page class.

In Web Services, the Web Service class is never instantiated.

In WCF, the Service class, which inherits the IService Interface is never instantiated.

Now comes the question – Who instantiates the above mentioned classes?

Behind the scene, the Handler is responsible for instantiation of the above mentioned classes. ASP.NET maps HTTP requests to HTTP handlers based on the extension of the requested file (type of file). Each HTTP handler can process individual HTTP URLs or groups of URL extensions in an application. ASP.NET includes several built-in HTTP handlers:

a)    ASP.NET – System.Web.UI.PageHandlerFactory

b)    Web Service – System.Web.Services.Protocols.WebServiceHandlerFactory

c)    WCF – System.ServiceModel.Activation.ServiceHttpHandlerFactory

There are different drivers for different protocols. The driver or API of the protocol (ex – http.sys) has to have the ability to differentiate and resolve requests.

 

.sys files are the drivers for protocols. http.sys file is a driver or API or listener for the HTTP protocol. You can say that HTTP is abstraction, while http.sys is encapsulation. http.sys file handles the request and response in HTTP. The http.sys file is located in C:\Windows\System32\drivers\ folder.

When a request arrives on the server, the drivers for the protocols (.sys files) handles that request and forwards that request to a particular handler according to the extensions.