What is ADO .NET and what is difference between ADO and ADO.NET?

1.  ADO works with connected data. This means that when you access data such as viewing and updating data it is real-time with a connection being used all the time. This is barring of course you programming special routines to pull all your data into temporary tables. ADO.NET uses data in a disconnected fashion. When you access data ADO.NET makes a copy of the data using XML. ADO.NET only holds the connection open long enough to either pull down the data or to make any requested updates. This makes ADO.NET efficient to use for Web applications. It's also decent for desktop applications.
2. ADO has one main object that is used to reference data called the Recordset object. This object basically gives you a single table view of your data although you can join tables to create a new set of records. With ADO.NET you have various objects that allow you to access data in various ways. The DataSet object will actually allow you to store the relational model of your database. This allows you to pull up customers and their orders accessing/updating the data in each related table individually.
3. ADO allows you to create client-side cursors only whereas ADO.NET gives you the choice of either using client-side or server-side cursors. In ADO.NET classes actually handle the work of cursors. This allows the developer to decide which is best. For Internet development this is crucial in creating efficient applications.
4. ADO allows you to create client-side cursors only whereas ADO.NET gives you the choice of either using client-side or server-side cursors.
5. Whereas ADO allows you to persist records in XML format ADO.NET allows you to manipulate your data using XML as the primary means. This is nice when you are working with other business applications and also helps when you are working with firewalls because data is passed as HTML and XML.

What is diff between pointer and references?

1. Pointer can refer NULL, refernce cannot be NULL
2. Pointer can derefernce to another address, Reference 
once initialized stays that way till it dies.
3. pointer may not associate with a legitimate memory but 
References should associate with certain memory.
4. pointer may not be initialized while created but 
refernce has to intialized when it is created.

What is 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 ; }

What is data integrity? Explain constraints?

A constraint is a property assigned to a column or the set 
of columns in a table that prevents certain types of 
inconsistent data values from being placed in the column
(s). Constraints are used to enforce the data integrity. 
This ensures the accuracy and reliability of the data in 
the database. The following categories of the data 
integrity exist:


Entity Integrity 
Domain Integrity 
Referential integrity 
User-Defined Integrity

Entity Integrity ensures that there are no duplicate rows 
in a table.

Domain Integrity enforces valid entries for a given column 
by restricting the type, the format, or the range of 
possible values.

Referential integrity ensures that rows cannot be deleted, 
which are used by other records (for example, corresponding 
data values between tables will be vital).

User-Defined Integrity enforces some specific business 
rules that do not fall into entity, domain, or referential 
integrity categories.

Each of these categories of the data integrity can be 
enforced by the appropriate constraints. Microsoft SQL 
Server supports the following constraints:


PRIMARY KEY 
UNIQUE 
FOREIGN KEY 
CHECK 
NOT NULL

A PRIMARY KEY constraint is a unique identifier for a row 
within a database table. Every table should have a primary 
key constraint to uniquely identify each row and only one 
primary key constraint can be created for each table. The 
primary key constraints are used to enforce entity 
integrity.

A UNIQUE constraint enforces the uniqueness of the values 
in a set of columns, so no duplicate values are entered. 
The unique key constraints are used to enforce entity 
integrity as the primary key constraints.

A FOREIGN KEY constraint prevents any actions that would 
destroy link between tables with the corresponding data 
values. A foreign key in one table points to a primary key 
in another table. Foreign keys prevent actions that would 
leave rows with foreign key values when there are no 
primary keys with that value. The foreign key constraints 
are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be 
placed in a column. The check constraints are used to 
enforce domain integrity.

A NOT NULL constraint enforces that the column will not 
accept null values. The not null constraints are used to 
enforce domain integrity, as the check constraints.

Index - Clustered & Non Clustered 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: A clustered when defined on a column of a table, the data of the table in the data pages are sorted in order of the column on which index is defined. This column is referred as an Index Key. Since the data is sorted physically on the disk, the leaf page of the index pages are the data pages of the table. 

Inserting a new row in a table with clustered index defined, SQL Server ensures that the row is placed in the correct physical location in keysequence order. Structure of the Clustered Index is as given below:Non-Clustered Indexes: A Non-clustered index contain only the index key (column on which non-clustered index is defined) and a reference to find the data. The items in the index are stored in the order of the index key values, but the information in the table is stored in a different order (which can be dictated by a clustered index). If no clustered index is created on the table, the rows are not guaranteed to be in any particular order.
Non-clustered index structure is depicted as:At any given level in the index, the pages are linked together as shown in Figure below, and this is true regardless of whether the index is a Clustered index or a Non-clustered index.
Both the clustered and non-clustered indexes can be defined on one or more columns of a table, to serve the frequent database queries. However there can be only one clustered index defined on a table while there can be 249 non-clustered indexes created on a table.


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