Use of SQL Indexes

Groupcall XVault will continue to operate if the SQL database has indexes created within it; you should exercise caution in your indexing policy to ensure that you do not create excessive or duplicate indexes.

The following SQL allows you to determine SQL indexes already present in the Groupcall XVault database, and Groupcall can provide a default index selection to further optimise the performance of most XVault installations.

SELECT 
	schema_name(schema_id) as SchemaName, OBJECT_NAME(si.object_id) as TableName, si.name as IndexName, 
	(CASE is_primary_key WHEN 1 THEN 'PK' ELSE '' END) as PK, 
	(CASE is_unique WHEN 1 THEN '1' ELSE '0' END)+' '+ 
	(CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' '+  -- B=basic, C=Clustered, X=XML 
	(CASE INDEXKEY_PROPERTY(si.object_id,index_id,1,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
	(CASE INDEXKEY_PROPERTY(si.object_id,index_id,2,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
	(CASE INDEXKEY_PROPERTY(si.object_id,index_id,3,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
	(CASE INDEXKEY_PROPERTY(si.object_id,index_id,4,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
	(CASE INDEXKEY_PROPERTY(si.object_id,index_id,5,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
	(CASE INDEXKEY_PROPERTY(si.object_id,index_id,6,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
	'' as 'Type', 
	INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,1) as Key1, 
	INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,2) as Key2, 
	INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,3) as Key3, 
	INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,4) as Key4, 
	INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,5) as Key5, 
	INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,6) as Key6 
FROM sys.indexes as si 
	LEFT JOIN sys.objects as so on so.object_id=si.object_id 
WHERE index_id>0 -- omit the default heap 
	and OBJECTPROPERTY(si.object_id,'IsMsShipped')=0 -- omit system tables 
	and not (schema_name(schema_id)='dbo' and OBJECT_NAME(si.object_id)='sysdiagrams') -- omit sysdiagrams
--  uncomment to exclude xv_* indexes
--  and si.name not like 'xv_%'
ORDER BY SchemaName,TableName,IndexName

In terms of the recommended assignment of indexes within XVault this will depend fully on how you query into it, clearly the focus of any additional indexes you add will be determined by which columns you filter or join upon during your daily use.