Char Or Varchar For A Primary Key?

May 28, 2007

Hi,
I'm planning the structure of a SqlServer 2005 database for a new
application.
The requirement is that primary keys must be "natural"; i.e. in the table
Customers the primary key will be a max. 10 characters string (but the
string may be filled i.e. with only 5 charachters).

Should I define these primary keys as char[5] or varchar[5]?
I'm interested in your opinion in particular about performace issue, because
there will be tables with millions of records...


Thanks,
Davide.

View 7 Replies


ADVERTISEMENT

Datatype Question Varchar(max), Varchar(250), Or Char(250)

Oct 18, 2007



I have a table that contains a lot of demographic information. The data is usually small (<20 chars) but ocassionally needs to handle large values (250 chars). Right now its set up for varchar(max) and I don't think I want to do this.

How does varchar(max) store info differently from varchar(250)? Either way doesn't it have to hold the container information? So the word "Crackers" have 8 characters to it and information sayings its 8 characters long in both cases. This meaning its taking up same amount of space?

Also my concern will be running queries off of it, does a varchar(max) choke up queries because the fields cannot be properly analyzed? Is varchar(250) any better?

Should I just go with char(250) and watch my db size explode?

Usually the data that is 250 characters contain a lot of blank space that is removed using a SPROC so its not usually 250 characters for long.

Any insight to this would be appreciated.

View 9 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

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

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

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

Nchar, Varchar,char?

Jan 11, 2008

What is the difference between the above data types in SQL?  Which datatype should I use if I wanted both numbers and characters?

View 1 Replies View Related

Changed From Char To Varchar

Jun 3, 2004

i changed my state table from char 20 to varchar 20 and it still fills it with empty space at the end of the statename
I tried entering it with the trim command still no good
i looked a the sql command made in vs and it says that the variables are varchar
is there something i am missing with varchar
does it only work properly if it is over 50 or does it not change completely when you change from char to varchar

View 2 Replies View Related

VarChar Or Char Faster?

Mar 30, 2006

Does using varchar or varchar(max) affect the system performance?
Should I use fixed size coloumns to increase speed?

View 4 Replies View Related

Why Specify The Char Length In Varchar?

Feb 6, 2002

Newbie question:

Why bother specifing the length in varchar()?
Why not just specify the max and not worry about truncation?

Thanks,

Martin

View 2 Replies View Related

Difference Between CHAR And VARCHAR

Aug 8, 2002

I have one question to all SQL Guru's
I know the basic difference between CHAR and VARCHAR of CHAR taking all the space it is declared with and VARCHAR taking only amount of space used.

I want to know or link to any doucment which gives the difference between CHAR and VARCHAR more than what is mentioned above.

Please this is a urgent requirment.


Thanks in advance.

View 1 Replies View Related

Difference Between Char & Varchar

Nov 6, 2007

what'z the difference between char & varchar, like i am doing a cast function, which one should be used..

View 11 Replies View Related

How Many Char's In Varchar Record

Aug 22, 2005

helloHow could I check how many chars are in record, defined as varchar(8000).It's obvious that in such defined record could be 1 char to 8000 char. Butwhat query to SQL database should I post to give information about realylenght of this records ?thanks from advanceAdam

View 5 Replies View Related

Char Vs Varchar And Indexes

Sep 7, 2007

all these while i've only used varchar for any stringi heard from my ex-boss that char helps speed up searches. is thattrue?so there are these:1) char with index2) char without index3) char with clustered index4) varchar with index5) varchar without index6) varchar with clustered indexsome of my tables primary key (clustered) is a string type. would itbe benificial to use char? or would using (6) makes no difference?for non primary key columns that needs to be searched a lot, can i say(1) is the best?

View 4 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

SQL Variable Char Kinda Like Varchar

Jul 26, 2005

Hi, is there a type of data that we don't need to specify the length but can grow dynamically?instead of using varchar 2000, a type that acts like a varchar but the length is unlimited ....Thanks,

View 5 Replies View Related

% Performance Impact If Use Varchar Instead Of Char?

Feb 19, 2004

Hi,
Anybody have any idea howmuch % of performance will be affect if we are using varchar instead of char data type?.
Thanks,
Ravi

View 2 Replies View Related

Convert Varchar Or Char To Float

Jan 4, 2006

Hello,
Is there a way to convert varchar or char to float?
Thank you

View 6 Replies View Related

Nchar Or Char Or Nvarchar Or Varchar???

Apr 19, 2004

Hi,

Which of the above data type (alongwith size) should be used for storing things like Customer Name, Company name etc . ???

Also, what really is the benefit of one over the over :confused:

Thanks

View 7 Replies View Related

Can Varchar Length Be Set To More Than 8000 Char?

May 22, 2006

Hi, everyone, I want to know is there a way for me to set varchar to store more than 8000 characters? (I did checked from sql server books online and i know that the maximum storage for varchar, but i just want to know is there any exceptional way for me to store more than that).

Thanks for any reply.

aex

View 4 Replies View Related

Char Vs Varchar &#043; Padding Issue

Feb 20, 2008

I have upsized some tables from ms access (using the wizard) which has created many nvarchar fields. I know the system doesn't need unicode characters so I straight away changed them to varchar and the system is all working but am now deciding on which ones to change to char as I understand you get a performance gain. I am trying to do this without changing much ms access front end code so don't want to deal with rtrim I am thinking change the ones I can guarantee the length of. These happen to be the primary keys.

My questions are is this the correct way of doing things, am I correct in assuming I would have to rtrim alot in program code if I changed all to char.

Finally and. most importantly what about a single character field which could be null can I make this a char(1) and it will still return null if empty as it would as a varchar(1). I suppose this question is how does the padding work is it the character followed by a number of nulls (ascii 0 I think).

Sorry for the multiple questions but I think they are all related

View 2 Replies View Related

Join Zero Padded Char To Varchar

May 31, 2008

Having a brain fart and can't figure this out...I have 2 databases I need to join:db1.customer.customer_no char(15) right justified, zero paddedsample customer numbers:000000000000001000000000000010000000000000234000000000012345db2.customer.customer_no varchar(20) left justified, no paddingsample customer numbers:11023412345How do I join tables on customer_no? Use cast, convert? Strip zeroes fromdb1 table?Thanks.

View 2 Replies View Related

Datatypes Nchar/nvarchar Vs Char/varchar

Dec 2, 2005

can anybody please explain me why microsoft using nvarchar/nchar instead of varchar/char in northwind database and pubs database. I know if a column holds unicode data you should use nvarchar or nchar but for me all those tables in northwind/pubs are not holding unicode data. but still why microsoft settled for nchar/nvarchar.

View 2 Replies View Related

Data Types (char, Varchar, Nchar, Nvarchar, ...)

Jul 27, 2007

Could someone please help me by explaining which one is best to use and when?  For example, storing the word "Corona Del Mar" - which Data Type would be suggested?
 Thanks.

View 3 Replies View Related

Varchar Vs Char In 1st Field Of Composite Clustered Index

Jul 23, 2005

Would it be OK to use varchar(5) instead of char(5) as the first field of acomposite clustered index?My gut tells me that varchar would be a bad idea, but I am not finding muchinformation on this topic on this when I Google it.Currently the field is Char(4), and there is a need to increase it to hold 5characters.TIA

View 2 Replies View Related

Converting CHAR/VARCHAR/TEXT Into NCHAR/NVARCHAR/NTEXT!!!

Jan 28, 2005

Hi,
We are in process of converting all of the data type of the fields from CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT (DBCS). Having more than 900 store procedure its look like real pain to make modification in all of the SPs.

After failed to find any help from GOOGLE, I am posting this request. I am basically looking for any automated tool which are convert data type in SP based on the field of the table used in the SP. Or at least which can provide me some sort of list which can helpful for doing manual reactoring.

PLEASE HELP ME!!!

Thanks,

Firoz Ansari

View 2 Replies View Related

What's Difference About Datatype Char,VarChar,NChar,NVarChar In Sql 2000 ?

Jan 9, 2006

Hi All:
         I am new to Sql 2000 database,Now  I'm planing to create a table in my databse,my table included below fields like this :
       PoNo(the length is 15 characters) ,Supplier Name(the length is 50 characters).etc
      but I don't how to select the datatype for them. should I  select  Char or VarChar ?
      which one is the best slection ?
  thans in advanced!
 
 
         
    

View 5 Replies View Related

Implicit Conversion Of Char Value To Varchar Cannot Be Performed Because The Collation Of The Value Is Unresolved..

Nov 12, 2007

I got this erorr when trying to create my stored proc,

What do i need to fix, and how do i fix it?!!

Msg 457, Level 16, State 1, Procedure PROC_DAILY_ACTIVITY, Line 13

Implicit conversion of char value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.




Code Block
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <Zaccheus,Tenchy>
-- Create date: <NOVEMEBER,12,2007>
-- Description: <Reporting stored procedure,DAILY ACTIVITY,>
-- =============================================
CREATE PROCEDURE [dbo].[PROC_DAILY_ACTIVITY]
(@Region_Key int=null)
AS
BEGIN
SELECT
Region_Key,
Null as Customer_Code,
Non_Customer_Activities.Question_code,
Non_Customer_Activities.Description,
Region AS Region,
Name AS Territory_Name,
Non_Customer_Activities.Que_Desc AS Store_Name,
Non_Customer_Activities.Logged_Time AS TheDate,
Non_Customer_Activities.response AS Response,
Null as is_Visit_Fg
FROM [FSSRC].[dbo].Qry_Sales_Group Sales_Group
INNER JOIN
(Select QH.[question_code]
,Question_Header.Description
,CONVERT(datetime,DATEADD(day, (qh.cycle_day-1), p.start_date),6) Logged_Time
,SUBSTRING([entity_code],1,5) SR_Code
,[response]
,Territory_Code SR_Territory_Code
,'Not Customer Related' Que_Desc
From question_history QH
INNER JOIN Period P
ON p.period_code = qh.period_code
INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Territory_In_Sales_Responsible] as Territory_In_Sales_Responsible
ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code=SUBSTRING([entity_code],1,5) COLLATE Latin1_General_CI_AS
INNER JOIN dbo.questions Question_Header
ON Question_Header.question_code = QH.question_code
WHERE [entity_code] like '%.USER%'
AND Question_Header.Question_Code IN('AME01','ASE01','ACO01','ALU01','AOS01','APH01','ATR01','ATE01','ACR06','ACR05','ACR02','ACR03','ACR08','ACR07')
AND CONVERT(datetime,DATEADD(day, (qh.cycle_day-1), p.start_date),6) = '11/9/2007'
) Non_Customer_Activities
ON Sales_Group.Code = Non_Customer_Activities.SR_Territory_Code
UNION ALL
SELECT
Customer_Activities.Customer_Code,
NULL,
NULL,
Region AS Region,
Name AS Territory_Name,
Customer_Activities.Customer_Name AS Store_Name,
Customer_Activities.Logged_Time AS TheDate,
NULL AS Response,
is_Visit_Fg
FROM [FSSRC].[dbo].Qry_Sales_Group Sales_Group
INNER JOIN
(Select distinct time_log Logged_Time
,[entity_code] Customer_Code
,[name] Customer_Name
,Territory_Code Cust_Territory_Code
,MAX(is_Visit_Fg) Is_Visit_Fg
From question_history QH
INNER JOIN Period P
ON p.period_code = qh.period_code
INNER JOIN dbo.questions Question_Header
ON Question_Header.question_code = QH.question_code
INNER JOIN [FSSRC].[dbo].[customer]
ON Entity_Code = [customer_code]
INNER JOIN [FSSRC].[dbo].[visit] V
ON V.[customer_code] = QH.[entity_code]
AND V.[period_code] = QH.[period_code]
AND V.[cycle_day] = QH.[cycle_day]
INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Territory_In_Sales_Responsible] as Territory_In_Sales_Responsible
ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code=[sales_person_code] COLLATE Latin1_General_CI_AS
WHERE [entity_code] NOT like '%.USER%'
AND Convert(datetime,convert(Varchar,time_log,110)) = '11/9/2007'

GROUP BY
time_log
,[entity_code]
,[name]
,Territory_Code
) Customer_Activities
ON Sales_Group.Code = Customer_Activities.Cust_Territory_Code
WHERE @Region_Key=Region_Key
order by 4
END

View 2 Replies View Related

What Is The Difference Between Updating Null Value Vs Empty String To Varchar/char Field?

Aug 29, 2007

Hi,
What is the difference updating a null value to char/varchar type column

versus empty string to char/varchar type column?Which is the best to do and why?
Could anyone explain about this?

Example:

Table 1 : tCountry - Name varchar(80) nullable
Table 2 :tState - Name char(2) nullable
Table 3 :tCountryDetails - countryid,state (char(2) nullable) - May the country contain state or no state
So,when the state is not present for the country ,i have two options may be - null,''
tCountryDetails.State = '' or tCountryDetails.State = null?

View 9 Replies View Related







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