Indexes in SQL

We very well know what the purpose of an Index is and how useful it is when it comes to searching.
Same rule applies to the data stored in Table.
Here we have two types of Indexes, i.e. Cluster Index and Non-Cluster Index. It is very important how you apply Clustered/Non-Clustered index. It completely depends on the requirement.
In general, indexes improve performance when they are created on fields used in table joins.
When dealing with relatively small tables, indexes do not improve performance.
Although indexes improve search performance, they slow the updates, and this might be something worth considering.

Clustered Index:
By applying cluster index, the data is physically sorted on the field on which index is applied. This also implies that we can have just one clustered index per table as this also acts as the ‘RowLocater’. Querying through such a data takes much less time compared non-indexed data.
Clustered indexes are ideal for queries that select by a range of values or where you need sorted results. This is because the data is already presorted in the index for you.
Examples of this include when you are using BETWEEN, <, >, GROUP BY, ORDER BY, JOIN and aggregates such as MAX, MIN, and COUNT in your queries.
Avoid putting a clustered index on columns that increment,
Thus, all primary key constraints of capture DB are modified to Clustered Indexes. Primary keys of table ItemsInBlock and RO_ItemsInBlock are not made Clustered, reason being these table have cluster index on other fields.

Example:
Consider a Table_A, which has 3 columns (ID, Name, Age) and ID being Primary key then,
CREATE UNIQUE CLUSTERED INDEX [PK_Table_A]
ON [dbo].[Table_A] ([ID])


Non-Clustered Index:
In this, the data in the table is not physically sorted but instead it maintains indexed data. The row locater is just a pointer refers the rows depending the on the non-cluster index. Non-cluster indexes should be used as per the needs. Practically a table can have upto 249 non-clustered indexes. But, as mentioned earlier having too many indexes will slow down the performance on INSERT, UPDATE and DELETE will affect the data in Index too.

Example:
Consider a query SELECT Name FROM Table_A WHERE ID = 3 on Table_A, which has 3 columns (ID, Name, Age) where ID being Primary key and we have a non-clustered index :
CREATE INDEX [IDX_Table_A_ByID_Name] ON [dbo].[ExternalInputStatus]([ID], [Name])
In the above example, the data will be fetched from the non clustered indexed object of table instead of actual table (clustered index) because, all the data it needs is in the index.


Covering Indexes:
Apart from the above mentioned Indexes there is one more “concept” know as Covering Indexes. Its not a specific type of index.
The idea is, when you create an index, an object is created in the database that has all of the data in the index. SQL Server can then use this index to speed up your queries. Now, consider a query that only uses a couple of columns in your table. If you create an index that has all of the data from those columns, then there is no reason for the query to use the table (because all of the data is found in the index).

Example:
Consider a query SELECT Name, Age FROM Table_A WHERE ID = 3 on Table_A, which has 3 columns (ID, Name, Age) where ID being Primary key and if we apply an Index like:
CREATE NONCLUSTERED INDEX [IDX_ID_Name_AGE] ON [dbo].[Table_A]([Id], [Name], [FavoriteColor].
In the above example, the data will be fetched from the non clustered indexed object of table instead of Actual table. But, as mentioned earlier having too many indexes will slow down the performance on INSERT, UPDATE and DELETE will affect the data in Index too.

Core i3 vs Core i5 vs Core i7 - Whatever but, Intel Inside!

In my previous post I tried to explain the difference between a Core2Duo and Core i3. My crave didn't stop there and I went on further...

This post explains the difference between the Intel Core i3, Core i5 and the Core i7 processors.


Intel Core i3

Codenamed: Clarkdale (Westmere)

Fabrication: 32 nm

Instruction set: X86, X86-64, MMX, SSE 4.2

Socket: LGA 1156

Bus: Direct Media Interface

RAM: DDR3

Chipset Support: P55, H55, H57, Q57

The Core i3 processors are dual core processors based on the Clarkdale (Westmere) architecture. They have a 32 nm fabrication and work with LGA 1156 socket motherboards. Direct Media Interface (DMI) replaces FSB in the Core i3 processors, unlike the faster Quick Path Interconnect in the Core i7 processors. They have clock speeds ranging from 2.93 to 3.06 GHz. They have 2×256 KB L2 cache and 4 MB L3 cache. They are compatible with these chipsets – P55, H55, H57, Q57. They don’t support Turbo Boost (dynamic overclocking) unlike the Core i5 and Core i7. They also have an Integrated graphics processor. They also have Hyperthreading support.

Intel Core i5

Codenamed: Lynnfield (Nehalem), Clarkdale (Westmere)

Fabrication: 45 nm, 32 nm

Instruction set: X86, X86-64, MMX, SSE 4.2

Socket: LGA 1156

Bus: Direct Media Interface

RAM: DDR3

Chipset Support: P55, H55, H57, Q57

Intel Core i5

There are two types in Core i5 – Dual core and Quad Core. The dualcore Intel Core i5 processors are similar to the Core i3s while the quadcore Core i5s are much closer to the Core i7 in terms of features.

DualCore Core i5 Processors – They are based on the Clarkdale (Westmere) architecture like the Core i3. They also have a 32 nm fabrication and work with LGA 1156 socket motherboards. Direct Media Interface (DMI) replaces FSB in the Core i5 dualcore processors. They have clock speeds ranging from 3.2 to 3.6 GHz. They have 2×256 KB L2 cache and 4 MB L3 cache. They are compatible with these chipsets – P55, H55, H57, Q57. They support Turbo Boost (dynamic overclocking). They also have an Integrated graphics processor and also include Turbo Boost (dynamic overclocking). They also have Hyperthreading support.

QuadCore Core i5 Processors – They are based on the Lynnfield (Nehalem) architecture similar to the Core i7. They have a 45 nm fabrication and work with LGA 1156 socket motherboards. Direct Media Interface (DMI) replaces FSB in the Core i5 quadcore processors. There are 2 processors with clock speeds of 2.4 and 2.66 GHz. They have 4×256 KB L2 cache and 8 MB L3 cache. They are compatible with these chipsets – P55, H55, H57, Q57. They do support Turbo Boost (dynamic overclocking). However they don’t support Hyperthreading and don’t have a Integrated Graphics Processor.

Intel Core i7

Codenamed: Bloomfield (Nehalem)

Fabrication: 45 nm

Instruction set: X86, X86-64, MMX, SSE 4.2

Socket: LGA 1366

Bus: Quick Path Interconnect

RAM: DDR3

Chipset Support: X58

Intel Core i7

They are the most powerful of the lot. They are based on the Bloomfield (Nehalem) architecture. They have a 45 nm fabrication and work with LGA 1366 socket motherboards. They have Quick Path Interconnect (QPI) which is faster than both DMI in Core i3/i5 and FSB. They are native quad cores which offer the best performance. They have 4×256 KB L2 cache and 8 MB L3 cache. They are compatible with the X58 chipset. They support Turbo Boost (dynamic overclocking) and Hyperthreading (8 virtual cores). However they don’t have an Integrated Graphics Processor.

All the three – Core i3, Core i5 and Core i7 comes with Smart Cache (cache sharing between two cores) and support hardware virtualization.

Intel Core2Duo vs Intel Core i3

With everything changing so fast and getting smaller and smaller, it becomes obvious that all these processor manufacturers have to put in lot of effort and brain... (no offence) but Intel rocks!
So the question asked to me was, "what is the basic difference between a Core2Duo and the new Core i3/5/7 processors". It is quite easy to just say that Core i3... are next gen or 64bit core processors but, is it really that fast?
Indeed! In this post I have tried to explain that.
The Intel Core i3 range offers only dual core processors unlike the Core i5 and i7 which offer Quad cores as well.
Intel Core 2 Duo
Codenamed: Conroe, Allendale, Wolfdale – Core 2
Fabrication: 65 nm / 45 nm
Instruction set: X86, X86-64, MMX, SSE 4.1
Socket: LGA 775
Bus: 1333 MHz FSB
RAM: DDR2
Chipset Support: 965, P3x, G3x, P45, X48


Intel Core i3
Codenamed: Clarkdale
Fabrication: 32 nm
Instruction set: X86, X86-64, MMX, SSE 4.2
Socket: LGA 1156
Bus: Direct Media Interface
RAM: DDR3
Chipset Support: P55, H55, H57, Q57
LGA 1156, Direct Media Interface, Smart Cache
Both the Intel Core i3 and Intel Core 2 Duo processors are native dual cores; they have both processors on the same die which leads to better processing speeds. The Intel Core 2 Duo processors work on the LGA 775 socket, the new Core i3 and Core i5 processors use the LGA 1156 socket. The Core i3 processors support motherboards based on the following chipsets – P55, H55, H57, Q57.
As the Core i3 processors have a 32 nm fabrication, they are much more power efficient and run faster than Core 2 Duo processors which have a 65 / 45 nm fabrication. There are two processors in the Core i3 series – Core i3 530 (2.93 GHz) and the Core i3 540 (3.06 GHz). Both have 2 x 256 KB L2 cache and 4 MB of L3 cache. Both the Core i3 processors sport the DMI (Direct Media Interface) which is much faster than the FSB used in Core 2 Duo processors but is not as fast as Quick Path Interconnect in the Core i7.

The Core i3 processors also have a on die graphics processor which offer much better graphics performance. They also support Hyperthreading which means that each core can run 2 threads simultaneously which results in 2×2=4 virtual cores. They also have a on die memory controller which helps in much faster memory access than the Core 2 Duo processors. Also their chipsets are compatible with DDR3 RAM. The whole combination of Core i3 + DDR3 RAM offers much better performance than Core 2 Duo + DDR2 RAM. The Core i3 processors also support Smart Cache which allows cache memory sharing between the two cores reducing memory latencies.
However, unlike the Core i5 and Core i7 processors, the Core i3 processors also don’t support Turbo Boost which allows dynamic overclocking for better performance. Nevertheless, they are priced very low, which makes them a very good deal for budgetPCs

Read further:
Core i3 vs Core i5 vs Core i7