How to return data in alphabetical order from 2 different shards?-CodePudding

In SQL Server what is the correct way to retrieve data that is in alphabetical order from the same table on 2 different shards?

Right now on the db I am working on there is a table that exists on 2 different shards. I need to be able to select the data from each table in alphabetical order in batches of 5. So for example.

In Shard1 we have a table with 6 items of data

A,
A,
A,
A,
A,
A

In Shard2 we have a table with 4 items of data

A2,
B2,
C2,
D2,

When I run my query the first time I need to return

A,
A,
A,
A,
A

When I run it the next time it should return

A,
A2,
B2,
C2,
D2

I have been looking through documentation but unclear what to do. The reason is that this seems not possible because there would have to be some sort of middle component that would have to keep track of the alphabetical order of each table in separate databases. I know it is easy to execute operations that alphabetize on 1 table in 1 database, but across databases and tables seems not possible. What is the correct way to do this with sql server? Or this even possible?

CodePudding user response：

It's not fully clear what you're asking about since sharding isn't a concrete feature in SQL Server.

That being said, let's assume you have two separate SQL Server instances, with the same table, and you've setup a Linked Server between them. You can combine the two sets of data with a UNION or UNION ALL (depending on if you want to remove or keep duplicates in the dataset, respectively) within a subquery and then order them alphabetically like so:

SELECT TOP 5 -- Top 5 alphabetically 
    ItemId
FROM
(
    SELECT ItemId
    FROM dbo.Items -- Local instance's copy of the table

    UNION -- Removing duplicates (as per your example)

    SELECT ItemId
    FROM LinkedServerName.DatabaseName.dbo.Items -- Remote instance's copy of the table
) AS Items
ORDER BY ItemId