Binary field usage in SQL Server

Can anyone point me in the right direction to find documentation for the problem below?
I need to store and retrieve ten fields of 16-bits each for testing 16 true-false conditions (a total of 160 bits in each record) so I think I'd like to use ten 2-byte binary fields (160 "bit" fields would be quite unmanageble, if even possible [I think there is some kind of limit to the number of fields in a single record]).
I'm not quickly finding in the SQL Server's online documentation how to test for, use and update binary fields. I'll keep looking, but can anyone point me in the right direction? I'm using VB, if that makes any difference.

The post below is from SQL Server BOL (books online) documentation on BIT, BINARY and VARBINARY data types. I am assuming you know BIT is proprietry because of three valued logic there is no Boolean data type in ANSI SQL. If you need more information post again. Hope this helps.

bit
Integer data type 1, 0, or NULL.

Remarks
Columns of type bit cannot have indexes on them.

Microsoft? SQL Server? optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.

binary and varbinary
Binary data types of either fixed-length (binary) or variable-length (varbinary).

binary [ ( n ) ]

Fixed-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is n+4 bytes.

varbinary [ ( n ) ]

Variable-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length of the data entered + 4 bytes, not n bytes. The data entered can be 0 bytes in length. The SQL-92 synonym for varbinary is binary varying.

Remarks
When n is not specified in a data definition, or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

Use binary when column data entries are consistent in size.

Use varbinary when column data entries are inconsistent in size.

Big table

Hi,

I am writing a small dating site and now I am designing the DB.

At some point I realized that I will need to create a table with more
than 12 columns and I felt that it is not such a good idea.
I've tried normalizing the database, but It was with no effect.
So let's sat that I have the following table:

CREATE TABLE users
(
id int CONSTRAINT pk_user_id PRIMARY KEY,
username varchar(100),
password varchar(100),
isAccVerificated bit,
verificationNumber int,
f_name nvarchar(250),
l_name nvarchar(250),
e-mail varchar(250),
age int,
location l_name nvarchar(250),
visitors int,
isMale bit
picture_data binary
)

"picture_data" contain the real picture in byre array.

So my question is what is preferably to do.
I believe this is not such a good idea to leave this table like this.

Thanks in advance.12 columns is a perfectly reasonable number if you need them. I don't
think most people would call that large. Your defined sizes of those
columns are a different matter. You surely don't need 250 characters
each for first and last name! It's wise to choose sensible maximum
sizes for character columns - if you put the space in there, someone
will use it.

Going by the column names it doesn't look like you have any obvious
transitive dependencies but you do appear to have some other problems:

ID is the only key, which it shouldn't be if it's an artificial key.
How about enforcing the constraint that the username or email address
be unique and non-NULL? Usually that makes sense for sites that require
registration.

All your columns except ID are nullable! Do you really want people to
register anonymously? What's the point of a row with only an ID but no
other information.

isMale? Why not call this Gender or Sex and use the ISO codes for Sex,
which are 1=Male and 2=Female?

Store date of birth rather than age. Otherwise you have the problem of
keeping the age up to date.

Don't store passwords in the database; store secure hashes instead.
Password recovery mechanisms are insecure and bad practice, even where
high levels of security aren't a requirement (people tend to re-use
their passwords so you could end-up exposing the password to someone's
E-Banking account!)

--
David Portas
SQL Server MVP
--|||John,

Along with what David said, I'd avoid storing the pictures in the database.
For one thing, surely you want the members to be able to post multiple
pictures, and this design won't allow for that. Store them in the file
system instead.

Take a look at:
http://www.aspfaq.com/show.asp?id=2149

-Andy

<John.Arthur@.gmail.com> wrote in message
news:1113326413.314223.31090@.o13g2000cwo.googlegro ups.com...
> Hi,
> I am writing a small dating site and now I am designing the DB.
> At some point I realized that I will need to create a table with more
> than 12 columns and I felt that it is not such a good idea.
> I've tried normalizing the database, but It was with no effect.
> So let's sat that I have the following table:
> CREATE TABLE users
> (
> id int CONSTRAINT pk_user_id PRIMARY KEY,
> username varchar(100),
> password varchar(100),
> isAccVerificated bit,
> verificationNumber int,
> f_name nvarchar(250),
> l_name nvarchar(250),
> e-mail varchar(250),
> age int,
> location l_name nvarchar(250),
> visitors int,
> isMale bit
> picture_data binary
> )
> "picture_data" contain the real picture in byre array.
> So my question is what is preferably to do.
> I believe this is not such a good idea to leave this table like this.
> Thanks in advance.|||You can easily normalize the pictures into another table so that
multiple pictures could be
stored under the same account. Personally, I like that approach better
than cluttering the
file system.

-- Dave|||True. To each his own... :)

"Dave" <dafergu2@.hotmail.com> wrote in message
news:1113329911.088937.55740@.l41g2000cwc.googlegro ups.com...
> You can easily normalize the pictures into another table so that
> multiple pictures could be
> stored under the same account. Personally, I like that approach better
> than cluttering the
> file system.
> -- Dave

Best way to stop Transactional Replication

Dear All,
Can anyone point me to a resource for the best way to disable transactional
replication for one database where there are multiple databases on a server
being replicated.
Thanks
Peter
Drop the publications. To do this expand your database, expand the
publications folder and right click on each one and select delete.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Peter Nolan" <PeterNolan@.discussions.microsoft.com> wrote in message
news:E6A1ACD5-D9A0-4091-ABDE-0E677A132485@.microsoft.com...
> Dear All,
> Can anyone point me to a resource for the best way to disable
transactional
> replication for one database where there are multiple databases on a
server
> being replicated.
> Thanks
> Peter
|||Thanks Hilary,
We have done that in the past, however the last time we did it we ran into a
problem. Although the publication had been deleted using the method you said,
when we went to modify the table stucture of the publication database we
recieved the 'Cannot modify schema as its used in replication error'
Finally I had to create a new database, copy the data over, then apply the
changes. We have another release this Thursday, is there anything you can
sugest ?
Thanks for your time
Peter
"Hilary Cotter" wrote:

> Drop the publications. To do this expand your database, expand the
> publications folder and right click on each one and select delete.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Peter Nolan" <PeterNolan@.discussions.microsoft.com> wrote in message
> news:E6A1ACD5-D9A0-4091-ABDE-0E677A132485@.microsoft.com...
> transactional
> server
>
>
|||You could use sp_MSunmarkreplinfo to allow modifications
after dropping the publication.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
I will try that if things become pear shaped.
Peter
"Paul Ibison" wrote:

> You could use sp_MSunmarkreplinfo to allow modifications
> after dropping the publication.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>