SQL Server index on multiple table columns

Question

I wrote a query which uses two columns and each of those columns come from different tables. How can I make an index for these columns and is it even possible?

select countryName, balance
from main.country c 
join main.person p on (c.countryId = p.countryId)
where balance = (select MAX(balance) 
                 from main.person p2 
                 join main.country c2 on (c2.countryId = p2.countryId)
                 where c.countryId = c2.countryId 
                   and p.countryId = p2.countryId)
order by countryName;

Your question seems to be a duplicate of stackoverflow.com/questions/8509026/…. — Behrang Saeedzadeh
– Behrang Saeedzadeh, Commented May 26, 2018 at 0:03
For some reason sql doesn't allow me to join these tables create index idx_countryperson on main.country c inner join main.person p on c.countryId=p.countryId(c.countryName, p.balance); — finsters
– finsters, Commented May 26, 2018 at 0:31
Not sql! The product is called SQL Server! SQL is a language used by numerous relational database management systems. SQL Server is only on of them. Others include Oracle, MySQL, PostgreSQL, MariaDB, to name a few. — Behrang Saeedzadeh
– Behrang Saeedzadeh, Commented May 26, 2018 at 1:21

Hooman Bahreini · Accepted Answer · 2018-05-26 01:08:20Z

3

In SQL Server, if you want to create an index on columns from different tables, then you can create a Schema Bound View and build your index on top of that view.

In you case, create a schema bound view:

CREATE VIEW MyBoundView
WITH SCHEMABINDING  
AS  
   -- YOU QUERY 
   select countryName, balance
   from main.country c join main.person p on(c.countryId=p.countryId)
   where balance=(select MAX(balance) from main.person p2 join main.country c2 
   on(c2.countryId=p2.countryId)
   where c.countryId=c2.countryId and p.countryId=p2.countryId)
   order by countryName;  

GO

Now you can create an index with your two columns on this bound view:

--Example index on the bound view.  
CREATE UNIQUE CLUSTERED INDEX IDX_V1   
   ON MyBoundView (countryName, balance);  
GO

You may find this article useful.

answered May 26, 2018 at 1:08

Hooman Bahreini

15.7k11 gold badges87 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-05-26 01:15:12Z

1

This is your query:

select countryName, balance
from main.country c join
     main.person p
     on c.countryId = p.countryId
where balance = (select MAX(balance)
                 from main.person p2 join
                      main.country c2
                      on c2.countryId = p2.countryId
                 where c.countryId = c2.countryId and p.countryId = p2.countryId
                )
order by countryName;

From what I can tell, you want the highest balance in each country, along with duplicates, if any. You can get these using:

select top (1) with ties c.countryName, p.balance
from main.country c join
     main.person p
     on c.countryId = p.countryId
order by rank() over (partition by c.countryId order by p.balance desc);

To get these in order by country name, you need a subquery:

select cp.*
from (select top (1) with ties c.countryName, p.balance
      from main.country c join
           main.person p
           on c.countryId = p.countryId
      order by rank() over (partition by c.countryId order by p.balance desc)
     ) cp
order by countryName;

edited May 26, 2018 at 1:15

answered May 26, 2018 at 0:59

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Gordon Linoff Over a year ago

@finsters . . . Some wishful programming there. There is no argument; the order is set by the order by.

Collectives™ on Stack Overflow

SQL Server index on multiple table columns

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related