Vchar Vs Char

Jul 20, 2005

Hi

I have a SQL database with approx., 90m rows. Within the table
there several fields that are varchar 4. On a test server with approx.
200k rows I converted those to char4.

It showed that the database size shrunk by approx. 20 %, why I don't
understand.

My question is would I get a performance improvement for select
queries against those fields when they are char4 as opposed to varchar
4 ?

Thanks

View 1 Replies


ADVERTISEMENT

Char Nchar Vchar Nvchar !?

Feb 14, 2006

Hello
in MS SQL 2000 for text it is possible ti use
char
nchar
varchar
nvarchar

what must be used and for what ?
I want to store normal text with occidental char (accents)

thank you

View 4 Replies View Related

Convert Vchar To Int

Feb 5, 2007

how to convert vchar value to int.
Thanks

View 1 Replies View Related

VCHAR To XML After Table Is Load Via SSIS

Oct 26, 2007


I loaded a vchar column using script trans (SSIS doesn't support XML data types :/) to format the XML data properly. Since the XML data in the vchar column was not encoded, when I try to alter the vchar column to xml after the table is loaded it fails. I assume this is because the parser is having trouble with the content of the data within the xml tags.

This is how I coded the XML rather than using a xml method. "true" places the element end tag.




newXML += FormatElement("CONFERENCE") _

+ FormatElement("NAME") + ConvertToESC(RTrim(Mid(Row.activity, 1, 34))) _

+ FormatElement("NAME", True) _

+ FormatElement("START_DATE") + ConvertToESC(RTrim(Mid(Row.activity, 35, 8))) _

+ FormatElement("START_DATE", True) _

+ FormatElement("END_DATE") + ConvertToESC(RTrim(Mid(Row.activity, 43, 8))) _

+ FormatElement("END_DATE", True) _

+ FormatElement("CONFERENCE", True)


My question is, can I either load the data, using T-SQL, into an XML data type from a vchar column or is there a way to alter the type from vchar to xml without encountering the following parsing error?



Msg 9421, Level 16, State 1, Line 1

XML parsing: line 1, character 64, illegal name character

Thanks for any suggestions!

James

View 1 Replies View Related

How To Substring From 12 Char To 8 Char Itemid

Jun 19, 2008

Hi,


alter PROCEDURE [dbo].[PPUpdateIWDetails]

(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@TAllotedQty Numeric,
@EmployeeID NVARCHAR(36),
@Trndate datetime
)
AS
BEGIN
By default iam passing 12 char itemid as parameter...

Here iam selecting the itemid from InventoryLedger -if it is 8 char than this query should be executed
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)

BEGIN
DECLARE @Qty INT

select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END

Here iam selecting the itemid from InventoryLedger -if it is 12 char than this query should be executed(both queries are same)
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)

BEGIN
DECLARE @Qty INT

select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END

View 11 Replies View Related

Char(1) And Char(2) Take Same Space?

Aug 21, 2007

I create two tables:

create table table1
(
col1 char(1)
)

go

create table table2
(
col2 char(2)
)

go

I add some records to two tables after createing operation completed.

Then i use dbcc page command to oversee the structures of data page in two tables.
I found some interest things:
The rows in two tabes take up same space:9 bytes

You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE
or calculate from the offset array



Any suggestions?

View 14 Replies View Related

Bit Or Char?

Dec 8, 2005

Sql Server uses bit field for boolean while Access uses Y/N. What are the pros and cons of either way? Thanks.

View 14 Replies View Related

What Does It Mean Char(13) And Char(10)

May 4, 2000

What does it mean char(13) and char(10) when I use this in my sql code

ali

View 1 Replies View Related

Char To Bit

Jul 23, 2005

I am importing a table where I need to convert a char(1) with thevalues of 't' or 'f' into a bit field with valies of 1 or 0. Is therea built-in function that does that? I've been searching, but I can'tfind an answer.

View 6 Replies View Related

Concatenate Int && Var Char - SQL

Oct 1, 2007

Hi, I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar. But i am receiving the error:'Conversion failed when converting the varchar value ',' to data type int.The SQL I am trying to use is:select game_no + ',' + team_name as matchfrom result  Thanks 

View 2 Replies View Related

Char Vs Varchar

Dec 5, 2001

Hi,

Does any body know of any performance implications of using 'varchar' data type against 'char'?

I have some columns that are using 'char' data type, but the data in them is not fixed length. So, to gain some disk space I am planning to change the data type to 'varchar'. But, I am concerned if there will be any performance de-gradation or any other implications of doing this.

Regards
Chakri

View 3 Replies View Related

CHAR Vs VARCHAR

Mar 26, 2001

Hi,

Is that true that using CHAR datatype improves the performance comparing to VARCHAR

thanks
indeed.

View 3 Replies View Related

Non-English Char

Nov 2, 2000

Would the char or varchar datatype fieldname accept non-english letters like Japanees, Chinees, Russian..etc.

Or should I use nchar or nvarchar?

Thanks

View 2 Replies View Related

VARCHAR Vs. CHAR

Oct 27, 2000

Why would you want to use char when you have varchar? Is there any performance hit using a varchar and the size you make that varchar?

Debate going at work.

Phil

View 7 Replies View Related

Varchar V Char

Feb 17, 1999

I have recently inherited a database where all of the tables use varchar instead of chars for fields. Very , very few of these fields are involved in keys of even indices, but performance is an issue. I thought that I had read that varchars are worse for performance than chars when page splits may occur. Is this related to updates only, or does it matter?

Any help appreciated.

View 1 Replies View Related

Char (8) Vs. Varchar (8)

May 8, 2006

In relation to the code in this thread

http://forums.databasejournal.com/showthread.php?t=42622

My customer code field is Char (8) but accept an argument of Varchar (8) to my stored procedure. Don't ask me why!?!!

The customer code could be anything from 'A' to 'ZZZZZZZZ'.

Will this have any effect especially relating to overhead and retrieving incorrect data?

View 1 Replies View Related

Addition Of Char + Int !!!

Jan 15, 2002

Hi Everybody,

I hv executed the following query in the Query Qnalyzer
(Ofcourse I am Using SQL 2000 Enterprise Edition) and Surprisingly
I am getting the Output as follows,

query:-
select '5' + 10

Output:- 15

Can anybody please tell me, why it is happening like this? OR Did I miss any Configuration Parameter in the Server Settings?.

Tks in Advance,
Sam

View 1 Replies View Related

Getting MAX From Char Numbers

Jul 28, 2001

I have a table which will recieve and interface file to be loaded. All the fields are there except an account_id field which is a char(12), but the data is a number 000000000001, 000000000002, etc. These numbers are to be incremented by one. I understand how to write the process using a cursor to run through all the records and to increment them before loading. My question is the best way to get the max number in the existing table which is the char(12) , and icrement the number by one, but retain the leading zeroes I will need. Anyone had the pleasure of doing this providing input greatly appreciated.

View 5 Replies View Related

Convert Char(1) To Int

Feb 12, 2008

I'm looking through some data in a third party application, trying to figure something out for a report. There is a char(1) column holding control characters that apparently effects the status of certain records, and whether they should appear on the report. I want to look at the integer value of those characters rather than the character representation, but I can't get it to work. I keep getting "Error converting data type varchar to numeric." I'm trying to do something like this:
Code:

SELECT CONVERT(int, MyColumn) As IntValue FROM MyTable

I've tried every combination of cast and convert I can think of, as well as numeric data types other than int. There aren't that many distinct values, so I'm just going to do this manually, but I still want to know why it won't work, or what I'm doing wrong. Any thoughts?

View 2 Replies View Related

Char Type In MS SQL

Dec 12, 2005

Dear All,

I am new to MS SQL. One VB application that I need to maintain using it.
There is one problem reported but I couldn't explain: one field is declared
as Char (20), but the application seems able to insert a string with length
even 30. There is no character truncatd when retrieved neither.

However, I cannot simulate the case in the MSSQL database manager. When I executed a SQL script to insert, I was simply not allowed to do so.

Any explanation to the sDid anyone encounter same thing before?
Thanks for any hint.


Regards

View 3 Replies View Related

Char Vs Varchar

Feb 21, 2006

Hi,
This question may sound silly,but please comment.
Please tell me a situation where char should be used and not varchar.
Let us assume that we are dealing with non unicode characters.
Well, I find varchar is always smarter than char, so why char?
Thanks!!
Rudra

View 14 Replies View Related

Varchar And Char

Feb 9, 2004

i would like to know if there is an overhead in using VARCHAR when you use to store it...

a colleague of mine claims that if the field is defined to be VARCHAR the system creates and additional column DOUBLE/DECIMAL with storage size of 17/18bytes.

such that if the size of the varchar field is less than 30 it is better to be defined as CHAR instead.

please help me out here... i think there's something wrong with his statement, but i need concrete proof to it... a link to page or pdf file would be very much appreciated.

View 2 Replies View Related

How Do I Use Char(xxx) In A LIKE-statement

Feb 16, 2004

I want to search several columns after a range of characters. How do I use the CHAR value in a LIKE-statment?

declare @i int
select @i = 0
while @i between 1 and 10
begin
select * from tab1 where col1 like char(@i) ???
(how do I use char here)
end

View 1 Replies View Related

Char To Smalldatetime

Mar 1, 2004

SQL Server 2000

Used to know how to do this but am having no luck today. I have data coming in from a .txt file that gives me char strings for dates, e.g. 02242003 for Feb. 2, 2003.

Need to whomp this into small datetime with the whole convert/cast thing but I guess I've previously only gone the other way--smalldatetime to char.

Thanks!!!

View 4 Replies View Related

Char Format

Mar 8, 2004

I have one table that has a column 'amount' with datatype 'numeric(19,5)'
I'm want to insert the data from this column into another column (in a different database) with this format: '00000000.00' as 'char'
example: If in the first column I have 800.75864 I want to insert it in the other column as '00000800.75'

any suggestions on how to make this conversion?

View 10 Replies View Related

Problem With Char(15)

May 12, 2008

there are some problems with char(15) columns:

(1) Purely fractional numbers sometimes have the first zero removed. So "0.03875" is stored as ".03875"
(2) Some numbers have trailing zeros added. So "70.7" is stored as "70.700000".

Can someone help how to overcome the formatting errors.

Thanks,

View 11 Replies View Related

Pad Char(2) Field With Zero

Apr 6, 2006

Hi

I'm extracting a char(2) field from a table that has a value of '1' and writing it to another table but want the output field to be padded with a zero. I have tried this below:

right('00' + isnull(Field1, ' '),2

but the output field comes out as '1 ' (that's 1 followed by a space). Does anyone have any idea how I can do this or please point out what I'm doing wrong in my 'right(' fucntion above.
Thanks,
Jeff

View 3 Replies View Related

Int To Char Conversion

May 8, 2006

Hi,

I'm scrambling to complete a project and ran into an int to char problem. I'm sure, in my haste, I'm doing something stupid. Here is an example of what I'm trying to do but it doesn't convert as expected. I want the SEQ_NBR2 value to convert from 4 to 0000004.

select right('0000000' + convert(char(7), isnull(a.SEQ_NBR2, 0.0)), 7)
fromTXN_HEAD_837 a
where a.file_auth_nbr = '1084472388468'
anda.seq_nbr2 = 4
and a.sgmt_id2 = 'BHT'

Thanks, Jeff

View 5 Replies View Related

Primary Key (Int Vs Char)

Apr 13, 2007

I seem to recall many debates and articles about primary keys, but for the life of me, I can't seem to figure out what to do in what is probably be a very straight forward situation (below). Must be all the late nights lately impairing my common sense.

So, I've basically got a set of data where I want to extract a status field from the table design. I'll end up with two tables:

MainTable(Field1,Field2, ... ,FieldA ,StatusID)
StatusTable(StatusID,Field1,Field2) [shouldn't be that big a table, actually]

It's a silly thing but... are there any significant reasons why I should choose int for my StatusID instead of char?

I seem to be under the impression that using arbitrary int numbers as the identifiers will yield better performance on joins; but from past experience, using arbitrary int numbers can be a bother to interpret the data. :P I've seen tables where they were so full of numeric codes, that I had to do multiple joins to the respective code tables to yield what each code meant (or what that particular row meant as a whole).

Of course, I don't mean to use an actual full description for my primary keys, but would it be so bad to use something like "N" (to indicate "normal")?

View 9 Replies View Related

Removing Last Two Char

Apr 25, 2007

Afternoon all

What is the quickest way to remove the last two char from a colunm and leaving the rest of the colunm intact.

cheers

View 4 Replies View Related

Convert Char(4) To Int

Jun 12, 2007

I have a Column called PostNr which is of type Char(4)
I want to be able to Convert it to int
How can I accomplish that in query analyser, I tried changing it in Ent Manager, it keeps timing out.
I data in Post is in this format, does not contain illegal character just number

View 5 Replies View Related

Char Vs. Varchar

Jul 23, 2005

Greetings,I have a question. I work on some SQL2k/ASP.NET apps at work. Mypredacessor, who created the databases/tables seemed to have liked touse 'char' for all text fields. Is there a reason why he would havedone this over using varchar? It's a minor annoyance to always have toRTRIM data and it makes directly making changes to the database moreannoying (with all the pointless trailing spaces)?I usually use char for fixed string lengths, like state abbreviationsor something, and varchar for strings of unknown length.Is it a performance issue? Our database doesn't do much traffic, forthe most part.

View 5 Replies View Related

Int To Char Conversion

Feb 2, 2006

I have data tables that include ZIP code, as char(5). The values looklike integers, but they are padded with leading zeroes to fill out 5characters, '00234'.There are SPs to look up data, with @Zip char(5) among the parameters.Some users call these with integer values, @Zip = 234, and SQL makesthe conversion. Is it necesary to add the leading zeroes in the SP --@Zip=RIGHT('00000'+@Zip,5) -- or would SQL find this match? (234 ='00234'). It looks like the conversion is to '234' and the matchfails.Thanks,Jim GeissmanCountrywide

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved