65

Use SCOPE_IDENTITY() if you are inserting a single row and want to retrieve the ID that was generated. CREATE TABLE #a(identity_column INT IDENTITY(1,1), x CHAR(1)); INSERT #a(x) VALUES('a'); SELECT SCOPE_IDENTITY(); Result: ---- 1 Use the OUTPUT clause if you are inserting multiple rows and need to retrieve the set of IDs that were generated. INSERT #...


30

This is a known and expected issue - the way IDENTITY columns are managed by SQL Server has changed in SQL Server 2012 (some background); by default it will cache 1000 values and if you restart SQL Server, reboot the server, fail over, etc. it will have to throw out those 1000 values, because it won't have a reliable way to know how many of them were ...


30

When inserting a row, is there a window of opportunity between the generation of a new Identity value and the locking of the corresponding row key in the clustered index, where an external observer could see a newer Identity value inserted by a concurrent transaction? Yes. The allocation of identity values is independent of the containing user transaction. ...


27

Can I rely on the returned identity values from the dbo.Target table insert to be returned in the order they existed in the 1) VALUES clause and 2) #Target table, so that I can correlate them by their position in the output rowset back to the original input? No, you can't rely on anything to be guaranteed without an actual documented guarantee. The ...


26

This is to implement the feature found in the standard. (copied from a draft, date: 2011-12-21): 4.15.11 Identity columns The columns of a base table BT can optionally include not more than one identity column. The declared type of an identity column is either an exact numeric type with scale 0 (zero), INTEGER for example, or a distinct type ...


22

Your question is, essentially: Why can I no longer do this risky thing that I should never have been allowed to do in the first place? The answer to that question is largely irrelevant (though you can see some Microsoft comments in these Connect items asking for this functionality: #294193 and #252226). For completeness, my synopsis is: The ability to ...


19

Other than additional disk space (and in turn memory usage and I/O), there's not really any harm in adding an IDENTITY column even to tables that don't need one (an example of a table that doesn't need an IDENTITY column is a simple junction table, like mapping a user to his/her permissions). I rail against blindly adding them to every single table in a ...


19

That is, when table row is deleted, it's PK must be reused in subsequent inserts. What universe is your lecturer from?? That is grossly inefficient. If you try to do that, you will cut your performance prospects down by a factor of 10. If you need gapless numbers for auditing reasons, build them explicitly, not directly from database tools. And never ...


19

There is nothing at all wrong with starting at -2,147,483,648 as far as SQL Server is concerned. Starting at 2,147,483,647 and counting backwards with IDENTITY(2147483647,-1) is perfectly valid too. Things that would make me be wary of doing so: It might confuse people who don't expect to see negative values in such positions. It is unusual enough that it ...


18

By definition, a table is an unordered bag of rows (see #3 here). There is no way to ask SQL Server which row was inserted last unless you are doing so in the same batch as the insert. For example, if your table has an IDENTITY column, you can use SCOPE_IDENTITY() (never use @@IDENTITY, since that can be unreliable if you have or will ever add triggers to ...


17

The way I understand your question is that you have an existing table with a column that has up until now been populated with manual values, and now you want to (1) make this column an IDENTITY column, and (2) make sure that the IDENTITY starts from the most recent value in the existing rows. First off, some test data to play with: CREATE TABLE dbo....


16

I don't think there's any real "internals" reason. The metadata is stored at column level not table level. It would need a rethink though of scalar functions such as scope_identity() and pseudo column syntax such as $identity as there would now be ambiguities. Philosophically if the purpose of identity is to produce something that uniquely identifies an ...


16

bigserial is a pseudo-type, a notational convenience that is resolved to type bigint internally - plus a sequence, a column default, a dependency and an ownership. Basic commands to convert an existing bigint column with existing rows to a bigserial: CREATE SEQUENCE tbl_tbl_id_seq; ALTER TABLE tbl ALTER COLUMN tbl_id SET DEFAULT nextval('tbl_tbl_id_seq'); ...


15

You can use a join to create and populate the new table in one go: SELECT t.* INTO dbo.NewTable FROM dbo.TableWithIdentity AS t LEFT JOIN dbo.TableWithIdentity ON 1 = 0 ; Because of the 1 = 0 condition, the right side will have no matches and thus prevent duplication of the left side rows, and because this is an outer join, the left side rows will ...


15

Explicit identity insert require IDENTITY_INSERT property set to ON. SET IDENTITY_INSERT MyTable ON -- Statement Allows explicit values to be inserted into -- the identity column of a table. GO INSERT INTO MyTable(ID, Name, Description) VALUES (0, 'Special title', 'special item'); GO SET IDENTITY_INSERT MyTable OFF -- ...


14

SELECT INTO has two phases (not visible in execution plans). First, it creates a table that matches the metadata of the query used to create it. This happens in a system transaction, so the (empty) created table will continue to exist even if the SELECT INTO is wrapped in a user transaction that is rolled back. At the end of the first phase, we have an ...


13

I found that it is a new feature (don't know what is use of it) Before SQL Server 2012, identity allocations were always individually logged (as each value was used). This per-row logging activity could limit throughput in scenarios where many identity values are generated in a short space of time. To improve efficiency, SQL Server 2012 (and later) logs ...


13

From my experience, the main and overwhelming reason to use a separate ID for every table is the following: In almost every case my customer swore a blood oath in the conception phase that some external, "natural" field XYZBLARGH_ID will stay unique forever, and will never change for a given entity, and will never be re-used, there eventually appeared cases ...


13

You can use procedure (introduced in SQL Server 2012): sp_sequence_get_range To use it you need to create a SEQUENCE object and use it as a default value instead of IDENTITY column. There is an example: CREATE SCHEMA Test ; GO CREATE SEQUENCE Test.RangeSeq AS int START WITH 1 INCREMENT BY 1 CACHE 10 ; CREATE TABLE Test....


12

$IDENTITY (and $ROWGUID) are documented in SELECT Clause (Transact-SQL). I would caution against using it because it will throw an error when a table doesn't have an identity column. Take these two examples - the first one works (check your Results tab), but the second one throws an error: CREATE TABLE #EmployeesWithIdentity (EmployeeID INT IDENTITY(1,1), ...


12

Aaron Bertrand has a 4-part series on this topic, starting with: Minimizing impact of widening an IDENTITY column – part 1 If you absolutely need to move to bigint, must minimize downtime, and have plenty of time for planning, the approach he documents in part 4 is: At a very high level, the approach is to create a set of shadow tables, where all the ...


12

You need to declare the type of the column, too (INT or BIGINT or SMALLINT): ALTER TABLE sourceTable ADD COLUMN ogc_fid int -- int or bigint or smallint GENERATED BY DEFAULT AS IDENTITY; Also be sure to use the latest point release. As the IDENTITY has only been recently added, there were bugs affecting this particular command ...


11

There is no built-in command to remove the identity property from a column in SQL Server. You may have to create a new table, copy all the data across, and then rename the table. This can be time-consuming and awkward, especially if you have things like foreign key constraints, schema-bound functions, indexed views and so on. That said, there is a way to ...


11

You can use DBCC CHECKIDENT to reseed the IDENTITY column. Here is a sample you can run: SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.foo(ID INT IDENTITY(1,1)); GO INSERT dbo.foo DEFAULT VALUES; GO 100 -- note: set it to ([the next value you want] - 1) DBCC CHECKIDENT(N'dbo.foo', RESEED, 499); GO INSERT dbo.foo DEFAULT VALUES; GO 3 SELECT ID FROM ...


11

Since you can reset the IDENTITY by issuing a simple TRUNCATE: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'TRUNCATE TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';' + CHAR(13) + CHAR(10) FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] INNER JOIN sys.identity_columns AS ic ON t.[object_id] = ic.[...


11

As there is a primary key defined on identity column you wont be able to directly alter this column. Both the approaches that you have mentioned in your question can be used and downtime depends on how your server is performing and number of rows reside in that table. Drop the PK and alter the column; or First drop the PK /****** Object: DROP ...


11

Here's a way that scales easily to three related tables. Use MERGE to insert the data into the copy tables so that you can OUTPUT the old and new IDENTITY values into a control table and use them for related tables mapping. The actual answer is just two create table statements and three merges. The rest is sample data setup and tear down. USE tempdb; --##...


10

Apart from performance, they all have rather different meanings. SCOPE_IDENTITY() will give you the last identity value inserted into any table directly within the current scope (scope = batch, stored procedure, etc. but not within, say, a trigger that was fired by the current scope). IDENT_CURRENT() will give you the last identity value inserted into a ...


10

If this is the only transaction on that table, then yes. However, another transaction could also do an insert at the same time, and be somewhere in between 1-100, unless you use SERIALIZABLE isolation. From IDENTITY (Property) (Transact-SQL) in the product documentation: The identity property on a column does not guarantee the following: Uniqueness of the ...


9

Setting IDENTITY_INSERT ON on its own does not eliminate concurrency - this does not place any exclusive locks on the table, only a brief schema stability (Sch-S) lock. So what could theoretically happen, under the default behavior, is you could do this in session 1: BEGIN TRANSACTION; -- 1 SET IDENTITY_INSERT dbo.tablename ON; -- 2 INSERT dbo.tablename(...


Only top voted, non community-wiki answers of a minimum length are eligible