Q
Problem solve Get help with specific problems with your technologies, process and projects.

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

Are there any system-generated unique identification 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

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close