[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

sql server - text vs nvarchar

Posted on 2012-04-11
6
Medium Priority
?
868 Views
Last Modified: 2012-04-12
Hi Experts,

I am having a table in sql server 2005.

I will be reading a text file and insert the file details as string in to the table column.

Columun Name: InputXML

Initially, The data type of this column is "Text".

Since, I got the requirement saying that the input file may have 150k length data,

I changed the InptXML column data type to "nvarchar(max)"

Now, while running the batch, I am getting an error which says "FileGroup is Full and could not allocate space"

I was not sure if the error was comming even if the datatype was text.

I thought using nvarchar is better than text data type.

Please suggest,

1. Why is filegroup full error is comming? and how to solve this? (Currently, I am just clearing the table each and every time, i get the error) Is there is any solution for this?
2. Is it because of nvarchar type?
3. Which data type i should use Text/ nvarchar(max).

Please help.
0
Comment
Question by:RameshLathu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 13

Expert Comment

by:Ashok
ID: 37835938
Try using
VARCHAR(MAX)

Ashok
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37835952
execute this for your table (change MyTable with your real table name).

sp_tableoption N'MyTable', 'large value types out of row', 'ON'

HTH
Ashok
0
 
LVL 13

Assisted Solution

by:Ashok
Ashok earned 400 total points
ID: 37835960
sp_tableoption N'MyTable', 'large value types out of row', 'OFF'

Not sure which one is default if you do not set it manually so
if 'ON' does not work, try setting it to 'OFF'.

HTH
Ashok
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 5

Assisted Solution

by:hafeezmca
hafeezmca earned 1200 total points
ID: 37836058
HI,

Regarding your first & second query:

Unplanned growth of the Data or Transaction log can take up space in the disk. Check if the autogrow option for both Data and log are not set * By percent * .
 
You can see this by selecting Database->properties->Data files tab and Transaction log tab.
 
Please refer the following links for more information:
 

http://support.microsoft.com/default.aspx/kb/315512/EN-US/

If some of your tables are growing faster than you thought then if possible archive and purge them if not you may need to add more space to the disk...
 
To resolve this issue withou a down time you can create new to a different drive by adding new file to PRIMARY FILGROUP


For more information about how to grow and shrink your database and log files, click the following article numbers to view the articles in the Microsoft Knowledge Base:
 
256650  (http://support.microsoft.com/kb/256650/ ) How to shrink the SQL Server 7.0 transaction log
 
272318  (http://support.microsoft.com/kb/272318/ ) Shrinking the transaction log in SQL Server 2000 with DBCC SHRINKFILE
 
317375  (http://support.microsoft.com/kb/317375/ ) A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
 
247751  (http://support.microsoft.com/kb/247751/ ) BUG: Database maintenance plan does not shrink the database
 
305635  (http://support.microsoft.com/kb/305635/ ) A timeout occurs when a database is automatically expanding

Regarding your third query:

SQL 2005 has deprecated the TEXT in favor of VARCHAR(MAX)  .
Use varchar unless you deal with a lot of internationalized data, then use nvarchar.

The default setting for VARCHAR(MAX) is to store its text value in the table structure, unless the text is over 8,000 bytes at which point it behaves like an TEXT and stores the text value in the LOB.

Since you have altered the table, SQL Server did the alter table, it didn't use the default NVARCHAR(MAX) setting of text in row, but kept the text in the LOB and still uses pointers lookups to get the text out of the LOB.

So, execute this command,  UPDATE <<TableName>> SET Inputxml = Inputxml;

Here are good articles:

http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx

http://stackoverflow.com/questions/35366/varchar-vs-nvarchar-performance
0
 
LVL 12

Accepted Solution

by:
Anuradha Goli earned 400 total points
ID: 37836879
using the SQL Server Management Studio. Connect to the server running the Database, open the server/databases, right-click on the database (default name) and click properties. Click on the files tab, change the initial size to the new size(increase size) and click Ok. You can validate that the change in size occurred by going back to the properties of the database.
0
 

Author Closing Comment

by:RameshLathu
ID: 37837278
Thankyou
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question