serge.laot (5/13/2008)

---

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.

Returns the number of characters of the specified string expression, excluding trailing blanks.