Sales.Customer Table

Description

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

Properties

Creation Date	08/01/2010 08:40
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	19185
Data Space Used	824.00 KB
Index Space Used	1,176.00 KB

Columns

Column Name	Description	Datatype	Length	Default	Formula
CustomerID	Primary key for Customer records.	int	4
TerritoryID	ID of the territory in which the customer is located. Foreign key to SalesTerritory.SalesTerritoryID.	int	4
AccountNumber	Unique number identifying the customer assigned by the accounting system.	varchar	10		(isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),''))
CustomerType	Customer type: I = Individual, S = Store	nchar	1
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	uniqueidentifier	16	(newid())
ModifiedDate	Date and time the record was last updated.	datetime	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

Check Constraints

Name	Description	Expression
CK_Customer_CustomerType	Check constraint [CustomerType]='I' OR [CustomerType]='i' OR [CustomerType]='S' OR [CustomerType]='s'	(upper([CustomerType])='I' OR upper([CustomerType])='S')

Relationships

Relationship	Description
FK_Customer_SalesTerritory_TerritoryID	Foreign key constraint referencing SalesTerritory.TerritoryID.
FK_CustomerAddress_Customer_CustomerID	Foreign key constraint referencing Customer.CustomerID.
FK_Individual_Customer_CustomerID	Foreign key constraint referencing Customer.CustomerID.
FK_SalesOrderHeader_Customer_CustomerID	Foreign key constraint referencing Customer.CustomerID.
FK_Store_Customer_CustomerID	Foreign key constraint referencing Customer.CustomerID.

Objects that depend on Sales.Customer

Database Object	Object Type	Description	Dep Level
Sales.CustomerAddress	Table	Cross-reference table mapping customers to their address(es).	1
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.Individual	Table	Demographic data about customers that purchase Adventure Works products online.	1
iStore	Trigger	AFTER INSERT trigger inserting Store only if the Customer does not exist in the Individual table.	2
iuIndividual	Trigger	AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Individual table to the current date.	2
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.	2
Sales.Store	Table	Customers (resellers) of Adventure Works products.	1
Sales.StoreContact	Table	Cross-reference table mapping stores and their employees.	2
dbo.ufnGetContactInformation	User Defined Function	Table value function returning the first name, last name, job title and contact type for a given contact.	2
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.	2
Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	1
Sales.vIndividualDemographics	View	Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table.	2
Sales.vSalesPersonSalesByFiscalYears	View	Uses PIVOT to return aggregated sales information for each sales representative.	2
Sales.vStoreWithDemographics	View	Stores (names and addresses) that sell Adventure Works Cycles products to consumers.	1

Objects that Sales.Customer depends on

Database Object	Object Type	Description	Dep Level
dbo.Name	User Defined Data Type		2
Sales.SalesTerritory	Table	Sales territory lookup table.	1
dbo.ufnLeadingZeros	User Defined Function	Scalar function used by the Sales.Customer table to help set the account number.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
CREATE TABLE [Sales].[Customer](
	[CustomerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[TerritoryID] [int] NULL,
	[AccountNumber]  AS (isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),'')),
	[CustomerType] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[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]  WITH CHECK ADD  CONSTRAINT [FK_Customer_SalesTerritory_TerritoryID] FOREIGN KEY([TerritoryID])
REFERENCES [SalesTerritory] ([TerritoryID])
ALTER TABLE [Sales].[Customer] CHECK CONSTRAINT [FK_Customer_SalesTerritory_TerritoryID]
ALTER TABLE [Sales].[Customer]  WITH CHECK ADD  CONSTRAINT [CK_Customer_CustomerType] CHECK  ((upper([CustomerType])='I' OR upper([CustomerType])='S'))
ALTER TABLE [Sales].[Customer] CHECK CONSTRAINT [CK_Customer_CustomerType]
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]

Related Objects

Sales Schema
AdventureWorks Database