User Tools

Site Tools


notes:sql:guid

ROWGUIDCOL

A column of the ROWGUIDCOL type:

  • A UNIQUE constraint on the column is defined automatically
  • Only one such a column is allowed per table
  • A new GUID can be created using NEWID()

Example:

-- Create a test table
IF OBJECT_ID('Books') IS NOT NULL DROP TABLE Books
GO
CREATE TABLE Books
(
    -- Row GUID
    BookID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
 
    Title NVARCHAR(250) NOT NULL DEFAULT ''
)
GO
 
-- Insert a few rows
INSERT INTO Books VALUES (NEWID(),'Title1')
INSERT INTO Books VALUES (NEWID(),'Title2')
INSERT INTO Books VALUES (NEWID(),'Title3')
 
-- Show results
SELECT * FROM Books
 
BookID                               Title
------------------------------------ ------
A9C1F8D0-8F15-44F0-915C-B2E39A83C5CC Title1
3FE8C523-3055-4A7C-928C-1285DAC5BD95 Title2
01AE6C0C-5F97-4DE4-82D0-AF70256F8BDC Title3

ROWGUIDCOL property:

-- Add ROWGUIDCOL property to a UNIQUEIDENTIFIER column
ALTER TABLE Customer ALTER COLUMN CustomerID ADD ROWGUIDCOL
 
-- Drop ROWGUIDCOL property
ALTER TABLE Customer ALTER COLUMN CustomerID DROP ROWGUIDCOL
notes/sql/guid.txt · Last modified: 2015/06/24 by admin