Creates a table that lists state names and abbreviations.

States.Table.sql

-- Created a simple table for storing state names and postal abbreviations.
SET NOCOUNT ON;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[States]') AND type in (N'U'))
DROP TABLE [dbo].[States]
GO


CREATE TABLE States (
	Name varchar(30) NOT NULL, --The largest "state" name is 'FEDERATED STATES OF MICRONESIA' which is exactly 30 characters long
	Abbreviation CHAR(2) NOT NULL
		CONSTRAINT PK_States_Abbreviation PRIMARY KEY CLUSTERED
);
GO

BEGIN TRAN;

-- Source http://www.usps.com/ncsc/lookups/usps_abbreviations.html Pulled on 2011-02-17
-- Added the military abbreviations would have removed uniqueness and complicated things

INSERT INTO States (Name, Abbreviation) VALUES ('ALABAMA', 'AL');
INSERT INTO States (Name, Abbreviation) VALUES ('ALASKA', 'AK');
INSERT INTO States (Name, Abbreviation) VALUES ('AMERICAN SAMOA', 'AS');
INSERT INTO States (Name, Abbreviation) VALUES ('ARIZONA', 'AZ');
INSERT INTO States (Name, Abbreviation) VALUES ('ARKANSAS', 'AR');
INSERT INTO States (Name, Abbreviation) VALUES ('CALIFORNIA', 'CA');
INSERT INTO States (Name, Abbreviation) VALUES ('COLORADO', 'CO');
INSERT INTO States (Name, Abbreviation) VALUES ('CONNECTICUT', 'CT');
INSERT INTO States (Name, Abbreviation) VALUES ('DELAWARE', 'DE');
INSERT INTO States (Name, Abbreviation) VALUES ('DISTRICT OF COLUMBIA', 'DC');
INSERT INTO States (Name, Abbreviation) VALUES ('FEDERATED STATES OF MICRONESIA', 'FM');
INSERT INTO States (Name, Abbreviation) VALUES ('FLORIDA', 'FL');
INSERT INTO States (Name, Abbreviation) VALUES ('GEORGIA', 'GA');
INSERT INTO States (Name, Abbreviation) VALUES ('GUAM', 'GU');
INSERT INTO States (Name, Abbreviation) VALUES ('HAWAII', 'HI');
INSERT INTO States (Name, Abbreviation) VALUES ('IDAHO', 'ID');
INSERT INTO States (Name, Abbreviation) VALUES ('ILLINOIS', 'IL');
INSERT INTO States (Name, Abbreviation) VALUES ('INDIANA', 'IN');
INSERT INTO States (Name, Abbreviation) VALUES ('IOWA', 'IA');
INSERT INTO States (Name, Abbreviation) VALUES ('KANSAS', 'KS');
INSERT INTO States (Name, Abbreviation) VALUES ('KENTUCKY', 'KY');
INSERT INTO States (Name, Abbreviation) VALUES ('LOUISIANA', 'LA');
INSERT INTO States (Name, Abbreviation) VALUES ('MAINE', 'ME');
INSERT INTO States (Name, Abbreviation) VALUES ('MARSHALL ISLANDS', 'MH');
INSERT INTO States (Name, Abbreviation) VALUES ('MARYLAND', 'MD');
INSERT INTO States (Name, Abbreviation) VALUES ('MASSACHUSETTS', 'MA');
INSERT INTO States (Name, Abbreviation) VALUES ('MICHIGAN', 'MI');
INSERT INTO States (Name, Abbreviation) VALUES ('MINNESOTA', 'MN');
INSERT INTO States (Name, Abbreviation) VALUES ('MISSISSIPPI', 'MS');
INSERT INTO States (Name, Abbreviation) VALUES ('MISSOURI', 'MO');
INSERT INTO States (Name, Abbreviation) VALUES ('MONTANA', 'MT');
INSERT INTO States (Name, Abbreviation) VALUES ('NEBRASKA', 'NE');
INSERT INTO States (Name, Abbreviation) VALUES ('NEVADA', 'NV');
INSERT INTO States (Name, Abbreviation) VALUES ('NEW HAMPSHIRE', 'NH');
INSERT INTO States (Name, Abbreviation) VALUES ('NEW JERSEY', 'NJ');
INSERT INTO States (Name, Abbreviation) VALUES ('NEW MEXICO', 'NM');
INSERT INTO States (Name, Abbreviation) VALUES ('NEW YORK', 'NY');
INSERT INTO States (Name, Abbreviation) VALUES ('NORTH CAROLINA', 'NC');
INSERT INTO States (Name, Abbreviation) VALUES ('NORTH DAKOTA', 'ND');
INSERT INTO States (Name, Abbreviation) VALUES ('NORTHERN MARIANA ISLANDS', 'MP');
INSERT INTO States (Name, Abbreviation) VALUES ('OHIO', 'OH');
INSERT INTO States (Name, Abbreviation) VALUES ('OKLAHOMA', 'OK');
INSERT INTO States (Name, Abbreviation) VALUES ('OREGON', 'OR');
INSERT INTO States (Name, Abbreviation) VALUES ('PALAU', 'PW');
INSERT INTO States (Name, Abbreviation) VALUES ('PENNSYLVANIA', 'PA');
INSERT INTO States (Name, Abbreviation) VALUES ('PUERTO RICO', 'PR');
INSERT INTO States (Name, Abbreviation) VALUES ('RHODE ISLAND', 'RI');
INSERT INTO States (Name, Abbreviation) VALUES ('SOUTH CAROLINA', 'SC');
INSERT INTO States (Name, Abbreviation) VALUES ('SOUTH DAKOTA', 'SD');
INSERT INTO States (Name, Abbreviation) VALUES ('TENNESSEE', 'TN');
INSERT INTO States (Name, Abbreviation) VALUES ('TEXAS', 'TX');
INSERT INTO States (Name, Abbreviation) VALUES ('UTAH', 'UT');
INSERT INTO States (Name, Abbreviation) VALUES ('VERMONT', 'VT');
INSERT INTO States (Name, Abbreviation) VALUES ('VIRGIN ISLANDS', 'VI');
INSERT INTO States (Name, Abbreviation) VALUES ('VIRGINIA', 'VA');
INSERT INTO States (Name, Abbreviation) VALUES ('WASHINGTON', 'WA');
INSERT INTO States (Name, Abbreviation) VALUES ('WEST VIRGINIA', 'WV');
INSERT INTO States (Name, Abbreviation) VALUES ('WISCONSIN', 'WI');
INSERT INTO States (Name, Abbreviation) VALUES ('WYOMING', 'WY');
COMMIT TRAN;

-- As an alternative we could have just added the index here.
ALTER INDEX PK_States_Abbreviation ON States
	REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
GO