Reading Time: 2 minutes It is absolutely possible to create indexes within a table variable in Microsoft SQL Server. The key is to add the indexes as part of the table variable definition Take a look at this regular table definition (which does not have any indexes placed on it yet): CREATE TABLE Books Continue Reading
Indexes
SQL Server Filtered Indexes: Explained for Beginners
Reading Time: 5 minutes Filtered indexes in SQL Server can dramatically increase the efficiency of queries against data in your database. They are a fantastic optimization tool you should understand and remember during the design and development of tables in your database! In this brief tutorial, we’ll discuss what a filtered index is and Continue Reading
SQL Server Covering Indexes: Explained with Examples
Reading Time: 6 minutes Indexes are extremely important when it comes to writing queries that return data in an efficient way. We not only want to make sure our queries return exactly the information we need, but we also want that information to be returned to us quickly. In this brief tutorial, we’ll discuss the Continue Reading
SQL Server Key Lookup: Explained with Examples
Reading Time: 7 minutes The SQL Server “Key Lookup” operation is something you may see when looking at the execution plan of a query. Maybe you’re trying to optimize the query to run a bit faster and noticing that a lookup operation is taking a LARGE amount of the query processing time. Or maybe Continue Reading
UNIQUEIDENTIFIER: Explained for Beginners
Reading Time: 7 minutes There are several ways we can guarantee unique values in our primary key columns in our SQL Server tables. One data type we can use to store unique values is the UNIQUEIDENTIFER. In this very brief tutorial, we’ll talk about what the SQL Server UNIQUEIDENTIFIER data type is and how Continue Reading
SQL Server Unique Index: Everything you need to know
Reading Time: 8 minutes If you know a thing or two about Clustered indexes and Nonclustered indexes, you might be looking to “round out” the topic with an understanding of what a Unique index is. Unique indexes are a great way to enforce uniqueness in a column other than the Primary Key column. If Continue Reading
The B-Tree: How it works, and why you need to know
Reading Time: 11 minutes If you had to explain what a B-Tree is and how it works, could you do it? I mean, you know it’s a thing in SQL Server, and it is part of indexes (…somehow), but do you really know what it is? Wood you beleaf me if I told you Continue Reading
Nonclustered Index in SQL Server: A Beginner’s Guide
Reading Time: 10 minutes In this discussion, we’ll talk about the younger brother of the clustered index: The nonclustered index. In a separate discussion, we talked about how understanding and using clustered indexes is very helpful towards writing efficient queries in SQL Server. If you missed that discussion, you can find it here: Clustered Continue Reading
SQL Server Clustered Index: The Ultimate Guide for the Complete Beginner
Reading Time: 10 minutes Understanding how indexes work is a critical component to understanding how to write efficient queries in SQL Server. The first type of index you should know is the clustered index. We will discuss the following topics about clustered indexes in this tutorial: What is a clustered index? How does a Continue Reading
SQL Server Index INCLUDE: Explained with Examples
Reading Time: 9 minutes In this tutorial, we will discuss a common characteristic of nonclustered indexes: INCLUDED columns. If you missed the full discussion about nonclustered indexes, I suggest you read that first. Check it out here: Nonclustered Indexes: The Ultimate Guide for the Complete Beginner In this tutorial, we will answer the following Continue Reading