May 5, 2005 at 12:23 pm
Hi all...
I am building a new database and my Members table includes some common fields like username,password,email,name,surname and etc...
1)so which data type i must use for them for the BEST performance...i heard varchar covers more space but faster with sql commands...so is there any advice that u can give me about some performance clues.
2)And another question when i insert a string to nvarchar field limited with 20 char...then i insert for exm. 10 char string...but when i open the database and highlight the inserted data i see 20 characters used...(first 10 character is my string and the rest is full of space)
i dont understand why... my string is just trimmed string but am i must do another server side trim???
thanks a lot for your helps i really need this...
have a nice day
May 5, 2005 at 1:26 pm
varchar uses 1 byte to store each character, nvarchar use 2 bytes to support multiple language requirements.
By default, "set ansi_padding" is on, Trailing blanks in character values inserted into varchar columns are not trimmed. You need set it off when create or alter table.
SET ANSI_PADDING OFF does not apply to nchar or nvarchar. SET ANSI_PADDING is always ON for nchar and nvarchar.
May 5, 2005 at 2:53 pm
If you don't explicityly need unicode or double-byte characters then stick with CHAR, VARCHAR, TEXT etc rather than NCHAR, NVARCHAR, NTEXT.
I don't use CHAR very often because it is a fixed length field and most text data is variable length.
The exceptions I use are compulsory postal code fields, telephone/fax number fields and Geodemographic codes.
I also try and stay away from large VARCHAR fields. A TEXT field can hold up to 2Gb data but in your actual table only the pointer is stored in your record rather than the actual data itself.
It is possible to force TEXT fields to store an amount of data directly in your record. See http://www.sqlservercentral.com/columnists/bknight/textinrow.asp
The other thing to watch is that if you use replication the default size that SQL will replication is 64Kb. You can up this to 2Gb but you better have a good reason for wanting to do this.
May 5, 2005 at 4:02 pm
thanks a lot for your answers...
i couldnt find "ANSI_PADDING off or on" setting...
i must use unicode so nvarchar sure coz it must support multilanguage...so if i limit nvarchar field to 20...then for exm my string is 10 char long...cant i trim it or how?
because when i try to insert 10 char to limited to 20 char field it inserts but it covers 20 char length(10 char my string+the rest 10 char is full of space)
i hope i could explain my problem...where am i do wrong?
thanks again
May 5, 2005 at 4:49 pm
by the way i think my second problem is about inserting method...
i use this method with java.
result.setString(1,Username);
username is a string and includes trimmed username value....
but although it is trimmed it fills the field with spaces after username...
why can it bee?
May 6, 2005 at 2:28 am
I think you are getting confused with the byte storage rather than the physical character storage.
NVARCHAR(10) and VARCHAR(10) can both take 10 characters but NVARCHAR will store it in 20 bytes.
Triming strings list SELECT LTRIM(RTRIM(MyColumn)) FROM MyTable
May 6, 2005 at 12:13 pm
ANSI_PADDING is a server configuration value. In Enterprise Manager, right click on the server, select Properties. Click on the Connections tab. You'll see 'ANSI padding' in the list that comes up.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 6, 2005 at 1:05 pm
thanks again...
no i am not confusing the byte values i meaned the number of character storage...
and my problem is on inserting values...
for example:
mystring="helloworld"
rs.setString(1,mystring); (by the way my field is nvarchar with max 20 length)
everything ok but when i open the database and return the rows i see my string like below.
mystring="helloworld(space ten times like this' ')" (10 char is my string the rest 10 char full of space)
but it must be like this;
mystring="helloworld" isnt it?
so is it normal? i am not expert but i dont think its normal...so how am i gonna correct it???
thankss a lot really i am appreciated by your helps...
May 9, 2005 at 2:11 am
Well, as VARCHAR/NVARCHAR shouldn't pad the string that implies that either your variable is paddding or the setString function is padding it.
What happens if you run
SELECT databasepropertyex(DB_NAME(),'IsAnsiPaddingEnabled')
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply