Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to develop a CMS which will support two Language English, Arabic. This CMS will be a sort of Article Publishing site. While designing & analysis i found that some articles are more than 8000 characters in length. My table has some column as

PageID int,
PageTitleEnglish nvarchar(200),
PageTitleArabic nvarchar(200),
PageDescEnglish nvarchar(500),
PageDescArabic nvarchar(500),
PageBodyEnglish nvarchar(max)
PageBodyArabic nvarchar(max)

If i keep PageBody as nvarchar(4000) then i a limited to 4000 characters and if i have to store Arabic version then i need 16000 bytes (As Arabic is Unicode and take 3 time more space then ASCII).

So i am only left with option of defining PageBody as nVarchar(max), This will have it downside from performance point of view. My actual question is if some data in PageBody column is less than 4000 characters will it MS SQL Store than data in inline column or separately in the database.

I looked for this on Google also but didn't find any relevant answer and how i can improve performance in such scenario.

Any suggestions for best practice for such design of multilingual CMS are welcome.

I need to Support Only two languages Arabic & English

share|improve this question
    
Will you always have English and Arabic? Or maybe just one optional? If so, will one always be mandatory? Do you expect more languages later? –  gbn Dec 30 '11 at 10:47
add comment

migrated from stackoverflow.com Dec 30 '11 at 15:15

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 8 down vote accepted

An nvarchar(max) value will be stored "in-row" if it is short enough.

The default behaviour can be modified using sp_tableoption, "large value types out of row" option. I wouldn't bother. The DB engine will manage this efficiently by itself.

As for design, there are several ways of doing this based on your model:

  • Will you always have both English and Arabic?
  • Can one be optional? If so, will one always be mandatory?
  • Do you expect more languages later?

1. Separate tables

That is, you can split off the separate languages into different tables.
This allows table level collations rather than column level ones

It allows allows more rows per page and more chance of in-row LOB storage

PageParent

  • PageID int,
  • PageOtherInfo...

PageEnglish (note varchar may be OK here)

  • PageID int,
  • PageTitleEnglish varchar(200),
  • PageDescEnglish varchar(500),
  • PageBodyEnglish varchar(max)

PageArabic

  • PageID int,
  • PageTitleArabic nvarchar(200),
  • PageDescArabic nvarchar(500),
  • PageBodyArabic nvarchar(max)

2. Separate rows

Or have a languageID column to support several languages.
This has the drawback that collation will be fixed for all languages which means poor sorting/filtering

PageParent

  • PageID int,
  • PageOtherInfo..

Page

  • PageID int,
  • LanguageCode,
  • PageTitle nvarchar(200),
  • PageDesc nvarchar(500),
  • PageBody nvarchar(max)
share|improve this answer
1  
@MikeNakis: I've updated my answer to spell this out for any other relational theorists –  gbn Dec 30 '11 at 12:02
1  
I removed my downvote. I have to admit that a) I did not notice the second section of your post and b) I had not given any thought to the issue of the collating sequence. (A voice was telling me that downvoting someone with 116k rep is bound to be regretted, but I did not listen to it.) –  MikeNakis Dec 30 '11 at 12:25
1  
@MikeNakis: the collate clause will invalidate the use of indexes in a query. Effectively it is a function on the column. See point 2 here simple-talk.com/sql/t-sql-programming/… –  gbn Dec 30 '11 at 13:35
2  
In SQL Server you could have non persisted computed columns e.g. PageTitleEnglish AS PageTitle COLLATE Latin1_General_CS_AS then create a filtered index on them. Not that I would. –  Martin Smith Dec 30 '11 at 16:00
1  
@gbn Since the issue of collation is a separate one, I started another question here: stackoverflow.com/questions/8683229/… –  Mike Nakis Dec 31 '11 at 8:40
show 13 more comments
  • MS SQL Server has a fixed page size of 8KB.
  • A row is never splitted over several pages, but several rows can share a single page.
  • nvarchar(max) and other BLOB data may however be stored outside of the row/page.

This means that for everything to fit into one row, the sum of all sizes must be less than 8K. If it doesn't, SQL Server will store the BLOBs outside of the row/page.

Are the amounts of data so big that this really causes a performance problem?

As another option, you could perhaps change your databasestructure to have seperate rows for english and arabic pages, and include a language-code column instead. Then you won't have to fit both the english and the arabic text in the same row, and that would also make sense when fetching data, as you probably wouldn't need to fetch english and arabic at the same time.

share|improve this answer
    
Thanks, I didn't get you point "As another option, you could perhaps change your databasestructure to have seperate rows for english and arabic page". What you are saying makes sense but i am bit confused can you give me an example how i can store them in separate rows or where in DB i can make such changes. –  StudentDubai Dec 30 '11 at 11:38
1  
You'll have collation issues when you try to sort or filter because one collation on the one column will not be good for both languages. –  gbn Dec 30 '11 at 12:00
    
can we resolve this issue by defining collation at column level. I mean define Arabic collation for Arabic column. Will that resolve issue for search –  StudentDubai Dec 30 '11 at 12:05
    
@gbn is right that there may be collation issues with storing everything in one table. –  Arjan Einbu Dec 30 '11 at 12:20
    
@StudentDubai: See gbn's answer for "2. Seperate rows". He now covers it! –  Arjan Einbu Dec 30 '11 at 12:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.