56

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


46

The table name prefix has very good reasons. Consider: TableA (id int identity, stringdata varchar(max)) TableB (id int identity, stringdata varchar(max)) We want to DELETE from TableA records that exist in both tables. Easy enough, we will just do an INNER JOIN: DELETE a FROM TableA A INNER JOIN TableB B ON b.id = B.id ....and we just wiped ...


36

Mostly it's to keep foreign keys from becoming a tremendous pain. Let's say you have two tables: Customer and CustomerAddress. The primary key for both is a column named id, which is an identity (int) column. Now you need to have the customer ID referenced from CustomerAddress. You can't name the column id, obviously, so you go with customer_id. This leads ...


29

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


28

Here's a summary of all the answers about the advantages obtained from the convention to not use a common name for all primary keys: Less mistakes, since the identity fields are not named the same You cannot mistakenly write a query that joins on B.Id = B.Id instead of A.Id = B.Id, because the identity fields will never be named the exact same. Clearer ...


26

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


23

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


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


22

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


22

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


20

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


19

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


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


17

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


16

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


15

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


14

While it doesn't automatically prevent duplicates, you can disable the identity temporarily using the following, and then you would likely just want to set the identity seed to the highest value in the table: SET IDENTITY_INSERT dbo.tablename ON; INSERT ... SET IDENTITY_INSERT dbo.tablename OFF; DECLARE @sql NVARCHAR(MAX); SELECT @sql = N'DBCC ...


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

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


13

There is no single magic algorithm for patient matching, and I doubt there ever will be. For starters, there are regional variances. As MMattoli pointed out, what works well in an urban United States hospital probably won't fit well in a rural Australian clinic treating Aborigines. Also, individual sites have differing views on fault tolerance. If you ...


13

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


13

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


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

To copy my answer from the linked question: There is a situation where sticking "ID" on every table isn't the best idea: the USING keyword, if it's supported. We use it often in MySQL. For example, if you have fooTable with column fooTableId and barTable with foreign key fooTableId, then your queries can be constructed as such: SELECT fooTableId, ...


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


11

In your instead of trigger, you definitely can get the inserted value... but not until after you've performed the insert. USE tempdb; GO CREATE TABLE dbo.SmellThis ( id INT IDENTITY(1,1), name VARCHAR(32) ); GO CREATE TRIGGER dbo.SmellThis_First ON dbo.SmellThis INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; DECLARE @ids TABLE(id INT); IF ...


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


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