SQL Server interview questions

Explain the use of keyword WITH ENCRYPTION. Create a Store Procedure with Encryption.It is a way to convert the original text of the stored procedure into encrypted form. The stored procedure gets obfuscated and the output of this is not visible to 


CREATE PROCEDURE Abc

WITH ENCRYPTION

AS

<< SELECT statement>>

GO


What is a linked server in SQL Server?It enables SQL server to address diverse data sources like OLE DB similarly. It allows Remote server access and has the ability to issue distributed queries, updates, commands and transactions.


Features and concepts of Analysis Services



Analysis Services is a middle tier server for analytical processing, OLAP, and Data mining. It manages multidimensional cubes of data and provides access to heaps of information including aggregation of data One can create data mining models from data sources and use it for Business Intelligence also including reporting features.

Some of the key features are:

· Ease of use with a lot of wizards and designers.

· Flexible data model creation and management

· Scalable architecture to handle OLAP

· Provides integration of administration tools, data sources, security, caching, and reporting etc.

· Provides extensive support for custom applications


What is Analysis service repository?


Every Analysis server has a repository to store metadata for the objects like cubes, data sources etc. It’s by default stored in a MS Access database which can be also migrated to a SQL Server database.


What is SQL service broker?



Service Broker allows internal and external processes to send and receive guaranteed, asynchronous messaging. Messages can also be sent to remote servers hosting databases as well. The concept of queues is used by the broker to put a message in a queue and continue with other applications asynchronously. This enables client applications to process messages at their leisure without blocking the broker. Service Broker uses the concepts of message ordering, coordination, multithreading and receiver management to solve some major message queuing problems. It allows for loosely coupled services, for database applications.





What is user defined datatypes and when you should go for them?



User defined data types are based on system data types. They should be used when multiple tables need to store the same type of data in a column and you need to ensure that all these columns are exactly the same including length, and nullability.

Parameters for user defined datatype:

Name

System data type on which user defined data type is based upon.

Nullability.

For example, a user-defined data type called post_code could be created based on char system data type.





What is bit datatype?



A bit datatype is an integer data type which can store either a 0 or 1 or null value.





Describe the XML support SQL server extends.

SQL Server (server-side) supports 3 major elements:
Creation of XML fragments: This is done from the relational data using FOR XML to the select query.
Ability to shred xml data to be stored in the database.
Finally, storing the xml data.

Client-side XML support in SQL Server is in the form of SQLXML. It can be described in terms of
XML Views: providing bidirectional mapping between XML schemas and relational tables.
Creation of XML Templates: allows creation of dynamic sections in XML.





What is SQL Server English Query?



English query allows accessing the relational databases through English Query applications. Such applications permit the users to ask the database to fetch data based on simple English instead of using SQL statements.





What is the purpose of SQL Profiler in SQL server?



SQL profiler is a tool to monitor performance of various stored procedures. It is used to debug the queries and procedures. Based on performance, it identifies the slow executing queries. Capture any problems by capturing the events on production environment so that they can be solved.





What is XPath?



XPath is an expressions to select a xml node in an XML document.

It allows the navigation on the XML document to the straight to the element where we need to reach and access the attributes.





What are the Authentication Modes in SQL Server?



a. Windows Authentication Mode (Windows Authentication): uses user’s Windows account

b. Mixed Mode (Windows Authentication and SQL Server Authentication): uses either windows or SQL server





Explain Data Definition Language, Data Control Language and Data Manipulation Language.



Data Definition Language (DDL):- are the SQL statements that define the database structure.

Example:

a. CREATE

b. ALTER

c. DROP

d. TRUNCATE

e. COMMENT

f. RENAME


Data Manipulation Language (DML):- statements are used for manipulate or edit data.

Example:

a. SELECT - retrieve data from the a database

b. INSERT - insert data into a table

c. UPDATE - updates existing data within a table

d. DELETE

e. MERGE

f. CALL

g. EXPLAIN PLAN

h. LOCK TABLE

Data Control Language (DCL):-statements to take care of the security and authorization.

Examples:
GRANT
REVOKE



What are the steps to process a single SELECT statement?



Steps



a. The select statement is broken into logical units

b. A sequence tree is built based on the keywords and expressions in the form of the logical units.

c. Query optimizer checks for various permutations and combinations to figure out the fastest way using minimum resources to access the source tables. The best found way is called as an execution plan.

d. Relational engine executes the plan and processes the data





Explain GO Command.



Go command is a signal to execute the entire batch of SQL statements after previous Go.



What is the significance of NULL value and why should we avoid permitting null values?



NULL value means that no entry has been made into the column. It states that the corresponding value is either unknown or undefined. It is different from zero or "". They should be avoided to avoid the complexity in select & update queries and also because columns which have constraints like primary or foreign key constraints cannot contain a NULL value.









What is the difference between UNION and UNION ALL?



UNION selects only distinct values whereas UNION ALL selects all values and not just distinct ones.

UNION: SELECT column_names FROM table_name1
UNION
SELECT column_names FROM table_name2

UNION All: SELECT column_names FROM table_name1
UNION ALL
SELECT column_names FROM table_name2





What is use of DBCC Commands?



DBCC (Database consistency checker) act as Database console commands for SQL Server to check database consistency. They are grouped as:

Maintenance: Maintenance tasks on Db, filegroup, index etc. Commands include DBCC CLEANTABLE, DBCC INDEXDEFRAG, DBCC DBREINDEX, DBCC SHRINKDATABASE, DBCC DROPCLEANBUFFERS, DBCC SHRINKFILE, DBCC FREEPROCCACHE, and DBCC UPDATEUSAGE.

Miscellaneous: Tasks such as enabling tracing, removing dll from memory. Commands include DBCC dllname, DBCC HELP, DBCC FREESESSIONCACHE, DBCC TRACEOFF, DBCC FREESYSTEMCACHE, and DBCC TRACEON.

Informational: Tasks which gather and display various types of information. Commands include DBCC INPUTBUFFER, DBCC SHOWCONTIG, DBCC OPENTRAN, DBCC SQLPERF, DBCC OUTPUTBUFFER, DBCC TRACESTATUS, DBCC PROCCACHE, DBCC USEROPTIONS, and DBCC SHOW_STATISTICS.

Validation: Operations for validating on Db, index, table etc. Commands include DBCC CHECKALLOC, DBCC CHECKFILEGROUP, DBCC CHECKCATALOG, DBCC CHECKIDENT, DBCC CHECKCONSTRAINTS, DBCC CHECKTABLE, and DBCC CHECKDB.





What is Log Shipping?



Log shipping defines the process for automatically taking backup of the database and transaction files on a SQL Server and then restoring them on a standby/backup server. This keeps the two SQL Server instances in sync with each other. In case production server fails, users simply need to be pointed to the standby/backup server. Log shipping primarily consists of 3 operations:

Backup transaction logs of the Production server.

Copy these logs on the standby/backup server.

Restore the log on standby/backup server.













What is the difference between a Local and a Global temporary table?



Temporary tables are used to allow short term use of data in SQL Server. They are of 2 types:


Local

Global


Only available to the current Db connection for current user and are cleared when connection is closed.

Available to any connection once created. They are cleared when the last connection is closed.


Multiple users can’t share a local temporary table.

Can be shared by multiple user sessions.






What is the STUFF and how does it differ from the REPLACE function?



Both STUFF and REPLACE are used to replace characters in a string.
select replace('abcdef','ab','xx') results in xxcdef

select replace('defdefdef','def','abc') results in abcabcabc
We cannot replace a specific occurrence of “def” using REPLACE.

select stuff('defdefdef',4, 3,'abc') results in defabcdef

where 4 is the character to begin replace from and 3 is the number of characters to replace.






What are the rules to use the ROWGUIDCOL property to define a globally unique identifier column?



Only one column can exist per table that is attached with ROWGUIDCOL property. One can then use $ROWGUID instead of column name in select list.





What is the actions prevented once referential integrity is enforced?



Actions prevented are:

· Breaking of relationships is prevented once referential integrity on a database is enforced.

· Can’t delete a row from primary table if there are related rows in secondary table.

· Can’t update primary table’s primary key if row being modified has related rows in secondary table.

· Can’t insert a new row in secondary table if there are not related rows in primary table.

· Can’t update secondary table’s foreign key if there is no related row in primary table.





What are the commands available for Summarizing Data in SQL Server?



Commands for summarizing data in SQL Server:


Command

Description

Syntax/Example


SUM

Sums related values

SELECT SUM(Sal) as Tot from Table1;


AVG

Average value

SELECT AVG(Sal) as Avg_Sal from Table1;


COUNT

Returns number of rows of resultset

SELECT COUNT(*) from Table1;


MAX

Returns max value from a resultset

SELECT MAX(Sal) from Table1;


MIN

Returns min value from a resultset

SELECT MIN(Sal) from Table1;


GROUP BY

Arrange resultset in groups

SELECT ZIP,City FROM Emp GROUP BY ZIP


ORDER BY

Sort resultset

SELECT ZIP,City FROM Emp ORDER BY City






List out the difference between CUBE operator and ROLLUP operator



Difference between CUBE and ROLLUP:


CUBE

ROLLUP


It’s an additional switch to GROUP BY clause. It can be applied to all aggregation functions to return cross tabular result sets. .

It’s an extension to GROUP BY clause. It’s used to extract statistical and summarized information from result sets. It creates groupings and then applies aggregation functions on them.


Produces all possible combinations of subtotals specified in GROUP BY clause and a Grand Total.

Produces only some possible subtotal combinations.






What are the guidelines to use bulk copy utility of SQL Server?



Bulk copy is an API that allows interacting with SQL Server to export/import data in one of the two data formats. Bulk copy needs sufficient system credentials.

· Need INSERT permissions on destination table while importing.

· Need SELECT permissions on source table while exporting.

· Need SELECT permissions on sysindexes, sysobjects and syscolumns tables.

bcp.exe northwind..cust out "c:\cust.txt" –c -T

Export all rows in Northwind.Cust table to an ASCII-character formatted text file.









What are the capabilities of Cursors?



Capabilities of cursors:

· Cursor reads every row one by one.

· Cursors can be used to update a set of rows or a single specific row in a resultset

· Cursors can be positioned to specific rows.

· Cursors can be parameterized and hence are flexible.

· Cursors lock row(s) while updating them.





What are the ways to controlling Cursor Behavior?



There are 2 ways to control Cursor behavior:

· Cursor Types: Data access behavior depends on the type of cursor; forward only, static, keyset-drive and dynamic.

· Cursor behaviors: Keywords such as SCROLL and INSENSITIVE along with the Cursor declaration define scrollability and sensitivity of the cursor.





What are the advantages of using Stored Procedures?



Advantages of using stored procedures are:

· They are easier to maintain and troubleshoot as they are modular.

· Stored procedures enable better tuning for performance.

· Using stored procedures is much easier from a GUI end than building/using complex queries.

· They can be part of a separate layer which allows separating the concerns. Hence Database layer can be handled by separate developers proficient in database queries.

· Help in reducing network usage.

· Provides more scalability to an application.

· Reusable and hence reduce code.





What are the ways to code efficient transactions?



Some ways and guidelines to code efficient transactions:

· Do not ask for an input from a user during a transaction.

· Get all input needed for a transaction before starting the transaction.

· Transaction should be atomic

· Transactions should be as short and small as possible.

· Rollback a transaction if a user intervenes and re-starts the transaction.

· Transaction should involve a small amount of data as it needs to lock the number of rows involved.

· Avoid transactions while browsing through data.







What are the differences among batches, stored procedures, and triggers?




Batch

Stored Procedure

Triggers


Collection or group of SQL statements. All statements of a batch are compiled into one executional unit called execution plan. All statements are then executed statement by statement.

It’s a collection or group of SQL statements that’s compiled once but used many times.

It’s a type of Stored procedure that cannot be called directly. Instead it fires when a row is updated, deleted, or inserted.






What security features are available for stored procedures?



Security features for stored procedures:

· Grants users permissions to execute a stored procedure irrespective of the related tables.

· Grant users users permission to work with a stored procedure to access a restricted set of data yet no give them permissions to update or select underlying data.

· Stored procedures can be granted execute permissions rather than setting permissions on data itself.

· Provide more granular security control through stored procedures rather than complete control on underlying data in tables.





What are the instances when triggers are appropriate?



Scenarios for using triggers:

· To create a audit log of database activity.

· To apply business rules.

· To apply some calculation on data from tables which is not stored in them.

· To enforce referential integrity.

· Alter data in a third party application

· To execute SQL statements as a result of an event/condition automatically.





What are the restrictions applicable while creating views?



Restrictions applicable while creating views:

· A view cannot be indexed.

· A view cannot be Altered or renamed. Its columns cannot be renamed.

· To alter a view, it must be dropped and re-created.

· ANSI_NULLS and QUOTED_IDENTIFIER options should be turned on to create a view.

· All tables referenced in a view must be part of the same database.

· Any user defined functions referenced in a view must be created with SCHEMABINDING option.

· Cannot use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE, COMPUTE BY in views.



What are the events recorded in a transaction log?



Events recorded in a transaction log:

· Broker event category includes events produced by Service Broker.

· Cursors event category includes cursor operations events.

· CLR event category includes events fired by .Net CLR objects.

· Database event category includes events of data.log files shrinking or growing on their own.

· Errors and Warning event category includes SQL Server warnings and errors.

· Full text event category include events occurred when text searches are started, interrupted, or stopped.

· Locks event category includes events caused when a lock is acquired, released, or cancelled.

· Object event category includes events of database objects being created, updated or deleted.

· OLEDB event category includes events caused by OLEDB calls.

· Performance event category includes events caused by DML operators.

· Progress report event category includes Online index operation events.

· Scans event category includes events notifying table/index scanning.

· Security audit event category includes audit server activities.

· Server event category includes server events.

· Sessions event category includes connecting and disconnecting events of clients to SQL Server.

· Stored procedures event category includes events of execution of Stored procedures.

· Transactions event category includes events related to transactions.

· TSQL event category includes events generated while executing TSQL statements.

· User configurable event category includes user defined events.





Describe when checkpoints are created in a transaction log.



Activities causing checkpoints are:

· When a checkpoint is explicitly executed.

· A logged operation is performed on the database.

· Database files have been altered using Alter Database command.

· SQL Server has been stopped explicitly or on its own.

· SQL Server periodically generates checkpoints.

· Backup of a database is taken.







Define Truncate and Delete commands.




TRUNCATE

DELETE


This is also a logged operation but in terms of deallocation of data pages.

This is a logged operation for every row.


Cannot TRUNCATE a table that has foreign key constraints.

Any row not violating a constraint can be Deleted.


Resets identity column to the default starting value.

Does not reset the identity column. Starts where it left from last.


Removes all rows from a table.

Used delete all or selected rows from a table based on WHERE clause.


Cannot be Rolled back.

Need to Commit or Rollback


DDL command

DML command

C#.NET/VB.NET interview questions

Explain the elements of the .NET Framework.

a. CLR (Common Language Runtime): It is a common managed environment where all the .net programs run. Supports multiple languages and has the garbage collector.

b. .Net Framework Class Libraries: For each source code compiler (VB.NET, C#.NET, etc.), there is a minimum set of coding standards that must be met. The minimum set of coding standards that must be met to compile .NET code into MSIL code is known as CLS - Common Language Specification. The role of the Common Language Specification is to ensure that all generated code (MSIL) that meets the minimum set of coding standards can operate successfully within the .NET framework. THE CTS (Common Type System) handles conversion of programming-language data types into .NET compatible (MSIL) data types. The implicit benefit of the CTS is the reduction of development time when attempting to coordinate data types between two sets of different programming-language code.

c. Data and XML: Support for disconnected programming model and XML.

d. XML webservices: creating webservices for distributed architecture.

e. Webforms: Provides support and functionality for Web based UI.

f. Windows forms: Provides support and functionality for Windows based UI.

What is assembly manifest? What is the information it provides.

Assembly Manifest is a file that contains data that describes how the elements present inside an assembly are connected to each other. The assembly manifest contains assembly metadata to define the scope of the assembly and resolve references to resources and classes.

Information provided by Assembly Manifest:
a. Assembly Name
b. Version Number
c. Culture
d. Strong name
e. List of files inside the assembly
f. Reference information

Explain how a .NET application is compiled and executed

Any code written in any .NET complaint languages when compiled, converts into MSIL (Microsoft Intermediate Language) code in form of an assembly through CLS, CTS. IL is the language that CLR can understand. On execution, this IL is converted into binary code by CLR’s just in time compiler (JIT) and these assemblies or DLL are loaded into the memory.


Describe the .NET base class library
.NET’s Base class library exists in order to encapsulate huge number of common functions and makes them easily accessible to the developer. .NET base class library provides the functionality like ADO.NET, XML, Threading, IO, Security, Diagnostics, Resources, Globalization, collections etc. It serves as the main point of interaction between developer and runtime.


Explain the difference between value types and reference types

Value Type:
a. Stores the data.
b. The value of value types is stored on the managed stack.
c. One variable can have just one value.
d. They are lighter objects.
Reference Type:
a. Stores the reference to the data.
b. A reference type is allocated on the heap.
c. several variables can reference the same data
d. They are heavier objects.

Explain the importance of Imports and Using Statements.

Import statement: creates a property on the global object with the name supplied as namespace and initializes it to contain the object that corresponds to the namespace being imported. Any properties created using the import statement cannot be assigned to, deleted, or enumerated. All import statements are executed when a script starts.

Using statements: mainly defines the namespaces whose objects will be used in the form. This clearly solves 2 purposes: Defines all the namespaces that will be used in a form. Secondly, reduces the hassle for the programmer to type the name of namespace again and again while using classes/objects that belong to the namespace.

Explain the difference between a class and a structure

Class:
a. It is reference type.
b. Null value can be assigned to a variable in a class
c. It can have destructor.
d. All variables in classes are by default private.
e. Good to be used from architecture view as it provides high flexibility.
Structure:
a. It is value type.
b. Null value assignment is not feasible here.
c. Cannot have destructor.
d. All variables in structures are public.
e. Good to be used for simple data structures.





Explain how garbage collection manages the reclamation of unused memory.

The garbage collector assumes that all objects in the managed heap are garbage. It starts walking the roots and builds a graph of all objects reachable from the roots recursively. It stops when it attempts to add an object to the graph that it previously added. The graph contains the set of all objects that are reachable from the application's roots. Any object/s that is not in the graph is not accessible by the application, and is considered garbage. Collection only occurs when the heap is full. In such a case, each and every garbage object calls the Finalize method and reclaims the unused memory. 55. Explain how garbage collection deals with circular references.

Explain how garbage collection deals with circular references.

The .Net runtime knows about all the references between the objects. It can identify all the circular references that are reachable from the root and hence finalize them to free them all at once if and when needed.

Explain the process of creating a menu using the MainMenu component.

MainMenu component is a component that allows the display of Menus at runtime on a form.
Process of creating Menu using MainMenu Component:
a. Add MainMenu component on Windows Form.
b. Menu designer allows deciding the structure of the main menu by selecting the Type Here area and adding the Menu Items to be displayed on the menu.
c. Add functionality to Menu Items as required.

Explain the process of creating a context menu using the ContextMenu component

ContextMenu component provides the users with the ability to access some very frequently used commands. Context menu works by right click of mouse. They mainly provide access to commands particular to the control that has been clicked upon.



Process for creating context menus:
Open the windows form application.
Select ContextMenu component from toolbox.
A menu is added. Click on Type here and type in new Menu Items to be placed on the Menu.
Provide the functionality.
Associate the context menu with the form or the control it is supposed to be related to.
What is a delegate? Explain how to create it.

A delegate declares a ref type that references a named of anonymous method. Delegates are secure and type-safe. Consider them as type safe function pointers.

public delegate void Del<T>(T item);

Del<int> d1 = new Del<int>(Notify);


Explain how to declare and raise events from your application.

Declare Events: “Event” keyword is used to declare an event.

public delegate void MyCustomHandler(object o, MyEventArgse);

public class MyEventArgs: EventArgs
{
public readonly int Age;

public MyEventArgs(int age)
{
Age = age;
}

}

public class MyCustomListener
{
public void Show(object o, MyEventArgs e)
{
Console.WriteLine(
"Age is {0}",
e.Age);
}
}


Describe how to implement event handlers and associate them with events.

public class MyClass
{
public static event MyCustomHandler MyEvent;

public static void Main()
{
MyCustomListener mcll = new MyCustomListener();
MyEvent += new MyCustomHandler(mcl1.Show);
GetAge();
}

public static void OnMyEvent(MyEventArgse)
{
if(MyEvent!=null)
MyEvent(new object(),e);
}

public static void GetAge()
{
MyEventArgse1 = new MyEventArgs(25);
OnMyEvent(e1);

}

}


What is Break Mode? How to set breakpoints?

Break mode is the state of an application when the execution gets paused and allows the developer to edit the value in the current state. To attain a break mode we can do any of the following steps:
a. Selecting Break from the Run menu (Ctrl+Break) or pressing the pause button.
b. Reaching to break point.

Setting up the break points:
a. Go to the line where you need to mark the breakpoint.
b. Click with mouse on left corner margin of that line.
c. Another way is to press F9


Describe how to step through code in .NET.

Steps to step through the code in .NET:
a. Start the program in debug mode.
b. When the first breakpoint is reached then step through can be done in one of the two ways:
i. Press F10 to move to next line.
ii. Select debug menu and click on step over. This would step over the breakpoint to next level.
c. Other options are: “Step Into” and “Step Out”.


Describe the debugging windows available in .NET.

Debug->Windows:
Breakpoints: displays a list of all the breakpoints and where they are. Shows condition when that breakpoint will be hit if a condition exists and the Hit Count shows the number of times that breakpoint has been hit.

Output: Displays the status messages for various features in the IDE. It shows the output form a list of objects throughout debug mode of the application.

Immediate: This window allows the programmer to write code and verify values through programming while debugging the application. It helps in checking the values of objects/controls etc, and the conditions throughout the application in debug mode.

What are Debug and Trace classes? Explain how to use them to display error classes.

Both are used to help programmers find errors, occurring events and flow of code. In release mode, however, debug class is disabled and only Trace class is valid to trap things in a live application.
Both have assert functions to validate values.

Trace.WriteLine(variable value or comment).
Debug.WriteLine(variable value or comment).

We could create error handlers to trap unhandled exceptions trapped by Trace and Debug class.

Describe how to create Trace Listeners and log Trace output.

[Conditional("TRACE")]
public static void InitializeUnhandledExceptionHandler()
{
AppDomain.CurrentDomain.UnhandledException +=
new UnhandledExceptionEventHandler(CutomExceptionHandler);
}
public static void CustomExceptionHandler(object sender,
UnhandledExceptionEventArgs args)
{
Exception e=(Exception) args.ExceptionObject;
Trace.WriteLine("Exception: "+e.Message+"\n"+e.GetType() +
"\nStack Trace:\n"+e.StackTrace);
MessageBox.Show(
"An error has occurred:"+e.Message+"\nin: "+e.GetType(),
"Fatal",
MessageBoxButtons.OK,
MessageBoxIcon.Stop,
MessageBoxDefaultButton.Button1);
Trace.Close();
Process.GetCurrentProcess().Kill();
}

[Conditional("DEBUG")]
public static void TrapDebug(string str)
{
Debug.WriteLine("Debug error: "+str);
}

What are Trace switches? Describe how to create and use Trace switches.

Trace switches allow us to filter, enable/disable the outputs through Trace. We can configure them through the config file. 3 types of trace switches:
BooleanSwitch: Enable/Disable trace statements.
TraceSwitch and SourceSwitch: used for trapping particular Trace levels.

BooleanSwitch dataSwitch =
new BooleanSwitch("Comment", "module1");
TraceSwitch generalSwitch =
new TraceSwitch("comment",
"module1");

Explain how to configure Trace switches in the application’s .config file.

switches are configured using the .config file

<system.diagnostics>
<switches>
<add name="MyTraceSwitch" value="1" />
<add name="TraceSwitch2" value="1" />
</switches>
</system.diagnostics>
both are on.

Explain how exceptions are handled by the common language runtime in .NET.

The CLR uses a technique generally referred to as a two-pass exception review process. What this means is that the CLR will process an exception in two passes. In the first pass, the CLR will determine if there is a handler for the exception. This is done by reviewing the entries in the SEH table; specifically it looks at the Try Offset and Try Length flags to see if the exception occurred within a guarded block, and if so, whether the Flags entry dictates that a handler exists for this type of occurrence. Let's assume that the CLR did find a handler during the first pass. At that point the CLR begins a second pass of the SEH table during which it will work through the execution phase of the exception management process. So we can divide the two passes into a discovery pass, in which we determine whether there is a handler in this method context to handle the exception; and an execution pass, in which we actually execute the handler and any special rules.
When code throws an exception, the CLR looks up the call stack looking for a catch filter to handle the exception. When it finds the relevant catch block, before executing the code, it will execute all code in all finally blocks - starting from the try block that threw the exception and stopping with the catch filter that matches the exception. when the CLR encounters an exception for a method it will use the descriptors in the SEH table to determine how to handle the exception, which code block is affected, and what handler should be invoked.

Describe the different types of user-authored controls in NET.

User authored controls are which not part of the .net framework library. It includes both custom controls and user controls.

a. Custom Controls: They look similar to ASP.NET controls. They can be created in one of the 3 ways:-
a. Deriving a custom control from existing custom control.
b. Making a composite custom control by combining 2 or more existing controls
c. By creating a new control from scratch by deriving the control from its base class.

b. User Controls: enables a part of ASP.NET page to be reused. The reusable part is in form of a control with the extension .ascx. They look like to be a group of ASP.NET controls which can be used over and over again.

Explain with code sample how to create an inherited control.

Steps to create inherited Control:-
a. Create a new project.
b. Add a custom control to the project.
c. Change the name of the class you need to inherit the control from the base class. E.g. inherit the class from System.Windows.Forms.Button if the control s to be inherited from a button class.
d. Implement the control with custom properties and featured needed by the control.
e. Override the OnPaint method if the control’s appearance needs to be changed.
f. Save the build the control
g. Reference you control into another or the same project and use the control.


Explain with code sample how to create a user control.

Steps to create a User control:
a. Select a project
b. Right click and add a new item (User Control - .ascx) to the selected project.
c. Add @Control Directive
d. Add all the controls that you want to be displayed on the User control as a part of one or more web pages.
e. Write the code for all the tasks to be performed by the user control.
f. Create accessor methods for the outside world accessing this user control.

Using the User control:
a. Register the control on the webpage it needs to be used by putting @Register directive.
b. Specify the following attributes to the register directive:
a. TagPrefix: defines the namespace in which the control would reside
b. TagName: defines the name with which control is referred
c. Src: Path of where the control is kept.
c. Control is then used on the page using the following code:
<TagPrefix:TagName />


Explain with code sample how to create a custom control.

Steps to create a custom control:
a. Create a new project.
b. Add a custom control to the project.
c. Change the name of the class you need to inherit the control from the base class. E.g. inherit the class from System.Windows.Forms.Button if the control s to be inherited from a button class.
d. Implement the control with custom properties and featured needed by the control.
e. Override the OnPaint method if the control’s appearance needs to be changed.
f. Save the build the control
g. Reference you control into another or the same project and use the control.

Describe the .NET Framework architecture.


.Net framework has two components:
1. .Net framework class library
2. Common language runtime.
FCL facilitates the types through CTS which are common to all the supported languages.
The CLS ensures that all languages are interoperable. This ensures that all code is managed .i.e. code which is converted to MSIL.
The CLR has the class loader that load the MSIL code of an application into runtime, which is then converted into native code by the JIT complier. The CLR manages code and provide services such as memory management, threading, remoting, type safety, security, Exception handling etc.

What is the managed execution process?

Managed execution process is a process where CLR executes the managed code. The steps involved in this process are:
a. Choosing the right compiler
b. Compiling the code to MSIL. This also generates the required metadata.
c. Compile the MSIL ode to native machine code.
d. Executing the code with the variety of services available.

What are assemblies? Describe the types of assemblies.

Assembly is a compiled output of program which are used for easy deployment of an application. They are executables in the form of exe or dll. It also is a collection of resources that were used while building the application and is responsible for all the logical functioning.
Types of assemblies:
a. Private Assemblies: are accessible by a single application. They reside within the application folder and are unique by name. They can be directly used by copying and pasting them to the bin folder.

b. Shared Assemblies: are shared between multiple applications to ensure reusability. They are placed in GAC.

c. Satellite Assemblies: are the assemblies to provide the support for multiple languages based on different cultures. These are kept in different modules based on the different categories available.

Explain the role of assemblies in .NET.

Assemblies are main building blocks. An assembly maybe defined as a unit of deployment. A single assembly is a collection of types, and resources. The CLR does not understand any types that are outside assemblies. The CLR executes the code in assemblies as they contain MSIL code. They define type, version and security boundaries.

Assemblies in .Net are a solution to the Dll hell problem as one can use different versions of same assembly in different applications at the same time. To make a shared assembly, we need to register it with GAC where as private assemblies reside in applications directory.

What are windows services? How are they differ from other .NET application?

Windows services are a way to create continuously running applications in the background. They don’t interfere with other applications and can be run whenever a machine starts. They can be paused if and when needed and quietly run in the background without the need of any user intervention. Windows services can be configured to run under specific user accounts. They run under their own windows sessions and are ideal for tasks that need to be performed periodically or for monitoring requirements.
Main difference between windows services and other .Net applications lies in the fact that they run in their own windows session without any user intervention in the background.

Object Oriented Programming-Interview Questions

What is OOP?

The object oriented programming is commonly known as OOP. Most of the languages are developed using OOP concept. Object-oriented programming (OOP) is a programming concept that uses "objects" to develop a system.

A programming object has an ability to perform actions and has attributes. It performs just like real world entities for e.g. a motor bike. A bike performs actions such as ’Start’, ’Stop’ etc., and it has attributes like red color, 150 cc etc. So does an Object. Actions and attributes are represented by Methods and fields or properties respectively in programming language.

An object hides the implementation details and exposes only the functionalities and parameters it requires to its client. Here also an object shares the same concept as that of a bike. While driving a motor bike, we are unaware of its implementation details such as how it is developed, internal working of gears etc.? We know only the functions or actions it can perform.

What are the various elements of OOP?

Various elements of OOP are:

• Object


• Class


• Method


• Encapsulation


• Information Hiding


• Inheritance


• Polymorphism

Explain an object.

An object is an entity that keeps together state and behaviors. For instance, a car encapsulates state such as red color, 900 cc etc and behaviors as ’Start’, ’Stop’ etc., so does an object.

An object is an instance of a class. If you consider Dog as a class, it will contain all possible dog traits, while object German Shepherd contains characteristics of specific type of dog.



Define a class.

A class represents description of objects that share same attributes and actions. It defines the characteristics of the objects such as attributes and actions or behaviors. It is the blue print that describes objects.

What is Method?

Method is an objects behavior. If you consider Dog as an object then its behaviors are bark, walk, run etc. Explain Encapsulation concept in OOP.

Encapsulation means keeping actions and attributes together under a single unit. This can also be understood using a motor bike example. A bike has actions such as ’switch on light’, ’horn’ etc. and attributes such specific color, size, weight etc. Here the actions and attributes are bundled together under a single unit, bike.

In a programming language, methods and properties that correspond to actions and attributes respectively are kept under a unit called object. The advantage of encapsulation is that the implementation is not accessible to the client. The user has to know only the functionality of encapsulated unit and information to be supplied to get the result.

What is Information Hiding in OOP?

Information hiding concept restricts direct exposure of data. Data is accessed indirectly using safe mechanism, methods in case of programming object. Taking bike as an example, we have no access to the piston directly, we can use ’start button’ to run the piston. You can understand the advantage of information hiding concept from this example. If a bike manufacturer allows direct access to piston, it would be very difficult to control actions on the piston.

Define Inheritance.

Inheritance concept in OOP allows us to create a new class using an existing one. It also allows the new class to add its own functionality. This concept can also be related to real world entity. A bike manufacturer uses same mechanism of existing version of the bike while launching a new version with some added functionalities. This allows him to save time and efforts.

Explain the term Polymorphism.

Polymorphism means the ability to take more than one form. An operation may exhibit different behaviors in different instances. The behavior depends on the data types used in the operation.



What is Overloading Polymorphism?

Overloading allows multiple functions to exist with same name but different parameters. Again if you take bike as an example, it has a function Start with two forms i.e. ’Auto Start’ and ’kick start’.

Explain Overriding Polymorphism.

Overriding means changing behavior of methods of base class in derive class by overriding the base class methods. If class A is a base class with method ’calculate’ and class B inherits class A, thus derives method ’calculate’ of class A. The behavior of ’calculate’ in class B can be changed by overriding it.

What are the advantages of OOP?

Following are the advantages of OOP:

• It presents a simple, clear and easy to maintain structure.


• It enhances program modularity since each object exists independently.


• New features can be easily added without disturbing the existing one.


• Objects can be reused in other program.

About This PL SQL Programming Tutorial

This Oracle PL SQL tutorial teaches you the basics of programming in PL/SQL with appropriate PL/SQL tutorials with examples. You can use this tutorial as your guide or reference while programming with PL SQL. I will be making this Oracle PL SQL programming tutorial as often as possible to share my knowledge in PL SQL and help you in learning PL SQL better.


Even though the programming concepts discussed in this tutorial is specific to Oracle PL SQL. The concepts like cursors, functions and stored procedures can be used in other database systems like Sybase , Microsoft SQL server etc, with some change in syntax. This PL/SQL tutorial will be growing regularly; let us know if any topic related to PL SQL needs to be added or you can also share your knowledge on PL SQL with us. Lets share our knowledge about PL SQL with others.

The PL/SQL Engine

Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be stored in the client system (client-side) or in the database (server-side).