    DBCC CHECKIDENT(&#39;TableName&#39;, RESEED, 0)

Just a word of warning with:
&lt;pre&gt;&lt;code&gt;DBCC CHECKIDENT (MyTable, RESEED, 0)&lt;/code&gt;&lt;/pre&gt;

If you did not truncate the table, and the identity column is the PK, you will get an error when reaching pre-existing identites.

For example, you have identities (3,4,5) in the table already.  You then reset the identity column to 1.  After the identity 2 is inserted, the next insert will try to use the identity 3, which will fail.

To set the identity to 100:

    DBCC CHECKIDENT (MyTable, RESEED, 100)

If I open a solution in Visual Studio 2008 and run a unit test then VS creates a new .vsmdi file in the Solution Items folder and gives it the next number available e.g. My Solution2.vsmdi.

Any idea why VS is doing this and how I can get it to stop doing this?

Why does Visual Studio create a new .vsmdi file?

What is the Java equivalent of PHP&#39;s `$_POST`? After searching the web for an hour, I&#39;m still nowhere closer.

Accessing post variables using Java Servlets

I would like to have a nice template for doing this in development. How do I reset an increment identity&#39;s starting value in SQL Server?

How do I reset an increment identity&#39;s starting value in SQL Server

<p>I would like to have a nice template for doing this in development. How do I reset an increment identity's starting value in SQL Server?</p>


Since both a `Table Scan` and a `Clustered Index Scan` essentially scan all records in the table, why is a Clustered Index Scan supposedly better?

As an example - what&#39;s the performance difference between the following when there are many records?:

    declare @temp table(
        SomeColumn varchar(50)
    )

    insert into @temp
    select &#39;SomeVal&#39;
    
    select * from @temp

    -----------------------------

    declare @temp table(
        RowID int not null identity(1,1) primary key,
        SomeColumn varchar(50)
    )

    insert into @temp
    select &#39;SomeVal&#39;
    
    select * from @temp


What&#39;s the difference between a Table Scan and a Clustered Index Scan?

When you have a query or stored procedure that needs performance tuning, what are some of the first things you try?

Favourite performance tuning tricks

What is the best way to remove duplicate rows from a fairly large **`SQL Server`** table (i.e. 300,000+ rows)?

The rows, of course, will not be perfect duplicates because of the existence of the `RowID` identity field.


**MyTable**

    RowID int not null identity(1,1) primary key,
    Col1 varchar(20) not null,
    Col2 varchar(2048) not null,
    Col3 tinyint not null

How can I remove duplicate rows?

We&#39;re seeing some pernicious, but rare, deadlock conditions in the Stack Overflow SQL Server 2005 database.

I attached the profiler, set up a trace profile using [this excellent article on troubleshooting deadlocks](http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/), and captured a bunch of examples. The weird thing is that **the deadlocking write is *always* the same**:

    UPDATE [dbo].[Posts]
    SET [AnswerCount] = @p1, [LastActivityDate] = @p2, [LastActivityUserId] = @p3
    WHERE [Id] = @p0

The other deadlocking statement varies, but it&#39;s usually some kind of trivial, simple **read** of the posts table. This one always gets killed in the deadlock. Here&#39;s an example

    SELECT
    [t0].[Id], [t0].[PostTypeId], [t0].[Score], [t0].[Views], [t0].[AnswerCount], 
    [t0].[AcceptedAnswerId], [t0].[IsLocked], [t0].[IsLockedEdit], [t0].[ParentId], 
    [t0].[CurrentRevisionId], [t0].[FirstRevisionId], [t0].[LockedReason],
    [t0].[LastActivityDate], [t0].[LastActivityUserId]
    FROM [dbo].[Posts] AS [t0]
    WHERE [t0].[ParentId] = @p0

To be perfectly clear, we are not seeing write / write deadlocks, but read / write.

We have a mixture of LINQ and parameterized SQL queries at the moment. We have added `with (nolock)` to all the SQL queries. This may have helped some. We also had a single (very) poorly-written badge query that I fixed yesterday, which was taking upwards of 20 seconds to run every time, and was running every minute on top of that. I was hoping this was the source of some of the locking problems!

Unfortunately, I got another deadlock error about 2 hours ago. Same exact symptoms, same exact culprit write.

The truly strange thing is that the locking write SQL statement you see above is part of a very specific code path. It&#39;s *only* executed when a new answer is added to a question -- it updates the parent question with the new answer count and last date/user. This is, obviously, not that common relative to the massive number of reads we are doing! As far as I can tell, we&#39;re not doing huge numbers of writes anywhere in the app.

I realize that NOLOCK is sort of a giant hammer, but most of the queries we run here don&#39;t need to be that accurate. Will you care if your user profile is a few seconds out of date?

Using NOLOCK with Linq is a bit more difficult as [Scott Hanselman discusses here](http://www.hanselman.com/blog/GettingLINQToSQLAndLINQToEntitiesToUseNOLOCK.aspx).

We are flirting with the idea of using

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

on the base database context so that all our LINQ queries have this set. Without that, we&#39;d have to wrap every LINQ call we make (well, the simple reading ones, which is the vast majority of them) in a 3-4 line transaction code block, which is ugly.

I guess I&#39;m a little frustrated that trivial reads in SQL 2005 can deadlock on writes. I could see write/write deadlocks being a huge issue, but *reads?* We&#39;re not running a banking site here, we don&#39;t need perfect accuracy every time.

Ideas? Thoughts?

---

&gt; Are you instantiating a new LINQ to SQL DataContext object for every operation or are you perhaps sharing the same static context for all your calls?

Jeremy, we are sharing one static datacontext in the base Controller for the most part:

    private DBContext _db;
    /// &lt;summary&gt;
    /// Gets the DataContext to be used by a Request&#39;s controllers.
    /// &lt;/summary&gt;
    public DBContext DB
    {
        get
        {
            if (_db == null)
            {
                _db = new DBContext() { SessionName = GetType().Name };
                //_db.ExecuteCommand(&quot;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED&quot;);
            }
            return _db;
        }
    }

Do you recommend we create a new context for every Controller, or per Page, or .. more often?

Diagnosing Deadlocks in SQL Server 2005

&quot;Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services.&quot;

I find using SQL Server Profiler extremely useful during development, testing and when I am debugging database application problems. Does anybody know if there is an equivalent program for MySql?

Is there a Profiler equivalent for MySql?

Why does the following behave unexpectedly in Python?

    &gt;&gt;&gt; a = 256
    &gt;&gt;&gt; b = 256
    &gt;&gt;&gt; a is b
    True           # This is an expected result
    &gt;&gt;&gt; a = 257
    &gt;&gt;&gt; b = 257
    &gt;&gt;&gt; a is b
    False          # What happened here? Why is this False?
    &gt;&gt;&gt; 257 is 257
    True           # Yet the literal numbers compare properly

I am using Python 2.5.2. Trying some different versions of Python, it appears that Python 2.3.3 shows the above behaviour between 99 and 100.

Based on the above, I can hypothesize that Python is internally implemented such that &quot;small&quot; integers are stored in a different way than larger integers and the `is` operator can tell the difference. Why the leaky abstraction? What is a better way of comparing two arbitrary objects to see whether they are the same when I don&#39;t know in advance whether they are numbers or not?


&quot;is&quot; operator behaves unexpectedly with integers

I have seen various methods used when retrieving the value of a primary key identity field after insert.

    declare @t table (
    	id int identity primary key,
    	somecol datetime default getdate()
    )
    insert into @t
    default values
    
    select SCOPE_IDENTITY() --returns 1
    select @@IDENTITY --returns 1
    
Returning a table of identities following insert:

    Create Table #Testing (  
    	id int identity,  
    	somedate datetime default getdate()  
    )  
    insert into #Testing  
    output inserted.*  
    default values   

What method is proper or better? Is the OUTPUT method scope-safe?

The second code snippet was borrowed from [SQL in the Wild][1]


  [1]: http://sqlinthewild.co.za/index.php/2008/12/31/on-the-output-of-a-data-modification/#more-124

@@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity

I have a MS SQL 2005 database with a table `Test` with column `ID`. `ID` is an identity column. 

I have rows in this table and all of them have their corresponding ID auto incremented value.

Now I would like to change every ID in this table like this:

   ` ID = ID + 1`

But when I do this I get an error:

 &gt;   Cannot update identity column &#39;ID&#39;.

I&#39;ve tried this:
```
    ALTER TABLE Test NOCHECK CONSTRAINT ALL 
    set identity_insert ID ON
```
But this does not solve the problem.

I need to have identity set to this column, but I need to change values as well from time to time. So my question is how to accomplish this task.

How to change identity column values programmatically?

(Came up with this question in the course of trying to answer [this other one][1])  

Consider the following MS-SQL table, called GroupTable:

&lt;pre&gt;
GroupID
-------
1  
2  
3  
&lt;/pre&gt;

where GroupID is the primary key and is an Identity column.

How do you insert a new row into the table (and hence generate a new ID) **without** using IDENTITY_INSERT ON?

Note that this:  

    INSERT INTO GroupTable() Values ()   
... won&#39;t work.

edit: we&#39;re talking SQL 2005 or SQL 2008 here.


  [1]: https://stackoverflow.com/questions/850017/how-to-manage-groups-in-the-database/850049#850049

How to insert into a table with just one IDENTITY column?

When a class in Java doesn&#39;t override **hashCode()**, 
printing an instance of this class gives a nice unique number.

The Javadoc of Object says about **hashCode()**:
&gt; *As much as is reasonably practical, the hashCode method defined by class Object does return distinct integers for distinct objects.*

But when the class overrides **hashCode()**, how can I get
its unique number?

Content Type	Original Author	Original Content on Stackoverflow
Question	Joel Meador	View Question on Stackoverflow
Solution 1 - Sql Server	Ryan Sampson	View Answer on Stackoverflow
Solution 2 - Sql Server	Forgotten Semicolon	View Answer on Stackoverflow
Solution 3 - Sql Server	Keith	View Answer on Stackoverflow

How do I reset an increment identity's starting value in SQL Server

Sql Server Problem Overview

Sql Server Solutions

Solution 1 - Sql Server

Solution 2 - Sql Server

Solution 3 - Sql Server

Accessing post variables using Java Servlets

Why does Visual Studio create a new .vsmdi file?

Attributions