If you have a query and need to calculate the difference between two dates (or datetimes) using the standard comparison operators (<, >, ==, !=, <=, and >=) is the best way to do it. Using standard comparison operators gives the optimizer the best chance to use an index on the date column. In contrast,...
Routinely checking your systems is a critical part of your daily job as a DBA. Often times these simple checks can be enough to catch something bad before it becomes something really bad. Depending on your environment, your DBA’s Checklist should include (but is not limited to): 1) Backups Check your backups by checking backup...Continue reading
Today, I had a nice challenge, how to bulk rename column names in a database. The issue was I needed to move few tables from development environment to production environment and since some of the tables were imported from other environments, several column names needed to be standardized before the transfer. To better illustrate this...Continue reading
Recently, after upgrading an SQL Server instance to SQL Server 2016, our developers noticed that the application was not functioning properly. To fix this we needed to roll back the upgrade by downgrading the SQL Server database to SQL Server 2014. Unfortunately for us, SQL Server does not allow you to restore or attach a...
I recently built a SQL Server 2016 production database from a development database. Unfortunately, the database diagrams that had been created in the development database were lost. Since the new and the old database both contain the same tables and schema objects, I decided to copy database diagrams from one database to another. Sysdiagrams is a table...Continue reading
When deciding whether to use sp_who or sp_who2, check the table below for a detailed comparison list. The main difference between the two commands is this: sp_who is documented and officially supported. sp_who2 is undocumented and therefore unsupported but commonly used. As you can see on the comparison table below, they both basically return the same...Continue reading
If you are trying to attach a database and you only have the database file (.mdf) and not the transaction log file (.ldf) the steps are fairly similar to what they were in my previous blog – How to attach database using different methods in SQL Server Instance. Just like when attaching a database with transaction log...Continue reading
Today I needed to transfer one of our development databases to another development environment. I had two choices: I could make a full backup/restore and drop the database since we did not want the same database stay on both servers or I could use the detach/attach database method. In order to eliminate some steps I decided to...Continue reading