Hot answers tagged

38

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


32

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


20

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


20

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


17

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


15

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause. From Craig Freedman: Combining TOP with ORDER BY adds determinism to the set of rows returned. ...


14

You can't guarantee contiguous or consecutive identity values in SQL Server An insert of multiple rows won't guarantee this. See Do Inserted Records Always Receive Contiguous Identity Values. The current identity value stays incremented on rollback (either explicit ROLLBACK or implicit due to a CONSTRAINT error). Also: Loading a temp table doesn't ...


14

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


13

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


12

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


10

Just to augment the other answers: a table is, by definition, an unordered set of rows. If you don't specify an ORDER BY clause, SQL Server is free to return the rows in whatever order it deems most efficient. This will often just happen to coincide with the order of insert, since most tables have a clustered index on identity, datetime or other ...


10

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


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


9

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


9

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


8

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


8

I suggest the following code to prevent it from happening: (Instead of the code you posted in your question) DELETE FROM dbo.table GO IF EXISTS ( SELECT * FROM sys.identity_columns WHERE object_id = OBJECT_ID('dbo.table') AND last_value IS NOT NULL ) DBCC CHECKIDENT ('dbo.table', RESEED, 0); It should prevent the ...


8

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


8

Going backwards just feels wrong to me. With only two data centers you could also implement identity ranges. Unless you cycle through identity values at an alarming rate, there is no reason you can't have: -- Data center 1 CREATE TABLE dbo.Table ( ID INT IDENTITY(1,1) PRIMARY KEY -- , ... ); -- Data center 2 CREATE TABLE dbo.Table ( ID INT ...


7

It won't cause problems in that SQL Server lets you do it: create table decrement( id integer identity(0,-1), test int ) insert into decrement (test) select number from numbers select top 10 id, test from decrement order by id asc go id test ------------ -5103 5110 -5102 5109 -5101 5108 -5100 5107 -5099 5106 -5098 5105 -5097 5104 -5096 ...


7

After normalizing a database schema to limit redundancy, tables are divided in smaller tables with established relations (one to one, one to many, many to many). In the process single fields in the original table can appear in multiple normalized tables. For instance a database for a blog could look like this in its unnormalized form, assuming a unique ...


7

Tables: CREATE TABLE dbo.Claim ( DataRowID bigint IDENTITY NOT NULL, ClaimColumn integer NOT NULL, CONSTRAINT PK_Claim PRIMARY KEY CLUSTERED (DataRowID) ); GO CREATE TABLE dbo.ClaimExtended ( ClaimDataRowID bigint NOT NULL, ExtendedColumn integer NOT NULL, CONSTRAINT PK_ClaimExtended PRIMARY KEY CLUSTERED ...


7

If you call the procedure multiple times, those are different scopes, so SCOPE_IDENTITY() is expected to be null. And you need to be careful about concepts - if you call the procedure multiple times, how is the second invocation really going to be sure that "the last generated value" was from the previous invocation from that process, vs. some other ...


7

If you can access the DAC (Dedicated Administrator Console), you can inspect the value of the identity column, for INT columns, by looking at the idtval column in sys.syscolpars. Thanks to Martin Smith for directing me to that table via this very useful answer by Roi Gavish on a related question here. Take, for instance, the following temporary table: USE ...


7

This is a known behaviour difference in SQL Server 2012 because of a change in the way IDENTITY is implemented. See related Connect Item for some discussion and possible workarounds (if this is a real issue for you)


7

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


6

As Paul White answered absolutely correct there is a possibility for temporarily "skipped" identity rows. Here is just a small piece of code to reproduce this case for your own. Create a database and a testtable: create database IdentityTest go use IdentityTest go create table dbo.IdentityTest (ID int identity, c1 char(10)) create clustered index ...


6

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


6

This is a known behavior change that was reported on Connect and closed as "by design." You'll notice that the docs for 2008 & 2014 are slightly different, partially to reflect that the output of commands like DBCC CHECKIDENT() (2008 vs. 2014) better reflect reality. The workaround you're using now is as good as any I can think of, since there is no ...



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