What are the types of indexes?

Clustered indexes : All the rows will be stored in the order of the primary key. There can be only one clustered index per table.
Non-clustered indexes : All the columns which are not primary key can be define as non clustered index and they are stored as B-tree structure with the leaf level nodes having the index key value.

What are the types of indexes? was last modified: October 1st, 2017 by Justin Antony

What’s the difference between a primary key and a unique key?

difference primary key unique key sql server
Both primary key and unique enforce uniqueness of the column on which they are defined.
By default primary key creates a clustered index on the column
Unique key creates a nonclustered index by default
primary key doesn’t allow NULLs but unique key allows one NULL value.

What’s the difference between a primary key and a unique key? was last modified: October 1st, 2017 by Justin Antony

Difference between a primary key and a unique key

primary key unique key
A primary key creates a clustered index on the column and unique keys create a nonclustered index. Primary keys do not allow NULL value and unique keys allow one NULL value.
Both primary and unique keys enforce uniqueness of the column on which they are defined. A table can have only one primary key but many unique key

Difference between a primary key and a unique key was last modified: October 1st, 2017 by Justin Antony

32-bit ole db provider “msolap” cannot be loaded in-process on a 64-bit sql server

msolap cannot be loaded
The issue occurs if you rebuild index sometimes due to registry corruption

For this issue you need to deregister and register the dll files again. This will basically overwrites the 32 bit dll with the 64 bit dll.

Deregister Steps:

regsvr32 /u “C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll”

regsvr32 /u “C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll”

Register Steps:

regsvr32 “C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll”

regsvr32 “C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll”

32-bit ole db provider “msolap” cannot be loaded in-process on a 64-bit sql server was last modified: October 1st, 2017 by Justin Antony

Find position of a string in SQL Server

The char index function will return the position of the string. It will always return the first position and its occurrence based on the start position we mention

select charindex('s','jesus',0)
select charindex('s','jesus',4)

 

The first select will return 3 starting position is 0

The second select will return 5. It will ignore first occurrence since starting position is 4.

Find position of a string in SQL Server was last modified: October 1st, 2017 by Justin Antony

List Table and Index details of a database

Table Index Details

List the table and index details of a database using the following query

select 
s.name + '.' + t.name as [Table], max(i.type_desc) as [Index Type]
from
sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), NULL, NULL, NULL,NULL ) AS PS
INNER JOIN 
sys.tables t on PS.object_id = t.object_id
INNER JOIN 
sys.schemas s on t.schema_id = s.schema_id
INNER JOIN 
sys.indexes i on t.object_id = i.object_id
where 
s.name <> 'tmp'
group by s.name + '.' + t.name

 

List Table and Index details of a database was last modified: October 1st, 2017 by Justin Antony