Determine next order id

I'm working on a sproc that determines the next order id for a specified customer. The table has

custid int,

ordernum varchar(10)

Data is:

1000, 1000-001

1000, 1000-002

1001, 1001-001

1000, 1000-003

I need to know the next ordernum for the specified custid. For example, GetNextOrderNum(1000) should return 1000-004. GetNextOrderNum(1002) should return 1002-001 (since there aren't any orders yet).

I honestly don't know where to begin.

Can someone please help?

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(OderNumber,3) + 1 AS VARCHAR(6)),3)
FROm Orders
Order by OrderNumer
Where CustId = @.CustId

SELECT @.LASTOrdernNum = ISNULL(@.LASTOrdernNum,'001')

SELECT CAST(CustId as VARCHAR(4)) + '-' + LASTOrdernNum

Should b something like the above (untested).

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

That was very close. Thanks very much.

Is there a way to return a string value? I won't be calling this SP with ExecuteScalar, so I'd like to RETURN the value instead of just selecting it.

Here's the working version:

Code Snippet

ALTER PROCEDURE GetNextOrderID

(

@.custid INT,

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @.LastOrderNum VARCHAR(10)

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(Ordernum,3) + 1 AS VARCHAR(6)),3)

FROM MFOrder

Where mforder.custid = @.custid

Order by Ordernum

SELECT @.LastOrderNum = ISNULL(@.LastOrderNum,'001')

SELECT CAST(@.custid as VARCHAR(5)) + '-' + @.LastOrdernum

END

I can use RETURN on the last line instead of select, but my test code errors with:

Conversion failed when converting the varchar value '10052-002' to data type int.

|||

RETURN allows for only an integer operand, which is generally used as a status or return code.

You may want to consider creating a function rather than a procedure.

|||

ALTER PROCEDURE GetNextOrderID

(

@.custid INT,

@.retval varchar(255) output

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @.LastOrderNum VARCHAR(10)

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(Ordernum,3) + 1 AS VARCHAR(6)),3)

FROM MFOrder

Where mforder.custid = @.custid

Order by Ordernum

SELECT @.LastOrderNum = ISNULL(@.LastOrderNum,'001')

SELECT @.retval = CAST(@.custid as VARCHAR(5)) + '-' + @.LastOrdernum

END

sql

determine if value in field is number

I have a varchar field in a table that could contain values such as 123,
345, ABC, or CDF
I want to do a select statement on the table to return records where this
field could be number (123, 345) and exclude those that are not (ABC, CDF).
Is there an IsNum function or something like that?
hi matt,
make use of isnumeric function, which returns 1 for valid numeric value.
ex:
select *
from
<table>
where isnumeric(col) = 1
Vishal Parkar
vgparkar@.yahoo.co.in

Determine if a column is indexed (and the index name)

Is there a slick way to determine if all the indexes (if any) for a particular field?

let's say:

tbPeople

ID (bigint)

FirstName (varchar(50))

LastName (varchar(50))

I need to determine if [FirstName] in indexed anywhere, and if so, what are the name(s) of the indexes.

thanks ahead of time.

Which version of SS are you using?

To select all indexes where the column is part of the key, in 2005, you can use:

select

object_name(si.[object_id])as table_name,

si.name as index_name,

index_col(object_name(sic.[object_id]), sic.index_id, sic.index_column_id)as column_name,

sic.key_ordinal,

sic.is_descending_key,

sic.is_included_column

from

sys.indexesas si

innerjoin

sys.index_columnsas sic

on si.[object_id] = sic.[object_id]

and si.index_id = sic.index_id

where

si.object_id=object_id('dbo.Employees')

andcolumnproperty(object_id('dbo.Employees'),'LastName','ColumnId')= sic.column_id

orderby

sic.[object_id],

sic.index_id

go

AMB

|||

Brilliant. Thanks. Was not aware of sys.index_columns.

Any cute tricks for the equivalent query on SS 2K?

|||

Try:

select

object_name(si.[id])as table_name,

si.name as index_name,

col_name(sic.[id], sic.colid)as column_name,

sic.keyno

from

sysindexesas si

innerjoin

sysindexkeysas sic

on si.[id] = sic.[id]

and si.indid = sic.indid

where

si.[id] =object_id('dbo.Employees')

andcol_name(sic.[id], sic.colid)='LastName'

orderby

sic.[id],

sic.indid

go

AMB

Detecting Carriage Returns in a column

Can anyone provide me with some SQL that will identify rows from a table where a varchar column named "Notes" contain Carriage Returns?

I know that with report writer SQR I can translate CR's to white space but I do not know of any Sybase function that will allow me to do the same, any ideas on this well would be appreciated.For Microsoft SQL Server, I'd use CharIndex. For Sybase, it would depend on which of the Sybase servers you are using. Different Sybase engines have different string handling syntaxes.

-PatP|||Thanks Pat. One other dumb question is how is a Carriage Return represented in SQL? char[13]?? THanks again for your help, I used to know this but I'm a little rusty.|||That depends on what you mean by a carriage return. ;)

The problem is complex, because different operating systems store the "line end" marker differently, and because different SQL implementations have different ways of dealing with character expressions.

In Microsoft SQL Server, you represent a lone carriage return character asChar(13)...but, based on your previous question I think you really want to find a "line end" instead of a carriage return character. If you want to find a "line end" as used in Microsoft SQL Server, then you want:Char(13) + Char(10)

-PatP

Detail needed about varchar

I'm trying to understand past the basics of varchar...
1) Does a varchar(1000) take more storage in the actual database file than a
varchar(500)? (assume each contains a 300 byte string). If not, why wouldn't
I always use varchar(8000)?
2) Does a varchar(10) take more space than a char(10)? (is there overhead
specifying the actual length in varchar?)
3) Are there any performance penalties (and specifically, what are they)
when using varchar instead of char?1. No, no more space. Lazy? Use only the space you need and use the
appropriate column data types and sizes.
2. Yes, every varchar in a table uses an additional 2 bytes to describe the
actual length of the data stored.
3. If you have 10 rows per page with a row size of 500 bytes. And you update
a row where the varchar that used to contain 10 bytes is now 500 bytes, the
whole page has to be re-arranged for this data to fit in. This may even
cause the page to be split and incur a lot of extra IO. SQL always has to
guess how much of the variable space will be used up when it does page
allocations. When a able is created, all the fixed length data types are
stored on the left and all the variable length data types are stored after
that.
Do yourself a favor and create a table with 1'000'000 rows in it, and then
create an index on a varchar column and check the database growth.
Do the same with a properly sized char column and compare the space
requirements of the table. Create clustered and non-clustered indexes.
If you ever want to know how well a system is written, it is easy, look at
the DB. If it is clean and very well though out with appropriate data types,
you generally know the developer knew hat he was doing and took pride in his
work.
Get yourself Kalen Delaney's book "Inside SQL 2000" (MS Press). Once you
read that, you will change your mind how you do DB design.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Lig Reffej" <Lig Reffej@.discussions.microsoft.com> wrote in message
news:1C986552-89FF-4C3B-912D-9C58C3F6466F@.microsoft.com...
> I'm trying to understand past the basics of varchar...
> 1) Does a varchar(1000) take more storage in the actual database file than
a
> varchar(500)? (assume each contains a 300 byte string). If not, why
wouldn't
> I always use varchar(8000)?
> 2) Does a varchar(10) take more space than a char(10)? (is there overhead
> specifying the actual length in varchar?)
> 3) Are there any performance penalties (and specifically, what are they)
> when using varchar instead of char?
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl...
> "Lig Reffej" <Lig Reffej@.discussions.microsoft.com> wrote in message
> news:1C986552-89FF-4C3B-912D-9C58C3F6466F@.microsoft.com...
> > I'm trying to understand past the basics of varchar...
> >
> > 1) Does a varchar(1000) take more storage in the actual database file
than
> a
> > varchar(500)? (assume each contains a 300 byte string). If not, why
> wouldn't
> > I always use varchar(8000)?
> 1. No, no more space. Lazy? Use only the space you need and use the
> appropriate column data types and sizes.
The question is whether varchar(8000) -is- actually the most appropriate. :)
Specifying the length of a character column is in many cases a matter of
predicting the future. E.g. what is the maximum last name that will needed
to be held by this system?
I think Lig's point might be, why try to predict it?|||Mark Wilden wrote:
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:%23TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl...
>> "Lig Reffej" <Lig Reffej@.discussions.microsoft.com> wrote in message
>> news:1C986552-89FF-4C3B-912D-9C58C3F6466F@.microsoft.com...
>> I'm trying to understand past the basics of varchar...
>> 1) Does a varchar(1000) take more storage in the actual database
>> file than a varchar(500)? (assume each contains a 300 byte string).
>> If not, why wouldn't I always use varchar(8000)?
>> 1. No, no more space. Lazy? Use only the space you need and use the
>> appropriate column data types and sizes.
> The question is whether varchar(8000) -is- actually the most
> appropriate. :)
> Specifying the length of a character column is in many cases a matter
> of predicting the future. E.g. what is the maximum last name that
> will needed to be held by this system?
> I think Lig's point might be, why try to predict it?
If only because row density is important. If only because you can only
fit 8060 bytes in a row and using a single varchar(8000) leaves 60 bytes
for other data should the column be filled.
If you can't predict the length of a character column with any accuracy,
use text/ntext.
David Gugick
Imceda Software
www.imceda.com|||"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:#TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl:
> 1. No, no more space. Lazy? Use only the space you need and use the
> appropriate column data types and sizes.
> 2. Yes, every varchar in a table uses an additional 2 bytes to
> describe the actual length of the data stored.
Is the additional 2 bytes per row, page, table or what?
> 3. If you have 10 rows per page with a row size of 500 bytes. And you
> update a row where the varchar that used to contain 10 bytes is now
> 500 bytes, the whole page has to be re-arranged for this data to fit
> in. This may even cause the page to be split and incur a lot of extra
> IO. SQL always has to guess how much of the variable space will be
> used up when it does page allocations. When a able is created, all the
> fixed length data types are stored on the left and all the variable
> length data types are stored after that.
> Do yourself a favor and create a table with 1'000'000 rows in it, and
> then create an index on a varchar column and check the database
> growth. Do the same with a properly sized char column and compare the
> space requirements of the table. Create clustered and non-clustered
> indexes.
> If you ever want to know how well a system is written, it is easy,
> look at the DB. If it is clean and very well though out with
> appropriate data types, you generally know the developer knew hat he
> was doing and took pride in his work.
> Get yourself Kalen Delaney's book "Inside SQL 2000" (MS Press). Once
> you read that, you will change your mind how you do DB design.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland|||JTC ^..^ wrote:
> "Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
> news:#TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl:
>> 1. No, no more space. Lazy? Use only the space you need and use the
>> appropriate column data types and sizes.
>> 2. Yes, every varchar in a table uses an additional 2 bytes to
>> describe the actual length of the data stored.
>
> Is the additional 2 bytes per row, page, table or what?
>
Additional 2 for each varchar
--
David Gugick
Imceda Software
www.imceda.com|||Just to be crystal clear on this, a varchar(10) with 10 characters stored
will take 2 more bytes than a char(10). Because the varchar is variable
length, we need to store its length in the record, hence the overhead is per
column.
If the varchar(10) only stores 1 char, then its total size will be 3 bytes,
less than the 10 bytes consumed by the fixed size char(10) - as fixed size
chars are padded up to their declared size always.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OSajStMyEHA.2260@.TK2MSFTNGP15.phx.gbl...
> JTC ^..^ wrote:
> > "Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
> > news:#TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl:
> >
> >> 1. No, no more space. Lazy? Use only the space you need and use the
> >> appropriate column data types and sizes.
> >>
> >> 2. Yes, every varchar in a table uses an additional 2 bytes to
> >> describe the actual length of the data stored.
> >
> >
> >
> > Is the additional 2 bytes per row, page, table or what?
> >
> >
>
> Additional 2 for each varchar
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Paul S Randal [MS] wrote:
> Just to be crystal clear on this, a varchar(10) with 10 characters
> stored will take 2 more bytes than a char(10). Because the varchar is
> variable length, we need to store its length in the record, hence the
> overhead is per column.
> If the varchar(10) only stores 1 char, then its total size will be 3
> bytes, less than the 10 bytes consumed by the fixed size char(10) -
> as fixed size chars are padded up to their declared size always.
> Regards.
I may have mispoken. Since I don't have time right now to dig into the
technical books, I'll leave to another user to post the correct results.
--
David Gugick
Imceda Software
www.imceda.com

Detail needed about varchar

I'm trying to understand past the basics of varchar...
1) Does a varchar(1000) take more storage in the actual database file than a
varchar(500)? (assume each contains a 300 byte string). If not, why wouldn't
I always use varchar(8000)?
2) Does a varchar(10) take more space than a char(10)? (is there overhead
specifying the actual length in varchar?)
3) Are there any performance penalties (and specifically, what are they)
when using varchar instead of char?
1. No, no more space. Lazy? Use only the space you need and use the
appropriate column data types and sizes.
2. Yes, every varchar in a table uses an additional 2 bytes to describe the
actual length of the data stored.
3. If you have 10 rows per page with a row size of 500 bytes. And you update
a row where the varchar that used to contain 10 bytes is now 500 bytes, the
whole page has to be re-arranged for this data to fit in. This may even
cause the page to be split and incur a lot of extra IO. SQL always has to
guess how much of the variable space will be used up when it does page
allocations. When a able is created, all the fixed length data types are
stored on the left and all the variable length data types are stored after
that.
Do yourself a favor and create a table with 1'000'000 rows in it, and then
create an index on a varchar column and check the database growth.
Do the same with a properly sized char column and compare the space
requirements of the table. Create clustered and non-clustered indexes.
If you ever want to know how well a system is written, it is easy, look at
the DB. If it is clean and very well though out with appropriate data types,
you generally know the developer knew hat he was doing and took pride in his
work.
Get yourself Kalen Delaney's book "Inside SQL 2000" (MS Press). Once you
read that, you will change your mind how you do DB design.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Lig Reffej" <Lig Reffej@.discussions.microsoft.com> wrote in message
news:1C986552-89FF-4C3B-912D-9C58C3F6466F@.microsoft.com...
> I'm trying to understand past the basics of varchar...
> 1) Does a varchar(1000) take more storage in the actual database file than
a
> varchar(500)? (assume each contains a 300 byte string). If not, why
wouldn't
> I always use varchar(8000)?
> 2) Does a varchar(10) take more space than a char(10)? (is there overhead
> specifying the actual length in varchar?)
> 3) Are there any performance penalties (and specifically, what are they)
> when using varchar instead of char?
>
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl...
[vbcol=seagreen]
> "Lig Reffej" <Lig Reffej@.discussions.microsoft.com> wrote in message
> news:1C986552-89FF-4C3B-912D-9C58C3F6466F@.microsoft.com...
than[vbcol=seagreen]
> a
> wouldn't

> 1. No, no more space. Lazy? Use only the space you need and use the
> appropriate column data types and sizes.
The question is whether varchar(8000) -is- actually the most appropriate.
Specifying the length of a character column is in many cases a matter of
predicting the future. E.g. what is the maximum last name that will needed
to be held by this system?
I think Lig's point might be, why try to predict it?
|||Mark Wilden wrote:
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:%23TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl...
>
> The question is whether varchar(8000) -is- actually the most
> appropriate.
> Specifying the length of a character column is in many cases a matter
> of predicting the future. E.g. what is the maximum last name that
> will needed to be held by this system?
> I think Lig's point might be, why try to predict it?
If only because row density is important. If only because you can only
fit 8060 bytes in a row and using a single varchar(8000) leaves 60 bytes
for other data should the column be filled.
If you can't predict the length of a character column with any accuracy,
use text/ntext.
David Gugick
Imceda Software
www.imceda.com
|||"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:#TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl:

> 1. No, no more space. Lazy? Use only the space you need and use the
> appropriate column data types and sizes.
> 2. Yes, every varchar in a table uses an additional 2 bytes to
> describe the actual length of the data stored.
Is the additional 2 bytes per row, page, table or what?

> 3. If you have 10 rows per page with a row size of 500 bytes. And you
> update a row where the varchar that used to contain 10 bytes is now
> 500 bytes, the whole page has to be re-arranged for this data to fit
> in. This may even cause the page to be split and incur a lot of extra
> IO. SQL always has to guess how much of the variable space will be
> used up when it does page allocations. When a able is created, all the
> fixed length data types are stored on the left and all the variable
> length data types are stored after that.
> Do yourself a favor and create a table with 1'000'000 rows in it, and
> then create an index on a varchar column and check the database
> growth. Do the same with a properly sized char column and compare the
> space requirements of the table. Create clustered and non-clustered
> indexes.
> If you ever want to know how well a system is written, it is easy,
> look at the DB. If it is clean and very well though out with
> appropriate data types, you generally know the developer knew hat he
> was doing and took pride in his work.
> Get yourself Kalen Delaney's book "Inside SQL 2000" (MS Press). Once
> you read that, you will change your mind how you do DB design.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
|||JTC ^..^ wrote:
> "Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
> news:#TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl:
>
>
> Is the additional 2 bytes per row, page, table or what?
>
Additional 2 for each varchar
David Gugick
Imceda Software
www.imceda.com
|||Just to be crystal clear on this, a varchar(10) with 10 characters stored
will take 2 more bytes than a char(10). Because the varchar is variable
length, we need to store its length in the record, hence the overhead is per
column.
If the varchar(10) only stores 1 char, then its total size will be 3 bytes,
less than the 10 bytes consumed by the fixed size char(10) - as fixed size
chars are padded up to their declared size always.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OSajStMyEHA.2260@.TK2MSFTNGP15.phx.gbl...
> JTC ^..^ wrote:
>
> Additional 2 for each varchar
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Paul S Randal [MS] wrote:
> Just to be crystal clear on this, a varchar(10) with 10 characters
> stored will take 2 more bytes than a char(10). Because the varchar is
> variable length, we need to store its length in the record, hence the
> overhead is per column.
> If the varchar(10) only stores 1 char, then its total size will be 3
> bytes, less than the 10 bytes consumed by the fixed size char(10) -
> as fixed size chars are padded up to their declared size always.
> Regards.
I may have mispoken. Since I don't have time right now to dig into the
technical books, I'll leave to another user to post the correct results.
David Gugick
Imceda Software
www.imceda.com

Detail needed about varchar

I'm trying to understand past the basics of varchar...
1) Does a varchar(1000) take more storage in the actual database file than a
varchar(500)? (assume each contains a 300 byte string). If not, why wouldn'
t
I always use varchar(8000)?
2) Does a varchar(10) take more space than a char(10)? (is there overhead
specifying the actual length in varchar?)
3) Are there any performance penalties (and specifically, what are they)
when using varchar instead of char?1. No, no more space. Lazy? Use only the space you need and use the
appropriate column data types and sizes.
2. Yes, every varchar in a table uses an additional 2 bytes to describe the
actual length of the data stored.
3. If you have 10 rows per page with a row size of 500 bytes. And you update
a row where the varchar that used to contain 10 bytes is now 500 bytes, the
whole page has to be re-arranged for this data to fit in. This may even
cause the page to be split and incur a lot of extra IO. SQL always has to
guess how much of the variable space will be used up when it does page
allocations. When a able is created, all the fixed length data types are
stored on the left and all the variable length data types are stored after
that.
Do yourself a favor and create a table with 1'000'000 rows in it, and then
create an index on a varchar column and check the database growth.
Do the same with a properly sized char column and compare the space
requirements of the table. Create clustered and non-clustered indexes.
If you ever want to know how well a system is written, it is easy, look at
the DB. If it is clean and very well though out with appropriate data types,
you generally know the developer knew hat he was doing and took pride in his
work.
Get yourself Kalen Delaney's book "Inside SQL 2000" (MS Press). Once you
read that, you will change your mind how you do DB design.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Lig Reffej" <Lig Reffej@.discussions.microsoft.com> wrote in message
news:1C986552-89FF-4C3B-912D-9C58C3F6466F@.microsoft.com...
> I'm trying to understand past the basics of varchar...
> 1) Does a varchar(1000) take more storage in the actual database file than
a
> varchar(500)? (assume each contains a 300 byte string). If not, why
wouldn't
> I always use varchar(8000)?
> 2) Does a varchar(10) take more space than a char(10)? (is there overhead
> specifying the actual length in varchar?)
> 3) Are there any performance penalties (and specifically, what are they)
> when using varchar instead of char?
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl...

> "Lig Reffej" <Lig Reffej@.discussions.microsoft.com> wrote in message
> news:1C986552-89FF-4C3B-912D-9C58C3F6466F@.microsoft.com...
than[vbcol=seagreen]
> a
> wouldn't
[vbcol=seagreen]
> 1. No, no more space. Lazy? Use only the space you need and use the
> appropriate column data types and sizes.
The question is whether varchar(8000) -is- actually the most appropriate.
Specifying the length of a character column is in many cases a matter of
predicting the future. E.g. what is the maximum last name that will needed
to be held by this system?
I think Lig's point might be, why try to predict it?|||Mark Wilden wrote:
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:%23TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl...
>
>
> The question is whether varchar(8000) -is- actually the most
> appropriate.
> Specifying the length of a character column is in many cases a matter
> of predicting the future. E.g. what is the maximum last name that
> will needed to be held by this system?
> I think Lig's point might be, why try to predict it?
If only because row density is important. If only because you can only
fit 8060 bytes in a row and using a single varchar(8000) leaves 60 bytes
for other data should the column be filled.
If you can't predict the length of a character column with any accuracy,
use text/ntext.
David Gugick
Imceda Software
www.imceda.com|||"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:#TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl:

> 1. No, no more space. Lazy? Use only the space you need and use the
> appropriate column data types and sizes.
> 2. Yes, every varchar in a table uses an additional 2 bytes to
> describe the actual length of the data stored.
Is the additional 2 bytes per row, page, table or what?

> 3. If you have 10 rows per page with a row size of 500 bytes. And you
> update a row where the varchar that used to contain 10 bytes is now
> 500 bytes, the whole page has to be re-arranged for this data to fit
> in. This may even cause the page to be split and incur a lot of extra
> IO. SQL always has to guess how much of the variable space will be
> used up when it does page allocations. When a able is created, all the
> fixed length data types are stored on the left and all the variable
> length data types are stored after that.
> Do yourself a favor and create a table with 1'000'000 rows in it, and
> then create an index on a varchar column and check the database
> growth. Do the same with a properly sized char column and compare the
> space requirements of the table. Create clustered and non-clustered
> indexes.
> If you ever want to know how well a system is written, it is easy,
> look at the DB. If it is clean and very well though out with
> appropriate data types, you generally know the developer knew hat he
> was doing and took pride in his work.
> Get yourself Kalen Delaney's book "Inside SQL 2000" (MS Press). Once
> you read that, you will change your mind how you do DB design.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland|||JTC ^..^ wrote:
> "Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
> news:#TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl:
>
>
> Is the additional 2 bytes per row, page, table or what?
>
Additional 2 for each varchar
David Gugick
Imceda Software
www.imceda.com|||Just to be crystal clear on this, a varchar(10) with 10 characters stored
will take 2 more bytes than a char(10). Because the varchar is variable
length, we need to store its length in the record, hence the overhead is per
column.
If the varchar(10) only stores 1 char, then its total size will be 3 bytes,
less than the 10 bytes consumed by the fixed size char(10) - as fixed size
chars are padded up to their declared size always.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OSajStMyEHA.2260@.TK2MSFTNGP15.phx.gbl...
> JTC ^..^ wrote:
>
> Additional 2 for each varchar
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Paul S Randal [MS] wrote:
> Just to be crystal clear on this, a varchar(10) with 10 characters
> stored will take 2 more bytes than a char(10). Because the varchar is
> variable length, we need to store its length in the record, hence the
> overhead is per column.
> If the varchar(10) only stores 1 char, then its total size will be 3
> bytes, less than the 10 bytes consumed by the fixed size char(10) -
> as fixed size chars are padded up to their declared size always.
> Regards.
I may have mispoken. Since I don't have time right now to dig into the
technical books, I'll leave to another user to post the correct results.
David Gugick
Imceda Software
www.imceda.com

design question - so many columns....

Got a situation where our main, core data in our main tables consist
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?
On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.
|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.
|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -
|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.
|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
> 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
>
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.

design question - so many columns....

Got a situation where our main, core data in our main tables consist
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > Got a situation where our main, core data in our main tables consist
> > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > several "sets" of booleans. Example - health risk factors....high
> > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > say. The user can choose none, all, or any combo in between in these
> > sets of booleans. Well each of those fundamentally is just a bit
> > column, with a zero or one, attached to the record concerning the
> > individual person/event record. Well what if I have 8 or 9 "sets" of
> > these boolean questions? This results in 150-200+ columns in my
> > table. I know sql can handle up to 1024, and these data really belong
> > on this record with this individual person/event.
> > I just wanted to see what others thought from the design perspective.
> > Any suggestions?
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
> > On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > > Got a situation where our main, core data in our main tables consist
> > > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > > several "sets" of booleans. Example - health risk factors....high
> > > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > > say. The user can choose none, all, or any combo in between in these
> > > sets of booleans. Well each of those fundamentally is just a bit
> > > column, with a zero or one, attached to the record concerning the
> > > individual person/event record. Well what if I have 8 or 9 "sets" of
> > > these boolean questions? This results in 150-200+ columns in my
> > > table. I know sql can handle up to 1024, and these data really belong
> > > on this record with this individual person/event.
> > > I just wanted to see what others thought from the design perspective.
> > > Any suggestions?
> > That's quite easy. Let's say you have one column called 'Risk Factor'
> > that can take 20 possible values. You can store values like
> > 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> > would prefer this. Another solution is you can have a varchar(20) with
> > values like 10001000 etc where you set a bit for particular risk
> > factor. You have to know the ordinal position for a particular risk
> > and if that bit is set or not.
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>> Got a situation where our main, core data in our main tables consist
>> of 15-20 normal columns (dates, integers, varchar, etc.) and then
>> several "sets" of booleans. Example - health risk factors....high
>> blood pressure, diabetes, depression....up to 20 risk factors, let's
>> say. The user can choose none, all, or any combo in between in these
>> sets of booleans. Well each of those fundamentally is just a bit
>> column, with a zero or one, attached to the record concerning the
>> individual person/event record. Well what if I have 8 or 9 "sets" of
>> these boolean questions? This results in 150-200+ columns in my
>> table. I know sql can handle up to 1024, and these data really belong
>> on this record with this individual person/event.
>> I just wanted to see what others thought from the design perspective.
>> Any suggestions?
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers => 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
> > On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> > > On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > > > Got a situation where our main, core data in our main tables consist
> > > > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > > > several "sets" of booleans. Example - health risk factors....high
> > > > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > > > say. The user can choose none, all, or any combo in between in these
> > > > sets of booleans. Well each of those fundamentally is just a bit
> > > > column, with a zero or one, attached to the record concerning the
> > > > individual person/event record. Well what if I have 8 or 9 "sets" of
> > > > these boolean questions? This results in 150-200+ columns in my
> > > > table. I know sql can handle up to 1024, and these data really belong
> > > > on this record with this individual person/event.
> > > > I just wanted to see what others thought from the design perspective.
> > > > Any suggestions?
> > > That's quite easy. Let's say you have one column called 'Risk Factor'
> > > that can take 20 possible values. You can store values like
> > > 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> > > would prefer this. Another solution is you can have a varchar(20) with
> > > values like 10001000 etc where you set a bit for particular risk
> > > factor. You have to know the ordinal position for a particular risk
> > > and if that bit is set or not.
> > and of course if you want to be relational then you have a row for
> > each 'Risk Factor'. So instead of growing the table horizontally you
> > grow them vertically. So if a person has 'Diabetes' and 'High Blood
> > Pressure' there are 2 rows for that person. Then you can group by a
> > particular 'Risk Factor'. For example, find all patients where 'Risk
> > Factor' is 'Diabetes' and sum how much they spent etc on medication
> > etc.- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.

design question - so many columns....

Got a situation where our main, core data in our main tables consist
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>
>
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
>
>
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
> 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
>
>
>
>
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.