.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
satyapriyanayak
SP
abhays
Sasi Prabhu


Find questions, FAQ's and their answers related to .NET, C#, Vb.Net, Sql Server and many more.


Post New Question Subscribe to Interview Questions
 
Quick Links For Interview Questions Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  OOPs  JQuery  JavaScript/VBScript
BiztalkPatten/Practices.IISWCFWPFWWF
NetworkingAptitudeOthers  All    
 

Interview Questions And Answers

SQL Server Interview Questions and Answers

Author: kamlesh          Posted Date: April 19, 2012    Category: Sql Server     Points: 40

SQL Server
What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage. (Read more here)
SQL DML and DDL
SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).
The query and update commands form the DML part of SQL:
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
The DDL part of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables. The most important DDL statements in SQL are:
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
What are the Properties of the Relational Tables?
Relational tables have the following six properties:
Values are atomic.
Column values are of the same kind.
Each row is unique.
The sequence of columns is insignificant.
The sequence of rows is insignificant.
Each column must have a unique name.
What is Normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
What is De-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
How is ACID property related to Database?
ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for while evaluating databases and application architectures. For a reliable database, all this four attributes should be achieved.
Atomicity is an all-or-none proposition.
Consistency guarantees that a transaction never leaves your database in a half-finished state.
Isolation keeps transactions separated from each other until they are finished.
Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.
What is a Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.
Describe how you can optimize stored procedures in SQL Server?
Below are some points to optimize stored procedure in SQL Server

• Use as much as possible WHERE clause filters. Where Clause is the most important part for optimization.

• Select only those fields which really required.

• Joins are expensive in terms of time. Make sure that use all the keys that relate to the tables together and don't join to the unused tables, always try to join on indexed fields. The join type is important as well in (INNER, OUTER).
What are the advantages of using Stored procedure
Stored procedure provides high security - All Grand and Revoke access can be given to the base table using stored procedures
Stored procedures avoid network traffic - Sql server prepares a cache of the sp and it avoid network traffic
Stored procedure provide performance - Every execution of sp will be take care by query analyser to prepare a layout before executing

What is constraints?
SQL Server users constraints to enforce limitations on the data that can be entered into a particular column in table. There are following types of constraints.

Unique, Default, Check, Primary Key, Foreign Key, Not Null.
What Primary key and Unique key?

Primary key are used with Foreign key to enforce referential integrity. Unique constraints allows nulls to be inserted into the field. But there can't be null in Primary key.

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

Unique Key Constraint:
The column values should retain uniqueness.
It allows null values in the column.
It will create non-clustered index by default.
Any number of unique constraints can be added to a table.

Primary Key Constraint:
Primary key will create column data uniqueness in the table.
It Wont allow Null values.
By default Primary key will create clustered index.
Only one Primary key can be created for a table.
Multiple columns can be consolidated to form a single primary key.
What is a View?
A simple view can be thought of as a subset of a table. It can be used for retrieving data as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does the data in the view as views are the way to look at parts of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.
What is an Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan, the SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.
What is trigger?
Triggers allows us to execute a batch of SQL code when either an insert, update or delete command is executed against a specific table.
Triggers are special types of stored procedures that are defined to execute automatically in place of or after data modifications. They can be executed automatically on the insert, delete and update operation.
What are the Different Types of Triggers?
There are two types of Triggers.
1)DML Trigger
There are two types of DML Triggers
1.Instead of Trigger
Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete.
2. After Trigger
After triggers execute following the triggering action, such as an insert, update, or delete.
2)DDL Trigger
This type of trigger is fired against Drop Table, Create Table, Alter Table or Login events. DDL Triggers are always After Triggers.
What are the Advantages of using SQL Triggers ?
Different Advantages of Using SQL Triggers :

. It can catch the errors in business logic at the database level.

. It provides an alternative way to run scheduled tasks.

. It is very much useful when we use it to audit the change the data in a database table.

. Using SQL trigger,we don’t have to wait to run the scheduled tasks. we can handle
those tasks before or after changes being made to database tables.


. It provides an alternative way to check integrity.
What are the Disadvantages of using SQL Triggers ?
Disadvantages of Using SQL Triggers :

. Trigger can provide extended validation and it cannot be replaced with all the validations.

. Simple validations can be done at the application level itself .

. Triggers executes invisibly from client-application which connects to the database server.So it is difficult to figure out what happens at the database layer.

. Triggers runs on every update made to the table therefore it adds more load to the database and cause the system to run slow.
Difference between Triggers and Stored procedures
Both are database objects containing blocks lof code that can be used for implementing business logic

The differences are:

1)Triggers fire automatically but they need events for that.
(Example: create,alter,drop,insert,delete,update)
Procedures have to be explicitly called and then executed.
They do not need create,alter,drop,insert,delete,update.
we can also execute procedures automatically using the sp_procoption.

2))we cannot pass parameters inside the triggers,
but we can pass parameters inside stored procedures
-------------------
example: if we want to display a message "error"

using a trigger: we need some DDL/DML Statement
using a procedure: NO DDL/DML is needed

What is difference between DELETE and TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
1. TRUNCATE:
1. TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
2. TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
3. TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
4. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
5. TRUNCATE cannot be rolled back.
6. TRUNCATE is DDL Command.
7. TRUNCATE Resets identity of the table
2. DELETE:
1. DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
2. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
3. DELETE Can be used with or without a WHERE clause
4. DELETE Activates Triggers.
5. DELETE can be rolled back.
6. DELETE is DML Command.
7. DELETE does not reset identity of the table.
Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.
Difference between HAVING and WHERE Clause
Where Clause:
1.Where Clause can be used other than Select statement also
2.Where applies to each and single row
3.In where clause the data that fetched from memory according
to condition
4.Where is used before GROUP BY clause
Ex:Using Condition for the data in the memory.

Having Clause:
1.Having is used only with the SELECT statement.
2.Having applies to summarized rows (summarized with GROUP BY)
3.In having the completed data firstly fetched and then separated according to condition.
4.HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query
Ex: when using the avg function and then filter the data like ava(Sales)>0

Summary:
Having works like Where clause with out Group By Clause
Difference Between Union and Union All
Union:
1.UNION only selects distinct values
2.Output is in sorted order

UnionAll:
1.UNION ALL selects all values (including duplicates).
2.Output is not in sorted order

EX:
SELECT Col
FROM @Table1
UNION
SELECT Col
FROM @Table2

/* Result of Union All operation */
SELECT Col
FROM @Table1
UNION ALL
SELECT Col
FROM @Table2

Output:
Union:
1
2
3
5

UnionAll:

1
2
3
2
5
What's the maximum size of a row?
8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".
What is Clustered & Non-Clustered Index?
Clustered Index: Clustered index physically rearrange the data that users inserts in your tables. It is nothing but a dictionary type data where actual data remains.

Non-Clustered Index: It Non-Clustered Index contains pointers to the data that is stored in the data page. It is a kind of index backside of the book where you see only the reference of a kind of data.
What is cursor in SQL Server?
A cursor is a set of rows together with a pointer that identifies a current row.

In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, its like recordset in the ASP and visual basic.
How to return XML in SQL Server?
We can use FOR XML statement at the end of the query to return xml data from the SQL Server.
select * from mytable for xml auto
There are three mode of returning XML and they are auto, raw and explicit
What is the difference between Stored Procedure and Function?

There are few differences between Stored Procedure and Function that are
• Stored Procedures are stored in a compiled format into the database where as Functions are compiled at run time.

• The Stored Procedures can perform certain tasks in the database by using insert, delete, update and create commands but in function you can't use these commands.

• Normally the Stored procedures are used to process certain task but the Functions are used to compute the values that is we can pass some value as input and then it perform some task on the value and return output.

• Stored procedures can change in the server directly but Functions cannot change in the server directly.

• To run a Stored Procedure we have to use the Execute or Exec command where as Functions can run as an executable file.

• Stored Procedure can return multiple values where as Functions can return only single value.

• The Stored Procedures can be used directly in the program by using its commandtype but Functions can be used by using SQL Query.

• The Stored Procedures are having both IN and OUT parameter where as Functions are always having IN parameter, no OUT parameter is possible.

• Stored Procedures cannot be used as an inline with a select statement while Functions can.

• The temparary variable is required to hold the return value of a Stored Procedure but in Functions, the temporary variable is optional.

What is subquery? Explain the Properties of a Subquery?
Subqueries are often referred to as sub-selects as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A subquery is executed by enclosing it in a set of parentheses. Subqueries are generally used to return a single row as an atomic value although they may be used to compare values against multiple rows with the IN keyword.
A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a resultset. This implies that a subquery SELECT statement can stand alone, and it does not depend on the statement in which it is nested. A subquery SELECT statement can return any number of values and can be found in the column list of a SELECT statement, and FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A subquery can also be used as a parameter to a function call. Basically, a subquery can be used anywhere an expression can be used.
SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.
SQL JOIN
The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
Look at the "Persons" table:
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the same P_Id. The P_Id distinguishes two persons even if they have the same name.
Next, we have the "Orders" table:
O_Id
OrderNo
P_Id
1
77895
3
2
44678
3
3
22456
1
4
24562
1
5
34764
15
Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers to the persons in the "Persons" table without using their names.
Notice that the relationship between the two tables above is the "P_Id" column.

What are Different Types of Join?
SQL INNER JOIN Keyword
The INNER JOIN keyword return rows when there is at least one match in both tables.
SQL INNER JOIN Syntax
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: INNER JOIN is the same as JOIN.
SQL INNER JOIN Example
The "Persons" table:
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
The "Orders" table:
O_Id
OrderNo
P_Id
1
77895
3
2
44678
3
3
22456
1
4
24562
1
5
34764
15
Now we want to list all the persons with any orders.
We use the following SELECT statement:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
The result-set will look like this:
LastName
FirstName
OrderNo
Hansen
Ola
22456
Hansen
Ola
24562
Pettersen
Kari
77895
Pettersen
Kari
44678
The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in "Persons" that do not have matches in "Orders", those rows will NOT be listed.

SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.
SQL LEFT JOIN Example
The "Persons" table:
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
The "Orders" table:
O_Id
OrderNo
P_Id
1
77895
3
2
44678
3
3
22456
1
4
24562
1
5
34764
15
Now we want to list all the persons and their orders - if any, from the tables above.
We use the following SELECT statement:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
The result-set will look like this:
LastName
FirstName
OrderNo
Hansen
Ola
22456
Hansen
Ola
24562
Pettersen
Kari
77895
Pettersen
Kari
44678
Svendson
Tove
 
The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).
SQL RIGHT JOIN Syntax
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
SQL RIGHT JOIN Example
The "Persons" table:
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
The "Orders" table:
O_Id
OrderNo
P_Id
1
77895
3
2
44678
3
3
22456
1
4
24562
1
5
34764
15
Now we want to list all the orders with containing persons - if any, from the tables above.
We use the following SELECT statement:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
The result-set will look like this:
LastName
FirstName
OrderNo
Hansen
Ola
22456
Hansen
Ola
24562
Pettersen
Kari
77895
Pettersen
Kari
44678
 
 
34764
The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).
SQL FULL JOIN Keyword
The FULL JOIN keyword return rows when there is a match in one of the tables.
SQL FULL JOIN Syntax
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name


SQL FULL JOIN Example
The "Persons" table:
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
The "Orders" table:
O_Id
OrderNo
P_Id
1
77895
3
2
44678
3
3
22456
1
4
24562
1
5
34764
15
Now we want to list all the persons and their orders, and all the orders with their persons.
We use the following SELECT statement:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
The result-set will look like this:
LastName
FirstName
OrderNo
Hansen
Ola
22456
Hansen
Ola
24562
Pettersen
Kari
77895
Pettersen
Kari
44678
Svendson
Tove
 
 
 
34764
The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.

Where do you think the users names and passwords will be stored in sql server?
They get stored in master db in the sysxlogins table.
What is a deadlock?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.
SQL Server detects deadlocks and terminates one user’s process
Difference between VARCHAR and NVARCHAR
VARCHAR:

1.Storage: 8 bit
2.Abbreviation: Variable -Length Character String
3.Accepts only English character
4.Doesn't supports other language symbols
5.Runs faster than NVARCHAR as consumes less memory
6.Use this when you develop the application for only local purpose

NVARCHAR:

1.Storage: 16 bit
2.Abbreviation: uNicode
3.Accepts both English character and non-English symbols
4.supports other language symbols
5.Runs slower than VARCHAR as consumes less memory
6.Use this when you use your application globally
Difference between char and varchar data types in Sql Server?

Char:
1.Fixed length memory storage
2.CHAR takes up 1 byte per character
3.Use Char when the data entries in a column are expected to be the same size
5.Ex:
Declare test Char(100);
test="Test" -
Then "test" occupies 100 bytes first four bytes with values and rest with blank data.


VarChar:
1.Variable length memory storage(Changeable)
2.VARCHAR takes up 1 byte per character, + 2 bytes to hold length information
3.varchar when the data entries in a column are expected to vary considerably in size.
4.Ex:
Declare test VarChar(100);
test="Test" -
Then "test" occupies only 4+2=6 bytes. first four bytes for value and other two bytes for variable length information.

Conclusion:
1.When Using the fixed length data's in column like phone number, use Char
2.When using the variable length data's in column like address use VarChar
How to Get nth Record in a Table?
First Get the n records fron the table using

Select Top n FROM UserTable
Now Reverse the Order using identity Column like:
Select Top n FROM UserTable Order By 1 DESC

Now we need nth record that can be get as
SELECT TOP 1 * FROM (Select Top n FROM UserTable Order By 1 DESC)AC

For Example i need to get 5th record From userTable then quey will be like this:
SELECT TOP 1 * FROM (SELECT TOP 5 * FROM UserTable Order By 1 DESC)AC
What is COMMIT and ROLLBACK statement in SQL
Commit statement helps in termination of the current transaction and do all the changes that occur in transaction persistent and this also commits all the changes to the database.
ROLLBACK do the same thing just terminate the currenct transaction but one another thing is that the changes made to database are ROLLBACK to the database.

What are the different types of Locks
Mainly There are three types of locks in SQL Server :

(1)Shared locks are used for operations that does not allow to change or update data, such as a SELECT statement.
(2)Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.
(3)Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.
What is the difference between UNION ALL Statement and UNION
The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION.
Reason : Because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

What is BCP?
The Bulk Copy Program (BCP) is a command-line utility that ships with SQL Server. It is used to transform data from one database to another.
What is DTS?
Data Transformation Services (DTS) in SQL Server 2000 provides a set of graphical tools and programmable objects to export and import data.

Can we have Try catch in Sqlserver?
Yes we can have Try catch statements in Sqlserver from SQLServer 2005
What is the syntax of try catch?
BEGIN TRY
END TRY
BEGIN CATCH

END CATCH;
What is a "dead lock"?
A "Dead Lock" happens when two or more transaction is locked from a common resource. In this case each must have to wait for the other to unlock that resource.
What is "Functional Dependency"?
Suppose there are two sets of attributes, X and Y. Then Y is said to be functionally dependent to X if a given value for each attribute in X uniquely determines the value of the attributes in Y.

Here X is called the determinant of the functional dependency and the functional dependency is denoted as X--->Y

Difference between Database Migration and Upgradation ?
What is Migration ?
Migrating from one Product type to another Product type.
ie: MSAccess to SQL Server, Oracle to SQL Server

What is Upgradation ?
Upgrading from one Edition/Version to another Edition/Version within the same Product type.
ie:SQL Server 2005 to SQL Server 2008
What does the Group By clause mean when used in databases?
Group By clause is Used usually with Aggregate Functions (eg. sum, average) to group rows with same data together.
Define Rollback and Rollforward?
Rollback: The Rollback transaction is a transaction which rolls back the transaction to the beginning of the transaction. The transaction can be rolled back completely by specifying the transaction name in the Rollback statement or to cancel any changes to a database during current transaction. It is permissible to use before Commit transaction.

Rollforward: Recovering a database by applying different transactions that recorded in the database log files. It is nothing but re-doing the changes made by a transaction i.e. after the committed transaction and to over write the changed value again to ensure consistency.
Define Concurrency and Concurrency control? Explain what are the different techniques?
Concurrency allows us the simultaneous access of same data by different users. The process of managing simultaneous execution of transactions in a shared database, to ensure the serializability of transactions, is known as concurrency control. It avoids the adverse effect of one transaction to another transaction. These are of two types.

Pessimistic concurrency control: It assumes when a conflicts happen. This technique detects conflicts as soon as they occur and resolve them using locking. The system lock prevents users from modifying data in a way so that it will not affect other user. After a user performs an action that causes a lock to be applied, so that other users cannot perform any action on that.

Optimistic concurrency control: This is called optimistic because the conflicts between transactions are rare and it doesn't require locking. Here the transactions are executed without any restrictions. It is mainly used when there is low contention for data and it checks for conflicts before the commit.
What is a Database Transaction?
A Database Transaction is a logical unit of database operations and a unit of work in database management system. These are highly important to use transactions when working with databases. In this the SQL statement is treated as a transaction and will be automatically committed immediately after it is successfully executed. For this successful transaction that is from begin to end transaction, it follows the term ACID (Atomicity, Consistency, Isolation, Durability).

To implement Database Transaction we have to follow the following steps i.e.

beginTransaction();
setTransactionSuccessful();
endTransaction();
What is the difference between a Database Administrator and a Data Administrator?
Database Administrator: The basic role of a Database Administrator is to store and manage the information in the database. They are responsible for reviewing the contents in the database. They are performing all the activities related to maintaining the database and responsible for designing, implementing the database. Also their responsible is to backed up data regularly and prevent from unauthorized access. They are also known as Database Coordinator or Database Programmer.

The different functionalities of a database administrator are maintaining database system software, developing physical database structures and data dictionary.

Data Administrator: The Data Administrator is responsible for defining data elements, data names and their relationship with the database analyst. The basic responsible is how to install and configure the RDBMS applications and also they have to know the requirements of the software application in terms of functions and assure the data integrity. They are also known as Data Analyst. Following are some basic steps for a Data Administrator,

• Specification of organization data.
• The design and maintenance of data management application.
• Validating the data and files.
• Security of files or databases.
What is the exact use of PIVOT in sql server ?
The use of Pivot element is , it is used to reorganize and summarize the selected columns and rows of data in a table to produce the desired reports.

For example ,

Let us create a table :
CREATE TABLE ItemSales(
SalesPerson VARCHAR(50),
Item VARCHAR(50),
ItemAmount INT)

Insert values into it :
INSERT INTO ItemSales
VALUES('Person1', 'Pickles', $100.00)
INSERT INTO ItemSales
VALUES('Person1', 'Pickles', $100.00)
INSERT INTO ItemSales
VALUES('Person2' ,'Oranges' ,$50.00 )
INSERT INTO ItemSales
VALUES('Person2', 'Pickles', $25.00)
INSERT INTO ItemSales
VALUES('Person2', 'Oranges', $300.00)
INSERT INTO ItemSales
VALUES('Person1', 'Oranges', $500.00)

Now create and assigning a pivot element
SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM
(SELECT SalesPerson, Item, ItemAmount
FROM ItemSales ) ps
PIVOT
(
SUM (ItemAmount)
FOR Item IN
( [Oranges], [Pickles])
) AS Pvt
After this the complete data is summarized and the report generated is in this format:

-----------------------------------------------
SalesPerson || Oranges || Pickles
-----------------------------------------------
Person1 || 500 || 200
Person2 || 350 || 25
What are derived Tables?
They are basically select statements in the from clause referred to by an
alias name.The alias name creates a table in the result set that can then be
referred to by some other Select statement

example:
2 tables emp and emp1 have got salary column. To calculate the maximun
salary from the salary columns of 2 tables

select max(salary) from (select salary from emp union select salary from emp1) a

a: is the derived table that will provide the input to the outer query
Difference between Composite , Candidate and alternate keys ?
Composite Key :
A composite key is a combination of more than one column to identify a unique row in a table.
Exp : EmpID, EmailID, SSN in Employee table and project ID in project table .
if EmpID and project id are put in projectionHours table then combination of empid and project ID called as composite key because combination of these two act as primary key in projectionHours table.

Candidate Key:
All keys in a table that become unique called as candidate key.
Exp : EmpID , EmailID and SSN all will be always unique for any employee in that case all these three columns called as candidate keys.

Alternate Key:
Among of candidate keys if any single key or combination of keys made as primary key then rest candidate key called as alternate key.
Exp : Suppose in employee table EmpID is primary key then Emailid and SSN are called as alternate key mean later on these key can be act as primary key without affecting existing data in table.

OOPS Concepts

Author: kamlesh          Posted Date: April 18, 2012    Category: OOPS     Points: 40

OOPS Features :
The object oriented programming (OOP) is a programming model where Programs are organized around object and data rather than action and logic.
* OOP allow decomposition of a problem into a number of entities called
Object and then builds data and function around these objects.
The Program is divided into number of small units called Object. The data and function are build around these objects.
The data of the objects can be accessed only by the functions associated with that object.
The functions of one object can access the functions of other object.
What is Object?

Objects are the basic run-time entities in an object oriented system.They may represent a person,a place or any item that the program has to handle. 

"Object is a Software bundle of related variable and methods. "

“Object is an instance of a class”
Class will not occupy any memory space. Hence to work with the data represented by the class you must create a variable for the class, which is called as an object. 
When an object is created by using the keyword new, then memory will be allocated for the class in heap memory area, which is called as an instance and its starting address will be stored in the object in stack memory area.
 When an object is created without the keyword new, then memory will not be allocated in heap I.e. instance will not be created and object in the stack contains the value null.
When an object contains null, then it is not possible to access the members of the class using that object.

class Employee
{

}
Syntax to create an object of class Employee:-
Employee objEmp = new Employee();
What is Class?
A class is the core of any modern Object Oriented Programming language such as C#.
In OOP languages it is must to create a class for representing data.Class is a blueprint of an object that contains variables for storing data and functions to performing operations on these data. Class will not occupy any memory space and hence it is only logical representation of data.To create a class, you simply use the keyword "class" followed by the class name:
class Employee
{
}

Difference between a Class and an object

Posted by: Ddd
Class:

1)It is a datatype that contains the programming logic.

2)Class is visible in the source code and resides in hard disk.

3)Class is like a template or blueprint of the object. It implements reusability,

encapsulation, inheritance

example:Button is a class with properties like Text,BackColor,
events like click, methods like Focus

Object:

1)it is a chunk of memory that implements the class logic.

2)Object is in the RAM and not visible in source code.

3)It is the real world implementation of the class.
Each object has its own copy of data.
example: Button1, Button2 are the objects of Button class.

Value Type
As name suggest Value Type stores “value” directly.
For eg:
//I and J are both of type int
I = 20;
J = I;
int is a value type, which means that the above statements will results in two locations in memory.
For each instance of value type separate memory is allocated.
Stored in a Stack.
It Provides Quick Access, because of value located on stack.
Reference Type
As name suggest Reference Type stores “reference” to the value.
For eg:
Vector X, Y; //Object is defined. (No memory is allocated.)
X = new Vector(); //Memory is allocated to Object. //(new is responsible for allocating memory.)
X.value = 30; //Initialising value field in a vector class.
Y = X; //Both X and Y points to same memory location. //No memory is created for Y.
Console.writeline(Y.value); //displays 30, as both points to same memory
Y.value = 50;
Console.writeline(X.value); //displays 50.
Note: If a variable is reference it is possible to indicate that it does not refer to any object by setting its value to null;
Reference type are stored on Heap.
It provides comparatively slower access, as value located on heap.
ref keyword
Passing variables by value is the default. However, we can force the value parameter to be passed by reference. Note: variable “must” be initialized before it is passed into a method.
out keyword
out keyword is used for passing a variable for output purpose. It has same concept as ref keyword, but passing a ref parameter needs variable to be initialized while out parameter is passed without initialized.
It is useful when we want to return more than one value from the method.
Note: You must assigned value to out parameter in method body, otherwise the method won’t compiled.
Boxing and Un-Boxing
Boxing: means converting value-type to reference-type.
Eg:
int I = 20;
string s = I.ToSting();
UnBoxing: means converting reference-type to value-type.
Eg:
int I = 20;
string s = I.ToString(); //Box the int
int J = Convert.ToInt32(s); //UnBox it back to an int.
Note: Performance Overheads due to boxing and unboxing as the boxing makes a copy of value type from stack and place it inside an object of type System.Object in the heap.

What is Static field?
To indicate that a field should only be stored once no matter how many instance of the class we create.
What is Static Method?
It is possible to declare a method as Static provided that they don't attempt to access any instance data or other instance methods.
What is Virtual keyword?
This keyword indicates that a member can be overridden in a child class. It can be applied to methods, properties, indexes and events.
“The virtual keyword is used to modify a method, property, indexer or event declaration, and allow it to be overridden in a derived class.”
What is Polymorphisms?
Polymorphism means one name many forms.
One function behaves different forms.
In other words, "Many forms of a single object is called Polymorphism."

Real World Example of Polymorphism:
Example-1: 
A Teacher behaves to student.
A Teacher behaves to his/her seniors.
Here teacher is an object but attitude is different in different situation.
Example-2: 
Person behaves SON in house at the same time that person behaves EMPLOYEE in office.
Example-3: 
Your mobile phone, one name but many forms
As phone
As camera
As mp3 player
As radio

Example of Compile Time Polymorphism: Method Overloading
Example of Run Time Polymorphism: Method Overriding

Example of Compile Time Polymorphism

Method Overloading
- Method with same name but with different arguments is called method overloading.
- Method Overloading forms compile-time polymorphism.
- Example of Method Overloading:
class A1
{
void hello()
{ Console.WriteLine(“Hello”); }

void hello(string s)
{ Console.WriteLine(“Hello {0}”,s); }
}


Example of Run Time Polymorphism

Method Overriding
- Method overriding occurs when child class declares a method that has the same type arguments as a method declared by one of its superclass.
- Method overriding forms Run-time polymorphism.
- Note: By default functions are not virtual in C# and so you need to write “virtual” explicitly. While by default in Java each function are virtual.
- Example of Method Overriding:
Class parent
{
virtual void hello()
{ Console.WriteLine(“Hello from Parent”); }
}

Class child : parent
{
override void hello()
{ Console.WriteLine(“Hello from Child”); }
}

static void main()
{
parent objParent = new child();
objParent.hello();
}
//Output
Hello from Child.
What is Inheritance?
When a class acquire the property of another class is known as inheritance.
Inheritance is process of object reusability.
For example, A Child acquire property of Parents.

public class ParentClass
    {
        public ParentClass()
        {
            Console.WriteLine("Parent Constructor.");
        }

        public void print()
        {
            Console.WriteLine("I'm a Parent Class.");
        }
    }

    public class ChildClass : ParentClass
    {
        public ChildClass()
        {
            Console.WriteLine("Child Constructor.");
        }

        public static void Main()
        {
            ChildClass child = new ChildClass();

            child.print();
        }
}
Output:
Parent Constructor.
Child Constructor.
I'm a Parent Class.

Abstraction:
Abstraction is "To represent the essential feature without representing the back ground details."

Abstraction lets you focus on what the object does instead of how it does it.

Abstraction provides you a generalized view of your classes or object by providing relevant information.

Abstraction is the process of hiding the working style of an object, and showing the information of an object in understandable manner.

Real world Example of Abstraction: - 
Suppose you have an object Mobile Phone.

Suppose you have 3 mobile phones as following:-

Nokia 1400 (Features:- Calling, SMS)
Nokia 2700 (Features:- Calling, SMS, FM Radio, MP3, Camera)
Black Berry (Features:-Calling, SMS, FM Radio, MP3, Camera, Video Recording, Reading E-mails)

Abstract information (Necessary and Common Information) for the object "Mobile Phone" is make a call to any number and can send SMS."

so that, for mobile phone object you will have abstract class like following:-

    abstract class MobilePhone
    {
        public void Calling();
        public void SendSMS();
    }

    public class Nokia1400 : MobilePhone
    {

    }

    public class Nokia2700 : MobilePhone
    {
        public void FMRadio();
        public void MP3();
        public void Camera();
    }

    public class BlackBerry : MobilePhone
    {
        public void FMRadio();
        public void MP3();
        public void Camera();
        public void Recording();
        public void ReadAndSendEmails();

    }

Abstraction means putting all the variables and methods in a class which are necessary.
For example: - Abstract class and abstract method.
Abstraction is the common thing.
example: 
If somebody in your collage tell you to fill application form, you will fill your details like name, address, data of birth, which semester, percentage you have got etc.
If some doctor gives you an application to fill the details, you will fill the details like name, address, date of birth, blood group, height and weight.
See in the above example what is the common thing?
Age, name, address so you can create the class which consist of common thing that is called abstract class. 
That class is not complete and it can inherit by other class.

Encapsulation: 
Wrapping up data member and method together into a single unit (i.e. Class) is called Encapsulation.

Encapsulation is like enclosing in a capsule. That is enclosing the related operations and data related to an object into that object.

Encapsulation is like your bag in which you can keep your pen, book etc. It means this is the property of encapsulating members and functions.

    class Bag
    {
        book;
        pen;
        ReadBook();
    }


Encapsulation means hiding the internal details of an object, i.e. how an object does something.

Encapsulation prevents clients from seeing its inside view, where the behaviour of the abstraction is implemented.

Encapsulation is a technique used to protect the information in an object from the other object.

Hide the data for security such as making the variables as private, and expose the property to access the private data which would be public.
So, when you access the property you can validate the data and set it.

Example:

class Demo
{
   private int _mark;

   public int Mark
   {
     get { return _mark; }
     set { if (_mark > 0) _mark = value; else _mark = 0; }
   }
 }

Real world Example of Encapsulation:-
Let's take example of Mobile Phone and Mobile Phone Manufacturer
Suppose you are a Mobile Phone Manufacturer and you designed and developed a Mobile Phone design(class), now by using machinery you are manufacturing a Mobile Phone(object) for selling, when you sell your Mobile Phone the user only learn how to use the Mobile Phone but not that how this Mobile Phone works.

This means that you are creating the class with function and by making object (capsule) of it you are making availability of the functionality of you class by that object and without the interference in the original class.

Example-2: 
TV operation 
It is encapsulated with cover and we can operate with remote and no need to open TV and change the channel.
Here everything is in private except remote so that anyone can access not to operate and change the things in TV.

Difference between Abstraction and Encapsulation :-

Abstraction
Encapsulation
1. Abstraction solves the problem in the design level.

1. Encapsulation solves the problem in the implementation level.

2. Abstraction is used for hiding the unwanted data and giving relevant data.

2. Encapsulation means hiding the code and data into a single unit to protect the data from outside world.


3. Abstraction lets you focus on what the object does instead of how it does it

3. Encapsulation means hiding the internal details or mechanics of how an object does something.

4. Abstraction- Outer layout, used in terms of design.
For Example:-
 Outer Look of a Mobile Phone, like it has a display screen and keypad buttons to dial a number.

4. Encapsulation- Inner layout, used in terms of implementation.
For Example:- Inner Implementation detail of a Mobile Phone, how keypad button and Display Screen are connect with each other using circuits.


What is Abstract Class?
Abstract class is a class that can not be instantiated, it exists extensively for inheritance and it must be inherited. There are scenarios in which it is useful to define classes that is not intended to instantiate; because such classes normally are used as base-classes in inheritance hierarchies, we call such classes abstract classes.
Abstract classes cannot be used to instantiate objects; because abstract classes are incomplete, it may contain only definition of the properties or methods and derived classes that inherit this implements it's properties or methods.
Static, Value Types & interface doesn't support abstract modifiers. Static members cannot be abstract. Classes with abstract member must also be abstract.
What is an Interface?
An interface is a contract & defines the requisite behavior of generalization of types.
An interface mandates a set of behavior, but not the implementation. Interface must be inherited. We can't create an instance of an interface.
An interface is an array of related function that must be implemented in derived type. Members of an interface are implicitly public & abstract.
An interface can inherit from another interface.

Interfaces Vs Abstract Class.
A class may implement one or more interfaces.
A class may inherit only one Abstract Class or any other class
An interface cannot have access modifiers for any types declared in it. By Default all are public.
An abstract class can contain access modifiers.
If various implementations only share method signatures then it is better to use Interfaces.
If various implementations are of the same kind and use common behavior or status then abstract class is better to use.
Requires more time to find the actual method in the corresponding classes.
Fast
All methods declared in interface must be implemented in derived class.
Only abstract methods need to be implemented in derived classes.
If we add a new method to an Interface then we have to track down all the implementations of the interface and define implementation for the new method.
If we add a new method to an abstract class then we have the option of providing default implementation and therefore all the existing code will work without any modification.
No fields can be defined in interfaces
We can define fields and constants in abstract class.

What is pure virtual function?
When you define only function prototype in a base class without and do the complete implementation in derived class. This base class is called abstract class and client won’t able to instantiate an object using this base class.

A pure virtual function is a function that must be overridden in a derived class and need not be defined. A virtual function is declared to be "pure" using the curious "=0"
syntax:
class Base
{
public:
void f1(); // not virtual
virtual void f2(); // virtual, not pure
virtual void f3() = 0; // pure virtual
};

What is access modifier in C#?
Access modifiers are keywords used to specify the declared accessibility of a member or a type. This section introduces the four access modifiers:
public
protected
internal
private
public
The type or member can be accessed by any other code in the same assembly or another assembly that references it.
Example
In the following example, two classes are declared, MyClass1 and MyClass2. The public members x and y of the MyClass1 are accessed directly from MyClass2.
// protected_public.cs
// Public access
using System;
class MyClass1
{
public int x;
public int y;
}

class MyClass2
{
public static void Main()
{
MyClass1 mC = new MyClass1();

// Direct access to public members:
mC.x = 10;
mC.y = 15;
Console.WriteLine("x = {0}, y = {1}", mC.x, mC.y);
}
}
private
The type or member can be accessed only by code in the same class or struct.
In this example, the Employee class contains a public member, Name, and a private member, Salary. The public member can be accessed directly, while the private member must be accessed through the public method AccessSalary().
// private_keyword.cs
using System;
class Employee
{
public string name = "xx";
double salary = 100.00; // private access by default
public double AccessSalary() {
return salary;
}
}

class MainClass
{
public static void Main()
{
Employee e = new Employee();

// Accessing the public field:
string n = e.name;

// Accessing the private field:
double s = e.AccessSalary();
}
}
protected
The type or member can be accessed only by code in the same class or struct, or in a class that is derived from that class.
A protected member of a base class is accessible in a derived class only if the access takes place through the derived class type. For example, consider the following code segment:
class A
{
protected int x = 123;
}

class B : A
{
void F()
{
A a = new A();
B b = new B();
a.x = 10; // Error
b.x = 10; // OK
}
}
internal
The type or member can be accessed by any code in the same assembly, but not from another assembly.
Example
This example contains two files, Assembly1.cs and Assembly2.cs. The first file contains an internal base class, BaseClass. In the second file, an attempt to access the member of the base class will produce an error.
File Assembly1.cs:
// Assembly1.cs
// compile with: /target:library
internal class BaseClass
{
public static int IntM = 0;
}
File Assembly2.cs
// Assembly2.cs
// compile with: /reference:Assembly1.dll
// CS0122 expected
class TestAccess
{
public static void Main()
{
BaseClass myBase = new BaseClass(); // error, BaseClass not visible outside assembly
}
}
protected internal
The type or member can be accessed by any code in the assembly in which it is declared, or from within a derived class in another assembly. Access from another assembly must take place within a class declaration that derives from the class in which the protected internal element is declared, and it must take place through an instance of the derived class type.
Default Access modifiers in C#?
An enum has default modifier as public

A class has default modifiers as Internal . It can declare members (methods etc) with following access modifiers:
public
internal
private
protected internal

An interface has default modifier as public

A struct has default modifier as Internal and it can declare its members (methods etc) with following access modifiers:
public
internal
private

A methods, fields, and properties has default access modifier as "Private" if no modifier is specified.
What are Constructors ?
Constructors are used for initializing the members of a class whenever an object is created with the default values for initialization.

If no constructor defined then the CLR will provide an implicit constructor which is called as Default Constructor.

A class can have any number of constructors provided they vary with the number of arguments that are passed, which is they should have different signatures.

Constructors do not return a value
Constructors can be overloaded
Example:
public class mySampleClass
{
public mySampleClass()
{
// This is the constructor method.
}
// rest of the class members goes here.
}
When the object of this class is instantiated, this constructor will be executed. Something like this:
mySampleClass obj = new mySampleClass()
// At this time the code in the constructor will // be executed
What are the various types of Constructors
Public : Accessible to All
Private: Those classes in which only static members are there and you don't want there objects to be created in any class.
Static: Used for initializing only the static members of the class. These will be invoked for the very first time the class is being loaded on the memory. They cannot accept any arguments. Static Constructors cannot have any access modifiers.
Intern: implementations of the abstract class to the assembly defining the class. A class containing an internal constructor cannot be instantiated outside of the assembly (Namespace).
and External

What is a private constructor? Where will you use it?
When you declare a Constructor with Private access modifier then it is called Private Constructor. We can use the private constructor in singleton pattern.

If you declare a Constructor as private then it doesn’t allow to create object for its derived class, i.e you loose inherent facility for that class.

Example:
Class A
{
// some code
Private Void A()
{
//Private Constructor
}
}

Class B:A
{
//code
}

B obj = new B();// will give Compilation Error

Because Class A constructor declared as private hence its accessibility limit is to that class only, Class B can't access. When we create an object for Class B that constructor will call constructor A but class B have no rights to access the Class A constructor hence we will get compilation error.
Differences between a structure and class
Structure:

1)It is a Value Type
2)Its variable directly contains the data on the stack
3)Each structure variable has its independent copy
of data.
4)One structure cannot inherit other
5)They do not have destructors
6)They do no have explicit parameterless constructors
7)we cannot put sealed /abstract modifiers before the structures.
8)Easier memory management
9)examples:
int, short,long,DateTime,
Point
(predefined)

Uses:
Structures are typically used for handling
small amounts of data or where inheritance, overriding is not required
example: int a=100;

Class

1)It is a reference Type
2)Its variable has references to the data(data is stored in the object created in the heap) .
3)Two Class variables can refer to the same object
4)One class can inherit the other(unless the class is sealed/static)
5)Classes have destructors
6)They can have explicit parameterless constructors
7)Sealed/abstract modifers can be put before classes.
8) Comparitively Difficult memory management
9)example: SqlConnection,DataView(predefined classes)

Classes are typically used where inheritance, overriding is required
or we need to create objects capable of handling large data
example: DataSet,ArrayList can handle large data.

ASP.Net Interview questions and answer

Author: kamlesh          Posted Date: April 05, 2012    Category: ASP.Net     Points: 40

ASP.NET Page Life Cycle
When the Page is requested for the first time
The Life Cycle of a page when requested for the first time:
Initializing: During this phase, the server creates an instance of the server control
Loading: During this phase, the instance of the control is loaded onto the page object in which it is defined.
PreRendering: During this phase, the control is updated with the changes made to it. This prepares the control for rendering.
Saving: During this phase, the state information of the control is saved. For example, if a value is set for the control during the Load event, it is embedded in the HTML tag that will be returned to the browser.
Rendering: During this phase, the server creates the corresponding HTML tag for the control.
Disposing: During this phase, all cleanup tasks, such as closing files and database connections opened by the control are performed.
Unloading: During this phase, all cleanup tasks, such as destroying the instances of server control are performed. This is the final event in the life cycle of a server control
Life cycle when the page processed during a postback event
The processing sequence in which a page is processed during a postback event is:
Initializing: During this phase, the server creates an instance of the server control
Loading view state: During this phase, the view state of the control posted by the client is reloaded into the new instance of the control.
Loading: During this phase, the instance of the control is loaded onto the page object in which it is defined.
Loading the postback data: During this phase, the server searches any data corresponding to the control that is loaded in the data posted by the client.
PreRendering: During this phase, the control is updated with the changes made to it. This prepares the control for rendering.
Saving state: During this phase, the change in the state of control between the current request and the previous request of the page is saved. For each change, the corresponding event is raised. For example, if the text of a textbox is changed, the new text is saved and a text_change event is raised.
Rendering: During this phase, the server creates the corresponding HTML tag for the control.
Disposing: During this phase, all cleanup tasks, such as closing files and database connections opened by the control are performed.
Unloading: During this phase, all cleanup tasks, such as destroying the instances of server control are performed. This is the final event in the life cycle of a server control

What is IIS? Why is it used?
Internet Information Services (IIS) is created by Microsoft to provide Internet-based services to ASP.NET Web applications. It makes your computer to work as a Web server and provides the functionality to develop and deploy Web applications on the server. IIS handles the request and response cycle on the Web server. It also offers the services of SMTP and FrontPage server extensions. The SMTP is used to send emails and use FrontPage server extensions to get the dynamic features of IIS, such as form handler.
What is the basic difference between ASP and ASP.NET?
The basic difference between ASP and ASP.NET is that ASP is interpreted; whereas, ASP.NET is compiled. This implies that since ASP uses VBScript; therefore, when an ASP page is executed, it is interpreted. On the other hand, ASP.NET uses .NET languages, such as C# and VB.NET, which are compiled to Microsoft Intermediate Language (MSIL).
What is the difference between mechine.config and web.config?
machine.config is a system level configuration i.e it is applied on all application in o/s that the configuration is set where as in web.config it is applicable to only one application i.e each asp.net webapplication will contain atleast on web.config file.
What is the difference between Response.Redirect and Server.Transfer.
Server.Transfer transfers page processing from one page directly to the next page without making a round-trip back to the client's browser. This provides a faster response with a little less overhead on the server.Server.Transfer does not update the clients url history list or current url. 
Response.Redirect is used toredirect the user's browser to another page or site. This performs a trip back to the client where the client's browser is redirected to the new page. The user's browser history list is updated to reflect the new address.
Explain the Application and Session objects in ASP.NET.
Application state is used to store data corresponding to all the variables of an ASP.NET Web application. The data in an application state is stored once and read several times. Application state uses the HttpApplicationState class to store and share the data throughout the application. You can access the information stored in an application state by using the HttpApplication class property. Data stored in the application state is accessible to all the pages of the application and is the same for all the users accessing the application. The HttpApplicationState class provides a lock method, which you can use to ensure that only one user is able to access and modify the data of an application at any instant of time.

Each client accessing a Web application maintains a distinct session with the Web server, and there is also some specific information associated with each of these sessions. Session state is defined in the element of the web.config file. It also stores the data specific to a user session in session variables. Different session variables are created for each user session. In addition, session variables can be accessed from any page of the application. When a user accesses a page, a session ID for the user is created. The session ID is transferred between the server and the client over the HTTP protocol using cookies.
what is boxing and unboxing?
Boxing is what happens when a value-type object is assigned to a reference-type variable.
Unboxing is what happens when a reference-type variable is assigned to a value-type variable.
What are the uses of Reflection??
Reflectionis a concept using which we can
1) Load assemblies dynamically
2) Invoke methods at runtime
3) Retriving type information at runtime.
What is State Management? How many ways are there to maintain a state in .NET?
State management is used to store information requests. The state management is used to trace the information or data that affect the state of the applications.
There are two ways to maintain a state in .NET, Client-Based state management and Server-Based state management.
The following techniques can be used to implement the Client-Based state management:
View State
Hidden Fields
Cookies
Query Strings
Control State
The following techniques can be used to implement Server-Based state management:
Application State
Session State
Profile Properties
Where is the View state Data stored?
ViewState data is stored in the hidden field. When the page is submitted to the server the data is sent to the server in the form of hidden fields for each control. If th viewstate of the control is enable true the value is retained on the post back to the client when the page is post backed.
Where do the Cookie State and Session State information be stored?
Cookie Information will be stored in a txt file on client system under a folder named Cookies. Search for it in your system you will find it.
Coming to Session State
As we know for every process some default space will be allocated by OS.
In case of InProc Session Info will be stored inside the process where our
application is running.
In case of StateServer Session Info will be stored using ASP.NET State Service.
In case of SQLServer Session info will be stored inside Database. Default DB
which will be created after running InstallSQLState Script is ASPState.
How many types of Cookies are available in ASP.NET?
There are two types of Cookies available in ASP.NET:
Session Cookie - Resides on the client machine for a single session until the user does not log out.
Persistent Cookie - Resides on a user's machine for a period specified for its expiry, such as 10 days, one month, and never.
The user can set this period manually.
What are the different types of sessions in ASP.Net? Name them.?
Session Management can be achieved in two ways
1)InProc
2)OutProc
OutProc is again two types
1)State Server
2)SQL Server
InProc
Adv.:
1) Faster as session resides in the same process as the application
2) No need to serialize the data
DisAdv.:
1) Will degrade the performance of the application if large chunk of data is stored
2) On restart of IIS all the Session info will be lost
State Server
Adv.:
1) Faster then SQL Server session management 
2) Safer then InProc. As IIS restart 
won't effect the session data
DisAdv.:
1) Data need to be serialized
2) On restart of ASP.NET State Service session info will be lost
3)Slower as compared to InProc
SQL Server
Adv.:
1) Reliable and Durable
2) IIS and ASP.NET State Service 
restart won't effect the session data
3) Good place for storing large chunk of data
DisAdv.:
1) Data need to be serialized
2) Slower as compare to InProc and State Server
3)Need to purchase Licensed 
version of SQL Serve
What is caching? What are different ways of caching in ASP.NET?
Caching is a technique of persisting the data in memory for immediate access to requesting program calls. This is considered as the best way to enhance the performance of the application.

Caching is of 3 types:
Output Caching - Caches the whole page.
Fragment Caching - Caches a part of the page
Data Caching - Caches the data

Explain the validation controls. How many validation controls in ASP.NET 4.0?
Validation controls are responsible to validate the data of an input control. Whenever you provide any input to an application, it performs the validation and displays an error message to user, in case the validation fails.
ASP.NET 4.0 contains the following six types of validation controls:
CompareValidator - Performs a comparison between the values contained in two controls.
CustomValidator - Writes your own method to perform extra validation.
RangeValidator- Checks value according to the range of value.
RegularExpressionValidator - Ensures that input is according to the specified pattern or not.
RequiredFieldValidator - Checks either a control is empty or not.
ValidationSummary - Displays a summary of all validation error in a central location.
Write three common properties of all validation controls.
Three common properties of validation controls are as follows:
ControlToValidate - Provides a control to validate
ErrorMessage - Displays an error message
IsValid - Specifies if the control's validation has succeeded or not
Text - Displays a text for validation control before validation
What are the various ways of authentication techniques in ASP.NET?
There are various techniques in ASP.NET to authenticate a user. You can use one of the following ways of authentication to select a built-in authentication provider:
Windows Authentication - This mode works as the default authentication technique. It can work with any form of Microsoft Internet Information Services (IIS) authentication, such as Basic, Integrated Windows authentication (NTLM/Kerberos), Digest, and certificates. The syntax of Windows authentication mode is given as follows:
Forms Authentication - You can specify this mode as a default authentication mode by using the following code snippet:
Passport - This mode works with Microsoft Passport authentication, as shown in the following code snippet:



















What is the difference between an interface and abstract class ?
Ans:
1. In an interface class, all methods are abstract and there is no implementation. In an abstract class some methods can be concrete.
2. In an interface class, no accessibility modifiers are allowed. An abstract class may have accessibility modifiers.
Define abstract class?
Ans:
1. A class that cannot be instantiated.
2. An abstract class is a class that must be inherited and have the methods overridden.
3. An abstract class is essentially a blueprint for a class without any implementation
Define interface class ?
Ans:
Interfaces, like classes, define a set of properties, methods, and events. But unlike classes, interfaces do not provide implementation. They are implemented by classes, and defined as separate entities from classes.
Define marshling?
It handles the communication between real server object and the client object. We can say that It’s a fake copy of the server object that resides on the client side and behaves as if it was the server.



What is the difference between a Struct and a Class?

Author: kamlesh          Posted Date: January 20, 2012    Category: C#     Points: 40

Structs are value-type variables and are thus saved on the stack, additional overhead but faster retrieval. Another difference is that structs cannot inherit.

Explain ACID rule of thumb for transactions.

Author: kamlesh          Posted Date: January 20, 2012    Category: C#     Points: 40


A transaction must be:
1. Atomic - it is one unit of work and does not dependent on previous and following transactions.
2. Consistent - data is either committed or roll back, no "in-between" case where something has been updated and something hasn't.
3. Isolated - no transaction sees the intermediate results of the current transaction).
4. Durable - the values persist if the data had been committed even if the system crashes right after.

SQL Interview Questions and Answers

Author: kamlesh          Posted Date: April 27, 2011    Category: Sql Server     Points: 40

What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables.

What is normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.
What are different normalization forms?
1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies.
What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.
What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs.
What is View?
It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table
What is Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries.
What is the difference between clustered and a non-clustered index?
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 are the different index configurations a table can have?
A table can have one of the following index configurations:
No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes
What is cursors?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
In order to work with a cursor we need to perform some steps in the following order:
Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor
What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.
Storped Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.
What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.
What are different type of Collation Sensitivity?
Case sensitivity
A and a, B and b, etc.
Accent sensitivity
a and á, o and ó, etc.
Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
Width sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.
What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
What is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
TRUNCATE
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back using logs.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.
DELETE
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back using logs.
DELETE is DML Command.
DELETE does not reset identity of the table.
Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
What types of Joins are possible with Sql Server?
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. HAVING criteria is applied after the the grouping of rows has occurred.
What are the different types of replication? Explain.
The SQL Server 2000-supported replication types are as follows:
. Transactional
. Snapshot
. Merge
Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.
Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.
Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.
What is DataWarehousing?
. Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
. Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
. Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting;
. Integrated, meaning that the database contains data from most or all of an organization's operational applications, and that this data is made consistent.
What is OLTP(OnLine Transaction Processing)?
In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

SQL

Author: kamlesh          Posted Date: March 21, 2011    Category: Sql Server     Points: 40

SQL is a standard language for accessing databases.

RDBMS

RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL,

and Microsoft Access.

The data in RDBMS is stored in database objects called tables.

A table is a collection of related data entries and it consists of columns and rows.


SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The query and update commands form the DML part of SQL:

* SELECT - extracts data from a database
* UPDATE - updates data in a database
* DELETE - deletes data from a database
* INSERT INTO - inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links

between tables, and impose constraints between tables. The most important DDL statements in SQL are:

* CREATE DATABASE - creates a new database
* ALTER DATABASE - modifies a database
* CREATE TABLE - creates a new table
* ALTER TABLE - modifies a table
* DROP TABLE - deletes a table
* CREATE INDEX - creates an index (search key)
* DROP INDEX - deletes an index


The SQL SELECT DISTINCT Statement

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will

want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.
SQL SELECT DISTINCT Syntax
SELECT DISTINCT column_name(s) FROM table_name

The WHERE clause is used to filter records.

The WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion.
SQL WHERE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator value

Operators Allowed in the WHERE Clause

With the WHERE clause, the following operators can be used:
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns


The AND & OR Operators

The AND operator displays a record if both the first condition and the second condition is true.

The OR operator displays a record if either the first condition or the second condition is true.

The ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by a specified column.

The ORDER BY keyword sort the records in ascending order by default.

If you want to sort the records in a descending order, you can use the DESC keyword.
SQL ORDER BY Syntax
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC


The INSERT INTO Statement

The INSERT INTO statement is used to insert a new row in a table.
SQL INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two forms.

The first form doesn't specify the column names where the data will be inserted, only their values:
INSERT INTO table_name
VALUES (value1, value2, value3,...)

The second form specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)


The UPDATE Statement

The UPDATE statement is used to update existing records in a table.
SQL UPDATE Syntax
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

The DELETE Statement

The DELETE statement is used to delete rows in a table.
SQL DELETE Syntax
DELETE FROM table_name
WHERE some_column=some_value


The TOP Clause

The TOP clause is used to specify the number of records to return.

The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records

can impact on performance.

Note: Not all database systems support the TOP clause.
SQL Server Syntax
SELECT TOP number|percent column_name(s)
FROM table_name


The LIKE Operator

The LIKE operator is used to search for a specified pattern in a column.
SQL LIKE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern


SQL Wildcards

SQL wildcards can substitute for one or more characters when searching for data in a database.

SQL wildcards must be used with the SQL LIKE operator.

With SQL, the following wildcards can be used:
Wildcard Description
% A substitute for zero or more characters
_ A substitute for exactly one character
[charlist] Any single character in charlist
[^charlist]

or

[!charlist] Any single character not in charlist

Using the % Wildcard

Now we want to select the persons living in a city that starts with "sa" from the "Persons" table.

We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE 'sa%'

Using the _ Wildcard

Now we want to select the persons with a first name that starts with any character, followed by "la" from the

"Persons" table.

We use the following SELECT statement:
SELECT * FROM Persons
WHERE FirstName LIKE '_la'


Using the [charlist] Wildcard

Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName LIKE '[bsp]%'


The IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.
SQL IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)


The BETWEEN Operator

The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.
SQL BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

SQL Alias

You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long

or complex table names or column names.

An alias name could be anything, but usually it is short.
SQL Alias Syntax for Tables
SELECT column_name(s)
FROM table_name
AS alias_name
SQL Alias Syntax for Columns
SELECT column_name AS alias_name
FROM table_name

SQL JOIN

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship

between certain columns in these tables.

Tables in a database are often related to each other with keys.

A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value

must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data

in every table.


Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.

* JOIN: Return rows when there is at least one match in both tables
* LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
* RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
* FULL JOIN: Return rows when there is a match in one of the tables


SQL INNER JOIN Keyword

The INNER JOIN keyword return rows when there is at least one match in both tables.
SQL INNER JOIN Syntax
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name


Output : -- Match Between Table 1 and Table 2

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right

table (table_name2).
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.

Output:-- All Entryis of Table-1 with compaire to table 2

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the

left table (table_name1).
SQL RIGHT JOIN Syntax
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

OutPut:-- All Entries of Table 2

SQL FULL JOIN Keyword

The FULL JOIN keyword return rows when there is a match in one of the tables.
SQL FULL JOIN Syntax
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name


All REcords of Table 1 and Table 2


The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
SQL UNION Syntax
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2


The SQL SELECT INTO Statement

The SELECT INTO statement selects data from one table and inserts it into a different table.

The SELECT INTO statement is most often used to create backup copies of tables.
SQL SELECT INTO Syntax

We can select all columns into the new table:
SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename


The CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database.
SQL CREATE DATABASE Syntax
CREATE DATABASE database_name


The CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in a database.
SQL CREATE TABLE Syntax
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)




SQL Constraints

Constraints are used to limit the type of data that can go into a table.

Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

We will focus on the following constraints:

* NOT NULL
* UNIQUE
* PRIMARY KEY
* FOREIGN KEY
* CHECK
* DEFAULT


SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)



SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.



SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.


Indexes

An index can be created in a table to find data more quickly and efficiently.

The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.
SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column_name)


The DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.
DROP INDEX Syntax for MS Access:
DROP INDEX index_name ON table_name


The ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
SQL ALTER TABLE Syntax

To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype


SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
SQL CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition


Data types and ranges for Microsoft Access, MySQL and SQL Server.
Microsoft Access Data Types
Data type Description Storage
Text Use for text or combinations of text and numbers. 255 characters maximum
Memo Memo is used for larger amounts of text. Stores up to 65,536 characters. Note: You cannot sort a memo field. However, they are searchable
Byte Allows whole numbers from 0 to 255 1 byte
Integer Allows whole numbers between -32,768 and 32,767 2 bytes
Long Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes
Single Single precision floating-point. Will handle most decimals 4 bytes
Double Double precision floating-point. Will handle most decimals 8 bytes
Currency Use for currency. Holds up to 15 digits of whole dollars, plus 4 decimal places. Tip: You can choose which country's currency to use 8 bytes
AutoNumber AutoNumber fields automatically give each record its own number, usually starting at 1 4 bytes
Date/Time Use for dates and times 8 bytes
Yes/No A logical field can be displayed as Yes/No, True/False, or On/Off. In code, use the constants True and False (equivalent to -1 and 0). Note: Null values are not allowed in Yes/No fields 1 bit
Ole Object Can store pictures, audio, video, or other BLOBs (Binary Large OBjects) up to 1GB
Hyperlink Contain links to other files, including web pages
Lookup Wizard Let you type a list of options, which can then be chosen from a drop-down list 4 bytes

MySQL Data Types

In MySQL there are three main types : text, number, and Date/Time types.

Text types:
Data type Description
CHAR(size) Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
TINYTEXT Holds a string with a maximum length of 255 characters
TEXT Holds a string with a maximum length of 65,535 characters
BLOB For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.) Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.

Note: The values are sorted in the order you enter them.

You enter the possible values in this format: ENUM('X','Y','Z')
SET Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice

Number types:
Data type Description
TINYINT(size) -128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis
SMALLINT(size) -32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis
MEDIUMINT(size) -8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis
INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
BIGINT(size) -9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis
FLOAT(size,d) A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DOUBLE(size,d) A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DECIMAL(size,d) A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.

Date types:
Data type Description
DATE() A date. Format: YYYY-MM-DD

Note: The supported range is from '1000-01-01' to '9999-12-31'
DATETIME() *A date and time combination. Format: YYYY-MM-DD HH:MM:SS

Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP() *A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MM:SS

Note: The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC
TIME() A time. Format: HH:MM:SS

Note: The supported range is from '-838:59:59' to '838:59:59'
YEAR() A year in two-digit or four-digit format.

Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

*Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various formats, like YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD.
SQL Server Data Types

Character strings:
Data type Description Storage
char(n) Fixed-length character string. Maximum 8,000 characters n
varchar(n) Variable-length character string. Maximum 8,000 characters
varchar(max) Variable-length character string. Maximum 1,073,741,824 characters
text Variable-length character string. Maximum 2GB of text data

Unicode strings:
Data type Description Storage
nchar(n) Fixed-length Unicode data. Maximum 4,000 characters
nvarchar(n) Variable-length Unicode data. Maximum 4,000 characters
nvarchar(max) Variable-length Unicode data. Maximum 536,870,912 characters
ntext Variable-length Unicode data. Maximum 2GB of text data

Binary types:
Data type Description Storage
bit Allows 0, 1, or NULL
binary(n) Fixed-length binary data. Maximum 8,000 bytes
varbinary(n) Variable-length binary data. Maximum 8,000 bytes
varbinary(max) Variable-length binary data. Maximum 2GB
image Variable-length binary data. Maximum 2GB

Number types:
Data type Description Storage
tinyint Allows whole numbers from 0 to 255 1 byte
smallint Allows whole numbers between -32,768 and 32,767 2 bytes
int Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes
bigint Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 8 bytes
decimal(p,s) Fixed precision and scale numbers.

Allows numbers from -10^38 +1 to 10^38 -1.

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
5-17 bytes
numeric(p,s) Fixed precision and scale numbers.

Allows numbers from -10^38 +1 to 10^38 -1.

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
5-17 bytes
smallmoney Monetary data from -214,748.3648 to 214,748.3647 4 bytes
money Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes
float(n) Floating precision number data from -1.79E + 308 to 1.79E + 308.

The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.
4 or 8 bytes
real Floating precision number data from -3.40E + 38 to 3.40E + 38 4 bytes

Date types:
Data type Description Storage
datetime From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds 8 bytes
datetime2 From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds 6-8 bytes
smalldatetime From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute 4 bytes
date Store a date only. From January 1, 0001 to December 31, 9999 3 bytes
time Store a time only to an accuracy of 100 nanoseconds 3-5 bytes
datetimeoffset The same as datetime2 with the addition of a time zone offset 8-10 bytes
timestamp Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable

Other data types:
Data type Description
sql_variant Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
uniqueidentifier Stores a globally unique identifier (GUID)
xml Stores XML formatted data. Maximum 2GB
cursor Stores a reference to a cursor used for database operations
table Stores a result-set for later processing


SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

* AVG() - Returns the average value
* COUNT() - Returns the number of rows
* FIRST() - Returns the first value
* LAST() - Returns the last value
* MAX() - Returns the largest value
* MIN() - Returns the smallest value
* SUM() - Returns the sum

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

* UCASE() - Converts a field to upper case
* LCASE() - Converts a field to lower case
* MID() - Extract characters from a text field
* LEN() - Returns the length of a text field
* ROUND() - Rounds a numeric field to the number of decimals specified
* NOW() - Returns the current system date and time
* FORMAT() - Formats how a field is to be displayed




SQL Interview Questions and Answers

Author: kamlesh          Posted Date: March 15, 2011    Category: Sql Server     Points: 40

1. What are two methods of retrieving SQL?
2. What cursor type do you use to retrieve multiple recordsets?
3. What is the difference between a "where" clause and a "having" clause? - "Where" is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data.Having clause is a kind of filtering command.
4. What is the basic form of a SQL statement to read data out of a table? The basic form to read data out of table is 'SELECT * FROM table_name; ' An answer: 'SELECT * FROM table_name WHERE xyz= 'whatever';' cannot be called basic form because of WHERE clause.
5. What structure can you implement for the database to speed up table reads? - Follow the rules of DB tuning we have to: 1] properly use indexes ( different types of indexes) 2] properly locate different DB objects across different tablespaces, files and so on.3] create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB, LOB and .)
6. What are the tradeoffs with having indexes? - 1. Faster selects, slower updates. 2. Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index.
7. What is a "join"? - 'join' used to connect two or more tables logically with or without common field.
8. What is "normalization"? "Denormalization"? Why do you sometimes want to denormalize? - Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing.
9. What is a "constraint"? - A constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server: PRIMARY/UNIQUE - enforces uniqueness of a particular table column. DEFAULT - specifies a default value for a column in case an insert operation does not provide one. FOREIGN KEY - validates that every value in a column exists in a column of another table. CHECK - checks that every value stored in a column is in some specified list. Each type of constraint performs a specific type of action. Default is not a constraint. NOT NULL is one more constraint which does not allow values in the specific column to be null. And also it the only constraint which is not a table level constraint.
10. What types of index data structures can you have? - An index helps to faster search values in tables. The three most commonly used index-types are: - B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases. - Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only few space and is very fast.(however, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD) - Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.
11. What is a "primary key"? - A PRIMARY INDEX or PRIMARY KEY is something which comes mainly from
database theory. From its behavior is almost the same as an UNIQUE INDEX, i.e. there may only be one of each value in this column. If you call such an INDEX PRIMARY instead of UNIQUE, you say something about
your table design, which I am not able to explain in few words. Primary Key is a type of a constraint enforcing uniqueness and data integrity for each row of a table. All columns participating in a primary key constraint must possess the NOT NULL property.
12. What is a "functional dependency"? How does it relate to database table design? - Functional dependency relates to how one object depends upon the other in the database. for example, procedure/function sp2 may be called by procedure sp1. Then we say that sp1 has functional dependency on sp2.
13. What is a "trigger"? - Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurance of one of the data-modification operations. A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert,update,delete and query statements. Basically, trigger is a set of SQL statements A trigger is a solution to the restrictions of a constraint. For instance: 1.A database column cannot carry PSEUDO columns as criteria where a trigger can. 2. A database constraint cannot refer old and new values for a row where a trigger can.
14. Why can a "group by" or "order by" clause be expensive to process? - Processing of "group by" or "order by" clause often requires creation of Temporary tables to process the results of the query. Which depending of the result set can be very expensive.
15. What is "index covering" of a query? - Index covering means that "Data can be found only using indexes, without touching the tables"
16. What types of join algorithms can you have?
17. What is a SQL view? - An output of a query can be stored as a view. View acts like small table which meets our criterion. View is a precomplied SQL query which is used to select data from one or more tables. A view is like a table but it doesn't physically take any space. View is a good way to present data in a particular format if you use that query quite often. View can also be used to restrict users from accessing the tables directly.


ASP.Net Interview Questions and Answers

Author: kamlesh          Posted Date: March 15, 2011    Category: ASP.Net     Points: 40

ASP.NET 3.5 Interview Questions & Answers :

1. Explain the life cycle of an ASP .NET page.?
Following are the events occur during ASP.NET Page Life Cycle:

1)Page_PreInit
2)Page_Init
3)Page_InitComplete
4)Page_PreLoad
5)Page_Load
6)Control Events
7)Page_LoadComplete
8)Page_PreRender
9)SaveViewState
10)Page_Render
11)Page_Unload

Among above events Page_Render is the only event which is raised by page. So we can't write code for this event.

2. how does the cookies work in asp.net?
we know Http is an state-less protocol which is required for interaction between clinet and server .

so there is an need to remeber state of request raised by an web browser so that
web server can recognize you have already previously visited or not.

There are two types of state management techniques:
a) Client side state management
b) Server - side statemanagement

Using cookies comes under clinet side statemanagement .In HttpResponse we write
Cookie containing sessionId and other information within it.

when a browser made a request to the web server the same cookie is sent to the server where server recognize the session id and get other information stored to it previously.

3. What is Ispostback method in ASP.Net? Why do we use that??
Basically Post back is an action performed by a interactive Webpage. When it goes to the server side for a non-client Operation Server again posts it back to the client and hence the name.
Ex:

if(!IsPostBack)

will not allow the page to post back again n again bcoz it reduces the performance.

4. Can User Control be stored in library?.
I will say "NO"

there are 3 types of controls:
1) User Control
2) Custom Control
3) Web parts

you can reuse User control in the current project in which you have built it, but you can't move it to other project as unless you just copy paste the same file there and make the changes for that project ( which violets the concept of library).

but custom control can be shared between projects. and you can precompile them even as a dll, so this means you can use them in library of any type.

5. what is the difference between application state and caching?
Application Object and Cached Object both falls under Server side State Management.

Application object resides in InProc i.e. on the same server where we hosted our application.
Cache Object resides on server side/ DownStream/Client Side.

Application Object will be disposed once application will stop.
Cache Object can be disposed using Time based cache dependency.

Only one user can access Application Object at a time hence we have to lock it every time we modify it.

6. what is boxing and unboxing?
Boxing is what happens when a value-type object is assigned to a reference-type variable.
Unboxing is what happens when a reference-type variable is assigned to a value-type variable.


7. What are the uses of Reflection??
Reflection is a concept using which we can

1) Load assemblies dynamically
2) Invoke methods at runtime
3) Retriving type information at runtime.

8. What is the use of AutoWireup in asp.net?
AutoEventWireup attribute is used to set whether the events needs to be automatically generated or not.
In the case where AutoEventWireup attribute is set to false (by default) event handlers are automatically required for Page_Load or Page_Init. However when we set the value of the AutoEventWireup attribute to true the ASP.NET runtime does not require events to specify event handlers like Page_Load or Page_Init.

9. what events will occur when a page is loaded?
Below are the events occures during page load.

1) Page_PreInit
2) Page_Init
3) Page_InitComplete
4) Page_PreLoad

10. Where is the View state Data stored?
ViewState data is stored in the hidden field. When the page is submitted to the server the data is sent to the server in the form of hidden fields for each control. If th viewstate of the control is enable true the value is retained on the post back to the client when the page is post backed.

11. What is the difference between custom web user control and a custom web server control?
Web User Control:
1) Easy to Create.
2) It Can be used inside the same Application.(To use it in other application we need to add it to that project.)
3) It Can take advantage of Caching Technique.

Web Server Control:
1) Bit tuff to create as compare to User Control.
2) Easy to use.
3) Can be added to ToolBox.

12. Where do the Cookie State and Session State information be stored?
Cookie Information will be stored in a txt file on client system under a
folder named Cookies. Search for it in your system you will find it.

Coming to Session State
As we know for every process some default space will be allocated by OS.

In case of InProc Session Info will be stored inside the process where our
application is running.
In case of StateServer Session Info will be stored using ASP.NET State Service.
In case of SQLServer Session info will be stored inside Database. Default DB
which will be created after running InstallSQLState Script is ASPState.

13. What is the difference between adding reference in solution Explorer and adding references by USING ?
Adding reference in solution explorer is used to add the DLL for that project for reference only. If you want to utilize that DLL methods/functions in our aspx.cs/.cs file etc you must write using that nameclass library name in file.

14. What are the different types of sessions in ASP.Net? Name them.?
Session Management can be achieved in two ways

1)InProc
2)OutProc

OutProc is again two types
1)State Server
2)SQL Server

InProc
Adv.:
1) Faster as session resides in the same process as the application
2) No need to serialize the data

DisAdv.:
1) Will degrade the performance of the application if large chunk of data is stored
2) On restart of IIS all the Session info will be lost

State Server
Adv.:
1) Faster then SQL Server session management
2) Safer then InProc. As IIS restart
won't effect the session data

DisAdv.:
1) Data need to be serialized
2) On restart of ASP.NET State Service session info will be lost
3)Slower as compared to InProc

SQL Server
Adv.:
1) Reliable and Durable
2) IIS and ASP.NET State Service
restart won't effect the session data
3) Good place for storing large chunk of data

DisAdv.:
1) Data need to be serialized
2) Slower as compare to InProc and State Server
3)Need to purchase Licensed
version of SQL Serve
15. How do you design a website with multilingual support in ASP.NET?
Multilingual website can be created using Globalization and Localization.

Using Globalization we change the Currency Date Numbers etc to Language Specific Format.

To change the string which is there in the label button etc to language specific string we use Localization.

In Localization we have to create different Resource files for different languages.
During this process we use some classes present in System.Resources System.Globalization System.Threading namespaces.
16. What is caching? What are different ways of caching in ASP.NET?
Caching is a technique of persisting the data in memory for immediate access to requesting program calls. This is considered as the best way to enhance the performance of the application.

Caching is of 3 types:
Output Caching - Caches the whole page.
Fragment Caching - Caches a part of the page
Data Caching - Caches the data

17. What is meant by 3-tier architecture.
We generally split our application into 3-Layers
1)Presentation Layer ( Where we keep all web forms Master Pages and User Controls).
2)Business Layer (Where we keep business logic). e.g Code related to manipulating data Custom Exception classes Custom Control classes Login related code if any etc. etc.
3)Data Access Layer (Where we keep code used to interact with DB). e.g. We can have the methods which are using SQL Helper (Application Block).

18. Explain the basic functionality of garbage collector?
Garbage Collector in .Net Framework is used for Automatic Memory Management i.e. it is collect all unused memory area and give to application. system.gc.collect() is a method for release the memory. But remember one think it is only an request i.e. we can't explicitly release the memory by using system.gc.collect().

19. What is the difference between mechine.config and web.config?
machine.config is a system level configuration i.e it is applied on all application in o/s that the configuration is set where as in web.config it is applicable to only one application i.e each asp.net webapplication will contain atleast on web.config file.

20. How can exception be handled with out the use of try catch?
using Exception Management application block

or
Page_error
Application_error objects

21. What is the difference between Response.Redirect and Server.Transfer.
Server.Transfer transfers page processing from one page directly to the next page without making a round-trip back to the client's browser. This provides a faster response with a little less overhead on the server.Server.Transfer does not update the clients url history list or current url.

Response.Redirect is used toredirect the user's browser to another page or site. This performs a trip back to the client where the client's browser is redirected to the new page. The user's browser history list is updated to reflect the new address.

22. Where the assembly is stored in asp.net?.
private are stored in application / bin directory and public are stored in GAC.

23. How we implement Web farm and Web Garden concept in ASP.NET?.
A web farm is a multi-server scenario. So we may have a server in each state of US. If the load on one server is in excess then the other servers step in to bear the brunt.
How they bear it is based on various models.
1. RoundRobin. (All servers share load equally)
2. NLB (economical)
3. HLB (expensive but can scale up to 8192 servers)
4. Hybrid (of 2 and 3).
5. CLB (Component load balancer).
A web garden is a multi-processor setup. i.e. a single server (not like the multi server above).
How to implement webfarms in .Net:
Go to web.config and
Here for mode you have 4 options.
a) Say mode inproc (non web farm but fast when you have very few customers).
b) Say mode StateServer (for webfarm)
c) Say mode SqlServer (for webfarm)
Whether to use option b or c depends on situation. StateServer is faster but SqlServer is more reliable and used for mission critical applications.
How to use webgardens in .Net:
Go to web.config and
Change the false to true. You have one more attribute that is related to webgarden in the same tag called cpuMask.

24. Is there any limit for query string? means what is the maximum size?..
Servers should be cautious about depending on URI lengths above 255 bytes because some older client or proxy implementations may not properly support these lengths.

Query string length depends on browser compatability

IE supports upto 255
Firefox supports upto 4000

25. What is the exact purpose of http handlers?
ASP.NET maps HTTP requests to HttpHandlers. Each HttpHandler enables processing of individual HTTP URLs or groups of URL extensions within an application. HttpHandlers have the same functionality as ISAPI extensions with a much simpler programming model

Ex
1.Default HttpHandler for all ASP.NET pages ->ASP.NET Page Handler (*.aspx)
2.Default HttpHandler for all ASP.NET service pages->ASP.NET Service Handler (*.asmx)

An HttpHandler can be either synchronous or asynchronous. A synchronous handler does not return until it finishes processing the HTTP request for which it is called. An asynchronous handler usually launches a process that can be lengthy and returns before that process finishes
After writing and compiling the code to implement an HttpHandler you must register the handler using your application's Web.config file.

C# Dot Net Interview Questions and Answers

Author: kamlesh          Posted Date: March 15, 2011    Category: C#     Points: 40

1. Does C# support multiple-inheritance?
No.

2. Who is a protected class-level variable available to?
It is available to any sub-class (a class inheriting this class).

3. Are private class-level variables inherited?
Yes, but they are not accessible. Although they are not visible or accessible via the class interface, they are inherited.

4. Describe the accessibility modifier "protected internal".
It is available to classes that are within the same assembly and derived from the specified base class.

5. What's the top .NET class that everything is derived from?
System.Object.

6. What does the term immutable mean?
The data value may not be changed. Note: The variable value may be changed, but the original immutable data value was discarded and a new data value was created in memory.

7. What's the difference between System.String and System.Text.StringBuilder classes?
System.String is immutable. System.StringBuilder was designed with the purpose of having a mutable string where a variety of operations can be performed.

8. What's the advantage of using System.Text.StringBuilder over System.String?
StringBuilder is more efficient in cases where there is a large amount of string manipulation. Strings are immutable, so each time a string is changed, a new instance in memory is created.

9. Can you store multiple data types in System.Array?
No.

10. What's the difference between the System.Array.CopyTo() and System.Array.Clone()?
The Clone() method returns a new array (a shallow copy) object containing all the elements in the original array. The CopyTo() method copies the elements into another existing array. Both perform a shallow copy. A shallow copy means the contents (each array element) contains references to the same object as the elements in the original array. A deep copy (which neither of these methods performs) would create a new instance of each element's object, resulting in a different, yet identacle object.

11. How can you sort the elements of the array in descending order?
By calling Sort() and then Reverse() methods.

12. What's the .NET collection class that allows an element to be accessed using a unique key?
HashTable.

13. What class is underneath the SortedList class?
A sorted HashTable.

14. Will the finally block get executed if an exception has not occurred?­
Yes.

15. What's the C# syntax to catch any possible exception?
A catch block that catches the exception of type System.Exception. You can also omit the parameter data type in this case and just write catch {}.

16. Can multiple catch blocks be executed for a single try statement?
No. Once the proper catch block processed, control is transferred to the finally block (if there are any).

17. Explain the three services model commonly know as a three-tier application.
Presentation (UI), Business (logic and underlying code) and Data (from storage or other sources).

Class Questions

1. What is the syntax to inherit from a class in C#?
Place a colon and then the name of the base class.
Example: class MyNewClass : MyBaseClass

2. Can you prevent your class from being inherited by another class?
Yes. The keyword "sealed" will prevent the class from being inherited.

3. Can you allow a class to be inherited, but prevent the method from being over-ridden?
Yes. Just leave the class public and make the method sealed.

4. What's an abstract class?
A class that cannot be instantiated. An abstract class is a class that must be inherited and have the methods overridden. An abstract class is essentially a blueprint for a class without any implementation.

5. When do you absolutely have to declare a class as abstract?
1. When the class itself is inherited from an abstract class, but not all base abstract methods have been overridden.
2. When at least one of the methods in the class is abstract.

6. What is an interface class?
Interfaces, like classes, define a set of properties, methods, and events. But unlike classes, interfaces do not provide implementation. They are implemented by classes, and defined as separate entities from classes.

7. Why can't you specify the accessibility modifier for methods inside the interface?
They all must be public, and are therefore public by default.

8. Can you inherit multiple interfaces?
Yes. .NET does support multiple interfaces.

9. What happens if you inherit multiple interfaces and they have conflicting method names?
It's up to you to implement the method inside your own class, so implementation is left entirely up to you. This might cause a problem on a higher-level scale if similarly named methods from different interfaces expect different data, but as far as compiler cares you're okay.
To Do: Investigate

10. What's the difference between an interface and abstract class?
In an interface class, all methods are abstract - there is no implementation. In an abstract class some methods can be concrete. In an interface class, no accessibility modifiers are allowed. An abstract class may have accessibility modifiers.

11. What is the difference between a Struct and a Class?
Structs are value-type variables and are thus saved on the stack, additional overhead but faster retrieval. Another difference is that structs cannot inherit.

Method and Property Questions

1. What's the implicit name of the parameter that gets passed into the set method/property of a class?
Value. The data type of the value parameter is defined by whatever data type the property is declared as.

2. What does the keyword "virtual" declare for a method or property?
The method or property can be overridden.

3. How is method overriding different from method overloading?
When overriding a method, you change the behavior of the method for the derived class. Overloading a method simply involves having another method with the same name within the class.

4. Can you declare an override method to be static if the original method is not static?
No. The signature of the virtual method must remain the same. (Note: Only the keyword virtual is changed to keyword override)

5. What are the different ways a method can be overloaded?
Different parameter data types, different number of parameters, different order of parameters.

6. If a base class has a number of overloaded constructors, and an inheriting class has a number of overloaded constructors; can you enforce a call from an inherited constructor to a specific base constructor?
Yes, just place a colon, and then keyword base (parameter list to invoke the appropriate constructor) in the overloaded constructor definition inside the inherited class.

Events and Delegates

1. What's a delegate?
A delegate object encapsulates a reference to a method.

2. What's a multicast delegate?
A delegate that has multiple handlers assigned to it. Each assigned handler (method) is called.

XML Documentation Questions

1. Is XML case-sensitive?
Yes.

2. What's the difference between // comments, /* */ comments and /// comments?
Single-line comments, multi-line comments, and XML documentation comments.

3. How do you generate documentation from the C# file commented properly with a command-line compiler?
Compile it with the /doc switch.

Debugging and Testing Questions

1. What debugging tools come with the .NET SDK?
1. CorDBG - command-line debugger. To use CorDbg, you must compile the original C# file using the /debug switch.
2. DbgCLR - graphic debugger. Visual Studio .NET uses the DbgCLR.

2. What does assert() method do?
In debug compilation, assert takes in a Boolean condition as a parameter, and shows the error dialog if the condition is false. The program proceeds without any interruption if the condition is true.

3. What's the difference between the Debug class and Trace class?
Documentation looks the same. Use Debug class for debug builds, use Trace class for both debug and release builds.

4. Why are there five tracing levels in System.Diagnostics.TraceSwitcher?
The tracing dumps can be quite verbose. For applications that are constantly running you run the risk of overloading the machine and the hard drive. Five levels range from None to Verbose, allowing you to fine-tune the tracing activities.

5. Where is the output of TextWriterTraceListener redirected?
To the Console or a text file depending on the parameter passed to the constructor.

6. How do you debug an ASP.NET Web application?
Attach the aspnet_wp.exe process to the DbgClr debugger.

7. What are three test cases you should go through in unit testing?
1. Positive test cases (correct data, correct output).
2. Negative test cases (broken or missing data, proper handling).
3. Exception test cases (exceptions are thrown and caught properly).

8. Can you change the value of a variable while debugging a C# application?
Yes. If you are debugging via Visual Studio.NET, just go to Immediate window.

ADO.NET and Database Questions

1. What is the role of the DataReader class in ADO.NET connections?
It returns a read-only, forward-only rowset from the data source. A DataReader provides fast access when a forward-only sequential read is needed.

2. What are advantages and disadvantages of Microsoft-provided data provider classes in ADO.NET?
SQLServer.NET data provider is high-speed and robust, but requires SQL Server license purchased from Microsoft. OLE-DB.NET is universal for accessing other sources, like Oracle, DB2, Microsoft Access and Informix. OLE-DB.NET is a .NET layer on top of the OLE layer, so it's not as fastest and efficient as SqlServer.NET.

3. What is the wildcard character in SQL?
Let's say you want to query database with LIKE for all employees whose name starts with La. The wildcard character is %, the proper query with LIKE would involve 'La%'.

4. Explain ACID rule of thumb for transactions.
A transaction must be:
1. Atomic - it is one unit of work and does not dependent on previous and following transactions.
2. Consistent - data is either committed or roll back, no "in-between" case where something has been updated and something hasn't.
3. Isolated - no transaction sees the intermediate results of the current transaction).
4. Durable - the values persist if the data had been committed even if the system crashes right after.

5. What connections does Microsoft SQL Server support?
Windows Authentication (via Active Directory) and SQL Server authentication (via Microsoft SQL Server username and password).

6. Between Windows Authentication and SQL Server Authentication, which one is trusted and which one is untrusted?
Windows Authentication is trusted because the username and password are checked with the Active Directory, the SQL Server authentication is untrusted, since SQL Server is the only verifier participating in the transaction.

7. What does the Initial Catalog parameter define in the connection string?
The database name to connect to.

8. What does the Dispose method do with the connection object?
Deletes it from the memory.
To Do: answer better. The current answer is not entirely correct.

9. What is a pre-requisite for connection pooling?
Multiple processes must agree that they will share the same connection, where every parameter is the same, including the security settings. The connection string must be identical.

Assembly Questions

1. How is the DLL Hell problem solved in .NET?
Assembly versioning allows the application to specify not only the library it needs to run (which was available under Win32), but also the version of the assembly.

2. What are the ways to deploy an assembly?
An MSI installer, a CAB archive, and XCOPY command.

3. What is a satellite assembly?
When you write a multilingual or multi-cultural application in .NET, and want to distribute the core application separately from the localized modules, the localized assemblies that modify the core application are called satellite assemblies.

4. What namespaces are necessary to create a localized application?
System.Globalization and System.Resources.

5. What is the smallest unit of execution in .NET?
an Assembly.

6. When should you call the garbage collector in .NET?
As a good rule, you should not call the garbage collector. However, you could call the garbage collector when you are done using a large object (or set of objects) to force the garbage collector to dispose of those very large objects from memory. However, this is usually not a good practice.

7. How do you convert a value-type to a reference-type?
Use Boxing.

8. What happens in memory when you Box and Unbox a value-type?
Boxing converts a value-type to a reference-type, thus storing the object on the heap. Unboxing converts a reference-type to a value-type, thus storing the value on the stack.
« Previous12Next »
 




Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend