Required Hardware to install Visual Studio 2008

Requirements vary for different combinations of components within Visual Studio 2008. To install Visual Studio 2008, you need:

* Computer with a 1.6 GHz or faster processor
* Visual Studio 2008 can be installed on the following operating systems:
o Windows Vista® (x86 & x64) - all editions except Starter Edition
o Windows® XP (x86 & x64) with Service Pack 2 or later - all editions except Starter Edition
o Windows Server® 2003 (x86 & x64) with Service Pack 1 or later (all editions)
o Windows Server 2003 R2 (x86 and x64) or later (all editions)
* 384 MB of RAM or more (768 MB of RAM or more for Windows Vista)
* 2.2 GB of available hard-disk space
* 5400 RPM hard drive
* 1024 x 768 or higher-resolution display
* DVD-ROM Drive

FAQ's of SQL Server

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.

Define candidate key, alternate key, composite key.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called composite key.

What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them. See CREATE DEFUALT in books online.

Whar is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.

Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of cursors. Here is an example:

If you have to give a flat hike to your employees using the following criteria:

Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike

In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:

UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END

Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing,

What is a join and explain different types of joins?

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 a Stored Procedure?

Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.

What is the basic difference between clustered and a non-clustered index?

The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

What are cursors?

Well cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retreival one by one and remove rows which have duplicate values.

Which TCP/IP port does SQL Server run on?

SQL Server runs on port 1433 but we can also change it for better security.

Can we use Truncate command on a table which is referenced by FOREIGN KEY?

No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.

What is the use of DBCC commands?

DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.

What is the difference between a HAVING CLAUSE and 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.

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.

Can you link only other SQL Servers or any database servers such as Oracle?

We can link any server provided we have the OLE-DB provider from Microsoft to allow a link. For Oracle we have a OLE-DB provider for oracle that microsoft provides to add it as a linked server to the sql server group.

How do you troubleshoot SQL Server if its running very slow?

First check the processor and memory usage to see that processor is not above 80% utilization and memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users and current SQL activities and jobs running which might be a problem. Third would be to run UPDATE_STATISTICS command to update the indexes.

What is log shipping?

Can we do logshipping with SQL Server 7.0 - Logshipping is a new feature of SQL Server 2000. We should have two SQL Server - Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan.

Let us say the SQL Server crashed and you are rebuilding the databases including the master database what procedure to you follow?

For restoring the master db we have to stop the SQL Server first and then from command line we can type SQLSERVER .m which will basically bring it into the maintenance mode after which we can restore the master db.

What is BCP? When do we use it?

BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same.

Why is a UNION ALL faster than a UNION?

UNION ALL faster than a UNION because for union operation server needs to remove the duplicate values but for union all its not. Thats why the UNOIN ALL is fater than UNION Operation. It is recommended that if you know that the union set operation never returns duplicate values than you must use UNION ALL instead of UNION.

ASP.NET FAQ Interview Questions Websites

I would like share some best ASP.NET FAQ Interview Questions and Answers web sites.
Here Some best sites,
· http://www.syncfusion.com/faq/aspnet/default.aspx
· http://www.aspnetfaq.com
· http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=4081&lngWId=10 ( Answers will be avilable in the downloadable document.-at bottom)
· http://blogs.crsw.com/mark/articles/254.aspx, & http://blog.daveranck.com/archive/2005/01/20/355.aspx
· http://www.techinterviews.com/?p=176 & http://www.techinterviews.com/?p=193
· ASP.NET Interview Questions
· C# Interview Questions
· DotNet Remoting Interview Questions (
· DotNet WindowsForms Interview Questions
· http://www.webhostforasp.net
· http://www.toqc.com/entropy/TheAnswers1.html
· http://www.toqc.com/entropy/TheAnswers2.html
· http://www.akaas.net/jobs/asp-net-interview-questions.htm
· http://www.akaas.net/dot-net-faqs.htm
· http://www.akaas.net/jobs/asp-net-interview-questions.htm
· http://moredotnet.googlepages.com
· http://www.interviewcorner.com/
· www.kyapoocha.com
· www.coolinte rview.com
· www.geekinterview.com
· http://aspnetinfo.googlepages.com
· http://dotnet-question-answer.blogspot.com/
· http://www.mytechsky.com
· http://www.dotnetquestion.info/dot_net/interview.htm
· http://www.coolinterview.com/

ADO.NET Transactions

A transaction is a group SQL statements to be executed as one unit to modify the state of database, a transaction requires a database connection and a transaction object. The crux of utilizing transactions with SQL Server and ADO.NET is the SqlTransaction class. This class name varies depending upon the database platform utilized. For instance, the transaction class for OLEDB databases is OleDbTransaction.

The System.Data.SqlClient namespace contains the SqlTransaction class. There are two properties in the class:
· Connection: Indicates the SqlConnection object associated with the transaction.
· IsolationLevel: Specifies the transaction's IsolationLevel.

The IsolationLevel property is an Enumeration object with the following members:
· Chaos: The pending changes from more highly isolated transactions cannot be overwritten.
· ReadCommitted: Shared locks are held while the data is read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data.
· ReadUncommitted: A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.
· RepeatableRead: Locks are placed on all data that is used in a query, preventing other users from updating the data. This prevents nonrepeatable reads, though phantom rows are still possible.
· Serializable: A range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete.

The IsolationLevel allows a level of record locking, The SqlTransaction object provides methods as well. You may use the following methods to work with the transaction:
· Commit: The database transaction is committed.
· Rollback: A database transaction is rolled back from a pending state. A transaction may not be rolled back once it's committed.
· Save: Creates a savepoint in the transaction that can be used to roll back a portion of the transaction, and specifies the savepoint name.

Example on SqlTransaction:
SqlConnection sqlConn = new SqlConnection(ConnectionString);
cmd1 = new SqlCommand();
cmd2=new SqlCommand();
cmd1.Connection = sqlConn;
cmd2.Connection = sqlConn;
cmd1.CommandText = "insert into Employee(EmpName,EmpSalary,deptid) values('A',1000,9)";
cmd2.CommandText = "Update Employee set EmpSalary=EmpSalary + 1000 where EmpName='A'"; // for commit
//cmd2.CommandText = "Update Employee set EmpSalary=EmpSalary = 1000 where EmpName='A'"; // for Rollback
SqlTransaction SqlTran;
sqlConn.Open();
SqlTran = sqlConn.BeginTransaction();
cmd1.Transaction = SqlTran;
cmd2.Transaction = SqlTran;
try
{
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery(); // if both command excuted sucessfully then will commit if any of command fails that is attched to sqltransaction then that will be roll backed.
SqlTran.Commit();
MessageBox.Show("Transcation Committed Sucessfully");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
SqlTran.Rollback();
MessageBox.Show("Transaction Rollbacked");
}
finally
{
sqlConn.Close();
}
Note: Once a transaction has begin over a connection, a command without its transaction property set cannot be executed in that connection unless either the transaction is committed or roll backed.
Transactions help the database in satisfying all the ACID (Atomic, Consistent, Isolated, and Durable). Transaction processing has improved a lot in ADO.NET 2.0.

Stored Procedure in ADO.NET

It is a precompiled set of SQL statement which is compiled in native form and stored in the backend. They are very fast on their first execution also.

Benefits Using SP:
· They allow modular programming.
You can create the procedure once, store it in the database, and call it any number of times in your program. Stored procedures can be created by a person who specializes in database programming, and they can be modified independently of the program source code.
· They allow faster execution.
If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.
· They can reduce network traffic.
An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
· They can be used as a security mechanism.
Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.
· It’s easy to update logic/code in them because it stored at one place in the database.

Sample Stored Procedure:
Create proc GetSalary(@EmpID int,@EmpSal Money out) as
Begin
select @EmpSal=EmpSalary from Employee where EmpID=@EmpID
End

Example:
SqlConnection con=new SqlConnection();
con.ConnectionString = “your connection string”
SqlCommand cmd=new SqlCommand();
cmd.CommandText="GetSalary"; //SP name
cmd.CommandType=CommandType.StoredProcedure;
cmd.Connection=con;

SqlParameter ID=new SqlParameter("@EmpID",SqlDbType.Int);
SqlParameter sal=new SqlParameter("EmpSal",SqlDbType.Money);
sal.Direction=ParameterDirection.Output;
sal.Value = DBNull.Value;
// Parameter direction is enum it has the parameters
// Input - The parameter is an input parameter.
// InputOutput - The parameter is capable of both input and output.
// Output - The parameter is an output parameter.
// ReturnValue - The parameter represents a return value from an operation such as a stored procedure, built-in function, or user-defined function.

ID.Value= “provide employee id”;
cmd.Parameters.Add(ID);
cmd.Parameters.Add(sal);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show(sal.Value.ToString());
Note:
· Every Input Parameter must have a value before the command is executed. If required we can set to DBNull.Value.
· As far as possible try to execute all SQL statements using SP.

MARS Concept in SqlDataReader

Note that while a DataReader is open, the Connection is in use exclusively by that DataReader. You will not be able to execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.
This is over come in .Net 2005 by a concept called MARS(MultipleActiveResources).
If MultipleActiveResources=True is added to connection string then more that one data reader can be opened at the same time in that single connection object. It’s a new feature in ADO.NET 2.0.

Example Data Reader with MARS Concept:
SqlConnection sqlConn = new SqlConnection("Data Source=Server1;Initial Catalog=XXXXDB;Persist Security Info=True;User ID=XXXXX;Password=XXXXX;MultipleActiveResultsets=True");

// In above connection string if you did not write MultipleActiveResultsets=True will get Exception as {There is already an open DataReader associated with this Command which must be closed first.}

SqlCommand cmdDept = new SqlCommand();
cmdDept.Connection=sqlConn;
cmdDept.CommandText="select DeptID,DeptName from Department";
sqlConn.Open();
SqlDataReader DRDept=cmdDept.ExecuteReader();
string tempStr=string.Empty ;

while (DRDept.Read())
{
SqlCommand cmdEmp=new SqlCommand("select * from Employee where Deptid=" + DRDept["DeptID"],sqlConn);
SqlDataReader DREmp = cmdEmp.ExecuteReader();

while (DREmp.Read())
{
tempStr = tempStr + DREmp["EmpID"] + "\t" + DREmp["EmpName"] + "\t" + DREmp["EmpSalary"] + "\t"+ DRDept["DeptName"] + "\n";
}

DREmp.Close();
}
DRDept.Close();
sqlConn.Close();

MessageBox.Show(tempStr);

Note: The result of one select statement should not overlap with the other result of another statement, Means both the select statements should not perform on Same Table.

Option in VB.Net

In VB Compiler has options like

1. Option Strict: on/off
Restricts implicit data type conversions to only widening conversions.
If used, the Option Strict statement must appear in a file before any other source code statements.
Visual Basic allows conversions of many data types to other data types. Data loss can occur when the value of one data type is converted to a data type with less precision or smaller capacity. A run-time error occurs if such a narrowing conversion fails. Option Strict ensures compile-time notification of these narrowing conversions so they can be avoided.
In addition to disallowing implicit narrowing conversions, Option Strict generates an error for late binding. An object is late bound when it is assigned to a variable that is declared to be of type Object.
Because Option Strict On provides strong typing, prevents unintended type conversions with data loss, disallows late binding, and improves performance, its use is strongly recommended.

2. Option Comapre: Binary/Text
Forces explicit declaration of all variables in a file.
Binary :Optional. Results in string comparisons based on a sort order derived from the internal binary representations of the characters.
Text :Optional. Results in string comparisons based on a case-insensitive text sort order determined by your system's locale.

3. Option Explicit: on/off
Forces explicit declaration of all variables in a file.

The options will not avaliable in C#.