There are a couple different ways to combine data from different tables. The most common method is an INNER JOIN (shorthand is JOIN) on two rows for where they match up. This will give you an intersection of the two tables. It can be used for one-to-one matching or one-to-many. A LEFT and a RIGHT JOIN will give you the same intersection, but also every record that doesn’t match for whichever side of the JOIN expression you are specifying....

Most queries return a handful of rows by filtering on a couple columns. A non-clustered index duplicates those columns you are filtering on into their own separate data structure, where it keeps these columns sorted (that’s the secret to knowing where things are at). Now your query can read a subset of your total data size to get what you need. This makes things very fast when compared to using the basic storage you would get in a clustered index (see T-SQL Tip 4)....

You should almost always have a clustered index. Each table gets one clustered index, but they do a whole lot by organizing it into an order for storage. Define how the engine interacts with storage Make updates, deletes, and creating indexes faster Enable deletes to free up table space (This allows your selects to skip this empty space) Prevent stored pages from “overflowing” and creating pointers to the overflow Allow for the raw data to be seeked into instead of scanned in entirety All selects will perform at least as good if not better

NOLOCK is poorly named. You would think it means it doesn’t take locks, but what it really does is ignore other locks. NOLOCK is a shorthand way of using the Read Uncommitted isolation level. This is better named. You can and will read uncommitted records with NOLOCK. This can cause results to be inaccurate because the uncommitted records may ultimately never be committed. Meanwhile NOLOCK can block additional writes from happening because it takes a lock…

Appropriately sized datatypes are going to be crucial for running SQL queries efficiently. If you don’t want your database to be full of 💩 and other emojis you’ll want to put down the nvarchar(max). When SQL Server sees your query is using column with a max datatype, it wants to allocate 4KB of RAM per row, and that’s going to add up quick. On top of that, you can’t index a max column....