Cursors in SQL Server are best used only for tables with primary keys or unique keys

cursors cursor, I think most people have used it in SQL Server. Cursors can still be used when the amount of data in a table is not too large, since cursors are the easiest way to iterate through each row of data in a table. But what happens if you use a cursor to loop a table that doesn't have a primary key or a unique key?

Let's take a look at this example, we declare a temporary table #foo and insert a row of data, the table has no primary key, and then we use a cursor named ID to update the table [Name] column data, execute the following statement to see what happens?

CREATE TABLE#Foo ([ID] [smallint] IDENTITY(1,1),    [Code] [Char](3)NULL,    [Name] [varchar]( -)NULL,    [Provinceid] [tinyint] NULL)GoInsert#Foo (Code, Name, Provinceid)Select 'A','New York',3;DeclareIdCursor   for  Select Top 5Id from#FooOrder  byIDOpenID;Fetch  fromID;Update#FooSetName+='1' Where  Current  ofIDCloseID;deallocateIDSelect *  from#FooGoDrop Table#Foo

The results of the implementation are as follows:

You will find SQL Server error, prompting you to declare the cursor ID is a READ_ONLY read-only cursor. The READ_ONLY cursor means that the declared cursor can only read the data, the cursor cannot do any update operations, and our statement above uses the cursor ID to update the table #foo data, so the error has been made.

Now let's change the above statement to the following, mainly in the declaration table #foo when the column [ID] declared in order to primary key, and then execute the following statement:

CREATE TABLE#Foo ([ID] [smallint] IDENTITY(1,1),    [Code] [Char](3)NULL,    [Name] [varchar]( -)NULL,    [Provinceid] [tinyint] NULL CONSTRAINT [Pk_foo] PRIMARY KEY CLUSTERED (    [ID] ASC))GoInsert#Foo (Code, Name, Provinceid)Select 'A','New York',3;DeclareIdCursor   for  Select Top 5Id from#FooOrder  byIDOpenID;Fetch  fromID;Update#FooSetName+='1' Where  Current  ofIDCloseID;deallocateIDSelect *  from#FooGoDrop Table#Foo

The results of the implementation are as follows:

This time the cursor ID has no error, and the statement executes successfully.

Why does a cursor become read_only when used with a table without a primary key or unique key?

If your table does not has a unique index (or a PRIMARY KEY constraint or UNIQUE key constraint, both of which create a U  Nique index behind the scenes), then your dynamic cursor is converted to a static cursor. And all static cursors must is read only.

If one of the tables referenced by the cursor has no unique index, the cursor would be converted to STATIC. And STATIC cursors is read-only. See Using implicit Cursor Conversions for more information.

Therefore, when using cursors, it is best to add a primary key or unique key to the table, so that when updating data using a cursor, the error will not be made.

Cursors in SQL Server are best used only for tables with primary keys or unique keys