check my SP [its not working fine]

Hello everyone, im using SQL Server 2000, here is my table:

Table1: Buyers

BuyerID int
ParentID int
MerchantCode varchar(20) (each buyer has unique merchant code we say that its login is Merchant Code)
PinCode (this is used as a password for the buyers login)
ApprovalStatusCode int (FK, data is for authenticationending Approval, Approved, Cancelled)
IsCliEnabled smallint
Clis varchar(100) (this is the CSV: 12345, 2346,....)[as each buyer has more that 1 CLi values so this field is in CSV form data)

//Now buyers also has SubAccounts (Table2) that was made for his workers or some one else (Not necessary every buyer has SubAccounts)

Table2: Buyers SubAccounts

BuyerID
AccountNumber smallint
PinCode varchar(64)
CreateDate datetime

now here is my SP,
CREATE PROCEDURE IvrAuthenticateBuyer

@.MerchantCode varchar(20),
@.PinCode varchar(64),
@.CLI varchar(15)

AS
-- For testing i give values at here
declare @.MerchantCode varchar(20)
set @.merchantCode='000000010'
declare @.PinCode varchar(64)
set @.PinCode='1234656'
declare @.CLI varchar(15)
set @.CLI='12345'
-

declare @.BuyerID int
declare @.ApprovalStatusCode smallint
declare @.IsCliEnabled smallint
declare @.Clis varchar(1000)

-- buyerID get by checking only 8 digits of merchant code (omit last one)
-- if last digit of merchant code are > "0" then
-- get them in a variable
-- check if the account is approved
-- check if CLI is enabled, if yes, check if @.cli is in the list

SELECT @.BuyerID = BuyerID, @.ApprovalStatusCode = ApprovalStatusCode,
@.IsCliEnabled = IsCLIEnabled, @.Clis = coalesce(@.Clis+',','')+CLIs
FROM Buyers
WHERE MerchantCode = @.MerchantCode

--select @.BuyerID
--select @.IsCliEnabled
--select @.ApprovalStatusCode
--select @.Clis
-- chk all conditions

if @.ApprovalStatusCode <> 2
raiserror('Account is not Approved',16,1)

select @.BuyerID

return

if (@.IsCliEnabled=1) --check whether true
begin --Main Begin
--charindex will return value greater than 0 if CLI is found in list
-- if charindex('34534',@.Clis)>0
if ','+@.Clis+',' like '%,'+@.CLI +',%'
begin
print 'CLI found in CSV list'
end
else
begin
raiserror('CLI NOT found in CSV list',16,1)
end
end --Main End
else
begin
raiserror('CLI NOT ENABLED',16,1)
end

-- Get Last Digit of Merchant Code and stored them in a variable

declare @.SubAccountNo varchar(2)
select @.SubAccountNo = Substring(@.MerchantCode,8,1)

-- Check It is SubAccount or the Buyer's Main Account

if convert(int, @.SubAccountNo) > 0
select 1
from BuyerSubAccounts
where PinCode=@.PinCode
else
select 1
from Buyers
where PinCode=@.PinCode

that is used for Buyer Authenticate, but i dont know whats wrong with this as its not working fine now,
as if i give wrong password then it also retuns the error of if i give wrong CLi same its runs without giving any error. Kindly
check it whats wrong with this as i m in much trouble still can't find out the actual problem in my SP. So i requested to all of u
plz help me and make my SP correct.

Thanx in Advance.

I believe I have found your problem. See code snippet below.

Code Snippet

CREATE PROCEDURE IvrAuthenticateBuyer

@.MerchantCode varchar(20),
@.PinCode varchar(64),
@.CLI varchar(15)

AS
-- For testing i give values at here
declare @.MerchantCode varchar(20)
set @.merchantCode='000000010'
declare @.PinCode varchar(64)
set @.PinCode='1234656'
declare @.CLI varchar(15)
set @.CLI='12345'
-

declare @.BuyerID int
declare @.ApprovalStatusCode smallint
declare @.IsCliEnabled smallint
declare @.Clis varchar(1000)

-- buyerID get by checking only 8 digits of merchant code (omit last one)
-- if last digit of merchant code are > "0" then
-- get them in a variable
-- check if the account is approved
-- check if CLI is enabled, if yes, check if @.cli is in the list

SELECT @.BuyerID = BuyerID, @.ApprovalStatusCode = ApprovalStatusCode,
@.IsCliEnabled = IsCLIEnabled, @.Clis = coalesce(@.Clis+',','')+CLIs
FROM Buyers
WHERE MerchantCode = @.MerchantCode

--select @.BuyerID
--select @.IsCliEnabled
--select @.ApprovalStatusCode
--select @.Clis
-- chk all conditions

if @.ApprovalStatusCode <> 2

BEGIN
raiserror('Account is not Approved',16,1)

select @.BuyerID

return

END

if (@.IsCliEnabled=1) --check whether true
begin --Main Begin
--charindex will return value greater than 0 if CLI is found in list
-- if charindex('34534',@.Clis)>0
if ','+@.Clis+',' like '%,'+@.CLI +',%'
begin
print 'CLI found in CSV list'
end
else
begin
raiserror('CLI NOT found in CSV list',16,1)
end
end --Main End
else
begin
raiserror('CLI NOT ENABLED',16,1)
end

-- Get Last Digit of Merchant Code and stored them in a variable

declare @.SubAccountNo varchar(2)
select @.SubAccountNo = Substring(@.MerchantCode,8,1)

-- Check It is SubAccount or the Buyer's Main Account

if convert(int, @.SubAccountNo) > 0
select 1
from BuyerSubAccounts
where PinCode=@.PinCode
else
select 1
from Buyers
where PinCode=@.PinCode

An IF statement will only apply to the statement immediately following it, unless that statement is BEGIN, then it will execute until the next END is found.

check my SP

i m using SQL Server 2000, in this i have the follwowing SP,

CREATE PROCEDURE AddNewCountry
@.CountryName varchar(50),
@.CountryCode varchar(5)

AS
IF exists(Select CountryName from Countries where CountryCode=@.CountryCode or CountryName=@.CountryName)
begin
raiserror('Country Already Exist',16,1)
end
else
begin
INSERT INTO Countries
VALUES
(@.CountryName,
@.CountryCode)
end
GO
in this SP in the where clause i used the following line :
IF exists(Select CountryName from Countries where CountryCode=@.CountryCode or CountryName=@.CountryName) (used OR operator)
but it inserted the data if i give 092,'america'

and if i used AND operator it chekc 092, 'america' if yes then shows error but if i give 092,'Japan' it inserted new row in the Countries table.

plz give me some other idea what changes i made in my SP as unique Country added with unique Code. if code exists then not allowed to used to enter data in the table same in
the case of country name .

plz give me idea what updation i made as after this my SP works fine.

In this case you have to use Or operator instead of AND operator.

And you can enforce Unique Constraint on both tables (not composite)

ex.

Create Table Countries(

CountryCode Int Unique,

CountryName Varchar(100) Unique

)

Check if string could be converted to number

I need to check if string could be converted to a int without throwing any
errors.
I need to do something like this
DECLARE @.s varchar(20)
DECLARE @.i int
--if following is possible
@.i=CAST (@.s as int)
--then
SELECT @.1
--else
SELECT 0
if string is not a number I really don't need to deal with it in the first
place.
The real life example of my scenario is checking uniqueness of check number
for bank transactions. If user writes ATM for check number we don't need to
check the uniqueness.
Could it be done?
Thanks,
Shimon.There's a built-in function in SQL - ISNUMERIC. Look it up in Books Online.
However, it has some issues. They are illustrated here:
http://www.aspfaq.com/show.asp?id=2390
..and more! ;)
ML|||Hi Shimon
you can use ISNUMERIC for this
select isnumeric(@.s)
For eg:
if Value of @.s is '123' then the value returned is 1
if Value of @.s is '123a' then the value returned is 0
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Shimon Sim" wrote:

> I need to check if string could be converted to a int without throwing any
> errors.
> I need to do something like this
> DECLARE @.s varchar(20)
> DECLARE @.i int
> --if following is possible
> @.i=CAST (@.s as int)
> --then
> SELECT @.1
> --else
> SELECT 0
> if string is not a number I really don't need to deal with it in the first
> place.
> The real life example of my scenario is checking uniqueness of check numbe
r
> for bank transactions. If user writes ATM for check number we don't need t
o
> check the uniqueness.
> Could it be done?
> Thanks,
> Shimon.
>
>|||Shimon wrote on Wed, 17 Aug 2005 08:25:30 -0400:

> I need to check if string could be converted to a int without throwing any
> errors.
> I need to do something like this
> DECLARE @.s varchar(20)
> DECLARE @.i int
> --if following is possible
> @.i=CAST (@.s as int)
> --then
> SELECT @.1
> --else
> SELECT 0
> if string is not a number I really don't need to deal with it in the first
> place.
> The real life example of my scenario is checking uniqueness of check
> number for bank transactions. If user writes ATM for check number we don't
> need to check the uniqueness.
> Could it be done?
> Thanks,
> Shimon.
Try
DECLARE @.s varchar(20)
DECLARE @.i int
/*set value of @.s here*/
SET @.s = 'test'
IF (ISNUMERIC(@.s) = 1)
SET @.i = CAST(@.s as int)
ELSE
SET @.i = 0
SELECT @.i
You'll get a response of 0. Change 'test' to '1000', you'll get 1000.
Dan|||Oh, one thing I missed in my reply - if the string is numeric, but too large
to fit into an int, you'll get an error, so you should have some check on
the string length to determine if it'll fit, or cast into the largest
numeric datatype.
Dan|||Thanks a lot. Exactly what I needed.
Shimon.
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:880E02E2-DA50-4466-9566-FC429802FB37@.microsoft.com...
> Hi Shimon
> you can use ISNUMERIC for this
> select isnumeric(@.s)
> For eg:
> if Value of @.s is '123' then the value returned is 1
> if Value of @.s is '123a' then the value returned is 0
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Shimon Sim" wrote:
>|||Shimon Sim,
Do you think it is enough using "like" operator?
Example:
select
cast(c1 as int)
from
(
select cast('1080' as varchar(10))
union all
select cast('atm' as varchar(10))
union all
select cast('1081' as varchar(10))
union all
select cast('atm' as varchar(10))
union all
select cast('atm' as varchar(10))
union all
select cast('1082' as varchar(10))
) as t1(c1)
where
c1 not like '%[^0-9]%'
AMB
"Shimon Sim" wrote:

> I need to check if string could be converted to a int without throwing any
> errors.
> I need to do something like this
> DECLARE @.s varchar(20)
> DECLARE @.i int
> --if following is possible
> @.i=CAST (@.s as int)
> --then
> SELECT @.1
> --else
> SELECT 0
> if string is not a number I really don't need to deal with it in the first
> place.
> The real life example of my scenario is checking uniqueness of check numbe
r
> for bank transactions. If user writes ATM for check number we don't need t
o
> check the uniqueness.
> Could it be done?
> Thanks,
> Shimon.
>
>|||Thank you for this note.
Shimon.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:OnUdojyoFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Oh, one thing I missed in my reply - if the string is numeric, but too
> large to fit into an int, you'll get an error, so you should have some
> check on the string length to determine if it'll fit, or cast into the
> largest numeric datatype.
> Dan
>|||I am not sure if it will work in my scenario.
Thank you
Shimon.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:D57DFB0A-33B2-4239-AA77-BA15195B6789@.microsoft.com...
> Shimon Sim,
> Do you think it is enough using "like" operator?
> Example:
> select
> cast(c1 as int)
> from
> (
> select cast('1080' as varchar(10))
> union all
> select cast('atm' as varchar(10))
> union all
> select cast('1081' as varchar(10))
> union all
> select cast('atm' as varchar(10))
> union all
> select cast('atm' as varchar(10))
> union all
> select cast('1082' as varchar(10))
> ) as t1(c1)
> where
> c1 not like '%[^0-9]%'
>
> AMB
> "Shimon Sim" wrote:
>

Check for table and return true or false

How can I write a stored procedure to return if a table exist or not?

I put:

CREATE procedure sp_BA_ReportExist

(
@.ISYES VARCHAR (10),
@.ISNO VARCHAR (10)
)

AS

DECLARE @.SQL varchar(8000)
SET @.SQL = " if object_id('BA_REPORT_MASTER') is not null RETURN "+@.ISYES+" ELSE RETURN "+@.ISNO+" "

EXEC(@.SQL)
GO

I ran it with: sp_BA_ReportExist '1','0'

but I get:

Server: Msg 178, Level 15, State 1, Line 1
A RETURN statement with a return value cannot be used in this context.
Server: Msg 178, Level 15, State 1, Line 1
A RETURN statement with a return value cannot be used in this context.

How can I make this work?

Thanks!

KenFigured it out... this works:

CREATE procedure sp_BA_ReportExist

(
@.ISYES VARCHAR (10),
@.ISNO VARCHAR (10)
)

AS

DECLARE @.SQL varchar(8000)
SET @.SQL = " if object_id('BA_REPORT_MASTER') is not null PRINT "+@.ISYES+" ELSE PRINT "+@.ISNO+" "

EXEC(@.SQL)
GO

As always I find the answer right after I post!|||you could also use:

declare @.TableName sysname
set @.TableNAme = 'sysobjects'
if OBJECTPROPERTY(OBJECT_ID(@.TableName),'IsTable') = 1
print "+@.ISYES+"
else
print "+@.ISNO+"|||I have another problem now...

How do I get the return value?|||do you want it as a result set, output parameter or as a numeric valued returned by the "RETURN" statement?|||A resultset will work.

Basically I just need to know if the table exists so my application can set some values. Ic na't figure out how to get the value back into the application.

Thanks so much for any light you can shed on this!

Ken|||try:

create procedure sp_BA_ReportExist(
@.ISYES VARCHAR (10)
, @.ISNO VARCHAR (10))
AS
if (object_id('BA_REPORT_MASTER') is not null)
select @.ISYES as Answer
else
select @.ISNO as Answer

return 0
GO

exec sp_BA_ReportExist 'Yes', 'No'|||Too Cool! Thank you so much!

I was kinda close, but didn't have it quite right!

Thanks for your help!

Ken|||or:
create procedure sp_TableExists(
@.TableName sysname
, @.ISYES VARCHAR (10) = 'Yes'
, @.ISNO VARCHAR (10) = 'No')
AS
select case OBJECTPROPERTY(OBJECT_ID(@.TableName),'IsTable') when 1 then @.ISYES else @.ISNO end as Answer
return 0
GO

exec sp_TableExists 'sysobjects','Yes', 'No'

or just

exec sp_TableExists 'sysobjects'

Check for Date

I want to enforce users to enter a date or leave the field null into a
varchar field.
We do not own the code to our database so I can not change the field to
a date/Time (It would violate our contract). I was thinking we could
enter a check constraint (This is OK per our contact) into the database
but I am not sure how.
THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Thanks for any help.Hi, Watson
You can use:
a) the ISDATE() function (which accepts any date format),
b) a LIKE expression (which accepts a given pattern, but cannot easily
check if the date is valid), or
c) a combination of the above
For example:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (ISDATE(USER_7)<>0)
or:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (USER_7 LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]')
or:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (ISDATE(USER_7)<>0
AND USER_7 LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]')
Razvan
Watson SQL wrote:
> I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.|||You could have a CHECK constraint as shown below:
ALTER TABLE YourTableName ADD CONSTRAINT CheckDate CHECK (ISDATE(ColumNName)
= 1 or ColumnName IS NULL)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
I want to enforce users to enter a date or leave the field null into a
varchar field.
We do not own the code to our database so I can not change the field to
a date/Time (It would violate our contract). I was thinking we could
enter a check constraint (This is OK per our contact) into the database
but I am not sure how.
THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Thanks for any help.|||>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Try:
ALTER TABLE dbo.CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (USER_7 IS NULL OR ISDATE(USER_7) = 1)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.
>|||ALTER TABLE CUST_ORDER_LINE ADD CONSTRAINT constraintname CHECK(ISDATE(USER_7))
No need to explicitly allow NULL. If a CHECK constraint evaluate to TRUE or UNK, the modification is
allowed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.
>

Check Constraint?

In SQL Server 2000, I want to apply a check constraint on a column type varchar that it is not duplicated. Could someone help me out with this?

Also, should I just make this column the primary key instead of an identity field being the primary key?

What is the performance difference by applying the constraint rather then just making it the primary key?

Mike BTry using a unique constraint !!

Check Constraint

I have an existing table with field ZIPCODE defined as VARCHAR(5).
I want to add a check constraint to allow only number from 0 to 9.

This is what I did but it gave me error:

alter table test
with check
add constraint ck_test
check (zip between '0' and '9')

error:

ALTER TABLE statement conflicted with COLUMN CHECK constraint 'ck_test'.
The conflict occurred in database 'lahdProperty', table 'test', column 'Zip'.

What did I do wrong. Thanks for your help.Don't have access to bol and my machine...I'll figure it out tomorrow, unless you get it tonight

BUT

Look at LIKE [0-9][0-9][0-9][0-9][0-9]|||I guess u r trying to check a single digit as zip.
existing data in the table conflicts with the constraint u r trying to add
so try to alter table with nocheck option.
or else drop the column and add it in which ever way u want.
------------------------
alter table test
with nocheck
add constraint ck_test
check (zip between '0' and '9')
------------------------

Originally posted by ttkh
I have an existing table with field ZIPCODE defined as VARCHAR(5).
I want to add a check constraint to allow only number from 0 to 9.

This is what I did but it gave me error:

alter table test
with check
add constraint ck_test
check (zip between '0' and '9')

error:

ALTER TABLE statement conflicted with COLUMN CHECK constraint 'ck_test'.
The conflict occurred in database 'lahdProperty', table 'test', column 'Zip'.

What did I do wrong. Thanks for your help.

CHECK constraint

CREATE TABLE T_StaffMain (
[snip]
SM_PostNameE VARCHAR(40) NOT NULL,
SM_PostNameD NVARCHAR(40) NOT NULL,
SM_PostLevel INTEGER DEFAULT 0 NOT NULL CHECK (SM_PostLevel IN
(0,1,2,3)),
[snip]
);
guys. i've got to change the CHECK statement above to values from 0 to
7 instead of 0 to 3.
this is in a production table, so dropping the table is not an option.
how to do this?
thanx
riyazHi
First , you have to DROP CONSTRAINT (see details in the BOL)
<rmanchu@.gmail.com> wrote in message
news:1141616935.268356.322910@.i39g2000cwa.googlegroups.com...
> CREATE TABLE T_StaffMain (
> [snip]
> SM_PostNameE VARCHAR(40) NOT NULL,
> SM_PostNameD NVARCHAR(40) NOT NULL,
> SM_PostLevel INTEGER DEFAULT 0 NOT NULL CHECK (SM_PostLevel IN
> (0,1,2,3)),
> [snip]
> );
> guys. i've got to change the CHECK statement above to values from 0 to
> 7 instead of 0 to 3.
> this is in a production table, so dropping the table is not an option.
> how to do this?
> thanx
> riyaz
>|||Hello, riyaz
You will have to drop the constraint, but you need to know it's name,
because you didn't name it when you created it. To find out the
constraint's name, you can use Enterprise Manager or the following
query:
SELECT o.name FROM sysconstraints k
INNER JOIN sysobjects o ON k.constid=o.id
INNER JOIN syscolumns c ON c.id=k.id AND c.colid=k.colid
WHERE c.id=OBJECT_ID('T_StaffMain') AND c.name='SM_PostLevel'
AND o.type='C'
You should create the new constraint with a name, like this:
ALTER TABLE T_StaffMain ADD CONSTRAINT [CK_T_StaffMain_SM_PostLevel]
CHECK (SM_PostLevel BETWEEN 0 AND 7)
Razvan

Characters not allowed in SQL varchar?

I have been using MS SQL server (8.0.194) and I have been wondering whatacters should I strip from entries before putting them into a varchar() field?

I check for single quote (') and handle that, and malicious attempts. But is it ok to have the newline characters in there(\r\n)? The always show up as the ASCII-square box, so I was wondering if I need to be stripping them out as well?
What other "normally used" text characters do I also need to watch out for, if any?

Thanks.I wouldn't think it would matter what you "put into a varchar" as long as when you "pull" the text back out you DISPLAY it in the same manner from where you saved it. That is, if you used a simple text box for a line of entry then it likely won't matter. But if you use a Rich Text Box for input, then you should use a Rich Text Box for output once the data is retrieved from the database, Newline characters and all. Even a single-quote won't matter as long as your ADO objects are written to allow single-quotes w/out needing to use escape characters or methods (such as double-single-quotes, or \', or whatever).

Otherwise, don't use a Rich Text Box or input or use simpler ASCII codes, if you are building a string, such as {Carriage Return} {Line Feed} rather than {Newline}.

Hope that helps.

character types

i am using sql server 2000, i am new to databases, could you please tell me what are the different character types, example "varchar , numeric...etc"
How to find out the difference between them...
Where do i find a tutorial for this kind of basic knowledge ?
regardsHomework questions are not permitted on these forums, and ones that are written so brazenly will just invite ridicule at the poster.

Have a good day now.

Regards,|||A great resource for this sort of things is our best friend Google...
Search for "T-SQL data types" and you'll get a lovely handful of links that will tell you exactly what you want to know! :)

Once you've had a read, if you any specific questions post them back here!|||Oh George, you really are far too tolerant.|||That's because I don't think this one is homework (yet) ;)|||homework is indeed allowed and is often some of the more interesting posts.|||homework is indeed allowed and is often some of the more interesting posts.

I'll get my coat.|||Hi there,

Check the SQL server 2000 help file (called books online or BOL), if you do a search on 'data types' you should find everything you need.|||I'll get my coat.
Well, it is allowed, but it is certainly treated differently.
We do our best to help the poster to the answer themselves, but if they're unwilling then so are we.

I can understand why you think this one might be homework - but I personally don't see it yet. If you asked me a year ago what the datatypes were, I could probably name 3 :p
Not to mention I'd have even less of an idea what "BoL" stands for ;)|||The ignorance of youth.

Once you get to my age (24) you tend to show less emotion and occasionally affront a manner of indifference, often unknowingly, when you fine yourself in a social predicament. Such causes of this decline to a dour emotional state vary widely, though one that invariably seems to be effective, and which is notably the most prominent, is when one is asked to answer a painfully trivial question. The immediate reaction is to swiftly determine whether the original post was said in jest, an attempted cure to the illness described above, or if in fact, it was a question. Occasionally in exercising this judgment, we may reach the wrong conclusion.|||Occasionally in exercising this judgment, we may reach the wrong conclusion.Been there, done that, got the blood-stained tee-shirts to prove it! No serious harm done, such is life.

Twenty four years old... Yikes, I remember that wistfully!

-PatP|||Twenty four years old... Yikes, I remember that wistfully!
-PatP

I'll let you know my perspective in the year 2047.|||I'll let you know my perspective in the year 2047.Assuming that I'm still alive at that point, I'll be eagerly awaiting your analysis! ;)

-PatP|||Once you get to my age (24)damn...people around you are gonna be in BIG trouble then, by the time you get to my age (47)|||I thought I was ahead of my age in cynicism, but I think Robert may have taken the gold for that ;)

kc3377, how are you doing with your original question, do you have all the answers you need?

Character limit for variables inside a stocked procedure

I am currently having a problem where my SQL server seems to lock any variables to 1000 characters (ie. varchar(8000) can only hold 1000)

I have read in numerous sources it was possible to change that limit so the varchar can truly hold the 8000 characters and not stop at 1000, but there was no info on how to do this.

I am looking for a "How to" to put this limit to 8000.

Thank you!

Try Varchar(MAX) . It should help you.|||

Hi , see this link

http://www.sqlmag.com/Articles/ArticleID/26654/pg/2/2.html

|||

How you insert data to your field? Are you use stored procedure or any type of parameter? check the size of your parameter if it is not limited to 1000 chars, I never had problems with varchar(8000) like you so check the way how you insert value to your cell.

Thanks

|||

The problem is with the SQL Server itself, it has no relation to the type of variable or any data passed to the sotred procedure. The number of character that a stored procedure variable CANNOT exceed 1000.

Thus, even if I do :

DECLARE @.SQL varchar(8000)

The @.SQL will not hold more than 1000 characters. And I need to fix that and cannot seem to find were to do so. 1000 character is fine for quite simple task, but we had some stocked procedure that would have required over 10K characters in order to do what we wanted to do.

If you have any idea on how to change the limitation on the number of character a variable within a stored procedure can hold, I am looking for it since it is quite limitating.

|||

Veritek:

The problem is with the SQL Server itself, it has no relation to the type of variable or any data passed to the sotred procedure. The number of character that a stored procedure variable CANNOT exceed 1000.

I believe you are mistaken.

Try this from Query Analyzer:

DECLARE @.test varchar(2000)
SELECT @.test = REPLICATE('1',1000) + REPLICATE('2',700)
PRINT LEN(@.test)
PRINT @.test

You will see that the length returned is 1700. And that the string printed contains both 1's and 2's.

Your problem lies elsewhere. Something else is truncating your data at 1000 characters.

|||Affirmative, length is indeed 1700... But then I do not know where I could look ...|||

Veritek:

Affirmative, length is indeed 1700... But then I do not know where I could look ...

Well, either do we since we haven't seen any code...

jpazgier has suggested that you review your parameters to make sure you are not truncating data before it gets to your stored procedure.

|||

Well, still unresolved, and wont be anytime soon now since I would seem to have a new problem with the server.

Since the stocked procedure is receiving data from an aspx/vb set of files. Even when the .vb is of size 0.

Reinstalling the softwares seem in order now...

Thank you tho for the help!

|||

Well, we actually had to disable the SP causing the problem since during the weekend it simply stoped working and kept returning an error which we fail to see where it comes from.

I think we need to upgrade our software :p

|||

Veritek:

Thus, even if I do :

DECLARE @.SQL varchar(8000)

The @.SQL will not hold more than 1000 characters.

How are you determining that @.SQL will not hold more than 1000 characters? Again, you've really not shown us any of your code so it's difficult for us to help. I strongly doubt that reinstalling software is the answer.

char vs. varchar

The company I'm contracting at has a guideline that table columns should be
of type char if less than 20 characters, otherwise varchar. This guideline
was just changed to a requirement. In my opinion, the choice between char an
d
varchar should consider variability of data size as well as need of
modification performance vs. read performance, and therefore shouldn't be
based on a fixed size. Any comments I could use to help my cause, or any
disagreement?
Thanks
Vern RabeVern Rabe wrote:

> In my opinion, the
> choice between char and varchar should consider variability of data
> size as well as need of modification performance vs. read
> performance, and therefore shouldn't be based on a fixed size. Any
> comments I could use to help my cause, or any disagreement?
I agree with you. When for example you got a FirstName field, there
are names from 3 chars till 18 (in an example DB). Why would you waste
the space by using char? I only use char when the column length is the
same for every row. Good luck convincing the company ;)
Kind regards,
Stijn Verrept.|||Vern Rabe wrote:
Another advantage of using varchars for non fixed length columns: when
the text entered in a char column is smaller than the size of that
column it will be padded to the correct length so you'll need to handle
this in your application or use trim queries.
Kind regards.|||I'd like to hear the company's rationale for this requirement but a length
of 20 characters seems a bit excessive to me. Data are typically read much
more often than written. Although inexpensive storage mitigates the need
for byte counting, I don't see how one can justify using a particular data
type before the schema or application is designed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vern Rabe" <VernRabe@.discussions.microsoft.com> wrote in message
news:194CC9B9-0702-4E74-B3D2-602BA234DEA9@.microsoft.com...
> The company I'm contracting at has a guideline that table columns should
> be
> of type char if less than 20 characters, otherwise varchar. This guideline
> was just changed to a requirement. In my opinion, the choice between char
> and
> varchar should consider variability of data size as well as need of
> modification performance vs. read performance, and therefore shouldn't be
> based on a fixed size. Any comments I could use to help my cause, or any
> disagreement?
> Thanks
> Vern Rabe|||Char is for fixed width text while VarChar is for variable width text. If
the column is updated frequently, they may be concerned that changing the
length of data in a VarChar would result in page splits. However, this is a
very specific situation and would not justify using Char instead of VarChar
as a general rule. Find out who is responsible for defining database design
requirements, and ask them about it.
"Vern Rabe" <VernRabe@.discussions.microsoft.com> wrote in message
news:194CC9B9-0702-4E74-B3D2-602BA234DEA9@.microsoft.com...
> The company I'm contracting at has a guideline that table columns should
> be
> of type char if less than 20 characters, otherwise varchar. This guideline
> was just changed to a requirement. In my opinion, the choice between char
> and
> varchar should consider variability of data size as well as need of
> modification performance vs. read performance, and therefore shouldn't be
> based on a fixed size. Any comments I could use to help my cause, or any
> disagreement?
> Thanks
> Vern Rabe

CHAR vs. VARCHAR

What are the pros & cons of each datatype (char and varchar)?
I have several reference (lookup) tables that use the varchar. Would there
be any reason to convert these to char datatypes?Hi Wes,
It depends what you are doing, if your data is of fixed length, say a 8
letter code then use CHAR, there isn't the overhead (abeit small) of keeping
track of the varying length.
Varchar is good for text that is of varying length, for instance comments,
subject, titles etc... and can save significant space, if you made a title
char(500) then all the rows would be 500 bytes for that column (a lot of
wasted space).
Enter nvarchar and nchar; these are the recommended types to use in SQL
Server now and some things in Integration Services like the text extraction
require them. nchar/nvarchar stores 2 bytes per character and is for unicode
character sets. Personally, i dislike it as the systems i use aren't going
to require the 2 bytes, but for big multi-national stuff its the way
forward.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:06343322-86C2-444F-8149-7AA05F4A32DA@.microsoft.com...
> What are the pros & cons of each datatype (char and varchar)?
> I have several reference (lookup) tables that use the varchar. Would
> there
> be any reason to convert these to char datatypes?
>|||http://www.aspfaq.com/2354
http://tinyurl.com/cvtjm
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:06343322-86C2-444F-8149-7AA05F4A32DA@.microsoft.com...
> What are the pros & cons of each datatype (char and varchar)?
> I have several reference (lookup) tables that use the varchar. Would
> there
> be any reason to convert these to char datatypes?
>|||Tony,
You say that nchar & nvarchar are the recommended types now. Does this have
something to do with Sql Server 2005? If not, then why is this the
recommendation?
"Wes" wrote:

> What are the pros & cons of each datatype (char and varchar)?
> I have several reference (lookup) tables that use the varchar. Would ther
e
> be any reason to convert these to char datatypes?
>|||To add, fixed length datatypes internally consume the whole defined size
regardless of what you actually store in them. Variable length datatypes
physically consume only what you store in them, plus 2 bytes per column used
as an offset.
When you modify a value of a fixed type value, there will never be a need
for the storage space to expand. When you modify a variable type value, to a
longer one, it will need to physically expand the storage space, which might
result in a page split if the row resides in an index (clustered or
nonclustered), and there's no room for the expanded row in the page. If the
table is a heap (no clustered index), SQL Server will need to move the row
to a new location and leave a forwarding pointer in the original slot.
So generally speaking, in terms of modifications, fixed length types are
more appropriate.
On the other hand, fixed length types typically consume more space because
the always utilize the defined size. So retrieval of data typically results
in less I/O with variable length types.
So generally speaking, in terms of retrieval, variable length columns are
more appropriate.
Of course, in mixed systems where you do both modifications and retrievals
you need to prioritize what's more important to you, and in which types of
activities the systems suffers more.
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:06343322-86C2-444F-8149-7AA05F4A32DA@.microsoft.com...
> What are the pros & cons of each datatype (char and varchar)?
> I have several reference (lookup) tables that use the varchar. Would
> there
> be any reason to convert these to char datatypes?
>|||> You say that nchar & nvarchar are the recommended types now. Does this
> have
> something to do with Sql Server 2005? If not, then why is this the
> recommendation?
Because people are finally realizing that not all data is American, and does
not fit nicely in the character set support by non-Unicode data types.|||examnotes <Wes@.discussions.microsoft.com> wrote in
news:CE34A109-71C6-40B2-BEB1-1EE1F524E14E@.microsoft.com:

> You say that nchar & nvarchar are the recommended types now. Does
> this have something to do with Sql Server 2005? If not, then why is
> this the recommendation?
nchar and nvarchar is Unicode, and thus allows for storing character data
from other languages than English without any trouble. For instance, most
of you guys (Except Sommarskog) could possible have troble saving my
surname using char or varchar :)
When using unicode you can save information with different character sets,
as for instance nordic (my surname), gr and cyrillic. Of course, at the
cost of some extra bytes.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||I should also add that since these are lookup tables which are very small
anyway there are special considerations. If the typical types of access
methods against those are index s operations, read performance won't
really be affected by the choice of fixed/dynamic columns.
Also, comparing the physical I/O against the data tables vs. the lookup
tables, the lookups' part is typically very small.
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OaSbj1y3FHA.128@.tk2msftngp13.phx.gbl...
> To add, fixed length datatypes internally consume the whole defined size
> regardless of what you actually store in them. Variable length datatypes
> physically consume only what you store in them, plus 2 bytes per column
> used as an offset.
> When you modify a value of a fixed type value, there will never be a need
> for the storage space to expand. When you modify a variable type value, to
> a longer one, it will need to physically expand the storage space, which
> might result in a page split if the row resides in an index (clustered or
> nonclustered), and there's no room for the expanded row in the page. If
> the table is a heap (no clustered index), SQL Server will need to move the
> row to a new location and leave a forwarding pointer in the original slot.
> So generally speaking, in terms of modifications, fixed length types are
> more appropriate.
> On the other hand, fixed length types typically consume more space because
> the always utilize the defined size. So retrieval of data typically
> results in less I/O with variable length types.
> So generally speaking, in terms of retrieval, variable length columns are
> more appropriate.
> Of course, in mixed systems where you do both modifications and retrievals
> you need to prioritize what's more important to you, and in which types of
> activities the systems suffers more.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> Join us for the SQL Server 2005 launch at the SQL W in Israel!
> [url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
>
> "Wes" <Wes@.discussions.microsoft.com> wrote in message
> news:06343322-86C2-444F-8149-7AA05F4A32DA@.microsoft.com...
>|||Great feedback from everyone. Thanks.
"Itzik Ben-Gan" wrote:

> To add, fixed length datatypes internally consume the whole defined size
> regardless of what you actually store in them. Variable length datatypes
> physically consume only what you store in them, plus 2 bytes per column us
ed
> as an offset.
> When you modify a value of a fixed type value, there will never be a need
> for the storage space to expand. When you modify a variable type value, to
a
> longer one, it will need to physically expand the storage space, which mig
ht
> result in a page split if the row resides in an index (clustered or
> nonclustered), and there's no room for the expanded row in the page. If th
e
> table is a heap (no clustered index), SQL Server will need to move the row
> to a new location and leave a forwarding pointer in the original slot.
> So generally speaking, in terms of modifications, fixed length types are
> more appropriate.
> On the other hand, fixed length types typically consume more space because
> the always utilize the defined size. So retrieval of data typically result
s
> in less I/O with variable length types.
> So generally speaking, in terms of retrieval, variable length columns are
> more appropriate.
> Of course, in mixed systems where you do both modifications and retrievals
> you need to prioritize what's more important to you, and in which types of
> activities the systems suffers more.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> Join us for the SQL Server 2005 launch at the SQL W in Israel!
> [url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
>
> "Wes" <Wes@.discussions.microsoft.com> wrote in message
> news:06343322-86C2-444F-8149-7AA05F4A32DA@.microsoft.com...
>
>|||Ole,
But the non-Unicode Latin1 datatypes *does* support the Nordic characters, a
long with the "western
European" characters.
(But you will of course run into problems when you get into eastern Europe,
and of course Russia,
Asia etc.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns9701E9167880Folekristianbangaas@.
207.46.248.16...
> examnotes <Wes@.discussions.microsoft.com> wrote in
> news:CE34A109-71C6-40B2-BEB1-1EE1F524E14E@.microsoft.com:
>
> nchar and nvarchar is Unicode, and thus allows for storing character data
> from other languages than English without any trouble. For instance, most
> of you guys (Except Sommarskog) could possible have troble saving my
> surname using char or varchar :)
> When using unicode you can save information with different character sets,
> as for instance nordic (my surname), gr and cyrillic. Of course, at the
> cost of some extra bytes.
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging

char vs. varchar

Greetings,

I have a question. I work on some SQL2k/ASP.NET apps at work. My
predacessor, who created the databases/tables seemed to have liked to
use 'char' for all text fields. Is there a reason why he would have
done this over using varchar? It's a minor annoyance to always have to
RTRIM data and it makes directly making changes to the database more
annoying (with all the pointless trailing spaces)?

I usually use char for fixed string lengths, like state abbreviations
or something, and varchar for strings of unknown length.

Is it a performance issue? Our database doesn't do much traffic, for
the most part.It's not a performance issue unless you're using varchar(1) and the
overhead that incurs and have millions of records and higher traffic
than you probably have.

As a matter of fact, for larger char() fields, they can be slower than
varchar(), because it has to physically store more data pages than if
you used varchar(). If there are more data pages for the same number
of records, things get slower.

Make life easy on yourself and use varchar(). Don't use varchar(1)
though. I have seen people use it.|||The difference between char and varchar are in both storage and performance:

1. Storage wise: char columns have fixed length. If the user supplied value
for the column is less than the fixed length defined in the schema, the
column is padded with 0 at end to make the total length fixed. varchar
doesn't have a fixed length thus no padding is needed. But as the result
varchar columns have to store the size of the data together with the column
data, which takes an extra 2 bytes per varchar column.

2. Performance wise locating char is a little faster than varchar. Since
char columns have fixed length, they are stored in fixed location in a row.
This means locating a char column can directly jump to the fixed location in
a row to read. For varchar column since the size of the data is variable,
they can't be stored in fixed location in a row and rather there is soem
kind of lookup table in the row format to store the location of each varchar
column. This means locating a varchar column has to lookup the location of
the column in the lookup table stored in the row first before jumping to the
location to read. Referencing the lokup table introduces some perofrmance
overhead, especially ifthe lookup table reference causes cache line miss.

In summary, it is a matter of trade-off between padding+faster locate and
2-bytes-overhead-per-column+slower locate when choosing char v.s. varchar.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
<dmhendricks@.despammed.com> wrote in message
news:1105723409.312275.186390@.f14g2000cwb.googlegr oups.com...
> Greetings,
> I have a question. I work on some SQL2k/ASP.NET apps at work. My
> predacessor, who created the databases/tables seemed to have liked to
> use 'char' for all text fields. Is there a reason why he would have
> done this over using varchar? It's a minor annoyance to always have to
> RTRIM data and it makes directly making changes to the database more
> annoying (with all the pointless trailing spaces)?
> I usually use char for fixed string lengths, like state abbreviations
> or something, and varchar for strings of unknown length.
> Is it a performance issue? Our database doesn't do much traffic, for
> the most part.|||You would never see a practical performance advantage in using char
over varchar, unless you had an extremely high transaction application.
The varchar offset lookup is optimized in-memory. The real bottleneck
is disk I/O, not a few extra CPU cycles from looking up varchar
offsets.

If I have an app that uses a char(80), versus an app that uses a
varchar(80), with an average width of data of 40, the char(80) data is
going to use approximately twice as many data pages to store the data.
That means twice as much disk I/O to read the table, which is where the
real bottleneck is.

I don't believe there is any tradeoff here.|||Gary, see inline

Gary wrote:
> You would never see a practical performance advantage in using char
> over varchar, unless you had an extremely high transaction application.
> The varchar offset lookup is optimized in-memory.

I agree that you won't see any performance degradation here.

> The real bottleneck is disk I/O, not a few extra CPU cycles from looking up varchar
> offsets.

Again, I agree

> If I have an app that uses a char(80), versus an app that uses a
> varchar(80), with an average width of data of 40, the char(80) data is
> going to use approximately twice as many data pages to store the data.
> That means twice as much disk I/O to read the table, which is where the
> real bottleneck is.

This is only true in a perfect world scenario. If there is insufficient
free space to accomodate changes in the varchar data, then change
changes in the varchar(80) data will lead to fragmentation. Changes in
the char(80) data will not lead to fragementation, because any
replacement can be done in-place (assuming columns not part of a
clustered index).

So depending on the fill-factor, number of data changes, etc.
fragmentation will be a little or much greater for varchar compared to
char. This fragmentation is (as you probably know) especially expensive,
because it needs random I/O which is slower than sequential I/O.

I you reindex regularly, and have a sufficient fill factor, then
varchar(80) should always perform better if the average length is only
40.

> I don't believe there is any tradeoff here.

Personally, I don't see a good reason why one would ever choose a
varchar over char when the maximum size is 4 characters or less. For
sizes over 10 characters I tend to choose varchar almost automatically.
For anything between 4 and 10 I really think about the situation before
deciding char or varchar.

Gert-Jan|||Gert-Jan -

I totally agree with you. I simplified the situation quite a bit, but
with all other things being equal, yours is a good "guesstimate".

I ran a test "perfect world" scenario of char(80) vs. varchar(80) (40
char avg len), and both the CPU time and disk I/O were about 40% higher
with the char(80) scenario with 10000 records.

Gary

char vs varchar and indexes

all these while i've only used varchar for any string

i heard from my ex-boss that char helps speed up searches. is that
true?

so there are these:

1) char with index
2) char without index
3) char with clustered index
4) varchar with index
5) varchar without index
6) varchar with clustered index

some of my tables primary key (clustered) is a string type. would it
be 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?oh and

if the column is char(10)

and there's this data 'abc '

so is there a difference between these two ?

select * from t1 where col = 'abc'

or

select * from t1 where col='abc '|||Nick Chan (zzzxtreme@.yahoo.com) writes:

Quote:

Originally Posted by

all these while i've only used varchar for any string
>
i heard from my ex-boss that char helps speed up searches. is that
true?
>
so there are these:
>
1) char with index
2) char without index
3) char with clustered index
4) varchar with index
5) varchar without index
6) varchar with clustered index
>
some of my tables primary key (clustered) is a string type. would it
be benificial to use char? or would using (6) makes no difference?

The choice between char and varchar should be made be from the business
rules. If I see a char(12) column, I expect most columns to have 12
characters without trailing blanks.

I can't see why char would things faster. The physical layout of the row
is somewhat simpler, but on the other hand if the average length is far
from the max length, the char columns takes up more space, and more
space means more pages to read, and thus longer access times.

Quote:

Originally Posted by

if the column is char(10)
>
and there's this data 'abc '
>
>
so is there a difference between these two ?
>
select * from t1 where col = 'abc'
>
or
>
select * from t1 where col='abc '

Why don't you test? I think they are the same, as trailing blanks are
ignore when comparing. But these two are not the same:

SELECT * FROM tbl WHERE col LIKE @.varcharval + '%'
SELECT * FROM tbl WHERE col LIKE @.charval + '%'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Nick Chan wrote:

Quote:

Originally Posted by

>
all these while i've only used varchar for any string
>
i heard from my ex-boss that char helps speed up searches. is that
true?
>
so there are these:
>
1) char with index
2) char without index
3) char with clustered index
4) varchar with index
5) varchar without index
6) varchar with clustered index
>
some of my tables primary key (clustered) is a string type. would it
be 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?

I don't think there is a big performance difference between
handling/comparing a char column versus a varchar column.

So for optimal performance, it comes down to two other aspects, required
space and fragmentation.

A varchar has an overhead of 2 bytes per values. These 2 bytes specify
the length of the value. Also, if the column in question is the only
varchar column in the table, then you should add another byte (because
that byte would be saved if no varchar columns were used). So then,
based on the average value length, you can calculate whether char or
varchar uses the least space. For example, a varchar(10) with an average
data length of 6 would require less space than a char(10). Another
example: a varchar(2) will always be less space efficient than a
char(2).

The other consideration is fragmentation. If you use a varchar column,
and it is updated often, and the updates will often change the data
length of the value, then this will cause fragmentation. Updates of a
char column can always be done in place, which minimizes fragmentation.

So in general, if the column's defined size is small, or if the average
data length is close to the defined length, then you best choose char,
otherwise, use varchar.

--
Gert-Jan|||Thanks guys for the replies !!
On Sep 8, 3:48 am, Gert-Jan Strik <so...@.toomuchspamalready.nlwrote:

Quote:

Originally Posted by

Nick Chan wrote:
>

Quote:

Originally Posted by

all these while i've only used varchar for any string

>

Quote:

Originally Posted by

i heard from my ex-boss that char helps speed up searches. is that
true?

>

Quote:

Originally Posted by

so there are these:

>

Quote:

Originally Posted by

1) char with index
2) char without index
3) char with clustered index
4) varchar with index
5) varchar without index
6) varchar with clustered index

>

Quote:

Originally Posted by

some of my tables primary key (clustered) is a string type. would it
be benificial to use char? or would using (6) makes no difference?

>

Quote:

Originally Posted by

for non primary key columns that needs to be searched a lot, can i say
(1) is the best?

>
I don't think there is a big performance difference between
handling/comparing a char column versus a varchar column.
>
So for optimal performance, it comes down to two other aspects, required
space and fragmentation.
>
A varchar has an overhead of 2 bytes per values. These 2 bytes specify
the length of the value. Also, if the column in question is the only
varchar column in the table, then you should add another byte (because
that byte would be saved if no varchar columns were used). So then,
based on the average value length, you can calculate whether char or
varchar uses the least space. For example, a varchar(10) with an average
data length of 6 would require less space than a char(10). Another
example: a varchar(2) will always be less space efficient than a
char(2).
>
The other consideration is fragmentation. If you use a varchar column,
and it is updated often, and the updates will often change the data
length of the value, then this will cause fragmentation. Updates of a
char column can always be done in place, which minimizes fragmentation.
>
So in general, if the column's defined size is small, or if the average
data length is close to the defined length, then you best choose char,
otherwise, use varchar.
>
--
Gert-Jan- Hide quoted text -
>
- Show quoted text -

char vs varchar - reclaiming free space

One of our customers is using MSDE2000 and has reached 2 gb limit. After
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan Matic
Hi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/de...r_da2_1uzr.asp
John
"DRagan Matic" wrote:

> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>
|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy

char vs varchar - reclaiming free space

One of our customers is using MSDE2000 and has reached 2 gb limit. After
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan MaticHi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
John
"DRagan Matic" wrote:

> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using
:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy

char vs varchar - reclaiming free space

One of our customers is using MSDE2000 and has reached 2 gb limit. After
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan MaticHi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
John
"DRagan Matic" wrote:
> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy

Char vs Varchar

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!!
Rudrafixed length identifier fields? even though smart numbers are stupid.|||use CHAR(n) instead of VARCHAR(n) when n<4

consider using CHAR instead of VARCHAR when there's only one non-null VARCHAR in the table

what did you mean by "smarter" anyway?|||fixed length identifier fields? even though smart numbers are stupid.

Yea, I have seen those in fixed length identifier fields,any more use of char?
Well, in database designing which attributes are assigned as char?
If we don't use all the characters tehn its a mess...what are the best situation to use char? Plz comment...

It may seem a silly one but I think it has an important significance in database designing...:)
Thanks!!
Joydeep|||use CHAR(n) instead of VARCHAR(n) when n<4

Yea thats a very good point...:rolleyes:
and

consider using CHAR instead of VARCHAR when there's only one non-null VARCHAR in the table

that should be applied when and only when n<4.Isn't it?
ok,thank you for those info.

what did you mean by "smarter" anyway

by smarter I mean to say varchar though variable length does provide more efficient storage than char and also doesn't need any trim functions to compare...and many more are there...:)

Thanks!!
Joydeep|||yes, those are advantages for VARCHAR, good points

there really isn't any reason to have CHAR, when you think about it

i'm guessing it must be an historic relic from back in the days when databases were a lot less efficient handling VARCHARs|||i'm guessing it must be an historic relic from back in the days when databases were a lot less efficient handling VARCHARs
LOL,:D
rudy.ca... its cool and your pic too :)
Thanks again r937|||Oh heavens, there are lots of reasons for using the CHAR datatype. It is far more efficient when dealing with "character indicators" which are short, fixed length strings (like Y/N, M/F, etc). CHAR is also better for moving data back and forth between today's equipment and yesterday's equipment... It is practically impossible to deal with variable length columns in Z/OS, and many of us still have to deal with things like that.

In general, I prefer to use VARCHAR, but there are times and reasons to use CHAR, and I wouldn't want to be without it as a choice.

-PatP|||Oh heavens, there are lots of reasons for using the CHAR datatype. It is far more efficient when dealing with "character indicators" which are short, fixed length strings (like Y/N, M/F, etc). CHAR is also better for moving data back and forth between today's equipment and yesterday's equipment... It is practically impossible to deal with variable length columns in Z/OS, and many of us still have to deal with things like that.

-PatP
And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fields.:rolleyes:

Thanks Pat
Joydeep|||And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fields.:rolleyes:

Thanks Pat
Joydeep

I would say using CHAR in the zipcode is a good idea. I'm in Canada and we have postal codes that contain letters and numbers. Not only that, they are 6 characters long! I've seen some pretty bad e-commerce sites that wouldn't let me put in my address because their "zip code" field wouldn't let me enter the last character of my postal code.|||And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fieldsWell, the rule about using CHAR when length < 4 really applies to variable length strings less than four characters. Any time you have a fixed-length string (such as a five digit zip code or a nine digit social security number) CHAR is more appropriate and more efficient than VARCHAR.|||Well, the rule about using CHAR when length < 4 really applies to variable length strings less than four characters.i do believe i said that quite early in the thread :)

Any time you have a fixed-length string (such as a five digit zip code ...in this particular case VARCHAR(37) would've been way better, since it would allow you to store 9-digit (or 10 character, if you store the dash between the 5 digits and the 4) with absolutely no change to your database or your app

whereas with CHAR(5) for the zip code, you're screwed

another fine example of the one of the many benefits of VARCHAR

;)|||i do believe i said that quite early in the threadGreat advice is worth saying twice, eh?

in this particular case VARCHAR(37) would've been way better, since it would allow you to store 9-digit (or 10 character, if you store the dash between the 5 digits and the 4) with absolutely no change to your database or your appI'm a strong believer in storing ZIP and ZIP4 as separate fields. Pesky normalization habits of mine...|||I'm a strong believer in storing ZIP and ZIP4 as separate fields. Pesky normalization habits of mine...oh you silly man

okay, either you are consistent and silly, or else you are inconsistent and pragmatic, but please don't use "normalization" as an excuse for rationalize it either way

do you put house number in a separate column? i.e. not address1='123 sesame st' but address1_number='123', address1_street='sesame st'

do you put apartment/suite number in a separate column?

do you put zip code into a different table? after all, it's in a one-to-many relationship with addresses, so if a zip code changes, wouldn't you want to use a surrogate key instead?

and really, the 4-digit zip code suffix is functionally dependent on the 5-digit zip code prefix, so if you have those two columns side by side in the same row, what does that do for your normalization efforts?

address fields are NOTORIOUSLY the wrong example to use when discussing normalization

:)|||I always enjoy a goo d pedantic discussion...

Speaking of OS/390 z/OS

varchar is still painful in DB2 for the Client and/or COBOL Sprocs?

I'm about to launch a new dev project there and am in the middle of building the model soon...and they want free form description columns out the but at 300 bytes...

I need to talk them down to 255 to avoid LONG datatypes, but since they have so many, I was hoping to use varhcar.

I'll use char to make life easier, because I really don't care about DASD all that much...I just imagine speed will be impacted because of the misuse of the buffers...|||I'll use char to make life easier, because I really don't care about DASD all that much...I just imagine speed will be impacted because of the misuse of the buffers...

Is there anybody who cares for Domain Integrity? I think there should be some specific norms for database designing and normalization.Then the fuss about char and varchar implementation should have been gone..;)

Joydeep|||Is there anybody who cares for Domain Integrity? I think there should be some specific norms for database designing and normalization.Then the fuss about char and varchar implementation should have been gone..;)

Joydeep

Really now.

At the moment, I just want to slam the damn thing into production and make the deadline.

If this were SQL Server I was working on, it wouldn't be a problem.

I think I'm gonna go with:

, COL1 CHAR(255) NOT NULL WITH DEFAULT

I want my developers to be happy...actually I want my developers to be productive and accurate, i.e. I don't want code blowing up all over the place...

Ever seen an external COBOL Stored Procedure for DB2 OS/390?

And they've implemented some heavy duty Changeman procedures...they can't even compile code unless it's in a package...|||Really now.

At the moment, I just want to slam the damn thing into production and make the deadline.

If this were SQL Server I was working on, it wouldn't be a problem.

I think I'm gonna go with:

, COL1 CHAR(255) NOT NULL WITH DEFAULT

I want my developers to be happy...actually I want my developers to be productive and accurate, i.e. I don't want code blowing up all over the place...

Ever seen an external COBOL Stored Procedure for DB2 OS/390?

And they've implemented some heavy duty Changeman procedures...they can't even compile code unless it's in a package...
I agree with you for the above case.But don't you think database designing should involve a greater time than the rest of the jobs in production? Meeting deadlines is always a headache,but do you think we could sacrifice the dedicated time of designing for the sake of deadline only? :)

Joydeep|||do you put house number in a separate column? i.e. not address1='123 sesame st' but address1_number='123', address1_street='sesame st'I'd do it in a second if it didn't place an undo burden on the person entering the data, and if there were a simple method of ensuring data entry integrity. Many business processes (such as bulk mail discounts) require the address to be parsed and sorted in a specific manner.

do you put zip code into a different table? after all, it's in a one-to-many relationship with addresses, so if a zip code changes, wouldn't you want to use a surrogate key instead?I would absolutely do that if I had additional zip code attributes to store, such as demographics. Heck, I might do it just to ensure the validity of the zip codes that are entered. Yeah, it IS a one-to-many relationship, whether you choose to materialize the data or not.

and really, the 4-digit zip code suffix is functionally dependent on the 5-digit zip code prefix, so if you have those two columns side by side in the same row, what does that do for your normalization efforts?Yes, for stricty relationtional integrity zip4 codes should be a subtable of zip, and only the foreign key to zip4 should be stored in the adress table. But most applications do not require and cannot efficiently enforce these rules on the users. Asking the user to separte ZIP from ZIP4, however, is a pretty small request, and greatly facilitates grouping and sorting by zip code when zip4 is not required.
address fields are NOTORIOUSLY the wrong example to use when discussing normalizationAu contraire! The notorious unreliability of address fields make them a great cautionary tale against storing multiple attributes in a single column.|||Well, let's see.

The business hired a management team to develop the specs. I have been going through them and we are working out the kinks. The requirements are what they are, but the business keeps saying they don't know what they want specifically.

I'm ok with that, and I've already made modifications to tha model. It's pretty sound, but there are some definete kluges in there.

Also, I have the management team to blame.

I will still be worrying about performance and data integrity...but the integrity might take a hit in some places...

I'm gonna start a new thread|||Well, let's see.

The business hired a management team to develop the specs. I have been going through them and we are working out the kinks. The requirements are what they are, but the business keeps saying they don't know what they want specifically.

I'm ok with that, and I've already made modifications to the model. It's pretty sound, but there are some definete kluges in there.

Also, I have the management team to blame.
I'm gonna start a new thread
Yea, thats a common problem.They don't even clear up the functional areas properly.That makes the case complicated.Too many groups spoil the broth.
I think you need more patience than technical expertise here...:D
Good luck!!
Joydeep|||Trick is to not care so much

char or varchar?

Is it better to set a column type to char(x) or varchar(x), from the indexes
point of view? Which is faster?
Also which is more convinient when comparing strings (do i have to right
trim char(x) columns before comparing)?It depends on your requirements. SQL Server stores the data for these
datatypes diffrerently
1) VARCHAR(n)
SQL Server stores 1 byte per character.Declared but unused charcters do not
consume storage
2) CHAR(n)
SQL Server stores 1byte per charcter n declared ,event if partialy unused
"Savvoulidis Iordanis" <iordanis_sav@.hotmail.com> wrote in message
news:OF%23DNEVOHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Is it better to set a column type to char(x) or varchar(x), from the
> indexes point of view? Which is faster?
> Also which is more convinient when comparing strings (do i have to right
> trim char(x) columns before comparing)?
>
>|||On Tue, 16 Jan 2007 08:05:56 +0200, "Savvoulidis Iordanis"
<iordanis_sav@.hotmail.com> wrote:

>Is it better to set a column type to char(x) or varchar(x), from the indexe
s
>point of view? Which is faster?
>Also which is more convinient when comparing strings (do i have to right
>trim char(x) columns before comparing)?
It depends. 8-)
On the one hand, a varying length column takes more processing than a
fixed length column. I seem to remember hearing that this is
especially true when they are used in an index.
On the other hand, a varchar column MAY, depending on the size of the
actual data compared to the declared size, save a lot of space. And
wasting space means the index takes up more space, which is to say
more pages. More pages means more I/O, and consuming more space in
the cache - or fitting fewer pages in the cache.
(Keep in mind that varchar has a two-byte overhead that char lacks. So
a varchar(5) column uses up 2 to 7 bytes of storage.)
There are some clear-cut cases. A column that averages 6 characters
of data would be better as a varchar(30) than as a char(30), even if
used in an index. But a column that averages 6 characters of data
would probably be better as a char(10) than a varchar(10 for indexing.
Sorry, I don't have a magic number to tell you when the balance tips
from one to the other.
Roy Harvey
Beacon Falls, CT|||As well as Uri's and Roy's comments, you might want to consider the
update/insert ratio as another factor n deciding which datatype to use. If
there are a lot of updates you might potentially end up with many forwarding
pointers if there isn't enough space to expand the varchar column's data
in-place, so in such circumstances varchar may become slower than char
access. Running DBCC SHOWCONTIG and supplying the option WITH TABLERESULTS
will give you an indication if this is an issue for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .