63

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 #...


31

You can reset the identity value by DBCC CHECKIDENT('tableName', RESEED, 0) So next time you insert into TableName, the identity value inserted will be 1. When you delete rows from the table, it will not reset the Identity value, but it will keep increasing it. Just like what happened in your case. Now when you truncate the table, it will reset the ...


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. ...


28

From the documentation: DEFAULT VALUES Forces the new row to contain the default values defined for each column. So: INSERT dbo.TABLE DEFAULT VALUES; In addition: always use the schema prefix always terminate statements with semi-colons


27

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 ...


26

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 ...


25

Identity columns and Primary Keys are two very distinct things. An Identity column provides an auto-incrementing number. That's all it does. The Primary Key (at least in SQL Server) is a unique constraint that guarantees uniqueness and is usually (but not always) the clustered key. Again in MS SQL Server it is also an index (in some RDBMS they are not as ...


25

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

Kin has shown you how you can reset the IDENTITY value, but outside of a development environment when you're really removing all of the data, why do you need to do this? I hope you are not intending to maintain a contiguous sequence of IDENTITY values when you are in production. And I hope you aren't really writing your code to hard-code the IDENTITY ...


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 ...


17

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 ...


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

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 ...


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

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

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 ...


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

bigserial is not a type. It's 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('...


11

The cost of using a simple synthetic integer PK is small, and the benefit in your case would probably be quite considerable. As you point out, you'll have a much simpler FK relationship. A small PK makes for small (and fast) indices. Your total table space will probably be made less by adding such a column. If business rules ever change, you won't have to ...


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

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

Doing dbcc checkident('dbo.table',reseed,0) will cause the next entry in a newly created / truncated table to have 0 as the identity. CREATE TABLE TestIdent ( ID INT NOT NULL CONSTRAINT PK_TestIdent PRIMARY KEY CLUSTERED IDENTITY(1,1) , SomeText nvarchar(255) ); dbcc checkident('dbo.TestIdent',reseed,...


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