|
when i should use nvarchar(max) type and when not?
|
|
|
|
|
I can't say when to use or not, but this link[^] should help with explaining the differences. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
An nvarchar column can stores unicode data but requires twice the space compared to varchar.
A varchar column is restricted to an 8-bit codepage and is therefore limited to one or a few alphabets/languages at a time. Codepage incompatabilities are a PITA, and Unicode is the cure for codepage problems.
Normally you take a performance hit using nvarchar as it takes twice the space in both memory and disc. But if the client uses a different codepage than the server you might take a performance hit on the conversion of the varchar instead.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Use it for strings; not for numerical and temporal data. 
|
|
|
|
|
I don't really understand your question. Are you asking:
- when should I use nvarchar instead of varchar?
- when should I use nvarchar(max) instead of a specified size such as nvarchar(100)?
- when should I use nvarchar(max) instead of ntext?
- when should I use nvarchar(max) instead another datatype?
- something else
- all of the above
These are all different questions (some of which have been answered already).
|
|
|
|
|
Problem we are Facing Currently: Records getting inserted as only one during the process of updating the server.
Previously, all the given records get inserted into the database table during the updation process.
"updateprice" is the .mdb exists locally in the same folder where the calling file for updating records exists. It contains record data to be uploaded to the server database. It acts as an temporary storage database.
The records to be updated to the server are either in an notepad or excel. Whatever be the count in records, previously it was updated fine. "rsTextData.Update" is line 86. I'm blinking where it fails.The error which we're facing and the code is given below for the assistance.
Error Showing as
****************
1 record(s) converted
File Name:DTRinggit081110Ringgit Extends Downtrend Against Greenback
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_text_data'. Cannot insert duplicate key in object 'text_data'.
/data_maintain/News.asp, line 86
code
****
<table width="100%" cellspacing="0" cellpadding="0" align="center">
<tr><td height="25" class="mtext" background="../images/top_bar.jpg" align="center"><font color="#FFFFFF"><b>ADMINISTRATION</b></font></td></tr>
<tr><td height="10"></td></tr>
<tr><td height="20"></td></tr>
<tr><td width="100%" class="mtext">
<p style="margin-left: 10; margin-right: 3">
<center>
<b> Uploading Text Data in progress...<br>
<br>Please wait..</b>
</center>
<%
set myconn=server.createobject("ADODB.connection")
myconn.open "updateprice"
myconn.CommandTimeout=0
set rsData = Server.CreateObject ("ADODB.Recordset")
NewsCodeSelectSql="Select * from text_data"
set rsData=myconn.execute(NewsCodeselectsql)
code = 0
intRecCount = 0
set myconn1=server.createobject("ADODB.connection")
myconn1.open "DSN=commodities;uid=fbspl07;pwd=07#web&;"
myconn1.commandtimeout=0
Response.Write("1 record(s) converted<br>")
do while not rsdata.eof
Set cmdTemp=Server.CreateObject("ADODB.Command")
cmdTemp.CommandType=2
sql="select * from text_Data where 1<>1"
set rsTextData=Server.CreateObject("ADODB.Recordset")
rstextdata.locktype=adLockOptimistic
rsTextData.Open "select * from text_Data where 1<>1",myconn1
msgbox rsTextData.RecordCount
rsTextData.Addnew
response.write "File Name:" &rsdata(3)
'response.write "Title: " &rsdata(4)&"<br>"
rsTextData("Text_id")=rsdata(0)
rsTextData("Date1")=rsdata(1)
rsTextData("Comname_id")=rsdata(2)
rsTextData("Filename")=rsdata(3)
'response.write "Title= " & rsdata(4) &"<br>"
title1=replace(rsdata(4),"("," ")
title1=replace(title1,")"," ")
title1=replace(title1,":","-")
response.write title1
'response.end
rsTextData("Title")=title1
'rsTextData("Title")= rsdata(4)
rsTextData("Source")=rsdata(5)
rsTextData("isspot")=rsdata(6)
rsTextData("Module_type")=rsdata(7)
rsTextData("Submodule_type")=rsdata(8)
rsTextData("Time1")=time()
rsTextData.Update
rsTextData.close
rsData.movenext
code = code + 1
If code mod 25 = 0 then
Response.Write(code &" records converted<br>")
end If
loop
response.WRITE("<br><br><b> Text Data is Inserted Successfully....</b><br><br>")
%>
<p align="RIGHT"><a href="javascript:history.back()" target="_parent"><font face="Arial" size="2">Back</font></a> </td></tr>
<tr><td height="20"></td></tr>
</table>
M.Sworna Vidhya
|
|
|
|
|
swornavidhya_m wrote: Violation of PRIMARY KEY constraint 'PK_text_data'. Cannot insert duplicate key in object 'text_data'.
There's already a record in the "text_data" table that holds the same value in the "Text_Id" field as the one that you're trying to insert. That fails, since a primary key needs to be unique; you can't add two records in the table with the same key.
Do a SELECT on the table, using the value from rsdata(0) - you'll see that it's already in there.
I are Troll 
|
|
|
|
|
Eddy Vluggen wrote: There's already a record in the "text_data" table that holds the same value in the "Text_Id" field as the one that you're trying to insert. That fails, since a primary key needs to be unique; you can't add two records in the table with the same key.
Actually, the datas stored in notepad or excel. Then those stored values are copied to "updateprice.mdb". The values from "updateprice.mdb" are transferred to the "text_data" table in server with the method called for updating the values to the server. In this method only i'm facing the problem.
The "Text_Id" field is the unique one and won't have any duplicates in any case. Also, the data team members who are in-charge for inserting datas to server, said that the server updation by inserting is done properly when the record count<=3. If the recordcount exceeds 3, then the error is showing as:
Error Showing as
****************
1 record(s) converted
File Name:DTRinggit081110Ringgit Extends Downtrend Against Greenback
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_text_data'. Cannot insert duplicate key in object 'text_data'.
/data_maintain/News.asp, line 86
I'm blinking and stuck with datas getting updated to the server. Kindly help me.
M.Sworna Vidhya
|
|
|
|
|
swornavidhya_m wrote: The "Text_Id" field is the unique one and won't have any duplicates in any case.
You'll get the error that you described if you try to insert one that's already there.
swornavidhya_m wrote: If the recordcount exceeds 3
What's the value of the PK-field (Text_Id) in those three records?
I are Troll
|
|
|
|
|
The value of TEXT_ID will be as like 1779562, 1779563, 1779564, and so on.
|
|
|
|
|
There are three records already in the database you said. What are *those* values? (List them all three please) And what is the value of the PK-field of the record that you're inserting?
I are Troll
|
|
|
|
|
I have a stored procedure with two params in sql2005. If I excute the stored procedure it takes 2 min but i copied the same query in new window, declare variables and run the query it takes 1sec. What is the problem.
|
|
|
|
|
Can you post the query?
I are Troll
|
|
|
|
|
Is it having dynamic queries? Without watching the code, it is quite difficult to find the problem. Can you post relevant code? (Please do not dump everything. Just provide relevant code and use code block link.) 
|
|
|
|
|
|
Good info!
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Thank you, I've been looking for that article for ages, it (or a similar article) was posted some time ago as a similar response and I didn't grab it then.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
A friend of mine just informed me about that this week. 
|
|
|
|
|
When you encounter such a performance difference, does it then matter, which one was executed first? Or in other words: is the stored procedure faster after you run the normal query?
SQL Server can cache execution plans and results, maybe that's the issue here.
|
|
|
|
|
how to write a query change the datatype for primary key coulm in atable using sqlserver 2008.
Thnaks In advance
Ch.Gayatri
|
|
|
|
|
use ALTER TABLE, you might also consider this[^].
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i tired but its did not worked ou becoz my primary key is used as Fkey in so many tables.
|
|
|
|
|
Then you have a serious design problem, what are the data type changes you want to affect?
You could do this right by:
Identify and drop all the FK referencing the table
use alter table to change the data type on the redacted table
change the data types on all the fk fields
recreate the fk you identified and dropped.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You are in big problem, just drop refference and recreate them. Thanks
|
|
|
|
|
We have a maintenance plan set up to run on a set period taht includes indexing the databases. For some reason, the index tasks is faling with the following:
Failed:(-1073548784) Executing the query "ALTER INDEX [PK_redactedname] ON [dbo].[tblredactedname] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )
" failed with the following error: "Online index operation cannot be performed for index 'PK_redactedname' because the index contains column 'redactedfield' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I'm not an expert so I could be reading this wrong but the field that it's referring to...is not an identity field nor is it indexable so why is it trying? The message code is unfortunately too generic judging from google fu. I've tried looking around to see if there's a setting I need to override so that it doesn't try to index this table but to no effect.
Can anyone offer some pointers?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
