Hot answers tagged

37

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


30

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


19

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

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


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


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


12

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


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


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


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

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

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

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

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


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


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

Unless I'm missing something your problem is not moving the data it's dealing with the identity values that are already set up. If that is the case then try this. Pick a value greater than your current ident values on either DB. I would pick a round value, say 1,000,000. Pick the ident values you want to change (for example if you have lookup tables that ...


6

You can use the output clause to throw the inserted rows into a table variable. Then you'll be able to see all of the identity values. Link for the output clause: http://technet.microsoft.com/en-us/library/ms177564.aspx


6

As name is unique and will never change, it is certainly a good candidate key from a relational theory point of view. You might find using a integer surrogate key preferable for space and performance reasons though as it will take less space than the text in every table that has a foreign key to this table (and every index as FKs are usually indexed ...


6

You can do this. WITH T AS (SELECT ISNULL((SELECT MAX(ID) FROM StagingTable), 0) + ROW_NUMBER() OVER (ORDER BY NaturalID) AS New_ID, ID FROM StagingTable WHERE ID IS NULL) UPDATE T SET ID = New_ID So the windowed function is used in the SELECT list but you can still use the result of it to ...


6

Some problems that may rise with with this setting: Following the link in @Martin Smith's comment, negative values in an identity column may cause issues with some applications: Why database designers do not make IDENTITY columns start from the min value rather than 1? Another issue is not related to the values being negative but being decreasing, and if ...


6

Until SQL Server supports sequences (next version "Denali") then you'll have to have a common table. However, if I understand you, I think you're looking at the subtype/supertype pattern. A sequence would be nice but if you designed using, say, Object Role Modelling then you'd generate this pattern/schema Basically, you have a common "Contract" table: ...


6

One third-party vendor we hired to create something for us actually named all their identity columns Ident just to avoid using Id. Using "Ident" instead of "Id" doesn't really solve anything if "Ident" ends-up being used on all of their tables. There's a good article on SQL coding conventions on the Drupal site that indicates a good practice for this ...



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