Last tip, T-SQL Tip 12, was on temp tables. Another object that will allow you to hold onto and reuse results is a table variable. These are created with an @ symbol and are created in a declare statement, @ThisIsAVariable. They only persist as long as the batch they are declared in. If you store the results of a query in a table variable, you can use that variable for further queries in your batch....

SQL Server lets you build temporary tables using the same code as a normal table. These are created using a # sign in front of the table name, #ThisIsTemporary. These only stick around as long as the session they are created in. If you use two ## signs it is created globally and other sessions can see the table with the name specified. These are a great way to help SQL Server do a single workload one time instead of repeatedly....

I may have misled you a bit. Every tip so far, and probably in the future too, has exceptions. SELECT * is bad….. unless you don’t know what’s in the table yet… Every table should have a clustered index and primary key….. unless it’s temporary table… Scans are better than seeks….. unless you want a lot of ordered data… As our topics grow more complex, the space for exceptions to the rules will grow larger....

Let’s combine everything we’ve covered lately to make a new table. This new table should have a clustered index, and the most likely clustered index would be an IDENTITY column using int or bigint. Given its inherent uniqueness it will also make a great primary key. For the other columns, we should double think any max datatype columns. Try to instead store these in smaller chunks, or as-is with a smaller datatype....

We talked about why you should use a clustered index in T-SQL Tip 4. Now let’s talk about what to make the clustered index. Here’s an acronym for things that make a good clustered index: One Column: Small data footprint, generally not strings, one column Unique: Something that by design won’t repeat Increasing. You won’t put a row in the middle of what already exists Immutable: Unchanging, and not going to be updated It’s for those characteristics that I generally recommend an int or bigint IDENTITY column....