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,
ORDER BY NEWID()
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:
- Launch your database system and create a new table.
- Name the first column.
- Access users: select AutoNumber from the Data Type column. (Notice that the default Field Size setting is Long Integer.) SQL Server users: choose uniqueidentifier.
- Access users: select Replication ID from the Field Size property dropdown list (see Figure 3).
|Figure 3: Choose the Replication ID Field Size Setting|
- 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.
- 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|
- 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|