SQL Server returns the ID of the inserted data and the number of rows affected

Tag: Row BER statement its where means insert first batch

First look at the data inside the database (s_id is the self-growing identity column):

There are three ways to return the ID (identity value) of the last inserted data in SQL Server:

The first of the @ @IDENTITY:

1 Insert  intoStudent (s_stuno,s_name,s_sex,s_height)2 Values('013','Weizhuang','male','185');3 4 Select @ @IDENTITY        --back to

@ @IDENTITY can return the last generated identity value (including any called stored procedures and triggers) for the insertion of the current connection in all ranges . This function is not only applicable to tables. The value returned by the function is the identity value generated by the last table insert row. For example, there is now a table and a B table, a table insert a data and trigger trigger (trigger) to insert a piece of data for table B, when @ @IDENTITY return is actually the value of the identity column of B table, because @ @IDENTITY it always gets the value of the last change data of the self-increment field. @ @IDENTITY Returns the identity value that was last generated for any table in all scopes of the current session.

The second type of ident_current:

1 Insert  intoStudent (s_stuno,s_name,s_sex,s_height)2 Values('014','Big-Life','female','183');3 4 SelectIdent_current ('Student')--back to

Ident_current () is a function that has a parameter whose parameter is the name of the table that specifies the value of the identity column to return.

Ident_current () returns the last identity value generated for a particular table in any session and any scope, and is not restricted by scope and session, but by the specified table.

The third type of scope_identity:

1 Insert  intoStudent (s_stuno,s_name,s_sex,s_height)2 Values('015','month son','female','165');3 4 Select scope_identity()--return

Scope_identity () returns the identity value that was last generated for the current session and any tables in the current scope. A scope is a module-a stored procedure, trigger, function, or batch.

IDENT_INCR usage:

1 -- The first parameter of the IDENTITY is the starting value of the self-growth, the second parameter represents the growth from the growth, that is, how much it grows at a time 2 SELECT IDENT_INCR ('Student')    --

SELECT ident_incr (' TableName '), a parameter, ' TableName ' represents the table name or view name of the specified table.

Returns the increment value of the identity field for the specified table (that is, the value added for each insertion of a new numeric sequence).

Ident_seed usage:

1 -- The first parameter of the IDENTITY is the starting value of the self-growth, the second parameter represents the growth from the growth, that is, how much it grows at a time 2 Select Ident_seed ('Student')    -- returns 1

Select Ident_seed (' TableName '), a parameter, ' TableName ' represents the table name or view name of the specified table.

Returns the seed value of the identity field for the specified table (that is, the starting value, specifying the value from which the identity column starts).

SQL gets the next value that identifies the increment:

1 -- gets the next value incremented by the current identity, which is the next value of the largest value of the current identity 2 Select Ident_current ('Student'+ident_incr('Student ')

SQL returns the number of rows affected by the previous statement:

@ @ROWCOUNT Returns the number of rows affected (insert, select, Delete, update):

1 Select *  from where S_sex=' female '23Select@ @ROWCOUNT    --  return 6

If the number of rows is greater than 2 billion, use Rowcount_big ().

1 Select *  from where S_sex=' male '; 2 3 Select Rowcount_big ()    -- return 9

ROWCOUNT usage:

The function of rowcount is to restrict the subsequent SQL to stop processing after returning the specified number of rows, such as the following example:

1 --Check out the top 5 data2 Set RowCount 53 Select *  fromStudentwhereS_sex='male';4 5 --Check out the following 5 data6 Set RowCount 57 Select *  fromStudentwhereS_sex='male' Order  bys_iddesc;

That is, the query above rowcount has the same effect as top [number].

To cancel the SET ROWCOUNT qualification, SET ROWCOUNT 0 is all you need.

Note:the qualification of SET ROWCOUNT is as valid as the modification and deletion. such as the following example:

1 Set RowCount 3 2 Update Set S_birthdate='2017-01-01'3where s_id<  - 4 5 Select @ @ROWCOUNT    --

1 Set RowCount 52 Delete  fromStudentwhereS_birthdate is NULL3 4 Select @ @ROWCOUNT    --returns 55 6 Set RowCount 07 8 Select *  fromStudent

SQL Server returns the ID of the inserted data and the number of rows affected