Indexing strategy for link tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Indexing strategy for link tables

Author

Topic

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-15 : 17:16:41

Tara encouraged me to post a schema for a discussion on the best strategy for indexing link tables.

Here is a script for 2 main tables, Contacts and Companies, and their link tables. Note the following:

- The database is designed for merge replication (see my post 'GUIDs that start with date/time?' http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57800)

- Checkboxes are tiny ints so that they can be used in indexes set on multiple checkboxes.
Our application doesn't support checkbox arrays so each checkbox is in its own column.

- IX_Company_CompanyName is clustered. Would it be better if PK_Company were clustered?

Any comments will be appreciated.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Activity_Bill_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Activity] DROP CONSTRAINT LNK_Activity_Bill_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Activity_Involves_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Activity_Involves_Company] DROP CONSTRAINT LNK_Activity_Involves_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Activity_Original_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Activity_Original_Company] DROP CONSTRAINT LNK_Activity_Original_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Activity_Related_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Activity_Related_Company] DROP CONSTRAINT LNK_Activity_Related_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_App_Related_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[App_Related_Company] DROP CONSTRAINT LNK_App_Related_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Company_Parent_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Company] DROP CONSTRAINT LNK_Company_Parent_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_User_InvolvedWith_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Company_Involves_User] DROP CONSTRAINT LNK_User_InvolvedWith_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Location_Connected_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Company_PurchasesAt_Location] DROP CONSTRAINT LNK_Location_Connected_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Company_Referred_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Company_Referred_Company] DROP CONSTRAINT LNK_Company_Referred_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Company_ReferredBy_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Company_Referred_Company] DROP CONSTRAINT LNK_Company_ReferredBy_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Company_Connected_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Company_Related_Company] DROP CONSTRAINT LNK_Company_Connected_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Company_Related_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Company_Related_Company] DROP CONSTRAINT LNK_Company_Related_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Group_Related_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Company_Related_Group] DROP CONSTRAINT LNK_Group_Related_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Relation_Connected_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Company_Related_Relation] DROP CONSTRAINT LNK_Relation_Connected_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Vendor_Related_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Company_Related_Vendor] DROP CONSTRAINT LNK_Vendor_Related_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Vendor_UsedBy_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Company_Uses_Vendor] DROP CONSTRAINT LNK_Vendor_UsedBy_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Contact_Related_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact] DROP CONSTRAINT LNK_Contact_Related_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Contact_InvolvedWith_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_InvolvedWith_Company] DROP CONSTRAINT LNK_Contact_InvolvedWith_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Contact_Referred_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_Referred_Company] DROP CONSTRAINT LNK_Contact_Referred_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Contact_ReferredBy_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_ReferredBy_Company] DROP CONSTRAINT LNK_Contact_ReferredBy_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_EmailAli_Related_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[EmailAli] DROP CONSTRAINT LNK_EmailAli_Related_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Expense_Bill_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Expense] DROP CONSTRAINT LNK_Expense_Bill_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Expense_Related_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Expense_Related_Company] DROP CONSTRAINT LNK_Expense_Related_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Message_Related_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Message_Related_Company] DROP CONSTRAINT LNK_Message_Related_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Opp_For_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Opp] DROP CONSTRAINT LNK_Opp_For_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Opp_Involves_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Opp_Involves_Company] DROP CONSTRAINT LNK_Opp_Involves_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Project_For_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Project] DROP CONSTRAINT LNK_Project_For_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Project_Involved_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Project_Involved_Company] DROP CONSTRAINT LNK_Project_Involved_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Quote_Involves_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Quote_Involves_Company] DROP CONSTRAINT LNK_Quote_Involves_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_ToDo_Involves_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ToDo_Involves_Company] DROP CONSTRAINT LNK_ToDo_Involves_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_ToDo_Related_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ToDo_Related_Company] DROP CONSTRAINT LNK_ToDo_Related_Company
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Activity_bc_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Activity_bc_Contact] DROP CONSTRAINT LNK_Activity_bc_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Activity_cc_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Activity_cc_Contact] DROP CONSTRAINT LNK_Activity_cc_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Activity_Involves_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Activity_Involves_Contact] DROP CONSTRAINT LNK_Activity_Involves_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Activity_Original_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Activity_Original_Contact] DROP CONSTRAINT LNK_Activity_Original_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Activity_Related_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Activity_Related_Contact] DROP CONSTRAINT LNK_Activity_Related_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_App_With_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[App_With_Contact] DROP CONSTRAINT LNK_App_With_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Product_Interested_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_InterestedIn_Product] DROP CONSTRAINT LNK_Product_Interested_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Company_Involves_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_InvolvedWith_Company] DROP CONSTRAINT LNK_Company_Involves_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Company_ReferredBy_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_Referred_Company] DROP CONSTRAINT LNK_Company_ReferredBy_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Company_Referred_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_ReferredBy_Company] DROP CONSTRAINT LNK_Company_Referred_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Contact_Referred_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_ReferredBy_Contact] DROP CONSTRAINT LNK_Contact_Referred_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Contact_ReferredBy_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_ReferredBy_Contact] DROP CONSTRAINT LNK_Contact_ReferredBy_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Group_Connected_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_Related_Group] DROP CONSTRAINT LNK_Group_Connected_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Industry_Connected_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_Related_Industry] DROP CONSTRAINT LNK_Industry_Connected_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_JobFunc_Connected_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_Related_JobFunc] DROP CONSTRAINT LNK_JobFunc_Connected_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_User_Connected_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_Related_User] DROP CONSTRAINT LNK_User_Connected_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Vendor_Connected_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_Related_Vendor] DROP CONSTRAINT LNK_Vendor_Connected_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Contact_ReportsTo_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_ReportsTo_Contact] DROP CONSTRAINT LNK_Contact_ReportsTo_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Contact_Supervises_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact_ReportsTo_Contact] DROP CONSTRAINT LNK_Contact_Supervises_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_EmailAli_Related_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[EmailAli] DROP CONSTRAINT LNK_EmailAli_Related_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Expense_Related_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Expense_Related_Contact] DROP CONSTRAINT LNK_Expense_Related_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Message_Related_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Message_Related_Contact] DROP CONSTRAINT LNK_Message_Related_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Opp_OriginatedBy_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Opp] DROP CONSTRAINT LNK_Opp_OriginatedBy_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Opp_Involves_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Opp_Involves_Contact] DROP CONSTRAINT LNK_Opp_Involves_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Opp_KeySponsor_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Opp_KeySponsor_Contact] DROP CONSTRAINT LNK_Opp_KeySponsor_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Project_OriginatedBy_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Project] DROP CONSTRAINT LNK_Project_OriginatedBy_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Project_Involved_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Project_Involved_Contact] DROP CONSTRAINT LNK_Project_Involved_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Quote_bc_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Quote_bc_Contact] DROP CONSTRAINT LNK_Quote_bc_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Quote_cc_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Quote_cc_Contact] DROP CONSTRAINT LNK_Quote_cc_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Quote_Involves_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Quote_Involves_Contact] DROP CONSTRAINT LNK_Quote_Involves_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_Quote_To_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Quote_To_Contact] DROP CONSTRAINT LNK_Quote_To_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_ToDo_Involves_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ToDo_Involves_Contact] DROP CONSTRAINT LNK_ToDo_Involves_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LNK_ToDo_Related_Contact]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ToDo_Related_Contact] DROP CONSTRAINT LNK_ToDo_Related_Contact
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Company]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company_Referred_Company]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Company_Referred_Company]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company_Related_Company]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Company_Related_Company]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Contact]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Contact]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Contact_InvolvedWith_Company]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Contact_InvolvedWith_Company]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Contact_ReferredBy_Contact]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Contact_ReferredBy_Contact]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Contact_Referred_Company]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Contact_Referred_Company]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Contact_ReportsTo_Contact]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Contact_ReportsTo_Contact]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Contact_ReferredBy_Company]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Contact_ReferredBy_Company]
GO

CREATE TABLE [dbo].[Company] (
	[GID_ID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[CHK_ActiveField] [tinyint] NULL ,
	[MMO_AddrBilling] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MMO_AddrMailing] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MMO_AddrShipping] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FIL_Attachments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CHK_Billable] [tinyint] NULL ,
	[CUR_BillingRate] [money] NULL ,
	[TXT_CityBilling] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_CityMailing] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_CityShipping] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MMO_CompetingProds] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_CountryBilling] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_CountryMailing] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_CountryShipping] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MLS_Currency] [smallint] NULL ,
	[TXT_CustNo] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_CustCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CHK_DeleteRec] [tinyint] NULL ,
	[CHK_DemoData] [tinyint] NULL ,
	[MMO_Directions] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_DUNSno] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SR__ExchRate] [real] NULL ,
	[CHK_ExtDevReview] [tinyint] NULL ,
	[TEL_FaxNo] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FIL_File] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SR__Latitude] [real] NULL ,
	[SR__Longitude] [real] NULL ,
	[CHK_MainLocation] [tinyint] NULL ,
	[MLS_MarketingPriority] [smallint] NULL ,
	[MMO_Note] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_OrigCreatedBy] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DTR_OrigCreatedTime] [datetime] NULL ,
	[TEL_Phone2] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TEL_PhoneNo] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MMO_Profile] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_SICCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_StateBilling] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_StateMailing] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_StateShipping] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CHK_TargetAcct] [tinyint] NULL ,
	[TXT_TaxID] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CHK_Taxable] [tinyint] NULL ,
	[MLS_Type] [smallint] NULL ,
	[URL_URLs] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[URL_WebPage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_ZipBilling] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_ZipMailing] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_ZipShipping] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_ModBy] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DTT_ModTime] [timestamp] NULL ,
	[MMO_ImportData] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SI__ShareState] [tinyint] NULL ,
	[GID_CreatedBy_User] [uniqueidentifier] NULL ,
	[GID_Has_Terms] [uniqueidentifier] NULL ,
	[GID_In_PubTerr] [uniqueidentifier] NULL ,
	[GID_In_Terr] [uniqueidentifier] NULL ,
	[GID_Parent_Company] [uniqueidentifier] NULL ,
	[GID_Related_Division] [uniqueidentifier] NULL ,
	[GID_Related_Industry] [uniqueidentifier] NULL ,
	[GID_Related_Source] [uniqueidentifier] NULL ,
	[GID_TeamLeader_User] [uniqueidentifier] NULL ,
	[GID_Employs_User] [uniqueidentifier] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Company_Referred_Company] (
	[GID_ID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[GID_Company] [uniqueidentifier] NOT NULL ,
	[GID_Company2] [uniqueidentifier] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Company_Related_Company] (
	[GID_ID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[GID_Company] [uniqueidentifier] NOT NULL ,
	[GID_Company2] [uniqueidentifier] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Contact] (
	[GID_ID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[CHK_ActiveField] [tinyint] NULL ,
	[TXT_AssistantName] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TEL_AssistantPhone] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_AssistantTitle] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FIL_Attachments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TEL_BusPhone] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MLS_BuyingRole] [smallint] NULL ,
	[MLS_BuyingStyle] [smallint] NULL ,
	[TEL_CellPhone] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_CityBusiness] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_CityHome] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_CityOther] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_CompanyNameText] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_ContactCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_CountryBusiness] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_CountryHome] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_CountryOther] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DTE_DateOfBirth] [int] NULL ,
	[CHK_DemoData] [tinyint] NULL ,
	[MMO_Directions] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EML_Email] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TEL_Fax] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TEL_HomePhone] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MLS_InfluenceRole] [smallint] NULL ,
	[DTE_LastContactDate] [int] NULL ,
	[TXT_LastContactedBy] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_ModBy] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DTT_ModTime] [timestamp] NULL ,
	[CHK_MailingList] [tinyint] NULL ,
	[TEL_MainPhone] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_NameFirst] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_NameLast] [nvarchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DTE_NextContactDate] [int] NULL ,
	[TXT_Nickname] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MMO_Note] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_OrigCreatedBy] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DTT_OrigCreatedTime] [datetime] NULL ,
	[TEL_OtherPhone] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TEL_Pager] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CHK_ExtDevReview] [tinyint] NULL ,
	[MLS_Priority] [smallint] NULL ,
	[CHK_Review] [tinyint] NULL ,
	[INT_ReviewInterval] [smallint] NULL ,
	[MLS_Salutation] [smallint] NULL ,
	[TXT_StateBusiness] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_StateHome] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_StateOther] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MMO_AddrBusiness] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MMO_AddrHome] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MMO_AddrOther] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_TitleText] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MLS_Type] [smallint] NULL ,
	[URL_URLs] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[URL_Web] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_ZipBusiness] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_ZipHome] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TXT_ZipOther] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MMO_ImportData] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MMO_OutlookData] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MMO_PalmData] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SI__ShareState] [tinyint] NULL ,
	[GID_CreatedBy_User] [uniqueidentifier] NULL ,
	[GID_Is_User] [uniqueidentifier] NULL ,
	[GID_Related_Company] [uniqueidentifier] NULL ,
	[GID_Related_Source] [uniqueidentifier] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Contact_InvolvedWith_Company] (
	[GID_ID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[GID_Contact] [uniqueidentifier] NOT NULL ,
	[GID_Company] [uniqueidentifier] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Contact_ReferredBy_Contact] (
	[GID_ID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[GID_Contact] [uniqueidentifier] NOT NULL ,
	[GID_Contact2] [uniqueidentifier] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Contact_Referred_Company] (
	[GID_ID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[GID_Contact] [uniqueidentifier] NOT NULL ,
	[GID_Company] [uniqueidentifier] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Contact_ReportsTo_Contact] (
	[GID_ID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[GID_Contact] [uniqueidentifier] NOT NULL ,
	[GID_Contact2] [uniqueidentifier] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Contact_ReferredBy_Company] (
	[GID_ID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[GID_Contact] [uniqueidentifier] NOT NULL ,
	[GID_Company] [uniqueidentifier] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Company_Referred_Company] WITH NOCHECK ADD 
	CONSTRAINT [PK_Company_Referred_Company] PRIMARY KEY  CLUSTERED 
	(
		[GID_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Company_Related_Company] WITH NOCHECK ADD 
	CONSTRAINT [PK_Company_Related_Company] PRIMARY KEY  CLUSTERED 
	(
		[GID_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Contact_InvolvedWith_Company] WITH NOCHECK ADD 
	CONSTRAINT [PK_Contact_InvolvedWith_Company] PRIMARY KEY  CLUSTERED 
	(
		[GID_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Contact_ReferredBy_Contact] WITH NOCHECK ADD 
	CONSTRAINT [PK_Contact_ReferredBy_Contact] PRIMARY KEY  CLUSTERED 
	(
		[GID_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Contact_Referred_Company] WITH NOCHECK ADD 
	CONSTRAINT [PK_Contact_Referred_Company] PRIMARY KEY  CLUSTERED 
	(
		[GID_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Contact_ReportsTo_Contact] WITH NOCHECK ADD 
	CONSTRAINT [PK_Contact_ReportsTo_Contact] PRIMARY KEY  CLUSTERED 
	(
		[GID_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Contact_ReferredBy_Company] WITH NOCHECK ADD 
	CONSTRAINT [PK_Contact_ReferredBy_Company] PRIMARY KEY  CLUSTERED 
	(
		[GID_ID]
	)  ON [PRIMARY] 
GO

 CREATE  CLUSTERED  INDEX [IX_Company_CompanyName] ON [dbo].[Company]([TXT_CompanyName], [TXT_CustCode]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [IX_Contact_NameLastFirstCode] ON [dbo].[Contact]([TXT_NameLast], [TXT_NameFirst], [TXT_ContactCode]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Company] ADD 
	CONSTRAINT [PK_Company] PRIMARY KEY  NONCLUSTERED 
	(
		[GID_ID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_Company_CreatedBy_User] ON [dbo].[Company]([GID_CreatedBy_User]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_Has_Terms] ON [dbo].[Company]([GID_Has_Terms]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_In_Terr] ON [dbo].[Company]([GID_In_Terr]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_Parent_Company] ON [dbo].[Company]([GID_Parent_Company]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_Related_Division] ON [dbo].[Company]([GID_Related_Division]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_Related_Industry] ON [dbo].[Company]([GID_Related_Industry]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_Related_Source] ON [dbo].[Company]([GID_Related_Source]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_TeamLeader_User] ON [dbo].[Company]([GID_TeamLeader_User]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_Employs_User] ON [dbo].[Company]([GID_Employs_User]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_ZipCompName] ON [dbo].[Company]([TXT_ZipMailing], [TXT_CompanyName]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_ModDateTime] ON [dbo].[Company]([DTT_ModTime]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_Referred_Company] ON [dbo].[Company_Referred_Company]([GID_Company]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_ReferredBy_Company] ON [dbo].[Company_Referred_Company]([GID_Company2]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_Related_Company] ON [dbo].[Company_Related_Company]([GID_Company]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_Connected_Company] ON [dbo].[Company_Related_Company]([GID_Company2]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Contact] ADD 
	CONSTRAINT [PK_Contact] PRIMARY KEY  NONCLUSTERED 
	(
		[GID_ID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_Contact_CreatedBy_User] ON [dbo].[Contact]([GID_CreatedBy_User]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Contact_Is_User] ON [dbo].[Contact]([GID_Is_User]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Contact_Related_Company] ON [dbo].[Contact]([GID_Related_Company]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Contact_Related_Source] ON [dbo].[Contact]([GID_Related_Source]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Contact_ModDateTime] ON [dbo].[Contact]([DTT_ModTime]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Contact_InvolvedWith_Company] ON [dbo].[Contact_InvolvedWith_Company]([GID_Contact]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_Involves_Contact] ON [dbo].[Contact_InvolvedWith_Company]([GID_Company]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Contact_ReferredBy_Contact] ON [dbo].[Contact_ReferredBy_Contact]([GID_Contact]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Contact_Referred_Contact] ON [dbo].[Contact_ReferredBy_Contact]([GID_Contact2]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Contact_Referred_Company] ON [dbo].[Contact_Referred_Company]([GID_Contact]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_ReferredBy_Contact] ON [dbo].[Contact_Referred_Company]([GID_Company]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Contact_ReportsTo_Contact] ON [dbo].[Contact_ReportsTo_Contact]([GID_Contact]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Contact_Supervises_Contact] ON [dbo].[Contact_ReportsTo_Contact]([GID_Contact2]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Contact_ReferredBy_Company] ON [dbo].[Contact_ReferredBy_Company]([GID_Contact]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Company_Referred_Contact] ON [dbo].[Contact_ReferredBy_Company]([GID_Company]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Company] ADD 
	CONSTRAINT [LNK_Company_CreatedBy_User] FOREIGN KEY 
	(
		[GID_CreatedBy_User]
	) REFERENCES [dbo].[User] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Company_Employs_User] FOREIGN KEY 
	(
		[GID_Employs_User]
	) REFERENCES [dbo].[User] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Company_Has_Terms] FOREIGN KEY 
	(
		[GID_Has_Terms]
	) REFERENCES [dbo].[Terms] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Company_In_Terr] FOREIGN KEY 
	(
		[GID_In_Terr]
	) REFERENCES [dbo].[Terr] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Company_Parent_Company] FOREIGN KEY 
	(
		[GID_Parent_Company]
	) REFERENCES [dbo].[Company] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Company_Related_Division] FOREIGN KEY 
	(
		[GID_Related_Division]
	) REFERENCES [dbo].[Division] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Company_Related_Industry] FOREIGN KEY 
	(
		[GID_Related_Industry]
	) REFERENCES [dbo].[Industry] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Company_Related_Source] FOREIGN KEY 
	(
		[GID_Related_Source]
	) REFERENCES [dbo].[Source] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Company_TeamLeader_User] FOREIGN KEY 
	(
		[GID_TeamLeader_User]
	) REFERENCES [dbo].[User] (
		[GID_ID]
	) NOT FOR REPLICATION 
GO

alter table [dbo].[Company] nocheck constraint [LNK_Company_CreatedBy_User]
GO

alter table [dbo].[Company] nocheck constraint [LNK_Company_Employs_User]
GO

alter table [dbo].[Company] nocheck constraint [LNK_Company_Has_Terms]
GO

alter table [dbo].[Company] nocheck constraint [LNK_Company_In_Terr]
GO

alter table [dbo].[Company] nocheck constraint [LNK_Company_Parent_Company]
GO

alter table [dbo].[Company] nocheck constraint [LNK_Company_Related_Division]
GO

alter table [dbo].[Company] nocheck constraint [LNK_Company_Related_Industry]
GO

alter table [dbo].[Company] nocheck constraint [LNK_Company_Related_Source]
GO

alter table [dbo].[Company] nocheck constraint [LNK_Company_TeamLeader_User]
GO

ALTER TABLE [dbo].[Company_Referred_Company] ADD 
	CONSTRAINT [LNK_Company_Referred_Company] FOREIGN KEY 
	(
		[GID_Company2]
	) REFERENCES [dbo].[Company] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Company_ReferredBy_Company] FOREIGN KEY 
	(
		[GID_Company]
	) REFERENCES [dbo].[Company] (
		[GID_ID]
	) NOT FOR REPLICATION 
GO

alter table [dbo].[Company_Referred_Company] nocheck constraint [LNK_Company_Referred_Company]
GO

alter table [dbo].[Company_Referred_Company] nocheck constraint [LNK_Company_ReferredBy_Company]
GO

ALTER TABLE [dbo].[Company_Related_Company] ADD 
	CONSTRAINT [LNK_Company_Connected_Company] FOREIGN KEY 
	(
		[GID_Company]
	) REFERENCES [dbo].[Company] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Company_Related_Company] FOREIGN KEY 
	(
		[GID_Company2]
	) REFERENCES [dbo].[Company] (
		[GID_ID]
	) NOT FOR REPLICATION 
GO

alter table [dbo].[Company_Related_Company] nocheck constraint [LNK_Company_Connected_Company]
GO

alter table [dbo].[Company_Related_Company] nocheck constraint [LNK_Company_Related_Company]
GO

ALTER TABLE [dbo].[Contact] ADD 
	CONSTRAINT [LNK_Contact_CreatedBy_User] FOREIGN KEY 
	(
		[GID_CreatedBy_User]
	) REFERENCES [dbo].[User] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Contact_Is_User] FOREIGN KEY 
	(
		[GID_Is_User]
	) REFERENCES [dbo].[User] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Contact_Related_Company] FOREIGN KEY 
	(
		[GID_Related_Company]
	) REFERENCES [dbo].[Company] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Contact_Related_Source] FOREIGN KEY 
	(
		[GID_Related_Source]
	) REFERENCES [dbo].[Source] (
		[GID_ID]
	) NOT FOR REPLICATION 
GO

alter table [dbo].[Contact] nocheck constraint [LNK_Contact_CreatedBy_User]
GO

alter table [dbo].[Contact] nocheck constraint [LNK_Contact_Is_User]
GO

alter table [dbo].[Contact] nocheck constraint [LNK_Contact_Related_Company]
GO

alter table [dbo].[Contact] nocheck constraint [LNK_Contact_Related_Source]
GO

ALTER TABLE [dbo].[Contact_InvolvedWith_Company] ADD 
	CONSTRAINT [LNK_Company_Involves_Contact] FOREIGN KEY 
	(
		[GID_Contact]
	) REFERENCES [dbo].[Contact] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Contact_InvolvedWith_Company] FOREIGN KEY 
	(
		[GID_Company]
	) REFERENCES [dbo].[Company] (
		[GID_ID]
	) NOT FOR REPLICATION 
GO

alter table [dbo].[Contact_InvolvedWith_Company] nocheck constraint [LNK_Company_Involves_Contact]
GO

alter table [dbo].[Contact_InvolvedWith_Company] nocheck constraint [LNK_Contact_InvolvedWith_Company]
GO

ALTER TABLE [dbo].[Contact_ReferredBy_Contact] ADD 
	CONSTRAINT [LNK_Contact_Referred_Contact] FOREIGN KEY 
	(
		[GID_Contact]
	) REFERENCES [dbo].[Contact] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Contact_ReferredBy_Contact] FOREIGN KEY 
	(
		[GID_Contact2]
	) REFERENCES [dbo].[Contact] (
		[GID_ID]
	) NOT FOR REPLICATION 
GO

alter table [dbo].[Contact_ReferredBy_Contact] nocheck constraint [LNK_Contact_Referred_Contact]
GO

alter table [dbo].[Contact_ReferredBy_Contact] nocheck constraint [LNK_Contact_ReferredBy_Contact]
GO

ALTER TABLE [dbo].[Contact_Referred_Company] ADD 
	CONSTRAINT [LNK_Company_ReferredBy_Contact] FOREIGN KEY 
	(
		[GID_Contact]
	) REFERENCES [dbo].[Contact] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Contact_Referred_Company] FOREIGN KEY 
	(
		[GID_Company]
	) REFERENCES [dbo].[Company] (
		[GID_ID]
	) NOT FOR REPLICATION 
GO

alter table [dbo].[Contact_Referred_Company] nocheck constraint [LNK_Company_ReferredBy_Contact]
GO

alter table [dbo].[Contact_Referred_Company] nocheck constraint [LNK_Contact_Referred_Company]
GO

ALTER TABLE [dbo].[Contact_ReportsTo_Contact] ADD 
	CONSTRAINT [LNK_Contact_ReportsTo_Contact] FOREIGN KEY 
	(
		[GID_Contact2]
	) REFERENCES [dbo].[Contact] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Contact_Supervises_Contact] FOREIGN KEY 
	(
		[GID_Contact]
	) REFERENCES [dbo].[Contact] (
		[GID_ID]
	) NOT FOR REPLICATION 
GO

alter table [dbo].[Contact_ReportsTo_Contact] nocheck constraint [LNK_Contact_ReportsTo_Contact]
GO

alter table [dbo].[Contact_ReportsTo_Contact] nocheck constraint [LNK_Contact_Supervises_Contact]
GO

ALTER TABLE [dbo].[Contact_ReferredBy_Company] ADD 
	CONSTRAINT [LNK_Company_Referred_Contact] FOREIGN KEY 
	(
		[GID_Contact]
	) REFERENCES [dbo].[Contact] (
		[GID_ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [LNK_Contact_ReferredBy_Company] FOREIGN KEY 
	(
		[GID_Company]
	) REFERENCES [dbo].[Company] (
		[GID_ID]
	) NOT FOR REPLICATION 
GO

alter table [dbo].[Contact_ReferredBy_Company] nocheck constraint [LNK_Company_Referred_Contact]
GO

alter table [dbo].[Contact_ReferredBy_Company] nocheck constraint [LNK_Contact_ReferredBy_Company]
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-15 : 19:19:27

Depends on how they are used.
I would expect that it's better for the name to be clustered but it depends on the system.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2005-11-16 : 01:14:53

"Checkboxes are tiny ints so that they can be used in indexes set on multiple checkboxes"

Not sure about this ... you don;t appear to have any indexes on any of you CHK columns, and given that they will only have column values of 0 / 1 they won't be selective, so the indexes won't be used - unless you are planning to append them to indexes that have other columns too (to improve selectivity)

CREATE CLUSTERED INDEX [IX_Company_CompanyName] ON [dbo].[Company]([TXT_CompanyName], [TXT_CustCode]) ON [PRIMARY]

Presumably you either have a WHERE clause with both Company Name AND Customer Code - or you do
SELECT TXT_CustCode FROM dbo.Company WHERE TXT_CompanyName = 'MyValue'
(no other columns used, but the equivalent in a JOIN would qualify) to justify having the TXT_CustCode in the index?

Same with TXT_ContactCode on the end of the clustered index on Contact table.

Next thing that concerns me is that you have very "wide" clustered indexes on Company and Contact AND you have lots of (normal) indexes on those tables too. The index entries will therefore be huge - very few will fit on a page and it will use lots of disk space. I wonder therefore whether this is the best choice of clustered index?

Other more learned folk will know better than I do!

Why have you got "nocheck constraint" on all)/) of your FKs?

I presume you have thought about your use of GUIDs instead of INTs for your PKs? This is very costly (compared to IDENTITY columns). (But maybe there is no other way to allocate INT PKs using merge replication?)

1. GUID is 16 bytes instead of 4 for INT - so you only get 1/4 as many entries [or something like that!] in an index page
2. It greatly fragments your primary key indexes

Kristen

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-16 : 18:31:23

Reply to Kristen:

1. On checkboxes as tiny ints:

We have some cases in which the values of several checkboxes and selection lists are highly selective. One example that comes to mind is billable activities - only 1-5% of total activity records are billable. The next checkbox is 'Billed'. One query is all billable unbilled activities. There are typically very few unbilled activities. Add to that a Type selection list to see only 'support' billable unbilled activities.

The point is to structure checkboxes so that they can be indexed. The schema is managed by a tool, so a user can create a checkbox and can decide to index it.

2. CREATE CLUSTERED INDEX [IX_Company_CompanyName] ON [dbo].[Company]([TXT_CompanyName], [TXT_CustCode]) ON [PRIMARY]

>>Presumably you either have a WHERE clause with both Company Name AND Customer Code<<

Yes, the name doesn't guarantee uniqueness and we do WHERE clauses like this. I thought the index would also help sorting by company name and code. This is needed in our current system, does SS benefit from composite indices for sorting purposes?

3. Next thing that concerns me is that you have very "wide" clustered indexes on Company and Contact AND you have lots of (normal) indexes on those tables too. The index entries will therefore be huge - very few will fit on a page and it will use lots of disk space. I wonder therefore whether this is the best choice of clustered index?

I don't know. We just loaded some 61000 Contacts and 48000 Companies with links and my first impressions are that performance is very good. Of course, I don't know what I am looking at. How can I get a set of statistics on the execution plan that I could post here, for ex.? I enabled all Query>Show... menus in QA.

4. Why have you got "nocheck constraint" on all)/) of your FKs?

So that we can populate foreign key IDs during import without having the corresponding records there. When the foreign table is imported, the records exist. Basically, the links are defined just for charting purposes. Is there a better way of accomplishing this?

5. I presume you have thought about your use of GUIDs instead of INTs for your PKs? This is very costly (compared to IDENTITY columns). (But maybe there is no other way to allocate INT PKs using merge replication?)

Using identity, we'd have to allocate an increment like 1000, which would still limit us forever in the no of replicated clients (although heavens help you if you have a 1000 clients replicating). Even with an increment as small as 250, we'd need bigint, which is 8 bytes. Also, our application assumes global uniqueness when dealing with IDs, so GUIDs are pretty much a requirement. For all the overhead, GUIDs are a wonderfully liberating concept. We've had customers who merged their databases, for example. This was just a matter of importing the data - IDs didn't have to change.

You brought up a point about fragmentation of PK indexes. How would that be exhibited, in declining read performance with time, poor insert performance, or something else? It's interesting that COMBs caused such little degradation in performance for Jimmy Nilsson.

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 00:33:12

"We have some cases in which the values of several checkboxes and selection lists are highly selective"

OK, I'd be interested to know if the Query Plan actually uses those indexes in reality. Hopefully it will, otherwise switch back to BIT and discard the indexes.

The other thing you might do <shudder!> is to put a VIEW on the table, and index that:

CREATE VIEW MyView
AS
SELECT *
FROM MyTable
WHERE MyIsBilledFlag = 0

However, having an indexed view on a table raises other complications in my experience, so we've tended to avoid them to make our lives simpler! I believe that the Enterprise Edition of SQL Server will use the Index from the View on queries on the underlying table. Non E.E. version would need to explicitly query the VIEW rather than the TABLE to get the benefit.

"So that we can populate foreign key IDs during import without having the corresponding records there"

I would create the FKs after the data was imported ...

"... defined just for charting purposes"

... because I don't use the charting tools! Fair enough.

"Index size"

What is the ratio of "Data" to "Index" like (compared to other tables):

EXEC Sp_SpaceUsed 'Company'

"How can I get a set of statistics on the execution plan that I could post here"

I use this:


-- Comment in the SHOWPLAN to see the Query Plan, OR the STATISTICS to see the logical stats
-- SET SHOWPLAN_TEXT ON
GO
-- SET STATISTICS IO ON; SET STATISTICS TIME ON

-- ... put query here - e.g. :

SELECT * FROM Northwind.dbo.Products

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SET SHOWPLAN_TEXT OFF
GO

"How would that be exhibited, in declining read performance with time, poor insert performance"

Both. Solvable with good re-indexing maintenance procedures, but they will go out of shape again (whereas INTs don't). You probably won't want to set your FILL FACTOR to 100% (again, with INTs you could), so inherently fewer keys/index page.

"interesting that COMBs caused such little degradation in performance for Jimmy Nilsson"

COMBs are essentially ascending-sequence though, aren't they?

There is a new ascending-sequence style GUID in SQL20005 I believe.

"I thought the index would also help sorting by company name and code"

I don't think SQL Server will use an Index for the ORDER BY if indexes were already used for the WHERE clause etc. But I'm sure someone will enlighten me if I've got that wrong! The fact that its the clustered index may be relevant in your case.

Kristen

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-18 : 22:23:26

How can I see what the query plan uses? With SET SHOWPLAN/STATISTICS?

>>I would create the FKs after the data was imported ...<<

Great idea but we don't really have control over what the customer will do. Once in production, data can be imported and reimported many times (merged with existing data with duplicate resolution, etc.). When we do it, we could drop the FK, import, then recreate it.

>>What is the ratio of "Data" to "Index" like (compared to other tables):<<

Company: rows 48936, reserved: 84408 KB, data 56152 KB, index_size: 27704 KB, unused: 552 KB.

Contact: rows 61743, reserved 102344 KB, data 85912 KB, index_size 16192 KB, unused 240 KB.

Thanks for the tip about Sp_SpaceUsed and SET SHOWPLAN/STATISTICS.

>>You probably won't want to set your FILL FACTOR to 100% (again, with INTs you could), so inherently fewer keys/index page.<<

I left it at 0. From what I read, it should be somewhere around 50% for this type of index?

>>COMBs are essentially ascending-sequence though, aren't they?<<

Only in the trailing bytes. First 10 bytes are random, taken from uniqueidentifier.

>>There is a new ascending-sequence style GUID in SQL20005 I believe.<<

I'll check it out when I get there.

Thanks again for your comments and tips.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources