Tag : clustered index

Unique Key is Clustered or Nonclustered 2

An easy question for you “Unique Key is Clustered or Nonclustered”? Lets explain in more detail, When I create a Unique key on a table without explicitly defining the type of index(clustered or nonclustered), which type of index will be used to accommodate the key?

I have already written a blog on behavior of primary key for clustered and nonclustered index type selection in such kind of scenarios and here is the link. In this post titled as “Unique Key is Clustered or Nonclustered”, we will explore the behavior of Unique Key in regards of Clustered and Nonclustered index type selection. Before going forward, I would like to focus on the differences between Primary Key and Uniquer Key below.

Difference between Primary Key and Unique Key

 Primary key

  1. It is a column or set of columns, used to uniquely identify each row of the given table.
  2. NULL values

Creating primary key without clustered index 1

One of my colleague asked me a question that “Can we create a primary key without a clustered index?”. I answered him that “Yes, of-course!” and also did not forget to share this information to all of you in this post named “Create nonclustered primary key”. I know many of us are well aware of this and have did this many times in our day to day SQL activity. Still for those who don’t know how they can accommodate a primary key with a nonclustered index, i am sharing this post, in which i will show you that how we can create a non-clustered primary key on a table.

Before creating this nonclustered primary key, here i am showing you the default behavior of SQL Server during Primary key creation. In case table does not have any Clustered index created on it, SQL Server creates a PRIMARY KEY on a … More

Clustered Vs NonClustered indexes (When to choose which one)

In this topic we are going to compare clustered indexes with nonclustered indexes with their usage and performance and will also discuss some points regarding the topic “when to choose which one”.  Clustered Vs NonClustered indexes is an old topic and you can get many article written so far. But in this post I will cover the definitions, structure of indexes, syntax of clustered and nonclustered indexes and the most interesting part is “when to choose which one”. So lets explore clustered and nonclustered indexes with “When to use Clustered vs NonClustered indexes” in deep.

Clustered index

As we know that a Clustered indexes sorts and stores the data rows in the table or view based on their key values. These are the columns included in the index definition. And the most important thing is we can have only one clustered index per table, because the data rows themselves can … More