SQL: Microsoft Dynamics - Rebuilding a Database View Named 'FilteredContact'

Why?

Update: currently, I’m unable to rebuild this complex view due to SQL constraints. Hence, this article is a story of a workaround – tracing the object (db table) where a certain view is based. Indexes can often be created on such tables to improve SQL performance.

Because this MS Dynamics db object cannot be indexed, leading to high CPU consumption during runtime. Here’s an example of an occasion when SQL Server has pegged CPU utilization at 100% for hours.

How?

Read the ‘FilteredContact’ view build code to see that it’s based on the ‘Contacts’ view
Read the ‘Contacts’ view to realize that it’s based on the ‘ContactBase’ table
Add an index to ‘ContactBase’ table

Part 1: get the definitions views of ‘Filtered Contact’

--
-- Method 1: Obtaining definition (code) to create object
--
-- Object: 'FilteredContact' view
DECLARE @objectid int
DECLARE @code VARCHAR(MAX)
SET @objectid=(SELECT object_id FROM sys.objects
WHERE name = 'FilteredContact')
SET @code=(SELECT definition from sys.sql_modules
WHERE object_id = @objectid)

-- Print text blobs that are longer than 8000 chars
-- Be advised that there will be extra carriage returns
-- Hence printed code cannot be executed without fixing
DECLARE @count INT = 0
DECLARE @maxcount INT = (LEN(@code) / 8000) + 1
WHILE @count < @maxcount 
BEGIN
print(substring(@code, @count * 8000, (@count + 1) * 8000))
SET @count = @count + 1
END

An attempt to alter an existing view

USE TEST_MSCRM
GO
--ALTER VIEW [dbo].[FilteredContact] WITH SCHEMABINDING
--AS
--SELECT ...;

Another attempt to recreate view

When trying to create an index on such view using a t-sql such as the one shown below:

-- Creating an index
USE TEST_MSCRM
GO
CREATE UNIQUE CLUSTERED INDEX idx_FilteredContact
ON [dbo].[FilteredContact] (contactid,accountid);
GO

This error message occurs:

Msg 1939, Level 16, State 1, Line 4
Cannot create index on view 'FilteredContact' because the view is not schema bound.

Completion time: 2018-11-04T18:58:37.6112165-08:00

Red letters are added strings to the @code output from prior

USE [TEST_MSCRM]
GO
DROP VIEW IF EXISTS dbo.[FilteredContact];
GO
--
/*create view dbo.[FilteredContact] (
[accountid],
[accountiddsc],
[accountidname],
[accountidyominame],
...
) with view_metadata WITH SCHEMABINDING AS
select
[Contact].[AccountId],
--[Contact].[AccountIdDsc]
0,
[Contact].[AccountIdName],
[Contact].[AccountIdYomiName],
[Contact].[AccountRoleCode],
AccountRoleCodePLTable.Value,
[Contact].[Address1_AddressId],
[Contact].[Address1_AddressTypeCode],
Address1_AddressTypeCodePLTable.Value,
...
*/

The idea to recreate a table or view with the Schema Binding option so it can then be indexed to optimize runtime querying CPU utilization. However, some complex tsql has inner joins that runs into the constraints of ‘object referencing itself.’

Part 2: read ‘Contacts’ view

--
-- Method 2: Obtaining definition (code)
--
-- Object: 'Contact' view
DECLARE @sql VARCHAR(MAX);

SELECT @sql = definition
FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID('Contact');

-- Print text blobs that are longer than 8000 chars
-- Be advised that there will be extra carriage returns
-- Hence printed code cannot be executed without fixing
DECLARE @count INT = 0
DECLARE @maxcount INT = (LEN(@sql) / 8000) + 1
WHILE @count < @maxcount 
BEGIN
print(substring(@sql, @count * 8000, (@count + 1) * 8000))
SET @count = @count + 1
END

Error message to indicate ‘Contact’ view was based on ‘ContactBase’ table:

Msg 4512, Level 16, State 3, Procedure Contact, Line 437 [Batch Start Line 2]
Cannot schema bind view 'dbo.Contact' because name 'ContactBase' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Part 3: Creating a full-text index

It’s often better to run a SQL Profiler Trace and Database Engine Tuning Advisor to automate this process. Hence, the below instructions can be used for targeted database objects with much more effort than following the instructions provided in the above link.

Sample codes to create an index

-- Creating an index
USE TEST_MSCRM
GO
CREATE UNIQUE CLUSTERED INDEX idx_FilteredContact
ON [dbo].[FilteredContact] (contactid,accountid);
GO

-- Create a nonclustered index on a table or view
CREATE INDEX ContactBase ON ContactBase ('EmployeeId');

-- Create a clustered index on a table and use a 3-part name for the table
CREATE CLUSTERED INDEX ContactBase ON TEST_MSCRM.dbo.ContactBase ('EmployeeId');

Navigate to TEST_MSCRM > Tables > right-click ‘dbo.ContactBase’ > Full-Text Index > Define Full-Text Index

click Next to create an index basing on cndx_PrimaryKey_Contact

Select ‘EmployeeId’ > click Next > Next > Next > Next > Finish

click Finish

USE [TEST_MSCRM]
GO
--DROP VIEW IF EXISTS [dbo].[FilteredContact];
--GO
--
-- report view for contact
--
CREATE view [dbo].[FilteredContact](
    [accountid],
    [accountiddsc],
    [accountidname],
    [accountidyominame],
    [accountrolecode],
    [accountrolecodename],
    [address1_addressid],
    [address1_addresstypecode],
    [address1_addresstypecodename],
    [address1_city] -- truncated for brevity  
) WITH view_metadata AS --WITH SCHEMABINDING AS
select
    [Contact].[AccountId],
    -- Truncated for brevity
    [Contact].[YomiMiddleName],
   dbo.fn_GetNumberFormatString(t.CurrencyPrecision, us.NumberGroupFormat, us.NegativeCurrencyFormatCode, 1, case o.CurrencyDisplayOption when 0 then t.CurrencySymbol when 1 then t.ISOCurrencyCode end, us.CurrencyFormatCode),
   dbo.fn_GetNumberFormatString(o.PricingDecimalPrecision, us.NumberGroupFormat, us.NegativeCurrencyFormatCode, 1, case o.CurrencyDisplayOption when 0 then t.CurrencySymbol when 1 then t.ISOCurrencyCode end, us.CurrencyFormatCode)
from Contact
    join SystemUserBase u on (u.SystemUserId = dbo.fn_FindUserGuid() and u.IsDisabled = 0)
    left join UserSettingsBase us on us.SystemUserId = u.SystemUserId
    left join OrganizationBase o on u.OrganizationId = o.OrganizationId
    left join TransactionCurrencyBase t on t.TransactionCurrencyId = [Contact].TransactionCurrencyId
    left outer join StringMap [AccountRoleCodePLTable] on 
		([AccountRoleCodePLTable].AttributeName = 'accountrolecode'
		and [AccountRoleCodePLTable].ObjectTypeCode = 2
		and [AccountRoleCodePLTable].AttributeValue = [Contact].[AccountRoleCode]
		and [AccountRoleCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address1_AddressTypeCodePLTable] on 
		([Address1_AddressTypeCodePLTable].AttributeName = 'address1_addresstypecode'
		and [Address1_AddressTypeCodePLTable].ObjectTypeCode = 2
		and [Address1_AddressTypeCodePLTable].AttributeValue = [Contact].[Address1_AddressTypeCode]
		and [Address1_AddressTypeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address1_FreightTermsCodePLTable] on 
		([Address1_FreightTermsCodePLTable].AttributeName = 'address1_freighttermscode'
		and [Address1_FreightTermsCodePLTable].ObjectTypeCode = 2
		and [Address1_FreightTermsCodePLTable].AttributeValue = [Contact].[Address1_FreightTermsCode]
		and [Address1_FreightTermsCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address1_ShippingMethodCodePLTable] on 
		([Address1_ShippingMethodCodePLTable].AttributeName = 'address1_shippingmethodcode'
		and [Address1_ShippingMethodCodePLTable].ObjectTypeCode = 2
		and [Address1_ShippingMethodCodePLTable].AttributeValue = [Contact].[Address1_ShippingMethodCode]
		and [Address1_ShippingMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address2_AddressTypeCodePLTable] on 
		([Address2_AddressTypeCodePLTable].AttributeName = 'address2_addresstypecode'
		and [Address2_AddressTypeCodePLTable].ObjectTypeCode = 2
		and [Address2_AddressTypeCodePLTable].AttributeValue = [Contact].[Address2_AddressTypeCode]
		and [Address2_AddressTypeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address2_FreightTermsCodePLTable] on 
		([Address2_FreightTermsCodePLTable].AttributeName = 'address2_freighttermscode'
		and [Address2_FreightTermsCodePLTable].ObjectTypeCode = 2
		and [Address2_FreightTermsCodePLTable].AttributeValue = [Contact].[Address2_FreightTermsCode]
		and [Address2_FreightTermsCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address2_ShippingMethodCodePLTable] on 
		([Address2_ShippingMethodCodePLTable].AttributeName = 'address2_shippingmethodcode'
		and [Address2_ShippingMethodCodePLTable].ObjectTypeCode = 2
		and [Address2_ShippingMethodCodePLTable].AttributeValue = [Contact].[Address2_ShippingMethodCode]
		and [Address2_ShippingMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address3_AddressTypeCodePLTable] on 
		([Address3_AddressTypeCodePLTable].AttributeName = 'address3_addresstypecode'
		and [Address3_AddressTypeCodePLTable].ObjectTypeCode = 2
		and [Address3_AddressTypeCodePLTable].AttributeValue = [Contact].[Address3_AddressTypeCode]
		and [Address3_AddressTypeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address3_FreightTermsCodePLTable] on 
		([Address3_FreightTermsCodePLTable].AttributeName = 'address3_freighttermscode'
		and [Address3_FreightTermsCodePLTable].ObjectTypeCode = 2
		and [Address3_FreightTermsCodePLTable].AttributeValue = [Contact].[Address3_FreightTermsCode]
		and [Address3_FreightTermsCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address3_ShippingMethodCodePLTable] on 
		([Address3_ShippingMethodCodePLTable].AttributeName = 'address3_shippingmethodcode'
		and [Address3_ShippingMethodCodePLTable].ObjectTypeCode = 2
		and [Address3_ShippingMethodCodePLTable].AttributeValue = [Contact].[Address3_ShippingMethodCode]
		and [Address3_ShippingMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [CreditOnHoldPLTable] on 
		([CreditOnHoldPLTable].AttributeName = 'creditonhold'
		and [CreditOnHoldPLTable].ObjectTypeCode = 2
		and [CreditOnHoldPLTable].AttributeValue = [Contact].[CreditOnHold]
		and [CreditOnHoldPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [CustomerSizeCodePLTable] on 
		([CustomerSizeCodePLTable].AttributeName = 'customersizecode'
		and [CustomerSizeCodePLTable].ObjectTypeCode = 2
		and [CustomerSizeCodePLTable].AttributeValue = [Contact].[CustomerSizeCode]
		and [CustomerSizeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [CustomerTypeCodePLTable] on 
		([CustomerTypeCodePLTable].AttributeName = 'customertypecode'
		and [CustomerTypeCodePLTable].ObjectTypeCode = 2
		and [CustomerTypeCodePLTable].AttributeValue = [Contact].[CustomerTypeCode]
		and [CustomerTypeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotBulkEMailPLTable] on 
		([DoNotBulkEMailPLTable].AttributeName = 'donotbulkemail'
		and [DoNotBulkEMailPLTable].ObjectTypeCode = 2
		and [DoNotBulkEMailPLTable].AttributeValue = [Contact].[DoNotBulkEMail]
		and [DoNotBulkEMailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotBulkPostalMailPLTable] on 
		([DoNotBulkPostalMailPLTable].AttributeName = 'donotbulkpostalmail'
		and [DoNotBulkPostalMailPLTable].ObjectTypeCode = 2
		and [DoNotBulkPostalMailPLTable].AttributeValue = [Contact].[DoNotBulkPostalMail]
		and [DoNotBulkPostalMailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotEMailPLTable] on 
		([DoNotEMailPLTable].AttributeName = 'donotemail'
		and [DoNotEMailPLTable].ObjectTypeCode = 2
		and [DoNotEMailPLTable].AttributeValue = [Contact].[DoNotEMail]
		and [DoNotEMailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotFaxPLTable] on 
		([DoNotFaxPLTable].AttributeName = 'donotfax'
		and [DoNotFaxPLTable].ObjectTypeCode = 2
		and [DoNotFaxPLTable].AttributeValue = [Contact].[DoNotFax]
		and [DoNotFaxPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotPhonePLTable] on 
		([DoNotPhonePLTable].AttributeName = 'donotphone'
		and [DoNotPhonePLTable].ObjectTypeCode = 2
		and [DoNotPhonePLTable].AttributeValue = [Contact].[DoNotPhone]
		and [DoNotPhonePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotPostalMailPLTable] on 
		([DoNotPostalMailPLTable].AttributeName = 'donotpostalmail'
		and [DoNotPostalMailPLTable].ObjectTypeCode = 2
		and [DoNotPostalMailPLTable].AttributeValue = [Contact].[DoNotPostalMail]
		and [DoNotPostalMailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotSendMMPLTable] on 
		([DoNotSendMMPLTable].AttributeName = 'donotsendmm'
		and [DoNotSendMMPLTable].ObjectTypeCode = 2
		and [DoNotSendMMPLTable].AttributeValue = [Contact].[DoNotSendMM]
		and [DoNotSendMMPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [EducationCodePLTable] on 
		([EducationCodePLTable].AttributeName = 'educationcode'
		and [EducationCodePLTable].ObjectTypeCode = 2
		and [EducationCodePLTable].AttributeValue = [Contact].[EducationCode]
		and [EducationCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [FamilyStatusCodePLTable] on 
		([FamilyStatusCodePLTable].AttributeName = 'familystatuscode'
		and [FamilyStatusCodePLTable].ObjectTypeCode = 2
		and [FamilyStatusCodePLTable].AttributeValue = [Contact].[FamilyStatusCode]
		and [FamilyStatusCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [FollowEmailPLTable] on 
		([FollowEmailPLTable].AttributeName = 'followemail'
		and [FollowEmailPLTable].ObjectTypeCode = 2
		and [FollowEmailPLTable].AttributeValue = [Contact].[FollowEmail]
		and [FollowEmailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [GenderCodePLTable] on 
		([GenderCodePLTable].AttributeName = 'gendercode'
		and [GenderCodePLTable].ObjectTypeCode = 2
		and [GenderCodePLTable].AttributeValue = [Contact].[GenderCode]
		and [GenderCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [HasChildrenCodePLTable] on 
		([HasChildrenCodePLTable].AttributeName = 'haschildrencode'
		and [HasChildrenCodePLTable].ObjectTypeCode = 2
		and [HasChildrenCodePLTable].AttributeValue = [Contact].[HasChildrenCode]
		and [HasChildrenCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [IsBackofficeCustomerPLTable] on 
		([IsBackofficeCustomerPLTable].AttributeName = 'isbackofficecustomer'
		and [IsBackofficeCustomerPLTable].ObjectTypeCode = 2
		and [IsBackofficeCustomerPLTable].AttributeValue = [Contact].[IsBackofficeCustomer]
		and [IsBackofficeCustomerPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [IsPrivatePLTable] on 
		([IsPrivatePLTable].AttributeName = 'isprivate'
		and [IsPrivatePLTable].ObjectTypeCode = 2
		and [IsPrivatePLTable].AttributeValue = [Contact].[IsPrivate]
		and [IsPrivatePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [LeadSourceCodePLTable] on 
		([LeadSourceCodePLTable].AttributeName = 'leadsourcecode'
		and [LeadSourceCodePLTable].ObjectTypeCode = 2
		and [LeadSourceCodePLTable].AttributeValue = [Contact].[LeadSourceCode]
		and [LeadSourceCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [MarketingOnlyPLTable] on 
		([MarketingOnlyPLTable].AttributeName = 'marketingonly'
		and [MarketingOnlyPLTable].ObjectTypeCode = 2
		and [MarketingOnlyPLTable].AttributeValue = [Contact].[MarketingOnly]
		and [MarketingOnlyPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [MergedPLTable] on 
		([MergedPLTable].AttributeName = 'merged'
		and [MergedPLTable].ObjectTypeCode = 2
		and [MergedPLTable].AttributeValue = [Contact].[Merged]
		and [MergedPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [msdyn_gdproptoutPLTable] on 
		([msdyn_gdproptoutPLTable].AttributeName = 'msdyn_gdproptout'
		and [msdyn_gdproptoutPLTable].ObjectTypeCode = 2
		and [msdyn_gdproptoutPLTable].AttributeValue = [Contact].[msdyn_gdproptout]
		and [msdyn_gdproptoutPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [ParticipatesInWorkflowPLTable] on 
		([ParticipatesInWorkflowPLTable].AttributeName = 'participatesinworkflow'
		and [ParticipatesInWorkflowPLTable].ObjectTypeCode = 2
		and [ParticipatesInWorkflowPLTable].AttributeValue = [Contact].[ParticipatesInWorkflow]
		and [ParticipatesInWorkflowPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [PaymentTermsCodePLTable] on 
		([PaymentTermsCodePLTable].AttributeName = 'paymenttermscode'
		and [PaymentTermsCodePLTable].ObjectTypeCode = 2
		and [PaymentTermsCodePLTable].AttributeValue = [Contact].[PaymentTermsCode]
		and [PaymentTermsCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [PreferredAppointmentDayCodePLTable] on 
		([PreferredAppointmentDayCodePLTable].AttributeName = 'preferredappointmentdaycode'
		and [PreferredAppointmentDayCodePLTable].ObjectTypeCode = 2
		and [PreferredAppointmentDayCodePLTable].AttributeValue = [Contact].[PreferredAppointmentDayCode]
		and [PreferredAppointmentDayCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [PreferredAppointmentTimeCodePLTable] on 
		([PreferredAppointmentTimeCodePLTable].AttributeName = 'preferredappointmenttimecode'
		and [PreferredAppointmentTimeCodePLTable].ObjectTypeCode = 2
		and [PreferredAppointmentTimeCodePLTable].AttributeValue = [Contact].[PreferredAppointmentTimeCode]
		and [PreferredAppointmentTimeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [PreferredContactMethodCodePLTable] on 
		([PreferredContactMethodCodePLTable].AttributeName = 'preferredcontactmethodcode'
		and [PreferredContactMethodCodePLTable].ObjectTypeCode = 2
		and [PreferredContactMethodCodePLTable].AttributeValue = [Contact].[PreferredContactMethodCode]
		and [PreferredContactMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [ShippingMethodCodePLTable] on 
		([ShippingMethodCodePLTable].AttributeName = 'shippingmethodcode'
		and [ShippingMethodCodePLTable].ObjectTypeCode = 2
		and [ShippingMethodCodePLTable].AttributeValue = [Contact].[ShippingMethodCode]
		and [ShippingMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [StateCodePLTable] on 
		([StateCodePLTable].AttributeName = 'statecode'
		and [StateCodePLTable].ObjectTypeCode = 2
		and [StateCodePLTable].AttributeValue = [Contact].[StateCode]
		and [StateCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [StatusCodePLTable] on 
		([StatusCodePLTable].AttributeName = 'statuscode'
		and [StatusCodePLTable].ObjectTypeCode = 2
		and [StatusCodePLTable].AttributeValue = [Contact].[StatusCode]
		and [StatusCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [TerritoryCodePLTable] on 
		([TerritoryCodePLTable].AttributeName = 'territorycode'
		and [TerritoryCodePLTable].ObjectTypeCode = 2
		and [TerritoryCodePLTable].AttributeValue = [Contact].[TerritoryCode]
		and [TerritoryCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    cross join dbo.fn_GetMaxPrivilegeDepthMask(2) pdm
where
(
	-- privilege check
	pdm.PrivilegeDepthMask is not null and
	(
	-- Owner check
	-- If the user has global access, then skip the ownership check
	((pdm.PrivilegeDepthMask & 0x8) != 0) or
	[Contact].OwnerId in 
		( -- returns only principals with Basic Read privilege for entity
			select pem.PrincipalId from PrincipalEntityMap pem WITH (NOLOCK)
			join SystemUserPrincipals sup WITH (NOLOCK) on pem.PrincipalId = sup.PrincipalId 
			where sup.SystemUserId = u.SystemUserId 
				and pem.ObjectTypeCode = 2
		)
		
	-- role based access
	or 
	
exists
(
	select 
	1
	where
	(
		-- deep/local security
		(((pdm.PrivilegeDepthMask & 0x4) != 0) or ((pdm.PrivilegeDepthMask & 0x2) != 0)) and 
		[Contact].[OwningBusinessUnit] in (select BusinessUnitId from SystemUserBusinessUnitEntityMap WITH (NOLOCK) where SystemUserId = u.SystemUserId and ObjectTypeCode = 2)
	) 
	or
	(
		-- global security
		((pdm.PrivilegeDepthMask & 0x8) != 0) and 
		[Contact].[OwningBusinessUnit] is not null 
	) 
)

	
	-- object shared to the user 
	or 
	[Contact].[ContactId] in 
		(
			select POA.ObjectId from PrincipalObjectAccess POA WITH (NOLOCK)
			join SystemUserPrincipals sup WITH (NOLOCK) on POA.PrincipalId = sup.PrincipalId
			where sup.SystemUserId = u.SystemUserId
				and POA.ObjectTypeCode = 2
				and ((POA.AccessRightsMask | POA.InheritedAccessRightsMask) & 1)=1
		)
	)
)

Sample code for ‘Contact’ view:

USE TEST_MSCRM
GO
--
-- base view for Contact
--
create view dbo.[Contact] (
    -- logical attributes
    [CreatedByYomiName],
    [CreatedByName],
    [OriginatingLeadIdYomiName],
    [OriginatingLeadIdName],
    [ModifiedByExternalPartyYomiName],
    [ModifiedByExternalPartyName],
    [CreatedByExternalPartyYomiName],
    [CreatedByExternalPartyName],
    [new_RetainedByYomiName],
    [new_RetainedByName],
    [DefaultPriceLevelIdName],
    [SLAName],
    [PreferredSystemUserIdName],
    [PreferredSystemUserIdYomiName],
    [TransactionCurrencyIdName],
    [PreferredServiceIdName],
    [SLAInvokedIdName],

    -- linked address entities
    [Address1_AddressTypeCode],
    [Address1_City],
    [Address1_Composite],
    [Address1_Country],
    [Address1_County],
    [Address1_AddressId],
    [Address1_Fax],
    [Address1_FreightTermsCode],
    [Address1_Latitude],
    [Address1_Line1],
    [Address1_Line2],
    [Address1_Line3],
    [Address1_Longitude],
    [Address1_Name],
    [Address1_PostalCode],
    [Address1_PostOfficeBox],
    [Address1_PrimaryContactName],
    [Address1_ShippingMethodCode],
    [Address1_StateOrProvince],
    [Address1_Telephone1],
    [Address1_Telephone2],
    [Address1_Telephone3],
    [Address1_UPSZone],
    [Address1_UTCOffset],

    [Address2_AddressTypeCode],
    [Address2_City],
    [Address2_Composite],
    [Address2_Country],
    [Address2_County],
    [Address2_AddressId],
    [Address2_Fax],
    [Address2_FreightTermsCode],
    [Address2_Latitude],
    [Address2_Line1],
    [Address2_Line2],
    [Address2_Line3],
    [Address2_Longitude],
    [Address2_Name],
    [Address2_PostalCode],
    [Address2_PostOfficeBox],
    [Address2_PrimaryContactName],
    [Address2_ShippingMethodCode],
    [Address2_StateOrProvince],
    [Address2_Telephone1],
    [Address2_Telephone2],
    [Address2_Telephone3],
    [Address2_UPSZone],
    [Address2_UTCOffset],

    [Address3_AddressTypeCode],
    [Address3_City],
    [Address3_Composite],
    [Address3_Country],
    [Address3_County],
    [Address3_AddressId],
    [Address3_Fax],
    [Address3_FreightTermsCode],
    [Address3_Latitude],
    [Address3_Line1],
    [Address3_Line2],
    [Address3_Line3],
    [Address3_Longitude],
    [Address3_Name],
    [Address3_PostalCode],
    [Address3_PostOfficeBox],
    [Address3_PrimaryContactName],
    [Address3_ShippingMethodCode],
    [Address3_StateOrProvince],
    [Address3_Telephone1],
    [Address3_Telephone2],
    [Address3_Telephone3],
    [Address3_UPSZone],
    [Address3_UTCOffset],

    -- ownership entries
    OwnerId,
    OwnerIdName,
    OwnerIdYomiName,
    OwnerIdDsc,
    OwnerIdType,
    OwningUser,
    OwningTeam,


	[AccountId],
	[AccountIdName],
	[AccountIdYomiName],
	[ParentContactId],
	[ParentContactIdName],
	[ParentContactIdYomiName],
    -- physical attributes
    [ContactId],
    [DefaultPriceLevelId],
    [CustomerSizeCode],
    [CustomerTypeCode],
    [PreferredContactMethodCode],
    [LeadSourceCode],
    [OriginatingLeadId],
    [OwningBusinessUnit],
    [PaymentTermsCode],
    [ShippingMethodCode],
    [ParticipatesInWorkflow],
    [IsBackofficeCustomer],
    [Salutation],
    [JobTitle],
    [FirstName],
    [Department],
    [NickName],
    [MiddleName],
    [LastName],
    [Suffix],
    [YomiFirstName],
    [FullName],
    [YomiMiddleName],
    [YomiLastName],
    [Anniversary],
    [BirthDate],
    [GovernmentId],
    [YomiFullName],
    [Description],
    [EmployeeId],
    [GenderCode],
    [AnnualIncome],
    [HasChildrenCode],
    [EducationCode],
    [WebSiteUrl],
    [FamilyStatusCode],
    [FtpSiteUrl],
    [EMailAddress1],
    [SpousesName],
    [AssistantName],
    [EMailAddress2],
    [AssistantPhone],
    [EMailAddress3],
    [DoNotPhone],
    [ManagerName],
    [ManagerPhone],
    [DoNotFax],
    [DoNotEMail],
    [DoNotPostalMail],
    [DoNotBulkEMail],
    [DoNotBulkPostalMail],
    [AccountRoleCode],
    [TerritoryCode],
    [IsPrivate],
    [CreditLimit],
    [CreatedOn],
    [CreditOnHold],
    [CreatedBy],
    [ModifiedOn],
    [ModifiedBy],
    [NumberOfChildren],
    [ChildrensNames],
    [VersionNumber],
    [MobilePhone],
    [Pager],
    [Telephone1],
    [Telephone2],
    [Telephone3],
    [Fax],
    [Aging30],
    [StateCode],
    [Aging60],
    [StatusCode],
    [Aging90],
    [PreferredSystemUserId],
    [PreferredServiceId],
    [MasterId],
    [PreferredAppointmentDayCode],
    [PreferredAppointmentTimeCode],
    [DoNotSendMM],
    [ParentCustomerId],
    [Merged],
    [ExternalUserIdentifier],
    [SubscriptionId],
    [PreferredEquipmentId],
    [LastUsedInCampaign],
    [ParentCustomerIdName],
    [ParentCustomerIdType],
    [TransactionCurrencyId],
    [OverriddenCreatedOn],
    [ExchangeRate],
    [ImportSequenceNumber],
    [TimeZoneRuleVersionNumber],
    [UTCConversionTimeZoneCode],
    [AnnualIncome_Base],
    [CreditLimit_Base],
    [Aging60_Base],
    [Aging90_Base],
    [Aging30_Base],
    [ParentCustomerIdYomiName],
    [CreatedOnBehalfBy],
    [ModifiedOnBehalfBy],
    [IsAutoCreate],
    [StageId],
    [ProcessId],
    [EntityImageId],
    [TraversedPath],
    [SLAId],
    [SLAInvokedId],
    [OnHoldTime],
    [LastOnHoldTime],
    [FollowEmail],
    [TimeSpentByMeOnEmailAndMeetings],
    [Business2],
    [Callback],
    [Company],
    [Home2],
    [CreatedByExternalParty],
    [ModifiedByExternalParty],
    [MarketingOnly],    
    [msdyn_gdproptout]
) with view_metadata as
select
    -- logical attributes
    [lk_contactbase_createdby].[YomiFullName],
    [lk_contactbase_createdby].[FullName],
    [contact_originating_lead].[YomiFullName],
    [contact_originating_lead].[FullName],    
    [sla_contact].[Name],

    -- linked address entities
    [XXaddress1].[AddressTypeCode],
    [XXaddress1].[City],
    [XXaddress1].[Composite],
    [XXaddress1].[Country],
    [XXaddress1].[County],
    [XXaddress1].[CustomerAddressId],
    [XXaddress1].[Fax],
    [XXaddress1].[FreightTermsCode],
    [XXaddress1].[Latitude],
    [XXaddress1].[Line1],
    [XXaddress1].[Line2],
    [XXaddress1].[Line3],
    [XXaddress1].[Longitude],
    [XXaddress1].[Name],
    [XXaddress1].[PostalCode],
    [XXaddress1].[PostOfficeBox],
    [XXaddress1].[PrimaryContactName],
    [XXaddress1].[ShippingMethodCode],
    [XXaddress1].[StateOrProvince],
    [XXaddress1].[Telephone1],
    [XXaddress1].[Telephone2],
    [XXaddress1].[Telephone3],
    [XXaddress1].[UPSZone],
    [XXaddress1].[UTCOffset],
    [XXaddress2].[AddressTypeCode],
    [XXaddress2].[City],
    [XXaddress2].[Composite],
    [XXaddress2].[Country],
    [XXaddress2].[County],
    [XXaddress2].[CustomerAddressId],
    [XXaddress2].[Fax],
    [XXaddress2].[FreightTermsCode],
    [XXaddress2].[Latitude],
    [XXaddress2].[Line1],
    [XXaddress2].[Line2],
    [XXaddress2].[Line3],
    [XXaddress2].[Longitude],
    [XXaddress2].[Name],
    [XXaddress2].[PostalCode],
    [XXaddress2].[PostOfficeBox],
    [XXaddress2].[PrimaryContactName],
    [XXaddress2].[ShippingMethodCode],
    [XXaddress2].[StateOrProvince],
    [XXaddress2].[Telephone1],
    [XXaddress2].[Telephone2],
    [XXaddress2].[Telephone3],
    [XXaddress2].[UPSZone],
    [XXaddress2].[UTCOffset],
    [XXaddress3].[AddressTypeCode],
    [XXaddress3].[City],
    [XXaddress3].[Composite],
    [XXaddress3].[Country],
    [XXaddress3].[County],
    [XXaddress3].[CustomerAddressId],
    [XXaddress3].[Fax],
    [XXaddress3].[FreightTermsCode],
    [XXaddress3].[Latitude],
    [XXaddress3].[Line1],
    [XXaddress3].[Line2],
    [XXaddress3].[Line3],
    [XXaddress3].[Longitude],
    [XXaddress3].[Name],
    [XXaddress3].[PostalCode],
    [XXaddress3].[PostOfficeBox],
    [XXaddress3].[PrimaryContactName],
    [XXaddress3].[ShippingMethodCode],
    [XXaddress3].[StateOrProvince],
    [XXaddress3].[Telephone1],
    [XXaddress3].[Telephone2],
    [XXaddress3].[Telephone3],
    [XXaddress3].[UPSZone],
    [XXaddress3].[UTCOffset],
    -- ownership entries
    OwnerId = [ContactBase].OwnerId,
    OwnerName = XXowner.Name,
    OwnerYomiName =  XXowner.YomiName,
    OwnerDsc = 0, -- DSC is removed, stub it to 0
    OwnerIdType = XXowner.OwnerIdType,
    OwningUser = case 
 		when XXowner.OwnerIdType= 8 then XXowner.OwnerId
		else null
		end,
    OwningTeam = case 
 		when XXowner.OwnerIdType= 9 then XXowner.OwnerId
		else null
		end,


	[AccountId] = case 
		when [ContactBase].[ParentCustomerIdType] = 1 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerId]
		else NULL
		end,
	[AccountIdName] = case 
		when [ContactBase].[ParentCustomerIdType] = 1 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdName]
		else NULL
		end,
	[AccountIdYomiName] = case 
		when [ContactBase].[ParentCustomerIdType] = 1 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdYomiName]
		else NULL
		end,
	[ParentContactId] = case 
		when [ContactBase].[ParentCustomerIdType] = 2 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerId]
		else NULL
		end,
	[ParentContactIdName] = case 
		when [ContactBase].[ParentCustomerIdType] = 2 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdName]
		else NULL
		end,
	[ParentContactIdYomiName] = case 
		when [ContactBase].[ParentCustomerIdType] = 2 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdYomiName]
		else NULL
		end,
    -- physical attribute
    [ContactBase].[ContactId],
    [ContactBase].[DefaultPriceLevelId],
    [ContactBase].[CustomerSizeCode],
    [ContactBase].[CustomerTypeCode],
    [ContactBase].[PreferredContactMethodCode],
    [ContactBase].[LeadSourceCode],
    [ContactBase].[OriginatingLeadId],
    [ContactBase].[OwningBusinessUnit],
    [ContactBase].[PaymentTermsCode],
    [ContactBase].[ShippingMethodCode],
    [ContactBase].[ParticipatesInWorkflow],
    [ContactBase].[IsBackofficeCustomer],
    [ContactBase].[Salutation],
    [ContactBase].[JobTitle],
    [ContactBase].[FirstName],
    [ContactBase].[Department],
    [ContactBase].[NickName],
    [ContactBase].[MiddleName],
    [ContactBase].[LastName],
    [ContactBase].[Suffix],
    [ContactBase].[YomiFirstName],
    [ContactBase].[FullName],
    [ContactBase].[YomiMiddleName],
    [ContactBase].[YomiLastName],
    [ContactBase].[Anniversary],
    [ContactBase].[BirthDate],
    [ContactBase].[GovernmentId],
    [ContactBase].[YomiFullName],
    [ContactBase].[Description],
    [ContactBase].[EmployeeId],
    [ContactBase].[GenderCode],
    [ContactBase].[AnnualIncome],
    [ContactBase].[HasChildrenCode],
    [ContactBase].[EducationCode],
    [ContactBase].[WebSiteUrl],
    [ContactBase].[FamilyStatusCode],
    [ContactBase].[FtpSiteUrl],
    [ContactBase].[EMailAddress1],
    [ContactBase].[SpousesName],
    [ContactBase].[AssistantName],
    [ContactBase].[EMailAddress2],
    [ContactBase].[AssistantPhone],
    [ContactBase].[EMailAddress3],
    [ContactBase].[DoNotPhone],
    [ContactBase].[ManagerName],
    [ContactBase].[ManagerPhone],
    [ContactBase].[DoNotFax],
    [ContactBase].[DoNotEMail],
    [ContactBase].[DoNotPostalMail],
    [ContactBase].[DoNotBulkEMail],
    [ContactBase].[DoNotBulkPostalMail],
    [ContactBase].[AccountRoleCode],
    [ContactBase].[TerritoryCode],
    [ContactBase].[IsPrivate],
    [ContactBase].[CreditLimit],
    [ContactBase].[CreatedOn],
    [ContactBase].[CreditOnHold],
    [ContactBase].[CreatedBy],
    [ContactBase].[ModifiedOn],
    [ContactBase].[ModifiedBy],
    [ContactBase].[NumberOfChildren],
    [ContactBase].[ChildrensNames],
    [ContactBase].[VersionNumber],
    [ContactBase].[MobilePhone],
    [ContactBase].[Pager],
    [ContactBase].[Telephone1],
    [ContactBase].[Telephone2],
    [ContactBase].[Telephone3],
    [ContactBase].[Fax],
    [ContactBase].[Aging30],
    [ContactBase].[StateCode],
    [ContactBase].[Aging60],
    [ContactBase].[StatusCode],
    [ContactBase].[Aging90],
    [ContactBase].[PreferredSystemUserId],
    [ContactBase].[PreferredServiceId],
    [ContactBase].[MasterId],
    [ContactBase].[PreferredAppointmentDayCode],
    [ContactBase].[PreferredAppointmentTimeCode],
    [ContactBase].[DoNotSendMM],
    [ContactBase].[ParentCustomerId],
    [ContactBase].[Merged],
    [ContactBase].[ExternalUserIdentifier],
    [ContactBase].[SubscriptionId],
    [ContactBase].[PreferredEquipmentId],
    [ContactBase].[LastUsedInCampaign],
    [ContactBase].[ParentCustomerIdName],
    [ContactBase].[ParentCustomerIdType],
    [ContactBase].[TransactionCurrencyId],
    [ContactBase].[OverriddenCreatedOn],
    [ContactBase].[ExchangeRate],
    [ContactBase].[ImportSequenceNumber],
    [ContactBase].[TimeZoneRuleVersionNumber],
    [ContactBase].[UTCConversionTimeZoneCode],
    [ContactBase].[AnnualIncome_Base],
    [ContactBase].[CreditLimit_Base],
    [ContactBase].[Aging60_Base],
    [ContactBase].[Aging90_Base],
    [ContactBase].[Aging30_Base],
    [ContactBase].[ParentCustomerIdYomiName],
    [ContactBase].[CreatedOnBehalfBy],
    [ContactBase].[ModifiedOnBehalfBy],
    [ContactBase].[IsAutoCreate],
    [ContactBase].[StageId],
    [ContactBase].[ProcessId],
    [ContactBase].[EntityImageId],
    [ContactBase].[TraversedPath],
    [ContactBase].[SLAId],
    [ContactBase].[SLAInvokedId],
    [ContactBase].[OnHoldTime],
    [ContactBase].[LastOnHoldTime],
    [ContactBase].[FollowEmail],
    [ContactBase].[TimeSpentByMeOnEmailAndMeetings],
    [ContactBase].[Business2],
    [ContactBase].[Callback],
    [ContactBase].[Company],
    [ContactBase].[Home2],
    [ContactBase].[CreatedByExternalParty],
    [ContactBase].[ModifiedByExternalParty],
    [ContactBase].[MarketingOnly],    
    [ContactBase].[msdyn_gdproptout]
from [ContactBase] 
    left join [CustomerAddressBase] XXaddress1 on ([ContactBase].[ContactId] = XXaddress1.ParentId and XXaddress1.AddressNumber = 1)
    left join [CustomerAddressBase] XXaddress2 on ([ContactBase].[ContactId] = XXaddress2.ParentId and XXaddress2.AddressNumber = 2)
    left join [CustomerAddressBase] XXaddress3 on ([ContactBase].[ContactId] = XXaddress3.ParentId and XXaddress3.AddressNumber = 3)
    left join [ContactBase] [contact_master_contact] on ([ContactBase].[MasterId] = [contact_master_contact].[ContactId])
    left join [LeadBase] [contact_originating_lead] on ([ContactBase].[OriginatingLeadId] = [contact_originating_lead].[LeadId])
    left join [EquipmentBase] [equipment_contacts] on ([ContactBase].[PreferredEquipmentId] = [equipment_contacts].[EquipmentId])    
    left join [ImageDescriptor] [lk_contact_entityimage] on ([ContactBase].[EntityImageId] = [lk_contact_entityimage].[ImageDescriptorId])
    left join [SystemUserBase] [lk_contactbase_createdby] with(nolock) on ([ContactBase].[CreatedBy] = [lk_contactbase_createdby].[SystemUserId])
    left join [SystemUserBase] [lk_contactbase_createdonbehalfby] with(nolock) on ([ContactBase].[CreatedOnBehalfBy] = [lk_contactbase_createdonbehalfby].[SystemUserId])
    left join [SystemUserBase] [lk_contactbase_modifiedby] with(nolock) on ([ContactBase].[ModifiedBy] = [lk_contactbase_modifiedby].[SystemUserId])
    left join [SystemUserBase] [lk_contactbase_modifiedonbehalfby] with(nolock) on ([ContactBase].[ModifiedOnBehalfBy] = [lk_contactbase_modifiedonbehalfby].[SystemUserId])
    left join [ExternalPartyBase] [lk_externalparty_contact_createdby] on ([ContactBase].[CreatedByExternalParty] = [lk_externalparty_contact_createdby].[ExternalPartyId])
    left join [ExternalPartyBase] [lk_externalparty_contact_modifiedby] on ([ContactBase].[ModifiedByExternalParty] = [lk_externalparty_contact_modifiedby].[ExternalPartyId])
    left join [SLABase] [manualsla_contact] on ([ContactBase].[SLAId] = [manualsla_contact].[SLAId] and [manualsla_contact].OverwriteTime = 0 and [manualsla_contact].ComponentState = 0)
    left join [SystemUserBase] [new_systemuser_contact_RetainedBy] with(nolock) on ([ContactBase].[new_RetainedBy] = [new_systemuser_contact_RetainedBy].[SystemUserId])    
    left join [SLABase] [sla_contact] on ([ContactBase].[SLAInvokedId] = [sla_contact].[SLAId] and [sla_contact].OverwriteTime = 0 and [sla_contact].ComponentState = 0)
    left join [SystemUserBase] [system_user_contacts] with(nolock) on ([ContactBase].[PreferredSystemUserId] = [system_user_contacts].[SystemUserId])
    left join [TransactionCurrencyBase] [transactioncurrency_contact] on ([ContactBase].[TransactionCurrencyId] = [transactioncurrency_contact].[TransactionCurrencyId])
    left join OwnerBase XXowner with(nolock) on ([ContactBase].OwnerId = XXowner.OwnerId)

Categories:

Database

SQL: Microsoft Dynamics – Rebuilding a Database View Named ‘FilteredContact’

Why?

How?

Sample code for ‘Contact’ view:

Leave a Reply Cancel reply

Search

Archives

Meta

SQL: Microsoft Dynamics – Rebuilding a Database View Named ‘FilteredContact’

Why?

How?

Sample code for ‘Contact’ view:

Leave a Reply Cancel reply

Search

Archives

Meta

Tag Cloud