[EN] SQL Server 2014 SP2 - how works TOP and OFFSET?

The TOP and OFFSET commands allows to display only some of the rows from the table - for example, 5 rows, 5 percent of all rows, or rows from 40 to 50. How to use it in practice?

For beginning, let's create a table with test data.

CREATE TABLE dbo.Cities

(

CityID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

CityName VARCHAR(255) NOT NULL,

Population INT NOT NULL

);

INSERT INTO dbo.Cities (CityName, Population)

VALUES

('Shanghai', 24256800), ('Beijing', 21516000),

('Delhi', 16787941), ('Lagos', 16060303),

('Tianjin', 15200000), ('Karachi', 14910352),

('Chengdu', 14427500), ('Istanbul', 14160467),

('Tokyo', 13513734), ('Guangzhou', 13080500),

('Mumbai', 12442373), ('Moscow', 12380664),

('Sao Paulo', 12038175), ('Lahore', 11126285),

('Shenzhen', 10467400), ('Jakarta', 10075310),

('Seoul', 9995784), ('Wuhan', 9785392);

In the dbo.Cities table there are 18 lines. 

SELECT COUNT(*) FROM dbo.Cities

How do I display 5 rows from all?

SELECT TOP(5) * FROM dbo.Cities

How do I display 5 rows from all, where the population is the smallest?

SELECT TOP(5) * FROM dbo.Cities

ORDER BY POPULATION ASC

How do I display 5 percent rows from all?

SELECT TOP(20) PERCENT * FROM dbo.Cities

How to skip records from 1 to 5 and display only the next 2 records (6.7)?

SELECT * FROM dbo.Cities

order by population desc

OFFSET 5 ROWS FETCH FIRST 2 ROWS ONLY;

How to skip records from 1 to 15 and display all others?

SELECT * FROM dbo.Cities

order by population desc

OFFSET 15 ROWS