Suggestion: Allow rules to be written so junior DBA get assistance in schema design

[brettshearer]

Example : it is unlikely that adding extra key fields after a date/time column in a non-unique index will ever be used as part of the seek (they may end up being a predicate lookup though).

I currently have about 100 of these rules that I run on the SQL schema as part of an automated suite. If this can be done earlier (with codified rules in DBSchema) then an entire development iteration could be avoided. Override could be recorded in the designer.

with data as
(
    select 
        i.object_id, i.index_id, i.name, 
        min(iif(t.name in ('datetime', 'datetime2', 'datetimeoffset', 'date', 'smalldatetime'), key_ordinal, 999)) MinDatecolumn,
        max(key_ordinal) Maxcolumns 
    from sys.indexes i
    join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
    join sys.columns c on c.object_id = i.object_id and c.column_id = ic.column_id
    join sys.types t on t.user_type_id = c.user_type_id
    where is_included_column = 0 and is_unique = 0
    group by i.object_id, i.index_id, i.name
)
select * from data where MinDateColumn < 999 and Maxcolumns > MinDateColumn

[wise-coders]

I don't really understand the workflow of these rules. But we have the automation scripts ( see Help / Automation Scripts ), which you can use to implement custom code in DbSchema, with direct access to the API. Is this something that could help? Please send more details about these rules, just to understand the workflow.

[brettshearer]

These are schema design rules that guide the addition of indexes or constraints. An example would be that the majority of numeric values in a system actually should be constrained to disallow negative values (especially physical attributes such as height, length etc). I currently use a combination of automation (adding the constraint when the attribute is added) and unit tests (run before the Pull request is accepted).

What I was wondering is whether these sorts of rules could be run from within DBSchema, rather than outside.

[wise-coders]

Yes, the automation scripts I wrote about should cover these cases. Please evaluate them.

Best regards, Dragos Pruteanu