Can we creating index on non unique value columns on temporary tables

_{Thursday, October 10, 2013 8:53 AM}

Hi Can we create a index on non unique values of a column on temporary  table? 

thanks,

Satwick 

satwick

_{Thursday, October 10, 2013 11:31 AM ✅Answered}

Hi Thank you for the reply,

How does it actually identifies and create index ID for identical value rows if we create a clustered index? Can you elaborate on this pls?

thanks,

When you create a non unique clustered index, SQL Server internally add a 4 byte hidden uniquifier column to the rows to guarantee uniqueness. This uniquifier will be part of the clustering key and will be copied to the non-clustered index keys as well.

You can read more on this below:

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/06/07/what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed.aspx

http://www.mssqltips.com/sqlservertip/2082/understanding-and-examining-the-uniquifier-in-sql-server/

http://michaeljswart.com/2012/12/well-that-wasnt-obvious/

Krishnakumar S

_{Thursday, October 10, 2013 8:55 AM}

Yes sure

create table #t (c int)

create index my_idx on #t(c)

insert into #t values (1),(1)

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Thursday, October 10, 2013 8:59 AM}

yes, you can create a non unique clustered as well non unique NONCLUSTERED index on temporary table. 

Here is example : 

CREATE TABLE #TEST
(
  A int 
)

insert into #test values (1),(1)

create clustered index c_A on #test (A)

create NONCLUSTERED index n_A on #Test(A)

select * from #Test

Regards Harsh

_{Thursday, October 10, 2013 10:43 AM}

Hi Thank you for the reply,

How does it actually identifies and create index ID for identical value rows if we create a clustered index? Can you elaborate on this pls?

thanks,

satwick

satwick

_{Thursday, October 10, 2013 11:03 AM}

Hi Thank you for the reply,

How does it actually identifies and create index ID for identical value rows if we create a clustered index? Can you elaborate on this pls?

If I understood your question correctly....

Each index on a table will have a distinct index id to identify those indexes in SQL Server. Optimizer will choose the best suitable index for the query execution.

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.