CODEWITHSUNDEEP
codefluent
Marc Bremmers
Marc Bremmers

Reputation: 21

Codefluent SQL Server producer

We are using the SQL Server producer. We want an index for each foreign key column. SQL Server does not put indexes onto foreign key columns automatically. How can we create an index for each foreign key column automatically? Should we code an aspect for this?

Upvotes: 2

Views: 119

Answers (1)

meziantou
meziantou

Reputation: 21347

CodeFluent Entities does not generate indices by default. However you can set index="true" on a property:

<cf:property name="Customer" index="true" />

And use the SQL Server Template Engine and the template provided by CodeFluent Entities "C:\Program Files (x86)\SoftFluent\CodeFluent\Modeler\Templates\SqlServer\[Template]CreateIndexes.sql" to create indices.

If you don't want to add index=true on each property, you can change the template to automatically include all properties, or you can write an aspect to add the attribute (this is more complex).

Another solution is to use a SQL script:

DECLARE @SQL NVARCHAR(max) 
SET @SQL = '' 

SELECT @SQL = @SQL +
    'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[' + tab.name + ']'') AND name = N''IX_' + cols.name + ''')' + CHAR(13)+CHAR(10) + 
    'CREATE NONCLUSTERED INDEX [IX_' + cols.name + '] ON [dbo].[' + tab.name + ']( [' + cols.name + '] ASC ) ON [PRIMARY];' + CHAR(13)+CHAR(10)
FROM sys.foreign_keys keys
INNER JOIN sys.foreign_key_columns keyCols ON keys.object_id = keyCols.constraint_object_id
INNER JOIN sys.columns cols ON keyCols.parent_object_id = cols.object_id AND keyCols.parent_column_id = cols.column_id
INNER JOIN sys.tables tab ON keyCols.parent_object_id = tab.object_id
ORDER BY tab.name, cols.name

EXEC(@SQL)

Upvotes: 1

Related Questions