SSELECT DISTINCT
tostring(ProductID)+'_'+tostring(getyear(Date))+'-'+tostring(getmonth(Date))+'-'+tostring(Getday(Date)) AS Prod_Date_Key,
Date,
PH.ProductID,
PH.ListPrice
FROM [ProductListPriceHistory] PH JOIN [AllDates] ON Date between PH.StartDate AND PH.EndDate
serial
, an Oracle or SQL ServerSEQUENCE
or a column defined with AUTO_INCREMENT
in MySQL). Some databases provide UUID/GUID as a possible data type for surrogate keys (e.g. PostgreSQL UUID
or SQL Server UNIQUEIDENTIFIER
).SurrogateKey | BusinessKey | EmployeeName | WorkingHoursPerWeek | RowValidFrom | RowValidTo |
---|---|---|---|---|---|
1 | BOS0120 | John Smith | 40 | 2000-01-01 | 2000-12-31 |
56 | P0000123 | Bob Brown | 25 | 1999-01-01 | 2011-12-31 |
234 | BOS0120 | John Smith | 35 | 2001-01-01 | 2009-12-31 |
IDENTITY
OR IDENTITY(n,n)
SEQUENCE
, or GENERATED AS IDENTITY
(starting from version 12.1)[3]SEQUENCE
(starting from SQL Server 2012)[4]AUTO_INCREMENT
AUTOINCREMENT
AS IDENTITY GENERATED BY DEFAULT
in IBM DB2|journal=
(help)