VS 2008 Solution Explorer Autohide

In Visual Studio 2008,

I lost AutoHide feature on that window. when I right click on Solution explorer I see that AutoHide is disabled.

How can I enable it.

 2) Also I like all my autohide windows on left hand. how can I move right hand windows to left hand like resource view.

Solution:

Two things you can try:

1. Window->ResetWindow Layout

2. Tools->Import and Export Settings… Reset All Settings (Whether you save the current settings or not is your choice).

Memory Leak in CLR

Definition of memory leak

A memory leak occurs when memory is allocated in a program and is never returned to the operating system, even though the program does not use the memory any longer. The following are the four basic types of memory leaks:

• In a manually managed memory environment: Memory is dynamically allocated and referenced by a pointer. The pointer is erased before the memory is freed. After the pointer is erased, the memory can no longer be accessed and therefore cannot be freed.
• In a dynamically managed memory environment: Memory is disposed of but never collected, because a reference to the object is still active. Because a reference to the object is still active, the garbage collector never collects that memory. This can occur with a reference that is set by the system or the program.
• In a dynamically managed memory environment: The garbage collector can collect and free the memory but never returns it to the operating system. This occurs when the garbage collector cannot move the objects that are still in use to one portion of the memory and free the rest.
• In any memory environment: Poor memory management can result when many large objects are declared and never permitted to leave scope. As a result, memory is used and never freed.

When it occurs?

Because of the garbage collection package that is implemented in the Microsoft .NET Framework, it is not possible to have a memory leak in managed code. This suggests two questions: How then can a memory leak occur? Why does it appear that you have a memory leak?

A memory leak can occur in a .NET Framework application when you use unmanaged code as part of the application. This unmanaged code can leak memory, and the .NET Framework runtime cannot address that problem.

Additionally, a project may only appear to have a memory leak. This condition can occur if many large objects (such as DataTable objects) are declared and then added to a collection (such as a DataSet). The resources that these objects own may never be released, and the resources are left alive for the whole run of the program. This appears to be a leak, but actually it is just a symptom of the way that memory is being allocated in the program.

If a collection is declared at the global level of the program, and objects are declared throughout the program and added to that collection, this means that even though the objects are no longer in scope, the objects remain alive because they are still being referenced.

Each time that this occurs, the amount of memory that the program is using increases. The memory does not decrease until the end of the program or the release of the objects from the collection. When you watch the program on a performance monitor, this appears to be a memory leak, but it is not. The program still has control over the memory but has chosen not to release it. The fact that the program still has control prevents this from being a memory leak, but the fact that the program keeps increasing the amount of memory used can make it appear to be a memory leak.

ISNULL vs. COALESCE

ISNULL()

ISNULL is a TSQL Function which is built into SQL Server. It is NOT a function defined by ANSI-92 – rather it is a feature which Microsoft has elected to include in TSQL in addition to the ANSI SQL standard.

ISNULL() accepts two parameters. The first is evaluated, and if the value is null, the second value is returned (regardless of whether or not it is null). The following queries will return the second parameter in both cases:

SELECT ISNULL(NULL, 1)
--Returns 1
SELECT ISNULL(NULL, NULL)
--Returns NULL

COALESCE()

COALESCE() is a TSQL function which, like ISNULL, is built into SQL Server. Unlike ISNULL, COALESCE is also a part of the ANSI-92 SQL Standard. Coalesce returns the first non-null expression in a list of expressions. The list can contain two or more items, and each item can be of a different data type. The following are valid examples of COALESCE:

SELECT COALESCE(NULL, 1)
--Returns 1

SELECT COALESCE(NULL, 3, NULL, 1)
--Returns 3

ISNULL vs. COALESCE

Whenever multiple methods exist for addressing a single problem, the inevitable question is: which method is better? There are a few differences between the two functions which make COALESCE come out on top more often than not:
- COALESCE is ANSI-92 compliant. In the event that you need to port your code to another RDBMS, COALESCE will not require rework.
- COALESCE accepts greater than two expressions, whereas ISNULL accepts only two. In order to compare three expressions with ISNULL, you would have to nest expressions:
SELECT ISNULL(ISNULL(Col1, Col2), Col3)
- ISNULL constrains the result of a comparison of parameterrs to the datatype of the first value. For example, the following query will produce some often undesirable results using ISNULL, however it will behave as expected with COALESCE:

DECLARE @Field1 char(3), @Field2 char(50)
SET @Field2 = 'Some Long String'

SELECT ISNULL(@Field1, @Field2)
--Returns 'Som'
SELECT COALESCE(@Field1, @Field2)
--Returns 'Some Long String'

Note: In other situations, COALESCE will produce unexpected results. COALESCE by nature promotes it’s arguments to the highest datatype among compatable arguments (arguments which are not explicitly case, and which aren’t compatable, will of course throw an error). When using COALESCE on an integer and a datetime, in that order, COALESCE will cast the integer as a datetime.

For example:
SELECT COALESCE(5, GETDATE())
Will not return 5, it will return 1900-01-06 00:00:00.000 (5 as a datetime). 

ISNULL vs. COALESCE

Whenever multiple methods exist for addressing a single problem, the inevitable question is: which method is better? There are a few differences between the two functions which make COALESCE come out on top more often than not:
- COALESCE is ANSI-92 compliant. In the event that you need to port your code to another RDBMS, COALESCE will not require rework.
- COALESCE accepts greater than two expressions, whereas ISNULL accepts only two. In order to compare three expressions with ISNULL, you would have to nest expressions:
SELECT ISNULL(ISNULL(Col1, Col2), Col3)
- ISNULL constrains the result of a comparison of parameterrs to the datatype of the first value. For example, the following query will produce some often undesirable results using ISNULL, however it will behave as expected with COALESCE:

DECLARE @Field1 char(3), @Field2 char(50)
SET @Field2 = 'Some Long String'

SELECT ISNULL(@Field1, @Field2)
--Returns 'Som'
SELECT COALESCE(@Field1, @Field2)
--Returns 'Some Long String'

Note: In other situations, COALESCE will produce unexpected results. COALESCE by nature promotes it’s arguments to the highest datatype among compatable arguments (arguments which are not explicitly case, and which aren’t compatable, will of course throw an error). When using COALESCE on an integer and a datetime, in that order, COALESCE will cast the integer as a datetime.

 For example:
SELECT COALESCE(5, GETDATE())
Will not return 5, it will return 1900-01-06 00:00:00.000 (5 as a datetime).

 
source:
 http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ISNULL_COALESCE&referringTitle=Homeb

Finding Duplicates in a Table

Here’s a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:

SELECT email,
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

You could also use this technique to find rows that occur exactly once:

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

For further reading:
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=DuplicateRows&referringTitle=Home

@@IDENTITY And SCOPE_IDENTITY()

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope.

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

Source: http://msdn.microsoft.com/en-us/library/ms190315.aspx

List All Database Objects in SQL Server

Below statemnent will list all tables int he current database.

USE YourDBName
GO

1) SELECT * FROM sys.Tables

2) SELECT 

 

* FROM  information_schema.Tables

Also, sysobjects, syscolumns and systypes are the database objects that we would be using here.

Sysobjects contains information about each object in the database. this includes the object name, the user id of the user who created this object and many other useful information.

      select * from sysobjects;

The most useful column in this table is the type column. This column signifies what type of object is in the returned rowset. specifying

      select * from sysobjects where type = ‘u’ ;

will return the list of tables on the current database. Here’s a list of possible values for xtype:

• C : CHECK constraint
• D : Default or DEFAULT constraint
• F : FOREIGN KEY constraint
• L : Log
• P : Stored procedure
• PK : PRIMARY KEY constraint (type is K)
• RF : Replication filter stored procedure
• S : System tables
• TR : Triggers
• U : User table
• UQ : UNIQUE constraint (type is K)
• V : Views
• X : Extended stored procedure
• TF : Functions

You can use syscolumns  to retrieve columns on the database. doing

      select * from syscolumns

would return a result containing column information which you can use to determine the scale, data type, precision and etc. The xtype on the syscolumns table acts differently from the sysobjects. the xtype column here represents the datatatype of that column. running this script:

   select * from syscolumns where xtype = 167;

will return all columns that has a datatype of varchar. heres a list of possible values for this xtype column:

34 image
35 text
36 uniqueidentifier
48 tinyint
52 smallint
56 int
58 smalldatetime
59 real
60 money
61 datetime
62 float
98 sql_variant
99 ntext
104 bit
106 decimal
108 numeric
122 smallmoney
127 bigint
165 varbinary
167 varchar
173 binary
175 char
189 timestamp
231 nvarchar
239 nchar
241 xml
231 sysname

 

I got this xtypes by running : select xtype, name from systypes; which basically contains a list of available sql datatypes.

Difference between Function and Stored procedure

The following are the differences between a User Defined Function and a Stored Procedure in SQL Server

Stored Procedure support deffered name resolution where as functions do not support deffered name resolution. User Defined Function can be used in a select statement where as you cannot use a stored procedure in a select statement. UDF’s cannot return Image, Text where as a StoredProcedure can return any datatype. In general User Defined Functions are used for computations where as Stored Procedures are used for performing business logic. UDF should return a value where as Stored Procedure need not. User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters where as a Stored Procedure can have upto 21000 input parameters. You cannot use non-deterministic built-in functions in UDF’s. For example functions like GETDATE() etc can not be used in UDFs, but can be used in Stored Procedures. Temporary Tables can not be used in a UDF where as a StoredProcedure can use Temporary Tables. UDF can not Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL. User Defined Function does not support error handling where as Stored Procedure support error handling. RAISEERROR or @@ERROR are not allowed in UDFs.

What is Self Join?

Use the script below to create Employee Table and populate it with some sample data. We will be using Employee Table to understand Self Join.

CREATE TABLE EMPLOYEE( [EMPLOYEEID] INT PRIMARY KEY, [NAME] NVARCHAR(50), [MANAGERID] INT ) GO INSERT INTO EMPLOYEE VALUES(101,’Mary’,102) INSERT INTO EMPLOYEE VALUES(102,’Ravi’,NULL) INSERT INTO EMPLOYEE VALUES(103,’Raj’,102) INSERT INTO EMPLOYEE VALUES(104,’Pete’,103) INSERT INTO EMPLOYEE VALUES(105,’Prasad’,103) INSERT INTO EMPLOYEE VALUES(106,’Ben’,103) GO

We use Self Join, if we have a table that references itself. For example, In the Employee Table below MANAGERID column references EMPLOYEEID column. So the table is said to referencing itself. This is the right scenario where we can use Self Join. Now I want to write a query that will give me the list of all Employee Names and their respective Manager Names. In order to achieve this I can use Self Join. In the Table below,Raj is the manager for Pete,Prasad and Ben. Ravi is the manager for Raj and Mary. Ravi does not have a manager as he is the president of the Company.

The query below is an example of Self Join. Both E1 and E2 refer to the same Employee Table. In this query we are joining the Employee Table with itself.

SELECT E1.[NAME],E2.[NAME] AS [MANAGER NAME] FROM EMPLOYEE E1 INNER JOIN EMPLOYEE E2 ON E2.EMPLOYEEID =E1.MANAGERID

This is because Ravi does not have a Manager. MANAGERID column for Ravi is NULL. If we want to get all the rows then we can use LEFT OUTER JOIN as shown below.

SELECT E1.[NAME],E2.[NAME] AS [MANAGER NAME] FROM EMPLOYEE E1 LEFT OUTER JOIN EMPLOYEE E2 ON E2.EMPLOYEEID =E1.MANAGERID

If we execute the above query we get all the rows, including the row that has a null value in the MANAGERID column. The results are shown below. The MANAGERNAME for 2nd record is NULL as Ravi does not have a Manager.

Let us now slightly modify the above query using COALESCE as shown below.

SELECT E1.[NAME],COALESCE(E2.[NAME],’No Manager’) as [MANAGER NAME] FROM EMPLOYEE E1 LEFT JOIN EMPLOYEE E2 ON E2.EMPLOYEEID =E1.MANAGERID

 If we execute the above query the output will be as shown in the image below. This is how COALESCE can be used.

In which scenario we use Abstract Classes and Interfaces

Interface:

–> If your child classes should all implement a certain group of methods/functionalities but each of the child classes is free to provide its own implementation then use interfaces.

For e.g. if you are implementing a class hierarchy for vehicles implement an interface called Vehicle which has properties like Colour MaxSpeed etc. and methods like Drive(). All child classes like Car Scooter AirPlane SolarCar etc. should derive from this base interface but provide a seperate implementation of the methods and properties exposed by Vehicle.

–> If you want your child classes to implement multiple unrelated functionalities in short multiple inheritance use interfaces.

For e.g. if you are implementing a class called SpaceShip that has to have functionalities from a Vehicle as well as that from a UFO then make both Vehicle and UFO as interfaces and then create a class SpaceShip that implements both Vehicle and UFO .

Abstract Classes

–> When you have a requirement where your base class should provide default implementation of certain methods whereas other methods should be open to being overridden by child classes use abstract classes.

For e.g. again take the example of the Vehicle class above. If we want all classes deriving from Vehicle to implement the Drive() method in a fixed way whereas the other methods can be overridden by child classes. In such a scenario we implement the Vehicle class as an abstract class with an implementation of Drive while leave the other methods / properties as abstract so they could be overridden by child classes.

–> The purpose of an abstract class is to provide a common definition of a base class that multiple derived classes can share.

For example a class library may define an abstract class that is used as a parameter to many of its functions and require programmers using that library to provide their own implementation of the class by creating a derived class.

Use an abstract class

• When creating a class library which will be widely distributed or reused—especially to clients, use an abstract class in preference to an interface; because, it simplifies versioning. This is the practice used by the Microsoft team which developed the Base Class Library. ( COM was designed around interfaces.)

• Use an abstract class to define a common base class for a family of types.

• Use an abstract class to provide default behavior.

• Subclass only a base class in a hierarchy to which the class logically belongs.

 

Use an interface

• When creating a standalone project which can be changed at will, use an interface in preference to an abstract class; because, it offers more design flexibility.

• Use interfaces to introduce polymorphic behavior without subclassing and to model multiple inheritance—allowing a specific type to support numerous behaviors.

• Use an interface to design a polymorphic hierarchy for value types.

• Use an interface when an immutable contract is really intended.

• A well-designed interface defines a very specific range of functionality. Split up interfaces that contain unrelated functionality.

 

Difference between UNIQUE constraint and PRIMARY key

A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table.

When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

* The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.

Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.

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.