CategorySQL Server

Database recovery is a very important thing, especially to DBA’s, and the occasional developer who screws something up.  Please note, I am a developer and I would NEVER mess ANYTHING up in a database.  I treat all databases equally and with respect.  Anyway, we are getting off track.  There are three recovery models: Simple, Full and Bulk-Logged. Simple:  This method does not do log backups...

If you have ever wanted to query the SQL Server job tables then you know they are a little cryptic. The following query will assist you in getting some useful information out of those tables. I recently wanted to be able to query these tables and get the error for when there was a failure during the running of the job. This can be accomplished using this query and just adding another couple of...

CTEs are a great thing to use in your query.  They are an excellent way to consolidate code and make it readable as well as create a “view” inside of your code.  One great thing I have used them for many times was to pick out the max date associated with a set of key values.  So let’s say there is a table that has a column called ID, Quantity and Date.  And in this table there are duplicate IDs...

By default the functions MAX and MIN do not count NULL in their evaluation of your data.  If we have a column containing only dates for instance and there is a NULL date, MAX and MIN will both ignore that value.  For instance, take the following data set as an example. WorkID    StoreID       EndDate ———– ———–...

By default the functions MAX and MIN do not count NULL in their evaluation of your data.  If we have a column containing only dates for instance and there is a NULL date, MAX and MIN will both ignore that value.  For instance, take the following data set as an example. WorkID    StoreID       EndDate ———– ———–...

On a recent project that I was working on we came across an issue where we needed to replicate several databases on a new server.  Much of the ETL had places where tables were truncated and reloaded.  Normally this is not an issue at all, simply TRUNCATE TABLE and away you go.  This does however cause a problem when you have a replicated database.  According to the Microsoft documentation, which...

Recently while creating a script to migrate the new holding tables over to the stage and production machines I ran into a little issue.  The holding table we are using in this case houses several different sets of data for our migration project.  Tables that are used only for going from the old system to the new (which will be going away in a few months) and tables that will be used in the future...

There may be a day when you need to create your own comma delimited list from data contained in a SQL table.  Sure you could build a cursor and go through each row adding a comma after each record.  The good news is that there is an alternative.  The better news is that this method is easier, faster and takes MUCH less code.  COALESCE in SQL has many uses and building a list is one of them...