Hi,
I wonder if there are any drawbacks with NVarChar(max) contra e.g. NVarChar(40) when it comes to performance and amount of storage used?
Are there other arguments to use e.g. NVarChar(40) than that you have more control of how long the strings are when you set the upper limit?
I'm using Sql Server 2005.
Tomsi
Hi,
I've never really used the SQLDatasource, I usually fill a dataset & use that. When would you use an SQLDatasource? I notice it doesn't return dataviews but I also noticed that you don't need to open the conenction. Any ideas on best practice?
Many thanks
I am trying to compile a case of why DTS packages need to be migrated to SSIS packages. Is there any document, whitepaper which can provide me drawbacks in DTS?
Thanks,
Vijay.
What is the answeof this ?
View 7 Replies View RelatedHi,
I have a question relating to XML and SQL. My company currently runs a website which allows its clients to log in, view their accounts and transaction history online. The website is totally read only with the exception of changing passwords.
The data is taken from our back office system overnight which runs an oracle 8i database (we cannot like our website to the database due to the agreement we have in place with our software supplier). The data is written to a CSV file which is then converted into XML. The XML file is saved to the webserver and is referenced by the website.
The structure of the website has a relationship where the Client has a Manager who can see their clients accounts, a Branch level that can see all of their Managers and the underlying clients and then finally a company level that sees everything.
We are finding that using XML is causing a real issue in performance and I was wondering if migrating the website to SQL server would improve the performance of the queries etc .
Any advice would be gratefully recieved
Lee
I was just wondering what if someone can summurize the benefits between using Access and SQL for an ASP.NET 2.0 website? Can you use an Access database that doesn't have SQL installed on it with ASP.NET?
View 3 Replies View RelatedI'm designing a database and was researching common conventions and standards. I found that some people don't use the identity column for a primary key because they prefer to create their own unique id number for the column. Whats your opinion?
Ryan
Hi, there is an existing application running on a SQL Server database.Now I have to built a different app, which will have two common tableswith the existing database (Customers, sales)Is there any benefit (performance mainly) creating the tables for thenew app in a new database? (On the same machine and the same instance)*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies View RelatedI find that in Database Exporer in Visual Web Developer, I can specify a DB in two ways, as exemplified here:
Data Connetctions + ke_test_db.mdf + ssftec023-lsqlexpress.master.dbo + ke_test_db2.mdf
Experimentation shows that mdf files that can be put under the "sqlexpress" item (ke_test_db2.mdf in the example) are those that SQL Server Configuration Manager Express knows about, whereas files that I create via adding an item in Solution Exporer appear at the top level (ke_test_db.mdf). My question is, is there any advantage or disadvantage to either architecture? I.e., Is there any advantage to being sure that one's DB files appear under the sqlexpress connection item?
Thanks!
-Kurt Euler
Could someone hit the high points of the benefits of using SQL Server (vs. other databases) with Visual Studio .NET and the .NET Framework.
Thanks
Hey all,
Very simple question from a real n00b: In many of my stored procedures I am repeating a CASE statement where I'm replacing null values with zero, like this:
(CASE WHEN @Field IS NULL THEN 0 ELSE @Field END)
I have many SPs where I have that code repeated literally dozens of times. Is there any performance benefit to creating a function to perform this task and using that instead of the repeated CASE statements? Or am I trying to be too clever?
Thanks!
Ron Moses
ConEst Software Systems
Hi,
When do we need to have multiple log files for one Database.
What performance counters should I measure before deciding to go for multiple log files.
Any comments / suggestions are welcome.
Thanks,
Loonysan
Hi
How can I find out which edition of SQL Server is running on my server ?
Also what would be the difference in terms of features between standard edition and enterprise edition ?
Thanks
Vijay
I've been researching AWE to determine if we should enable this for our environment.
Currently we have a quad core box with 4 gb of RAM (VMware). OS: Windows 2003 std, SQL Server 2005 std. 3GB is not set but will be as soon as we can perform maintenance on the server.
I have read mixed feedback on AWE, either it works great or grinds you to a hault. I would assume that the grinding to a hault is due to not setting the min/max values correctly or not enabling the lock page in memory setting.
We only have one instance of SQL on the server and this box won't be used for anything else aside from hosting SQL services. We do plan on running SSRS off of this server as well.
1. Will running SSRS and enabling AWE cause me problems? Will I have to reduce the max setting by the SSRS memory usage or will it share and play nice?
2. How do I go about setting the Max value? Should it be less than the physical RAM in the box? Right now its set to the default of 214748364, even if I don't enable AWE should this default value be changed?
3. It seems that even at idle the SQL server holds a lot of memory and the page file grows. If I restart the process in the morning, memory usage in taskmon is at 600mb or so. By the end of the day, its up around 2gb. How can I track down whats causing this, should this even concern me?
4. The lock Page in memory setting worries me. Everything I've read on this seems to give a warning about serious OS and other program support degradation. In some cases to the point where they have to restore the settings on the server before they can bring it back up. What are your thoughts on this.
HI, I am running the below method which returns this error: The parameterized query '(@contactdate nvarchar(4000),@dnbnumber nvarchar(4000),@prospect' expects the parameter '@futureopportunity', which was not supplied" Please help.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection)
' old method: Lots of INSERT statements Dim rowscopied As Integer = 0
' first, create the insert command that we will call over and over:
destConnection.Open()Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection)
ins.CommandType = CommandType.Textins.Parameters.Add("@contactdate", SqlDbType.NVarChar)
ins.Parameters.Add("@dnbnumber", SqlDbType.NVarChar)ins.Parameters.Add("@prospectname", SqlDbType.Text)
ins.Parameters.Add("@businessofficer", SqlDbType.NChar)ins.Parameters.Add("@phonemeeting", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate2", SqlDbType.NVarChar)ins.Parameters.Add("@phonemeetingappt", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate3", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentdate", SqlDbType.NVarChar)
ins.Parameters.Add("@appointmentlocation", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentkept", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated", SqlDbType.NVarChar)ins.Parameters.Add("@applicationgenerated2", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated3", SqlDbType.NVarChar)ins.Parameters.Add("@comments", SqlDbType.Text)
ins.Parameters.Add("@newaccount", SqlDbType.NVarChar)ins.Parameters.Add("@futureopportunity", SqlDbType.NVarChar)
' and now, do the work: For Each r As DataRow In sourceTable.RowsFor i As Integer = 0 To 15
ins.Parameters(i).Value = r(i)
Next
ins.ExecuteNonQuery()
'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then
'Console.WriteLine("-- copied {0} rows.", rowscopied)
'End If
Next
End Using
destConnection.Close()
End Sub
I had this question for quite a long time.
It seems the latter one don't take any extra storage space than the previous one.
As long as the real string length is less than 10.
Is that mean the latter one not cost anything?
I once heard the different is when they are in memory. But not sure of it.
Can anyone explain it and provide some official reference on it?
Thank.
I have read over the feature set and I really don't see a huge reasons to jump on the bus. There are a few niceties like IP enhancements, cluster enhancements and maybe bitlocker for the backup drive. Am I missing something?
View 3 Replies View Related
Hi All,
The storage reserverd for nvarchar(m) is 2*m+ 2 extra bytes.
I understood each character requires because it supports unicode format.
Why extra two bytes are required?
Regards
Abdul
Hello,
What is the maximum lenght when using nvarchar(MAX)?
If I want to save a really long text, should be this the data type to use?
I am using SQL 2005.
Thanks,
Miguel
Does anyone know the formula to calcuate the space occupied by an nvarchar(max) column for the purposes of sizing a table?
View 1 Replies View RelatedIs it bad form to use nvarchar(MAX) in place of column types with specific lengths like nvarchar(50)? Are there performance issues? Because to me (a novice), it appears that I would save space by using nvarchar(MAX) everywhere.
Same question applies to varbinary(MAX) as well.
Thanks
Jim
_______________
Jim Geurts
Personal: http://www.biasecurities.com
Work: http://propertycenteronline.com
I have an SSIS pkg, that gets data from Lotusnotes db and loads into SQL Server, using ODBC Driver for Notessql driver . I have a comments, field, in Lotusnotes which has comments>8000 chars in lotusnotes. Hence I created my destn SQL Table with datatype NVARCHAR(MAX) so that it can uplaod the comments that are >8000 chars.
However, every time I run the ssis pkg , the destn table is bringing only 250 chars ONLY in the comments field , and truncates the rest of the chars.
And I see the reason is because, on my ODBC Serttings for Lotusnotes, There is a section for NOTESSQL options
where the "Max length of text fields" set to 254. hence it brings only 254 chars into SQL.
However, If I increase that option "Max length of text fields" to 8000 or some higher number, the sssis package errors out on the datareader source itself, err is"
"The output column 'comments' has a length that is not valid.
Pl advise how can I load all the comments which are more than 8000 chars from lotus notes into SQL, AND KEEPING THE ODBC settings, the "Max length of text fields" TO 254 Only.
I have a scenario to sort on an nvarchar (50) field and I need to keep any changes to the sproc in the ORDER BY clause if possible. This field will contain strings such as...
abc-217c, abc-15a, abc-9a, abc-7b, abc-17ar, etc...
The issue I'm having is when the records are sorted, they are returned as...
abc-15a, abc-17ar, abc-217c, abc-7b, abc-9a,etc...ordering numerically on the first numeric character in the string ie, 1,1,2,7,9)
However, I need the numeric component to be treated as a whole number and order in this fashion...
abc-7b, abc-9a, abc-15a, abc-17ar, abc-217c (7,9,15,17,217, etc)
I feel pretty sure that this issue has come up before...can anybody provide a working example that would provide a simple(or not so simple) solution?
Hello, I have a column in my table that is a nvarchar.The information that we need to store in this column has exceeded the limit.Can we simply change the datatype to 'text' ? Will there be any issues that we might experience? Thank you in advance.
View 1 Replies View Relatedi 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.
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
Hello all,
I'm trying to convert a nvarchar datatype to int
(ie:1234-56-78 to 12345678) . These values are primary keys in two tables. Both these tables have 3500 rows of this key type. I want to convert this to a int so I can make it a AutoNumber primary key so I can increment it. Is this possible? If so, how do I do it. Do I need to delete the dashes first some how? I fairly new to database adminstration, so any guidance will be greatly appreciated.
Thank You
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)
I am new to SQL Server and would like to hear opinions on pros and cons of using nText vs. nVarChar data type for following uses:
URLs (up to 260 bytes)
Addresses (50-300 bytes)
Descriptions and comments (50-2,000 bytes)
Memos (up to 8000 bytes)
TIA.
Hello,
I need to save some news text in an SQL table. The text can be long.
1. Should I use nvarchar(MAX) or nText?
2. And what is the difference between nText and Text?
I am using SQL 2005.
Thanks,
Miguel
Dear All,
i'm trying to convert the datatype from ntext to nvarchar.i'm getting error. is it not possible?
Vinod
Even you learn 1%, Learn it with 100% confidence.
Hi all, Please help.
I have created a new SQL dB and imported a table from Access to SQL.
my date columns in Access have been imported as nvarchar(50).
all dates are in the format dd/mm/yyyy.And there is no bad data
My problem is that in the table design of my new SQL Table, if I change the datatype from nvarchar(50) to datetime I get the error "Arithmetic overflow error converting expression to data type datetime"
I know this is telling me that there is a problem with the format of some of the dates but all my dates are defo dd/mm/yyyy ,none of the data has format mm/dd/yyyy ie a 13th month.I know this because I cut the data down to just 2 pieces of data.
can someone help me get the format into datetime please
Ray..
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