Archive

Posts Tagged ‘Index’

What does “on-disk structure” means w.r.t Indexes in SQL Server – MSDN TSQL forum

August 18, 2015 Leave a comment

–> Question:

According to MSDN, an index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view.

In this definition, I don’t get what on-disk structure means.
 

–> My Answer:

Do not confuse with on-disk structure statement, not only index, a table is also an on-disk structure as all you data sits on-disk.

Check my response on you other similar question in MSDN TSQL forum.

Just like a table, an index is also on disk which can be built on top a table or separately.

– On top to restructure the whole table which is called the Clustered Index. Clustered Index is nothing but the table itself converted from a heap to a b-tree.

– Separately like a Non-Clustered index, which is a copy of the table with specific columns, created separately for performance reasons to read data will less columns.
 

Ref Link.

Advertisement

Difference between Index and Primary Key – MSDN TSQL forum

August 10, 2015 Leave a comment

–> Question:

What is the difference between the Index and the Primary Key?
 

–> My Answer:

In simple DBMS words:

– A Primary Key is a constraint or a Rule that makes sure to identify a table’s column uniquely and enforces it contains a value, ie. NOT NULL value.

– An Index on the other side is not a constraint, but helps you organize the table or selected columns to retrieve rows faster while querying with SELECT statement.

In SQL Server you can create only one Primary Key, and by-default it creates a Clustered Index on the table with the PK column as the Index key. But you can specify to create Non-Clustered Index with a PK also.

Indexes in SQL Server mainly are:

– Clustered Index

– Non Clustered

… you can specify them as unique or non-unique.

Other type of indexes are:

– ColumnStore

– Filtered

– XML

– Spatial

– Full Text
 

–> Another Answer by Erland:

A primary key is a logical concept. The primary key are the column(s) that serves to identify the rows.

An index is a physical concept and serves as a means to locate rows faster, but is not intended to define rules for the table. (But this is not true in practice, since some rules can only be defined through indexes, for instance filtered indexes.)

In SQL Server a primary key for a disk-based table is always implemented as an index. In a so-called memory-optimized table, the primary key can be implemnted as an index or as a hash.
 

–> Another Answer by CELKO:

PRIMARY KEY is define in the first chapter of the book on RDBMS you are too lazy to read. It is a subset of columns in the table which are all not null and unique in the table. An index is an access method used on the records of a physical file.

Many SQL products use indexes to implement keys; many do not (hashing is a better way for large DB products)
 

Check the video on Primary Keys:

PK Constraint
 

Ref link.