Delete duplicate record in SQL SERVER

Question :- Some time the question arise that how to delete duplicate record from table ?

Answer : firstly we create the table 

CREATE TABLE tblemployee1(
    [empid] int identity(1,1) NOT NULL,
    [empname] [varchar](10) NULL,
    [mgrid] [varchar](10) NULL,
    [test] [varchar](100) NULL)


insert some data on this  like below

insert into tblemployee1 values('Cherry',1,'Useful')
insert into tblemployee1 values('Cherry',1,'Useful')
insert into tblemployee1 values('Cherry',1,'Useful')
insert into tblemployee1 values('Rohit',2,'Useless')
insert into tblemployee1 values('Rohit',3,'Useless')
insert into tblemployee1 values('Usertest',3,'test')
insert into tblemployee1 values('Usertest',3,'test')

Select * from tblemployee1

output is

empid    empname    mgrid         test
1            Cherry          1             Useful
2            Cherry          1             Useful
3            Cherry          1             Useful
4            Cherry          1             Useful
5            Cherry          1             Useful
6            Rohit            2             Useless
7            Rohit            3             Useless
8            Usertest       3              test
9            Usertest       3              test

now we waana to delete the duplicate record which is with name  

the query is  
  
;with cte as(
Select ROw_number() over(partition by empname   order by empname) as col ,* from tblemployee1)
delete from  cte where col>1


then the ouput is

empid    empname    mgrid    test
1              Cherry          1          Useful
6              Rohit            2          Useless
8              Usertest       3          test 

here we use the rownumber for deletion with COMMON TABULAR EXPRESSION.

Hope this will help you 

if any issue then please let me know

Thanks 
ROHIT KUMAR SRIVASTAVA

 

Change Color of Status Bar in SQL SERVER

Some time when we work on multiple SQL SERVER then we are confused in which database we have to upload the data or execute the query so we are see the server name or scroll up down to check is it right server or not.
the best solution is if in this there are color combinatin then we are easily identfy this .
here is the solution in SQL SERVER 2008
Click on options>> then new window open

after that click on select button then new popup open

after that

after that select any color than OK .After that open the new window your task pane color is change

You set the different color for different SQLSERVER.

ROHIT KUMAR SRIVASTAVA

How can I quickly identify most recently modified stored procedures or table in SQL Server

1. Select * From sys.objects where type='u' and modify_date between GETDATE()-1 and GETDATE()

Note :  use p for store procedure,tr for trigger and fn for function

OR
2. Select * From sys.objects where type='u' order by modify_date desc

Note :  use p for store procedure,tr for trigger and fn for function

you change the date accodingly in first Query
use type='p' for storeprocedure in this Query



ROHIT SRIVASTAVA

Get the Data In List and this list to string with LINQ in ASP.Net

How to get the Data In List and retrive data from this list to string with LINQ

Firslty create a table from which we have to retrive data .
Create table category_name(
id tinyint IDENTITY(1,1),
category_name varchar(200),
cat_id tinyint   
)

and insert some value in this table .In this table we enter some category Id like 1,2,3,4,5

insert into category_name
values('Football',1),
 ('Cricket',1),
 ('Rugby',1),
 ('Hockey',1),
 ('Tennis',1),
 ('Polo',1),
 ('Horse Racing',1),
 ('Musical',2),
 ('Ballet',2),
 ('Classical music',2),
 ('Opera',2),
 ('Traditional art',4),
 ('Modern art',4),
 ('Historical',4),
 ('Military',4),
 ('Religious',4),
('wine',5),
('whiskey',5),
('liquor',5),
('Others',5),
('Kosher',3),
('Halal',3),
('No red meat',3),
('No fish',3),
('No shell fish',3),
('Dairy free',3)

After createion and insertion in table Now we come to ASp.net code .
Make a class (name DTCategory ) in App_code folder for Set the property like below
*You are free to add any name according you. I USe the DTCategory and call this everywhere where its requirement*

public class DTCategory
{
    public string CategoryName
    { get; set; }
    public Int16 ID
    { get; set; }
}

we make above because we have to pass this in our list for getting more than one value .
After this add another file in your App_code folder and put method for get all category name as well as ID from database .Lets this file name is
DALGetName.
Do'nt forget to add common namespace like System.Collection

put this code into DALGetName
public List<DTCategory> GetAllCategoryName()
    {
        List<DTCategory> lst = new List<DTCategory>();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "proc_get_all_category_name";
        try
        {
            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTCategory dto = new DTCategory();
                dto.CategoryName= Convert.ToString(dr["catergory_name"]);
                dto.ID = Convert.ToInt16(dr["id"]);
                lst.Add(dto);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
        }
        return lst;
    }

=============================
Now your Appcode work is Finish . now lets come in own aspx page where we want to retrive this with LINQ.
In above i get all the categoryname as well as Id in List . But our requirment is get the data in ID wise and bind it into DropDown List are any string . So we use the LINQ to get data
with our requirement .

Firstly we have to declare this is in above the page load. Dont forgot to add namespace System.Text


 public string _allsports = "";
    public string _allarts = "";
    public string _allcultural = "";
    public string _allgastronomy = "";
    public string _alldietry = "";


 protected void GetAllCategoryName()
    {
        #region /*This region is used for get the data dynamically */

        DALGetName objdalcat = new DALGetName(); // Call this method into your .cs page
        List<DTCategory> lstCategory = new List<DTCategory>();
        lstCategory = objdalcat.GetAllCategoryName();
        //Bind Sport
        var QuerySports = from x in lstCategory
                          where x.Id== 1
                          select x;
        StringBuilder sbSports = new StringBuilder();
        foreach (DTOContactCategoryName i in QuerySports)
        {
            sbSports.Append("<option value=" + i.ID + ">" + i.CategoryName + "</option>");
        }
        _allsports = sbSports.ToString();

        //Bind Arts
        var QueryArts = from x in lstCategory
                        where x.Id== 2
                        select x;
        StringBuilder sbArt = new StringBuilder();
        foreach (DTOContactCategoryName i in QueryArts)
        {
            sbArt.Append("<option value=" + i.ID + " >" + i.CategoryName + "</option>");
        }
        _allarts = sbArt.ToString();

        //Bind Cultural
        var QueryCultural = from x in lstCategory
                            where x.Id== 4
                            select x;
        StringBuilder sbCultural = new StringBuilder();
        foreach (DTOContactCategoryName i in QueryCultural)
        {
            sbCultural.Append("<option value=" + i.ID + " >" + i.CategoryName + "</option>");
        }
        _allcultural = sbCultural.ToString();

        //Bind Gastronomy
        var QueryGastronomy = from x in lstCategory
                              where x.Id== 5
                              select x;
        StringBuilder sbGastronomy = new StringBuilder();
        foreach (DTOContactCategoryName i in QueryGastronomy)
        {
            sbGastronomy.Append("<option value=" + i.ID + ">" + i.CategoryName + "</option>");
        }
        _allgastronomy = sbGastronomy.ToString();

        //Bind Other one Category which ID is 3
        var QueryDietary = from x in lstCategory
                           where x.Id== 3
                           select x;
        StringBuilder sbDietary = new StringBuilder();
        foreach (DTOContactCategoryName i in QueryDietary)
        {
            sbDietary.Append("<option value=" + i.ID + ">" + i.CategoryName + "</option>");
        }
        _alldietry = sbDietary.ToString();
        #endregion
    }

call this method on pageload method

====================in .aspx page we have some dropdown list and we bind the data which is Bind into this .cs Page

<select   class="ddlOptions">
                                        <%=_allsports %>
</select>

<select   class="ddlOptions">
                                        <%=_allcultural %>
</select>

<select   class="ddlOptions">
                                        <%=_allarts %>
</select>
<select   class="ddlOptions">
                                        <%=_allgastronomy %>
</select>
<select   class="ddlOptions">
                                        <%=_alldietry  %>
</select>




Hope  this will help you . If any issue then let me know

Thanks 

ROHIT SRIVASTAVA