Reset Database Table Identity Value

One of things that always motivate me to blog is future reference. Anything I place here makes a good reference later. And of course I can always tell people "Hey you can find it here"

So here is today's SQL tip:

you can use the DDBC command to reseed the identity column value. This is usually helpful after a series of DELETE operations. By default MS SQL Server doesn't reseed (give the default seed value + 1) the identity column, so say you had a Users table with 1000 records with an identity column UserID. Then you decided to kick all the users off ( DELETE FROM Users). Then you decided to add them back INSERT INTO Users(.. ) VALUES ( ... ), the first inserted row will have a UserID value of 1001 ( not 1).

DDBC command sytnax:

DBCC CHECKIDENT ( table_name, [reseed / noreseed], [new_seed_value])

So to use it on our Users table, we can do the following:

DBCC CHECKIDENT ( Users, reseed, 0)

I will be soon blogging on the use of FOR XML EXPLICIT. This is not a SQL blog, It is just SQL that I keep forgetting.

Method Overloading by Return Type

Days ago, I had that interesting conversation with some of my geek friends of overloading methods by return type. I said that is not possible in Java or C# to overload a method by its return type. In other words I said you can not have:

class Demo
{
    public static int f()
    {
        return 1;
    }

    public static float f()
    {
        return 1.0F;
    }
}

I have backed up my statement with the fact that compiler would have a lot of problems executing the right method, since the methods will have identical signatures but different return types so there is virtually no way of picking up the right method. So one of my friends suggested the following case:

class Demo
{
    public static int f(int a)
    {
        return a;
    }

    public static float f()
    {
        return 1.0F;
    }
}

With my previous assumption I said that the code won't compile too, which was proven wrong. Actually the compiler will allow that because it can simply differentiate between the two methods, since each of them has a different signature (different parameter list).

So we all learned a valuable lesson in method overloading 

Update

The .NET Framework CIL (Common Intermediate Language) actually supports method overloading by return type. Of course the current languages (C#, VB.NET, J# ... etc) running on top of the .NET Framework don't support that feature except when overloading class conversion operators.