0

I want to maintain all columns in all tables in a database by unique id and ID should be in increasing order.

When I will add a column in any table of database, the column should uniquely identified by its id in whole database.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
NecessaryDevil
  • 105
  • 1
  • 10
  • Guids aren't good enough? You could use a sequence, but that might become a hotspot and cause problems – James Z Oct 24 '15 at 17:48
  • 1
    You have stated your requirements but have asked no question. Can you show what you have attempted? Arbitrarily adding an `IDENTITY` columns to all tables as a primary key without thought is a recipe for a bad data model. – Dan Guzman Oct 24 '15 at 17:49

1 Answers1

2

There is a T-SQL keyword IDENTITY use this to auto-increment a column.

If you define a table it would look like this:

CREATE TABLE MyTabel
(
     ID int IDENTITY(1,1) PRIMARY KEY,
     SomeColumn varchar(100) NOT NULL,
     AnotherColumn VARCHAR(100)
)

The first value of IDENTITY defines it's initial value. The second value is the increment. So if you write something like IDENTITY(100, 10) the inital ID (first ID of the first row you add) would be 100. The second 110, third 120,...

+++EDIT+++

CREATE TABLE MyTable
(
    ID uniqueidentifier NEWID() PRIMARY KEY,
    SomeColumn varchar(100) NOT NULL,
    AnotherColumn VARCHAR(100)
)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MikeVe
  • 1,062
  • 8
  • 13
  • i used it but this will keep this rule strict to only MyTable table, is there any way to to keep columns unique by id in increasing order(in comparison of all columns in all available table) in a database. – NecessaryDevil Oct 24 '15 at 18:14
  • 1
    There is a MS-SQL function called NEWID() which will generate a 16-Byte global unique ID for your Database. To define a Table with it, it should work like above. I edited my answer. – MikeVe Oct 24 '15 at 18:20
  • ...but it is not possible to do this with increasing numbers for your whole database. A call of this function will just give you any random ID with no particular order. – MikeVe Oct 24 '15 at 18:32