System-generated unique ID numbers for reaching records (ROWID in Oracle) in SQL Server

Well, there is some easy ways to achieve your goal in SQL Server:

Sample No. 1
You can use the property IDENTITY when you add the columns. (Remember, in SQL Server a column is a field.) This will generate system sequential values identifying each row inserted to the table. This kind of column is often used for Primary Key Values.

 

 CREATE TABLE table (column_name data_type [IDENTITY[(seed[, increment]) NOT NULL) CREATE TABLE Students (student_id INT IDENTITY(100, 5) NOT NULL, lastname VARCHAR(20))

The values in the column will be 100, 105, 110, and so on for each new record inserted in the table. You must keep in mind that only one identity column is allowed per table; it cannot be updated; it does not allow null values; and also must be used with an integer (int, smallint, tinyint), numeric or decimal data type, but the numeric or decimal data types must be specified with a scale of 0.

Sample No. 2
You could also use the NEWID function and the uniqueidentifier data type, which will ensure globally unique values. This function and data type are used together when data is collated from many tables into a larger table, and you need to maintain uniqueness among all records. This data type uses a 16-byte binary string and is used to store a Global Unique Identifier (GUID). The NEWID function creates a unique identifier number that can store a GUID using the uniqueidentifier data type.

 

 CREATE TABLE suppliers (supp_id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(), supp_name CHAR(30) NOT NULL)

In the example above, a unique value for supp_id will be generated for each new row. Remember that I did use CREATE TABLE sentences but you can also do this with ALTER TABLE and after ALTER COLUMN or ADD (for adding columns).

 

For More Information

• What do you think about this answer? E-mail us at [email protected] with your feedback.
• The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
• The Best SQL Web Links
• Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our SQL Server guru is waiting to answer your toughest questions.