How To Reclaim Space In Columns Changed From Nvarchar To Varchar
Jul 23, 2005
Hi,
This is probably an easy question for someone so any help would be
appreciated.
I have changed the columns in a table that where nvarchar to the same
size of type varchar so halve the space needed for them.
I have done this a) becuase this is never going to be an international
application, b) we are running out of space and c) there are 100
million rows.
I have done this with the alter table statement which seems to work but
the space used in the database hasn't altered.
I'm presuming that the way the records are structured within the table
there is just now more space free inbetween each page???
Is there a way or re-shrinking just an individual table and free up
some of the space in there or am i missing the point somewhere?
Thanks in advance,
Ian
View 4 Replies
ADVERTISEMENT
Aug 2, 2006
Hello,I tried to make what one reply advice to recover the disk space afterDROPed columns but it doesn't work. I did DBCC DBREINDEX to the tablebut nothing changes.Any other solution?Ignacio
View 5 Replies
View Related
Jul 20, 2005
I have an application with highly compressable strings (gzip encodingusually does somewhere between 20-50X reduction.) My base 350MBdatabase is mostly made up of these slowly (or even static) strings. Iwould like to compress these so that my disk I/O and memory footprintis greatly reduced.Some databases have the ability to provide a compressedtable, compressed column, or provide a user defined function tocompress an indvidual Field with a user defined function[ala. COMPRESS() and DECOMPRESS() ].I could right a UDF with an extended prodcedure if I need to but I'mwondering if there are any other known methods to do this in MS SQLServer 2000 today?--Frederick Staatsfrederick dot w dot staats at intel dot com (I hate junk mail :-)
View 6 Replies
View Related
Feb 26, 2014
I know that if I have an nvarchar column I can use an equality like = N'supersqlstring' so it doesn't implicit cast as a varchar, like if I were to do ='supersqlstring'. And then I'll be a big SQL hero and all my stored procedures will run before a millisecond can whisper.
But if I'm comparing an nvarchar column to a varchar column, is it better to cast the varchar 'up' to an nvarchar or cast the nvarchar 'down' to a varchar?
For instance:
cast(a.varchar as nvarchar(100)) = an.nvarchar
or
cast(an.nvarchar as varchar(100)) = a.varchar
Leaving aside non-matching, like (at least I don't think) that SQL considers the varchar n to be equal to the nvarchar ń, what's the best way to handle this?
Pretend for a moment that each column contains a mixed letter and number ID with no accented or wiggly-squiggly Unicode characters; it's just designs clashing.
Is there a performance hitch doing it one way or another? Should I use COLLATE? Should one of the columns be altered?
View 8 Replies
View Related
Mar 19, 2015
Is it possible to reclaim space after rebuilding indexes(shrinking is not an option).
View 9 Replies
View Related
Sep 15, 2014
currently stuck with an issue where I need to reclaim the unused table space in SQL server.
ISSUE: Due to database size issue we have moved all the images from database to filesystem and want to reclaim the unused space now....I have tried shrinking the database and rebuild the indexes but didn't see any difference in the table or database size.
Similarly I have identified many Non clustered indexes on big transactional tables(~ 4 million records) that where not used since GO live 1 year back, so I wanted to drop these indexes to cut down the performance and maintenance overhead , so my question is will dropping these indexes reduces the database size?
View 5 Replies
View Related
Dec 30, 2014
best method to my issue regarding unused space. SQL SERVER 2014 BI EDITTION..I have a table that showed 62% Data, 7% unallocated, and 29% unused space.I ran the ALTER TABLE <table name> REBUILD, which changed it to 32% data and 67% unused.What I do with this table monthly and what I believe is causing this unused space issue is this:Every 1st of the month I get a csv file that holds around 3.2 million rows/15 columns of data. About 3 gbs every time.It's 13 months of data. I remove the last 12 months from the current table and import the new 13 month data.I do this twice a month and have no choice because the data is constantly updated and why we get it twice a month.So I am deleting around 6 gbs of data a month as well as adding around 6 gbs of data a month.
I believe this is why I end up with so much unused space, but just found the REBUILD command, which worked but now a small hit on performance and have too much space in the current table. I have read several times that shrinking the file is no good, but what other way to get rid of the extra space in this table? Also, the table doesn't have any indexes or primary key because of duplicates.
View 5 Replies
View Related
Feb 11, 2015
I have a table 300+GB. it holds 10 years of Data. I need to delete 5 years of data and put it to another server so I can have more space.
If I delete 5 years of data, Transaction log gets so huge and size of the database even gets bigger because of the .ldf file which even gets bigger! I think I can shrink the log file and the data file. Is this the best way to do it?
View 8 Replies
View Related
Feb 25, 2015
I have a SQL Server 2012 DB in Full recovery mode that has never had a TLOG backup. the log is huge 200+GB.
I tried doing a transaction log backup but there is not enough space on the Disk.
How can I reclaim this log space in SQL Server 2012?
View 6 Replies
View Related
Jun 3, 2004
i changed my state table from char 20 to varchar 20 and it still fills it with empty space at the end of the statename
I tried entering it with the trim command still no good
i looked a the sql command made in vs and it says that the variables are varchar
is there something i am missing with varchar
does it only work properly if it is over 50 or does it not change completely when you change from char to varchar
View 2 Replies
View Related
Aug 8, 2005
i have table Table1 with column lastname ,firstname ect.The Data type is varchar.
I have inserted values. I have selected records from this table and assign this records to the TDC control of the HTML .The query returns 130 records.The TDC control has the Unicode delimiter. Whene we select top 7 ,seven records are displayed ,when it is top 8 it does not show any records.
If we delete the Lastname and Firstname values for this 8th record then all the 130 records are displayed.
Why is this happning.Any great minds can help me in this pls
View 3 Replies
View Related
Aug 28, 2007
i have used nvarchar as my datatype in sql server 2000 now
i have decided to change to varchar as i can increase the character length from 4000 to 8000
Do I Lose data if i change the datatype.
View 7 Replies
View Related
Jul 10, 2003
I have a table using nvarchar(for what ever reason which beyond me why its a nvarchar...) that I would like to change to a varchar. There is no unicode in the fields so I don't have to worry about but I don't want to lose any text data. Will coverting the data type lose data?
Thanks
View 9 Replies
View Related
May 16, 2006
So I have an existing table that looks like:
ID BIGINT
VAL VARCHAR(128)
I am converting this table to something that will be multi language compliant. My question is, I know that NVARCHAR's take double the space of a VARCHAR. Do I actually need to double the length of the VAL field to store the same amount of data or does the DB handle that?
Basically I want to store a 128 character NVARCHAR.. do I need to set my table up like this:
ID BIGINT
VAL NVARCHAR(256)
or
ID BIGINT
VAL NVARCHAR(128)
View 3 Replies
View Related
Feb 26, 2007
Hi,I am new to MS SQL. When I create a column in a table, when shall Iuse nvarchar or varchar? Please help.Thanks,Mike
View 5 Replies
View Related
Dec 18, 2006
from the definition, i know that "n" means uni-code. but what is the exact advantage of having nchar or nvarchar over char or varchar?
View 1 Replies
View Related
Oct 5, 2006
Hello, everybody,
I am in need of storing long pieces of text in database, length between 2000 and 4000 characters (nvarchar limit).
If I have a column in a table:
my_text nvarchar(2000)
does this mean that in database the size used will always be 2000*2=4000 bytes at least (+ rounding), independent on what the real length of the text stored is?
Anoter alternative - storing texts in files is expected to become unmanageable very soon, so I would like to keep to the db.
Or, would it be reasonable to have such a table:
my_text_1 nvarchar(500),
my_text_2 nvarchar(500),
my_text_3 nvarchar(500),
my_text_4 nvarchar(500)
Or declaration already means the space for a column is reserved even if it is nullable?
My DBs are MS SQL Server 2000/2005.
View 2 Replies
View Related
Jun 19, 2004
Hello again everyone....
I have another question for everyone....
I am currently cleaning up my database to get its total size down and am not sure how nvarchar and varchar work exactly.
When defining the length of a varchar or nvarchar in enterprise manager, will that effect the size of the entry (as far as data size) no matter what the length of the entry? In other words, will there be a difference in Data Size for an entry with the length of 4 characters with a definition of varchar(4) versus an entry with the length of 4 characters with a definition of varchar(50).
****If there is no difference, is there any reason in trying to best guess the size to give nvarchar or varchar columns? It would seem easier to just define the lengths of columns which need variable lengths to 200 or 400 just to save time in not trying to best guess what the size might be...*****
Thanks ahead for any help...
-Alec
View 3 Replies
View Related
Aug 4, 2006
Hi,
Can someone please explain to me how the datapages in Microsoft SQL Server 2000 works. The pages are supposed to be 8K, that is 8192 bytes of which only 8060 are accessible for data storage (due to overhead).
Now, I currently have a table containing 8 fields. Two of these fields are varchar and should be converted to nvarchar. One of the varchar fields is limited to 255 characters and the other to 4000 characters. When I convert the 255 characters field to nvarchar it works just fine, but when I want to convert the 4000 characters field I get an error from MS SQL saying that it gets to big. Is the error only for the 4000 characters field (which growths to 8000 bytes when using nvarchar instead of varchar) or must the whole table fit into one datapage?
Could a blob maybe solve my problem, or will I face new problems when storing unicode characters in a blob?
Thanks in advance
View 3 Replies
View Related
Jan 14, 2006
I have a table with a Varchar field that will contain encrypted data. Since each byte can have a value from 0 through 255, can I use Varchar or should I change the field to NVarchar? The reason I ask is that during testing, the Varchar field sometimes is truncated, supposed to be 16 bytes but ends up as 5 or 6 or something less than 16.
View 2 Replies
View Related
Mar 9, 2007
Hi,I have a pretty straightforward question to do with variable length fields I hope someone can help me with:When using varchar (or nvarchar), is there any point in specifying a smaller length than the maximum? Does it save space or improve performance at all?ThanksRedit: I suppose the max rowsize is an issue. any others?
View 2 Replies
View Related
Feb 17, 2006
What is the difference between the nvarchar and varchar datatypes? Which should be used?
View 2 Replies
View Related
Apr 10, 2006
What is the diferent between varchar an nvarchar?
View 4 Replies
View Related
May 22, 2006
hi
could any one help me in differentiating between varchar and nvarchar
Thanks in advance
View 4 Replies
View Related
Oct 10, 2007
I have table with a field defined as nvarchar. I want to change it to varchar. I have a stored procedure which defines the parameter @strCall_desc as nvarchar(4000). Are there going to be ay problems with running this sp if I just change the field type as described.
TIA
View 6 Replies
View Related
Jul 20, 2005
HiThe maximum length of a nvarchar could be 4000 characters while that ofvarchar could be 8000.We are trying to use unicode which would require that the datatype forone our fields be converted from varchar to nvarchar. But looks likethis would result in loss of existing data.Is there a way to do this without loss of data?Many thanks.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Mar 14, 2006
I am using SQL Express
I have a very simple function to retreive the maximum value (MemberID) from a member table. The memberID column is in "nvarchar(8)" type.
The following shows the function:
ALTER FUNCTION dbo.GetLastMemberID()
RETURNS nvarchar(8)
AS
BEGIN
RETURN (SELECT ISNULL(MAX(MemberID),'IM000000') FROM MemberInfo)
END
When i run the function, only the first 4 character (say 'IM00') is returned.
If I change the "RETURNS nvarchar(8)" to either "RETURNS nvarchar(16)" or "RETURNS varchar(8)", whole column (8 characters) is returned.
My question: should I use nvarchar(16) or varchar(8) in the function in this case?
Thanks
View 7 Replies
View Related
Apr 11, 2006
Please I know this is fustrating but I really need help with this issue:
I am getting data conversion error when I tried to load data from one SQl table to another SQL table using SSIS.
The source table has a column with data type nvarchar(max). Also the destination table has the same data type nvarchar(max) but I keep getting conversion error when I use SCD transformation.
Error: " Input column "des" (116) has a long object data type of DT_TEXT, DT_NTEXT or DT_IMAGE which is not supported"
I am fine when I use OLEDB destination but I want to do an incremental load.
Is there a quick fix for this?
Thanks
Omon
View 5 Replies
View Related
Jun 26, 2007
what is the difference between nvarchar and varchar
View 3 Replies
View Related
Jun 20, 2007
I have looked at several explinations and I understand the difference between unicode and non-unicode.
I get that the basic idea around storage is "double", 2 bytes instead of 1.
My question is, does the 2 byte instead of 1 byte rule apply even if I am storing a char that doesn't need the full to bytes.
for arguments sake I have a table called "UnicodeTable" and one column called "Letter".
If I store the letter "A" on the first row of the "UnicodeTable" does the size of my database increase by 2 bytes?
View 1 Replies
View Related
May 4, 2004
I have an existing application that relies on a SQL Server database.
I want to switch all varchar fields to nvarchar so it can handle multiple languages.
The database has ~25 tables, many of which have varchar fields. I want to convert them all to nvarchar.
The database has ~150 stored procedures, many of which have varchar fields. I want to convert them all to nvarchar.
Are there any tools out there that would let me convert the tables of my choosing, and the stored procedures of my choosing, so that any 'varchar' mentions are changed to 'nvarchar' ? I've only used SQL Query Analyzer to write queries and use MS Access (and some SQL Enterprise Manager) to make the tables and relationships.
Thanks,
-Bret
View 1 Replies
View Related
Aug 20, 2001
We have few stored procedures that use nvarchar datatype, this was not issue on SQL server 7.0 but in 2000 becomes a big issue.
For example query that runs for 3 minutes in SQL server 2000 by replacing NVARCHAR to VARCHAR the same query runs for 2 seconds.
The biggest challenge that I have deals with tables and user-defined datatypes of NVARCHAR that has been bounded to the table.
How can I alter those without data corruption?
View 2 Replies
View Related
