Binary Primary Key

I have a 256 bit hash value to identify chunks of data. Would it be a good idea to create a 32-byte binary field as the primary key or encode the bytes to a string?

Well, to me if your natural key really is binary then that is the way I would designate the primary key -- as binary. I'd still like to hear other opinions, though

bigint or uniqueidentifer performance metrics?

If the use of the uniqueidentifier is now preferred, across how many
relations is it optimal to use as the type for primary keys? Is there some
point other than performance why one should not worry about reverting to the
use of bigint for related tables? I've only read a couple of stories about
it so far. What do you think?
<%= Clinton GallagherClinton
I would try to avoid using GUID as a Primary Key.
GUIDs take up 16-bytes of storage, more than an Identify column, which in
turn make the index larger, which increases I/O reads, which can hurt
performance. While the performance hit will be minimal if you do decide to
add a clustered index to a GUID column, every little bit adds up
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
> If the use of the uniqueidentifier is now preferred, across how many
> relations is it optimal to use as the type for primary keys? Is there some
> point other than performance why one should not worry about reverting to
> the use of bigint for related tables? I've only read a couple of stories
> about it so far. What do you think?
> <%= Clinton Gallagher
>|||That's what I read Uri but then on the next article GUIDs are praised not to
mention their use in the aspnet_Users table as well as other tables in the
ASP.NET 2.0 Membership, Roles and Profiles implementation Microsoft has
provided.
I think I'll just use their GUID as a foreign key and revert to bigint for
primary keys. It seems there are benefits for grabbing the last inserted
record using @.@.IDENTITY which GUID does not support.
I'm also trying to figure out how to use GUID as a primary key -- anyway --
and auto-generate the value using the newid( ) default binding. I've tried
learning to use newid( ) by entering data into the table but all I get to
see is red circles with ! and error messages or the red circles and no error
messages.
Thanks for your attention...
<%= Clinton Gallagher
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqYqhBqOGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Clinton
> I would try to avoid using GUID as a Primary Key.
> GUIDs take up 16-bytes of storage, more than an Identify column, which in
> turn make the index larger, which increases I/O reads, which can hurt
> performance. While the performance hit will be minimal if you do decide to
> add a clustered index to a GUID column, every little bit adds up
>
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
>|||This is kind of a confusing question, since you really need to give us more
information. Do you have tables that you need to store > 4 billion row? In
2005 there is a way to create an always increasing guid using
newsequentialid that can help a bit with the problems that using a GUID for
a key can create, but GUIDs make fine surrogate keys for tables, other than
performance and typability (the latter of which is just a programmer
convienience, so should not be really important to the question of whether
to choose them or not)
If you need the benefits of a GUID (like the UI can create them and be
guaranteed uniqueness,) then it might be worth it, otherwise use the correct
integer type. If you are going to have > 4 billion rows in many of your
tables, I hope you have spent a lot of money on a fast disk subsystem anyhow
because you will need it!
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
> If the use of the uniqueidentifier is now preferred, across how many
> relations is it optimal to use as the type for primary keys? Is there some
> point other than performance why one should not worry about reverting to
> the use of bigint for related tables? I've only read a couple of stories
> about it so far. What do you think?
> <%= Clinton Gallagher
>|||Well, good point. I should be comparing an int rather than bigint.
If its true that deleting a record can leave gaps in the sequence of the
key's
value when using an int which can impact sorting the same must be true
when using the uniqueidentifier.
Furthermore, as I understand it even when using newsequentialid( ) there
is no @.@.IDENTITY to get the value of the last inserted record. Do you
know if this is true?
Other than uniqueness, I see no reason to use uniqueidentifier yet..
That's the basis of this inquiry.
<%= Clinton Gallagher
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OwfDDvxOGHA.2040@.TK2MSFTNGP14.phx.gbl...
> This is kind of a confusing question, since you really need to give us
> more information. Do you have tables that you need to store > 4 billion
> row? In 2005 there is a way to create an always increasing guid using
> newsequentialid that can help a bit with the problems that using a GUID
> for a key can create, but GUIDs make fine surrogate keys for tables, other
> than performance and typability (the latter of which is just a programmer
> convienience, so should not be really important to the question of whether
> to choose them or not)
> If you need the benefits of a GUID (like the UI can create them and be
> guaranteed uniqueness,) then it might be worth it, otherwise use the
> correct integer type. If you are going to have > 4 billion rows in many
> of your tables, I hope you have spent a lot of money on a fast disk
> subsystem anyhow because you will need it!
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
>|||> Furthermore, as I understand it even when using newsequentialid( ) there
> is no @.@.IDENTITY to get the value of the last inserted record. Do you
> know if this is true?
One of the differences between uniqueidentifier and identity is that a
uniqueidentifier value can be assigned by the application instead of SQL
Server. This eliminates the need to retrieve the surrogate key value after
insertion.
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%2313yEZzOGHA.2624@.TK2MSFTNGP12.phx.gbl...
> Well, good point. I should be comparing an int rather than bigint.
> If its true that deleting a record can leave gaps in the sequence of the
> key's
> value when using an int which can impact sorting the same must be true
> when using the uniqueidentifier.
> Furthermore, as I understand it even when using newsequentialid( ) there
> is no @.@.IDENTITY to get the value of the last inserted record. Do you
> know if this is true?
> Other than uniqueness, I see no reason to use uniqueidentifier yet..
> That's the basis of this inquiry.
> <%= Clinton Gallagher
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:OwfDDvxOGHA.2040@.TK2MSFTNGP14.phx.gbl...
>|||That was helpful to be reminded of as the DNF will indeed generate a GUID
consistent with the uniqueidentitifer.
<%= Clinton Gallagher
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OOChgd0OGHA.2696@.TK2MSFTNGP14.phx.gbl...
> One of the differences between uniqueidentifier and identity is that a
> uniqueidentifier value can be assigned by the application instead of SQL
> Server. This eliminates the need to retrieve the surrogate key value
> after insertion.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:%2313yEZzOGHA.2624@.TK2MSFTNGP12.phx.gbl...
>

bigint field vs int

i am creating an application which the number of records will be beyond 100000 so i need to set the primary keys to bigint instead of int

i need to know about the performance and difference

will it affect the performance much and second will i have to change any code like delete,update

A standard Int ranges from -2,147,483,648 through 2,147,483,647 so you can use them to store values much larger than 100000.

If you do use bigints, you would need to use Int64 in your code

|||

HI,

There will be some performance difference in using int and bigint. If you use bigint the performance is slower in an 32 bit processer. But in case of a 64 bit processor the performance should not be a problem

|||

Int will give you up to 2,147,483,647 while Bigint gives up to 9,223,372,036,854,775,807. The only difference in your stored procedure is that you will need to change the parameter declarations from INT to BIGINT.

>i need to know about the performance and difference
Obviously more working memory will be required for BIGINT instead of INT (8 bytes instead of 4 for for each value), however SQL's will still work very efficiently provided there is enough RAM. If you only have enough RAM for INT, changing to BIGINT will put you at a disadvantage - so make sure you have enough RAM.

sql

Best way to retrieve rows matching an array of Ids

My database table has approx 400,000 rows, with an integer Id as primary key
.
I have an array of ids, with N elements where N is typically between 500 and
5,000.
I want to retrieve into a collection all rows whose id matches a value in
this array.
What is the 'best' way to do this and/or what are the tradeoffs to consider?
I can imagine a number of solutions as follows:
1. A parametered query which takes a single Id parameter and returns 1 row.
Implies N round trips to the server to retrieve N rows.
2. A parametered query with an IN clause which takes n parameters "... WHERE
ID IN (@.p1, @.p2, ... , @.pn)". Implies N/n round trips to the server where
each round trip retrieves up to n rows. What is the maximum / optimal valu
e
for n in this case?
3. Build the SQL string dynamically, with an IN clause generated by
concatenating up to n Id values. Implies N/n round trips to the server wher
e
each round trip retrieves up to n rows. What is the maximum / optimal valu
e
for n in this case?
4. Create a temporary table with a single integer ID column and insert the N
Ids from my array (how?). Join the original table with this temporary table
to retrieve all matching Ids.
...
Any suggestions?You can try the solution offered in http://www.aspfaq.com/2248 ... also
check out the link there to Erland's article on array handling.
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:656DF445-E0EC-495C-B62E-0A535EBC2366@.microsoft.com...
> My database table has approx 400,000 rows, with an integer Id as primary
> key.
> I have an array of ids, with N elements where N is typically between 500
> and
> 5,000.
> I want to retrieve into a collection all rows whose id matches a value in
> this array.
> What is the 'best' way to do this and/or what are the tradeoffs to
> consider?
> I can imagine a number of solutions as follows:
> 1. A parametered query which takes a single Id parameter and returns 1
> row.
> Implies N round trips to the server to retrieve N rows.
> 2. A parametered query with an IN clause which takes n parameters "...
> WHERE
> ID IN (@.p1, @.p2, ... , @.pn)". Implies N/n round trips to the server where
> each round trip retrieves up to n rows. What is the maximum / optimal
> value
> for n in this case?
> 3. Build the SQL string dynamically, with an IN clause generated by
> concatenating up to n Id values. Implies N/n round trips to the server
> where
> each round trip retrieves up to n rows. What is the maximum / optimal
> value
> for n in this case?
> 4. Create a temporary table with a single integer ID column and insert the
> N
> Ids from my array (how?). Join the original table with this temporary
> table
> to retrieve all matching Ids.
> ...
> Any suggestions?|||Very useful resources, thanks, it's a lot clearer. In my case, the number
of values is potentially greater than will fit into an 8K string. So I thin
k
the approach will be:
1. Create the temp table
2. Concatenate as many Ids as possible into a string which does not exceed
8K and call an SP which extracts integers from the string and inserts into
the temp table.
3. Repeat step 2 until all ids in my array are processed.
4. Execute a query with a join to the temp table
5. Drop the temp table.
This will be done from a .NET application. I am concerned about the
lifetime of the temporary table? If I get a SqlException while running the
above, my code will typically close its SqlConnection object - which won't
actually close the underlying connection, just return it to the connection
pool. Presumably this means the temp table could be left lying around for
the next unsuspecting user of that connection, right?
I guess the workaround would be to check for the existence of the temp table
before step 1 above and drop it if it already exists.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You can try the solution offered in http://www.aspfaq.com/2248 ... also
> check out the link there to Erland's article on array handling.
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:656DF445-E0EC-495C-B62E-0A535EBC2366@.microsoft.com...
>
>|||Arrays? Collections? Those are not SQL structures. We only have
tables. And we avoid procedural code, cursors, dynamic SQL and string
casting as much as possible -- which is about 99.99% of the time.
Load your list of ids into a one column table and use "WHERE foobar_id
IN (SELECT search_id FROM WorkingList)" in the body of the procedure.
Avoid kludges.|||I think we already established that a good approach is to load the ids into
a
(temporary) table. The question was, what is the 'best' way to load these
ids into the temporary table.
From the resources linked by Aaron Bertrand, it seems that a good approach
is to pass the array of values as a string - this means that all the values
can be passed in a single call.
Do you agree or what alternative would you recommend and why?
"--CELKO--" wrote:

> Arrays? Collections? Those are not SQL structures. We only have
> tables. And we avoid procedural code, cursors, dynamic SQL and string
> casting as much as possible -- which is about 99.99% of the time.
> Load your list of ids into a one column table and use "WHERE foobar_id
> IN (SELECT search_id FROM WorkingList)" in the body of the procedure.
> Avoid kludges.
>

Best way to insert data into tables without primary keys

I am working on a SQL Server database in which there are no primary
keys set on the tables. I can tell what they are using for a key. It
is usually named ID, has a data type of int and does not allow nulls.
However, since it is not set as a primary key you can create a
duplicate key.
This whole thing was created by someone who is long gone. I don't
know how long I will be here and I don't want to break anything. I
just want to work with things the way they are.
So if I want to insert a new record, and I want the key, which is
named ID, to be the next number in the sequence, is there something I
can do in an insert sql statement to do this?Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to prevent folks from
wasting time on already answered questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup), and
indicate that you've already posted elsewhere.
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
See .programming
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"tom c" <tomcarr1@.gmail.com> wrote in message
news:1157578232.173886.295680@.m79g2000cwm.googlegroups.com...
>I am working on a SQL Server database in which there are no primary
> keys set on the tables. I can tell what they are using for a key. It
> is usually named ID, has a data type of int and does not allow nulls.
> However, since it is not set as a primary key you can create a
> duplicate key.
> This whole thing was created by someone who is long gone. I don't
> know how long I will be here and I don't want to break anything. I
> just want to work with things the way they are.
> So if I want to insert a new record, and I want the key, which is
> named ID, to be the next number in the sequence, is there something I
> can do in an insert sql statement to do this?
>|||Will do Arnie. Sorry for the double post. I found .programming after I
had already posted here and then realized it was a better place for the
question and re posted it there. I won't do it again. Thanks for you
help.
Arnie Rowland wrote:
> Often, the quality of the responses received is related to our ability to
> 'bounce' ideas off of each other. In the future, to prevent folks from
> wasting time on already answered questions, please:
> Don't post to multiple newsgroups. Choose the one that best fits your
> question and post there. Only post to another newsgroup if you get no answer
> in a day or two (or if you accidentally posted to the wrong newsgroup), and
> indicate that you've already posted elsewhere.
> If you really think that a question belongs into more than one newsgroup,
> then use your newsreader's capability of multi-posting, i.e., posting one
> occurrence of a message into several newsgroups at once. If you multi-post
> appropriately, answers 'should' appear in all the newsgroups. Folks
> responding in different newsgroups will see responses from each other, even
> if the responses were posted in a different newsgroup.
>
> See .programming
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "tom c" <tomcarr1@.gmail.com> wrote in message
> news:1157578232.173886.295680@.m79g2000cwm.googlegroups.com...
> >I am working on a SQL Server database in which there are no primary
> > keys set on the tables. I can tell what they are using for a key. It
> > is usually named ID, has a data type of int and does not allow nulls.
> > However, since it is not set as a primary key you can create a
> > duplicate key.
> >
> > This whole thing was created by someone who is long gone. I don't
> > know how long I will be here and I don't want to break anything. I
> > just want to work with things the way they are.
> >
> > So if I want to insert a new record, and I want the key, which is
> > named ID, to be the next number in the sequence, is there something I
> > can do in an insert sql statement to do this?
> >

Best way to insert data into tables without primary keys

I am working on a SQL Server database in which there are no primary
keys set on the tables. I can tell what they are using for a key. It
is usually named ID, has a data type of int and does not allow nulls.
However, since it is not set as a primary key you can create a
duplicate key.
This whole thing was created by someone who is long gone. I don't
know how long I will be here and I don't want to break anything. I
just want to work with things the way they are.
So if I want to insert a new record, and I want the key, which is
named ID, to be the next number in the sequence, is there something I
can do in an insert sql statement to do this?Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to prevent folks from
wasting time on already answered questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup), and
indicate that you've already posted elsewhere.
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
See .programming
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"tom c" <tomcarr1@.gmail.com> wrote in message
news:1157578232.173886.295680@.m79g2000cwm.googlegroups.com...
>I am working on a SQL Server database in which there are no primary
> keys set on the tables. I can tell what they are using for a key. It
> is usually named ID, has a data type of int and does not allow nulls.
> However, since it is not set as a primary key you can create a
> duplicate key.
> This whole thing was created by someone who is long gone. I don't
> know how long I will be here and I don't want to break anything. I
> just want to work with things the way they are.
> So if I want to insert a new record, and I want the key, which is
> named ID, to be the next number in the sequence, is there something I
> can do in an insert sql statement to do this?
>|||Will do Arnie. Sorry for the double post. I found .programming after I
had already posted here and then realized it was a better place for the
question and re posted it there. I won't do it again. Thanks for you
help.
Arnie Rowland wrote:[vbcol=seagreen]
> Often, the quality of the responses received is related to our ability to
> 'bounce' ideas off of each other. In the future, to prevent folks from
> wasting time on already answered questions, please:
> Don't post to multiple newsgroups. Choose the one that best fits your
> question and post there. Only post to another newsgroup if you get no answ
er
> in a day or two (or if you accidentally posted to the wrong newsgroup), an
d
> indicate that you've already posted elsewhere.
> If you really think that a question belongs into more than one newsgroup,
> then use your newsreader's capability of multi-posting, i.e., posting one
> occurrence of a message into several newsgroups at once. If you multi-post
> appropriately, answers 'should' appear in all the newsgroups. Folks
> responding in different newsgroups will see responses from each other, eve
n
> if the responses were posted in a different newsgroup.
>
> See .programming
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "tom c" <tomcarr1@.gmail.com> wrote in message
> news:1157578232.173886.295680@.m79g2000cwm.googlegroups.com...

Best way to copy a table

What is the best way to copy a table, with the foriegn keys, primary keys and indexes, from one database to another using SQL Server 2005?

In sql server 2000 I used the DTS, but in SQL server 2005 it does'nt bring over the foriegn keys, primary keys and indexes.

Thanks

Right click database, "Generate Script..."

|||Run an INSERT INTO with Column list it gives you control of the insert order. Run a search for INSERT INTO with Column list in SQL Server BOL (books online). Hope this helps.