dotnet: April 2014

Tuesday, 22 April 2014

Find out 3rd highest Basic_sal

CREATE TABLE Employee([Eid] [int] NULL,[Ename] [nvarchar](255) NULL,[Basic_Sal] [float] NULL)

insert into Employee values(1,'Neeraj',45000)
insert into Employee values(2,'Ankit',5000)
insert into Employee values(3,'Akshay',6000)
insert into Employee values(4,'Ramesh',7600)
insert into Employee values(5,'Vikas',4000)
insert into Employee values(7,'Neha',8500)
insert into Employee values(8,'Shivika',4500)
insert into Employee values(9,'Tarun',9500)

select Ename,Basic_sal
from(select Ename,Basic_Sal,ROW_NUMBER() over (order by Basic_Sal desc) as rowid from Employee)A
where rowid=3
Based on the rowid we can find out the salary in any order...

select Ename,Basic_sal, [rank]
from(select Ename,Basic_Sal,DENSE_RANK() over (order by Basic_Sal desc) as [rank] from Employee)A
where [rank]=3

Asp-Net-form-validation-using-javascript/

Sunday, 6 April 2014

ASP.NET IQ

ASPNET

(B) What’s the sequence in which ASP.NET events are processed ?
Following is the sequence in which the events occur :-
√ Page_Init.
√ Page_Load.
√ Control events
√ Page_Unload event.
Page_init event only occurs when first time the page is started , but Page_Load occurs in subsequent request of the page.

(B) In which event are the controls fully loaded ?
Page_load event guarantees that all controls are fully loaded.Controls are also accessed in Page_Init events but you will see that viewstate is not fully loaded during this event.

(B) How can we identify that the Page is PostBack ?
Page object has a “IsPostBack” property which can be checked to know that is the page posted back.

(A) What is event bubbling ?
Server controls like Datagrid , DataList , Repeater can have other child controls inside them.Example DataGrid can have combo box inside datagrid.These child control do not raise there events by themselves , rather they pass the event to the container parent (which can be a datagrid , datalist , repeater) , which passed to the page as “ItemCommand” event.As the child control send there events to parent this is termed as event bubbling.

(B) How do we assign page specific attributes ?
Page attributes are specified using the @Page directive.

(A) Administrator wants to make a security check that no one has tampered with ViewState , how can he ensure this ?
Using the @Page directive EnableViewStateMac to True.
(B) What’s the use of @ Register directives ?
@Register directive informs the compiler of any custom server control added to the page.

(B) What’s the use of SmartNavigation property ?
It’s a feature provided by ASP.NET to prevent flickering and redrawing when the page is posted back.
Note:- This is only supported for IE browser. Project’s who have browser compatibility as requirement have to think some other ways of avoiding flickering.

(B) What is AppSetting Section in “Web.Config” file ?
Web.config file defines configuration for a webproject.Using “AppSetting” section we can define user defined values.Example below defined is “ConnectionString” section which will be used through out the project for database connection.

(B) Where is ViewState information stored ?
In HTML Hidden Fields.

(I) What’s the use of @ OutputCache directive in ASP.NET?
It’s basically used for caching.See more for Caching chapter.

(B) How can we create custom controls in ASP.NET ?
User controls are created using .ASCX in ASP.NET.After .ASCX file is created you need to two things in order that the ASCX can be used in project:.
√ Register the ASCX control in page using the <%@ Register directive.Example
<%@ Register tagprefix="Account" Tagname="footer" Src="Footer.ascx" %>
√ Now to use the above accounting footer in page you can use the below directive.

(B) How many types of validation controls are provided by ASP.NET ?
There are main six types of validation controls :-
RequiredFieldValidator: It checks does the control have any value.It's used when you want the control should not be empty.
RangeValidator: Checks if the value in validated control is in that specific range. Example TxtCustomerCode should not be more than eight length.
CompareValidator: Checks that the value in controls should match the value in other control. Example Textbox TxtPie should be equal to 3.14.
RegularExpressionValidator: When we want the control value should match with a specific regular expression.
CustomValidator: Used to define UserDefined validation. ValidationSummary
Displays summary of all current validation errors.

(B) Can you explain what is “AutoPostBack” feature in ASP.NET ?
If we want the control to automatically postback in case of any event , we will need to check this attribute as true.Example on a ComboBox change we need to send the event immediately to the server side then set the “AutoPostBack” attribute to true.

(B) How can you enable automatic paging in DataGrid ?
Following are the points to be done in order to enable paging in Datagrid :-
√ Set the “AllowPaging” to true.
√ In PageIndexChanged event set the current pageindex clicked.
Note:- The answers are very short , if you have implemented practically its just a revision.If you are fresher just make sample code using Datagrid and try to implement this functionality.

(B) What’s the use of “GLOBAL.ASAX” file ?
It allows to execute ASP.NET application level events and set application-level variables.

(B) What’s the difference between “Web.config” and“Machine.Config” ?
“Web.config” files apply settings to each web application , while “Machine.config” file apply settings to all ASP.NET applications.

(B) What’s a SESSION and APPLICATION object ?
Session object store information between HTTP request for a particular user. While application object are global across users.
(A) What’s difference between Server.Transfer and response.Redirect ?
Following are the major differences between them:-
√ Response.Redirect sends message to the browser saying it to move to some different page.While server.transfer does not send any message to the browser but rather redirects the user directly from the server itself. So in server.transfer there is no round trip while response.redirect has a round trip and hence puts a load on server.
√ Using Server.Transfer you can not redirect to a different from the server itself. Example If your server is www.yahoo.com you can use server.transfer to move to www.microsoft.com but yes you can move to www.yahoo.com/travels , i.e. within websites. This cross server redirect is possible only using Response.redirect.
√ With server.transfer you can preserve your information. It has a parameter
called as “preserveForm”. So the existing query string etc. will be able in the
calling page. In response.redirect you can maintain the state. You can but has lot of drawbacks. If you are navigating with in the same website use “Server.transfer” or else go for “response.redirect()”
(A)What’s difference between Authentication and authorization?
This can be a tricky question. These two concepts seem altogether similar but there is wide range of difference. Authentication is verifying the identity of a user and authorization is process where we check does this identity have access rights to the system. In short we can say the following authentication is the process of obtaining some sort of credentials from the users and using those credentials to verify the user’s identity. Authorization is the process of allowing an authenticated user access to resources. Authentication is always precedes to Authorization; even if your application lets anonymous users connect and use the application, it still authenticates them as being anonymous.

(I) What is impersonation in ASP.NET ?
By default, ASP.NET executes in the security context of a restricted user account on the local machine. Sometimes you need to access network resources such as a file on a shared drive, which requires additional permissions. One way to overcome this restriction is to use impersonation. With impersonation, ASP.NET can execute the request using the identity of the client who is making the request, or ASP.NET can impersonate a specific account you specify in web.config.

(B) Can you explain in brief how the ASP.NET authentication process works?
ASP.NET does not run by itself it runs inside the process of IIS. So there are two authentication layers which exist in ASP.NET system. First authentication happens at the IIS level and then at the ASP.NET level depending on the WEB.CONFIG file.
Below is how the whole process works:-
√ IIS first checks to make sure the incoming request comes from an IP address that is allowed access to the domain. If not it denies the request.
√ Next IIS performs its own user authentication if it is configured to do so. By default IIS allows anonymous access, so requests are automatically authenticated, but you can change this default on a per – application basis with in IIS.
√ If the request is passed to ASP.net with an authenticated user, ASP.net checks to see whether impersonation is enabled. If impersonation is enabled, ASP.net acts as though it were the authenticated user. If not ASP.net acts with its own configured account.
√ Finally the identity from step 3 is used to request resources from the operating system. If ASP.net authentication can obtain all the necessary resources it grants the users request otherwise it is denied. Resources can include much more than just the ASP.net page itself you can also use .Net’s code access security features to extend this authorization step to disk files, Registry keys and other resources.

(A) What are the various ways of authentication techniques in ASP.NET?
Selecting an authentication provider is as simple as making an entry in the web.config file for the application. You can use one of these entries to select the corresponding built in authentication provider:
√
√
√
√ Custom authentication where you might install an ISAPI filter in IIS that compares incoming requests to list of source IP addresses, and considers requests to be authenticated if they come from an acceptable address. In that case, you would set the authentication mode to none to prevent any of the .net authentication providers from being triggered.
Windows authentication and IIS
If you select windows authentication for your ASP.NET application, you also have to configure authentication within IIS. This is because IIS provides Windows authentication. IIS gives you a choice for four different authentication methods: Anonymous, basic digest and windows integrated If you select anonymous authentication, IIS doesn’t perform any authentication, Any one is allowed to access the ASP.NET application.

If you select basic authentication, users must provide a windows username and password to connect. How ever this information is sent over the network in clear text, which makes basic authentication very much insecure over the internet. If you select digest authentication, users must still provide a windows user name and password to connect. However the password is hashed before it is sent across the network. Digest authentication requires that all users be running Internet Explorer 5 or later and that windows accounts to stored in active directory.
If you select windows integrated authentication, passwords never cross the network. Users must still have a username and password, but the application uses either the Kerberos or challenge/response protocols authenticate the user. Windows-integrated authentication requires that all users be running internet explorer 3.01 or later Kerberos is a network authentication protocol. It is designed to provide strong authentication for client/server applications by using secret-key cryptography. Kerberos is a solution to network security problems. It provides the tools of authentication and strong cryptography over the network to help to secure information in systems across entire enterprise

Passport authentication
Passport authentication lets you to use Microsoft’s passport service to authenticate users of your application. If your users have signed up with passport, and you configure the authentication mode of the application to the passport authentication, all authentication duties are off-loaded to the passport servers. Passport uses an encrypted cookie mechanism to indicate authenticated users. If users have already signed into passport when they visit your site, they’ll be considered authenticated by ASP.NET. Otherwise they’ll be redirected to the passport servers to log in. When they are successfully log in, they’ll be redirected back to your site
To use passport authentication you have to download the Passport Software Development Kit (SDK) and install it on your server. The SDK can be found at http:// msdn.microsoft.com/library/ default.asp?url=/downloads/list/ websrvpass.aps. It includes full details of implementing passport authentication in your own applications.
Forms authentication
Forms authentication provides you with a way to handle authentication using your own custom logic with in an ASP.NET application. The following applies if you choose forms authentication.
√ When a user requests a page for the application, ASP.NET checks for the presence of a special session cookie. If the cookie is present, ASP.NET assumes the user is authenticated and processes the request.
√ If the cookie isn’t present, ASP.NET redirects the user to a web form you provide
You can carry out whatever authentication, checks you like in your form. When the user is authenticated, you indicate this to ASP.NET by setting a property, which creates the special cookie to handle subsequent requests.
(A)How does authorization work in ASP.NET?
ASP.NET impersonation is controlled by entries in the applications web.config file. The default setting is “no impersonation”. You can explicitly specify that ASP.NET shouldn’t use impersonation by including the following code in the file

It means that ASP.NET will not perform any authentication and runs with its own privileges. By default ASP.NET runs as an unprivileged account named ASPNET. You can change this by making a setting in the processModel section of the machine.config file. When you make this setting, it automatically applies to every site on the server. To user a high-privileged system account instead of a low-privileged, set the userName attribute of the processModel element to SYSTEM. Using this setting is a definite security risk, as it elevates the privileges of the ASP.NET process to a point where it can do bad things to the operating system. When you disable impersonation, all the request will run in the context of the account running ASP.NET: either the ASPNET account or the system account. This is true when
you are using anonymous access or authenticating users in some fashion. After the user has been authenticated, ASP.NET uses it own identity to request access to resources. The second possible setting is to turn on impersonation.

In this case, ASP.NET takes on the identity IIS passes to it. If you are allowing anonymous access in IIS, this means ASP.NET will impersonate the IUSR_ComputerName account that IIS itself uses. If you aren’t allowing anonymous access,ASP.NET will take on the credentials of the authenticated user and make requests for resources as if it were that user. Thus by turning impersonation on and using a non-anonymous method of authentication in IIS, you can let users log on and use their identities within your ASP.NET application.
Finally, you can specify a particular identity to use for all authenticated requests With this setting, all the requests are made as the specified user (Assuming the password it correct in the configuration file). So, for example you could designate a user for a single application, and use that user’s identity every time someone authenticates to the application. The drawback to this technique is that you must embed the user’s password in the web.config file in plain text. Although ASP.NET won’t allow anyone to download this file, this is still a security risk if anyone can get the file by other means.

(B)What’s difference between Datagrid , Datalist and repeater ?
A Datagrid, Datalist and Repeater are all ASP.NET data Web controls. They have many things in common like DataSource Property , DataBind Method ItemDataBound and ItemCreated. When you assign the DataSource Property of a Datagrid to a DataSet then each DataRow present in the DataRow Collection of DataTable is assigned to a corresponding DataGridItem and this is same for the rest of the two controls also. But The HTML code generated for a Datagrid has an HTML TABLE element created for the particular DataRow and its a Table form representation with Columns and Rows.
For a Datalist its an Array of Rows and based on the Template Selected and the RepeatColumn Property value We can specify how many DataSource records should appear per HTMLrow. In short in datagrid we have one record per row, but in datalist we can have five or six rows per row. For a Repeater Control,The Datarecords to be displayed depends upon the Templates specified and the only HTML generated is the due to the Templates. In addition to these , Datagrid has a in-built support for Sort,Filter and paging the Data ,which is not possible when using a DataList and for a Repeater Control we would require to write an explicit code to do paging.

(A)From performance point of view how do they rate ?
Repeater is fastest followed by Datalist and finally datagrid.

(B)What’s the method to customize columns in DataGrid?
Use the template column.

(B)How can we format data inside DataGrid?
Use the DataFormatString property.

(A) How will decide the design consideration to take a Datagrid , datalist or repeater ?
Many make a blind choice of choosing datagrid directly , but that's not the right way. Datagrid provides ability to allow the end-user to sort, page, and edit its data.But it comes at a cost of speed.Second the display format is simple that is in row and columns. Real life scenarios can be more demanding that With its templates, the DataList provides more control over the look and feel of the displayed data than the DataGrid.It offers better performance than datagrid Repeater control allows for complete and total control.
With the Repeater, the only HTML emitted are the values of the databinding statements in the templates along with the HTML markup specified in the templates—no "extra" HTML is emitted, as with the DataGrid and DataList. By requiring the developer to specify the complete generated HTML markup, the Repeater often requires the longest development time. But repeater does not provide editing features like datagrid so everything has to be coded by programmer. However, the Repeater does boast the best performance of the three data Web controls. Repeater is fastest followed by Datalist and finally datagrid.

(B) Difference between ASP and ASP.NET?
ASP.NET new feature supports are as follows :-
Better Language Support
√ New ADO.NET Concepts have been implemented.
√ ASP.NET supports full language (C# , VB.NET,C++) and not simple scripting like VBSCRIPT. Better controls than ASP
√ ASP.NET covers large set’s of HTML controls..
√ Better Display grid like Datagrid , Repeater and datalist.Many of the display grid have paging support. Controls have event supports
√ All ASP.NET controls support events.
√ Load, Click and Change events handled by code makes coding much simpler and much better organized. Compiled Code The first request for an ASP.NET page on the server will compile the ASP.NET code and keep a cached copy in memory. The result of this is greatly increased performance. Better Authentication Support ASP.NET supports forms-based user authentication, including cookie management and automatic redirecting of unauthorized logins. (You can still do your custom login page and custom user checking).
User Accounts and Roles ASP.NET allows for user accounts and roles, to give each user (with a given role) access to different server code and executables.
High Scalability
√ Much has been done with ASP.NET to provide greater scalability.
√ Server to server communication has been greatly enhanced, making it possible to scale an application over several servers. One example of this is the ability to run XML parsers, XSL transformations and even resource hungry session objects on other servers. Easy Configuration
√ Configuration of ASP.NET is done with plain text files.
√ Configuration files can be uploaded or changed while the application is running. No need to restart the server. No more metabase or registry puzzle.
Easy Deployment No more server restart to deploy or replace compiled code. ASP.NET simply redirects all new requests to the new code.

(I)How can we force all the validation control to run ?
Page.Validate

(B)How can we check if all the validation control are valid and proper ?
Using the Page.IsValid() property you can check whether all the validation are done.

(A)If you have client side validation is enabled in your Web page , Does that mean server side code is not run?
When client side validation is enabled server emit’s JavaScript code for the custom validators. But note that does not mean that server side checks on custom validators do not execute. It does this two time redundant check. As some of the validators do not support client side scripting.

(A)Which JavaScript file is referenced for validating the validators at the client side ?
WebUIValidation.js javascript file installed at “aspnet_client” root IIS directory is used to validate the validation controls at the client side

(B)How to disable client side script in validators?
Set EnableClientScript to false.

(A)I want to show the entire validation error message in a message box on the client side?
In validation summary set “ShowMessageBox” to true.
(A)What is Tracing in ASP.NET & how can we enable it?
Tracing allows us to view in detail how the code was executed.
<%@ Page Trace="true" %>

(A)Whats the use of <%@ page aspcompat=true %> attribute ?
This attribute works like a compatibility option. As said before ASP worked in STA model and ASP.NET works in MTA model. But what if your ASP.NET application is using a VB COM component. In order that VB COM runs properly in ASP.NET threading model we have to set that attribute. After defining the ASPCOMPAT directive attribute ASP.NET pages runs in STA model thus building the compatibility between ASP.NET and old COM components who does not support MTA model.

(B)Explain the differences between Server-side and Clientside code?
Server side code is executed at the server side on IIS in ASP.NET framework, while client side code is executed on the browser.

SQL Server

What is normalization? What are different type of normalization?
It is set of rules that has been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization. Benefits of Normalizing your database include:
√ Avoiding repetitive entries
√ Reducing required storage space
√ Preventing the need to restructure existing tables to accommodate new data.
√ Increased speed and flexibility of queries, sorts, and summaries.

Following are the three normal forms :-
First Normal Form: For a table to be in first normal form, data must be broken up into the smallest units possible.In addition to breaking data up into the smallest meaningful values, tables in first normal form should not contain repetitions groups of fields.
Second Normal form: The second normal form states that each field in a multiple field primary keytable must be directly related to the entire primary key. Or in other words,each non-key field should be a fact about all the fields in the primary key.
Third normal form: A non-key field should not depend on other Non-key field.The field "Total" is dependent on "Unit price" and "qty".
What is denormalization ?
Denormalization is the process of putting one fact in numerous places (its vice-versa of normalization).Only one valid reason exists for denormalizing a relational design - to enhance performance.The sacrifice to performance is that you increase redundancy in database.
What is a candidate key ?
A table may have more than one combination of columns that could uniquely identify the rows in a table; each combination is a candidate key. During database design you can pick up one of the candidate keys to be the primary key. For example, in the supplier table supplierid and suppliername can be candidate key but you will only pick up supplierid as the primary key.
What are different types of joins and whats the difference between them ?
INNER JOIN
Inner join shows matches only when they exist in both tables.Example in the below SQL there are two tables Customers and Orders and the inner join in made on Customers Customerid and Orders Customerid. So this SQL will only give you result with customers who have orders.If the customer does not have order it will not display that record.

SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

LEFT OUTER JOIN
Left join will display all records in left table of the SQL statement.In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN (Is covered in the next section) if you switch the side of each table.

SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON
Customers.CustomerID =Orders.CustomerID

RIGHT OUTER JOIN
Right join will display all records in right table of the SQL statement.In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.

SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders
ON Customers.CustomerID =Orders.CustomerID
What are indexes and What is the difference between clustered and nonclustered indexes?
Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker There are clustered and nonclustered indexes. 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 node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
What is the use of OLAP & What's a measure in OLAP?
OLAP is useful because it provides fast and interactive access to aggregated data and the ability to drill down to detail.

Measures are the key performance indicators that you want to evaluate. To determine which of the numbers in the data might be measures, a rule of thumb is: If a number makes sense when it is aggregated, then it is a measure.
What is RAID and how does it work ?
Redundant Array of Independent Disks (RAID) is a term used to describe the technique of improving data availability through the use of arrays of disks and various data-striping methodologies. Disk arrays are groups of disk drives that work together to achieve higher data-transfer and I/O rates than those provided by single large drives. An array is a set of multiple disk drives plus a specialized controller (an array controller) that keeps track of how data is distributed across the drives. Data for a particular file is written in segments to the different drives in the array rather than being written to a single drive.

For speed and reliability, it's better to have more disks. When these disks are arranged in certain patterns and use a specific controller, they are called a Redundant Array of Inexpensive Disks (RAID) set. There are several numbers associated with RAID, but the most common are 1, 5 and 10.

RAID 1 works by duplicating the same writes on two hard drives. Let's assume you have two 20 Gigabyte drives. In RAID 1, data is written at the same time to both drives. RAID1 is optimized for fast writes.

RAID 5 works by writing parts of data across all drives in the set (it requires at least three drives). If a drive failed, the entire set would be worthless. To combat this problem, one of the drives stores a "parity" bit. Think of a math problem, such as 3 + 7 = 10. You can think of the drives as storing one of the numbers, and the 10 is the parity part. By removing any one of the numbers, you can get it back by referring to the other two, like this: 3 + X = 10. Of course, losing more than one could be evil. RAID 5 is optimized for reads.

RAID 10 is a bit of a combination of both types. It doesn't store a parity bit, so it's fast, but it duplicates the data on two drives to be safe. You need at least four drives for RAID 10. This type of RAID is probably the best compromise for a database server.
What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
Following are difference between them :-
√ DELETE TABLE syntax logs the deletes thus making the delete operation slow.TRUNCATE table does not log any information but it logs information about deallocation of data page of the table.So TRUNCATE table is faster as compared to delete table.
√ DELETE table can be rolled back while TRUNCATE can not be.
√ DELETE table can have criteria while TRUNCATE can not.
√ TRUNCATE table can not triggers.
What are the problems that can occur if you do not implement locking properly in SQL SERVER ?
Following are the problems that occur if you do not implement locking properly in SQL SERVER.
Lost Updates
Lost updates occur if you let two transactions modify the same data at the same time, and the transaction that completes first is lost. You need to watch out for lost updates with the READ UNCOMMITTED isolation level. This isolation level disregards any type of locks, so two simultaneous data modifications are not aware of each other. Suppose that a customer has due of 2000$ to be paid.He pays 1000$ and again buys a product of 500$.Lets say that these two transactions are now been entered from two different counters of the company.Now both the counter user start making entry at the same time 10:00 AM.Actually speaking at 10:01 AM the customer should have 2000$-1000$+500 = 1500$ pending to be paid.But as said in lost updates the first transaction is not considered and the second transaction overrides it.So the final pending is 2000$+500$ = 2500$.....I hope the company does not loose the customer.
Non-Repeatable Read
Non-repeatable reads occur if a transaction is able to read the same row multiple times and gets a different value each time.Again, this problem is most likely to occur with the READ UNCOMMITTED isolation level. Because you let two transactions modify data at the same time, you can get some unexpected results. For instance, a customer wants to book flight , so the travel agent checks for the flights availability.Travel agent finds a seat and goes ahead to book the seat.While the travel agent is booking the seat , some other travel agent books the seat.When this travel agent goes to update the record , he gets error saying that “Seat is already booked”.In short the travel agent gets different status at different times for the seat.
Dirty Reads
Dirty reads are a special case of non-repeatable read. This happens if you run a report while transactions are modifying the data that you're reporting on. For example there is a customer invoice report which runs on 1:00 AM in afternoon and after that all invoices are sent to the respective customer for payments.Lets say one of the customer has 1000$ to be paid.Customer pays 1000$ at 1:00 AM and at the same time report is run. Actually customer has no money pending but is still issued a invoice.
Phantom Reads
Phantom reads occur due to a transaction being able to read a row on the first read, but not being able to modify the same row due to another transaction deleting rows from the same table. Lets say you edit a record in the mean time somebody comes and deletes the record , you then go for updating the record which does not exist....Panic. Interestingly, the phantom reads can occur even with the default isolation level supported by SQL Server: READ COMMITTED. The only isolation level that doesn't allow phantoms is SERIALIZABLE, which ensures that each transaction is completely isolated from others. In other words, no one can acquire any type of locks on the affected row while it is being modified.
What are different transaction levels in SQL SERVER ?
Twist :- what are different types of locks in SQL SERVER ?
Transaction Isolation level decides how is one process isolated from other process. Using transaction levels you can implement locking in SQL SERVER.
There are four transaction levels in SQL SERVER :-
READ COMMITTED
The shared lock is held for the duration of the transaction, meaning that no other transactions can change the data at the same time. Other transactions can insert and modify data in the same table, however, as long as it is not locked by the first transaction.
READ UNCOMMITTED
No shared locks and no exclusive locks are honored. This is the least restrictive isolation level resulting in the best concurrency but the least data integrity.
REPEATABLE READ
This setting disallows dirty and non-repeatable reads. However, even though the locks are held on read data, new rows can still be inserted in the table, and will subsequently be read by the transaction.
SERIALIZABLE
This is the most restrictive setting holding shared locks on the range of data. This setting does not allow the insertion of new rows in the range that is locked; therefore, no phantoms are allowed.
Following is the syntax for setting transaction level in SQL SERVER.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
What are different locks in SQL SERVER ?
Depending on the transaction level six types of lock can be acquired on data :-
Intent
The intent lock shows the future intention of SQL Server's lock manager to acquire locks on a specific unit of data for a particular transaction. SQL Server uses intent locks to queue exclusive locks, thereby ensuring that these locks will be placed on the data elements in the order the transactions were initiated. Intent locks come in three flavors: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). IS locks indicate that the transaction will read some (but not all) the resources in the table or page by placing shared locks. IX locks indicate that the transaction will modify some (but not all) resources in the table or page by placing exclusive locks. SIX locks indicate that the transaction will read all resources, and modify some(but not all) of them. This will be accomplished by placing the shared locks on the resources read and exclusive locks on the rows modified. Only one SIX lock is allowed per resource at one time; therefore, SIX locks prevent other connections from modifying any data in the resource (page or table), although they do allow reading the data in the same resource.

Shared
Shared locks (S) allow transactions to read data with SELECT statements. Other connections are allowed to read the data at the same time; however, no transactions are allowed to modify data until the shared locks are released.
Update
Update locks (U) are acquired just prior to modifying the data. If a transaction modifies a row, then the update lock is escalated to an exclusive lock; otherwise, it is converted to a shared lock. Only one transaction can acquire update locks to a resource at one time. Using update locks prevents multiple connections from having a shared lock that want to eventually modify a resource using an exclusive lock. Shared locks are compatible with other shared locks, but are not compatible with Update locks.
Exclusive
Exclusive locks (X) completely lock the resource from any type of access including reads. They are issued when data is being modified through INSERT, UPDATE and DELETE statements.
Schema
Schema modification locks (Sch-M) are acquired when data definition language statements, such as CREATE TABLE, CREATE INDEX, ALTER TABLE, and so on are being executed. Schema stability locks (Sch-S) are acquired when store procedures are being compiled.
Bulk Update
Bulk update locks (BU) are used when performing a bulk-copy of data into a table with TABLOCK hint. These locks improve performance while bulk copying data into a table; however, they reduce concurrency by effectively disabling any other connections to read or modify data in the table.
What is LOCK escalation?
Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.
What are the different ways of moving data/ databases between servers and databases in SQL Server?
There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, detaching and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
You can use Having Clause with the GROUP BY function in a query and WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
What is difference between UNION and UNION ALL SQL syntax ?
UNION SQL syntax is used to select information from two tables.But it selects only distinct records from both the table. , while UNION ALL selects all records from both the tables.
What is ACID fundamental and what are transactions in SQL SERVER ?
A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:
Atomicity
√ A transaction must be an atomic unit of work; either all of its data modifications are performed or none of them is performed.
Consistency
√ When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity. All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.
Isolation
√ Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.
Durability
√ After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.
What is purpose of Replication ?
Replication is way of keeping data synchronized in multiple databases. SQL server replication has two important aspects publisher and subscriber. Publisher Database server that makes data available for replication is called as Publisher. Subscriber Database Servers that get data from the publishers is called as Subscribers.
) What is BCP utility in SQL SERVER ?
BCP (Bulk Copy Program) is a command line utility by which you can import and export large amounts of data in and out of SQL SERVER database.
What are different types of triggers in SQl SERVER 2000 ?
There are two types of triggers :-
√ INSTEAD OF triggers
INSTEAD OF triggers fire in place of the triggering action. For example, if an INSTEAD OF UPDATE trigger exists on the Sales table and an UPDATE statement is executed against the Salestable, the UPDATE statement will not change a row in the sales table. Instead, the UPDATE statement causes the INSTEAD OF UPDATE trigger to be executed, which may or may not modify data in the Sales table.
√ AFTER triggers
AFTER triggers execute following the SQL action, such as an insert, update, or delete.This is the traditional trigger which existed in SQL SERVER. INSTEAD OF triggers gets executed automatically before the Primary Key and the Foreign Key constraints are checked, whereas the traditional AFTER triggers gets executed after these constraints are checked. Unlike AFTER triggers, INSTEAD OF triggers can be created on views.
If we have multiple AFTER Triggers on table how can we define the sequence of the triggers ?
If a table has multiple AFTER triggers, then you can specify which trigger should be executed first and which trigger should be executed last using the stored procedure sp_settriggerorder. All the other triggers are in an undefined order which you cannot control.
What is SQl injection ?
It is a Form of attack on a database-driven Web site in which the attacker executes unauthorized SQL commands by taking advantage of insecure code on a system connected to the Internet, bypassing the firewall. SQL injection attacks are used to steal information from a database from which the data would normally not be available and/or to gain access to an organization’s host computers through the computer that is hosting the database.

SQL injection attacks typically are easy to avoid by ensuring that a system has strong input validation. As name suggest we inject SQL which can be relatively dangerous for the database. Example this is a simple SQL

SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'x'

Now somebody does not put “x” as the input but puts “x ; DROP TABLE members;”. So the actual SQL which will execute is :-

SELECT email, passwd, login_id, full_name FROM members
WHERE email = 'x' ; DROP TABLE members;

Asp.net IQ

asp.net IQ

Question:-What do you mean by three-tier architecture?
Answer: The three-tier architecture was comes into existence to improve management of code and contents and to improve the performance of the web based applications.There are mainly three layers in three-tier architecture.the are define as follows
(1)Presentation
(2)Business Logic
(3)Database
(1)First layer Presentation contains mainly the interface code, and this is shown to user. This code could contain any technology that can be used on the client side like HTML, JavaScript or VBScript etc.
(2)Second layer is Business Logic which contains all the code of the server-side .This layer have code to interact with database database and to query, manipulate, pass data to user interface and handle any input from the UI as well.
(3)Third layer Data represents the data store like MS Access, SQL Server, an XML file, an Excel file or even a text file containing data also some addtional database are also added to that layers.
Question:-How many types of cookies are there in .NET ?
Answer: Two type of cookeies.
a) single valued eg request.cookies(”UserName”).value=”dotnetquestion”
b)Multivalued cookies. These are used in the way collections are used example
request.cookies(”CookiName”)(”UserName”)=”dotnetquestionMahesh”
request.cookies(”CookiName”)(”UserID”)=”interview″

Question: What is Finalizer in .NET define Dispose and Finalize ?
Answer: We can say that Finalizer are the methods that's helps in cleanp the code that is executed before object is garbage collected .The process is called finalization . There are two methods of finalizer Dispose and Finalize .There is little diffrenet between two of this method .
When we call Dispose method is realse all the resources hold by an object as well as all the resorces hold by the parent object.When we call Dispose method it clean managed as well as unmanaged resources.
Finalize methd also cleans resources but finalize call dispose clears only the unmanged resources because in finalization the garbase collecter clears all the object hold by managed code so finalization fails to prevent thos one of methd is used that is: GC.SuppressFinalize.
Question: Define SMTPclient class in DotNet framework class libarary ?
Answer: Each classes in dotnet framework inclue some properties,method and events.These properties ,methods and events are member of a class.SMTPclient class mainly concern with sending mail.This class contain the folling member.
Properties:-
Host:-The name or IP address of email server.
Port:-Port that is use when sending mail.
Methods:-
Send:-Enables us to send email synchronously.
SendAsynchronous:-Enables us to send an email asynchronously.
Event:-
SendCompleted:-This event raised when an asynchronous send opertion completes.

Question: When we get Error 'HTTP 502 Proxy Error' ?
Answer: We get this error when we execute ASP.NET Web pages in Visual Web Developer Web server, because the URL randomly select port number and proxy servers did not recognize the URL and return this error. To resolve this problem we have to change settings in Internet Explorer to bypass the proxy server for local addresses, so that the request is not sent to the proxy.
Question: What is late binding ?
Answer: When code interacts with an object dynamically at runtime .because our code literally doesnot care what type of object it is interacting and with the methods thats are supported by object and with the methods thats are supported by object .The type of object is not known by the IDE or compiler ,no Intellisense nor compile-time syntax checking is possible but we get unprecedented flexibilty in exchange.if we enable strict type checking by using option strict on at the top of our code modules ,then IDE and compiler will enforce early binding behaviour .By default Late binding is done.

Question:-What is Com Marshler and its importance in .NET ?
Answer: Com Marshler is one of useful component of CLR. Its Task is to marshal data between Managed and Unmanaged environment .It helps in representation of data accross diffrenet execution enviroment.It performs the conversion of data format between manage and unmanaged code.By the helps of Com Marshlar CLR allows manage code to interoperate with unmanaged code.
Question: What is CSU and its description ?
Answer: CSU stands for comma separate values also called comma delimited.It is plain text file which stores spreadsheets or basic datatype in very simple format.One record in each line and each field separted with comma's it is often used to transfer large ammount spreadsheet data or database information between program.

Question: The IHttpHandler and IHttpHandlerFactory interfaces ?
Answer: The IHttpHandler interface is implemented by all the handlers. The interface consists of one property called IsReusable. The IsReusable property gets a value indicating whether another request can use the IHttpHandler instance. The method ProcessRequest() allows you to process the current request. This is the core place where all your code goes. This method receives a parameter of type HttpContext using which you can access the intrinsic objects such as Request and Response. The IHttpHandlerFactory interface consists of two methods - GetHandler and ReleaseHandler. The GetHandler() method instantiates the required HTTP handler based on some condition and returns it back to ASP.NET. The ReleaseHandler() method allows the factory to reuse an existing handler.

Question: what is Viewstate?
Answer:View state is used by the ASP.NET page framework to automatically save the values of the page and of each control just prior to rendering to the page. When the page is posted, one of the first tasks performed by page processing is to restore view state.
State management is the process by which you maintain state and page information over multiple requests for the same or different pages.
Client-side options are:
* The ViewState property * Query strings
* Hidden fields * Cookies
Server-side options are:
* Application state * Session state * DataBase
Use the View State property to save data in a hidden field on a page. Because ViewState stores data on the page, it is limited to items that can be serialized. If you want to store more complex items in View State, you must convert the items to and from a string.
ASP.NET provides the following ways to retain variables between requests:
Context.Handler object Use this object to retrieve public members of one Web form’s class from a subsequently displayed Web form.
Query strings Use these strings to pass information between requests and responses as part of the Web address. Query strings are visible to the user, so they should not contain secure information such as passwords.
Cookies Use cookies to store small amounts of information on a client. Clients might refuse cookies, so your code has to anticipate that possibility.
View state ASP.NET stores items added to a page’s ViewState property as hidden fields on the page.
Session state Use Session state variables to store items that you want keep local to the current session (single user).
Application state Use Application state variables to store items that you want be available to all users of the application.
Question: DOTNET PAGE LIFECYCLE ?
Answer: While excuting the page, it will go under the fallowing steps(or fires the events) which collectivly known as Page Life cycle.
Page_Init -- Page Initialization
LoadViewState -- View State Loading
LoadPostData -- Postback data processing
Page_Load -- Page Loading
RaisePostDataChangedEvent -- PostBack Change Notification
RaisePostBackEvent -- PostBack Event Handling
Page_PreRender -- Page Pre Rendering Phase
SaveViewState -- View State Saving
Page_Render -- Page Rendering
Page_UnLoad -- Page Unloading

Question: What is Satellite Assemblies ?
Answer: Satellite assemblies are often used to deploy language-specific resources for an application. These language-specific assemblies work in side-by-side execution because the application has a separate product ID for each language and installs satellite assemblies in a language-specific subdirectory for each language. When uninstalling, the application removes only the satellite assemblies associated with a given language and .NET Framework version. No core .NET Framework files are removed unless the last language for that .NET Framework version is being removed. For example, English and Japanese editions of the .NET Framework version 1.1 share the same core files. The Japanese .NET Framework version 1.1 adds satellite assemblies with localized resources in a \ja subdirectory. An application that supports the .NET Framework version 1.1, regardless of its language, always uses the same core runtime files.

Question: What is CAS ?
Answer:CAS: CAS is the part of the .NET security model that determines whether or not a piece of code is allowed to run, and what resources it can use when it is running. For example, it is CAS that will prevent a .NET web applet from formatting your hard disk. How does CAS work? The CAS security policy revolves around two key concepts - code groups and permissions. Each .NET assembly is a member of a particular code group, and each code group is granted the permissions specified in a named permission set. For example, using the default security policy, a control downloaded from a web site belongs to the 'Zone - Internet' code group, which adheres to the permissions defined by the 'Internet' named permission set. (Naturally the 'Internet' named permission set represents a very restrictive range of permissions.)
Question: Automatic Memory Management ?
Answer: Automatic Memory Management: From a programmer's perspective, this is probably the single biggest benefit of the .NET Framework. No, I'm not kidding. Every project I've worked on in my long career of DOS and Windows development has suffered at some point from memory management issues. Proper memory management is hard. Even very good programmers have difficulty with it. It's entirely too easy for a small mistake to cause a program to chew up memory and crash, sometimes bringing the operating system to a screeching halt in the process.

Programmers understand that they're responsible for releasing any memory that they allocate, but they're not very good at actually doing it. In addition, functions that allocate memory as a side effect abound in the Windows API and in the C runtime library. It's nearly impossible for a programmer to know all of the rules. Even when the programmer follows the rules, a small memory leak in a support library can cause big problems if called enough.

The .NET Framework solves the memory management problems by implementing a garbage collector that can keep track of allocated memory references and release the memory when it is no longer referenced. A large part of what makes this possible is the blazing speed of today's processors. When you're running a 2 GHz machine, it's easy to spare a few cycles for memory management. Not that the garbage collector takes a huge number of cycles--it's incredibly efficient.
The garbage collector isn't perfect and it doesn't solve the problem of mis-managing other scarce resources (file handles, for example), but it relieves programmers from having to worry about a huge source of bugs that trips almost everybody up in other programming environments.
On balance, automatic memory management is a huge win in almost every situation.

Question: What Language familar to CLR?
Answer: Any language that can be compiled into Microsoft Intermediate Language (MSIL) is considered a .NET-compliant language. Following are a few of the popular .NET-compliant languages supported by CLR:

Asp Net Web Services IQ

Asp Net Web Services

Asp Net Web Services: Here are some web services interview questions which is very helpful in interviews. This great listing of interview questions provides a lot of information on Web Services Questions and Web Services Faqs. Not at all, instead of web services faqs and interview questions you can also learn how to use web services in asp net. If you need more web services faqs used in asp net you can also contact us for more info.
Question: Can you define basic element of WebServices and explain any one from them?
Answer: These are as follows SOAP, WSDL and UDDI. And I am explaining about the SOAP(Simple Object Access Protocol) it is a communication protocol it is for communication between application it is platform and language independent.It is based on XML and also help to get from Firwall.

Question: Explain functioning of Web Services Protocols ?
Answer: Http-Get:- This is standard protocol that helps client to communicate with server with HTTP.When client send a request to server via HTTP request and reuired parameter are attached with the querystring.Example:-
http://www.dotnetquestion.info/dotnet/interview.aspx?id=pervej&cast=munjal and we get the value from querystring.
Request.querystring("id")
Request.querystring("cast").

Http-Post:-This is same as Http-Get but the diffrence is that in place of sending parameters onto the URL information is send with HTTP request message with some extra information which contains Parameters and their values.This Protocols is limited to sending name/value pairs.
SOAP:-The only diffrence is that its relies on the XML as compares to Http-Get,Http-Post.SOAP can send not only the name/value pairs but also some complex object also as for example datatypes,class,objects.SOAP can also uses request/reponse model as Http-Get,Http-post but it is not limited to Request/Response it can also send types of message.Because its uses XML that is pure text so firewalls not created so much problem because its easily converted in to HTML. .

Question: Why do some web service classes derive from System.Web.WebServices while others do not ?
Answer: Those asp net Web Service classes which employ objects like Application, Session, Context, Server, and User have to derive from System.Web.WebServices. If it does not use these objects, it is not necessary to be derived from it.

Question: What is WSDL ?
Answer: WSDL is an XML format for describing network services as a set of endpoints operating on messages containing either document-oriented or procedure-oriented information. The operations and messages are described abstractly, and then bound to a concrete network protocol and message format to define an endpoint. Related concrete endpoints are combined into abstract endpoints (services). (Source: www.w3.org).

Question: What is the standard you use to wrap up a call to a Web service ?
Answer: HTTP with SOAP.

Question: What is Asp Net Web Services ?
Answer: Web services are programmable business logic components that provide access to functionality through the Internet. Standard protocols like HTTP can be used to access them. Web services are based on the Simple Object Access Protocol (SOAP), which is an application of XML. Web services are given the .asmx extension..

Question: Define Protocols that helps Web Services in Asp Net?
Answer: From my point of view Web Services used three protocols for interacting with the clients.Http-Post,Http-Get,SOAP.

Question: What are VSDISCO files ?
Answer: VSDISCO files are DISCO files that enable dynamic discovery of Web Services. ASP.NET links the VSDISCO to a HTTP handler that scans the host directory and subdirectories for ASMX and DISCO files and returns a dynamically generated DISCO document. A client who requests a VSDISCO file gets back what appears to be a static DISCO document.

Question: What is UDDI ?
Answer: UDDI stands for Universal Description, Discovery, and Integration. It is like an "Yellow Pages" for Web Services. It is maintained by Microsoft, IBM, and Ariba, and is designed to provide detailed information regarding registered Web Services for all vendors. The UDDI can be queried for specific Web Services.

Question: Is it possible to generate the source code for an ASP.NET Web service from a WSDL ?
Answer: The Wsdl.exe tool (.NET Framework SDK) can be used to generate source code for an ASP.NET web service with its WSDL link.
Example: wsdl /server http://api.google.com/GoogleSearch.wsdl.

Question: Can you give an example of when it would be appropriate to use a web service as opposed to a non-serviced .NET component ?
Answer: When to Use Asp Net Web Services:
(i)Communicating through a Firewall When building a distributed application with 100s/1000s of users spread over multiple locations, there is always the problem of communicating between client and server because of firewalls and proxy servers. Exposing your middle tier components as Web Services and invoking the directly from a Windows UI is a very valid option.

(ii)Application Integration When integrating applications written in various languages and running on disparate systems. Or even applications running on the same platform that have been written by separate vendors.

(iii)Business-to-Business Integration This is an enabler for B2B intergtation which allows one to expose vital business processes to authorized supplier and customers. An example would be exposing electronic ordering and invoicing, allowing customers to send you purchase orders and suppliers to send you invoices electronically.

(iv)Software Reuse This takes place at multiple levels. Code Reuse at the Source code level or binary componet-based resuse. The limiting factor here is that you can reuse the code but not the data behind it. Webservice overcome this limitation. A scenario could be when you are building an app that aggregates the functionality of serveral other Applicatons. Each of these functions could be performed by individual apps, but there is value in perhaps combining the the multiple apps to present a unifiend view in a Portal or Intranet.

Question: What are Service Oriented Architectures (SOA) ?
Answer: SOA describes an information technology architecture that enables distributed computing environments with many different types of computing platforms and applications. Web services in asp net are one of the technologies that help make SOAs possible. As a concept, SOA has been around since the 1980s, but many early IT technologies failed to achieve the goal of linking different types of applications and systems. By making early investments with .NET, Microsoft has helped provide the building blocks that today are putting many enterprise customers on the path to successfully implementing SOAs. With SOAs, companies can benefit from the unimpeded flow of information that is the hallmark of connected systems.

Question: What is .NET Passport ?
Answer: .NET Passport is a Web-based service that is designed to make signing in to Web sites fast and easy. Passport enables participating sites to authenticate a user with a single set of sign-in credentials, alleviating the need for users to remember numerous passwords and user names.

Stored Procedures Interview Questions

Stored Procedures in SQL Server

Microsoft SQL Server provides the stored procedure mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks.

Benefits

• Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
• Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
• Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
• Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.

Structure

Stored procedures are extremely similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result. This result is returned to the calling environment through the use of a recordset, output parameters and a return code. That may sound like a mouthful, but you'll find that stored procedures are actually quite simple. Let's take a look at a practical example

Example

Assume we have the following table named Inventory:

This information is updated in real-time and warehouse managers are constantly checking the levels of products stored at their warehouse and available for shipment. In the past, each manager would run queries similar to the following:

SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = 'FL'

This resulted in very inefficient performance at the SQL Server. Each time a warehouse manager executed the query, the database server was forced to recompile the query and execute it from scratch. It also required the warehouse manager to have knowledge of SQL and appropriate permissions to access the table information.

We can simplify this process through the use of a stored procedure. Let's create a procedure called sp_GetInventory that retrieves the inventory levels for a given warehouse. Here's the SQL code:

CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location

Our Florida warehouse manager can then access inventory levels by issuing the command

EXECUTE sp_GetInventory 'FL'

The New York warehouse manager can use the same stored procedure to access that area's inventory.

EXECUTE sp_GetInventory 'NY'

Granted, this is a simple example, but the benefits of abstraction can be seen here. The warehouse manager does not need to understand SQL or the inner workings of the procedure. From a performance perspective, the stored procedure will work wonders. The SQL Sever creates an execution plan once and then reutilizes it by plugging in the appropriate parameters at execution time.

Now that you've learned the benefits of stored procedures, get out there and use them! Try a few examples and measure the performance enhancements achieved -- you'll be amazed!

Create an ASP SQL Stored Procedure

Stored Procedures are very powerful database tools. They are essentially mini-programs and using SQL you can achieve many of the programmatic tricks you might use in an ASP page, including loops, if else statements and so on.

The useful thing about stored procedures is that they separate out the database activity from the displaying. This keeps your ASP pages very simple and makes maintenance a lot easier.

Here I'll introduce you to the basics of using a stored procedure and passing a parameter to it.

CREATING THE STORED PROCEDURE

Open Enterprise Manager, and open your database in the tree structure. Right click on Stored Procedures and select 'New Stored Procedure'

Let's create a procedure called getproducts

Here we have created a variable called

@product_id

and passed it to our SQL select statement. All variables have the @ sign before them in SQL language.

Now, from within your ASP page, you can call this procedure in a number of ways.

As the procedure in this case is a select statement, you will most likely want to receive the results into a recordset:

If however the procedure was an update statment - for example,

update products set delivered=1 where product_id=@product_id

then you might want simply to execute the procedure as follows:

What is the difference between "is" and "as" while creating procedure. Ex:- create procedure is or as?

Both are same.

There is no difference as such, We use AS when Stored Procedure is defined in a package if SP is outside pkg we use IS. Here is the exampleEx: ASPakcage Headercreate package schema.testpkg

Can you call procedure in functions

Yes , we can. Here is simple example.

CREATE OR REPLACE PROCEDURE TEST_PROC(V_NUM OUT NUMBER) AS
BEGIN
V_NUM := 10;
END;
/

CREATE OR REPLACE FUNCTION TEST_FUNC RETURN NUMBER AS
V_NUM NUMBER;
BEGIN
TEST_PROC(V_NUM);
RETURN V_NUM;
END;
/

SELECT TEST_FUNC() FROM DUAL;

OUTPUT
1
0

What is the difference between "stored procedure" and "dynamic SQL" ?

Stored procedures are stored in data base in complied form. In dynamic

sql the statements are dynamically constructed at run time.

Stored Procedures are compiled and runs on the server. dynamic SQL are not compiled and runs on client.

Stored Procedures Interview Questions

Showing Questions 1 - 19 of 19 Questions

Sort by:

Answers

Date

SQL-transaction in nested stored procedure

Asked By: nikhiljain27 | Asked On: Jul 13th, 2013

1 answer

Can we use SQL-transaction in nested stored procedure(stored procedure calling from stored procedure)? one SQL-transaction object in primary sp. second SQL-transaction object in nested sp(second sp).

Answered by: Akshay Jain on: Jan 29th, 2014

Yes U can... I till behave like a normal transaction

Tuesday, 22 April 2014

Sunday, 6 April 2014

Stored Procedures Interview Questions

Editorial / Best Answer