Sales.Store Table

Description

Customers (resellers) of Adventure Works products.

Properties

Creation Date	08/01/2010 08:40
File Group	PRIMARY
Text File Group	PRIMARY
System Object
Published for Replication
Rows	701
Data Space Used	808.00 KB
Index Space Used	80.00 KB

Columns

Column Name	Description	Datatype	Length	Default
CustomerID	Primary key. Foreign key to Customer.CustomerID.	int	4
Name	Name of the store.	dbo.Name	50
SalesPersonID	ID of the sales person assigned to the customer. Foreign key to SalesPerson.SalesPersonID.	int	4
Demographics	Demographic informationg about the store such as the number of employees, annual sales and store type.	Sales.StoreSurveySchemaCollection	16
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_Store_rowguid	Unique nonclustered index. Used to support replication samples.
IX_Store_SalesPersonID	Nonclustered index.
PK_Store_CustomerID	Primary key (clustered) constraint

Triggers

Trigger	Description
iStore	AFTER INSERT trigger inserting Store only if the Customer does not exist in the Individual table.

Relationships

Relationship	Description
FK_Store_Customer_CustomerID	Foreign key constraint referencing Customer.CustomerID.
FK_Store_SalesPerson_SalesPersonID	Foreign key constraint referencing SalesPerson.SalesPersonID
FK_StoreContact_Store_CustomerID	Foreign key constraint referencing Store.CustomerID.

Objects that depend on Sales.Store

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.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.	1
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.StoreContact	Table	Cross-reference table mapping stores and their employees.	1
dbo.ufnGetContactInformation	User Defined Function	Table value function returning the first name, last name, job title and contact type for a given contact.	2
Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	2
Sales.vIndividualDemographics	View	Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table.	2
Sales.vStoreWithDemographics	View	Stores (names and addresses) that sell Adventure Works Cycles products to consumers.	1

Objects that Sales.Store depends on

Database Object	Object Type	Description	Dep Level
Sales.Customer	Table	Current customer information. Also see the Individual and Store tables.	1
HumanResources.Employee	Table	Employee information such as salary, department, and title.	2
dbo.ErrorLog	Table	Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.	2
dbo.Flag	User Defined Data Type		3
Sales.Individual	Table	Demographic data about customers that purchase Adventure Works products online.	1
dbo.Name	User Defined Data Type		1
Sales.SalesPerson	Table	Sales representative current information.	1
Sales.SalesTerritory	Table	Sales territory lookup table.	2
dbo.ufnLeadingZeros	User Defined Function	Scalar function used by the Sales.Customer table to help set the account number.	2
dbo.uspLogError	Stored Procedure	Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.	1
dbo.uspPrintError	Stored Procedure	Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[Store](
	[CustomerID] [int] NOT NULL,
	[Name] [dbo].[Name] NOT NULL,
	[SalesPersonID] [int] NULL,
	[Demographics] [xml](CONTENT [Sales].[StoreSurveySchemaCollection]) NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Store_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].[Store]  WITH CHECK ADD  CONSTRAINT [FK_Store_Customer_CustomerID] FOREIGN KEY([CustomerID])
REFERENCES [Customer] ([CustomerID])
ALTER TABLE [Sales].[Store] CHECK CONSTRAINT [FK_Store_Customer_CustomerID]
ALTER TABLE [Sales].[Store]  WITH CHECK ADD  CONSTRAINT [FK_Store_SalesPerson_SalesPersonID] FOREIGN KEY([SalesPersonID])
REFERENCES [SalesPerson] ([SalesPersonID])
ALTER TABLE [Sales].[Store] CHECK CONSTRAINT [FK_Store_SalesPerson_SalesPersonID]
ALTER TABLE [Sales].[Store] ADD  CONSTRAINT [DF_Store_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Sales].[Store] ADD  CONSTRAINT [DF_Store_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

Related Objects

Sales Schema
AdventureWorks Database