I don't like having the same thing defined in two places, if I can avoid it. I realize the two queries below are dealing with two different tables, but those tables hold basically the same kind of data (distinct predicates warrant the two queries), and I think of the two projections below as "the same thing defined in two places". When...
Hi all I am in the process of optimising my database and I was thinking of changing the datatype for some columns from DATETIME to SMALLDATETIME on my tables. Is there a system stored procedure that returns both the contents/code of a store procedure and the dependent table which will then allow me to do a join on a filtered list of ta...
I work on a system that is based on a SQL server database that has a lot of years of development on it. It is not huge in data volume (a few GB) but it has a lot of complexity (hundreds of tables, hundreds of stored procedures). I want to start by cleaning out the stuff that isn't used any more. We have a weekly/monthly/quarterly/annual...
I'm interested in database refactoring. I deal with several databases that don't have a large amount of data, just a few GB with at most a few hundred thousand rows. However, they have hundreds -- sometimes many hundreds -- of tables, views, sprocs and functions. In some places a divide-and-rule strategy using schemas has been implemente...
Scott W. Ambler has put up a good list of basic database smells. It would be good to see how these can be corrected, what database refactorings can be applied as a remedy and if any smells are missing from the list. For "ground rules" when replying, take a look at question on programming smells if you please. ...
I was just wondering if a storage engine type existed that allowed you to do version control on row level contents. For instance, if I have a simple table with ID, name, value, and ID is the PK, I could see that row 354 started as (354, "zak", "test")v1 then was updated to be (354, "zak", "this is version 2 of the value")v2 , and could s...
In SQL Server, is there any way to check whether the changes in the schema will impact Stored Procedures (and/or Views)? For example a change of the column name in one table, may break some Stored Procedures; how to check the impacted stored procs? ...
I'm sure this might be quite common query but couldn't find good answer as for now. Here is my question: I've got a table named Contacts with varchar column Title. Now in the middle of development I want to replace field Title with TitleID which is foreign key to ContactTitles table. At the moment table Contacts has over 60 dependencie...
I'm looking for guidance on encrypting fields (and/or tables if possible) for MySQL. I will settle for a decent tutorial but I'd really like specific tips on managing the transition from an unencrypted schema to one utilizing encrypted fields. Thanks! ...
For a bit fall cleaning, I am moving 25 tables between MySQL databases (different pieces of hardware). This is not the WHOLE database, just 25 tables out of a few hundred... These tables don't really belong in there, I won't go into why for NDA reasons. Now, this is going to break a lot of code and sql queries. What is the best way t...
Backstory At work where we're planning on deprecating a Natural Key column in one of our primary tables. The project consists of 100+ applications that link to this table/column; 400+ stored procedures that reference this column directly; and a vast array of common tables between these applications that also reference this column. The ...