Follow @endyourif rss Facebook LinkedIn

Add a column with a default value to an existing table in SQL Server

Published on Jan 29, 2020

For any given project that has been existence for any amount of time, at some point your table design will change and you will need to add a new column to your SQL table. More specifically, this value should be NOT NULL so you want/need to set a default value when adding the new column.

Delete duplicate rows with MySQL or MSSQL

Published on Feb 27, 2019

The following article will provide a single SQL query that will delete rows that are duplicated in your table. Before you begin, be sure you've tracked down the bad code that is causing this; otherwise, you will need to run it multiple times!

To start, I will create a basic table structure that will help understand the SQL query:

Concatenate Multiple Rows From a Table Into One Field with MySQL

Published on Feb 17, 2019

When you want to merge multiple rows from a table into one field separated by a specific character using MySQL.

By utilizing the existing GROUP_CONCAT function with MySQL, you can easily group multiple rows together into a single field result.

MySQL: OR versus UNION – which is faster?

Published on Mar 24, 2014

I must say, this came as a shock to me, until I looked at the results and thought about it for a minute.  Before jumping in, let me explain how and where I encountered this.  I was recently working on a project called Deja Scene – The actor to actor movie database.  The purpose of this site is to find two actors who have starred in multiple movies together.

Because the nature of the algorithm an actor can be either on the left side of the connection or the right side.

Let’s look at an example, if I search for George Clooney, I will find that he has 372 Deja Scene connections.  Here was the original query I used to retrieve the list of his connections:



SELECT *

FROM  `actor2_actors`

WHERE actor1_id =1

OR actor2_id =1



Seems pretty straight forward; however, I was seeing really slow performance and I couldn’t figure out why.  I created an index on both the actor1_id and the actor2_id, so I assumed Mysql was properly indexing this.  Boy was I wrong.  Look at the results when I use the EXPLAIN function:

Setting Up CakePHP with IIS

Published on Jun 11, 2013

I’ve found myself doing this several times over the past little while as I had to switch computers.  Each time I would struggle with a few of the steps, so I thought I would document them this time and share for future me (or you if you like).

Mysql: Insert or Update in One SQL Statement

Published on Oct 3, 2012

Regular Expressions with Mysql

Published on Oct 2, 2012

Mysql Uptime Check Script

Published on Oct 1, 2012

Renaming a Database in Mysql

Published on Sep 28, 2012

Load Testing Mysql with mysqlslap

Published on Sep 17, 2012

Update: It Actually Should Be LEPP Not LEMP or LAMP!

Published on Sep 14, 2012