How can delete duplicate rows from table except one row?

This problem can be solved by many ways. But I have two simple method :-
1) using  simple sub query Method
2) using Rowcount

1) delete
   top (select count(*)-1 from TableName where Condition )
   from
 TableName where Condition .
Note :- Both condition should be same for apropriate result.

2) RowCount is very usefull keyword in sqlserver. By this way we can fix , how many row will be affected .
suppose in a table we have n number of rows , we want to display only 1 row , just write down
     set rowcount 1
after that any DML or DDL command effects only one row of  table.
and ,
    set rowcount 0 , for all rows from table
For the above problem , at first get number of similar rows from table using " select count(*) from TABLENAME  where Condtion " (suppose get n rows , where n is integer)
Then
set rowcount  n-1
delete  from  TABLENAME  where  Condition
set rowcount 0

What is a GUID and How to create a GUID in C# ?

GUID --->   Globally unique identifier   -  128-bit integer
GUID  can be used to uniquely identify something
This method can be in system name space . GUID method System.Guid.NewGuid() initializes a new instance of the GUID class.
Syntax -   System.Guid.NewGuid();
Each time get a new number.

Difference between cast() and convert() in sql server 2005.

Both function are used in Sql server for explicit conversion of data from one data type to another data type. But using convert() function , data will convert in formated manner , not in cast() function.
For Example:-
cast()
cast(data as datatype)
select cast(getdate() as varchar(100))
output -- Nov 29 2010  5:39PM

convert()
convert(datatype,data,formate)
select  convert(varchar,getdate(),105)
output -- 29-11-2010

what is optimistic and premistic locking in Database

Optimistic locking assumes that no one would read or change the data while changes are being by a bean
Pessimistic locking would rather lock down the data so that no one can access it

or


In optimistic locking the row will not be locked until the actual update is performed. In order to prevent the lost update issue a version number column will be used. In pessimistic locking the row will be locked at the time when the data is selected from the database. This will prevent other users who want to update the same record. This should not prevent the readers but this behaviour varies from database to database.

What are Magic tables in SQL SERVER

There are two magic tables named inserted and deleted.

On Insertion :
whenever an insert takes place, the data goes into inserted
table before the commit of the transaction.

On Deletion :
Whenever a row gets deleted from a table, data goes into
deleted table.

On Update :
Since an update statement is a combination of delete and
insert so in case of update data goes in both, inserted and
deleted tables.

some usefull code of C#

click on this link for some usefull C# code

http://www.fincher.org/tips/Languages/csharp.shtml

What does NULL mean?

The value NULL is a very tricky subject in the database world, so don't be surprised if several applicants trip up on this question.
The value NULL means UNKNOWN; it does not mean '' (empty string). Assuming ANSI_NULLS are on in your SQL Server database, which they are by default, any comparison to the value NULL will yield the value NULL. You cannot compare any value with an UNKNOWN value and logically expect to get an answer. You must use the IS NULL operator instead.

Simplified and Extended Data Binding Syntax in ASP.NET 2.0

A very usefull link for data binding  in asp.net 2.0
http://www.15seconds.com/issue/040630.htm

How to Add ---Select--- in your Dropdown at run time when ur Dropdown bind with datasource.

How to Add ---Select--- in your Dropdown at run time when ur Dropdown bind with datasource.

a very simple step u can do it

Just write like this

Dropdown1.datasource=datatable;
Dropdown1.dataBind();
Dropdown1.Items.Insert(0,new ListItem("--Select--","-1"));

Last generated identity value on table in SQL SERVER 2005

SQL SERVER provides three different functions for capturing the last generated identity value on a table that contains an identity column:-


(a) select  @@IDENTITY

(b) select  SCOPE_IDENTITY()

(C) select  IDENT_CURRENT('tableName')


IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

• IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
  
• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
  
• SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

How can optimize a Stored Procedure?

when used in stored procedure , the RETURN statement can specify an integer value to procedure. If no value is specified on RETURN , a stored procedure returns 0 , The stored procedure returns a value 0 when no errors were encountered . Any non-zero value indicates as error occured.

Temporary Table in Sql Server 2005

Temporary tables are created in tempdb . And this table is created with prefixed with a pound sign(#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When close the session the table will be  automatically dropped. This can be created as just like other table with a few exceptions.it can't have forgin key constraints on Temporary Table.
Example:-
Create table #tablename(column name datatype,......)
insert into #tablename(column name..)  values(value.....)

it has almost comman behaviour like normal table.

Delay Function in SQL SERVER

In sql server 2005 , we can use delay function for waiting , For Example

----Delay for  seconds


WAITFOR DELAY '000:00:10'

SELECT '10 Second Delay'

GO

----Delay till 1 AM

WAITFOR TIME '1:00:00'

SELECT  'Time is 1 AM'

GO