Is there a version control system for database structure changes? I often run into the following problem. I work on some changes to a project that require new tables or columns in the database. I make the database modifications and continue my work. Usually, I remember to write down the changes so that they can be replicated on the live system. However, I don't always remember what I've changed and I don't always remember to write it down. So, I make a push

Check for changes to an SQL Server table? How can I monitor an SQL Server database for changes to a table without using triggers or modifying the structure of the database in any way? My preferred programming environment is .NET and C#. I'd like to be able to support any SQL Server 2000 SP4 or newer. My application is a bolt-on data visualization for another company's product. Our customer base is in the thousands, so I don't want to have to put in requirements that we modify

Flat File Databases What are your best practices around creating flat file database structures in PHP? A lot of the more mature PHP flat file frameworks I see out there attempt to implement SQL-like query syntax, which is over the top for my purposes in most cases (I would just use a database at that point). Are there any elegant tricks out there to get good performance and features with the small code overhead one would want by taking on this problem in

How does database indexing work? Given that indexing is so important as your data set increases in size, can someone explain how does indexing works at a database agnostic level? For information on queries to index a field, check out How do I index a database column. Solution Why is it needed? When data is stored on disk based storage devices, it is stored as blocks of data. These blocks are accessed in their entirety, making them the atomic disk access operation. Disk blocks

How do you list all triggers in a MySQL Database? What is the command to list all triggers in a MySQL database? Answer :  The command is: show triggers or you can access the INFORMATION_SCHEMA table directly by: select trigger_schema, trigger_name, action_statement from information_schema.triggers You can do this from version 5.0.10 onwards. More information about the TRIGGERS table is here. Source

How do I quickly rename a MySQL database (change schema name)? The MySQL manual at MySQL covers this. Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does bad things, exist only in a handful of versions, and is a bad idea overall. This needs to work with InnoDB, which stores things very differently than MyISAM. Answer : For InnoDB, the following seems to work: create the new

How to select the nth row in a SQL database table? I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server MySQL PostgreSQL SQLite Oracle I am currently doing something like the following in SQL Server 2005, but I'd be interested in seeing other's more agnostic approaches: WITH Ordered AS ( SELECT ROW_NUMBER() OVER