View: [AdventureWorks2016CTP3].[Person].[vStateProvinceCountryRegion]

CollapseAll image

Collapse image View properties


Name  Value 
 Schema   [Person] 
 Owner   [dbo] 
 Creation date   16.11.2015 
 Is schema bound 
 Encrypted   
 ID   1245247491 

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Resultset


Name  Description Data type Max length
StateProvinceID   int 4
StateProvinceCode   nchar 3
IsOnlyStateProvinceFlag   [dbo].[Flag] 1
StateProvinceName   [dbo].[Name] 50
TerritoryID   int 4
CountryRegionCode   nvarchar 3
CountryRegionName   [dbo].[Name] 50
Total: 7 column(s)

Collapse image Indexes


Index  Description  Primary  Unique  Size KB 
[IX_vStateProvinceCountryRegion]   StateProvinceID, CountryRegionCode   32 0

Collapse image Objects that [Person].[vStateProvinceCountryRegion] depends on


Object name Object type Dep level
[Person] Schema 1
[Person].[CountryRegion] Table 1
[Person].[StateProvince] Table 1
Total 3 object(s)

Collapse image Extended properties


Name  Value 
MS_Description Joins StateProvince table with CountryRegion table.

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE VIEW [Person].[vStateProvinceCountryRegion] 
WITH SCHEMABINDING 
AS 
SELECT 
    sp.[StateProvinceID] 
    ,sp.[StateProvinceCode] 
    ,sp.[IsOnlyStateProvinceFlag] 
    ,sp.[Name] AS [StateProvinceName] 
    ,sp.[TerritoryID] 
    ,cr.[CountryRegionCode] 
    ,cr.[Name] AS [CountryRegionName]
FROM [Person].[StateProvince] sp 
    INNER JOIN [Person].[CountryRegion] cr 
    ON sp.[CountryRegionCode] = cr.[CountryRegionCode];
GO
EXEC sp_addextendedproperty N'MS_Description', N'Joins StateProvince table with CountryRegion table.', 'SCHEMA', N'Person', 'VIEW', N'vStateProvinceCountryRegion', NULL, NULL
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [IX_vStateProvinceCountryRegion]
    ON [Person].[vStateProvinceCountryRegion] ([StateProvinceID], [CountryRegionCode])
    ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sp_addextendedproperty N'MS_Description', N'Clustered index on the view vStateProvinceCountryRegion.', 'SCHEMA', N'Person', 'VIEW', N'vStateProvinceCountryRegion', 'INDEX', N'IX_vStateProvinceCountryRegion'
GO

Collapse image See also


List of views