Usage of Table with single column (Identity)?

Question

Hi,

Whats the purpose of having single column table (identity column)..? is there a good usecase available??

is it really a good practise.??

Cheers

Ramesh Vel

Answer 1

Could be for utility Numbers table in SQL Server < 2005. Check this blog post about it.

Anyway a table with identity column only is normally used to overcome some SQL Server obstacle that's either harder to do otherwise or to slow using other (ie. built-in) methods.

Answer 2

Hmm a table with a single column which is an identity and nothing more - there can not be many uses for that.

One I can think of : If you needed multiple tables to have identity values, but did not want these values to clash with each other, then you could use a centralised table which only had one column (identity) and this was used to produce the numbers.

Answer 3

When migrating from Oracle databases, you need a way to emulate Oracle sequences...Tables with single identity columns is the way to go.

Answer 4

Another use could also be of a boolean nature in that the presence of the key in the single-column pkey table indicates a true predicate evaluation for some business logic.

Meaning that the pkey value in this special table could be a foreign key to another table. Though i'm not sure if that buys anything over a bit flag in that other table.

But it's definitely good practice as a tally or numbers table.

Answer 5

I think people use this to replicate Oracle's SEQUENCE. Basically they want a single unique identifier for any entity they create in their system, so they have something like this:

CREATE PROCEDURE dbo.GenerateIdentifier
    @Identifier INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.SingleColumnTable DEFAULT VALUES;

    SET @Identifier = SCOPE_IDENTITY();
END
GO

Now whenever they want to add a new contact, or customer, or order, etc. they first call this procedure and get the new identifier. Then only one entity in the system will have an identifier = 1, or 2, etc.

Answer 6

That table might be serving as an enterprise-wide unique id generator where all the applications (including legacy) would rely as a system of record.

Answer 7

If you are normalizing past 3rd normal you may want to eliminate multiple many to many relationships to a single table. This is hard to explain without a schema example, but I'll give it a shot...

To eliminate multiple many to many relationships to a single table will require using a surrogate PK that is common to multiple tables. (This gives you a single many to many join instead of one for each table.) In the extreme case you end up with a single column table providing nothing buta FK to the other tables which they can use as a pure FK or a PK. Think of it as multiple tables inheriting a many to many relationship from a single column parent, but nothing else. The nice thing about this is that the single column table drops out of any actual queries since you can use the (inherited) FK to join to the many to many table it connects to.