0

Would like to ask about using of schemas in Sql Server 2019. For now we are using numbers as schemas for procedures and functions:

0,1,2 etc. to version procedures and functions. For example [1].[GetFirstName] is the newest version of procedure but we have also node where we has to use [0].[GetFirstName] procedure (for example to compare efficiency between old and new).

For tables and views we are not using schemas but I am thinking about adding below:

  • conf - Configuration - for table which store confiuration data
  • dict - Dictionary - for table which store dictionaries
  • log - Logging - for table which store logs
  • proc - Processing - for tables which wille be use to massive updates and insert commands (~ 300 insert/updates per seconds)

My questions:

  • Do you think that using procedure version as schema is a good idea ?
  • Is it good idea to have different idea for schemas in tables/views (responsible of table) and in procedure/functions (versioning)

  • Do you think that using below schema preposition is a good idea ? What problems can I have in future in process of addministration/change on production ?

  • Do you have any idea (except schemas) how to name tables with config,dictionaries,processing ? Is it worth to divide them in groups by for example add substring at the end (StatusDict, EventDict, StatusConfig, EventConfig, TransactionProc) etc. ? I would add that I am using naming convenction from: SQL Server Name Convenction. Beacuse of naming tables like I describe above the ID for columns will be StatusDictID, EventDictID instead of StatusID, EventID.

Tank you in advance for answers.

3

My personal opinion is that it is not a good idea. I tend to think of the schema as a namespace, to logically group related things together.

Versioning should be handled (IMO) no different from other code, i.e. via a source control system. Changes to the data model should be handled via upgrades/migrations.

That aside, do you make a new copy of every procedure function when you change one procedure, or do you have an individual "latest schema" for every object? How does your code figure out what is the latest version of a particular procedure?

| improve this answer | |
  • Of course we will use source control system :) But we need both version of stored procecdures. Thats why my idea was to create versioning via schemas. According to your question - every new change in code of single procedure and new release we have to add also new version of every procedure (even If we does not change anything). We also make commitment to not execute procedure in procedure (to not complicate logic). – axdna Apr 6 at 14:22

Not the answer you're looking for? Browse other questions tagged or ask your own question.