Selecting a running position of the returned row in MSSQL

For some reason this concept is hard to summarize in one line. Basically we’re looking to return how many rows are in the table before the row returned in the result. Sort of like what position the rows is in line. If we have a table like this: Row: 1,2,3 Row: 2,4,5 Row: 3,6,7 And we want to find out the position of row 2, then it’s fairly simple because the primary key will tell us what order it is. However, if you delete row 1,...

Read More

MySQL Update Row if Key Exists

Many times you have to do a query before you do a write action to a database because you have to see whether or not a row exists for the data you want to put in. If it does exist, you’d then do another query that was an update, otherwise you’d have to do an insert. Well if you want a quicker way, and you don’t need to use operators like “Update `column`+1″, then “REPLACE INTO” is the function for you. ...

Read More

MySQL Limiting Left Joins

Sometimes it’s nice to just use a left join to see if a column exists. That’s what left joins are for. Your base query will still return rows even if there are no rows in the joined table(s). However, what if there is a one to many relationship between the base table and the joined table, that is to say that for any row in the base table there could be any number of rows in the joined table. If you have more than one row in the...

Read More

MySQL Replace function

I don’t recommend over using the REPLACE() function in MySQL.  It completely destroys performance.  However, sometimes, either by poor design, or just oversight, you have to replace something in order to make a join work, or the application code work.  REPLACE() is case-sensitive. The syntax is pretty simple: REPLACE(`field`, ‘find’, ‘replacewith’) Example: Fruit Values: Apple, Orange, Pear,...

Read More

Testing a replicated backup system

Testing a replicated backup system

If you have a high need for availability for a production application, but don’t have the money to invest in full fault tolerance networking, machinery, and access, you’ll need to have some sort of realtime backup system in place should your primary environment go down. This poses the issue of how do you keep one system on standbye, but be sure that it’s ready to go at all times. The image below shows one possible setup. The...

Read More