ASP.NET Interview preparation

Explain the ASP.NET Page Directives?

Page directives configure the runtime environment that will execute the page. The complete list of directives is as follows:

@ Assembly - Links an assembly to the current page or user control declaratively. 

@ Control - Defines control-specific attributes used by the ASP.NET page parser and compiler and can be included only in .ascx files (user controls).

@ Implements - Indicates that a page or user control implements a specified .NET Framework interface declaratively. 

@ Import - Imports a namespace into a page or user control explicitly.

@ Master - Identifies a page as a master page and defines attributes used by the ASP.NET page parser and compiler and can be included only in .master files.

@ MasterType - Defines the class or virtual path used to type the Master property of a page.

 @ OutputCache - Controls the output caching policies of a page or user control declaratively.

 @ Page - Defines page-specific attributes used by the ASP.NET page parser and compiler and can be included only in .aspx files.

 @ PreviousPageType - Creates a strongly typed reference to the source page from the target of a cross-page posting.

 @ Reference - Links a page, user control, or COM control to the current page or user control declaratively.

 @ Register - Associates aliases with namespaces and classes, which allow user controls and custom server controls to be rendered when included in a requested page or user control.

• Life cycle of an ASP .NET page.? 

Following are the events occur during ASP.NET Page Life Cycle:

1)Page_PreInit
2)Page_Init
3)Page_InitComplete
4)Page_PreLoad
5)Page_Load
6)Control Events
7)Page_LoadComplete
8)Page_PreRender
9)SaveViewState
10)Page_Render
11)Page_Unload

Among above events Page_Render is the only event which is raised by page. So we can't write code for this event. 

What is the difference between Server.Transfer and Response.Redirect?

Response.Redirect involves a roundtrip to the server whereas Server.Transfer conserves server resources by avoiding the roundtrip. It just changes the focus of the webserver to a different page and transfers the page processing to a different page.

Response.Redirect can be used for both .aspx and html pages whereas Server.Transfer can be used only for .aspx pages.

Response.Redirect can be used to redirect a user to an external websites. Server.Transfer can be used only on sites running on the same server. You cannot use Server.Transfer to redirect the user to a page running on a different server.

Response.Redirect changes the url in the browser. So they can be bookmarked. Whereas Server.Transfer retains the original url in the browser. It just replaces the contents of the previous page with the new one.

What method do you use to explicitly kill a users session?

Session.Abandon().

• how does the cookies work in asp.net? 

we know Http is an state-less protocol which is required for interaction between clinet and server .

so there is an need to remeber state of request raised by an web browser so that 
web server can recognize you have already previously visited or not.

There are two types of state management techniques:
a) Client side state management
b) Server - side statemanagement

Using cookies comes under clinet side statemanagement .In HttpResponse we write 
Cookie containing sessionId and other information within it.

when a browser made a request to the web server the same cookie is sent to the server where server recognize the session id and get other information stored to it previously. 

• what is the difference between application state and caching? 

Application Object and Cached Object both falls under Server side State Management.

Application object resides in InProc i.e. on the same server where we hosted our application.
Cache Object resides on server side/ DownStream/Client Side.

Application Object will be disposed once application will stop.
Cache Object can be disposed using Time based cache dependency.

Only one user can access Application Object at a time hence we have to lock it every time we modify it. 

• what is boxing and unboxing?

Boxing is what happens when a value-type object is assigned to a reference-type variable.
Unboxing is what happens when a reference-type variable is assigned to a value-type variable.

• What is the use of AutoWireup in asp.net?

AutoEventWireup attribute is used to set whether the events needs to be automatically generated or not.
In the case where AutoEventWireup attribute is set to false (by default) event handlers are automatically required for Page_Load or Page_Init. However when we set the value of the AutoEventWireup attribute to true the ASP.NET runtime does not require events to specify event handlers like Page_Load or Page_Init. 

• what events will occur when a page is loaded?

Below are the events occures during page load.

1) Page_PreInit
2) Page_Init
3) Page_InitComplete
4) Page_PreLoad 

• Where is the View state Data stored?

ViewState data is stored in the hidden field. When the page is submitted to the server the data is sent to the server in the form of hidden fields for each control. If the viewstate of the control is enable true the value is retained on the post back to the client when the page is post backed. 

• Where do the Cookie State and Session State information be stored?

Cookie Information will be stored in a txt file on client system under a
folder named Cookies. Search for it in your system you will find it.

Coming to Session State
As we know for every process some default space will be allocated by OS.

In case of InProc Session Info will be stored inside the process where our
application is running.
In case of StateServer Session Info will be stored using ASP.NET State Service.
In case of SQLServer Session info will be stored inside Database. Default DB
which will be created after running InstallSQLState Script is ASPState.

•  What are the different types of sessions in ASP.Net? Name them.?

Session Management can be achieved in two ways

1)InProc
2)OutProc

OutProc is again two types
1)State Server
2)SQL Server

InProcAdv.:
1) Faster as session resides in the same process as the application
2) No need to serialize the data

DisAdv.:
1) Will degrade the performance of the application if large chunk of data is stored
2) On restart of IIS all the Session info will be lost

State ServerAdv.:
1) Faster then SQL Server session management
2) Safer then InProc. As IIS restart
won't effect the session data

DisAdv.:
1) Data need to be serialized
2) On restart of ASP.NET State Service session info will be lost
3)Slower as compared to InProc

SQL ServerAdv.:
1) Reliable and Durable
2) IIS and ASP.NET State Service
restart won't effect the session data
3) Good place for storing large chunk of data

DisAdv.:
1) Data need to be serialized
2) Slower as compare to InProc and State Server
3)Need to purchase Licensed version of SQL Server

• How can exception be handled with out the use of try catch?

using Exception Management application block

or
Page_error
Application_error objects

• What is the difference between Response.Redirect and Server.Transfer.

Server.Transfer transfers page processing from one page directly to the next page without making a round-trip back to the client's browser. This provides a faster response with a little less overhead on the server.Server.Transfer does not update the clients url history list or current url.

Response.Redirect is used toredirect the user's browser to another page or site. This performs a trip back to the client where the client's browser is redirected to the new page. The user's browser history list is updated to reflect the new address.

• How we implement Web farm and Web Garden concept in ASP.NET?.

A web farm is a multi-server scenario. So we may have a server in each state of US. If the load on one server is in excess then the other servers step in to bear the brunt.
How they bear it is based on various models.
1. RoundRobin. (All servers share load equally)
2. NLB (economical)
3. HLB (expensive but can scale up to 8192 servers)
4. Hybrid (of 2 and 3).
5. CLB (Component load balancer).
A web garden is a multi-processor setup. i.e. a single server (not like the multi server above).
How to implement webfarms in .Net:
Go to web.config and
Here for mode you have 4 options.
a) Say mode inproc (non web farm but fast when you have very few customers).
b) Say mode StateServer (for webfarm)
c) Say mode SqlServer (for webfarm)
Whether to use option b or c depends on situation. StateServer is faster but SqlServer is more reliable and used for mission critical applications.
How to use webgardens in .Net:
Go to web.config and
Change the false to true. You have one more attribute that is related to webgarden in the same tag called cpuMask.

• Is there any limit for query string? means what is the maximum size?..

Servers should be cautious about depending on URI lengths above 255 bytes because some older client or proxy implementations may not properly support these lengths.

Query string length depends on browser compatability

IE supports upto 255
Firefox supports upto 4000

• What is the exact purpose of http handlers?

ASP.NET maps HTTP requests to HttpHandlers. Each HttpHandler enables processing of individual HTTP URLs or groups of URL extensions within an application. HttpHandlers have the same functionality as ISAPI extensions with a much simpler programming model

Ex
1.Default HttpHandler for all ASP.NET pages ->ASP.NET Page Handler (*.aspx)
2.Default HttpHandler for all ASP.NET service pages->ASP.NET Service Handler (*.asmx)

An HttpHandler can be either synchronous or asynchronous. A synchronous handler does not return until it finishes processing the HTTP request for which it is called. An asynchronous handler usually launches a process that can be lengthy and returns before that process finishes
After writing and compiling the code to implement an HttpHandler you must register the handler using your application's Web.config file.

• How information about the user's locale can be accessed?

The information regarding a user's locale can be accessed by using the System.Web.UI.Page.Culture property.

• What is the difference between SQL notification and SQL invalidation?

The SQL cache notification generates notifications when the data of a database changes, on which your cache item depends. The SQL cache invalidation makes a cached item invalid when the data stored in a SQL server database changes.

• What is Cross Page Posting? How is it done?

By default, ASP.NET submits a form to the same page. In cross-page posting, the form is submitted to a different page. This is done by setting the ‘PostBackUrl’ property of the button(that causes postback) to the desired page. In the code-behind of the page to which the form has been posted, use the ‘FindControl’ method of the ‘PreviousPage’ property to reference the data of the control in the first page.

• I have created a configuration setting in my web.config and have kept it at the root level. How do I prevent it from being overridden by another web.config that appears lower in the hierarchy?

By setting the element's Override attribute to false.

• How do you exclude an ASP.NET page from using Themes?

To remove themes from your page, use the EnableTheming attribute of the Page directive.

• What is use of the AutoEventWireup attribute in the Page directive ?

The AutoEventWireUp is a boolean attribute that allows automatic wireup of page events when this attribute is set to true on the page. It is set to True by default for a C# web form whereas it is set as False for VB.NET forms. Pages developed with Visual Studio .NET have this attribute set to false, and page events are individually tied to handlers.

• What happens when you change the web.config file at run time?

ASP.NET invalidates the existing cache and assembles a new cache. Then ASP.NET automatically restarts the application to apply the changes.

• Can you programmatically access IIS configuration settings?

Yes. You can use 

ADSI - Active Directory Service Interfaces

WMI - Windows Management Instrumentation

COM - Component Object model interfaces to configure IIS programmatically.

• What is an application server?

As defined in Wikipedia, an application server is a software engine that delivers applications to client computers or devices. The application server runs your server code. Some well known application servers are IIS (Microsoft), WebLogic Server (BEA), JBoss (Red Hat), WebSphere (IBM).

General .Net Interview Questions

• What is an application server?

As defined in Wikipedia, an application server is a software engine that delivers applications to client computers or devices. The application server runs your server code. Some well known application servers are IIS (Microsoft), WebLogic Server (BEA), JBoss (Red Hat), WebSphere (IBM).

• What is object role modeling (ORM) ?

It is a logical model for designing and querying database models. There are various ORM tools in the market like CaseTalk, Microsoft Visio for Enterprise Architects, Infagon etc.

• What is a private assembly?

A private assembly is local to the installation directory of an application and is used only by that application.

• What is a shared assembly?

A shared assembly is kept in the global assembly cache (GAC) and can be used by one or more applications on a machine.

• What is the difference between user and custom controls?

User controls are easier to create whereas custom controls require extra effort.

User controls are used when the layout is static whereas custom controls are used in dynamic layouts.

A user control cannot be added to the toolbox whereas a custom control can be.

A separate copy of a user control is required in every application that uses it whereas since custom controls are stored in the GAC, only a single copy can be used by all applications.

• What is a COM Callable Wrapper (CCW)?

CCW is a wrapper created by the common language runtime(CLR) that enables COM components to access .NET objects.

• What is a Runtime Callable Wrapper (RCW)?

RCW is a wrapper created by the common language runtime(CLR) to enable .NET components to call COM components.

• What is the .NET Framework?

The .NET Framework is set of technologies that form an integral part of the .NET Platform. It is Microsoft's managed code programming model for building applications that have visually stunning user experiences, seamless and secure communication, and the ability to model a range of business processes.

The .NET Framework has two main components: the common language runtime (CLR) and .NET Framework class library. The CLR is the foundation of the .NET framework and provides a common set of services for projects that act as building blocks to build up applications across all tiers. It simplifies development and provides a robust and simplified environment which provides common services to build application. The .NET framework class library is a collection of reusable types and exposes features of the runtime. It contains of a set of classes that is used to access common functionality.

• What is CLR?

The .NET Framework provides a runtime environment called the Common Language Runtime or CLR. The CLR can be compared to the Java Virtual Machine or JVM in Java. CLR handles the execution of code and provides useful services for the implementation of the program. In addition to executing code, CLR provides services such as memory management, thread management, security management, code verification, compilation, and other system services. It enforces rules that in turn provide a robust and secure execution environment for .NET applications.

• What is CTS?

Common Type System (CTS) describes the datatypes that can be used by managed code. CTS defines how these types are declared, used and managed in the runtime. It facilitates cross-language integration, type safety, and high performance code execution. The rules defined in CTS can be used to define your own classes and values.

• What is CLS?

Common Language Specification (CLS) defines the rules and standards to which languages must adhere to in order to be compatible with other .NET languages. This enables C# developers to inherit from classes defined in VB.NET or other .NET compatible languages.

What is managed code?

The .NET Framework provides a run-time environment called the Common Language Runtime, which manages the execution of code and provides services that make the development process easier. Compilers and tools expose the runtime's functionality and enable you to write code that benefits from this managed execution environment. The code that runs within the common language runtime is called managed code.

What is MSIL?

When the code is compiled, the compiler translates your code into Microsoft intermediate language (MSIL). The common language runtime includes a JIT compiler for converting this MSIL then to native code.

MSIL contains metadata that is the key to cross language interoperability. Since this metadata is standardized across all .NET languages, a program written in one language can understand the metadata and execute code, written in a different language. MSIL includes instructions for loading, storing, initializing, and calling methods on objects, as well as instructions for arithmetic and logical operations, control flow, direct memory access, exception handling, and other operations.

What is JIT?

JIT is a compiler that converts MSIL to native code. The native code consists of hardware specific instructions that can be executed by the CPU.

Rather than converting the entire MSIL (in a portable executable[PE]file) to native code, the JIT converts the MSIL as it is needed during execution. This converted native code is stored so that it is accessible for subsequent calls.

What is portable executable (PE)?PE is the file format defining the structure that all executable files (EXE) and Dynamic Link Libraries (DLL) must use to allow them to be loaded and executed by Windows. PE is derived from the Microsoft Common Object File Format (COFF). The EXE and DLL files created using the .NET Framework obey the PE/COFF formats and also add additional header and data sections to the files that are only used by the CLR.

What is an application domain?

Application domain is the boundary within which an application runs. A process can contain multiple application domains. Application domains provide an isolated environment to applications that is similar to the isolation provided by processes. An application running inside one application domain cannot directly access the code running inside another application domain. To access the code running in another application domain, an application needs to use a proxy.

How does an AppDomain get created? AppDomains are usually created by hosts. Examples of hosts are the Windows Shell, ASP.NET and IE. When you run a .NET application from the command-line, the host is the Shell. The Shell creates a new AppDomain for every application. AppDomains can also be explicitly created by .NET applications.

What is a dynamic assembly?

A dynamic assembly is created dynamically at run time when an application requires the types within these assemblies.

What is a strong name?

You need to assign a strong name to an assembly to place it in the GAC and make it globally accessible. A strong name consists of a name that consists of an assembly's identity (text name, version number, and culture information), a public key and a digital signature generated over the assembly.  The .NET Framework provides a tool called the Strong Name Tool (Sn.exe), which allows verification and key pair and signature generation.

What is GAC? What are the steps to create an assembly and add it to the GAC?

The global assembly cache (GAC) is a machine-wide code cache that stores assemblies specifically designated to be shared by several applications on the computer. You should share assemblies by installing them into the global assembly cache only when you need to.

Steps

- Create a strong name using sn.exe tool eg: sn -k mykey.snk

- in AssemblyInfo.cs, add the strong name eg: [assembly: AssemblyKeyFile("mykey.snk")]

- recompile project, and then install it to GAC in two ways :

·         drag & drop it to assembly folder (C:\WINDOWS\assembly OR C:\WINNT\assembly) (shfusion.dll tool)

·         gacutil -i abc.dll

What is the caspol.exe tool used for?

The caspol tool grants and modifies permissions to code groups at the user policy, machine policy, and enterprise policy levels.

What is a garbage collector?

A garbage collector performs periodic checks on the managed heap to identify objects that are no longer required by the program and removes them from memory.

What are generations and how are they used by the garbage collector?

Generations are the division of objects on the managed heap used by the garbage collector. This mechanism allows the garbage collector to perform highly optimized garbage collection. The unreachable objects are placed in generation 0, the reachable objects are placed in generation 1, and the objects that survive the collection process are promoted to higher generations.

What is Ilasm.exe used for?

Ilasm.exe is a tool that generates PE files from MSIL code. You can run the resulting executable to determine whether the MSIL code performs as expected.

What is Ildasm.exe used for?

Ildasm.exe is a tool that takes a PE file containing the MSIL code as a parameter and creates a text file that contains managed code.

What is the ResGen.exe tool used for?

ResGen.exe is a tool that is used to convert resource files in the form of .txt or .resx files to common language runtime binary .resources files that can be compiled into satellite assemblies.


What are the new features introduced in c# 4.0?

This is very commonly asked c# interview question. This question is basically asked to check, if you are passionate about catching up with latest technological advancements. The list below shows a few of the new features introduced in c# 4.0. If you are aware of any other new features, please submit those using the from at the end of this post.

1. Optional and Named Parameters
2. COM Interoperability Enhancements
3. Covariance and Contravariance
4. Dynamic Type Introduction

SQL Interview preparation

What is RDBMS?


Relational Data Base Management Systems (RDBMS) are database management systems that maintain
data records and indices in tables. Relationships may be created and maintained across and among the
data and tables. In a relational database, relationships between data items are expressed by means of
tables. Interdependencies among these tables are expressed by data values rather than by pointers.
This allows a high degree of data independence. An RDBMS has the capability to recombine the data
items from different files, providing powerful tools for data usage.

What is normalization?


Database normalization is a data design and organization process applied to data structures based on
rules that help build relational databases. In relational database design, the process of organizing data
to minimize redundancy. Normalization usually involves dividing a database into two or more tables and
defining relationships between the tables. The objective is to isolate data so that additions, deletions,
and modifications of a field can be made in just one table and then propagated through the rest of the
database via the defined relationships.

What are different normalization forms?


1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field
contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key 
If attributes do not contribute to a description of the key, remove them to a separate table. All
attributes must be directly dependent on the primary key

BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct
tables.

4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships 
There may be practical constrains on information that justify separating logically related many-to-many
relationships.

ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form
A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first
fulfill all the criteria of a 2NF and 1NF database.


What is Stored Procedure? 


A stored procedure is a named group of SQL statements that have been previously created and stored
in the server database. Stored procedures accept input parameters so that a single procedure can be
used over the network by several clients using different input data. And when the procedure is
modified, all clients automatically get the new version. Stored procedures reduce network traffic and 
improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc. 

What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE)
occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential

integrity of data by changing the data in a systematic fashion.  A trigger cannot be called or executed; 
the DBMS automatically fires the trigger as a result of a data modification to the associated table.

Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is
stored at the database level. Stored procedures, however, are not event-driven and are not attached to a
specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the 
procedure while triggers are implicitly executed. In addition, triggers can also execute stored
procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing
another trigger. A trigger that contains data modification logic within itself is called a nested trigger.


What is View?


A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as
updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the
view was created with. It should also be noted that as data in the original table changes, so does data
in the view, as views are the way to look at part of the original table. The results of using a view are 
not permanently stored in the database. The data accessed through a view is actually constructed using
standard T-SQL select command and can come from one to many different base tables or even other
views.


What is Index?


An index is a physical structure containing pointers to the data. Indices are created in an existing table 
to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of
a table, and each index is given a name. The users cannot see the indexes, they are just used to speed
up queries. Effective indexes are one of the best ways to improve performance in a database
application. A table scan happens when there is no index available to help a query. In a table scan SQL
Server examines every row in the table to satisfy the query results. Table scans are sometimes
unavoidable, but on large tables, scans have a terrific impact on performance.

Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this
reason, each database table may have only one clustered index.

Non-clustered indexes are created outside of the database table and contain a sorted list of references
to the table itself.Max of 249 non-clustered indices can be added to a table.


What is the difference between clustered and a non-clustered index?


A clustered index is a special type of index that reorders the way records in the table are physically
stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain
the data pages. 


A nonclustered index is a special type of index in which the logical order of the index does not match
the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of
the data pages. Instead, the leaf nodes contain index rows.


What are the different index configurations a table can have? 


A table can have one of the following index configurations:

• No indexes 
• A clustered index 
• A clustered index and many nonclustered indexes 
• A nonclustered index 
• Many nonclustered indexes

What is cursors?

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, 

instead of the typical SQL commands that operate on all the rows in the set at one time.

In order to work with a cursor we need to perform some steps in the following order: 

• Declare  cursor 
• Open cursor 
• Fetch row from the cursor 
• Process fetched row 
• Close cursor 
• Deallocate cursor

What is the use of DBCC commands?

DBCC stands for database consistency checker. We use these commands to check the consistency of 

the databases, i.e., maintenance, validation task and status checks. 

E.g. DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked. 

DBCC CHECKALLOC - To check that all pages in a db are correctly allocated. 

DBCC CHECKFILEGROUP - Checks all tables file group for any damage. 

What is a Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query 

both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy 

to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.

Storped Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server. 

What is Collation? 

Collation refers to a set of rules that determine how data is sorted and compared. Character data is 

sorted using rules that define the correct character sequence, with options for specifying casesensitivity, accent marks, kana character types and character width.

What are different type of Collation Sensitivity? 

Case sensitivity

A and a, B and b, etc. 

Accent sensitivity

a and á, o and ó, etc. 

Kana Sensitivity

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana 

sensitive. 

Width sensitivity

When a single-byte character (half-width) and the same character when represented as a double-byte 

character (full-width) are treated differently then it is width sensitive.

What's 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 nonclustered 

index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key 

allows one NULL only. 

How to implement one-to-one, one-to-many and many-to-many relationships while 

designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary 

and foreign key relationships. 

One-to-Many relationships are implemented by splitting the data into two tables with primary key and 

foreign key relationships. 

Many-to-Many relationships are implemented using a junction table with the keys from both the tables 

forming the composite primary key of the junction table.

What is difference between DELETE & TRUNCATE commands? 

Delete command removes the rows from a table based on the condition that we provide with a WHERE 

clause. Truncate will actually remove all the rows from a table and there will be no data in the table

after we run the truncate command.  

TRUNCATE

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. 

TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the 

page deallocations are recorded in the transaction log.     

TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes 

and so on remain. The counter used by an identity for new rows is reset to the seed for the column. 

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. 

Because TRUNCATE TABLE is not logged, it cannot activate a trigger. 

TRUNCATE can not be Rolled back. 

TRUNCATE is DDL Command.  

TRUNCATE Resets identity of the table. 

DELETE

DELETE removes  rows one at a time and records an entry in the transaction log for each deleted row. 

If you want to retain the identity counter, use DELETE instead. If you want to remove table definition

and its data, use the DROP TABLE statement. 

DELETE Can be used with or without a WHERE clause 

DELETE Activates Triggers. 

DELETE Can be Rolled back. 

DELETE is DML Command. 

DELETE does not reset identity of the table.  

Difference between Function and Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as 

Stored procedures cannot be. 

UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.

Inline UDF's can be though of as views that take parameters and can be used in JOINs and other 

Rowset operations. 

When is the use of UPDATE_STATISTICS command?

This command is basically used when a large processing of data has occurred. If a large amount of 

deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to 

take these changes into account. UPDATE_STATISTICS updates the indexes on these tables 

accordingly. 

What types of Joins are possible with Sql Server?

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. 

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT 

statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING 

behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a 

query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query

What is "normalization"? "Denormalization"? Why do you sometimes want to denormalize? 

Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing.

What is a "constraint"? 

 A constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server.

• PRIMARY/UNIQUE - enforces uniqueness of a particular table column. 
• DEFAULT - specifies a default value for a column in case an insert operation does not provide one. 
• FOREIGN KEY - validates that every value in a column exists in a column of another table. 
• CHECK - checks that every value stored in a column is in some specified list. 

Each type of constraint performs a specific type of action. Default is not a constraint. NOT NULL is one more constraint which does not allow values in the specific column to be null. And also it the only constraint which is not a table level constraint.

Why can a "group by" or "order by" clause be expensive to process? -

Processing of "group by" or "order by" clause often requires creation of Temporary tables to process the results of the query. Which depending of the result set can be very expensive.

What is a SQL view? 

 An output of a query can be stored as a view. View acts like small table which meets our criterion. View is a precomplied SQL query which is used to select data from one or more tables. A view is like a table but it doesn’t physically take any space. View is a good way to present data in a particular format if you use that query quite often. View can also be used to restrict users from accessing the tables directly.

how to delete duplicate rows from a table in sql server ?

CREATE TABLE [dbo].[ATTENDANCE](
 [EMPLOYEE_ID] [varchar](50) NOT NULL,
 [ATTENDANCE_DATE] [date] NOT NULL
) ON [PRIMARY] 

INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
   ('A001',CONVERT(DATETIME,'01-01-11',5)) 
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
   ('A001',CONVERT(DATETIME,'01-01-11',5)) 
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
   ('A002',CONVERT(DATETIME,'01-01-11',5)) 
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
   ('A002',CONVERT(DATETIME,'01-01-11',5)) 
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
   ('A002',CONVERT(DATETIME,'01-01-11',5)) 
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
   ('A003',CONVERT(DATETIME,'01-01-11',5)) 

After inserting the data, check the data of the below table. If we grouped the employee_id andattendance_date, then A001 and A002 become duplicates.

EMPLOYEE_ID
ATTENDANCE_DATE

A001
2011-01-01
A001
2011-01-01
A002
2011-01-01
A002
2011-01-01
A002
2011-01-01
A003
2011-01-01

First, insert an identity column in that table by using the following code:

 Collapse | Copy Code
ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)  

Now the table data will be like the following table:

EMPLOYEE_ID
ATTENDANCE_DATE
AUTOID

A001
2011-01-01
1
A001
2011-01-01
2
A002
2011-01-01
3
A002
2011-01-01
4
A002
2011-01-01
5
A003
2011-01-01
6


Check the AUTOID column. Now we will start playing the game with this column.

Now use the following code to find out the duplicate rows that exist in the table.

 Collapse | Copy Code
SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)
 FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

The above code will give us the following result:

EMPLOYEE_ID
ATTENDANCE_DATE
AUTOID

A001
2011-01-01
2
A002
2011-01-01
4
A002
2011-01-01
5


Ultimately, these are the duplicate rows which we want to delete to resolve the issue. Use the following code to resolve it.

 Collapse | Copy Code
DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)
 FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)