insert into #temp values(' ##########a##################### ')

original post didnt allowed me spaces so '#' is a placeholder for space

Also works with CHAR, NCHAR, and NVARCHAR.

Try this example also

DECLARE @Test CHAR(10), @Test2 CHAR(10)
SET @Test = 'test'
SET @Test2 = 'Test2'
SELECT  LEN(@Test), LEN(@Test + '_') , LEN(@Test2), LEN(@Test2 + '_')

How SQL Server Compares Strings with Trailing Spaces

http://support.microsoft.com/kb/316626

Trailing spaces with LIKE doesn't seem to work in all cases!

CREATE TABLE #Dummy(Column1 VARCHAR(10))
INSERT INTO #Dummy VALUES ('a       ')

--(1) Simple Comparison doesn't work as expected
SELECT '~' + Column1 + '~' AS [Simple Comparison] FROM #Dummy WHERE Column1 ='a'

--(2) Using LIKE also doesn't seem to work as expected!!
SELECT '~' + Column1 + '~' AS [Using LIKE Normal Way] FROM #Dummy WHERE Column1 LIKE 'a'

--(3) This way of comparison using LIKE WORKS!
SELECT '~' + Column1 + '~' AS [Using LIKE R2] FROM #Dummy WHERE 'a' LIKE Column1

--Cleanup
DROP TABLE #Dummy

I was about to write in that the behaviour around trailing spaces may not be the same always, but Mitesh & Vadivel have already mentioned it.

Also, please note that the behaviour around storage of trailing spaces also differs based on the setting of ANSI_PADDING (ON by default). Here's a simple test:

USE tempdb
GO

--Change the ANSI_PADDING setting
SET ANSI_PADDING OFF
GO

--Create a test table
CREATE TABLE TrailingSpacesCheck (RowId INT IDENTITY(1,1),
                                  RowValue VARCHAR(20)
                                 )
GO

--Insert some test data
INSERT INTO TrailingSpacesCheck (RowValue) VALUES ('  Nakul'),
                                                  ('Nakul  '),
                                                  ('Nakul')
GO

--Select the data
SELECT RowId, RowValue, LEN(RowValue) AS NumberOfCharacters, DATALENGTH(RowValue) AS NumberOfBytesUsed
FROM TrailingSpacesCheck
GO

/*
---------------------------
RESULTS - ANSI_PADDING OFF
---------------------------
RowId   RowValue	NumberOfCharacters	NumberOfBytesUsed
1         Nakul	         7	                   7
2       Nakul	         5	                   5
3       Nakul	         5	                   5

------------------------------------
RESULTS - ANSI_PADDING ON (Default)
------------------------------------
RowId   RowValue	NumberOfCharacters	NumberOfBytesUsed
1         Nakul	         7	                   7
2       Nakul	         5	                   7
3       Nakul	         5	                   5

*/


--Restore the ANSI_PADDING setting
SET ANSI_PADDING ON
GO

--Cleanup
IF OBJECT_ID('TrailingSpacesCheck') IS NOT NULL
BEGIN
    DROP TABLE TrailingSpacesCheck 
END
GO

trailing spaces = spaces at the end of the string ? I do think so. for a fixed (N)char(m) string, the stored value is including the spaces needed to have the m correct length. for a (n)VarChar(m) string, the spaces at the end of the values are rubbed off to gain storage space.Not really useful if m is low ( 10 for example ) but the gain may be important if the average size of the values are 20 for m= 100. Even there is an overhead of 32 bits because of the storage of the real length of the stored value, but ridiculous in comparison of possible 80 useless bytes. Another annoyance, the comparisons of values are longer to do because the comparison is done on strings having the same size, but it seems that the wasted time is very small. The main problem is the less important size of the stored value ( a drive is never cheap ) Anyway, an excellent and useful tip created by satyajit. I hope to retrieve next tips as good as this one

@Patrick: Regarding the following statement:

for a (n)VarChar(m) string, the spaces at the end of the values are rubbed off to gain storage space

I don't quite agree. You can see from my example script above that even in (n)VARCHAR(m) string, depending upon the value of ANSI_PADDING, the spaces at the end of a string may be stored (and not rubbed off) which can be proved by the change in the number of bytes consumed for the storage.

wow....never knew a space has so many things in sql server(also I am wondering if a space deserves this much variation and thus attention)... thanks nakul,vadivel,Patrick and mitesh... I will have to go through the examples mentioned before any reply

Sorry guys could not get back soon due to work. My take is ANSI_PADDING affects only storage not comparisons.I thought it would be a good blog topic so please go through below for details and examples

http://beyondrelational.com/modules/2/blogs/381/posts/15760/the-amazing-trailing-space-theory.aspx