uniqueidentifIEr中文含義“唯一的標識符”。
uniqueidentifIEr數據類型是16個字節的二進制值,應具有唯一性,必須與NEWID()函數配合使用。
uniqueidentifIEr數據類型與identity自增不同,不會為插入的新行自動生成新的ID,新值由NEWID()函數指定。
NEWID()函數值會生成全球唯一的標識,標識由網卡號和CPU時鐘組成,如:6F9619FF-8B86-D011-B42D-00C04FC964FF
CREATE TABLE MyUniqueTable
(UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(),
Characters VARCHAR(10) )
GO
INSERT INTO MyUniqueTable(Characters) VALUES ('abc')
INSERT INTO MyUniqueTable VALUES (NEWID(), 'def')
GO
一個表可以定義多個uniqueidentifIEr列。
對變量使用NEWID()函數
DECLARE @myid uniqueidentifIEr
SET @myid = NEWID()
PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)
在 CREATE TABLE 語句中使用 NEWID
-- Creating a table using NEWID for uniqueidentifIEr data type.
CREATE TABLE cust
(
CustomerID uniqueidentifIEr NOT NULL
DEFAULT newid(),
Company varchar(30) NOT NULL,
ContactName varchar(60) NOT NULL,
Address varchar(30) NOT NULL,
City varchar(30) NOT NULL,
StateProvince varchar(10) NULL,
PostalCode varchar(10) NOT NULL,
CountryRegion varchar(20) NOT NULL,
Telephone varchar(15) NOT NULL,
Fax varchar(15) NULL
)
GO
-- Inserting data into cust table.
INSERT cust
(CustomerID, Company, ContactName, Address, City, StateProvince,
PostalCode, CountryRegion, Telephone, Fax)
VALUES
(NEWID(), 'Wartian Herkku', 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', NULL,
'90110', 'Finland', '981-443655', '981-443655')
INSERT cust
(CustomerID, Company, ContactName, Address, City, StateProvince,
PostalCode, CountryRegion, Telephone, Fax)
VALUES
(NEWID(), 'Wellington Importadora', 'Paula Parente', 'Rua do Mercado, 12', 'Resende', 'SP',
'08737-363', 'Brasil', '(14) 555-8122', '')
INSERT cust
(CustomerID, Company, ContactName, Address, City, StateProvince,
PostalCode, CountryRegion, Telephone, Fax)
VALUES
(NEWID(), 'Cactus Comidas para Ilevar', 'Patricio Simpson', 'Cerrito 333', 'Buenos Aires', NULL,
'1010', 'Argentina', '(1) 135-5555', '(1) 135-4892')
INSERT cust
(CustomerID, Company, ContactName, Address, City, StateProvince,
PostalCode, CountryRegion, Telephone, Fax)
VALUES
(NEWID(), 'Ernst Handel', 'Roland Mendel', 'Kirchgasse 6', 'Graz', NULL,
'8010', 'Austria', '7675-3425', '7675-3426')
INSERT cust
(CustomerID, Company, ContactName, Address, City, StateProvince,
PostalCode, CountryRegion, Telephone, Fax)
VALUES
(NEWID(), 'Maison Dewey', 'Catherine Dewey', 'Rue Joseph-Bens 532', 'Bruxelles', NULL,
'B-1180', 'Belgium', '(02) 201 24 67', '(02) 201 24 68')
GO
使用 uniqueidentifIEr 和變量賦值,將名為
@myid
的局部變量聲明為
uniqueidentifIEr
數據類型的變量。然後使用
SET
語句為該變量賦值。
DECLARE @myid uniqueidentifIEr
SET @myid = 'A972C577-DFB0-064E-1189-0154C99310DAAC12'
GO