DATA VIEW

DataView Object provides a window into a DataTable that can
be sorted and filtered using the Sort,RowFilter, and
RowStateFilter properties.It also contains the AllowDelete,
AllowEdit,AllowEdit, and AllowNew properties to constrain
user input as needed.

DataTable can have many DataView objects  assigned to it,
allowing the data tobe viewed in many diffirent ways
without requiring the data to be reread from the database.

Example:::

DataTable customer = GetDataTable() //ur datable method
// add new datarow by adding the values
customer.Rows.Add("73OOFUFU","Mohamed","Ilyas",2533.00m);
customer.Rows.Add("3636366H","Idriis","Yassin",25545.00m);
//Sort and display
DataView view = new DataView(customer);
view.sort = "LastName ASC, FirstName ASC, Salary DESC";
GridView gv = new GridView();
gv.EnableViewState = false;
form1.Controls.Add(gv);
// assing ur gridview to dataview
gv.DataSource = veiw;
gv.DataBind();






# 1

data tables holds all the rows of the table where as date
view hold te filtered or reqired rows that are been retrived
on a particular condition

DATA TABLE

<%@ Page Language="C#" Debug="true" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Import Namespace="System.Data" %>

<script language="C#" runat="server">

private SqlConnectionconn = null;

private SqlCommandcmd = null;

private SqlDataAdapterda = null;

private DataTabledt = null;

private DataRowCollectiondr = null;

private String connString;

private String strSQL;

private void Page_Load(object sender, System.EventArgs e) {

if (!IsPostBack) {
connString = "data source=localhost;uid=test;pwd=test;initial catalog=Northwind";
strSQL = "SELECT LastName, FirstName, Title, HireDate FROM dbo.Employees";

try {
conn = new SqlConnection(connString);
cmd = new SqlCommand(strSQL, conn);
da = new SqlDataAdapter();
da.SelectCommand = cmd;
dt = new DataTable();
conn.Open();
da.Fill(dt);
dr = dt.Rows;

for (inti=0; i<=(dr.Count-1); i++) {
Response.Write(dr[i]["FirstName"].ToString()

+ " " + dr[i]["LastName"].ToString() + " - " +

dr[i]["Title"].ToString() + "<br>");

}
conn.Close();

} catch (SqlException ex) {
Response.Write("Error accessing database: " + ex.ToString());

} catch (Exception ex) {
Response.Write("Exception: " + ex.ToString());

} finally {

if (conn.State == ConnectionState.Open) {
conn.Close();

}
conn.Dispose();

} } }

</script>





<%@ Page Language="C#" Debug="true" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Import Namespace="System.Data" %>

<script language="C#" runat="server">

private SqlConnectionconn = null;

private SqlCommandcmd = null;

private SqlDataAdapterda = null;

private DataTabledt = null;

private DataRow[] dr = null;

private String connString;

private String strSQL;

private void Page_Load(object sender, System.EventArgs e) {

if (!IsPostBack) {
connString = "data source=localhost;uid=test;pwd=test;initial catalog=Northwind";
strSQL = "SELECT LastName, FirstName, Title, HireDate FROM dbo.Employees";

try {
conn = new SqlConnection(connString);
cmd = new SqlCommand(strSQL, conn);
da = new SqlDataAdapter();
da.SelectCommand = cmd;
dt = new DataTable();
conn.Open();
da.Fill(dt);
dgTest.DataSource = dt;
dgTest.DataBind();
conn.Close();

} finally {

if (conn.State == ConnectionState.Open) {
conn.Close();

}
conn.Dispose();

} } }

</script>

<asp:DataGrid id="dgTest" runat="Server"></asp:DataGrid>

What is SQL Profiler?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.
Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

What are the properties and different Types of Sub-Queries?

1. Properties of Sub-Query
   1. A sub-query must be enclosed in the parenthesis.
   2. A sub-query must be put in the right hand of the comparison operator, and
   3. A sub-query cannot contain an ORDER-BY clause.
   4. A query can contain more than one sub-query.
2. Types of Sub-Query
   1. Single-row sub-query, where the sub-query returns only one row.
   2. Multiple-row sub-query, where the sub-query returns multiple rows,. and
   3. Multiple column sub-query, where the sub-query returns multiple columns

What is difference between DELETE and 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.

1. TRUNCATE:
   1. TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
   2. 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.
   3. TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
   4. 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.
   5. TRUNCATE cannot be rolled back.
   6. TRUNCATE is DDL Command.
   7. TRUNCATE Resets identity of the table
2. DELETE:
   1. DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
   2. 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.
   3. DELETE Can be used with or without a WHERE clause
   4. DELETE Activates Triggers.
   5. DELETE can be rolled back.
   6. DELETE is DML Command.
   7. DELETE does not reset identity of the table.

Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.

What are different types of Collation Sensitivity?

1. Case sensitivity - A and a, B and b, etc.
2. Accent sensitivity
3. Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
4. Width sensitivity - A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently than it is width sensitive.

What are the different index configurations a table can have?

A table can have one of the following index configurations:

1. No indexes
2. A clustered index
3. A clustered index and many nonclustered indexes
4. A nonclustered index
5. Many nonclustered indexes