Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

Overview

Sample script for applying indexation to a MSSQL implementation of FarCry.

Copied from http://www.coldfusionmuse.com/files/farcry_MSSQL_indexing.html

ALTER TABLE [dbo].[categories] WITH NOCHECK ADD 
	CONSTRAINT [PK_categories] PRIMARY KEY  CLUSTERED 
	(
		[categoryID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[container] WITH NOCHECK ADD 
	CONSTRAINT [PK_container] PRIMARY KEY  CLUSTERED 
	(
		[objectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[container_aRules] WITH NOCHECK ADD 
	CONSTRAINT [PK_container_aRules] PRIMARY KEY  CLUSTERED 
	(
		[data],
		[objectid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dmEvent_aObjectIDs] WITH NOCHECK ADD 
	CONSTRAINT [PK_dmEvent_aObjectIDs] PRIMARY KEY  CLUSTERED 
	(
		[data],
		[objectid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dmExternalGroupToPolicyGroup] WITH NOCHECK ADD 
	CONSTRAINT [PK_dmExternalGroupToPolicyGroup] PRIMARY KEY  CLUSTERED 
	(
		[PolicyGroupId],
		[ExternalGroupUserDirectory],
		[ExternalGroupName]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dmHTML_aObjectIDs] WITH NOCHECK ADD 
	CONSTRAINT [PK_dmHTML_aObjectIDs] PRIMARY KEY  CLUSTERED 
	(
		[data],
		[objectid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dmHTML_aRelatedIDs] WITH NOCHECK ADD 
	CONSTRAINT [PK_dmHTML_aRelatedIDs] PRIMARY KEY  CLUSTERED 
	(
		[data],
		[objectid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dmNavigation_aObjectIDs] WITH NOCHECK ADD 
	CONSTRAINT [PK_dmNavigation_aObjectIDs] PRIMARY KEY  CLUSTERED 
	(
		[data],
		[objectid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dmNews_aObjectIds] WITH NOCHECK ADD 
	CONSTRAINT [PK_dmNews_aObjectIds] PRIMARY KEY  CLUSTERED 
	(
		[data],
		[objectid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dmNews_aRelatedIDs] WITH NOCHECK ADD 
	CONSTRAINT [PK_dmNews_aRelatedIDs] PRIMARY KEY  CLUSTERED 
	(
		[data],
		[objectid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[refCategories] WITH NOCHECK ADD 
	CONSTRAINT [PK_refCategories] PRIMARY KEY  CLUSTERED 
	(
		[categoryid],
		[objectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[refContainers] WITH NOCHECK ADD 
	CONSTRAINT [PK_refContainers] PRIMARY KEY  CLUSTERED 
	(
		[objectid],
		[containerid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[refObjects] WITH NOCHECK ADD 
	CONSTRAINT [PK_refObjects] PRIMARY KEY  CLUSTERED 
	(
		[objectid],
		[typename]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ruleChildLinks] WITH NOCHECK ADD 
	CONSTRAINT [PK_ruleChildLinks] PRIMARY KEY  CLUSTERED 
	(
		[objectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ruleEvents] WITH NOCHECK ADD 
	CONSTRAINT [PK_ruleEvents] PRIMARY KEY  CLUSTERED 
	(
		[objectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ruleEventsCalendar] WITH NOCHECK ADD 
	CONSTRAINT [PK_ruleEventsCalendar] PRIMARY KEY  CLUSTERED 
	(
		[objectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ruleHandpicked] WITH NOCHECK ADD 
	CONSTRAINT [PK_ruleHandpicked] PRIMARY KEY  CLUSTERED 
	(
		[objectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ruleLinks] WITH NOCHECK ADD 
	CONSTRAINT [PK_ruleLinks] PRIMARY KEY  CLUSTERED 
	(
		[objectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ruleNews] WITH NOCHECK ADD 
	CONSTRAINT [PK_ruleNews] PRIMARY KEY  CLUSTERED 
	(
		[objectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ruleRandomFact] WITH NOCHECK ADD 
	CONSTRAINT [PK_ruleRandomFact] PRIMARY KEY  CLUSTERED 
	(
		[objectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ruleText] WITH NOCHECK ADD 
	CONSTRAINT [PK_ruleText] PRIMARY KEY  CLUSTERED 
	(
		[objectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ruleXMLFeed] WITH NOCHECK ADD 
	CONSTRAINT [PK_ruleXMLFeed] PRIMARY KEY  CLUSTERED 
	(
		[objectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[statsSearch] WITH NOCHECK ADD 
	CONSTRAINT [PK_statsSearch] PRIMARY KEY  CLUSTERED 
	(
		[logId]
	)  ON [PRIMARY] 
GO


ALTER TABLE [dbo].[dmFacts] ADD 
	CONSTRAINT [PK_dmFacts] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[config] ADD 
	CONSTRAINT [PK_config] PRIMARY KEY  NONCLUSTERED 
	(
		[configName]
	)  ON [PRIMARY] 
GO

 CREATE  CLUSTERED  INDEX [inx_facts_datetime] ON [dbo].[dmFacts]([datetimecreated], [datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_Arch_lastupdated] ON [dbo].[dmArchive]([datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_css_datetime] ON [dbo].[dmCSS]([datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_Cron_dateupdated] ON [dbo].[dmCron]([datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_email_datetime] ON [dbo].[dmEmail]([datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_event_datetime] ON [dbo].[dmEvent]([datetimecreated], [datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_file_datetime] ON [dbo].[dmFile]([datetimecreated], [datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_flash_datetime] ON [dbo].[dmFlash]([datetimecreated], [datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_html_datetime] ON [dbo].[dmHTML]([datetimecreated], [datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_image_datetime] ON [dbo].[dmImage]([datetimecreated], [datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_include_datetime] ON [dbo].[dmInclude]([datetimecreated], [datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_link_datetime] ON [dbo].[dmLink]([datetimecreated], [datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_navigation_datetime] ON [dbo].[dmNavigation]([datetimecreated], [datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_news_datetime] ON [dbo].[dmNews]([datetimecreated], [datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_profile_datetime] ON [dbo].[dmProfile]([datetimecreated], [datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_redirect_datetime] ON [dbo].[dmRedirect]([datetimecreated], [datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_XML_datetime] ON [dbo].[dmXMLExport]([datetimecreated], [datetimelastupdated]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_audit_objectid] ON [dbo].[fqAudit]([objectid]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [ix_nto] ON [dbo].[nested_tree_objects]([nLeft], [nRight]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [inx_friendlyUrl] ON [dbo].[reffriendlyURL]([friendlyurl]) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [stats0] ON [dbo].[stats]([pageid], [logDateTime]) ON [PRIMARY]
GO


 CREATE  INDEX [inx_facts_title] ON [dbo].[dmFacts]([title]) ON [PRIMARY]
GO

 CREATE  INDEX [inx_container_label] ON [dbo].[container]([label]) ON [PRIMARY]
GO

 CREATE  INDEX [inx_aRules_data] ON [dbo].[container_aRules]([data]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dmArchive] ADD 
	CONSTRAINT [PK_dmArchive] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dmCSS] ADD 
	CONSTRAINT [PK_dmCSS] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_css_title] ON [dbo].[dmCSS]([title]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dmCron] ADD 
	CONSTRAINT [PK_dmCron] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_chron_title] ON [dbo].[dmCron]([title]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dmEmail] ADD 
	CONSTRAINT [PK_dmEmail] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_email_title] ON [dbo].[dmEmail]([Title]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dmEvent] ADD 
	CONSTRAINT [PK_dmEvent] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_event_title] ON [dbo].[dmEvent]([Title]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dmFile] ADD 
	CONSTRAINT [PK_dmFile] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_file_title] ON [dbo].[dmFile]([title]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dmFlash] ADD 
	CONSTRAINT [PK_dmFlash] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_flash_title] ON [dbo].[dmFlash]([Title]) ON [PRIMARY]
GO

 CREATE  UNIQUE  INDEX [idx_groupId] ON [dbo].[dmGroup]([groupId]) ON [PRIMARY]
GO

 CREATE  UNIQUE  INDEX [idx_groupName] ON [dbo].[dmGroup]([groupName]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dmHTML] ADD 
	CONSTRAINT [PK_dmHTML] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_html_title] ON [dbo].[dmHTML]([Title]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dmImage] ADD 
	CONSTRAINT [PK_dmImage] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_image_title] ON [dbo].[dmImage]([title]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dmInclude] ADD 
	CONSTRAINT [PK_dmInclude] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_include_title] ON [dbo].[dmInclude]([title]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dmLink] ADD 
	CONSTRAINT [PK_dmLink] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_link_title] ON [dbo].[dmLink]([title]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dmNavigation] ADD 
	CONSTRAINT [PK_dmNavigation] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_navigation_title] ON [dbo].[dmNavigation]([title]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dmNews] ADD 
	CONSTRAINT [PK_dmNews] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_news_title] ON [dbo].[dmNews]([Title]) ON [PRIMARY]
GO


 CREATE  UNIQUE  INDEX [idx_Permission] ON [dbo].[dmPermission]([PermissionName], [PermissionType]) ON [PRIMARY]
GO

 CREATE  UNIQUE  INDEX [idx_PermissionId] ON [dbo].[dmPermission]([PermissionId]) ON [PRIMARY]
GO

 CREATE  UNIQUE  INDEX [idx_Barnacle] ON [dbo].[dmPermissionBarnacle]([PermissionId], [Reference1], [PolicyGroupId]) ON [PRIMARY]
GO

 CREATE  UNIQUE  INDEX [idx_PolicyGroupId] ON [dbo].[dmPolicyGroup]([PolicyGroupId]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dmProfile] ADD 
	CONSTRAINT [PK_dmProfile] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dmRedirect] ADD 
	CONSTRAINT [PK_dmRedirect] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

 CREATE  UNIQUE  INDEX [idx_UserId] ON [dbo].[dmUser]([userId]) ON [PRIMARY]
GO

 CREATE  UNIQUE  INDEX [idx_UserLogin] ON [dbo].[dmUser]([userLogin]) ON [PRIMARY]
GO

 CREATE  UNIQUE  INDEX [idx_userToGroup] ON [dbo].[dmUserToGroup]([userId], [groupId]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dmXMLExport] ADD 
	CONSTRAINT [PK_dmXMLExport] PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_xml_title] ON [dbo].[dmXMLExport]([title]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[fqAudit] ADD 
	CONSTRAINT [PK_fqAudit] PRIMARY KEY  NONCLUSTERED 
	(
		[AuditID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[nested_tree_objects] ADD 
	 PRIMARY KEY  NONCLUSTERED 
	(
		[ObjectID]
	)  ON [PRIMARY] ,
	 CHECK ([nLeft] < [nRight])
GO

 CREATE  INDEX [inx_nestedtreeobj_parentid] ON [dbo].[nested_tree_objects]([ParentID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[reffriendlyURL] ADD 
	CONSTRAINT [PK_reffriendlyURL] PRIMARY KEY  NONCLUSTERED 
	(
		[objectid],
		[refobjectid]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_ruleChildLinks_label] ON [dbo].[ruleChildLinks]([label]) ON [PRIMARY]
GO


 CREATE  INDEX [inx_ruleEvents_label] ON [dbo].[ruleEvents]([label]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[stats] ADD 
	CONSTRAINT [PK_stats] PRIMARY KEY  NONCLUSTERED 
	(
		[logId]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [inx_stats_navid] ON [dbo].[stats]([navid]) ON [PRIMARY]
GO

 CREATE  INDEX [statsSearch0] ON [dbo].[statsSearch]([searchString], [logDateTime]) ON [PRIMARY]
GO
  • No labels