Friday, March 25, 2011

Row State And Row Version

Row State
When we modify any row data that row status has been changed according to action. When we call AcceptChanges() method of dataset or Table, status of all rows changed to Unchanged.
And deleted rows has been removed from the dataset. But when we call RejectChanges all Added rows has been deleted and deleted rows recovered and status of all rows set to Unchanged.
When we use Fill method to fill the dataset than all row’s status will be Unchanged initially until unless any change has been done.

Row State Description
Unchanged It means there is no change in row’s data.
Added It means row has been added in dataset.
Modified Row has been updated.
Deleted Row has been deleted from table.
Detached It means row is not attached with dataset. Means you have just created the row but not added in dataset by calling Add method yet. If row has been removed from dataset or deleted it’s row status will be detached.

Row Version
A data row object contains multiple value based on the version of row. If we pass version value in data row we can get the data row value for that version.

DataRow objEmpDataRow = objDtEmp.Rows[0];
string objEmpDataRow = objEmpDataRow["EmpID", DataRowVersion.Original].ToString();


Version can be categorized following way.

Current With the help of this you can get the current value of the row.
If row state is Deleted you can not get value for this version.
Default Default row version depends upon Row Status, If Row Status is Added,Modified or Unchanged, the default version value will be equivalent to Current. For Deleted it is equivalent to Original. For Detached it is equivalent to Proposed.
Original This gives you original values of the row. This is not exist if Row State is Added.
Proposed This gives proposed value of the row. This exist Only if Row Status is detached.

Friday, March 18, 2011

Non Clustered Index

In non clustered index leaf node contains the row pointer instead of row itself. If a clustered index is defined on table than leaf node contains the clustered index value rather than row pointer. So physical order of table does not change.Non clustered index can be defined on a table or view with a clustered index or heap. A table can have maximum 249 non clustered index.

The row pointer is a combination of file identifier, page number and number of the rows in page, this row pointer is also called ROW ID.

When we have to use non clustered index?

1) Column which contains mostly the distinct values. If there are very few distinct values(less than 95%) suppose 1 or 0 than query will not use index since table scan will be more efficient.
2) Columns that are frequently used in search that return exact result.
3) Columns that are frequently used in join and grouping
4) Used for queries that return few rows.
5) Queries that return small range of data. Clustered index perform better for large range of data.
6) Queries that contain both clause where and order by.
7) Try to create index on integer column rather than character column, since it takes less space.

How non clustered index affect the table
1) Does not change the physical order of table.
2) Non clustered index is similar to the back index of the book.

Friday, March 11, 2011

Clustered Index

What it does basically create a copy of table and sort it according to the specified column and delete the original table. So it help in searching and sorting the data against that column.
A table can have only one clustered index. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index.

If there is a primary key in table without specification of non clustered index and no clustered index exist on table than a clustered index automatically created on table.If no clustered index defined a table is called heap.


On which column we have to use Clustered Index? When we have to use clustered index?
1) The column which is used in search too frequently specifically in range search like date.
2) No need to create on Primary key it is automatically created for Primary Key if there is no other index created on any other column.You can override the default behavior of primary key by creating index on any other column, if you think it will improve performance.
3) Never create a cluster index on column which frequently updated since in case of clustered index it have to update the row in index table.
4) Create clustered index on a column which is used frequently for sorting the table.
5) Clustered index can be created on multiple column[Composite].
5) It is a good practice to create clustered index on each table.
6) Create a clustered index on a column which has sorted data somewhat.
7) Create a clustered index on a column which has minimal duplicate values.
6) Create a clustered index On a foreign key column.
7) Avoid creating a clustered index based on incrementing(Identity) column. When inserting and updating many rows at once it can create problem. As we know SQL Server automatically create a clustered index on primary key so explicitly specify non clustered key word to indicate that a primary key will use non clustered index.

Clustered index affect the table following way :

1) Reorder the data each time when you fired a query(Update,Delete)
2) Reorder the table according to clustered column.
3) Insert will be slow since the row will be inserted into right place.
4) If there are too many indexs(non clustered) than it affect(slow) the performance.
5) Every time the clustered index key is updated, SQL Server must maintain not just the clustered index but also the non-clustered indexes since non-clustered indexes contain a pointer to the clustered index instead of row pointer. This is yet another reason why you shouldn't create the clustered index on multiple columns.
6) Create a clustered index for each table, a table without clustered index is heap and data will not be sorted in any order and all row will be added at the end of table that can create the problem so better is that create a clustered index for each table if there is no index(by default) means no primary key.
7) If you are creating a clustered index on composite column suppose Name and Age. Than order of the column is very important here only queries that have Name(left most)in where clause will use the index.

Restrictions:
1) When creating an index using create statement you need to specify clustered by default it creates non clustered index.
2) Only owner of the table can create index.
3) Create Clustered index first than non clustered. Since if you will create clustered index after non clustered index it will update the non clustered index to replace row identifier with clustered index value.

Friday, March 4, 2011

Interview Questions TFT -- 5

1) If you have two div and you want to display them left-right than what css style you will use?

Answer: There is a float property which has three possible values : none,left and right, you can use that property to flow the content according to your requirement.By default it is none means your content will flow as it is given.

<div style="background:green;border:1;width:10;height:10;float:left" >
Hello
</div>
<div style="background:yellow;border:1;width:10;height:10">
How are you?
</div>

2) What is the use of dot(.) and Hash(#) in CSS?
We use dot(.) to specify the class and Hash(#) for id. If we define an item with dot(.) in CSS than that style will apply to all elements with the same class. Same for id.

3) If you have two elements with the same id, which one you will get while using getElementById().
Very First One( It will check the physical order of elements, float property will not affect this function).

4) What is the difference between getElementById() and getElemntsByName()
getElementById return single element while getElementsByName return array of elements.

5)What do you do make working like a group of radio buttons?
They should have same name to work like a group.

6) Where we use Having clause?
Having clause used with group by and aggregate functions.
Ex. Select Department from Inno Having avg(Salary)>100000 group by Department

7) Difference Between Clustered Index and Non Clustered Index? Can we have more than one clustered index on a table if not than why?
See Link :- http://interview-preparation-for-you.blogspot.com/2010/11/interview-questions.html

8) What is the difference between lib and binding?
Still Searching....

9) Give me some example of Dynamic polymorphism.
I know only one that is virtual function.

10) Give me some example of call back functions in JQuery.
I have used only success function in JQuery.

11) Write a program to print 1-100. All the numbers that are divisible by 3 and 5 should print in word and all other numbers should print in integer.
I know you can write it easily :)

12) You have an array of string. You write a program to get the index of that item which has maximum length.
This is a simple program but you have to write very carefully there should not be any loop holes basically he is checking your coding standard not only logic.

13) Is there any other method to prevent page refresh instead of Update Panel?
Through JQuery you can do it.

14) If you are saving a value using ajax function of JQuery and connection has been break in the middle than how you will handle this situation?
You can attach error call back function to ajax, it will return time out in case of connection break or any other case in which server does not send response.

15) Give me an example of iterator pattern?
See Link :- Iterator Pattern

16) What is the difference between Hashtable and Dictionary?
Dictionary is generic Hashtable is not.When retrieving the values from hashtable you need to typecast them into appropriate type while in case of dictionary there is no need to type cast.

17) What is entity framework?
Don't know.

18)Have you worked in WCF?
NO

19)How will you host webservices?
We can do it just like a web site. by deploying at this path C:\Inetpub\wwwroot. And make it virtual directory from inetmgr.

20) Difference between capturing and bubbling.
See :http://interview-preparation-for-you.blogspot.com/2010/08/bubble-event.html

21) How will you attach error call back function in ajax call?
Don't know

Followers

Link