Friday, October 31, 2008

Types of Join in SQL Server

Join
By using joins, we can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table. Sql server joins are used to combine result data from two or more tables.
There are three types of sql server joins.

1. Inner Join
2. Outer Join
3. Cross Joins

1. Inner Join

Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. Inner joins return rows only when there is at least one row from both tables that matches the join condition. Eg:Retrieving all rows where the student identification number is the same in both the students and courses tables.

2. Outer join
Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause.

There are there 3 types of outer joins.

a. Left outer join
All rows are retrieved from the left table referenced with a left outer join. If matching records are found then it will display data of right table with left table data otherwise put a null values instead of right table data.

b.Right outer join

All rows are retrieved from the right table referenced in a right outer join. If matching records are found then it will display data of left table with right table data otherwise put a null value instead of left table data.

c.Full outer join
All rows from both tables are returned in a full outer join.This joins are combination of both left outer join and right outer join.

3. Cross join

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

Self-join

A table can be joined to itself in a self-join.

Wednesday, October 29, 2008

Dispose method

Dispose method of IDisposable interface performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources. This method is used to explicitly release unmanaged resources in conjunction with the garbage collector.

When implementing this method, ensure that all held resources are freed by propagating the call through the containment hierarchy. For example, if an object A allocates an object B, and object B allocates an object C, then A's Dispose implementation must call Dispose on B, which must in turn call Dispose on C. An object must also call the Dispose method of its base class if the base class implements IDisposable.
Because the Dispose method must be called explicitly, objects that implement IDisposable must also implement a finalizer to handle freeing resources when Dispose is not called. Once the Dispose method has been called, it is unnecessary for the garbage collector to call the disposed object's finalizer. Dispose implementation calls the GC.SuppressFinalize method, to prevent automatic finalization.


 
// Implement IDisposable. Do not make this method virtual. A derived class should not be able to override this method.

public void Dispose()
{
// This object will be cleaned up by the Dispose method. Therefore, you should call GC.SupressFinalize to take this object off the finalization queue and prevent finalization code for this object from executing a second time.
GC.SuppressFinalize(this);
}

Finalize method

Finalize method allows an Object to free resources and perform other cleanup operations before the Object is reclaimed by garbage collection. This method acts as a safeguard to clean up resources in the event that the Dispose method is not called. Implement a Finalize method to clean up only unmanaged resources such as file handles or database connections. No need to implement a Finalize method for managed objects, because the garbage collector cleans up managed resources automatically.

The scope of the Object.Finalize method is protected. Every implementation of Finalize in a derived type must call its base type's implementation of Finalize. Finalize method is automatically called after an object becomes inaccessible, unless the object is re-registered using ReRegisterForFinalize and exempted from finalization by a call to SuppressFinalize.

Limitations:
1. The exact time when the finalizer executes during garbage collection is undefined. Resources are not guaranteed to be released at any specific time, unless calling a Close method or a Dispose
method.

2. The finalizers of two objects are not guaranteed to run in any specific order, even if one object refers to the other. That is, if Object A has a reference to Object B and both have finalizers, Object B might have already finalized when the finalizer of Object A starts.

3. The thread on which the finalizer is run is unspecified.
Destructors are the C# mechanism for performing cleanup operations.
In C#
// Use C# destructor syntax for finalization code.
// This destructor will run only if the Dispose method does not get called.
// It gives your base class the opportunity to finalize.
// Do not provide destructors in types derived from this class.
~MyResource()
{
     Dispose(false);
}
In VB.Net
' This finalizer will run only if the Dispose method does not get called.
' It gives your base class the opportunity to finalize.
' Do not provide finalize methods in types derived from this class.
Protected Overrides Sub Finalize()
Dispose(False)
      MyBase.Finalize()
End Sub

Tuesday, October 28, 2008

Garbage Collection

The garbage collector manages the allocation and release of memory for the application. Each time the new operator is used to create an object, the runtime allocates memory for the object from the managed heap. As long as address space is available, the runtime continues to allocate space for new objects. However, memory is not infinite. Eventually the garbage collector must perform a collection in order to free some memory. The garbage collector's optimizing engine determines the best time to perform a collection. When the garbage collector performs a collection, it checks for objects in the managed heap that are no longer being used by the application and performs the necessary operations to reclaim their memory.

Thursday, October 23, 2008

Difference between .ToString() and Convert.ToString

Both converts the specified value to its equivalent String representation.

Convert.Tostring handles null value and returns string.Empty.

But Tostring doesn't handle null value and throws a NullReferenceException.

object obj = null;
string b = Convert.ToString(obj); //value of b will be ""
string a = obj.ToString(); // throws NullReferenceException

Monday, October 20, 2008

Find the second largest salary in the Employee table

SQL Statement To find the second largest salary from the Employee table

Finding result in 4 ways :

SELECT TOP 1 Salary FROM
(SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) Temp
ORDER BY Salary ASC

SELECT TOP 1 Salary FROM Employee
WHERE Salary NOT IN (SELECT TOP 1 Salary FROM Employee ORDER BY Salary DESC)ORDER BY Salary DESC

SELECT Salary FROM Employee A
WHERE 2 = (SELECT count(*) FROM Employee B WHERE A.Salary <= B.Salary)

SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)

Saturday, October 18, 2008

Difference beteween Array and ArrayList

Array
An array is a data structure that contains a number of variables of the same type. Array Provides methods for creating, manipulating, searching, and sorting arrays. An array can be Single-Dimensional, Multidimensional or Jagged. Arrays are zero indexed: that is, an array with n elements is indexed from 0 to n-1. The default value of numeric array elements are set to zero, and reference elements are set to null. A jagged array is an array of arrays, and therefore its elements are reference types and are initialized to null.

ArrayList
ArrayList Represents a dynamically sized, index-based collection of objects. It Implements the IList interface using an array whose size is dynamically increased as required. The capacity of an ArrayList is the number of elements the list can hold. As elements are added to an ArrayList, the capacity is automatically increased as required through reallocation.

Difference
1. The capacity of an Array is fixed, whereas the capacity of an ArrayList is automatically increased as required. Arrays cannot be changed in size at runtime (except using 'ReDim' which will create a new array, copy the old array in the new array and destroy the old array).
2. ArrayList provide methods that add, insert, or remove a range of elements. In Array, we can get or set the value of only one element at a time.
3. ArrayList provide methods that return read-only and fixed-size wrappers to the collection. Array does not.
4. We can set the lower bound of an Array, but the lower bound of an arrayList is always zero.
5. An Array can have multiple dimensions, while an ArrayList can have only one dimension.
6. An Array of a specific type (other than Object) has better performance than an ArrayList because the elements of ArrayList are of type Object and, therefore, boxing and unboxing typically occur when storing or retrieving a value type.
7.Array is in the System namespace. ArrayList is in the System.Collections namespace.

Thursday, October 16, 2008

Primary key and Unique key difference

Primary key and unique are Entity integrity constraints.

Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist and no null values are entered.

Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values.

Difference
1. Unique key can be null, but Primariy key can't be null.
2. Primariy key can be refrenced to other table as FK.
3. We can have multiple Unique key in a table, but Primariy key is one and only one.
4. Primariy key itself is a unique key.

Difference between DELETE and TRUNCATE

1. DELETE is a DML command. TRUNCATE is a DDL command.

2. After DELETE we can rollback the records. After TRUNCATE we cannot rollback the records.

3. We can use WHERE Clause with DELETE Command. We can't use WHERE Clause with TRUNCATE command. TRUNCATE delete all rows from the table.
4. After using DELETE Command the memory occupied not released untill the user gives COMMIT. After using TRUNCATE Command the memory realeased immediately.

5. TRUNCATE do not check constraints. DELETE checks constraints.
6. TRUNCATE is faster than DELETE and uses Less transaction log space. DELETE removes rows one at a time and records each deleted row in the transaction log. TRUNCATE TABLE removes the data by deallocating all data pages used to store the table data and records only the page deallocations in the transaction log.

7. DELETE can activate trigger. TRUNCATE cannot activate a trigger because the operation does not log individual row deletions.

8. TRUNCATE reset the counter used by an identity column. DELETE retain the identity counter.

9. TRUNCATE cannot be used with tables referenced by foreign keys.

Difference between UNION and UNION ALL

The UNION command is used to select related information from two or more tables, like the JOIN statement. Union combines the result sets of two or more queries into a single result set, which includes all rows that belong to all queries in the union. To use Union, the number and the order of the columns and data type of the columns selected must be the same in all queries.

 

Difference
The UNION command only selects distinct values.
The UNION ALL command is equal to the UNION command, but UNION ALL selects all alues.

Stored Procedure and User-Defined function, Difference

Stored Procedure
A stored procedure is a set of Transact-SQL statements that has been compiled into a single execution plan and stored in the database. We can create applications that execute the stored procedures and process the results.
Stored procedures return data in four ways:
1. Output parameters, which can return either data or a cursor variable.
2. Return codes, which are always an integer value.
3. A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
4. A global cursor that can be referenced outside the stored procedure.


User-Defined Functions
Functions are subroutines made up of one or more Transact-SQL statements that can be used to encapsulate code for reuse. A user-defined function takes zero or more input parameters and returns either a scalar value or a table.

Difference Between Stored procedure and User-Defined Function
1. A Function return rowsets, tables, VARCHARs, INTs, and other data types. While a Stored procedure can only return an INT value.
2. Function always return a single value to the caller, while
Stored procedures do not.
3. When Transact-SQL encounters an error the function stops, while Transact-SQL will ignore an error in a Stored procedure and proceed to the next statement(provided Err handling).
4. A Stored procedure can be used in an XML FOR clause, a Function can't be.
5. Result of the function can be used within a SQL staement. A result set or retun value of the stored procedurecannot be used within a SQL statement.


Wednesday, October 01, 2008

Assemblies in .Net

Assemblies
Assemblies are the building blocks of .NET Framework applications which form the fundamental unit of deployment, versioning, reuse, activation scoping, and security permissions. An assembly is a collection of types and resources that are built to work together and form a logical unit of functionality.
Each time you create a Microsoft Windows Application, Windows Service, Class Library, or other application with Visual Basic .NET, you're building a single assembly. Each assembly is stored as an .exe or .dll file.
An assembly performs the following functions:

1. It contains code that the common language runtime executes. MSIL code will not be executed if it does not have an associated assembly manifest.

2. A security boundary : An assembly is the unit at which permissions are requested and granted.

3. A type boundary : Every type's identity includes the name of the assembly in which it resides.

4. A reference scope boundary : The assembly's manifest contains assembly metadata that is used for resolving types and satisfying resource requests.

5. A version boundary : The assembly is the smallest versionable unit in the common language runtime. All types and resources in the same assembly are versioned as a unit. The assembly's manifest describes the version dependencies you specify for any dependent assemblies.

6. A deployment unit : When an application starts, only the assemblies that the application initially calls must be present. Other assemblies, such as localization resources or assemblies containing utility classes, can be retrieved on demand.7.Assembly support side-by-side execution.

Assemblies can be static or dynamic. Static assemblies can include .NET Framework types (interfaces and classes), as well as resources for the assembly (bitmaps, JPEG files, resource files, and so on). Static assemblies are stored on disk in portable executable (PE) files. .NET Framework also create dynamic assemblies, which run directly from memory and are not saved to disk before execution. Dynamic assemblies can be saved to disk after execution.

Assembly Benefits
Assemblies are designed to simplify application deployment and to solve versioning problems that can occur with component-based applications. They are self-describing components having no dependencies on registry entries. So assemblies enable zero-impact application installation. They also simplify uninstalling and replicating applications.

Assembly Contents
In general, an assembly can contain :
1. Assembly manifest
2. Type metadata
3. Microsoft intermediate language (MSIL)
4. A set of resources

Assembly Manifest
Assembly manifest is assembly metadata which contains a collection of data that describes how the elements in the assembly relate to each other. An assembly manifest contains all the metadata needed to specify the assembly's version requirements and security identity, and all metadata needed to define the scope of the assembly and resolve references to resources and classes. The assembly manifest can be stored in either a PE file (an .exe or .dll) with Microsoft intermediate language (MSIL) code or in a standalone PE file that contains only assembly manifest information.

In a single-file assembly, the manifest is incorporated into the PE file.A multifile assembly can be with a standalone manifest file or with the manifest incorporated into one of the PE files in the assembly.

Information contained in the assembly manifest :
1. The assembly name and version
2. The culture or language the assembly supports.
3. The public key for any strong name assigned to the assembly
4. A list of files in the assembly with hash information
5. Type reference information : Information used by the runtime to map a type reference to the file that contains its declaration and implementation.
6. Information on referenced assemblies : A list of other assemblies that are referenced by the assembly.