Sales.SalesTaxRate Table

Description

Tax rate lookup table.

Properties

Creation Date	17/07/2014 16:11
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	29
Data Space Used	8.00 KB
Index Space Used	40.00 KB

Columns

Column Name	Description	Datatype	Length	Default
SalesTaxRateID	Primary key for SalesTaxRate records.	Integer	4
StateProvinceID	State, province, or country/region the sales tax applies to.	Integer	4
TaxType	1 = Tax applied to retail transactions, 2 = Tax applied to wholesale transactions, 3 = Tax applied to all sales (retail and wholesale) transactions.	UnsignedTinyInt	1
TaxRate	Tax rate amount.	Currency	8	((0.00))
Name	Tax rate description.	dbo.Name	50
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_SalesTaxRate_rowguid	Unique nonclustered index. Used to support replication samples.
AK_SalesTaxRate_StateProvinceID_TaxType	Unique nonclustered index.
PK_SalesTaxRate_SalesTaxRateID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_SalesTaxRate_TaxType	Check constraint [TaxType] BETWEEN (1) AND (3)	([TaxType]>=(1) AND [TaxType]<=(3))

Relationships

Relationship	Description
FK_SalesTaxRate_StateProvince_StateProvinceID	Foreign key constraint referencing StateProvince.StateProvinceID.

Objects that Sales.SalesTaxRate depends on

Database Object	Object Type	Description	Dep Level
Person.CountryRegion	Table	Lookup table containing the ISO standard codes for countries and regions.	2
dbo.Flag	User Defined Data Type		2
dbo.Name	User Defined Data Type		1
Sales.SalesTerritory	Table	Sales territory lookup table.	2
Person.StateProvince	Table	State and province lookup table.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesTaxRate](
    [SalesTaxRateID] [int] IDENTITY(1,1) NOT NULL,
    [StateProvinceID] [int] NOT NULL,
    [TaxType] [tinyint] NOT NULL,
    [TaxRate] [smallmoney] NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesTaxRate_SalesTaxRateID] PRIMARY KEY CLUSTERED 
(
    [SalesTaxRateID] 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].[SalesTaxRate] ADD  CONSTRAINT [DF_SalesTaxRate_TaxRate]  DEFAULT ((0.00)) FOR [TaxRate]
ALTER TABLE [Sales].[SalesTaxRate] ADD  CONSTRAINT [DF_SalesTaxRate_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Sales].[SalesTaxRate] ADD  CONSTRAINT [DF_SalesTaxRate_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Sales].[SalesTaxRate]  WITH CHECK ADD  CONSTRAINT [FK_SalesTaxRate_StateProvince_StateProvinceID] FOREIGN KEY([StateProvinceID])
REFERENCES [Person].[StateProvince] ([StateProvinceID])
ALTER TABLE [Sales].[SalesTaxRate] CHECK CONSTRAINT [FK_SalesTaxRate_StateProvince_StateProvinceID]
ALTER TABLE [Sales].[SalesTaxRate]  WITH CHECK ADD  CONSTRAINT [CK_SalesTaxRate_TaxType] CHECK  (([TaxType]>=(1) AND [TaxType]<=(3)))
ALTER TABLE [Sales].[SalesTaxRate] CHECK CONSTRAINT [CK_SalesTaxRate_TaxType]

Related Objects

Sales Schema
AdventureWorks Database