avoid Padding - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

avoid Padding

Author

Topic

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-10-05 : 07:02:11

Hi People,
I had created a database with tables having columns of nchar(size) type. I then moved my this database to a new database with nvarchar(size) type through 'Backup' and 'Restore' activities.
The new database dosent seem to have table with data not Padded.
I still can see data with same length.
Can i avoid padding my columns with this used bytes while doing 'Back up' and 'Restore' activities.

Thank you.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-06 : 00:23:57

Don't understand what's your problem. After restoring, target db is exactly same as source db.

Kristen
Test

22859 Posts

Posted - 2007-10-06 : 05:29:38

Changing column from CHAR to VARCHAR will NOT trim the spaces off the end. You will need to do an update if that's what you want.


CREATE TABLE TEMP_071006
(
	MyCol	nchar(100)
)
GO

INSERT INTO TEMP_071006(MyCol) VALUES( 'xxx')

SELECT DATALENGTH(MyCol), MyCol
FROM	TEMP_071006
GO

ALTER TABLE TEMP_071006 ALTER COLUMN 
	MyCol	nvarchar(100)
GO

SELECT DATALENGTH(MyCol), MyCol
FROM	TEMP_071006

UPDATE TEMP_071006
SET	MyCol = RTrim(MyCol)

SELECT DATALENGTH(MyCol), MyCol
FROM	TEMP_071006

GO
DROP TABLE TEMP_071006
GO

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources