Explain transactions and transaction modes in sql server?

A transaction is a set of dml operations which must be make perminent as a whole or must be undone as a whole

Sql server runs in following transaction modes.

1.Autocommit
2.Implicit transaction mode.
3.Explicit transaction mode.

AutoCommit: If sql server runs in autocommit mode then the transactionwill be started automatically when user submits dml command and transaction ends with commit automatically.
In Autocommit mode user cannot control transactions


Implicit Transaction mode : In this transaction starts automatically with any dml command but transaction ends with commit or rollback

Ex

updaet or sql statement--transaction starts
commit/rollback -- transaction ends

Explicit transaction mode : In explicit transaction mode the transaction starts with 'begin transaction' command and the transaction ends with 'commit transaction' or 'rollback transaction'

begin transaction
update statement
commit/rollback transaction


set implicit_transaction on --> it sets the implicit transaction mode on.

Explain about the cursor?

cursors are used to process multiple records(row by row processing)

A cursor is a pointer or handler to context area which stores records returned select statement.

Using cursors in a single request we can get required data from server can be loaded into client side temporary memory called context area.

Steps to create a cursor
1.declare a cursor

syntax: declare cursorname cursor for select statement.
Ex
Declare c1 cursor for select eno, ename, sal from emp

Some memory is created or allocated when we declare a cursor. that memory is called context area.

2. Open a cursor

Syntax: open <cursor name>
open c1;

when we open a cursor.
1.select statement is submitted to db server.
2dbserver executes the select statement and returns data and data returned by select statement is loaded into context area.
3.cursor will point to a context area.

3.Fetch the records from cursor

Syntax:
Fetch first\next\last\prior\absolute\relative n from <cursorname> into variables

Ex

Fetch next from c1 into @eno, @sal, @comm

Fetch statement can fetches only one record at a time to process multiple records fetch should be executed number of times
Fetch statement should be placed inside a loop.
4.Closing a cursor

syntax:
close cursorname;
ex
close c1;

5. Deallocate cursor

When the cursor is closed the point to context area is removed.
when the cursor is de allocated the memory allocated for cursor is released.

deallocate cursorname
deallocate c1;


Ex

declare @ename varchar(50)
declare @sal smallmoney

declare c1 cursor for select ename,sal from emp
open c1
fetch next c1 into @ename,@sal
while(@@Fetch_status=0)
begin
print @ename +'earns' +cast(@sal as varchar(20))
Fetch next from c1 into @ename, @sal
end
close c1
deallocate c1

Differences between clustered index and non clustered index in sql server?

Non-Clustered Index : If the order of values in index and order of values in the table are not same then it is called non clustered index.

Clustered Index: If the order of records in the table and order of the records in the index is same then it is called clustered index.

Non-Clustered Index : Non clustered index will be created automatically one you create unique key on a column.

Clustered Index : Clustered index will be automatically once you create a primary key.

We can have only one clustered index for a table.

we can create up to 249 non clustered indexes on a table.

Non-Clustered Index : The logical order of the index does not match the physical stored order of rows on disk.

Clustered Index : The logical order of the index matches the physical stored order of rows on disk.

Clustered Index is faster than non-clustered index. 
Because the leaf level of a clustered index is the actual 
data and the data is resorted in case of clustered index. 
In case of non-clustered index the leaf level is actually a 
pointer to the data in rows. The data is stored in one 
place, the index in another, with pointers to the storage 
location of the data. The items in the index are stored in 
the order of the index key values, but the information in 
the table is stored in a different order.

What is the trigger and explain the types of triggers?

A trigger is an operation that is executed when some kind of event occured in database. It can be an object or data change.

Types of triggers

DML Triggers

     After Triggers(only on table)

     Instead of triggers(table and views)

DDL Triggers

SQL CLR triggers

 After Trigger: If the trigger is instead of trigger then sql server executes trigger body instead of executing the DML operation.

Ex

create trigger trg1 on emp

after insert,update,delete

as

begin

if datename(dw,getdate())='sunday'

begin

rollback

raiseerror('Today is sunday',15,1)

end

end

Instead of trigger : If the trigger is instead of trigger then sql server executes trigger body instead of executing the DML operation.

CREATE TRIGGER INSTEADOF_TR_I_EmpQualification 
ON vw_EmpQualification
INSTEAD OF INSERT AS
BEGIN

DECLARE @Code TINYINT
SELECT @Code = qualificationCode 
FROM lib_Qualification L INNER JOIN INSERTED I
ON L.qualification = I.qualification

IF (@code is NULL )
BEGIN
RAISERROR (N'The provided qualification does not exist in qualification library',
16, 1)
RETURN
END

INSERT INTO employees (empcode, name, designation,qualificationCode,deleted) 
SELECT empcode, name, designation, @code, 0 
FROM inserted 
END
GO

Rules of triggers

cannot create or modify dbobjects.

cannot perform administrative tasks

cannot pass any parameters

cannot directly call triggers.

Advantages of triggers

-----------------------

Triggers are useful for auditing data changes or auditing database as well as managing the business rules.

Can access both new values and old values in data base when going to do any insert, update and delete.

Disadvantages of triggers

Triggers hide db operations

It effects performance

DDL triggers can be created in database or server. If you want to monitor table creations and drops you can use ddl triggers

Joins available in sql server.

Equi join or inner join
Non-equi join
Self join
Outer join
Cross join

Inner join : join the table based on the common column(s) available in both the tables.
ex
select e.ename,d.dname from emp e inneroin department d on d.deptno=e.deptno

non-equi join: joining 2 tables not based on the common column.
ex
select e.name,g.grade from emp e innerjoin grade g on e.sal between e.lowsal and e.highsal

self join: Joining a table to itself is known as self join
it is performed if it has self referential intigrity

ex

select x.ename,y.ename as mgrname from emp x join emp y on x.managerid=y.empid

outer join : returns unmatched records from the tables.
Left outer join and right outer join

cross join: It returns cross product of 2 tables.
if we submit the select statement with out join condition then sql server performs cross join.

select e.ename, d.dname from emp e dept d

What are the acid properties in sql server?

In sql server every transaction should follow acid properties
Atomicity
Consistency
Isolation
Durability

Atomicity: transaction ensures that either modifications are committed or not committed.
Consistency: The data should be in consistent state when transaction process is completed.
Isolation: A transaction work in isolation and does not allow other transactions to work concurrently on same piece of work.
Durability: Data is permanent once transaction is completed  and it can be recovered when system fails.

what are the differencces between where and having clause

where clause
To filter data before 'group by' use where clause.
To select particular groups use where clause.
If condition does not have aggregate functions use where clause.

Having Clause
To filter data after group by use having clause.
To select particular groups use having clause.
Used to apply functions on aggregate functions.

Date functions available in sql server

Day(getdate())-- Get the current day
Month(Getdate())--Get the current month.
Year(Getdaet())-- Gets the current year

Datepart(interval,date)

Intervals are "yy,  mm, dd, hh,  m1, ss, dw(Day of week).

select datepart(dw,Getdate()) output is "1"

select datename(dw, Getdate()) output is "monday"

DataAdd() function
Used to add years or months to particular date.

DateAdd(yy,1,getdate()) --It adds 1 year to current year.

DateDiff(interval,date1,date2): returns difference between 2 dates.
datediff(yy,getdate(),'11-feb-2012');

Updatin and deletion rules in sql server

Delete rules
On delete No action.
On delete set null.
On delete cascade.
On delete set default.

These rules coming into when we define the parent child relationship between 2 tables in sql server.

we need to define the rule while creating the child table. I mean to say while defining the foreign key we need to define the rules. the same thing applies for updation also

ex
Create table emp(eno int primarykey, ename varchar(50), Dno int references dept(dno) on delete no action)


Updation Rules
On update No action.
On update set null.
On update cascade.
On update set default.

What is self referential intigrity in sql server?

A foreign key in one table refers to primary key of same table is known as self referential intigrity.

ex:

Create table emp(eno int primary key, ename varchar(50), Manager int references emp(empno).

copying data from one table to another table

Insert into <destination table name> select * from <source Name>
ex:
insert into Emp_History select * from Employee.

Here 2 tables must be exists.

Create a table from another table 


select * into <newtable> from <old table>re  [where condition]

ex:
Create new table from existing table along with same data

select * into emp_backup from emp;

Create only structure from the existing table

select * into newemp from emp where 1=2;

Order of declaration and execution od a sql statement

Order of declaration
select
from
where
groupby
having
orderby

Order of execution

From
where
groupby
having
selecct
orderby

What is ABC of an Endpoint in WCF?

Endpoint: It is a communication point where a client can communicate with the service by making use of endpoint.

Endpoint must be existing in the client as well as in service.

Address(Where): Address indicates where we can find this service. Address is a url which points location of service.

Binding(How): It specifies message patterns, security and channel information which are required to access the service.

Contract(What): It specifies what exactly service does. It describes parameters and return values for a method.

what is wsdl, soap, Disco, UDDI in SOA(Service oriented applications)?

WSDL(Web Service Description Language): Used to create interface definitions for web service it says to clients what methods are available in service.

SOAP(Simple object Access Protocol): It is a Xml based language and used to create request and response messages.

DISCO(Discovery) : Used to create documents that provide links to multiple web service endpoints.

UDDI(Universal discovery, Description and integration): A standard for creating business registries that catalog companies, the web services they provide and corresponding urls wsdl documents.

Explain contracts in wcf?

Contract is an agreement between 2 or more parties. It defines how client should communicate with your service. Technically it describes parameters and return values for a method.

Contract is a platform neutral and standard way of describing what a service does

There are 4 types of contracts.
1.Service contract.
2.Message contract.
3.Data contract.
4.Fault contracts.

Service contract:It provides list of operations provided by a service.A client can make use of operations with the help of service contract.
End point provides implementation regarding service contract. each operation in service contract must be defined as operation contract.

Message Contract: Default soap message will be provided by wcf run time for communication between client and service. If it is not meeting your requirement then we can create our own message format.

Operation Contract: It describes which methods should be exposed as a service by using the service.

Data Contract: It defines which types of complex data will be exposed between the client and service. they determine which parameters to be serialized. wcf implicit contracts for built in types such as int and string but we can easily define explicit operation contract for custom types.
DataContract  --Attribute used to define the class
DataMember -- Attribute used to define properties

WCF uses xml, xsd, soap and binary formats as transmission languages. you can choose any one of them.


Fault Contracts: define which errors to be raised by the service and how the service handles and propagate errors to clients.

Explain Message and message patterns in wcf?

Message: It is a packet of information that can be exchange message in the form of soap message.

The soap message is transmitted in the form of soap envelop.

Message patterns
 
Simplex : Client sends a message and never expects a rsponse.
Duplex: Client and service can communicate each other with out waiting for others to respond.
Request & Reply: Client send a message and it will wait until services gives response.

Differences between WCF and web service?

Web services can be hosted on IIS.
WCF can be hosted in IIS, Windows Activation services, self hosting and windows service.
[Web service] attribute has to be added to class in web services.
[Service contract] attribute has to be added to class in wcf.
[web method] attribute represents the method exposed to client in web service.
[Operation contract] attribute represents the method exposed to client in wcf.
One-way,Request-Response are different operations supported by web service.
One-way, Request-response, Duplex operations supported by wcf services.
System.Xml.Serialization namespace is used for serialization in web service.
System.Runtime.Serialization namespace is used for serialization in wcf.
web service uses security protocol.
Security, reliable, messaging, Transaction protocols are used in wcf.

wcf and its advantages

WCF is a unified programming model provided in .net framework3.0  wcf is a combination feature of web services, .Net remoting, MSMQ and com+.

By using wcf we can implement a service which is of language independent, platform independent, application independent and protocol neutral.

WCF is a collection of operations where we can find at least one end point with complete service description.

wcf advantages
Interoperability is fundamental characterstic of wcf.
It provides better reliability and security compared to asmx.
No need to make code changes in code for implementing security model and changing binding. small changes in configuration will make your requirement.
It has integrated logging mechanism, changing configuration will provide this functionality.

Explain Authentication and authorization

Authentication: The process of verifying user credentials against a data source and create an identity is known as authentication.

Authorization : The process of checking whether the requested resource is allowed or denied for an identity is known as authorization.

All about assemblies.

An assemble is a file which contain IL(Intermediate language) code corresponds to a project.
It is also known as unit of deployment.

2 types
.exe--In process components which are capable of running on their own(Windows application).
.dll---Out process components which are not capable of running on their own(classs library)

Assembles are 2 types.
1.Private assembly
2.Shared assembly.

Shared assembly: The assemblies which are stored under the centralized location GAC(Global Assembly Cache) is known as shared assembly.

An assembly which contains 3 attributes like name,version,public key token are known as strong named assembly.

If you wish to include your assembly into GAC you need to create a public key token for that

Generating Public key token

sn -k filename
ex
sn -k key.snk.

copying assembly into GAC
hacutil -i/-u <assemblyname>  i-Install, -u-uninstall

What are the methods available in SqlCommand object??

ExecuteScalar()---> Executes 'select' statement which will returns  data in the form of rows and columns.

ExecuteScalar()--> Executes a 'select' statement which will returns single value.

ExecuteNonquery()-->Executes the DML(Data manipulation) statements and returns how many records get effected.

ExecuteXMLReader()--> Executes 'select' statement and returns data in the format of xml

System.Xml.XmlReader xmlr = cmd.ExecuteXmlReader();

What are the basic concepts(Features) of OOPs??

These are the basic concepts(Features) of OOPS.
1.Inheritance.
2.Polymorphism
3.Encapsulation.
4.Data Abstraction.
5.Reusabilty

In addition to these 5 we have Class,Objects,Message passing and delegation. these 5 are importent concepts

Inheritance: It is an approach which allows accessing members defined in a class from another class by establishing parent child relation ship.

Ex: In .Net Object class is parent class for all the classes.


Polymorphism: "entities behaves differently depending on the input they receive" is known as polymorphism.

Overloading and overriding com under polymorphism

Method Overloading: Defining same method name with the different signatures are known as overloading.
The changes in signature can be any of following.

1.Change in number of methd being passed to a method.
2.Change type of parameters being passed to the method.
3.Chane order of parameters being passed to a method.

Public void YourMethodName()
Public void YourMethodName(int x)
Public void YourMethodName(int x, string y)
Public void YourMethodName( string y,int x)

 Simply it is an approach which allows you to provide multiple behaviors to a method.


Method Overriding : A method defined in parent class is redefine under its child class with same signature and same parameters is known a s method overriding.

Public virtual void Display(int x)  -- Parent
{
//code
}


Public override void Display(int x) --Child
{
//Code
}

Here when you call Display method using child object it will calls the childs implementation

If you want to override a method you must define the method name in parent class with the virtual keyword.

Method Hiding

Rewriting a parent class method under the child class with out permission of parent is known as method hiding.

public void Show()   -------Parent
{
}


Public new void Show()--Child
{
//Code
}

Encapsulation: The process of wrapping up the data into single unit is known as Encapsulation.

Ex. Class is the best example because it hides every thing from outside.

DataAbstraction: It is the processing of representing essential feature with out representing the background details.

Ex. For a car driver he only needs to know about the how steering, accelerator,Gears and breaks work. He does not need to about how internal car engine works.

Explain state management feature in asp.net???

Session Management is a process by which you can maintain state and page information over multiple requests for the same or different pages.

The available option for session Management are

ClientSide
1.viewstate
2.Hiddenfiels
3.Cookies.
4.Querystrings

ServerSide
1.Application State
2.SessionState
3.Profile services

 Client side options store the information into a web page, a uniform resource locator or a cookie.

--I am not going to explain all the above listed options because that would be known by every one if he knows the .net

What is the difference between ResolveUrl() and ResolveClientUrl()??

ResolveUrl()-Returns url relative to site root using controls template source directory property.
ResolveClientUrl: returns url relative to the folder containing the source file in which controls are instantiated.

Prefer to use ResolveUrl()

What is the difference between Session.abondon and sessiom.Clear??

Session.Abondon() will end current session by firing Session_end
Session.Clear(): It just clears the sessions with out killing it.

What is smart navigation??

The cursor position is maintained when the page gets refreshed due to server side validation and the page gets refreshed.

What is caching

It is the technique of storing an in memory copy of some information that is frequently used items in server memory and expensive to create (Interms of performance).

Types of caching
Page output caching.-- setting at the page level
Data Caching.-- Caching the simple variable as we place in session
Fragment Caching. -- It means caching a portion of page(Usually a user controls data).
Data Source Caching.-- Some data source like sql data source can support caching.

Cache objects have 2 types of expirations.

Absolute expiration.
Sliding expiration.

Absolute Expiration: A date time object that identifies when the data should be removed fro the cache.
If you want to use sliding expiration then specify cach.NoAbsoluteexpiration.

Sliding Expiration. A time span object how much the data should be in cache.


Scevaning : It is process of removal of cache items from the cache if server memory is very low . Here it will remove the items based on the priority.

Cache dependency
1. Dependencies on other cache items
2.Dependencies on files and folders.
3.Dependencies on db query.



"OnRemoveCallBack" delegate in the application is used to notify when items are removed from the cache.

What is webfarm and webgarden

A web application runs on a single server that has multiple cpus is called webgarden.

A web application running on multiple servers is called a webfarm.


In both webfarms and web gardens client requests are redirected to asp.net process that is currently busy that means a single client can interact with the different servers over the course of his or her session.



Session state variables are by default stores inproc(within server memory) state by default..

2 built in ways to share session state information across webgarden or web farm.
1.StateServer : Simple to implement and does not require to install sql server
2.Sql Server

Differences between Server.Transfer and Response.Redirect

Server.Transfer can navigates you to the pages which are available in your application where as Response.Redirect can navigates you to external applications also.

When we use server .Transfer the redirection happens on server where as in case Response.Redirect redirection happens in browser.

Server.Transfer is faster as there is no round trip while navigating from one page to another.

Cookies in ASP.net

Cookie : Cookie is a small key value pair which is used to store the small piece of user information.
2 types are cookies are available in asp.net.
Session Cookie and persistent cookie
Session Cookie: These are stored in in-memory during the client browser session when the browser is closed the session cookies are lost.
Ex
HttpCookie sCookie=New HttpCookie("uname","Anji");
Response.cookies.add(sCookie);

//Code to read the cookie
string name= Request.cookies["uname"].value;

Persistent cookies are same as session cookies except that persistent cookies have an expiration date.These are generally used to store info that identifies a returning user to web site like user name or logid.

Creation of persistent cookies
HttpCookie pCookie=New HttpCookie("uname","Anji");
pCookie.Expires=DateTime.Now.AddDays(10);
Response.cookies.add(pCookie);


Cookie Dictionary : It is a single cookie object which can store multiple pieces of information You can use values properties to access and assign new values to cookiedictionary.

Advantages:
Easy to implement.
Does not require server resources.

Dis Advantages:
User can delete the cookie.
Browser can refuse the cookies.
Cookies are not secure.
Any one can view your cookie data once he logged in into your machine.

ASP.net page life cycle.

1)PreInit: The entry point of page life cycle is the pre-initialization phase called preinit. This is only event where you can access master page and themes programatically. You can dynamically set values of master pages and themes in this event. You can also create dynamic controls in this event.
Preinit()
{
//Set master pages and themes dynamically
}

2)Init(): This event fires after each control has been initialized ,each controls unique id is set and skin settings have been applied. Yo can use this event to change initialization values for controls.
3)InitComplete: Raise omnce all initializations of page and its controls have been completed. till now viewstate values are not yet loaded hence you can use this event to change viewstate that you want make sure are persistested after the next post back.

4)PreLoad(): Raised after the page loads viewstate for itself and for all controls and after it processes post back data that is included with the request instance.
LoadsViewstate, Postbackdata.

5)Load(): In this event the page will be restored to previous state incase of postbacks. Code in this page checks fr the postback and then set control properties appropriately.This is the first place in page lifecycle where ll the controls values are restored.

6)ControlEvents : ASP.net now calls any event on the page or its cntrols that caused postback to occur.

7)LoadComplete:  This signals end of load is completed.

8)SaveViewStateComplete: Prior to this event the viewstate for the page and its controls will be set. Any changes of page controls at this point or beyond are ignored.

9)Render: This is method of page object and its controls. At this point asp.net calls methods on each page controls to get its output. the Render method generates client-side html ,Dhtml and script that are necessary to dispay a controsl at browser.

10)Unload : This is for the clean up code. After this page's html is renderes and the objects are disposed off during this event.

--If you find it is hard to remember every thing first tell all the events and next starts explain each of them. when you said all the events the interviewer will be satisfy with that if you are lucky.

Differences between an abstract calss and interface.

An abstract class may contain abstract or non abstract methods.
Interface can contain only method signature.
An abstract class can inherit from a class and one or more interfaces.
An Interface can only inherit from another interface.
An abstract class can contain fields,constructors and destructors.
An Interface cannot contain fields,constructors and destructors.
An abstract class cannot inherit from structures.
An Interface can inherit from structures.
An abstract class does not support multiple inheritance.
An Interface can support multiple inheritance.

What are the differences between constant and read only?

A constant cannot be static while read only can be static.
A constant need to declare and initialize at the declaration only while read only can be initialized at declaration or by code on constructor.
A constant value is evaluated at the design time , while a readonly value is evaluated at the run time.