Tag Info

Hot answers tagged

30

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


22

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


13

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


11

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


9

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


9

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


9

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


8

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


7

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


7

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

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

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)


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

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


6

It is also worth checking previous lastnames as these often change.


6

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


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


5

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


5

The only way to guarantee a gapless series (assuming no deletes) is to serialize - almost always a bad idea With a sequence or identity you cannot assume your series will be gapless as @a_horse mentions - but it looks like you are assuming that even with gaps, there is some relationship between sequence order and insertion order - this is not true either! A ...


5

If you want a standby copy of your data for HA/DR then is much better to use log shipping or database mirroring. Since these technologies create a physical database copy, you avoid exactly the problem(s) you mention. Also, schema changes 'replicate' much easier. If you insist on transactional replication, seed your replicas with a backup instead of a ...


5

IDENTITY sequences will never be contiguous failed (and rolled back) INSERTs will increment rows can be deleted DBCC CHECKIDENT could have been run See Do Inserted Records Always Receive Contiguous Identity Values on SO too Row deletion is quite important if you have history/audit tables: you'd never want to reuse an ID that exists in some history/audit ...


5

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


4

Is it possible you have two tables with the same name in different schemas? Some random doc I found online states that message 8107 appears when you already have IDENTITY_INSERT set to ON for another table in the db... Another debugging step to check: You state that the issue keep occurring even after you drop and recreate the table, but what if you drop ...


4

If you don't specify the column list then this implicitly assumes a column list including all user insert-able columns (non identity, rowversion, computed) as below. CREATE TABLE #T ( C INT IDENTITY, D INT, E INT ) INSERT INTO #T VALUES (2,3) This is far more generally useful as the most common reason for having an IDENTITY column is to allow SQL Server ...


4

I name my columns CustomerID instead of ID, so whenever I type FROM dbo.Customers AS c JOIN dbo.CustomerOrders AS o SQL Prompt immediately suggests the following ON c.CustomerID = o.CustomerID It saves me a few keystrokes. Yet I think naming conventions are very subjective and as such I don't have a strong opinion one way or another.


4

This is an ongoing debate that many face, and experts will give good arguments for either of these. What your question boils down to is a surrogate vs. natural key (and option two seems to be a half-surrogate half-natural key). As for your third option, oftentimes I see people do whatever they can to find a unique candidate for a primary key constraint. ...


4

I'm not familiar with Azure but I can speak to SQL Server in general. SQL has a property called Force Encryption (which is set through SQL's Configuration Manager), which as it sounds will force clients to connect over encrypted sessions. This is done with certificates just like SSL. This guarantees the authenticity of the server as well as providing for ...



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