Indexes on Table Types

SQL Server allows limited indexing on table types only through PRIMARY KEY and UNIQUE constraints, which create underlying clustered and non-clustered indexes for each TVP instance. Arbitrary indexes are not supported because TVPs are designed as lightweight, immutable streaming rowsets with predictable memory usage and stable plan compilation. Additionally, TVPs historically lack full statistics, so even with indexes the optimizer may produce poor cardinality estimates. For large or complex workloads, best practice is to materialize TVP data into temp tables with full indexing and statistics. 

We can create only below inline indexes on TT

Ø  clustered Index

Ø  non clustered index

We CANNOT create normal CREATE INDEX statements.

Let’s see the demo

Creating cluster index

CREATE TYPE dbo.OrderTypePK AS TABLE (    OrderID INT PRIMARY KEY,    CustomerID INT,    Amount MONEY,    INDEX Pk_OrderID CLUSTERED(CustomerID) );

Creating non cluster index on TT

CREATE TYPE dbo.OrderTypeNC AS TABLE (    OrderID INT PRIMARY KEY ,    CustomerID INT,    Amount MONEY,    INDEX Pk_OrderID CLUSTERED(CustomerID),    INDEX IX_OrderType NONCLUSTERED( OrderID, CustomerID ) );

 Both TT created successfully.

We can not create index for TT using create index as below.

CREATE INDEX IX_Price ON dbo.OrderTypeNC(OrderID);

It failed because table types are metadata templates, not real tables.

When we are creating table type it create metadata and not required for storage. A metadata blueprint that SQL Server uses to instantiate memory-backed rowsets at runtime. Every time a TVP is passed. SQL Server creates an internal structure (in memory or tempdb) and applies the constraints + indexes defined in the type. 

Why order by clause not working properly on sql_variant column

ORDER BY on sql_variant is unreliable because SQL Server sorts by data type precedence first and value second, not by logical value. Mixed data types will always be grouped by their underlying type. sql_variant does NOT sort by actual stored value alone. It first sorts by data type precedence, then by value inside that type.

When we are ordering sql_variant it orders on below order

datetime > float > decimal > int > varchar > nvarchar > bit ...

let’s see the demo

here creating a table and inserting few records.

CREATE TABLE sql_variant_Puzzles_Demo (     ID INT IDENTITY PRIMARY KEY,     DataValue sql_variant );   INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES (1)              -- int INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(0)                -- int INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES (10)              -- int INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(2)                -- int INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES (10.0)              -- float INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(2.0)                -- float INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES('10')           -- varchar INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES('2')            -- varchar INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(CAST('2024-01-01' AS date))--date INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(CAST('2020-01-01' AS date)) --date INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES('2024-01-01') --varchar INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES('2020-01-01') --varchar INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(CAST(1 as bit)) --bit INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(CAST(0 as bit)) --bit

Table created and data inserted successfully.

See the data

Now ordering the data in descending order and see

SELECT     ID,     DataValue,               SQL_VARIANT_PROPERTY(DataValue, 'BaseType')  as datatype FROM sql_variant_Puzzles_Demo order by DataValue desc

 

Not getting the correct sorting order.

Two rows look identical in result set but distinct by return more rows than expected

This question is asked by interviewer they show two table like below.

Both tables have same records, but when we are using the distinct it return the differ result.

 

Why this happens.

DISTINCT on sql_variant does not deduplicate by displayed value. It compares the underlying data type and metadata as well, so values that appear identical but were stored using different base types remain distinct.

Let’s see the demo.

Creating two table and inserting few records.

CREATE TABLE tbl1 (     ID INT IDENTITY PRIMARY KEY,     DataValue sql_variant );   CREATE TABLE tbl2 (     ID INT IDENTITY PRIMARY KEY,     DataValue int );  insert into tbl1 (DataValue) values (10)         -- int insert into tbl1 (DataValue) values ('10')      -- varchar insert into tbl1 (DataValue) values (CAST(10 AS bigint)) --bigint insert into tbl1 (DataValue) values (CAST('10' AS nvarchar(10))); --nvarchar insert into tbl2 (DataValue) values (10), (10),(10),(10)

See the data

Now using distinct

Main reason is the datatype of both table tbl1 data type is sql_variant which can story any data type but in the seconds table tbl2 has int so in this table we can only insert int data type.

Due to that it tbl1 return 2 rows (int + bigint) and (varchar +nvarchar) while tbl2 has only one datatype.

What happens internally when using SELECT INTO vs CREATE TABLE with temp tables

SELECT INTO dynamically creates the temp table and inserts data in a single operation. Internally it performs metadata creation and data loading together, uses TempDB heavily, and usually benefits from minimal logging, making it very fast for large bulk operations — but it does not create indexes, constraints, or statistics automatically, so plan stability is weaker.

CREATE TABLE + INSERT creates metadata first, then loads data. Inserts are fully logged, TempDB allocation is better controlled, statistics and indexes are supported, execution plans are more stable, and it supports temp table caching. This makes it better for OLTP, complex joins, and repeat execution scenarios.

BUFFER LATCH in SQL server

A BUFFER LATCH is a protection mechanism used by SQL Server to safeguard buffer pool pages while they are being read or written. Whenever SQL Server reads a page from disk or writes a page to disk or pins page in buffer or checks page validity or modifies internal memory metadata. SQL Server places a Buffer Latch on that page.

we need Buffer Latches because multiple threads may try to simultaneously access same buffer page for readers or modifiers or flushing threads or checkpoint or Lazy writer. So, buffer latch protects in-memory page while it is being accessed. BUT unlike Locks, it has nothing to do with transactions.

Feature	BUFFER LATCH	PAGELATCH	LOCK
Protects	In-memory buffer structures	In-memory page access coordination	Logical data consistency
Location	Buffer Pool	Buffer Pool page access	Transaction engine
Purpose	Safety of memory copy of page	Avoid concurrent corruption of in-memory page	Isolation + concurrency control
Duration	Microseconds–milliseconds	Microseconds–milliseconds	Transaction duration
Appears in sys.dm_tran_locks	❌ No	❌ No	✔ Yes
Root problem when high	I/O stress / memory pressure	TempDB / hot page contention	Blocking

 Short version

Ø  Buffer Latch = Protect buffer page during I/O + internal access

Ø  Pagelatch = protect shared in-memory page access

Ø  Lock = protect logical data modifications

Below are the common buffer latch wait type

Wait Type	Meaning
BUFFERLATCH	General buffer latch wait
BUFFERLATCH_SH	Shared access to buffer
BUFFERLATCH_EX	Exclusive latch on buffer
BUFFERLATCH_UP	Update latch
PAGEIOLATCH_*	(Often seen with buffer latch during disk read)

 

 

Connecting cube to excel step by step

Open the excel file

Click on the data section and select from other source. You will get different data source. Select From Analysis service.

Click on that

Write the server Name and select the windows Authentication.

Click on next button.

Select the cube which one you want export in excel.

Click next

Click Finish

Click ok.

You will get the pivot Excel

You will get the desirer reports

Creating Backup, Restore, attach and detach cube in SSAS

In general term, a backup, or the process of backing up, refers to the copying and archiving of computer data so it may be used to restore the original after a data loss event.

Backups have two distinct purposes.

Ø  The primary purpose is to recover data after its loss, be it by data deletion or corruption.  Data loss can be a common experience of computer users.

Ø  The secondary purpose of backups is to recover data from an earlier time, according to a user-defined data retention policy, typically configured within a backup application for how long copies of data are required. Though backups popularly represent a simple form of disaster recovery, and should be part of a disaster recovery plan, by themselves, backups should not alone be considered disaster recovery.

Backup is very important activities of the databases. It is also very important in cube also. In this article I am going to explain step by step to creating the cube backup.

                       

Open SQL server Management studio.

Connect with Analysis services

Explore Database. You will get the list of cube which has been deployed on this Analysis services.

Right click on the cube for which you want to create a back up.

Click on backup.

A: - Click on browse where you want to store the backup of the cube. Select the paths where you want to keep the cube back up and write the cube backup name. Click ok.

B: - There are three type of option while creating the back up of the cube.

                               

Ø  Allow  file overwrite ( if you check this option it will be overwrite if any cube back up if exist on the same location).

Ø  Apply compression ( if you check this option it will be compress the cube back up file)

Ø  Encrypt back up file (if you check this option it will protect you back up with password protection).

C: - If you have checked encrypt back up file option then it will be enabling. You need to provide password. Whenever you restore this back up you need to provide that password that time.

D: - If you want to generate the script of the back file then click on the script you will get the following script.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">   <Object>     <DatabaseID>AdvenchaeWarks</DatabaseID>   </Object>   <File>E:\Cube BackUp\BackUpCube.abf</File>   <Password>****</Password> </Backup>

E: - Click OK.

Now back up has been created successfully.

Restore the backup cube file on the Analysis services

Right click on database

Click on Restore

A: - Click on the browser button and select the cube back up file. Click ok.

B: - Write Restore database name and select the path where you want to store .db file. Click OK.

C: - select the option if you want to over write the existing database the select it .

D: - while creating back up you used password for encrypt the backup file that password you need to provide here. If you did not provide password at backup creation type then keep it blank.

E: - If you want to generate script of restoring database click on script. You will get following script.

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">   <File>E:\Cube BackUp\BackUpCube.abf</File>   <DatabaseName>TestCubeBackUp</DatabaseName>   <AllowOverwrite>true</AllowOverwrite>   <Password>****</Password>   <DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"> E:\Cube BackUp\</DbStorageLocation> </Restore>

F: - Click Ok .Now back up has been restored.

Refresh the database you will get the new database.

Detach the cube

Right click on the cube database which one you want to detach.

Provide the password and click ok.

Cube database has been detached.  Refresh the database.

                                

Attached the cube database

Right click on the database and select attach

Select the .db file

Click ok and write the password.

If you want to generate the script then click on the script

<Attach xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">   <Folder>E:\Cube BackUp\TestCubeBackUp.7.db\</Folder>   <ReadWriteMode xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100"> ReadWrite</ReadWriteMode>   <Password>****</Password> </Attach>

Click ok. Now cube database has been attached. Refresh the database you will that cube database.

                             

     

Hope this article is helping you. If you have any query  feel free to disturb me.