0

I am reusing portions of a PostgreSQL schema in a SSQL database.

This is a snippet of my SQL statements:

CREATE TABLE pac_region 
(id INTEGER NOT NULL PRIMARY KEY,
 country_id INTEGER REFERENCES Country(id) ON UPDATE CASCADE ON DELETE NO ACTION,
 name VARCHAR(256) NOT NULL
);

CREATE UNIQUE INDEX idxu_pac_region_name ON pac_region(country_id, name(32));

I want to specify that only the first 32 chars of the name need to be unique (when combined with the country_id).

SSMS barfs at the (32) specification. What is the correct way to restrict the length of a text used in a compound index, in TSQL?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Homunculus Reticulli
  • 65,167
  • 81
  • 216
  • 341

1 Answers1

2

I don't think you can create a index partially on a column, like what you are trying. Rather, you can create a persisted computed column and add index on that column like

Taken from Create Index on partial CHAR Column

alter table pac_region 
add Computed_Name as cast(name as varchar(32)) persisted;

CREATE UNIQUE INDEX idxu_pac_region_name 
ON pac_region(country_id, Computed_Name);

(OR)

Probably by creating a indexed view.

Community
  • 1
  • 1
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • Does this actually store only the first 32 characters in the index's BTree, or is the whole `name` stored and truncated on-the-fly at run time? – Michael Green Apr 20 '14 at 12:16
  • @MichaelGreen, you are actually creating index on a new column which is 32 char long (which is also kind of substring value of the original column value) – Rahul Apr 20 '14 at 12:18