Choosing the PK

If you have two fields that together form a unique identifier, would it be
better to use them as PK or is it better to create another field (ex. Auto
Increment) and use that.
Specially if there are many tables that contain a FK to this table. It seems
a bit inpractical to keep several copies of these fields in several tables,
even though they are used as a PK<->FK.
(Assuming the two fields cannot be null)
What are your thoughts from experience?
Hi,
I will go with an Identity column with a clustered Index on it. This will
provide you a better data modeling and better data retrieval.
Thanks
Hari
SQL Server MVP
"Ash" wrote:

> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It seems
> a bit inpractical to keep several copies of these fields in several tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>
|||Ash,
in most cases I would say that using an identity key column as PK suits your
situation well. Consider extreme case: you have two columns as the
components of the key, each char(4000). In order to do a join, you use the
index that's created by setting up the PK. But each evaluation of each
index page would provide you only the information that the next index
reading should go up or down in pages. On the other hand, if you use an
identity column, with bigint it's only 8 bit, so your index search would be
much shorter. Normal life is not so extreme, but this is to illustrate.
Same principle applies where you do not want to use the identity key column.
If your key components are all narrow, and there are not many columns, AND
the values of those columns are frequently used in the child tables' access,
don't you think you would rather have them in those tables?
DB design and performance go hand in hand, and their optimization depends on
your situation.
hth
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F3C186BB-70D3-4316-A17F-B676D674CC25@.microsoft.com...
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It
> seems
> a bit inpractical to keep several copies of these fields in several
> tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>
|||I see your point..
So which scenario would the join run faster (I'm talking about > 2,000,000
records in each table)
1)ID is PK, F1 & F2 are unique and index
table1 table2
ID <IDENTITY> ID<IDENTIT>
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.ID=t2.ID AND t1.f1='Somthing' AND
t1.f2='Somthing Else'
2)F1 & F2 are PK and index
table1 table2
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.f1=t2.f1 AND t1.f2=t2.f2 AND
t1.f1='Somthing' AND t1.f2='Somthing Else'
Thanks..

Choosing the PK

If you have two fields that together form a unique identifier, would it be
better to use them as PK or is it better to create another field (ex. Auto
Increment) and use that.
Specially if there are many tables that contain a FK to this table. It seems
a bit inpractical to keep several copies of these fields in several tables,
even though they are used as a PK<->FK.
(Assuming the two fields cannot be null)
What are your thoughts from experience?Hi,
I will go with an Identity column with a clustered Index on it. This will
provide you a better data modeling and better data retrieval.
Thanks
Hari
SQL Server MVP
"Ash" wrote:
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It seems
> a bit inpractical to keep several copies of these fields in several tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||Ash,
in most cases I would say that using an identity key column as PK suits your
situation well. Consider extreme case: you have two columns as the
components of the key, each char(4000). In order to do a join, you use the
index that's created by setting up the PK. But each evaluation of each
index page would provide you only the information that the next index
reading should go up or down in pages. On the other hand, if you use an
identity column, with bigint it's only 8 bit, so your index search would be
much shorter. Normal life is not so extreme, but this is to illustrate.
Same principle applies where you do not want to use the identity key column.
If your key components are all narrow, and there are not many columns, AND
the values of those columns are frequently used in the child tables' access,
don't you think you would rather have them in those tables?
DB design and performance go hand in hand, and their optimization depends on
your situation.
hth
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F3C186BB-70D3-4316-A17F-B676D674CC25@.microsoft.com...
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It
> seems
> a bit inpractical to keep several copies of these fields in several
> tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||I see your point..
So which scenario would the join run faster (I'm talking about > 2,000,000
records in each table)
1)ID is PK, F1 & F2 are unique and index
table1 table2
ID <IDENTITY> ID<IDENTIT>
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.ID=t2.ID AND t1.f1='Somthing' AND
t1.f2='Somthing Else'
2)F1 & F2 are PK and index
table1 table2
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.f1=t2.f1 AND t1.f2=t2.f2 AND
t1.f1='Somthing' AND t1.f2='Somthing Else'
Thanks..

Choosing the PK

If you have two fields that together form a unique identifier, would it be
better to use them as PK or is it better to create another field (ex. Auto
Increment) and use that.
Specially if there are many tables that contain a FK to this table. It seems
a bit inpractical to keep several copies of these fields in several tables,
even though they are used as a PK<->FK.
(Assuming the two fields cannot be null)
What are your thoughts from experience?Hi,
I will go with an Identity column with a clustered Index on it. This will
provide you a better data modeling and better data retrieval.
Thanks
Hari
SQL Server MVP
"Ash" wrote:

> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It see
ms
> a bit inpractical to keep several copies of these fields in several tables
,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||Ash,
in most cases I would say that using an identity key column as PK suits your
situation well. Consider extreme case: you have two columns as the
components of the key, each char(4000). In order to do a join, you use the
index that's created by setting up the PK. But each evaluation of each
index page would provide you only the information that the next index
reading should go up or down in pages. On the other hand, if you use an
identity column, with bigint it's only 8 bit, so your index search would be
much shorter. Normal life is not so extreme, but this is to illustrate.
Same principle applies where you do not want to use the identity key column.
If your key components are all narrow, and there are not many columns, AND
the values of those columns are frequently used in the child tables' access,
don't you think you would rather have them in those tables?
DB design and performance go hand in hand, and their optimization depends on
your situation.
hth
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F3C186BB-70D3-4316-A17F-B676D674CC25@.microsoft.com...
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It
> seems
> a bit inpractical to keep several copies of these fields in several
> tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||I see your point..
So which scenario would the join run faster (I'm talking about > 2,000,000
records in each table)
1)ID is PK, F1 & F2 are unique and index
table1 table2
ID <IDENTITY> ID<IDENTIT>
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.ID=t2.ID AND t1.f1='Somthing' AND
t1.f2='Somthing Else'
2)F1 & F2 are PK and index
table1 table2
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.f1=t2.f1 AND t1.f2=t2.f2 AND
t1.f1='Somthing' AND t1.f2='Somthing Else'
Thanks..

checkboxlist sql database

I have a form with text boxes and checkboxlists that a user will fill out and click submit. When the user clicks submit, it will update the sql database. In my sql database I have checkbox fields. My question is how can I use the selected items in a checkboxlist to update the sql database individual check boxes. Below is the code I have so far that works for a text box:

PartialClass windrockform

Inherits System.Web.UI.Page

ProtectedSub submitButton_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles submitButton.Click

Dim dashdatasourceAsNew SqlDataSource

dashdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("WindrockIssuesConnectionString").ToString

dashdatasource.InsertCommandType = SqlDataSourceCommandType.Text

dashdatasource.InsertCommand ="INSERT INTO IssueLog (initiator) VALUES (@.initiator)"

dashdatasource.InsertParameters.Add("initiator", initiatorTextBox.Text)

Dim rowsAffectedAsInteger = 0

Try

rowsAffected = dashdatasource.Insert()

Catch exAs Exception

Server.Transfer("problem.aspx")

EndTry

If rowsAffected <> 1Then

Server.Transfer("problem.aspx")

Else

Server.Transfer("confirm.aspx")

EndIf

EndSub

EndClass

I am new to asp.net and would appreciate the help.

Thanks,

I believe the key you are looking for is

CheckBox11.Checked =False

|||

What are checkbox datatypes in SQL server? Maybe you mean they are of BIT datatype?

I think you will probably have to loop through your checkbox list and check each box to see if its checked, and set a parameter accordingly.

|||

Hi nkair19 ,

When the user clicks submit, it will update the sql database. In my sql database I have checkbox fields. My question is how can I use the selected items in a checkboxlist to update the sql database individual check boxes

I think maybe you mean you want to update certain data fields in your sql server database based on selected values in your checkboxlist. If I've understood you wrong, please feel free to tell me, thanks.

I think you can use an arraylist as a bridge between your checkboxlist and database filed. Like this:

ArrayList list=new ArrayList();

for (int i=0; i<checkboxlist1.Items.Count; i++)
{

if (checkboxlist1.Items[i].Selected)
{

list.add("the value you want to assign to the database field when checkbox item checked");
}

else

list.add("the value you want to assign to the database field when checkbox item not checked");
}

After this,in your sqlcommand text, you can refer to the corresponding arraylist value directly.

Hope my suggestion helps

|||

Did I forget to mention that I am using vb, not C#?

|||

I have this so far:

Dim listAsNew ArrayList()

3:

4:Dim iAsInteger = 0

5:While i < pacheckboxlist.Items.Count

6:

7:If pacheckboxlist.Items(i).SelectedThen

8:

9: list.Add("win6310_pa--is this where I add the check box name that I have in the SQL database?")

10:Else

11: list.Add("the value you want to assign to the database field when checkbox item not checked--I don't want anything to happen if nothing is checked because I have other checkboxlist groups to choose from")

12:

13:

14:EndIf

15: System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)

16:EndWhile

After this,in your sqlcommand text, you can refer to the corresponding arraylist value directly. --confused here.

Thanks.

Checkbox

Hi,

Even this question is not related to this forum but may be some one help me.

I have a master detail form. I want to delete record but only those record that user click with checkbox. There is no field like boolean. How can I make a scenerio about adding an unbound checkbox in detail form and how would i link with rows in detail sub form.

any help will be highly appreciated.

Regards,I'm guessing you are using MS Access? You should probably post your message in the MS access forum.

Use the bit data type in SQL Server to represent boolean values.|||i'd rub it with bacon.
everything works better with bacon.

mmmmmmmm