Rizwan GDB CS403 - Database Management Systems Closing Date Thursday, July 28, 2016

[Malik Rizwan BbLu]

Let suppose you are working as a database controller in a renowned company which has a huge database system that change recurrently. A lot of transactions, searches and DML operations are being processed daily. It is obvious that these operations facilities are being provided by different types of query languages.

Following are the two cases;

• The organization wishes to speed up its retrieval process of data from database table(s), having no Unicode characters and also demands to save hard disk space.
• The organization wishes to speed up its retrieval process of data from database table(s), having Unicode characters and also needs not to save hard disk space.

For above mentioned cases, you are asked to create columns (table) of database with suitable data types meeting to the following conditions. 

• The column (Table) will hold a fixed-length Unicode characters like Japanese, Chinese, French, Arabic and so on characters. 
• The data stored in a column is Unicode or multilingual and is of variable length.

Suggest the suitable Data Types, NCHAR, NVARCHAR, CHAR and VARCHAR, for the above mentioned situations with reasoning; so that the retrieval of information from the column (tables) would be fast enough.

Note: In the above scenario you only read from columns (table) and not write to columns (table).

For every situation explain your answer with suitable arguments.

[Awais Jutt]

[SK(MS-SE)]

wesy to meri baat ap logu ko samjh ati nai laking phir b i share my practical experience of current product we are working on , 

Simple Answer in a nutshell is 

You need to use VARCHAR with Collate  Arabic_CI_AI, 

ksi b Dataase ki collaate is tra set krty hain.

 ALTER DATABASE YOURDB COLLATE Arabic_CI_AI; 


Now Question is Why not NVACHAR?

NVARCHAR having Performance issue due to "Double Byte Storage" Disk space is not the issue but memory and performance will be. Double the page reads, double index size.

Till this point your GDB is complete. but main hamesha fact ki base pe baat krta hu.

Now Below detail is for those 5% students who are interest in "WHY"   

JIN KO SIRF SOLUTION SE GHARAZ HY PLZ DONT WASTE YOUR TIME TO READ BELOW JUST OPER WALA ANSWER KAFI HY

------------------------------------------------------------------------

AGAR KISI KO KOI ETRAAZ HO TO PLZ APNY EXPEREINCE KI BASE PE SHARE KRY POSITIVE CRITICISM. 

Yeh GDB "SQL Server Collations" se related hy.

we have currently same 100% scenario for our product which is running in South Africa, Japan, Pakistan and Behrain

for the Bank Account Title We have 100% requirement as your GDB

Collations

"Ye aesy rules hoty hain jo k specify karty hain k mutabik hum apny "Character data" ko sorted and compared krty hain  based ksi khas languages ki base pe"

Every Collation has name and type

CaseSensitivity

– CI   specifies case-insensitive,CS specifies case-sensitive.

•AccentSensitivity

–AI specifies accent-insensitive, AS specifies accent-sensitive.

•KanatypeSensitive

–Omitted specifies kanatype-insensitive, KS specifies kanatype-sensitive.

•WidthSensitivity

–Omitted specifies width-insensitive,WS specifies width-sensitive.

•

BIN, BIN2

: Specify a binary sort order

SQL Server main kuch renound Collate k name bata deta hu jinki detail aap net se b le skty hain

Latin1_General_CI_AS :
Estonian_CS_AS
Latin1_General_BIN

Arabic_CI_AI
For Detail please see the attached Presentation 


Feel free to communicate for any question

[✿ ƊiyA Ⱬ@hra ✿]

acha jo bhi hy ap ki "why" ko bhi prha mein ny or is br lag raha k wakai sahi bol rahy ho ap, so zara tarteeb sy bta sakty ho ap k kon sa sahi hy or thora solid reason k sath us ko explain kr do ap plz.

[+ ◦•●◉✿Dua✿◉●•◦]

kia just ak type he batane hay because 2 different condition de hoe has

The column (Table) will hold a. (((fixed-length Unicode ))))......characters like Japanese, Chinese, French, Arabic and so on characters.

The data stored in a column is .....((((Unicode or multilingual and is of variable length.))))))))

[SK(MS-SE)]

jab aap varchar lety ho suppose of length 300 but ur data length is fixed 20 then apki 280 waste nai hoti 

so column varchar would be fine, baki ap mry reply pe n "International_Text.pdf" pe is concept ko samjh skty ho

[muhammad hafeez awan]

varchar [ ( n | max ) ]
Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms forvarchar are charvarying or charactervarying.

what this non-unicode string in it. can u explain it for me.it see this definition on web site. https://msdn.microsoft.com/en-us/library/ms176089.aspx 

[princess]

yes, varchar is suitable ans for this quary. 

[princess]

VARCHAR: Since it is variable length it takes less memory spaces is the  suitable Data Type  for the above mentioned situations.

[Muhammad Faisal]

Student's Message:[BC140402012]

Subject: Q

assalam walaikum  respected sir.    field and coloumn are same thing?  and please clear to me what should i do in this gdb.?  thank you

Post Your Comments Other Students' Comments: 0

Instructor's Reply:

Dear student,   We can use it as interchangeably but there is some difference between them. Database Column: like a spreadsheet column Database Field: like a spreadsheet "cell" (a specific column of a specific row) In simple words, you have to tell the data type for both conditions for first case and same as for second case

[Akram khan]

Oh it was my query to instructor.
A sa bhe hota ha

[Umar Idrees]

Ans:-

Definitions:-

char [ ( n ) ]

Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.

varchar [ ( n | max ) ]

Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms for varchar are charvarying or charactervarying.

Arguments:-

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

Objects that use char or varchar are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause. The collation controls the code page that is used to store the character data.

If you have sites that support multiple languages, consider using the Unicode nchar or nvarchar data types to minimize character conversion issues. If you use char or varchar, recommend the following:

•     Use char when the sizes of the column data entries are consistent.
•     Use varchar when the sizes of the column data entries vary considerably.
•     Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

Definitions:-

nchar [ ( n ) ]

Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000. The storage size is two times n bytes. When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the string, the storage size of n bytes can be less than the value specified for n. The ISO synonyms for nchar are national char and national character..

nvarchar [ ( n | max ) ]

Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

Arguments:-

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

• Use nchar when the sizes of the column data entries are probably going to be similar.
• Use nvarchar when the sizes of the column data entries are probably going to vary considerably.
• sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysname is used to reference database object names.
• Objects that use nchar or nvarchar are assigned the default collation of the database unless a specific collation is assigned using the COLLATE clause.
  
  Just submitted this answer :)

[122bps4ftbw76]

• The column (Table) will hold a fixed-length Unicode characters like Japanese, Chinese, French, Arabic and so on characters.

Answer:

we use NCHAR because Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000. The storage size is two times nbytes. When the collation code page uses double-byte characters, the storage size is still n bytes. Therefore,NCHAR is best for this.

• The data stored in a column is Unicode or multilingual and is of variable length.

Answer:

NVARCHAR is used in this questions because Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes.Therefore, NVARCHAR is best for this.