I agree with a column type of char(10) as you say. When it is nvarchar(250) on SQL Serveur 2005 if you run the following query :

SELECT LEN(fieldname) FROM table;

you will not get 250 for each row but the actual length of the string minus the trailing spaces. So up to this point I agree with you. Inserting 'a' and then 'a ' will create an issue if the column is set as unique index whenever it is a char or nvarchar column, absolutly yes.

But let's take an example.

First create a simple table et queries

CREATE TABLE TEST (

[id] [int] NOT NULL ,

[text] [nvarchar] (50) NOT NULL

) ON [PRIMARY]

GO

ALTER TABLE [TEST] WITH NOCHECK ADD

CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED

(

[id]

) ON [PRIMARY]

GO

CREATE UNIQUE INDEX [IX_TEST] ON [TEST]([text]) ON [PRIMARY]

GO

/* Insert with a trailing space *:

INSERT INTO TEST (id, text)

VALUES (1, 'a ')

GO

INSERT INTO TEST (id, text)

VALUES (1, 'b')

GO

/* Then SELECT with like */

SELECT *

FROM TEST

WHERE text LIKE 'a'

/* Returns nothing */

SELECT *

FROM TEST

WHERE text LIKE 'a '

/* Returns 1, 'a ' */

SELECT *

FROM TEST

WHERE text LIKE '_ '

/* Returns 1, 'a ' */

SELECT *

FROM TEST

WHERE text LIKE '% '

/* returns 1,'a ' */

GO

So here, the trailing space remaining during insertion can have some effect according to the way you write your LIKE statement...

Could be confusing sometimes.