Check for numeric value

Is it possible to check whether a column (define as char
type) value is numeric using something like
Select * From table_name Where Field1 is numeric ?
Thanks,
BenYou can use the ISNUMERIC function for this:
WHERE ISNUMERIC(colname) = 1
Note, however that this returns 1 if the data can be converted to int,
float, money etc. So things like "E" and "," in the string will pass the
test. If you post what you mean precisely by "numeric" we can possibly give
a better suggestion.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ben" <bluebells88@.yahoo.com> wrote in message
news:2eb201c3a9c4$176cb430$a601280a@.phx.gbl...
> Is it possible to check whether a column (define as char
> type) value is numeric using something like
> Select * From table_name Where Field1 is numeric ?
> Thanks,
> Ben|||Thank your very much for your answer. This one works fine
for my case :)
I've another question: Is it possible to determine if the
column value is NOT alphabet. (using ASCII ? It seems
impossible to me.)
Thanks,
Ben
>--Original Message--
>You can use the ISNUMERIC function for this:
>WHERE ISNUMERIC(colname) = 1
>Note, however that this returns 1 if the data can be
converted to int,
>float, money etc. So things like "E" and "," in the
string will pass the
>test. If you post what you mean precisely by "numeric"
we can possibly give
>a better suggestion.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Ben" <bluebells88@.yahoo.com> wrote in message
>news:2eb201c3a9c4$176cb430$a601280a@.phx.gbl...
>> Is it possible to check whether a column (define as
char
>> type) value is numeric using something like
>> Select * From table_name Where Field1 is numeric ?
>> Thanks,
>> Ben
>
>.
>

Check Contraint question

I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) + ']%'),
1;LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is the
re a way to have it not throw an error, but rather just remove the offending
characters if entered? ThanksNo, that's not what a constraint does.
You can perhaps use an instead-of trigger to achieve this functionality.
Conor
"Burma Jones" <somebody@.somedomain.not> wrote in message
news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
']%'),[LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is
there a way to have it not throw an error, but rather just remove the
offending characters if entered? Thanks|||No. Constraints are declarative and do not perform actions. I would
do this kind of thing inthe front end or in the inpout procedure.
Triggers will fire any time the table is touched and work on all rows,
so they can be a bit costly.|||Since this is only a few thousand records, I'm not too worried about the
cost of using a trigger. Can you share an example, even pseudocode, showing
how to create a trigger which will remove those characters? Thanks
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:eu$9uj9cGHA.4932@.TK2MSFTNGP03.phx.gbl...
> No, that's not what a constraint does.
> You can perhaps use an instead-of trigger to achieve this functionality.
> Conor
> "Burma Jones" <somebody@.somedomain.not> wrote in message
> news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
> I have the following check constraint
> (isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
> ']%'),[LicensePlateNumber]),0) = 0)
> which works fine, throwing an error if those characters are entered. Is
> there a way to have it not throw an error, but rather just remove the
> offending characters if entered? Thanks
>|||On Wed, 10 May 2006 08:26:16 -0700, Burma Jones wrote:

>Since this is only a few thousand records, I'm not too worried about the
>cost of using a trigger. Can you share an example, even pseudocode, showin
g
>how to create a trigger which will remove those characters? Thanks
Hi Burma,
Here's a sample trigger that will remove the offending characters
silently:
CREATE TRIGGER YourTrigger
ON YourTable INSTEAD OF INSERT
AS
INSERT INTO YourTable (OtherColumns, LicensePlate)
SELECT OtherColumns,
REPLACE(REPLACE(REPLACE(REPLACE(LicenseP
late, ' ', ''), CHAR(9),
''), CHAR(10), ''), CHAR(13), ''), OtherColumns
FROM inserted
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP

Check Contraint question

I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) + ']%'),[LicensePlateNumber]),0) =
0)
which works fine, throwing an error if those characters are entered. Is the
re a way to have it not throw an error, but rather just remove the offending
characters if entered? ThanksNo, that's not what a constraint does.
You can perhaps use an instead-of trigger to achieve this functionality.
Conor
"Burma Jones" <somebody@.somedomain.not> wrote in message
news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
']%'),[LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is
there a way to have it not throw an error, but rather just remove the
offending characters if entered? Thanks|||No. Constraints are declarative and do not perform actions. I would
do this kind of thing inthe front end or in the inpout procedure.
Triggers will fire any time the table is touched and work on all rows,
so they can be a bit costly.|||Since this is only a few thousand records, I'm not too worried about the
cost of using a trigger. Can you share an example, even pseudocode, showing
how to create a trigger which will remove those characters? Thanks
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:eu$9uj9cGHA.4932@.TK2MSFTNGP03.phx.gbl...
> No, that's not what a constraint does.
> You can perhaps use an instead-of trigger to achieve this functionality.
> Conor
> "Burma Jones" <somebody@.somedomain.not> wrote in message
> news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
> I have the following check constraint
> (isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
> ']%'),[LicensePlateNumber]),0) = 0)
> which works fine, throwing an error if those characters are entered. Is
> there a way to have it not throw an error, but rather just remove the
> offending characters if entered? Thanks
>|||On Wed, 10 May 2006 08:26:16 -0700, Burma Jones wrote:

>Since this is only a few thousand records, I'm not too worried about the
>cost of using a trigger. Can you share an example, even pseudocode, showin
g
>how to create a trigger which will remove those characters? Thanks
Hi Burma,
Here's a sample trigger that will remove the offending characters
silently:
CREATE TRIGGER YourTrigger
ON YourTable INSTEAD OF INSERT
AS
INSERT INTO YourTable (OtherColumns, LicensePlate)
SELECT OtherColumns,
REPLACE(REPLACE(REPLACE(REPLACE(LicenseP
late, ' ', ''), CHAR(9),
''), CHAR(10), ''), CHAR(13), ''), OtherColumns
FROM inserted
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP

CHECK ()?

What is best? What is right?
--This way:
---
CREATE TABLE Districts
(
District char(2)
NOT NULL,
Constraint PK_District
Primary Key(District)
)
-- Items Padro --
INSERT INTO Districts VALUES('AC')
INSERT INTO Districts VALUES('AL')
INSERT INTO Districts VALUES('AM')
INSERT INTO Districts VALUES('BA')
INSERT INTO Districts VALUES('CE')
INSERT INTO Districts VALUES('DF')
INSERT INTO Districts VALUES('ES')
INSERT INTO Districts VALUES('GO')
INSERT INTO Districts VALUES('MA')
INSERT INTO Districts VALUES('MG')
INSERT INTO Districts VALUES('MS')
INSERT INTO Districts VALUES('MT')
INSERT INTO Districts VALUES('PA')
INSERT INTO Districts VALUES('PB')
INSERT INTO Districts VALUES('PE')
INSERT INTO Districts VALUES('PI')
INSERT INTO Districts VALUES('PR')
INSERT INTO Districts VALUES('RJ')
INSERT INTO Districts VALUES('RN')
INSERT INTO Districts VALUES('RO')
INSERT INTO Districts VALUES('RR')
INSERT INTO Districts VALUES('RS')
INSERT INTO Districts VALUES('SC')
INSERT INTO Districts VALUES('SE')
INSERT INTO Districts VALUES('SP')
INSERT INTO Districts VALUES('TO')
CREATE TABLE foo
(
...
District char(2),
...
Constraint FK_District
Foreign Key(District)
References Districts(District),
)
--Or this way:
---
CREATE TABLE foo
(
...
District char(2) CONSTRAINT chk_District CHECK (
UF = 'AC' OR
UF = 'AL' OR
UF = 'AM' OR
UF = 'BA' OR
UF = 'CE' OR
UF = 'DF' OR
UF = 'ES' OR
UF = 'GO' OR
UF = 'MA' OR
UF = 'MG' OR
UF = 'MS' OR
UF = 'MT' OR
UF = 'PA' OR
UF = 'PB' OR
UF = 'PE' OR
UF = 'PI' OR
UF = 'PR' OR
UF = 'RJ' OR
UF = 'RN' OR
UF = 'RO' OR
UF = 'RR' OR
UF = 'RS' OR
UF = 'SC' OR
UF = 'SE' OR
UF = 'SP' OR
UF = 'TO')
...
)
ThanksI would go with a foreign key solution as it is more maintainable and a
naatual way to implement referential integrity.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:uZvY0ugkFHA.2608@.TK2MSFTNGP14.phx.gbl...
> What is best? What is right?
> --This way:
> ---
>
> CREATE TABLE Districts
> (
> District char(2)
> NOT NULL,
> Constraint PK_District
> Primary Key(District)
> )
> -- Items Padro --
> INSERT INTO Districts VALUES('AC')
> INSERT INTO Districts VALUES('AL')
> INSERT INTO Districts VALUES('AM')
> INSERT INTO Districts VALUES('BA')
> INSERT INTO Districts VALUES('CE')
> INSERT INTO Districts VALUES('DF')
> INSERT INTO Districts VALUES('ES')
> INSERT INTO Districts VALUES('GO')
> INSERT INTO Districts VALUES('MA')
> INSERT INTO Districts VALUES('MG')
> INSERT INTO Districts VALUES('MS')
> INSERT INTO Districts VALUES('MT')
> INSERT INTO Districts VALUES('PA')
> INSERT INTO Districts VALUES('PB')
> INSERT INTO Districts VALUES('PE')
> INSERT INTO Districts VALUES('PI')
> INSERT INTO Districts VALUES('PR')
> INSERT INTO Districts VALUES('RJ')
> INSERT INTO Districts VALUES('RN')
> INSERT INTO Districts VALUES('RO')
> INSERT INTO Districts VALUES('RR')
> INSERT INTO Districts VALUES('RS')
> INSERT INTO Districts VALUES('SC')
> INSERT INTO Districts VALUES('SE')
> INSERT INTO Districts VALUES('SP')
> INSERT INTO Districts VALUES('TO')
> CREATE TABLE foo
> (
> ...
> District char(2),
> ...
> Constraint FK_District
> Foreign Key(District)
> References Districts(District),
> )
>
> --Or this way:
> ---
> CREATE TABLE foo
> (
> ...
> District char(2) CONSTRAINT chk_District CHECK (
> UF = 'AC' OR
> UF = 'AL' OR
> UF = 'AM' OR
> UF = 'BA' OR
> UF = 'CE' OR
> UF = 'DF' OR
> UF = 'ES' OR
> UF = 'GO' OR
> UF = 'MA' OR
> UF = 'MG' OR
> UF = 'MS' OR
> UF = 'MT' OR
> UF = 'PA' OR
> UF = 'PB' OR
> UF = 'PE' OR
> UF = 'PI' OR
> UF = 'PR' OR
> UF = 'RJ' OR
> UF = 'RN' OR
> UF = 'RO' OR
> UF = 'RR' OR
> UF = 'RS' OR
> UF = 'SC' OR
> UF = 'SE' OR
> UF = 'SP' OR
> UF = 'TO')
> ...
> )
>
> Thanks
>|||I'd rather store the data in a table. Who wants to go modify a constraint
whenever a district is added or removed?

> What is best? What is right?|||Whatever works best for you. If you think the Districts will change
regularly or if they have to be maintained directly by users then a table is
be the obvious choice. If they'll change less often than you like to make
releases of the database then you can use a CHECK constraint.
David Portas
SQL Server MVP
--

chart axis

Does anyone know how one can control/modify/overwrite standard X and Y axis
in chart control? I am even reconsidering rewriting the whole char component
since it is far from what is must look like.
Thanks,
--
Martin Kulov
http://www.codeattest.com/blogs/martin
MCAD Charter Member
MCSD.NET Early Achiever
MCSD> Does anyone know how one can control/modify/overwrite standard X and Y
> axis in chart control?
My problem is that when I have two data series the X axis is grouped with
values from both series and makes it unreadable. How can I create two
separate X axis that can read easily?
Thanks,
--
Martin Kulov
http://www.codeattest.com/blogs/martin
MCAD Charter Member
MCSD.NET Early Achiever
MCSD
"Martin Kulov" <kulov@.bezbokluk.abv.bg> wrote in message
news:e$K3zardFHA.1036@.tk2msftngp13.phx.gbl...
> Does anyone know how one can control/modify/overwrite standard X and Y
> axis in chart control? I am even reconsidering rewriting the whole char
> component since it is far from what is must look like.
> Thanks,
> --
> Martin Kulov
> http://www.codeattest.com/blogs/martin
> MCAD Charter Member
> MCSD.NET Early Achiever
> MCSD
>

char(9) or char(11) type character

I have a square character in some of my columns in a database which I need t
o
weed out. Problem is, it seems like it is neither a char(9) nor a char(11)
and I have no way of knowing what it is.(old data)
I tried the following for testing purposes.
****************************************
************
CREATE TABLE [Test] (
[Column1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO
insert into Test (column1) values ('testvalueStart' + char(11) +
'testvalueend' + char(11))
select charindex(char(11), column1) from Test.
****************************************
******************
The above returns 15, as expected. This is probably because I know what I am
searching for (char(11) in this case). However, in the actual table when I
try charindex for a char(9) or a char(11) on the problematic column, it
always returns a 0.
Are there any other characters that get put in as a square ? Is there any
way of finding out? Or is there a better way of approaching this problem?
Any help is appreciated.
~Naveen> Are there any other characters that get put in as a square ? Is there any
> way of finding out?
DECLARE @.foo VARCHAR(100);
SELECT @.foo = COLUMN1 FROM Test; -- assumes 1 row
-- otherwise add where clause
DECLARE @.len INT, @.i INT;
SET @.len = LEN(@.foo);
SET @.i = 1;
WHILE @.i <= @.len
BEGIN
PRINT SUBSTRING(@.foo, @.i, 1) + ' = CHAR(' +
RTRIM(ASCII(SUBSTRING(@.foo, @.i, 1))) + ')';
SET @.i = @.i + 1;
END|||Thanks Aaron for the sample. Neat thing that I could use sometime.
However, it gives me the ascii values for all the characters and for some
reason it omits the last character which happens to be the problematic one.
By trial and error I found that that the square thingy is a char(0) or a
null, so that'll take care of my problem for now.
Basically what i'm saying is the method fails for a column that has a value
of
'teststring' + char(0). Workarounds for that?
"Aaron Bertrand [SQL Server MVP]" wrote:

>
>
>
>
> DECLARE @.foo VARCHAR(100);
>
> SELECT @.foo = COLUMN1 FROM Test; -- assumes 1 row
> -- otherwise add where clause
>
> DECLARE @.len INT, @.i INT;
>
> SET @.len = LEN(@.foo);
>
> SET @.i = 1;
>
> WHILE @.i <= @.len
> BEGIN
> PRINT SUBSTRING(@.foo, @.i, 1) + ' = CHAR(' +
> RTRIM(ASCII(SUBSTRING(@.foo, @.i, 1))) + ')';
> SET @.i = @.i + 1;
> END
>
>|||Naveen,
you can use the solution
as a pattern for your own one
if it is suitable for you
(you don't post ddl and sample data
therefore i solved the problem
with guess-work about that):
SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
CREATE TABLE Seq(seq INTEGER NOT NULL PRIMARY KEY);
INSERT INTO Seq
SELECT ten * 10 + unit + 1
FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Tens(ten)
CROSS JOIN
(SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Units(unit);
CREATE TABLE Foo(s VARCHAR(50) NOT NULL PRIMARY KEY);
INSERT INTO Foo(s)
SELECT 'test' + CHAR(0) + '0' + CHAR(0) + '0' UNION ALL
SELECT 'test22' + CHAR(3) + '3' + CHAR(4)+ '4' UNION ALL
SELECT 'test333' + CHAR(6) + '6' UNION ALL
SELECT 'test4444' + CHAR(10) + '10' UNION ALL
SELECT 'test55555' + CHAR(15) + '15' UNION ALL
SELECT 'test666666' + CHAR(21) + '21' UNION ALL
SELECT 'test7777777' + CHAR(28) + '28' UNION ALL
SELECT 'test88888888' + CHAR(36) + '36' UNION ALL
SELECT 'test999999999' + CHAR(45) + '45' UNION ALL
SELECT 'test0123456789' + CHAR(55) + '55' UNION ALL
SELECT 'test01234567890' + CHAR(66) + '66' UNION ALL
SELECT 'test012345678901' + CHAR(0x7F) + '7F' UNION ALL
SELECT 'TEST1' + CHAR(1) + '1';
SELECT F.s, ASCII(SUBSTRING(F.s, S.seq, 1)) AS cs, S.seq AS pos
FROM Foo AS F, Seq AS S
WHERE S.seq <= LEN(F.s)
AND ( ASCII(SUBSTRING(F.s, S.seq, 1)) < 32
OR ASCII(SUBSTRING(F.s, S.seq, 1)) = 0x7F);
DROP TABLE Foo;
DROP TABLE Seq;
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)

Char(4) or integer for Year / Quarter Field

Hello,
what's best for year resp. quarter Field, char(4) resp. char(1), integer or other?
Both are part of a composite index.
Thanks
Silaswhat does "resp." mean?

best for a numeric year like 1937, 2007, etc. is SMALLINT

best for quarter (1-4) is TINYINT|||Why not combine them both?

SMALLINT as 20074 (yyyyq)
this will work upto year 3276 and quarter 4.|||why not? because how would you pull out the rows for 2007 that way?

that's why not combine them :)|||why not? because how would you pull out the rows for 2007 that way?

that's why not combine them :)
That's why :-)

resp. = respectively , isn't it?|||I thought this could pull all records for 2007

Col1 >= 20071 And Col1 < 20081

but then again, I might be wrong.|||yes, that works, but it's clumsy

now show me how to get all the rows for the 3rd quarter only

clumsier and clumsier!!

;)

char(13) function is not working

Hello everyone,
In SQL Server books online there is this following example:
B. Use CHAR to insert a control character
This example uses CHAR(13) to print name, address, and city information
on separate lines, when the results are returned in text.
USE Northwind
SELECT FirstName + ' ' + LastName, + CHAR(13) + Address,
+ CHAR(13) + City, + Region
FROM Employees
WHERE EmployeeID = 1
Here is the result set:
Nancy Davolio
507 - 20th Ave. E.
Apt. 2A
Seattle WA
But when you run the select statement above in query analyzer the output
does not match the output shown above. The char(13) does not work as a
carriage return. Any thoughts as to why? I am working on SQL 2000 sp3
on Windows 2000.
Thanks,
Raziq.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:eOg32A0lEHA.324@.TK2MSFTNGP11.phx.gbl...
> But when you run the select statement above in query analyzer the output
> does not match the output shown above. The char(13) does not work as a
> carriage return. Any thoughts as to why? I am working on SQL 2000 sp3
> on Windows 2000.
Do you have results set to text mode or grid mode? You need to use text
mode to see line breaks. Also, Windows uses CR-LF, which is CHAR(13) +
CHAR(10), some other systems use only CR or LF.

char(13) function is not working

Hello everyone,
In SQL Server books online there is this following example:
B. Use CHAR to insert a control character
This example uses CHAR(13) to print name, address, and city information
on separate lines, when the results are returned in text.
USE Northwind
SELECT FirstName + ' ' + LastName, + CHAR(13) + Address,
+ CHAR(13) + City, + Region
FROM Employees
WHERE EmployeeID = 1
Here is the result set:
Nancy Davolio
507 - 20th Ave. E.
Apt. 2A
Seattle WA
But when you run the select statement above in query analyzer the output
does not match the output shown above. The char(13) does not work as a
carriage return. Any thoughts as to why? I am working on SQL 2000 sp3
on Windows 2000.
Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:eOg32A0lEHA.324@.TK2MSFTNGP11.phx.gbl...
> But when you run the select statement above in query analyzer the output
> does not match the output shown above. The char(13) does not work as a
> carriage return. Any thoughts as to why? I am working on SQL 2000 sp3
> on Windows 2000.
Do you have results set to text mode or grid mode? You need to use text
mode to see line breaks. Also, Windows uses CR-LF, which is CHAR(13) +
CHAR(10), some other systems use only CR or LF.

Char(10) breaking query

I have been working on a pretty ugly stored procedure recently, while debugging I added a char(10) to the end of each line of the SQL query so I could copy it to query analyzer(QA) and debug the SQL syntax output from of the stored procedure.

It had no effect on the stored procedure working, but when I copied the query to QA it got the error below, so I removed them all and added them in one line at a time to find the problem.

--Server: Msg 170, Level 15, State 1, Line 3
--Line 3: Incorrect syntax near ','.

Below are the 2 querys, the only difference is the Char(10) between Amt6 and Amt7!

http://www.rakbiz.com/download/broken.txt
http://www.rakbiz.com/download/working.txt

I also tried char(13) with same results.

any ideas why this is happening or how to add line breaks for readability without this problem?

Thanks for your help!Why is the code all on 1 line?

Ir's generated code isn't it.

Kind of hard to debug it without being formatted...|||You've been living in the *nix world too long! Windoze uses CRLF (aka Char(13) + Char(10) in SQL) instead of just LF (Char(10), aka NewLine) to separate lines.

-PatP|||Thanks Pat for the tip, that was it, I would never have guessed!

Actually I am from *ax backgroup ;)

char(1) vs smallint

I'm having a disagreement with a fellow developer regarding flags. I prefer to declare flag columns as smallint and apply a rule restricting the values to 1 or 0. He prefers to use a char(1) with a rule restricting the values to 'Y' or 'N'. Can anyone give me ammunition against the char(1) or tell me if I'm wrong. Oh yeah, the flag is cast to a boolean in the app written in java, if that makes a difference.Y/N is convenient if users will be querying the database directly, but if you are performing any aggregations then 1/0 allows you to more easily sum the number of flagged records. The percentage flagged, for instance, is just Sum(Flag)/Count(*). (Note that the BIT type won't work with most aggregate functions, otherwise it would be the boolean type of choice.)

You do need to be careful with 1/0 to make sure other applications interpret it correctly. Under some systems TRUE = -1 and FALSE = 0, and other situations are possible.

blindman|||Originally posted by peterlemonjello
I'm having a disagreement with a fellow developer regarding flags. I prefer to declare flag columns as smallint and apply a rule restricting the values to 1 or 0. He prefers to use a char(1) with a rule restricting the values to 'Y' or 'N'. Can anyone give me ammunition against the char(1) or tell me if I'm wrong. Oh yeah, the flag is cast to a boolean in the app written in java, if that makes a difference.
Usually developers know much more than dbas ;). I am using tinyint for flags.|||Thanx blindman and snail! Unfortunately, I'm a developer that new way too much about databases and sql server so I'm a dba now too. As a developer I always said the only thing worse than a dba is a object oriented developer turned dba, guess I'm eating my own words... LOL!!!|||actually, the only thing worse than a dba is a data architect or data modeller like me with years (decades, actually) of modelling and sql language experience, who couldn't solve a performance problem to save his life other than perhaps declaring the obvious indexes...

performance issues aside, you have to look at the implications of your design on the sql to solve business problems

blindman had a superb example -- sum(flag)/count(*)

that's the type of thing a modeller knows, that a dba might not

tinyint (or smallint) is also good because it's a lot more portable across database platforms than boolean

rudy
http://r937.com/|||Those with experience in small shops that required both development and admin duties know best! :D

...but I also think my experience in object-oriented development has helped me develop modular database applications. There is no such thing as bad experience, just people who can't see beyond their own particular project scope.

blindman

char(1) and char(2) take same space?

I create two tables:

create table table1

(

col1 char(1)

)

go

create table table2

(

col2 char(2)

)

go

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

Then i use dbcc page command to oversee the structures of data page in two tables.

I found some interest things:

The rows in two tabes take up same space:9 bytes

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

or calculate from the offset array

Any suggestions?

Did you enter same type of data in both tables?
|||

yes

And you can found that :the two tables can both contain as many as 699 rows per data page.

|||That surprising. Char(2) suppose to use 2 bytes and Char(1) suppose to use 1.
Can you tell me how many records you have in both tables?
|||

the sql script i used:

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table1 values('a')
set @.a=@.a+1
end

-

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table2 values('aa')
set @.a=@.a+1
end

the rows in two tables both take up 9 bytes no matter how many rows you insertd.

|||

I assume you're only adding single character values to each table?

If thats the case, then you more than likely have the value SET ANSI_PADDING OFF when you created your tables. With this setting, any nullable char columns will be treated like varchar columns.

HTH!

|||

sorry, i don't understand what you say...

|||char(1) takes 1 byte, while char(2) takes up two bytes.

However, there is one other thing to consider: The minimum record size that SQL Server uses for data records is 9 bytes. Data records are records that are either records that are stored on leaf-pages on a B-Tree or are records stored in a heap.

The reason that the data record is 9 bytes is that for heaps we need to guarantee that the RID that is used to uniquely identify a row in a heap is never changed. To do this, SQL Server needs to be able to store at least a forwarding pointer on a page. A forwarding pointer is 9 bytes (1 byte header, and 8 byte RID of the actual record.

For B-Trees, the 9 byte limit is used as well. The reason here is that SQL Server quickly likes to convert a B-Tree to a heap without having to touch all the individual records. This is only possible if we can guarantee that the records are at least 9 bytes long.

To really see the difference between char(1) and char(2), create a record that is more than 9 bytes (say add a char(9) to it), and then create one table with the char(1) and another one with char(2). You'll see that the record size will be different.

Thanks,|||

Thats interesting stuff, Marcel.

However, can you clarify one thing. If each record stores a minimum of 9bytes per record, would you not expect the value from dbcc page to show 9 + the length of the data.

eg In this example, 10 (9bytes forwarding pointer and 1byte char(1) or 11 (9bytes forwarding pointer and 2bytes char(2))

Cheers

|||

The forwarding pointer is only stored if it is needed. In case a record is not forwarded, we don't store the forwarding pointer.

In a heap we only forward if a record gets updated, and the updated record does not fit on the page anymore. IN this case, we move the record to a new page, but leave a 9 byte forwarding pointer record on the original page.

DBCC PAGE shows the actual length of a record.

Suppose we have the following table:

CREATE TABLE t1 (col1 char(1)).

When a record is created, the actual size is 2 bytes header, 2 bytes for where null bitmap starts, 1 byte for null bit map, and 1 byte for the actual char(1) data. So the record would be 6 bytes. However, because a record needs to be at least 9 bytes, we make the record size 9 bytes.

Suppose now that we had the following table

CREATE TABLE t1 (col1 char(10)).

In this case the length of a record would be 2 bytes ehader, 2 byes for where nullbitmap starts, 1 byte for null bit map and 10 bytes for actual data. So the record would be 15 bytes.

Thanks,

|||

HI Marcel

Why sql server need forwarding pointer?

Only point the new Page which the original record stored?

We can use IAM to find all the pages of table? why need forwarding pointer?

thanks

|||

Forwarding pointers are used for heaps in case an row that was placed on a certain page gets updated and does not fit on the page anymore.

Let's go through an example.

Suppose there is a row on Page 1:100, and the row is placed in slot 0 on that page. In this case, the row ID (RID) is 1:100:0. This rowid is used to uniquely identify the row going forward.

Suppose not that the heap has a number of non-clustered indexes defined on it. The leaf pages of the non-clustered index contain the RID of the actual row. When the non-clustered index is traversed, and a leaf row is reached, SQL Server uses the RID to find the actual data for that row. So in the above example, SQL reads RID 1:100:0, and knows that it has to go to page 1:100, slot 0.

Let's now assume that the original row gets updated, and that because of update the row grows and does not fit on page 1:100 anymore (keep in mind that there are other rows on page 1:100 as well which take up spaces).

At this point, SQL Server could do two things:

1) It could move the row to a new page, and update all the RIDs in the non-clustered indexes for the original row. This is a very expensive operation, and thus SQL Server does not do this.

2) To keep the RID the same, SQL Server moves the row to a new page (for instance, 2:150:0, i.e. page 2:150, slot 0). On the original location (1:100:0), it keeps a very small, 9 byte forwarding pointer that points to slot 2:150:0.

Now when the data is retrieved, SQL Server reads the forwarding record, and uses the infromation in the forwarding record to find the actual record which is places on 2:150:0.

Because of this, SQL Server needs to guarantee that it can put at least a 9-byte forwarding record on any page that is used by an existing row, meaning that if a row is less than 9-byte, it will reserve 9 bytes anyways.

If the explanation is still unclear, it might help to have a look at the book 'Inside SQL Server 2005: the storage engine' by Kalen Delaney (http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_bbs_sr_1/002-2072619-2748829?ie=UTF8&s=books&qid=1187755949&sr=8-1).

The book has a better description than I can give here (I think there are about 10 pages or so about different record formats, etc).

Thanks,

|||

Marcel

thanks for your reply

char(1) and char(2) take same space?

I create two tables:

create table table1

(

col1 char(1)

)

go

create table table2

(

col2 char(2)

)

go

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

Then i use dbcc page command to oversee the structures of data page in two tables.

I found some interest things:

The rows in two tabes take up same space:9 bytes

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

or calculate from the offset array

Any suggestions?

Did you enter same type of data in both tables?
|||

yes

And you can found that :the two tables can both contain as many as 699 rows per data page.

|||That surprising. Char(2) suppose to use 2 bytes and Char(1) suppose to use 1.
Can you tell me how many records you have in both tables?
|||

the sql script i used:

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table1 values('a')
set @.a=@.a+1
end

-

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table2 values('aa')
set @.a=@.a+1
end

the rows in two tables both take up 9 bytes no matter how many rows you insertd.

|||

I assume you're only adding single character values to each table?

If thats the case, then you more than likely have the value SET ANSI_PADDING OFF when you created your tables. With this setting, any nullable char columns will be treated like varchar columns.

HTH!

|||

sorry, i don't understand what you say...

|||char(1) takes 1 byte, while char(2) takes up two bytes.

However, there is one other thing to consider: The minimum record size that SQL Server uses for data records is 9 bytes. Data records are records that are either records that are stored on leaf-pages on a B-Tree or are records stored in a heap.

The reason that the data record is 9 bytes is that for heaps we need to guarantee that the RID that is used to uniquely identify a row in a heap is never changed. To do this, SQL Server needs to be able to store at least a forwarding pointer on a page. A forwarding pointer is 9 bytes (1 byte header, and 8 byte RID of the actual record.

For B-Trees, the 9 byte limit is used as well. The reason here is that SQL Server quickly likes to convert a B-Tree to a heap without having to touch all the individual records. This is only possible if we can guarantee that the records are at least 9 bytes long.

To really see the difference between char(1) and char(2), create a record that is more than 9 bytes (say add a char(9) to it), and then create one table with the char(1) and another one with char(2). You'll see that the record size will be different.

Thanks,|||

Thats interesting stuff, Marcel.

However, can you clarify one thing. If each record stores a minimum of 9bytes per record, would you not expect the value from dbcc page to show 9 + the length of the data.

eg In this example, 10 (9bytes forwarding pointer and 1byte char(1) or 11 (9bytes forwarding pointer and 2bytes char(2))

Cheers

|||

The forwarding pointer is only stored if it is needed. In case a record is not forwarded, we don't store the forwarding pointer.

In a heap we only forward if a record gets updated, and the updated record does not fit on the page anymore. IN this case, we move the record to a new page, but leave a 9 byte forwarding pointer record on the original page.

DBCC PAGE shows the actual length of a record.

Suppose we have the following table:

CREATE TABLE t1 (col1 char(1)).

When a record is created, the actual size is 2 bytes header, 2 bytes for where null bitmap starts, 1 byte for null bit map, and 1 byte for the actual char(1) data. So the record would be 6 bytes. However, because a record needs to be at least 9 bytes, we make the record size 9 bytes.

Suppose now that we had the following table

CREATE TABLE t1 (col1 char(10)).

In this case the length of a record would be 2 bytes ehader, 2 byes for where nullbitmap starts, 1 byte for null bit map and 10 bytes for actual data. So the record would be 15 bytes.

Thanks,

|||

HI Marcel

Why sql server need forwarding pointer?

Only point the new Page which the original record stored?

We can use IAM to find all the pages of table? why need forwarding pointer?

thanks

|||

Forwarding pointers are used for heaps in case an row that was placed on a certain page gets updated and does not fit on the page anymore.

Let's go through an example.

Suppose there is a row on Page 1:100, and the row is placed in slot 0 on that page. In this case, the row ID (RID) is 1:100:0. This rowid is used to uniquely identify the row going forward.

Suppose not that the heap has a number of non-clustered indexes defined on it. The leaf pages of the non-clustered index contain the RID of the actual row. When the non-clustered index is traversed, and a leaf row is reached, SQL Server uses the RID to find the actual data for that row. So in the above example, SQL reads RID 1:100:0, and knows that it has to go to page 1:100, slot 0.

Let's now assume that the original row gets updated, and that because of update the row grows and does not fit on page 1:100 anymore (keep in mind that there are other rows on page 1:100 as well which take up spaces).

At this point, SQL Server could do two things:

1) It could move the row to a new page, and update all the RIDs in the non-clustered indexes for the original row. This is a very expensive operation, and thus SQL Server does not do this.

2) To keep the RID the same, SQL Server moves the row to a new page (for instance, 2:150:0, i.e. page 2:150, slot 0). On the original location (1:100:0), it keeps a very small, 9 byte forwarding pointer that points to slot 2:150:0.

Now when the data is retrieved, SQL Server reads the forwarding record, and uses the infromation in the forwarding record to find the actual record which is places on 2:150:0.

Because of this, SQL Server needs to guarantee that it can put at least a 9-byte forwarding record on any page that is used by an existing row, meaning that if a row is less than 9-byte, it will reserve 9 bytes anyways.

If the explanation is still unclear, it might help to have a look at the book 'Inside SQL Server 2005: the storage engine' by Kalen Delaney (http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_bbs_sr_1/002-2072619-2748829?ie=UTF8&s=books&qid=1187755949&sr=8-1).

The book has a better description than I can give here (I think there are about 10 pages or so about different record formats, etc).

Thanks,

|||

Marcel

thanks for your reply

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