Sales.Customer Table

In This Topic

Description

Current customer information. Also see the Person and Store tables.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	19820
Data Space Used	968.00 KB
Index Space Used	1,328.00 KB

Columns

Column Name	Description	Datatype	Length	Default	Formula
CustomerID	Primary key.	Integer	4
PersonID	Foreign key to Person.BusinessEntityID	Integer	4
StoreID	Foreign key to Store.BusinessEntityID	Integer	4
TerritoryID	ID of the territory in which the customer is located. Foreign key to SalesTerritory.SalesTerritoryID.	Integer	4
AccountNumber	Unique number identifying the customer assigned by the accounting system.	VarChar	10		(isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),''))
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	GUID	16	(newid())
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_Customer_AccountNumber	Unique nonclustered index.
AK_Customer_rowguid	Unique nonclustered index. Used to support replication samples.
IX_Customer_TerritoryID	Nonclustered index.
PK_Customer_CustomerID	Primary key (clustered) constraint

Relationships

Relationship	Description
FK_Customer_Person_PersonID	Foreign key constraint referencing Person.BusinessEntityID.
FK_Customer_SalesTerritory_TerritoryID	Foreign key constraint referencing SalesTerritory.TerritoryID.
FK_Customer_Store_StoreID	Foreign key constraint referencing Store.BusinessEntityID.

Objects that depend on Sales.Customer

Database Object	Object Type	Description	Dep Level
iduSalesOrderDetail	Trigger	AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.	3
Sales.SalesOrderDetail	Table	Individual products associated with a specific sales order. See SalesOrderHeader.	2
Sales.SalesOrderHeader	Table	General sales order information.	1
Sales.SalesOrderHeaderSalesReason	Table	Cross-reference table mapping sales orders to sales reason codes.	3
dbo.ufnGetContactInformation	User Defined Function	Table value function returning the first name, last name, job title and contact type for a given contact.	1
uSalesOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables.	3
Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	2
Sales.vSalesPersonSalesByFiscalYears	View	Uses PIVOT to return aggregated sales information for each sales representative.	3

Objects that Sales.Customer depends on

Database Object	Object Type	Description	Dep Level
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	2
Person.CountryRegion	Table	Lookup table containing the ISO standard codes for countries and regions.	4
HumanResources.Employee	Table	Employee information such as salary, department, and title.	3
dbo.Flag	User Defined Data Type		4
dbo.Name	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		5
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	4
Sales.SalesPerson	Table	Sales representative current information.	2
Sales.SalesTerritory	Table	Sales territory lookup table.	3
Sales.Store	Table	Customers (resellers) of Adventure Works products.	1
dbo.ufnLeadingZeros	User Defined Function	Scalar function used by the Sales.Customer table to help set the account number.	3

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[Customer](
    [CustomerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [PersonID] [int] NULL,
    [StoreID] [int] NULL,
    [TerritoryID] [int] NULL,
    [AccountNumber]  AS (isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),'')),
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED 
(
    [CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Sales].[Customer] ADD  CONSTRAINT [DF_Customer_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Sales].[Customer] ADD  CONSTRAINT [DF_Customer_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Sales].[Customer]  WITH CHECK ADD  CONSTRAINT [FK_Customer_Person_PersonID] FOREIGN KEY([PersonID])
REFERENCES [Person].[Person] ([BusinessEntityID])
ALTER TABLE [Sales].[Customer] CHECK CONSTRAINT [FK_Customer_Person_PersonID]
ALTER TABLE [Sales].[Customer]  WITH CHECK ADD  CONSTRAINT [FK_Customer_SalesTerritory_TerritoryID] FOREIGN KEY([TerritoryID])
REFERENCES [Sales].[SalesTerritory] ([TerritoryID])
ALTER TABLE [Sales].[Customer] CHECK CONSTRAINT [FK_Customer_SalesTerritory_TerritoryID]
ALTER TABLE [Sales].[Customer]  WITH CHECK ADD  CONSTRAINT [FK_Customer_Store_StoreID] FOREIGN KEY([StoreID])
REFERENCES [Sales].[Store] ([BusinessEntityID])
ALTER TABLE [Sales].[Customer] CHECK CONSTRAINT [FK_Customer_Store_StoreID]

Related Objects

Sales Schema
AdventureWorks Database

Send comments on this topic.