September 11, 2011 at 8:34 am
I have created table
xyz int
abc nvarchar(500)
zxc nvarchar(4000) and few more filed with nvarchar(500)
my question is ,is it is good idea to make another table where the field with abc nvarchar(4000) and referring id [int]will be there
as it will ease me to query the table and it will be fast as each row rough data will be 1500 bytes max
September 11, 2011 at 11:49 am
Hi
As always, it depends. If "abc" is not needed in each query, then it is definitely a good idea. I suggest to read one of the last SSC articles Speeding up database access - part 4 Fixing expensive queries[/url] and mperdeck's other articles.
Just out of my curiosity, why does your column need to be NVARCHAR(4000)? Why is 4000 the correct size? Is it impossible that your data will ever be larger than 4000 Unicode characters? What brings me to the vice versa question, why does it need to be 4000? Is it possible that 1000 Unicode characters are enough? Don't get me wrong, but column sizes like 4000 (what is the maximum size of that data type) or 500 always feel a bit random to me. Probably you can talk to your clients or project manager to get more details about what data will need to be stored.
Greets
Flo
September 11, 2011 at 11:15 pm
thanx for article u have mention it gave a clear picture now
I have took abc nvarchar (4000) as it will store the summary and i have put the condition that data won't exceed 3500 limit,i know maximum limit of nvarchar is 4000
September 12, 2011 at 2:08 am
Hi,
You could also make it a NVarchar(max) field.
When the data is short or null, all data are stored "on row", otherwise it will be moved to a separate page.
So I would not bother about creating a new table. Let SQL Server handle the I/O for you.
But you should test your solution, to see if it fits you.
Best regards,
Henrik Staun Poulsen
Stovi Software
September 12, 2011 at 4:58 am
Hi
Yes, NVARCHAR(MAX) is an option. However, in my opinion it should only be used if data are really large and/or rarely needed. When rarely needed I'd suggest to set the table option 'large value types out of row', as described in mperdeck's article, to get more rows into each data page.
Greets
Flo
September 12, 2011 at 8:09 am
If you don't need to go out of row, you don't want to. Especially with index reorganize restraints. I agree with Florian. If you don't access the data often, then a separate table can make sense, but think about the access patterns, the storage needs and even how often there is data in this field before you reach for that particular tool.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply