Partitioning a table state So far I always used date ranges to partition my tables, however now I like to do it by state or a selected list of cities where all others that are not explicitly listed in an "other" partition, is this possible with SQL Server ?
Create a secondary table with contiguous values for the known states (and maybe leave a little room for others you may want to segregate later):

CREATE TABLE dbo.States
(
  StateID TINYINT PRIMARY KEY,
  Abbreviation CHAR(2)
  StateName VARCHAR(255) NOT NULL UNIQUE
);

INSERT dbo.States VALUES
(1,'AL','Alabama'),(2,'AK','Alaska'), ..., (51,'WY','Wyoming');

-- leave 52 - 68 available for other partitions

INSERT dbo.States VALUES
(69,'EH','Canada'),(70,'PR','Puerto Rico');


Now, in the partitioned table, you can partition against the StateID column, and just set up the partition scheme such that 69+ is its own partition.

You can't do this with state or city names because partitioning on those values can only be alphabetical. There's not really a clean way to say "put PA in its own partition, and RI in its own partition, but lump PR into another partition along with Guam."