What is the use of Index

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records. The disk space required to store the index is typically less than that required by the table (since indices usually contain only the key-fields according to which the table is to be arranged, and exclude all the other details in the table), 


 Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book.

SQL Tuning

Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

SQL Tuning/SQL Optimization Techniques:

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.

For Example: Write the query as

SELECT id, first_name, last_name, age, subject FROM student_details;

Instead of:

SELECT * FROM student_details;9) To store large binary objects, first place them in the file system and add the file path in the database.

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause. 
For Example: Write the query as

SELECT id FROM employee 
WHERE name LIKE 'Ramesh%' 
and location = 'Bangalore';

Instead of:

SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee 
WHERE name LIKE 'Ramesh%';

9) To store large binary objects, first place them in the file system and add the file path in the database.

10) To write queries which provide efficient performance follow the general SQL standard rules.

a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb

SQL Optimization

Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

SQL Tuning/SQL Optimization Techniques:

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.

For Example: Write the query as

SELECT id, first_name, last_name, age, subject FROM student_details;

Instead of:

SELECT * FROM student_details;9) To store large binary objects, first place them in the file system and add the file path in the database.

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause. 
For Example: Write the query as

SELECT id FROM employee 
WHERE name LIKE 'Ramesh%' 
and location = 'Bangalore';

Instead of:

SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee 
WHERE name LIKE 'Ramesh%';

9) To store large binary objects, first place them in the file system and add the file path in the database.

10) To write queries which provide efficient performance follow the general SQL standard rules.

a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb

SQL Alias

SQL Aliases are defined for columns and tables. Basically aliases is created to make the column selected more readable.

For Example: To select the first name of all the students, the query would be like:

Aliases for columns:

SELECT first_name AS Name FROM student_details;
or
SELECT first_name Name FROM student_details;

In the above query, the column first_name is given a alias as 'name'. So when the result is displayed the column name appears as 'Name' instead of 'first_name'.

Output:

Name
-------------Rahul Sharma
Anjali Bhagwat
Stephen Fleming
Shekar Gowda
Priya Chandra

Aliases for tables:

SELECT s.first_name FROM student_details s; 

In the above query, alias 's' is defined for the table student_details and the column first_name is selected from the table.

Aliases is more useful when

• There are more than one tables involved in a query,
• Functions are used in the query,
• The column names are big or not readable,
• More than one columns are combined together

Difference between DELETE and TRUNCATE Statements

DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.

TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.

Difference between TRUNCATE and DELETE

Truncate	Delete
TRUNCATE is a DDL command	DELETE is a DML command
TRUNCATE TABLE always locks the table and page but not each row	DELETE statement is executed using a row lock, each row in the table is locked for deletion
Cannot use Where Condition	We can specify filters in where clause
It Removes all the data	It deletes specified data if where condition exists.
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.	Delete activates a trigger because the operation are logged individually.
Faster in performance wise, because it doesn’t keep any logs	Slower than truncate because, it keeps logs
Rollback is not possible	Rollback is possible
Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table	keeps object’s statistics and all allocated space. After a DELETE statement is executed, the table can still contain empty pages.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction lo	The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row
If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column	DELETE retain the identity Delete works at row level, thus row level constrains apply
Restrictions on using Truncate Statement  1. Are referenced by a FOREIGN KEY constraint.  2. Participate in an indexed view.  3. Are published by using transactional replication or merge replication.

Difference between DROP and TRUNCATE Statement

If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. But, if a table is truncated, the table structure remains the same, therefore any of the above problems will not exist.

Difference between Execute Scalar and Execute Reader

What is the difference between Execute Scalar and Execute Reader?

Execute Reader..
1.Returns a datareader with data.
2.It is done by command object.
3.It is readonly.
4.It executes only select command.
5. This is known as a forward-only retrieval of records.It uses your sql statement to read through the table from the first to the last.


Execute NonQuery..
1.It will not return any data.
2.It is used with insert and update.
3.It returns only the number of rows affected.

Execute Scaler..
1.It returns only one value.
2.That value will the first column first row value.

Execute Query..
1.Its for command objects.
2.It returns the value given by database through select statement.

Difference between for and foreach loop in c#

The for loop executes a statement or a block of statements repeatedly until a specified expression evaluates to false.  there is need to specify the loop bounds( minimum or maximum).

int j = 0;

for (int i = 1; i <= 5; i++)
{
j = j + i ;
}

The foreach statement repeats a group of embedded statements for each element in an array or an object collection.you do not need to specify the loop bounds minimum or maximum.
int j = 0;
int[] tempArr = new int[] { 0, 1, 2, 3, 5, 8, 13 };
foreach (int i in tempArr )
{
j = j + i ;
}

Difference between an abstract class and interface Class

eature	Interface	Abstract class
Declaration and implementation of Methods	Contain only declaration of functions and methods. It does not provide implementation of methods.	We Can provide default implementation of methods.
Implement Every Method or Override every method	The derived class must implement all the methods of interface.	Not necessary to implement all methods.
Fields and Constants	Fields can not be define	Fields can be define.
Access Modfiers	An interface cannot have access modifiers for the functions, properties etc everything is assumed as public	An abstract class can contain access modifiers for the functions, properties

Page Life cycle in asp.net

Event Life cycle of ASP.NET 2.0

To track the flowing of events in web application, you need to add “trace = true” in page directive as shown below.

       <% @Page Trace=”true”%>

PreInit –

1)    Entry point of page life cycle.

2)    We can create or re-create dynamic controls.

3)    We can change master page dynamically.

4)    We can change theme properties dynamically in this control.

Init -

1)    All Control are initialized in this event

2)    The init event of each controls occurs first then init event for page occurs.

Init Complete –

1)    Page is initialized.

2)    Use this event to make changes in view state that you want to make sure are affected after the next post back.

PreLoad –

1)    This event is called before the loading of the page is completed. 

Load –

1)    This event is raised for the Page first and then for all child controls.

2)    The view state can be accessed at this stage.

3)    This event indicates that the controls have been fully loaded.

LoadComplete -

1) This event signals indicates that the page has been loaded in the memory.

2) It also marks the beginning of the rendering stage.

PreRender –

1)    If you need to make any final updates to the contents of the controls or the page, then use this event. 

2)    It first fires for the page and then for all the controls.

PreRenderComplete - 

         1) Is called to explicitly state that the PreRender phase is completed. 

SaveStateComplete –

1)   In this event, the current state of the control is completely saved to the ViewState.

                   

Unload –

1)                             1)  This event is typically used for closing files and database connections.

Stages and corresponding events in the life cycle of the ASP.NET page cycle:

 

Stage	Events/Method
Page Initialization	Page_Init
View State Loading	LoadViewState
Postback data processing	LoadPostData
Page Loading	Page_Load
PostBack Change Notification	RaisePostDataChangedEvent
PostBack Event Handling	RaisePostBackEvent
Page Pre Rendering Phase	Page_PreRender
View State Saving	SaveViewState
Page Rendering	Page_Render
Page Unloading	Page_UnLoad