CODEWITHSUNDEEP
sql-servert-sql
Homunculus Reticulli
Homunculus Reticulli

Reputation: 68446

Specifying the number of chars to be included in a compound index in T-SQL

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?

Upvotes: 0

Views: 44

Answers (1)

Rahul
Rahul

Reputation: 77886

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.

Upvotes: 2

Related Questions