Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
Interview questions-Please answer
Posted Date:
08 Feb 2006
Total Responses:
1
|
Posted By: Ravindra R Member Level: Gold Points: 2
|
1. what is concurrency?
2. what is index? types of indexes? what r the advantages with them. Is there any chance to create clusterd index with out primary key?
3. Curser types
4. what is Stub?
5.what is global temp table?
|
Responses
|
| Author: Alla.Harini 09 Feb 2006 | Member Level: Silver | Rating:  Points: 2 |
Hi,
Concurrency in .net:
When multiple users attempt to modify data at the same time, controls need to be established in order to prevent one user's modifications from adversely affecting modifications from simultaneous users. The system of handling what happens in this situation is called concurrency control.
Types of Concurrency Control
In general, there are three common ways to manage concurrency in a database:
• Pessimistic concurrency control - a row is unavailable to users from the time the record is fetched until it is updated in the database.
• Optimistic concurrency control - a row is unavailable to other users only while the data is actually being updated. The update examines the row in the database and determines whether any changes have been made. Attempting to update a record that has already been changed results in a concurrency violation.
• "Last in wins" - a row is unavailable to other users only while the data is actually being updated. However, no effort is made to compare updates against the original record; the record is simply written out, potentially overwriting any changes made by other users since you last refreshed the records.
Pessimistic Concurrency
Pessimistic concurrency is typically used for two reasons. First, in some situations there is high contention for the same records. The cost of placing locks on the data is less than the cost of rolling back changes when concurrency conflicts occur.
Pessimistic concurrency is also useful for situations where it is detrimental for the record to change during the course of a transaction. A good example is an inventory application. Consider a company representative checking inventory for a potential customer. You typically want to lock the record until an order is generated, which would generally flag the item with a status of ordered and remove it from available inventory. If no order is generated, the lock would be released so that other users checking inventory get an accurate count of available inventory.
However, pessimistic concurrency control is not possible in a disconnected architecture. Connections are open only long enough to read the data or to update it, so locks cannot be sustained for long periods. Moreover, an application that holds onto locks for long periods is not scalable.
Note If your underlying data source supports transactions, you can simulate pessimistic concurrency by updating your data within a transaction. For more information, see Transactions in ADO.NET.
Optimistic Concurrency
In optimistic concurrency, locks are set and held only while the database is being accessed. The locks prevent other users from attempting to update records at the same instant. The data is always available except for the exact moment that an update is taking place. For more information, see Optimistic Concurrency.
When an update is attempted, the original version of a changed row is compared against the existing row in the database. If the two are different, the update fails with a concurrency error. It is up to you at that point to reconcile the two rows, using business logic that you create.
Last in Wins
With "last in wins," no check of the original data is made and the update is simply written to the database. It is understood that the following scenario can occur:
• User A fetches a record from the database.
• User B fetches the same record from the database, modifies it, and writes the updated record back to the database.
• User A modifies the 'old' record and writes it back to the database.
In the above scenario, the changes User B made were never seen by User A. Be sure that this situation is acceptable if you plan to use the "last in wins" approach of concurrency control.
Concurrency Control in ADO.NET and Visual Studio .NET
ADO.NET and Visual Studio .NET use optimistic concurrency, because the data architecture is based on disconnected data. Therefore, you need to add business logic to resolve issues with optimistic concurrency.
If you choose to use optimistic concurrency, there are two general ways to determine if changes have occurred: the version approach (true version numbers or date-time stamps) and the saving all values approach.
Version Number Approach
In the version number approach, the record to be updated must have a column that contains a date-time stamp or version number. The date-time stamp or a version number is saved on the client when the record is read. This value is then made part of the update.
One way to handle concurrency is to update only if value in the WHERE clause matches the value on the record. The SQL representation of this approach is:
UPDATE Table1 SET Column1 = @newvalue1, Column2 = @newvalue2
WHERE DateTimeStamp = @origDateTimeStamp
Alternatively, the comparison can be made using the version number:
UPDATE Table1 SET Column1 = @newvalue1, Column2 = @newvalue2
WHERE RowVersion = @origRowVersionValue
If the date-time stamps or version numbers match, the record in the data store has not changed and can be safely updated with the new values from the dataset. An error is returned if they don't match. You can write code to implement this form of concurrency checking in Visual Studio .NET. You will also have to write code to respond to any update conflicts. To keep the date-time stamp or version number accurate, you need to set up a trigger on the table to update it when a change to a row occurs.
Saving All Values Approach
An alternative to using a date-time stamp or version number is to get copies of all the fields when the record is read. The DataSet object in ADO.NET maintains two versions of each modified record: an original version (that was originally read from the data source) and a modified version, representing the user updates. When attempting to write the record back to the data source, the original values in the data row are compared against the record in the data source. If they match, it means that the database record has not changed since it was read. In that case, the changed values from the dataset are successfully written to the database.
Each data adapter command has a parameters collection for each of its four commands (DELETE, INSERT, SELECT, and UPDATE). Each command has parameters for both the original values, as well as the current (or modified) values.
Note Adding new records (the INSERT command) only requires the current values since no original record exists and removing records (the DELETE command) only requires the original values in order to locate the record to delete.
The following example shows the command text for a dataset command that updates a typical Customers table. The command is specified for dynamic SQL and optimistic concurrency.
UPDATE Customers SET CustomerID = @currCustomerID, CompanyName = @currCompanyName, ContactName = @currContactName,
ContactTitle = currContactTitle, Address = @currAddress, City = @currCity,
PostalCode = @currPostalCode, Phone = @currPhone, Fax = @currFax
WHERE (CustomerID = @origCustomerID) AND (Address = @origAddress OR @origAddress IS NULL AND Address IS NULL) AND (City = @origCity OR @origCity IS NULL AND City IS NULL)
AND (CompanyName = @origCompanyName OR @origCompanyName IS NULL AND CompanyName IS NULL) AND (ContactName = @origContactName OR @origContactName IS NULL AND ContactName IS NULL) AND (ContactTitle = @origContactTitle OR @origContactTitle IS NULL AND ContactTitle IS NULL)
AND (Fax = @origFax OR @origFax IS NULL AND Fax IS NULL) AND (Phone = @origPhone OR @origPhone IS NULL AND Phone IS NULL) AND (PostalCode = @origPostalCode OR @origPostalCode IS NULL AND PostalCode IS NULL);
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City,
PostalCode, Phone, Fax
FROM Customers WHERE (CustomerID = @currCustomerID)
Note that the nine SET statement parameters represent the current values that will be written to the database, whereas the nine WHERE statement parameters represent the original values that are used to locate the original record.
The first nine parameters in the SET statement correspond to the first nine parameters in the parameters collection. These parameters would have their SourceVersion property set to Current.
The next nine parameters in the WHERE statement are used for optimistic concurrency. These placeholders would correspond to the next nine parameters in the parameters collection, and each of these parameters would have their SourceVersion property set to Original.
The SELECT statement is used to refresh the dataset after the update has occurred. It is generated when you set the Refresh the DataSet option in the Advanced SQL Generations Options dialog box.
Note The above SQL uses named parameters, whereas OleDbDataAdapter commands use question marks (?) as parameter placeholders.
By default Visual Studio will create these parameters for you if you select the Optimistic Concurrency option in the DataAdapter Configuration Wizard.
Index and It’s purpose:
Indexes in databases are similar to indexes in books. In a database, an index allows the database program to find data in a table without scanning the entire table. An index in a database is a list of values in a table with the storage locations of rows in the table that contain each value. Indexes can be created on either a single column or a combination of columns in a table and are implemented in the form of B-trees. An index contains an entry with one or more columns (the search key) from each row in a table. A B-tree is sorted on the search key, and can be searched efficiently on any leading subset of the search key. For example, an index on columns A, B, C can be searched efficiently on A, on A, B, and A, B, C.
There are 2 types of indexes clustered and nonclustered. 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 nodes of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
Consider using a clustered index for:
o Columns that contain a large number of distinct values.
o Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
o Columns that are accessed sequentially.
o Queries that return large result sets.
Non-clustered indexes have the same B-tree structure as clustered indexes, with two significant differences:
o The data rows are not sorted and stored in order based on their non-clustered keys.
o The leaf layer of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Each index row contains the non-clustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value.
o Per table only 249 non clustered indexes.
Disadvantage of index:
Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much.
Cursors:
When you write code for a transaction that retrieves a single row of results, you can use a SELECT INTO statement. This is called a singleton SELECT statement.
When you write code for a transaction where the result set includes several rows of data, you must declare and use a cursor. A cursor is a mechanism you can use to fetch rows one at a time. For example, if you write code that includes a SELECT statement or stored procedure that returns multiple rows, you must declare a cursor and associate it with the SELECT statement. Then, by using the FETCH statement, you can retrieve one row at a time from the result set.
Cursor types:
Default: adOpenForwardOnly
Indicates the type of cursor used:
Forward-only/read-only (adOpenForwardOnly)
Static (adOpenStatic)
Keyset (adOpenKeyset)
Dynamic (adOpenDynamic)
stub:
A stub is a small program routine that substitutes for a longer program, possibly to be loaded later or that is located remotely. For example, a program that uses Remote Procedure Calls (RPC) is compiled with stubs that substitute for the program that provides a requested procedure. The stub accepts the request and then forwards it (through another program) to the remote procedure. When that procedure has completed its service, it returns the results or other status to the stub which passes it back to the program that made the request
Global Temp table:
The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from instances of Microsoft® SQL Server™ 2000. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server" 1
"For example, if you create a table named employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If you create a local temporary table named #employees, you are the only person who can work with the table, and it is deleted when you disconnect. If you create a global temporary table named ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it when both of you disconnect."
If u need more information search it.
|
| Post Reply |
|
|
|
You must Sign In to post a response.
|
|
|
|