RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Use Globally Unique Identifiers in Access and SQL Server : Page 3

When your business outgrows your database systems, using globally unique identifier (GUID) values in Access and SQL Server can offer a timely solution.

You can also use GUID values to randomly select rows from a table or view, and you don't have to create a uniqueidentifier column to take advantage of this technique. You can use the NEWID() function to generate GUID values in a query. To illustrate this unique use, execute the following query in SQL Server Desktop or SQL Server:

SELECT TOP 10 dbo.Products.ProductID, dbo.Products.ProductName, 
FROM dbo.Products

The resultset contains 10 seemingly random product records (see Figure 2). This technique is beneficial when featuring random products on a Web site or selecting random records for an audit.

Figure 2: Using GUID Values to Select a Random Set of Records

Add a GUID Column
Because the GUID datatype is native to Access (95 and later) and SQL Server (7.0 and later), generating GUID values is a very simple process. Access GUIDS are called Replication ID's. To generate a GUID in Access, you simply define a Replication ID AutoNumber field. In SQL Server, you set a uniqueidentifier column's Is RowGuid property to Yes. Creating a GUID primary key column in Access, SQL Server Desktop (MSDE), or SQL Server is very similar:

  1. Launch your database system and create a new table.
  2. Name the first column.
  3. Access users: select AutoNumber from the Data Type column. (Notice that the default Field Size setting is Long Integer.) SQL Server users: choose uniqueidentifier.
  4. Access users: select Replication ID from the Field Size property dropdown list (see Figure 3).

    Figure 3: Choose the Replication ID Field Size Setting
  5. In SQL Server, change the Is RowGuid property setting to Yes. Both SQL Server Desktop and SQL Server will set the Default Value property to NEWID(), the Transact-SQL function that generates a new GUID.
  6. Click the Primary Key tool on the Table Design toolbar in Access. In SQL Server, click the Set primary key tool. (Figure 4 shows an Access project, and Figure 5 shows a similar table in SQL Server's Enterprise Manager.)

    Figure 4: Change the Is RowGuid Property to Yes in an Access Project

    Figure 5: The GUID Column Defaults to the NEWID() as the Default Value Property
  7. Add at least one other column so you can add data and watch the GUID primary key respond (see Figure 6).

    Figure 6: Add Data So You Can Watch the System Generate GUID Values

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date