Data Types in AdventureWorks
This topic lists the Microsoft .NET Framework CLR user-defined types, Microsoft SQL Server 2005 system data types, and Transact-SQL alias data types that are represented in the AdventureWorks sample database.
CLR User-defined Types
The following table lists the samples in AdventureWorks that use CLR user-defined types. For more information about CLR user-defined types, see CLR User-Defined Types.
| Sample | Description |
|---|---|
Defines a Currency user-defined data type by using C#. This user-defined data type encapsulates both an amount and a culture that helps determine the correct way to display the amount as a currency value in that culture. The sample also provides a currency conversion function that returns an instance of the Currency user-defined type. |
|
Includes functions to expose assembly metadata to Transact-SQL: sample streaming table-valued functions to return the types in an assembly as a table, and also functions to return the fields, methods, and properties in a user-defined type. |
|
Shows the creation and use of a simple user-defined data type from both Transact-SQL and a client application that uses System.Data.SqlClient. |
|
Shows the implementation of a UTF-8 user-defined data type that extends the type system of the database to provide storage for UTF-8 encoded values. This type also implements code to convert Unicode strings to and from UTF-8. |
|
Defines two user-defined data types, CADatetime and CADate, that provide calendar-aware handling of dates and times. |
|
Passes an array of information from a client to a CLR stored procedure on the server by using a CLR user-defined data type. |
System Data Types
The following table lists the SQL Server system data types and the tables and columns that use them. For more information about system data types, see Data Types (Transact-SQL).
| Data type | Used in this schema.table.column |
|---|---|
bigint |
Not used. |
bit |
See Flag and NameStyle in the alias data types section that follows. |
char or nchar |
HumanResources.Employee.Gender HumanResources.Employee.MaritalStatus Person.StateProvince.StateProvinceCode Production.BillOfMaterials.UnitMeasureCode Production.Culture.CultureID Production.Document.Revision Production.Product.Class Production.Product.ProductLine Production.Product.SizeUnitMeasureCode Production.Product.Style Production.Product.WeightUnitMeasureCode Production.ProductModelProductDescriptionCulture.CultureID Production.TransactionHistory.TransactionType Production.TransactionHistoryArchive.TransactionType Production.UnitMeasure.UnitMeasureCode Purchasing.ProductVendor.UnitMeasureCode Sales.CountryRegionCurrency.CurrencyCode Sales.CurrencyRate.FromCurrencyCode Sales.CurrencyRate.ToCurrencyCode Sales.Customer.CustomerType Sales.Currency.CurrencyCode |
datetime |
ModifiedDate column in all tables dbo.AWBuildVersion.VersionDate dbo.DatabaseLog.PostTime HumanResources.Employee.BirthDate HumanResources.Employee.HireDate HumanResources.EmployeeDepartmentHistory.EndDate HumanResources.EmployeeDepartmentHistory.StartDate HumanResources.EmployeePayHistory.RateChangeDate HumanResources.Shift.EndTime HumanResources.Shift.StartTime Production.BillOfMaterials.EndDate Production.BillOfMaterials.StartDate Production.Product.DiscontinuedDate Production.Product.SellEndDate Production.Product.SellStartDate Production.ProductCostHistory.EndDate Production.ProductCostHistory.StartDate Production.ProductListPriceHistory.EndDate Production.ProductListPriceHistory.StartDate Production.TransactionHistory.TransactionDate Production.TransactionHistoryArchive.TransactionDate Production.WorkOrder.DueDate Production.WorkOrder.EndDate Production.WorkOrder.StartDate Production.WorkOrderRouting.ActualEndDate Production.WorkOrderRouting.ActualStartDate Production.WorkOrderRouting.ScheduledEndDate Production.WorkOrderRouting.ScheduledStartDate Purchasing.ProductVendor.LastReceiptDate Purchasing.PurchaseOrderDetail.DueDate Purchasing.PurchaseOrderHeader.OrderDate Purchasing.PurchaseOrderHeader.ShipDate Sales.SalesCurrencyRate.CurrencyRateDate Sales.SalesOrderHeader.DueDate Sales.SalesOrderHeader.OrderDate Sales.SalesOrderHeader.ShipDate Sales.SalesPersonQuotaHistory.QuotaDate Sales.SalesTerritoryHistory.EndDate Sales.SalesTerritoryHistory.StartDate Sales.ShoppingCartItem.DateCreated Sales.SpecialOfferEndDate Sales.SpecialOfferStartDate |
decimal |
Production.BillOfMaterials.PerAssemblyQty Production.Location.Availability Production.Product.Weight Purchasing.PurchaseOrderDetail.ReceivedQty Purchasing.PurchaseOrderDetail.RejectedQty Purchasing.PurchaseOrderDetail.StockedQty Production.WorkOrderRouting.ActualResourcesHrs |
float |
Not used. |
image |
Not used. See varbinary(max). |
int |
All tables. |
money |
HumanResources.EmployeePayHistory.Rate Production.Product.ListPrice Production.Product.StandardCost Production.ProductCostHistory.StandardCost Production.ProductListPriceHistory.ListPrice Production.TransactionHistory.ActualCost Production.TransactionHistoryArchive.ActualCost Production.WorkOrderRouting.PlannedCost Production.WorkOrderRouting.ActualCost Purchasing.ProductVendor.LastReceiptCost Purchasing.ProductVendor.StandardPrice Purchasing.PurchaseOrderDetail.LineTotal Purchasing.PurchaseOrderDetail.UnitPrice Purchasing.PurchaseOrderHeader.Freight Purchasing.PurchaseOrderHeader.SubTotal Purchasing.PurchaseOrderHeader.TaxAmt Purchasing.PurchaseOrderHeader.TotalDue Purchasing.ShipMethod.ShipBase Purchasing.ShipMethod.ShipRate Sales.CurrencyRate.AverageRate Sales.CurrencyRate.EndOfDayRate Sales.SalesOrderDetail.UnitPrice Sales.SalesOrderDetail.UnitPriceDiscount Sales.SalesOrderHeader.Freight Sales.SalesOrderHeader.SubTotal Sales.SalesOrderHeader.TaxAmt Sales.SalesOrderHeader.TotalDue Sales.SalesPerson.Bonus Sales.SalesPerson.SalesLastYear Sales.SalesPerson.SalesYTD Sales.SalesPersonQuotaHistory.SalesQuota Sales.SalesTerritory.CostLastYear Sales.SalesTerritory.CostYTD Sales.SalesTerritory.SalesLastYear Sales.SalesTerritory.SalesYTD |
numeric |
Sales.SalesOrderDetail.LineTotal |
smallint |
HumanResources.Department.DepartmentID HumanResources.Employee.SickLeaveHours HumanResources.Employee.VacationHours HumanResources.EmployeeDepartmentHistory.DepartmentID Production.BillOfMaterials.BOMLevel Production.Location.LocationID Production.Product.SafetyStockLevel Production.Product.ReorderPoint Production.ProductInventory.Quantity Production.ProductInventory.LocationID Production.WorkOrder.ScrappedQty Production.WorkOrder.ScrapReasonID Production.WorkOrderRouting.OperationSequence Production.WorkOrderRouting.LocationID Purchasing.PurchaseOrderDetail.OrderQty Sales.CreditCard.ExpYear Sales.SalesOrderDetail.OrderQty |
smallmoney |
Production.Location.CostRate Sales.SalesPerson.CommissionPct Sales.SalesTaxRate.TaxRate Sales.SpecialOffer.DiscountPct |
sysname |
dbo.DatabaseLog.DatabaseUser dbo.DatabaseLog.Event dbo.DatabaseLog.Schema dbo.DatabaseLog.Object |
text or ntext |
Not used. See varchar(max) or nvarchar(max). |
tinyint |
dbo.AWBuildVersion.SystemInformationID HumanResources.EmployeePayHistory.PayFrequency HumanResources.EmployeeDepartmentHistory.ShiftID HumanResources.EmployePayHistory.Rate Production.Document.Status Production.ProductInventory.Bin Purchasing.PurchaseOrderHeader.RevisionNumber Purchasing.PurchaseOrderHeader.Status Purchasing.Vendor.CreditRating Sales.CreditCard.ExpMonth Sales.SalesOrderHeader.RevisionNumber Sales.SalesOrderHeader.Status Sales.SalesTaxRate.TaxType |
uniqueidentifier (GUID) |
See the ROWGUID column in tables in the following schemas:
In AdventureWorks, the ROWGUID column is used in a replication sample. |
varbinary(max) |
Production.Document.Document Production.ProductPhoto.LargePhoto Production.ProductPhoto.ThumbnailPhoto |
varchar or nvarchar |
All tables. |
varchar(max) or nvarchar(max) |
Production.Document.DocumentSummary dbo.DatabaseLog.TSQL |
xml |
dbo.DatabaseLog.XmlEvent HumanResources.JobCandidate.Resume Person.Contact.AdditionalContactInfo Production.Illustration.Diagram Production.ProductModel.CatalogDescription Production.ProductModel.Instructions Sales.Individual.Demographics Sales.Store.Demographics |
Alias Data Types
The following table lists alias data types, the Transact-SQL user-defined data types, and the tables and columns that use them. For more information about alias data types, see Working with Alias Data Types.
| Data type name | Defined as | Used in this schema.table.column |
|---|---|---|
AccountNumber |
nvarchar(15) |
Sales.SalesOrderHeader.AccountNumber Purchasing.Vendor.AccountNumber |
Flag |
bit |
HumanResources.Employee.CurrentFlag HumanResources.Employee.SalariedFlag Person.StateProvince.IsOnlyStateProvinceFlag Production.Product.FinishedGoodsFlag Production.Product.MakeFlag Production.ProductProductPhoto.Primary Purchasing.Vendor.ActiveFlag Purchasing.Vendor.PreferredVendorStatusFlag Sales.SalesOrderHeader.OnlineOrderFlag |
NameStyle |
bit |
Person.Contact.NameStyle |
Name |
nvarchar(50) |
HumanResources.Department.GroupName HumanResources.Department.Name HumanResources.Shift.Name Person.AddressType.Name Person.Contact.FirstName Person.Contact.LastName Person.Contact.MiddleName Person.ContactType.Name Person.CountryRegion.Name Person.StateProvince.Name Production.Culture.Name Production.Location.Name Production.Product.Name Production.ProductCategory.Name Production.ProductModel.Name Production.ProductReview.ReviewerName Production.ProductSubcategory.Name Production.ScrapReason.Name Production.UnitMeasure.Name Purchasing.ShipMethod.Name Purchasing.Vendor.Name Sales.Currency.Name Sales.SalesReason.Name Sales.SalesReason.ReasonType Sales.SalesTaxRate.Name Sales.SalesTerritory.Name Sales.Store.Name |
OrderNumber |
nvarchar(25) |
Sales.SalesOrderHeader.SalesOrderNumber |
Phone |
nvarchar(25) |
Person.Contact.Phone |
See Also
Other Resources
AdventureWorks Data Dictionary
SQL Server Objects in AdventureWorks