I have a question(s) in regards with database development in general.
In a development cycle structure with development, quality assurence, staging and production what is the best way of managing the sql code like table creation and alter scripts, views, functions and stored procedures scripts? Along with that what is the best way to promote this code through different environments from DEV to QA to STAGING and to PROD.
The problem with sql code is different than the application code where the it resides in files that are kept in a source control environment like SourceSafe or PerForce. In application the files are in a collection that is promoted and files saved in any order and they are versioned but with sql code we need to create scripts in a certain order and when promoted they have to be executed in the same chronological order. The way I did it so far is creating scripts named so that their alphabetical order is actually their chronological order and I used command lines utilities like osql for MS-SQL and mysql for MySQL.
Is there really need for a source control environment for the sql code as is already present in the DEV database unlike application code? We have a shared DEV and QA environments.
I know this is not a one answer question, actually they are several questions in one, but I would like to hear opinions.
Thank you in advance!