CREATE PARTITION FUNCTION part_func_country (char(2)) AS RANGE right FOR VALUES ('DK', 'NO', 'SE');

CREATE PARTITION SCHEME part_sch_country AS PARTITION part_func_country ALL TO ([primary]);

CREATE TABLE members (
	ID int identity(1, 1),
	Name varchar(200),
	Country char(2),
	PRIMARY KEY CLUSTERED (ID ASC, Country asc)
) on part_sch_country(Country)

INSERT INTO members (Name, Country) 
SELECT 'frank', 'ch' UNION ALL
SELECT 'nina', 'dk' UNION ALL 
SELECT 'lise', 'fi' UNION ALL
SELECT 'henning', 'no' UNION ALL 
SELECT 'ole', 'no' UNION ALL 
SELECT 'jan', 'no' UNION ALL 
SELECT 'per', 'no' UNION ALL
SELECT 'thomas', 'se'

--> Peculiar? Or not...?
SELECT * FROM members WHERE $PARTITION.part_func_country(country) = ($PARTITION.part_func_country('dk'))

--> These are ok I guess
SELECT * FROM members WHERE $PARTITION.part_func_country(country) = ($PARTITION.part_func_country('no'))
SELECT * FROM members WHERE $PARTITION.part_func_country(country) = ($PARTITION.part_func_country('se'))
SELECT * FROM members WHERE $PARTITION.part_func_country(country) = ($PARTITION.part_func_country(null))

--> But this is even worse! ...or isn't it??
SELECT 
	TableName = OBJECT_NAME(i.object_id), 
	p.partition_number,
	rv.value,
	p.rows
FROM sys.partitions p 
	JOIN sys.indexes i 
		ON p.object_id = i.object_id
	JOIN sys.partition_schemes ps 
		ON ps.data_space_id = i.data_space_id
	JOIN sys.partition_functions f 
		ON f.function_id = ps.function_id
	LEFT JOIN sys.partition_range_values rv    
		ON f.function_id = rv.function_id 
			AND p.partition_number = rv.boundary_id
WHERE i.index_id = 1 
	AND OBJECT_NAME(i.object_id) = 'members'