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.

Binary Data Type

I am trying to store a byte array in a database. I want to use binary to store the data but I am confused about the type. The byte array I am trying to store is a password hash from SHA512.

I hash a plain text value then store the result in a byte array. I then want to store the byte array in the database as binary but I am confused when its asking for the size of the binary field. In nvarchar a size of 2 would mean 2 characters.

How should I choose the size of this binary field, and what does the size mean. If I choose a size of 6 does that mean 6 characters, like 010110. Or is it stored differently?

The maximum size of a plain text password is 30 characters, and the salt used to generate the SHA512 hash has a maximum size of 16, but I don't know the exact size of the salt because its randomly picked when the salt is generated.

I need to make sure the size of my binary field will hold the largest possible password hash, but I don't want it too large so its never completely used.

How is this data stored in the binary field, and what size binary field should I choose to make sure there are no problems with the password hash being truncated, yet making sure I'm not just wasting by creating a field thats too large.

Thanks!

The size is in bytes.|||

Use the varbinary datatype, since it expands to contain the content. That way you can overestimate the byte length without taking any (extra) space

passwordcolumn varbinary(2000) NOT NULL

/micke

|||

How should I choose the size of this binary field, and what does the size mean. If I choose a size of 6 does that mean 6 characters, like 010110. Or is it stored differently?

Ok, simple thing. What the size means, exactly, is written in the documentation - which you have and which you really should read. At one point. It is the MAXIMUM size of the data that can be stored. MAXIMUM - like, btw., with the nvarcher you mention as example.

The maximum size of a plain text password is 30 characters, and the salt used to generate the SHA512 hash has a maximum size of 16, but I don't know the exact size of the salt because its randomly picked when the salt is generated.

You DO know the maximum size of the salt. The salt should always have the SAME size. Maybe you add spaces to it. Or "0". Or whatever. And then, you basically only need the maximum size anyway.

How is this data stored in the binary field,

Did I mention the documentation? There is a nice section in that explains exactly how SQL Server stores binary data. But frankly, you really do not need to know HOW it is stored. All you need to know for the problem in hand is THAT it is stored. The storage details are an implementation detail of the engine.

and what size binary field should I choose to make sure there are no problems with the password hash being truncated

Choose a size that is lare enough to store the hash. The hash will normally be static in size anyway. So, the size of the vield chould really be clear. That said I would NOT store it as binary. I would store it as char(x) and encode the data in a human readable way (i.e. for example as HEX). Makes debugging a lot easier.

bigint as unsigned value?

The documentation seems to suggest that I can store either a signed or unsigned value in bigint. If I want to store an unsigned value how do I go about it?

nothing. actually.

just be sure your not going out of range of unsigned bigint.

to ensure that this things

you can however put a constraints on a column

that does not accept negative values

|||

I must still be missing something as the following code fails: with a failure to convert an unsigned int to signed int...

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace dbTest
{
class Program
{
static void Main(string[] args)
{
//Create a connection
// Data Source=(local);Database=AdventureWorks;" _
// & "Integrated Security=SSPI;"

string strConnString = @."Data Source=(local);Database=test;Integrated Security=SSPI";
SqlConnection objConn = new SqlConnection(strConnString);
// Create the query

string strSQL = "INSERT INTO dbo.Test (BI) VALUES(@.BI)";
SqlCommand objCmd = new SqlCommand(strSQL, objConn);

// Create parameter
SqlParameter UlongDB;
UInt64 quadValue = 0xFFFFFFFFFFFFFFFF;
UlongDB = new SqlParameter("@.BI", SqlDbType.BigInt);
UlongDB.Value = quadValue;
objCmd.Parameters.Add(UlongDB);

// Insert the record
try
{
objConn.Open();
objCmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Exception is: " + e.Message);
}
finally
{
objConn.Close();
}

}
}

big[er]int Datatype?

Hello,
I have a need to store a numeric value greater than the 2^63 allowed by the
bigint data type...any options?
TIADecimal?
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Steven Frank" <stevef@.relation.com> wrote in message
news:upMgP1rqDHA.1084@.tk2msftngp13.phx.gbl...
> Hello,
> I have a need to store a numeric value greater than the 2^63 allowed
by the
> bigint data type...any options?
> TIA
>|||How much bigger? E.g. did you try DECIMAL or NUMERIC?
If you don't need to perform calculations, you might be safer just storing
as a VARCHAR.
A
"Steven Frank" <stevef@.relation.com> wrote in message
news:upMgP1rqDHA.1084@.tk2msftngp13.phx.gbl...
> Hello,
> I have a need to store a numeric value greater than the 2^63 allowed by
the
> bigint data type...any options?
> TIA
>|||Well don't I feel like a dork... I though bigint was the King!
Thanks a lot!
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O3zlX7rqDHA.1084@.tk2msftngp13.phx.gbl...
> Decimal?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Steven Frank" <stevef@.relation.com> wrote in message
> news:upMgP1rqDHA.1084@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I have a need to store a numeric value greater than the 2^63 allowed
> by the
> > bigint data type...any options?
> >
> > TIA
> >
> >
>|||Well, I've spoken too soon.
The decimal sorks great for storage, but I *do* have to do calculations,
specifically a logical AND (&) operation which does not allow the use of the
decimal data type. Any ideas for this one?
Thanks again!
Steve
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uoRd29rqDHA.2012@.TK2MSFTNGP12.phx.gbl...
> How much bigger? E.g. did you try DECIMAL or NUMERIC?
> If you don't need to perform calculations, you might be safer just storing
> as a VARCHAR.
> A
>
> "Steven Frank" <stevef@.relation.com> wrote in message
> news:upMgP1rqDHA.1084@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I have a need to store a numeric value greater than the 2^63 allowed by
> the
> > bigint data type...any options?
> >
> > TIA
> >
> >
>|||> The decimal sorks great for storage, but I *do* have to do calculations,
> specifically a logical AND (&) operation which does not allow the use of
the
> decimal data type. Any ideas for this one?
Use CAST/CONVERT when performing the calculations?|||I've tried that and it blows up because you can cast it to a bigint because
its too big...
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23LoVn0TrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > The decimal sorks great for storage, but I *do* have to do calculations,
> > specifically a logical AND (&) operation which does not allow the use of
> the
> > decimal data type. Any ideas for this one?
> Use CAST/CONVERT when performing the calculations?
>

best way to store simple up or down values

I'm creating a table for maintenance records.

In each record, many of the values are simply checkboxes (on the UI).

In the database table for these attributes, is a good way to store the
state of these checkboxes as simple as 0 for false, 1 for true?

-David"wireless" <wireless200@.yahoo.com> wrote in message
news:90446ee7.0402110709.30e1e084@.posting.google.c om...
> I'm creating a table for maintenance records.
> In each record, many of the values are simply checkboxes (on the UI).
> In the database table for these attributes, is a good way to store the
> state of these checkboxes as simple as 0 for false, 1 for true?
> -David

There are two common ways to do this - either use a bit column, or use
something like a char(1) with a check constraint to ensure the values are
T/F or Y/N. The second solution is more portable, if that's a concern.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> There are two common ways to do this - either use a bit column, or use
> something like a char(1) with a check constraint to ensure the values are
> T/F or Y/N. The second solution is more portable, if that's a concern.

On the other hand it is more sensitive to localization issues. We used
to have such columns in our databases, but I think all are gone now. The
values we used where J/N.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns948CEE4DEAA00Yazorman@.127.0.0.1>...
> Simon Hayes (sql@.hayes.ch) writes:
> > There are two common ways to do this - either use a bit column, or use
> > something like a char(1) with a check constraint to ensure the values are
> > T/F or Y/N. The second solution is more portable, if that's a concern.
> On the other hand it is more sensitive to localization issues. We used
> to have such columns in our databases, but I think all are gone now. The
> values we used where J/N.

That's a good point, although I would guess (very possibly
incorrectly) that many IT people would be familiar with 'True' and
'False' as Boolean values in various programming languages, even if
their own natural language isn't English. Personally, I think a bit is
the most obvious data type for flags, but then that seems to invite a
lecture from Joe Celko...

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<402a789f$1_3@.news.bluewin.ch>...

> There are two common ways to do this - either use a bit column, or use
> something like a char(1) with a check constraint to ensure the values are
> T/F or Y/N. The second solution is more portable, if that's a concern.

Thanks, I went with the bit method. I've been working on projects
involving Oracle and other databases for the past few months and hope
not to port anything to any of them! I'm content to be back to a SQL
Server db.

-David|||Simon Hayes (sql@.hayes.ch) writes:
> That's a good point, although I would guess (very possibly
> incorrectly) that many IT people would be familiar with 'True' and
> 'False' as Boolean values in various programming languages, even if
> their own natural language isn't English. Personally, I think a bit is
> the most obvious data type for flags, but then that seems to invite a
> lecture from Joe Celko...

Already when you give the choice of Y/N and T/F you have give a choice
that can be source for confusion. One of the DBA goes for the former,
another for the latter, guess if developers will mess up.

As for localisation, recall that some of this data may make to a GUI.
That was the case with our J/N, which our Swedish users had no problem to
understand. They might be able to make out Y/N too, but I would guess that
T/F, or even S/F, would leave them completely in the dark.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I'm creating a table for maintenance records. In each record, many
of the values are simply checkboxes (on the UI). <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

When you convert the *record* on the paper forms into one or more
*rows* in one or more tables in the database, what does the data look
like?

What I have seen is for maintenance databases is that "yes/no" is not
good enough. You need to know temporal information for each task,
like "scheduled time" and "completed time" as minimal data for
computing MTBF and other things. Would you write an accountign system
in which you had flag for "paid/not paid" and leave out the amounts
and dates?

Simple yes/no flags are all too often computed columns that can be
deduced from the other attributes in the database.

Best way to store information

Hi,

I am using MSDE in my applications. Sometime the tables are large and it takes a time for reading the data by query.

1. If I split the tables it can help me? or not?
2. May be exist another way to read the data?

My application wrote in C# (dot.Net 1.1) using ADO (SQLClient).

Thank's
Alexei

there are a lot of things u can do to reduce the time taken...eg..tune ur queries, create indexes on tables..etc... but comming to ur specific questions
1. spliting the tables...
as ur using sql server 2000 msde , horizontal partition is not too well supported..and it helps most if u keep the partitions on different drives , having seperate headers.....this article gives some info
http://www.sqlteam.com/Item.ASP?ItemID=684

2. other ways...as i said earlier there r a lot of things u can do...identify the bottelnecks and then work towards improving them...|||

Alexei:

Can you present the queries that are giving you problems? Please include some short example data and a brief listing of the composition of the target table in question. It is easier if we know the target problem.

Dave

|||

You are seriously going to have to give way more information for anyone to answer this question.

You state you are using MSDE, is this as a server? How many users? If you have > 8 simultaneous queries it will start to punish you and seriously degrate performance.

And what do you mean large? Can you post table creations, some sample data, and most importantly, how many rows you have? It might be indexing, it could be poorly written queries, it could be a server/ram issue, but without the table create scripts, it would all be an uneducated guess...