SQL Order of Operations! You’re not the Doctor.

The SELECT statement you’re running isn’t what it’s cracked up to be. See how SQL really sees your query. This is the SQL Order of Operation.

Batch Delete

Before you go deleting millions of records DON’T! You need to watch this video to understand why you should not delete records in one full swoop but rather in blocks.

Batch Insert

Inserting millions of rows will take a tow on the transaction log. When you’re inserting this much data, your log file will quickly grow and will have potentials to crash your database. To help relieve the pressure of the log file, you’ll want to insert in batches (this is something that is done in SSIS).

Alter Schema

Why should you use many schemas?

Union vs Union All

So what’s the big difference between Union and Union All? Let’s take a look.

Useful Basic Functions

Here’s a short video on using some basic MS SQL Functions

Alter Schema

Schema’s are use to help isolate users to a specific schema, ETL purposes, reusing a table name that already exists, and etc. reasons. In this video, I’ll show you how to import data into a table. Once the data is ready an Alter Schema is performed to prevent any downtime of the table.

OraOLEDB.Oracle Linked Server

Do you want to use Oracle’s driver to directly connect to an Oracle database instead of using ODBC (DSN)? Is it any faster? You be the judge!

Step 1

Make sure you have Oracle 12g drivers installed on the server. This could be any version of Oracle

Step 2

Make a note as to where your Oracle installation path is. You’ll need a few information from the TNSADMIN.ora file. For example, my path is C:\Oracle\Ora12\Network\Admin\tnsadmin.ora

Inside tnsadmin.ora, you’ll need the HOST, PORT, and SERVICE_NAME

Step 3

In SSMS, navigate to the OraOLEDB.Oracle providers and open up the properties

Enable Allow inprocess

Step 4

Create a Linked Server

1. Linked Server: Call this Linked Server whatever you want
2. Provider: Select Oracle Provider for OLEDB
3. Product name: Call this whatever you want
4. Data source: This information comes from Step 2 in this format HOST:PORT/SERVICE_NAME i.e.(My_Host.afterowl.com:1571/My_Service_Name.afterowl.com)
   
5.  Click on the Security tab on the left and use Be made using the security context. Then click OK to create the Linked Server

Step 5

This is how you would use your new shiny Oracle Linked Server

SELECT * FROM OPENQUERY(“ORACLE”, ‘SELECT * FROM MyTable’) AS MyTable

In Recovery Status

Sometimes a very large database (VLF) stalls and or takes forever and a day to perform a 2 minutes task. In these fun times we restart the server. It gets funner when the server is back online and your database is IN RECOVERY.

This generally happens prior to the server restart, you still have large transactions running (i.e. millions/billions or records being DELETE/UPDATE/INSERT).

To check on the status of your database simply run

EXEC sys.sp_readerrorlog 0, 1, ‘Your_Database_MDF_Name’

At this point, you can’t use sp_helpfile because your database is inaccessible therefore navigate to where the mdf file is located. Most of the time, the mdf file is the same name as your database.

When you run the command above, in your message tab you’ll see the percentage of completion and how many seconds to go.

 

WAITFOR it …

There are times when you want to delay a script such as sending yourself a reminders or to run a job/stored procedure/etc.

Wait by Time

BEGIN
             WAITFOR TIME ’01:10′;
PRINT ‘This message will print at 1:10 am’;
END;

Wait by Delay

BEGIN
             WAITFOR DELAY ’01:00′;
PRINT ‘This message will print in one hour’;
END;