Print Multi table report with a range of parameters.

We have a work order print that is called from a web page using a URL. The
print consists of 4 tables. All of the recordsets use the same parameters
but come from different data sets. I have a request to make it print
multiple work orders at a time.
When I use a select for multiple work orders it prints it all in one set of
tables but I want a seperate set of tables for each work order. Is there a
way to group the tables together?
Any other ideas?You need to use a list control, and if your reports are in RS 2000, you need
to do some hand editing of the xml.
First create your list, then add the table which displays the actual data +
any textboxes for headings and such.
Then you mark your list, and set the Grouping property to the Work Order ID.
If you're working with RS 2005, right click on the list in the report,
choose Properties. Click on the "Edit Details Group" Button. Select "Page
Break at end". (Or start, if you want.)
If you're working with RS 2000, you need to hand edit your RDL like this:
Open the code version of the report
Find your list
In the Grouping section, add <PageBreakAtEnd>true</PageBreakAtEnd>
Your code should look something like this:
<List Name="List1">
<Style>
<FontFamily>Times New Roman</FontFamily>
<FontSize>18pt</FontSize>
<Color>Maroon</Color>
<FontWeight>900</FontWeight>
</Style>
<Top>0.875in</Top>
<Grouping Name="ListGrouping">
<GroupExpressions>
<GroupExpression>=Fields(Parameters!PageGroupingParameter1.Value).Value</GroupExpression>
<GroupExpression>=Fields(Parameters!PageGroupingParameter2.Value).Value</GroupExpression>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
Save the code, and test the report. Did it work?
Kaisa M. Lindahl Lervik
"msc" <matt@.dontspam.com> wrote in message
news:6C0D2B4C-DCED-4559-A849-18B260FA7C03@.microsoft.com...
> We have a work order print that is called from a web page using a URL.
> The
> print consists of 4 tables. All of the recordsets use the same parameters
> but come from different data sets. I have a request to make it print
> multiple work orders at a time.
> When I use a select for multiple work orders it prints it all in one set
> of
> tables but I want a seperate set of tables for each work order. Is there
> a
> way to group the tables together?
> Any other ideas?
>sql

Print list of tables with Identity row set to NOT FOR REPLICATION

Does anyone know if there's an SQL command i can run that will list
the tables in a database that have an identity column set to NOT FOR
REPLICATION?

Many thanks

Dan Williams."Dan Williams" <dan_williams@.newcross-nursing.com> wrote in message
news:2eac5d02.0406030812.2651f9e6@.posting.google.c om...
> Does anyone know if there's an SQL command i can run that will list
> the tables in a database that have an identity column set to NOT FOR
> REPLICATION?
> Many thanks
> Dan Williams.

select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdNotForRepl') =
1

Simon|||Cool. Thanks for that, it worked a treat.

Dan

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<40bf6492$1_1@.news.bluewin.ch>...
> "Dan Williams" <dan_williams@.newcross-nursing.com> wrote in message
> news:2eac5d02.0406030812.2651f9e6@.posting.google.c om...
> > Does anyone know if there's an SQL command i can run that will list
> > the tables in a database that have an identity column set to NOT FOR
> > REPLICATION?
> > Many thanks
> > Dan Williams.
> select TABLE_NAME, COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdNotForRepl') =
> 1
> Simon

print list of queries, tables, views and sp

I just started a new job and 1st time on sql server, how can i print list of queries, tables, views, stored procedures and functions?What do you mean by print?
What version of SQL Server are you running? This will have an effect on the query you need to run. The below example was written for 2000

SELECT name
, id
, type
FROM sysobjects
WHERE type IN ('V', 'U', 'SP', 'FN')
-- v = view, u = table, sp = sproc, fn = user-defined function|||2005 Users Note:
BOL Says
Important: This Microsoft SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use catalog views instead.

Any general comments as to whether we should still be coding with sysobjects ?

:angel:

GW|||We are kind of caught on the edge of the sword on this issue. Because users rarely give us enough information to know what version of SQL they are using, we tend to give them the answers that work under the largest possible set of conditions.

You are correct, using the catalog views is preferable if you are running a version of SQL Server that supports the catalog views. On a "going forward" basis, you probably ought to only use the catalog views, but on a "forum answer" I tend to stick with what will work for the largest number of people.

-PatP|||Anybody have the catalog solution to hand?

I don't get to play on much 2K5, but I am going to be taking my MCTS in it in a couple of months, so I should really get brushed up on it :p|||Play around with the view sys.objects. You should have it in no time. I think id changed to object_id, but most of the rest is the same.|||SELECT ROUTINE_TYPE, ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES

SELECT TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES-PatP

Print each tables in new page Options

Hi All,

I have a report which has four tables in it. But I have only one
Dataset. Since this dataset has more then 30 fields, I have divided
this into four tables to be shown in a report. This will help the user
to take print of the report and place each print out side by side for
view.

Showing each table in a new page is just fine. It works. But when we
take print out some times rows of second table are printed in same
page that shows rows of first table.

With this user can not put each print out side by side for view.

Is there any way to set option like print each table in new page? I
mean to say after printing first table, second table print should
start at new page.

Thanks in advance.

This is actually pretty easy (if it is what I think it is). I did this the other day with the same amount of data you are dealing with. Just get to the table's property pop-up menu and under the "General" tab click the check box next to "Insert a page break after this table". That should do the job for you. But I would suggest putting the tables directly under each other if you have manually spaced them out onto each page. This way you won't have awkward whitespace at the beginning of each page. Let me know if this helps you.|||

Thanks for the reply.

I forgot to mention one point. I have set visibility property on tables. Will this visibilty property affects how pages are printed?

Regards,

|||Yes, the visibility will most likely affect your formatting. I'm assuming you're using the dynamic visibility function for each table, as opposed to just a child object of the table. In this case it is harder to give you a solution. The reason is because SSRS 2005 is not capable of dynamic table relocation. If you could give me more specifics about which objects are dynamically visible then I might be able to offer a solution to your problem.|||

The whole table visibility is set through expression based on user input.

The report has large number of fields to be shown. If user selects Print format then these four tables will be shown to user to and user can take print out of the same. If user selects export format then all fields will be shown in one table and the above mentioned 4 tables will be hidden.

But report is shown properly. All four tables are shown in new page if user selects Print option. Page break is OK. While printing, report is printed continuously. No Page breaks while printing.

Is there anyother approach to this problem?

Prinitng Tables

Is there a way within Enterprise Manager to print the contents of a Table?
I could of sworn there was a way...I think I just forget.
Thanks!Use Query Analyzer. You can choose results to text or results to grid and
then print the results pane.
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:9F727D28-43B4-4715-9B19-F0D223FFB6DE@.microsoft.com...
> Is there a way within Enterprise Manager to print the contents of a
> Table?
> I could of sworn there was a way...I think I just forget.
> Thanks!|||You can highlight columns or rows and copy/paste them to Excel. It will even
carry over the column names.
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:9F727D28-43B4-4715-9B19-F0D223FFB6DE@.microsoft.com...
> Is there a way within Enterprise Manager to print the contents of a
> Table?
> I could of sworn there was a way...I think I just forget.
> Thanks!sql

Primarykey Fields

I'm try to get table columns name & primary key to generat Dlete/Insert
script to all my database tables..
I was able to get list of all user tables from sysobject and columns list
from syscolumns..
Now i need to know the primary key column (For delete Statments)..
How could i detrmine which column is primarykey or composite key'
thanxA couple of suggestions:
First, you may be trying to reinvent the wheel; have you looked at
using SQL-DMO or SCPTXFR to script out your database? May save you a
lot of time and energy.
Second, wherever possible, use the INFORMATION_SCHEMA views rather than
the system tables to query this type of information; look at the
following queries i nthe pubs database as an example:
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'authors'
SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = 'authors'
Note that the identification of a primary key is a constraint on the
table, not a property of a column.
Of course, this only rings true for SQL Server 2000; no clue about
SS2005.
HTH,
Stu|||Islamegy (NULL_Islamegy_NULL@.yahoo.com) writes:
> I'm try to get table columns name & primary key to generat Dlete/Insert
> script to all my database tables..
> I was able to get list of all user tables from sysobject and columns list
> from syscolumns..
> Now i need to know the primary key column (For delete Statments)..
> How could i detrmine which column is primarykey or composite key'
> thanx
Here is a query that lists the PK columns for all tables in a database.
There is a restriction that the query as written will not cover keys
with more than 10 columns, but this is easy to address.
select o.name,
MAX(CASE ik.keyno WHEN 1 THEN c.name END) +
coalesce(MAX(CASE ik.keyno WHEN 2 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 3 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 4 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 5 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 6 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 7 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 8 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 9 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 10 THEN ', ' + c.name END), '')
from sysobjects o
join sysindexes i on i.id = o.id
join sysindexkeys ik on i.id = ik.id
and i.indid = ik.indid
join syscolumns c on ik.id = c.id
and ik.colid = c.colid
join sysobjects pk ON i.name = pk.name
AND o.id = pk.parent_obj
group by o.name
order by o.name
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanx so much for this query..
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns976FD9D836B2FYazorman@.127.0.0.1...
> Islamegy (NULL_Islamegy_NULL@.yahoo.com) writes:
> Here is a query that lists the PK columns for all tables in a database.
> There is a restriction that the query as written will not cover keys
> with more than 10 columns, but this is easy to address.
> select o.name,
> MAX(CASE ik.keyno WHEN 1 THEN c.name END) +
> coalesce(MAX(CASE ik.keyno WHEN 2 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 3 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 4 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 5 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 6 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 7 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 8 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 9 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 10 THEN ', ' + c.name END), '')
> from sysobjects o
> join sysindexes i on i.id = o.id
> join sysindexkeys ik on i.id = ik.id
> and i.indid = ik.indid
> join syscolumns c on ik.id = c.id
> and ik.colid = c.colid
> join sysobjects pk ON i.name = pk.name
> AND o.id = pk.parent_obj
> group by o.name
> order by o.name
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Primary keys ...

I was setting up replication and discovered that explicitly defined primary
keys are required on the tables being replicated. We currenty have defined
these on exactly zero of our tables (legacy thing - you get the picture). I
told the developers I needed them. They asked me if it would break anything
if we put them on. I'm thinking that as long as we don't define explicit
PK/FK relationshiops bewteen the tables we should be OK.
I would appreciate any insight on the matter.
Bob Castleman
DBA PoseurOne idea would be to add a UniqueIdentifier field and set it to be the
RowGuidCol and the PK (along with a default of NewId()). By doing this, you
avoid SQL adding this column for you anyway for replication.
Thomas
"Bob Castleman" <nomail@.here> wrote in message
news:uNvZICqRFHA.2528@.TK2MSFTNGP10.phx.gbl...
>I was setting up replication and discovered that explicitly defined primary
>keys are required on the tables being replicated. We currenty have defined
>these on exactly zero of our tables (legacy thing - you get the picture). I
>told the developers I needed them. They asked me if it would break anything
if
>we put them on. I'm thinking that as long as we don't define explicit PK/FK
>relationshiops bewteen the tables we should be OK.
> I would appreciate any insight on the matter.
> Bob Castleman
> DBA Poseur
>|||We already have unique IDs on the tables, they were just never explicitly
defined within SQL Server as PKs. We may at some point start defining PK/FK
relationships and referential integrity so wouldn't make sense to use our
current IDs? I am more concerned about unintended side effects. I can't see
how defining a PK on table would cause a problem, but I need to make sure.
Bob
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
> One idea would be to add a UniqueIdentifier field and set it to be the
> RowGuidCol and the PK (along with a default of NewId()). By doing this,
> you avoid SQL adding this column for you anyway for replication.
>
> Thomas
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:uNvZICqRFHA.2528@.TK2MSFTNGP10.phx.gbl...
>|||As long as the data is actually unique, it shouldn't be a problem.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Bob Castleman" <nomail@.here> wrote in message
news:%23PkbQfqRFHA.648@.TK2MSFTNGP14.phx.gbl...
> We already have unique IDs on the tables, they were just never explicitly
> defined within SQL Server as PKs. We may at some point start defining
> PK/FK relationships and referential integrity so wouldn't make sense to
> use our current IDs? I am more concerned about unintended side effects. I
> can't see how defining a PK on table would cause a problem, but I need to
> make sure.
> Bob
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
>|||Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
use northwind
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a cross join sysobjects as b
go
declare @.sql nvarchar(4000)
declare @.s datetime
declare @.e datetime
declare @.i int
declare @.datepart varchar(15)
set @.s = '2005-01-01T12:00:00.000'
set @.e = '2005-01-01T13:00:00.000'
set @.i = 15
set @.datepart = 'minute'
set @.sql = N'
select
right(convert(varchar(35), dateadd(' + @.datepart + N', number, ''' +
convert(varchar(25), @.s, 126) + N'''), 100), 7) as colA
from
number as n
where
number % ' + ltrim(@.i) + N' = 0
and dateadd(' + @.datepart + N', number, ''' + convert(varchar(25), @.s, 126)
+ N''') < cast(''' + convert(varchar(25), @.e, 126) + N''' as datetime)'
print @.sql
exec sp_executesql @.sql
go
drop table number
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Bob Castleman" wrote:

> I was setting up replication and discovered that explicitly defined primar
y
> keys are required on the tables being replicated. We currenty have defined
> these on exactly zero of our tables (legacy thing - you get the picture).
I
> told the developers I needed them. They asked me if it would break anythin
g
> if we put them on. I'm thinking that as long as we don't define explicit
> PK/FK relationshiops bewteen the tables we should be OK.
> I would appreciate any insight on the matter.
> Bob Castleman
> DBA Poseur
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Why should I consider using an auxiliary numbers table?
> http://www.aspfaq.com/show.asp?id=2516
> use northwind
> go
> select
> identity(int, 1, 1) as number
> into
> number
> from
> sysobjects as a cross join sysobjects as b
> go
>
> declare @.sql nvarchar(4000)
> declare @.s datetime
> declare @.e datetime
> declare @.i int
> declare @.datepart varchar(15)
> set @.s = '2005-01-01T12:00:00.000'
> set @.e = '2005-01-01T13:00:00.000'
> set @.i = 15
> set @.datepart = 'minute'
> set @.sql = N'
> select
> right(convert(varchar(35), dateadd(' + @.datepart + N', number, ''' +
> convert(varchar(25), @.s, 126) + N'''), 100), 7) as colA
> from
> number as n
> where
> number % ' + ltrim(@.i) + N' = 0
> and dateadd(' + @.datepart + N', number, ''' + convert(varchar(25), @.s, 12
6)
> + N''') < cast(''' + convert(varchar(25), @.e, 126) + N''' as datetime)'
> print @.sql
> exec sp_executesql @.sql
> go
> drop table number
> go
>
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> AMB
>
> "Bob Castleman" wrote:
>|||If that's the case, then just declare those columns as the PK. You can decla
re a
PK without having to declare FK (although it helps with data integrity).
The only problems you might encounter by setting a given column(s) as the PK
are:
1. If the data is not unique or contains nulls
or
2. If the the app that writes the data expects that it can fill in duplicate
or
null data on one pass even if it changes it to be non-nullable and unique in
another pass. Obviously, this won't work as SQL will prevent any nulls or
duplciate values from ever being written.
Thomas
"Bob Castleman" <nomail@.here> wrote in message
news:%23PkbQfqRFHA.648@.TK2MSFTNGP14.phx.gbl...
> We already have unique IDs on the tables, they were just never explicitly
> defined within SQL Server as PKs. We may at some point start defining PK/F
K
> relationships and referential integrity so wouldn't make sense to use our
> current IDs? I am more concerned about unintended side effects. I can't se
e
> how defining a PK on table would cause a problem, but I need to make sure.
> Bob
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
>

Primary Keys

I came across a database created by another person in the company that
frequently has 2 primary keys on its tables. I'm not sure why it was done
this way, my guess is to create uniqueness with the 2-field combination.
My question is: Is this poor design? Shouldn't we create constraints or
indexes if 2-field uniqueness is the purpose. Is one way more efficient
than the other? Any other thoughts as to why a table would be set up this
way? Thanks.
>> I came across a database created by another person in the company that[vbcol=seagreen]
Since 2 primary keys are impossible in a single SQL table, I guess you meant
it to be a single key with two columns.
[vbcol=seagreen]
The number of columns in a key by itself does not tell us whether a specific
design is good or bad. There is nothing wrong in having multi-column keys;
however multi-column references can occasionally mess up data integrity
since SQL products allow NULLs in referencing columns.
[vbcol=seagreen]
Not necessarily. Keys by themselves are constraints and guarantee uniqueness
any way.
[vbcol=seagreen]
That is mostly irrelevant since at the logical level, key selection should
not be based on efficiency which is determined at the implementation level.
Considering the physical implementation level, unless we consider all
possible query combinations, data access paths and optimization rules and
test out all of them on a specific system using on a specific dataset, we
cannot generalize and conclude single column keys are efficient than
multicolumn keys or vice-versa.
Anith
|||Sorry wasn't clear, you are correct, the tables have 2 columns combined to
make a concatenated primary key. In some instances, one column is a foreign
key of another table.
My next question: If a primary key consists of 2 columns, how would, or
could you, make that a foreign key in another table, and how would you query
such a thing?
Say for instance, I have a customer table, and an address table.
The address table fields:
CustomerID
AddressDescription
Address
Sample Data:
CustomerID AddressDescription Address
1 Main Office 123 Main
Street
1 Secondary Location 456 Main Street
So now I want to create another table that lists, for instance, people
located at each office. How would I set up the foreign key to point to the
address table. Wouldn't it be necessary to create another field in the
address table, maybe an identity field, and make that field the primary key?
And how would you query the database when joining tables? If you joined the
address table to a query, could you base it on both the CustomerID and
AddressDescription being equal to another field in another table. You can't
do this, can you? So I guess efficiency really isn't my question, maybe
more that it's a practicality or best practice issue: Under what
circumstances would a 2-column primary key be preferable to a nonmeaningful
primary key, with perhaps constraints on the 2 columns if the combined
uniqueness is the desired result?
I hope I'm making sense, thanks for your thoughts! And thanks for the
reminder about allowed nulls in the referenced columns, that could raise
serious issues.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
> Since 2 primary keys are impossible in a single SQL table, I guess you
meant
> it to be a single key with two columns.
>
> The number of columns in a key by itself does not tell us whether a
specific
> design is good or bad. There is nothing wrong in having multi-column keys;
> however multi-column references can occasionally mess up data integrity
> since SQL products allow NULLs in referencing columns.
>
> Not necessarily. Keys by themselves are constraints and guarantee
uniqueness
> any way.
>
> That is mostly irrelevant since at the logical level, key selection should
> not be based on efficiency which is determined at the implementation
level.
> Considering the physical implementation level, unless we consider all
> possible query combinations, data access paths and optimization rules and
> test out all of them on a specific system using on a specific dataset, we
> cannot generalize and conclude single column keys are efficient than
> multicolumn keys or vice-versa.
> --
> Anith
>
|||To avoid problems and other types of modifications to the data structures, I
would suggest switching to a surrogate key and making that the PK. You can
then create a UNIQUE index covering those two columns.
This surrogate key allows you more flexibility down the road. It's easier
to add tables, create the joins (less typing anyhow), modify the current PK
to something else etc.
HTH
Rick
"Rock" <rockisland@.yahoo.com> wrote in message
news:OLbfeCttEHA.2804@.TK2MSFTNGP14.phx.gbl...
> Sorry wasn't clear, you are correct, the tables have 2 columns combined to
> make a concatenated primary key. In some instances, one column is a
foreign
> key of another table.
> My next question: If a primary key consists of 2 columns, how would, or
> could you, make that a foreign key in another table, and how would you
query
> such a thing?
> Say for instance, I have a customer table, and an address table.
> The address table fields:
> CustomerID
> AddressDescription
> Address
> Sample Data:
> CustomerID AddressDescription Address
> 1 Main Office 123 Main
> Street
> 1 Secondary Location 456 Main Street
> So now I want to create another table that lists, for instance, people
> located at each office. How would I set up the foreign key to point to
the
> address table. Wouldn't it be necessary to create another field in the
> address table, maybe an identity field, and make that field the primary
key?
> And how would you query the database when joining tables? If you joined
the
> address table to a query, could you base it on both the CustomerID and
> AddressDescription being equal to another field in another table. You
can't
> do this, can you? So I guess efficiency really isn't my question, maybe
> more that it's a practicality or best practice issue: Under what
> circumstances would a 2-column primary key be preferable to a
nonmeaningful[vbcol=seagreen]
> primary key, with perhaps constraints on the 2 columns if the combined
> uniqueness is the desired result?
> I hope I'm making sense, thanks for your thoughts! And thanks for the
> reminder about allowed nulls in the referenced columns, that could raise
> serious issues.
>
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
that[vbcol=seagreen]
> meant
> specific
keys;[vbcol=seagreen]
the[vbcol=seagreen]
> uniqueness
should[vbcol=seagreen]
> level.
and[vbcol=seagreen]
we
>
|||>> My next question: If a primary key consists of 2 columns, how would, or[vbcol=seagreen]
That would be simple. Just make sure your referencing table has two columns
with compatible datatypes and you can use the REFERENCES clause in the
CREATE TABLE or ALTER TABLE DDL like:
CREATE TABLR tbl (
c1 INT NOT NULL, c2 CHAR(30) NOT NULL, ...
( c1, c2 ) REFERENCES tbl2 ( c1, c2 )
PRIMARY KEY ( c1, c2 ) ) ;
For exact syntax, please refer to SQL Server Books Online.
[vbcol=seagreen]
Considering the example in your post, if the design is clean and both
columns are non-nullable they can be considered as valid choice for primary
key. ( Note that there are other desirable characteristics like stability,
minimality, simplicity and familiarity that come into play during key
selection as well )
[vbcol=seagreen]
As with many design considerations, the answer is it depends. If two columns
in the primary key can truly distinguish one entity from another, there is
no need to introduce a redundant identifier. And all the references to this
table must include the two columns and meaningful queries can be expressed
without loss of integrity.
However, multi-column keys can be often cumbersome, thereby could fail the
simplicity criteria. And NULLs in referencing columns is a serious issue in
SQL products which gives another logical reason to avoid multi-column keys
in some cases. That often gives a logical reason to use an additional
identifier which is unfamiliar, but stable, simple and minimal.
BTW, nothing prevents one from introducing artificial identifiers in all
your tables, but there are no proven advantages in doing so.
[vbcol=seagreen]
Yes, it does. But neither the SQL standards nor the vendors are interested
in addressing such issues since they are mostly busy implementing UFOs :-)
Anith
|||On Wed, 20 Oct 2004 12:14:14 -0600, Rock wrote:

>My next question: If a primary key consists of 2 columns, how would, or
>could you, make that a foreign key in another table, and how would you query
>such a thing?
>Say for instance, I have a customer table, and an address table.
>The address table fields:
>CustomerID
>AddressDescription
>Address
>Sample Data:
>CustomerID AddressDescription Address
>1 Main Office 123 Main
>Street
>1 Secondary Location 456 Main Street
>So now I want to create another table that lists, for instance, people
>located at each office. How would I set up the foreign key to point to the
>address table. Wouldn't it be necessary to create another field in the
>address table, maybe an identity field, and make that field the primary key?
Hi Rock,
That's not necessary. Your people table would look like this:
CREATE TABLE People (....,
....,
CustomerID int NOT NULL,
AddressDescription varchar(25) NOT NULL,
....,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
....
)

>And how would you query the database when joining tables? If you joined the
>address table to a query, could you base it on both the CustomerID and
>AddressDescription being equal to another field in another table. You can't
>do this, can you?
Yes, you can:
SELECT some interesting columns
FROM People
INNER JOIN Addresses
ON Addresses.CustomerID = People.CustomerID
AND Addresses.AddressDescription = People.AddressDescription
WHERE ....

> So I guess efficiency really isn't my question, maybe
>more that it's a practicality or best practice issue: Under what
>circumstances would a 2-column primary key be preferable to a nonmeaningful
>primary key, with perhaps constraints on the 2 columns if the combined
>uniqueness is the desired result?
If the addresses table used CustomerID plus a short code for the
addresstype as it's primary key, I'd just use this natural key.
In your case, the more verbose AddressDescription is used as part of the
primary key. As you can see above, this complete description would have to
be repeated each time an address is referenced. This will use up more
space in your database. I would definitely consider using a shorter key.
This key *could* be the AddressType (moving the AddressDescription to
another table, listing all AddressTypes with their description), or I
could choose to introduce a surrogate key. This will take up more space in
the addresses table itself (as you add an extra column, plus an extra
index to check uniqueness on both the surrogate key and the combination of
CustomerID / AddressDescription), but it would reduce the amount of space
needed for references. It would increase speed on joins, but it might also
introduce the need to join in an extra table on some queries.
The bottom line is: there is no fixed answer, it all depends. At the end
of the day, you'll have to weigh all options against each other and choose
the one that fits your situation best.

>I hope I'm making sense, thanks for your thoughts! And thanks for the
>reminder about allowed nulls in the referenced columns, that could raise
>serious issues.
These can be easiliy avoided. Either set a NOT NULL constraint on the
columns, or (if you must allow NULLS) add a CHECK constraint to ensure
that either both columns are NULL, or both columns are not NULL.
CREATE TABLE People (....,
....,
CustomerID int, -- NULL allowed
AddressDescription varchar(25), -- NULL allowed
....,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
CHECK ( ( CustomerID IS NULL
AND AddressDescription IS NULL)
OR ( CustomerID IS NOT NULL
AND AddressDescription IS NOT NULL))
....
)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
[vbcol=seagreen]
Not quite that easily. The problem is in regard to multi-column primary keys
and missing information. Relational literature has volumes about such
logical problems and the lack of theory behind handling missing information
being a serious issue.
We know SQL has only one way of dealing with missing information -- NULLs.
Since there is no mandatory requirement that all foreign key column values
should be non-nullable, SQL allows foreign key references on columns whose
values are known, unknown or partially known.
Now, consider a table with a multi-column primary key with columns, colA and
colB which is referenced by another table with corresponding multi-column
foreign key.
When the user inserts a referencing row with a value for colA and NULL for
colB, should the DBMS allow the insert? Since logically DBMS cannot preserve
the integrity of the relationship should it abort the insert? Or should it
consider the validity of the colA value alone and proceed with the insert?
What if there are duplicate values for colA in the referencing table which
cannot be evaluated due to NULLs in colB? If the colA value exists only once
should the DBMS substitute the only corresponding value for colB and proceed
with the insert ?
When deleting all the rows from the referenced table which contain a
specific value for colA, what should the DBMS do to the referencing table
that contains corresponding rows with a value for colA and a NULL for colB ?
While updating a few rows in the referenced table which contain a known colA
and colB, should the DBMS allow cascading actions on all corresponding rows
in the referencing table? Or should it evaluate the existence of rows with a
value for colA and exclude the rows with a null for colB ?
Anith
|||On Wed, 20 Oct 2004 17:21:36 -0500, Anith Sen wrote:

>Hugo,
>
>Not quite that easily. The problem is in regard to multi-column primary keys
>and missing information. Relational literature has volumes about such
>logical problems and the lack of theory behind handling missing information
>being a serious issue.
(snip)
>When the user inserts a referencing row with a value for colA and NULL for
>colB, should the DBMS allow the insert? Since logically DBMS cannot preserve
>the integrity of the relationship should it abort the insert? Or should it
>consider the validity of the colA value alone and proceed with the insert?
(snip more consequences of this proposed insert)
Hi Anith,
That is exactly why I suggested to either give all foreign key columns a
NOT NULL constraint, or (if NULL must be allowed) a CHECK to enforce that
they are either ALL null, or ALL not null. This will prevent all the
potential problems you describe.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Primary Keys

I came across a database created by another person in the company that
frequently has 2 primary keys on its tables. I'm not sure why it was done
this way, my guess is to create uniqueness with the 2-field combination.
My question is: Is this poor design? Shouldn't we create constraints or
indexes if 2-field uniqueness is the purpose. Is one way more efficient
than the other? Any other thoughts as to why a table would be set up this
way? Thanks.>> I came across a database created by another person in the company that
>> frequently has 2 primary keys on its tables. I'm not sure why it was
>> done this way, my guess is to create uniqueness with the 2-field
>> combination.
Since 2 primary keys are impossible in a single SQL table, I guess you meant
it to be a single key with two columns.
>> My question is: Is this poor design?
The number of columns in a key by itself does not tell us whether a specific
design is good or bad. There is nothing wrong in having multi-column keys;
however multi-column references can occasionally mess up data integrity
since SQL products allow NULLs in referencing columns.
>> Shouldn't we create constraints or indexes if 2-field uniqueness is the
>> purpose.
Not necessarily. Keys by themselves are constraints and guarantee uniqueness
any way.
>> Is one way more efficient than the other?
That is mostly irrelevant since at the logical level, key selection should
not be based on efficiency which is determined at the implementation level.
Considering the physical implementation level, unless we consider all
possible query combinations, data access paths and optimization rules and
test out all of them on a specific system using on a specific dataset, we
cannot generalize and conclude single column keys are efficient than
multicolumn keys or vice-versa.
--
Anith|||Sorry wasn't clear, you are correct, the tables have 2 columns combined to
make a concatenated primary key. In some instances, one column is a foreign
key of another table.
My next question: If a primary key consists of 2 columns, how would, or
could you, make that a foreign key in another table, and how would you query
such a thing?
Say for instance, I have a customer table, and an address table.
The address table fields:
CustomerID
AddressDescription
Address
Sample Data:
CustomerID AddressDescription Address
1 Main Office 123 Main
Street
1 Secondary Location 456 Main Street
So now I want to create another table that lists, for instance, people
located at each office. How would I set up the foreign key to point to the
address table. Wouldn't it be necessary to create another field in the
address table, maybe an identity field, and make that field the primary key?
And how would you query the database when joining tables? If you joined the
address table to a query, could you base it on both the CustomerID and
AddressDescription being equal to another field in another table. You can't
do this, can you? So I guess efficiency really isn't my question, maybe
more that it's a practicality or best practice issue: Under what
circumstances would a 2-column primary key be preferable to a nonmeaningful
primary key, with perhaps constraints on the 2 columns if the combined
uniqueness is the desired result?
I hope I'm making sense, thanks for your thoughts! And thanks for the
reminder about allowed nulls in the referenced columns, that could raise
serious issues.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
> >> I came across a database created by another person in the company that
> >> frequently has 2 primary keys on its tables. I'm not sure why it was
> >> done this way, my guess is to create uniqueness with the 2-field
> >> combination.
> Since 2 primary keys are impossible in a single SQL table, I guess you
meant
> it to be a single key with two columns.
> >> My question is: Is this poor design?
> The number of columns in a key by itself does not tell us whether a
specific
> design is good or bad. There is nothing wrong in having multi-column keys;
> however multi-column references can occasionally mess up data integrity
> since SQL products allow NULLs in referencing columns.
> >> Shouldn't we create constraints or indexes if 2-field uniqueness is the
> >> purpose.
> Not necessarily. Keys by themselves are constraints and guarantee
uniqueness
> any way.
> >> Is one way more efficient than the other?
> That is mostly irrelevant since at the logical level, key selection should
> not be based on efficiency which is determined at the implementation
level.
> Considering the physical implementation level, unless we consider all
> possible query combinations, data access paths and optimization rules and
> test out all of them on a specific system using on a specific dataset, we
> cannot generalize and conclude single column keys are efficient than
> multicolumn keys or vice-versa.
> --
> Anith
>|||To avoid problems and other types of modifications to the data structures, I
would suggest switching to a surrogate key and making that the PK. You can
then create a UNIQUE index covering those two columns.
This surrogate key allows you more flexibility down the road. It's easier
to add tables, create the joins (less typing anyhow), modify the current PK
to something else etc.
HTH
Rick
"Rock" <rockisland@.yahoo.com> wrote in message
news:OLbfeCttEHA.2804@.TK2MSFTNGP14.phx.gbl...
> Sorry wasn't clear, you are correct, the tables have 2 columns combined to
> make a concatenated primary key. In some instances, one column is a
foreign
> key of another table.
> My next question: If a primary key consists of 2 columns, how would, or
> could you, make that a foreign key in another table, and how would you
query
> such a thing?
> Say for instance, I have a customer table, and an address table.
> The address table fields:
> CustomerID
> AddressDescription
> Address
> Sample Data:
> CustomerID AddressDescription Address
> 1 Main Office 123 Main
> Street
> 1 Secondary Location 456 Main Street
> So now I want to create another table that lists, for instance, people
> located at each office. How would I set up the foreign key to point to
the
> address table. Wouldn't it be necessary to create another field in the
> address table, maybe an identity field, and make that field the primary
key?
> And how would you query the database when joining tables? If you joined
the
> address table to a query, could you base it on both the CustomerID and
> AddressDescription being equal to another field in another table. You
can't
> do this, can you? So I guess efficiency really isn't my question, maybe
> more that it's a practicality or best practice issue: Under what
> circumstances would a 2-column primary key be preferable to a
nonmeaningful
> primary key, with perhaps constraints on the 2 columns if the combined
> uniqueness is the desired result?
> I hope I'm making sense, thanks for your thoughts! And thanks for the
> reminder about allowed nulls in the referenced columns, that could raise
> serious issues.
>
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
> > >> I came across a database created by another person in the company
that
> > >> frequently has 2 primary keys on its tables. I'm not sure why it was
> > >> done this way, my guess is to create uniqueness with the 2-field
> > >> combination.
> >
> > Since 2 primary keys are impossible in a single SQL table, I guess you
> meant
> > it to be a single key with two columns.
> >
> > >> My question is: Is this poor design?
> >
> > The number of columns in a key by itself does not tell us whether a
> specific
> > design is good or bad. There is nothing wrong in having multi-column
keys;
> > however multi-column references can occasionally mess up data integrity
> > since SQL products allow NULLs in referencing columns.
> >
> > >> Shouldn't we create constraints or indexes if 2-field uniqueness is
the
> > >> purpose.
> >
> > Not necessarily. Keys by themselves are constraints and guarantee
> uniqueness
> > any way.
> >
> > >> Is one way more efficient than the other?
> >
> > That is mostly irrelevant since at the logical level, key selection
should
> > not be based on efficiency which is determined at the implementation
> level.
> > Considering the physical implementation level, unless we consider all
> > possible query combinations, data access paths and optimization rules
and
> > test out all of them on a specific system using on a specific dataset,
we
> > cannot generalize and conclude single column keys are efficient than
> > multicolumn keys or vice-versa.
> >
> > --
> > Anith
> >
> >
>|||>> My next question: If a primary key consists of 2 columns, how would, or
>> could you, make that a foreign key in another table, and how would you
>> query such a thing?
That would be simple. Just make sure your referencing table has two columns
with compatible datatypes and you can use the REFERENCES clause in the
CREATE TABLE or ALTER TABLE DDL like:
CREATE TABLR tbl (
c1 INT NOT NULL, c2 CHAR(30) NOT NULL, ...
( c1, c2 ) REFERENCES tbl2 ( c1, c2 )
PRIMARY KEY ( c1, c2 ) ) ;
For exact syntax, please refer to SQL Server Books Online.
>> Wouldn't it be necessary to create another field in the address table,
>> maybe an identity field, and make that field the primary key?
Considering the example in your post, if the design is clean and both
columns are non-nullable they can be considered as valid choice for primary
key. ( Note that there are other desirable characteristics like stability,
minimality, simplicity and familiarity that come into play during key
selection as well )
>> Under what circumstances would a 2-column primary key be preferable to a
>> nonmeaningful primary key, with perhaps constraints on the 2 columns if
>> the combined uniqueness is the desired result?
As with many design considerations, the answer is it depends. If two columns
in the primary key can truly distinguish one entity from another, there is
no need to introduce a redundant identifier. And all the references to this
table must include the two columns and meaningful queries can be expressed
without loss of integrity.
However, multi-column keys can be often cumbersome, thereby could fail the
simplicity criteria. And NULLs in referencing columns is a serious issue in
SQL products which gives another logical reason to avoid multi-column keys
in some cases. That often gives a logical reason to use an additional
identifier which is unfamiliar, but stable, simple and minimal.
BTW, nothing prevents one from introducing artificial identifiers in all
your tables, but there are no proven advantages in doing so.
>> And thanks for the reminder about allowed nulls in the referenced
>> columns, that could raise serious issues.
Yes, it does. But neither the SQL standards nor the vendors are interested
in addressing such issues since they are mostly busy implementing UFOs :-)
--
Anith|||On Wed, 20 Oct 2004 12:14:14 -0600, Rock wrote:
>My next question: If a primary key consists of 2 columns, how would, or
>could you, make that a foreign key in another table, and how would you query
>such a thing?
>Say for instance, I have a customer table, and an address table.
>The address table fields:
>CustomerID
>AddressDescription
>Address
>Sample Data:
>CustomerID AddressDescription Address
>1 Main Office 123 Main
>Street
>1 Secondary Location 456 Main Street
>So now I want to create another table that lists, for instance, people
>located at each office. How would I set up the foreign key to point to the
>address table. Wouldn't it be necessary to create another field in the
>address table, maybe an identity field, and make that field the primary key?
Hi Rock,
That's not necessary. Your people table would look like this:
CREATE TABLE People (....,
....,
CustomerID int NOT NULL,
AddressDescription varchar(25) NOT NULL,
....,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
....
)
>And how would you query the database when joining tables? If you joined the
>address table to a query, could you base it on both the CustomerID and
>AddressDescription being equal to another field in another table. You can't
>do this, can you?
Yes, you can:
SELECT some interesting columns
FROM People
INNER JOIN Addresses
ON Addresses.CustomerID = People.CustomerID
AND Addresses.AddressDescription = People.AddressDescription
WHERE ....
> So I guess efficiency really isn't my question, maybe
>more that it's a practicality or best practice issue: Under what
>circumstances would a 2-column primary key be preferable to a nonmeaningful
>primary key, with perhaps constraints on the 2 columns if the combined
>uniqueness is the desired result?
If the addresses table used CustomerID plus a short code for the
addresstype as it's primary key, I'd just use this natural key.
In your case, the more verbose AddressDescription is used as part of the
primary key. As you can see above, this complete description would have to
be repeated each time an address is referenced. This will use up more
space in your database. I would definitely consider using a shorter key.
This key *could* be the AddressType (moving the AddressDescription to
another table, listing all AddressTypes with their description), or I
could choose to introduce a surrogate key. This will take up more space in
the addresses table itself (as you add an extra column, plus an extra
index to check uniqueness on both the surrogate key and the combination of
CustomerID / AddressDescription), but it would reduce the amount of space
needed for references. It would increase speed on joins, but it might also
introduce the need to join in an extra table on some queries.
The bottom line is: there is no fixed answer, it all depends. At the end
of the day, you'll have to weigh all options against each other and choose
the one that fits your situation best.
>I hope I'm making sense, thanks for your thoughts! And thanks for the
>reminder about allowed nulls in the referenced columns, that could raise
>serious issues.
These can be easiliy avoided. Either set a NOT NULL constraint on the
columns, or (if you must allow NULLS) add a CHECK constraint to ensure
that either both columns are NULL, or both columns are not NULL.
CREATE TABLE People (....,
....,
CustomerID int, -- NULL allowed
AddressDescription varchar(25), -- NULL allowed
....,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
CHECK ( ( CustomerID IS NULL
AND AddressDescription IS NULL)
OR ( CustomerID IS NOT NULL
AND AddressDescription IS NOT NULL))
....
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
>> These can be easiliy avoided. Either set a NOT NULL constraint on the
>> columns, or (if you must allow NULLS) add a CHECK constraint to ensure
>> that either both columns are NULL, or both columns are not NULL.
Not quite that easily. The problem is in regard to multi-column primary keys
and missing information. Relational literature has volumes about such
logical problems and the lack of theory behind handling missing information
being a serious issue.
We know SQL has only one way of dealing with missing information -- NULLs.
Since there is no mandatory requirement that all foreign key column values
should be non-nullable, SQL allows foreign key references on columns whose
values are known, unknown or partially known.
Now, consider a table with a multi-column primary key with columns, colA and
colB which is referenced by another table with corresponding multi-column
foreign key.
When the user inserts a referencing row with a value for colA and NULL for
colB, should the DBMS allow the insert? Since logically DBMS cannot preserve
the integrity of the relationship should it abort the insert? Or should it
consider the validity of the colA value alone and proceed with the insert?
What if there are duplicate values for colA in the referencing table which
cannot be evaluated due to NULLs in colB? If the colA value exists only once
should the DBMS substitute the only corresponding value for colB and proceed
with the insert ?
When deleting all the rows from the referenced table which contain a
specific value for colA, what should the DBMS do to the referencing table
that contains corresponding rows with a value for colA and a NULL for colB ?
While updating a few rows in the referenced table which contain a known colA
and colB, should the DBMS allow cascading actions on all corresponding rows
in the referencing table? Or should it evaluate the existence of rows with a
value for colA and exclude the rows with a null for colB ?
--
Anith|||On Wed, 20 Oct 2004 17:21:36 -0500, Anith Sen wrote:
>Hugo,
>> These can be easiliy avoided. Either set a NOT NULL constraint on the
>> columns, or (if you must allow NULLS) add a CHECK constraint to ensure
>> that either both columns are NULL, or both columns are not NULL.
>Not quite that easily. The problem is in regard to multi-column primary keys
>and missing information. Relational literature has volumes about such
>logical problems and the lack of theory behind handling missing information
>being a serious issue.
(snip)
>When the user inserts a referencing row with a value for colA and NULL for
>colB, should the DBMS allow the insert? Since logically DBMS cannot preserve
>the integrity of the relationship should it abort the insert? Or should it
>consider the validity of the colA value alone and proceed with the insert?
(snip more consequences of this proposed insert)
Hi Anith,
That is exactly why I suggested to either give all foreign key columns a
NOT NULL constraint, or (if NULL must be allowed) a CHECK to enforce that
they are either ALL null, or ALL not null. This will prevent all the
potential problems you describe.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Primary Keys

I came across a database created by another person in the company that
frequently has 2 primary keys on its tables. I'm not sure why it was done
this way, my guess is to create uniqueness with the 2-field combination.
My question is: Is this poor design? Shouldn't we create constraints or
indexes if 2-field uniqueness is the purpose. Is one way more efficient
than the other? Any other thoughts as to why a table would be set up this
way? Thanks.>> I came across a database created by another person in the company that[vbcol=seagreen]
Since 2 primary keys are impossible in a single SQL table, I guess you meant
it to be a single key with two columns.
[vbcol=seagreen]
The number of columns in a key by itself does not tell us whether a specific
design is good or bad. There is nothing wrong in having multi-column keys;
however multi-column references can occasionally mess up data integrity
since SQL products allow NULLs in referencing columns.
[vbcol=seagreen]
Not necessarily. Keys by themselves are constraints and guarantee uniqueness
any way.
[vbcol=seagreen]
That is mostly irrelevant since at the logical level, key selection should
not be based on efficiency which is determined at the implementation level.
Considering the physical implementation level, unless we consider all
possible query combinations, data access paths and optimization rules and
test out all of them on a specific system using on a specific dataset, we
cannot generalize and conclude single column keys are efficient than
multicolumn keys or vice-versa.
Anith|||Sorry wasn't clear, you are correct, the tables have 2 columns combined to
make a concatenated primary key. In some instances, one column is a foreign
key of another table.
My next question: If a primary key consists of 2 columns, how would, or
could you, make that a foreign key in another table, and how would you query
such a thing?
Say for instance, I have a customer table, and an address table.
The address table fields:
CustomerID
AddressDescription
Address
Sample Data:
CustomerID AddressDescription Address
1 Main Office 123 Main
Street
1 Secondary Location 456 Main Street
So now I want to create another table that lists, for instance, people
located at each office. How would I set up the foreign key to point to the
address table. Wouldn't it be necessary to create another field in the
address table, maybe an identity field, and make that field the primary key?
And how would you query the database when joining tables? If you joined the
address table to a query, could you base it on both the CustomerID and
AddressDescription being equal to another field in another table. You can't
do this, can you? So I guess efficiency really isn't my question, maybe
more that it's a practicality or best practice issue: Under what
circumstances would a 2-column primary key be preferable to a nonmeaningful
primary key, with perhaps constraints on the 2 columns if the combined
uniqueness is the desired result?
I hope I'm making sense, thanks for your thoughts! And thanks for the
reminder about allowed nulls in the referenced columns, that could raise
serious issues.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
> Since 2 primary keys are impossible in a single SQL table, I guess you
meant
> it to be a single key with two columns.
>
> The number of columns in a key by itself does not tell us whether a
specific
> design is good or bad. There is nothing wrong in having multi-column keys;
> however multi-column references can occasionally mess up data integrity
> since SQL products allow NULLs in referencing columns.
>
> Not necessarily. Keys by themselves are constraints and guarantee
uniqueness
> any way.
>
> That is mostly irrelevant since at the logical level, key selection should
> not be based on efficiency which is determined at the implementation
level.
> Considering the physical implementation level, unless we consider all
> possible query combinations, data access paths and optimization rules and
> test out all of them on a specific system using on a specific dataset, we
> cannot generalize and conclude single column keys are efficient than
> multicolumn keys or vice-versa.
> --
> Anith
>|||To avoid problems and other types of modifications to the data structures, I
would suggest switching to a surrogate key and making that the PK. You can
then create a UNIQUE index covering those two columns.
This surrogate key allows you more flexibility down the road. It's easier
to add tables, create the joins (less typing anyhow), modify the current PK
to something else etc.
HTH
Rick
"Rock" <rockisland@.yahoo.com> wrote in message
news:OLbfeCttEHA.2804@.TK2MSFTNGP14.phx.gbl...
> Sorry wasn't clear, you are correct, the tables have 2 columns combined to
> make a concatenated primary key. In some instances, one column is a
foreign
> key of another table.
> My next question: If a primary key consists of 2 columns, how would, or
> could you, make that a foreign key in another table, and how would you
query
> such a thing?
> Say for instance, I have a customer table, and an address table.
> The address table fields:
> CustomerID
> AddressDescription
> Address
> Sample Data:
> CustomerID AddressDescription Address
> 1 Main Office 123 Main
> Street
> 1 Secondary Location 456 Main Street
> So now I want to create another table that lists, for instance, people
> located at each office. How would I set up the foreign key to point to
the
> address table. Wouldn't it be necessary to create another field in the
> address table, maybe an identity field, and make that field the primary
key?
> And how would you query the database when joining tables? If you joined
the
> address table to a query, could you base it on both the CustomerID and
> AddressDescription being equal to another field in another table. You
can't
> do this, can you? So I guess efficiency really isn't my question, maybe
> more that it's a practicality or best practice issue: Under what
> circumstances would a 2-column primary key be preferable to a
nonmeaningful
> primary key, with perhaps constraints on the 2 columns if the combined
> uniqueness is the desired result?
> I hope I'm making sense, thanks for your thoughts! And thanks for the
> reminder about allowed nulls in the referenced columns, that could raise
> serious issues.
>
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
that[vbcol=seagreen]
> meant
> specific
keys;[vbcol=seagreen]
the[vbcol=seagreen]
> uniqueness
should[vbcol=seagreen]
> level.
and[vbcol=seagreen]
we[vbcol=seagreen]
>|||>> My next question: If a primary key consists of 2 columns, how would, or[vbcol=seagreen]
That would be simple. Just make sure your referencing table has two columns
with compatible datatypes and you can use the REFERENCES clause in the
CREATE TABLE or ALTER TABLE DDL like:
CREATE TABLR tbl (
c1 INT NOT NULL, c2 CHAR(30) NOT NULL, ...
( c1, c2 ) REFERENCES tbl2 ( c1, c2 )
PRIMARY KEY ( c1, c2 ) ) ;
For exact syntax, please refer to SQL Server Books Online.
[vbcol=seagreen]
Considering the example in your post, if the design is clean and both
columns are non-nullable they can be considered as valid choice for primary
key. ( Note that there are other desirable characteristics like stability,
minimality, simplicity and familiarity that come into play during key
selection as well )
[vbcol=seagreen]
As with many design considerations, the answer is it depends. If two columns
in the primary key can truly distinguish one entity from another, there is
no need to introduce a redundant identifier. And all the references to this
table must include the two columns and meaningful queries can be expressed
without loss of integrity.
However, multi-column keys can be often cumbersome, thereby could fail the
simplicity criteria. And NULLs in referencing columns is a serious issue in
SQL products which gives another logical reason to avoid multi-column keys
in some cases. That often gives a logical reason to use an additional
identifier which is unfamiliar, but stable, simple and minimal.
BTW, nothing prevents one from introducing artificial identifiers in all
your tables, but there are no proven advantages in doing so.
[vbcol=seagreen]
Yes, it does. But neither the SQL standards nor the vendors are interested
in addressing such issues since they are mostly busy implementing UFOs :-)
Anith|||On Wed, 20 Oct 2004 12:14:14 -0600, Rock wrote:

>My next question: If a primary key consists of 2 columns, how would, or
>could you, make that a foreign key in another table, and how would you quer
y
>such a thing?
>Say for instance, I have a customer table, and an address table.
>The address table fields:
>CustomerID
>AddressDescription
>Address
>Sample Data:
>CustomerID AddressDescription Address
>1 Main Office 123 Main
>Street
>1 Secondary Location 456 Main Street
>So now I want to create another table that lists, for instance, people
>located at each office. How would I set up the foreign key to point to the
>address table. Wouldn't it be necessary to create another field in the
>address table, maybe an identity field, and make that field the primary key?[/vbcol
]
Hi Rock,
That's not necessary. Your people table would look like this:
CREATE TABLE People (....,
...,
CustomerID int NOT NULL,
AddressDescription varchar(25) NOT NULL,
...,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
...
)
[vbcol=seagreen]
>And how would you query the database when joining tables? If you joined th
e
>address table to a query, could you base it on both the CustomerID and
>AddressDescription being equal to another field in another table. You can'
t
>do this, can you?
Yes, you can:
SELECT some interesting columns
FROM People
INNER JOIN Addresses
ON Addresses.CustomerID = People.CustomerID
AND Addresses.AddressDescription = People.AddressDescription
WHERE ....

> So I guess efficiency really isn't my question, maybe
>more that it's a practicality or best practice issue: Under what
>circumstances would a 2-column primary key be preferable to a nonmeaningful
>primary key, with perhaps constraints on the 2 columns if the combined
>uniqueness is the desired result?
If the addresses table used CustomerID plus a short code for the
addresstype as it's primary key, I'd just use this natural key.
In your case, the more verbose AddressDescription is used as part of the
primary key. As you can see above, this complete description would have to
be repeated each time an address is referenced. This will use up more
space in your database. I would definitely consider using a shorter key.
This key *could* be the AddressType (moving the AddressDescription to
another table, listing all AddressTypes with their description), or I
could choose to introduce a surrogate key. This will take up more space in
the addresses table itself (as you add an extra column, plus an extra
index to check uniqueness on both the surrogate key and the combination of
CustomerID / AddressDescription), but it would reduce the amount of space
needed for references. It would increase speed on joins, but it might also
introduce the need to join in an extra table on some queries.
The bottom line is: there is no fixed answer, it all depends. At the end
of the day, you'll have to weigh all options against each other and choose
the one that fits your situation best.

>I hope I'm making sense, thanks for your thoughts! And thanks for the
>reminder about allowed nulls in the referenced columns, that could raise
>serious issues.
These can be easiliy avoided. Either set a NOT NULL constraint on the
columns, or (if you must allow NULLS) add a CHECK constraint to ensure
that either both columns are NULL, or both columns are not NULL.
CREATE TABLE People (....,
...,
CustomerID int, -- NULL allowed
AddressDescription varchar(25), -- NULL allowed
...,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
CHECK ( ( CustomerID IS NULL
AND AddressDescription IS NULL)
OR ( CustomerID IS NOT NULL
AND AddressDescription IS NOT NULL))
...
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
[vbcol=seagreen]
Not quite that easily. The problem is in regard to multi-column primary keys
and missing information. Relational literature has volumes about such
logical problems and the lack of theory behind handling missing information
being a serious issue.
We know SQL has only one way of dealing with missing information -- NULLs.
Since there is no mandatory requirement that all foreign key column values
should be non-nullable, SQL allows foreign key references on columns whose
values are known, unknown or partially known.
Now, consider a table with a multi-column primary key with columns, colA and
colB which is referenced by another table with corresponding multi-column
foreign key.
When the user inserts a referencing row with a value for colA and NULL for
colB, should the DBMS allow the insert? Since logically DBMS cannot preserve
the integrity of the relationship should it abort the insert? Or should it
consider the validity of the colA value alone and proceed with the insert?
What if there are duplicate values for colA in the referencing table which
cannot be evaluated due to NULLs in colB? If the colA value exists only once
should the DBMS substitute the only corresponding value for colB and proceed
with the insert ?
When deleting all the rows from the referenced table which contain a
specific value for colA, what should the DBMS do to the referencing table
that contains corresponding rows with a value for colA and a NULL for colB ?
While updating a few rows in the referenced table which contain a known colA
and colB, should the DBMS allow cascading actions on all corresponding rows
in the referencing table? Or should it evaluate the existence of rows with a
value for colA and exclude the rows with a null for colB ?
Anith|||On Wed, 20 Oct 2004 17:21:36 -0500, Anith Sen wrote:

>Hugo,
>
>Not quite that easily. The problem is in regard to multi-column primary key
s
>and missing information. Relational literature has volumes about such
>logical problems and the lack of theory behind handling missing information
>being a serious issue.
(snip)
>When the user inserts a referencing row with a value for colA and NULL for
>colB, should the DBMS allow the insert? Since logically DBMS cannot preserv
e
>the integrity of the relationship should it abort the insert? Or should it
>consider the validity of the colA value alone and proceed with the insert?
(snip more consequences of this proposed insert)
Hi Anith,
That is exactly why I suggested to either give all foreign key columns a
NOT NULL constraint, or (if NULL must be allowed) a CHECK to enforce that
they are either ALL null, or ALL not null. This will prevent all the
potential problems you describe.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

primary key/foreign key relationship

I'm using MS SQL Server Studio Express 9.0. When selecting SQL Server Compact Edition, I'm able to create tables and primary keys with no problem. But how do I create a relationship with another table (foreign key)?

Bob K.

There is not designer support for doing this with SQL CE, so you must use:

Code Snippet

ALTER TABLE Orders ADD FK_Customer_Order FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)

Designer support for this is promised for the next version of SQL CE (3.5) - in the "Orcas" timeframe.

|||thanks Eric.
|||Eric,

is there a way to enter the above sql statement from with Studio Express?
|||Connect to the SDF file, and select File, New, New query, type the text in the query editor and press F5 to execute...|||once again, thank you.
|||uhhh... and the beat goes on...

i've entered the following sql statement:

ALTER TABLE Camera ADD FK_Format_Camera FOREIGN KEY (FormatID) REFERENCES Format(FormatID)

and received the following error:
Major Error 0x80040E14, Minor Error 25501
> ALTER TABLE Camera ADD FK_Format_Camera FOREIGN KEY (FormatID) REFERENCES Format(FormatID)
There was an error parsing the query. [ Token line number = 1,Token line offset = 41,Token in error = FOREIGN ]

i've attached a link that shows the tables and columns i'm attempting to update. i just can't see what i'm doing wrong. could someone help?

bob k.

|||the link:

http://www.rkamarowski.com/errors/error.jpg

|||

Sorry, the correct syntax is (using the sample Northwind.sdf):

Code Snippet

ALTER TABLE Orders ADD CONSTRAINT Reference FOREIGN KEY ([Customer ID]) REFERENCES Customers ([Customer ID])

|||perfect! thanks eric.
|||

Good Afternoon Erik,

Can you help me out with a project of my?! I programming for the first time in the .net cf and i create my database all manualy thought SqlCeEngine..

But i want to know if there is a better approach to do this by the "next, next theory" (visuali) . if i create the database using a GUI of sql serv mobile edition, how can i choose where this database will be created and how can i indicate to the datasource in the connection string of an sqlConnection where is my database?! can you help me? i have a checkpoint day 17 to present my work and i would like to finish the hole project will that date to get some time to another project that is coming up, so its kind of urgent subject.

Thank you anyway!

Primary key vs Clustered Index with respect to Replication.

Is the following statement is TRUE.
Primary Key will allow tables to participate in replication
whereas Clustered Index will not allow tables to participate in replication.
I want to double check the above statement is valid
I created two tables with primary and clustered key.
create table tab1
(
col1 int primary key
, col2 int
)
create table tab2
(
col1 int ,
col2 int
)
CREATE UNIQUE CLUSTERED INDEX tab2_ind
ON tab2 (col1)
sp_help tab1
sp_help tab2
Few Observations
NULLABLE
Primary Key NO
Clustered Index YES
Col Constraint.
Primary Key YES
Clustered Index NO
Index
Primary Key clustered, unique
Clustered Index clustered, unique, primary key
For the Primary Key, A Constraint is created with the following values
constraint_type PRIMARY KEY (clustered)
constraint_name PK__tab1__486E7AE7
delete_action (n/a)
update_action (n/a)
status_enabled (n/a)
status_for_replication (n/a)
constraint_keys col1
In the above status_for_replication column value is (N/A)
I think Primary Key does not have any impact on replication.
Since I dont have any constraint for the Clustered Index
I think Clustered Index does not have any impact on replication.
Therefore I think the following statement is FALSE.
Primary Key will allow tables to participate in replication
whereas Clustered Index will not allow tables to participate in replication.
Irrespective of Primary Key or Clustered Index both tables will
participate in replication. Is it correct
Please throw some light on this issue.
Thanks in Advance
Rajesh Peddireddyi dont think there is anything to do with replication.
but what i see is, this has something to do with Referential integrity.
column in parent table should be a primary key
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Rajesh" wrote:

> Is the following statement is TRUE.
> Primary Key will allow tables to participate in replication
> whereas Clustered Index will not allow tables to participate in replicatio
n.
> I want to double check the above statement is valid
> I created two tables with primary and clustered key.
> create table tab1
> (
> col1 int primary key
> , col2 int
> )
>
> create table tab2
> (
> col1 int ,
> col2 int
> )
>
> CREATE UNIQUE CLUSTERED INDEX tab2_ind
> ON tab2 (col1)
> sp_help tab1
> sp_help tab2
> Few Observations
> NULLABLE
> Primary Key NO
> Clustered Index YES
> Col Constraint.
> Primary Key YES
> Clustered Index NO
>
> Index
> Primary Key clustered, unique
> Clustered Index clustered, unique, primary key
> For the Primary Key, A Constraint is created with the following values
> constraint_type PRIMARY KEY (clustered)
> constraint_name PK__tab1__486E7AE7
> delete_action (n/a)
> update_action (n/a)
> status_enabled (n/a)
> status_for_replication (n/a)
> constraint_keys col1
> In the above status_for_replication column value is (N/A)
> I think Primary Key does not have any impact on replication.
> Since I dont have any constraint for the Clustered Index
> I think Clustered Index does not have any impact on replication.
>
> Therefore I think the following statement is FALSE.
> Primary Key will allow tables to participate in replication
> whereas Clustered Index will not allow tables to participate in replicatio
n.
> Irrespective of Primary Key or Clustered Index both tables will
> participate in replication. Is it correct
> Please throw some light on this issue.
> Thanks in Advance
> Rajesh Peddireddy|||On Wed, 10 Aug 2005 11:49:03 -0700, Rajesh
<Rajesh@.discussions.microsoft.com> wrote:
>Is the following statement is TRUE.
>Primary Key will allow tables to participate in replication
Transactional, true.
For Merge, either the PK or another unique index are GUIDs.
>whereas Clustered Index will not allow tables to participate in replication.[/color
]
False. Replication doesn't care about cluster, just about PK and/or
GUID.
J.

Primary key question

Hi,

I am new to SQL 2000 and scratching my head...I am importing text files to SQL 2000 tables. How should I define the primary key in a table if the text file is :

Example 'customer invoice file'
column 1-Customer Bus Unit (Char 3)
column 2-Batch number (Char 6)
column 3-Document type (Char 1)
column 4-Invoice number (char 15)
column 5-Invoice dollar (Money)
column 6 - due date (date)
column 7 - customer name (char 35)
column 8 - customer address (char 50)

The primary key of this file is column1 + column 2 + coumn 3 + column 4. That concatnation of 4 fields make each record unique.
Should I define all 4 feilds as the primary key field? Thank you, Yanoroouh...yes...

Do you have a staging environemnt?

You can create work tables with no constraints...that way you can audit the data before it hit the final destination...|||Originally posted by Brett Kaiser
uh...yes...

Do you have a staging environemnt?

You can create work tables with no constraints...that way you can audit the data before it hit the final destination...

Please bear with me...
What do you mean a staging environment?
Could you suggest a work table design? (example)|||Sure...it's a table that looks like your final table...just that it has no keys, indexes or other constraints...even make all of the columns varchar (dates, money, ect)

You can the audit the data

LIKE

SELECT * FROM myTable99 WHERE ISDATE(adatecolumn) = 0

Will show you all rows with bad dates

SELECT * FROM myTable99 WHERE ISNUMERIC(anumericcolumn) = 0

Will show you all rows with non numbers in the expected number columns

PK violations

SELECT col1, col2, col3, col4, count(*)
FROM mytable99
GROUP BY col1, col2, col3, col4
HAVING COUNT(*) > 1

Shows where you'll have a dup key

ect

You can even make sure RI is ok...

If the data passes your audit, you can either bcp/dts or bulk insert the file, or Just INSERT it from your stage table...

Clear as mud?

Let me know...|||Okay, I will try that. Thank you very much.
I may encounter another questions... I will let you know.
Yanoroo|||Brett, are you still checking?
Not only the staging the work, I tried your PK violation check and it worked beautifully.
I appriciate your help. Yanoroo|||Hey....Merry Holiday

I'm glad it worked for you...It's always better to know that you're working with clean data...

TRUST NO ONE bearing Excel spreadsheets...(or other data sources outside your environment)..
Better still require data files with header and/or trailers...and if you can only get one...demand a trailer...with record counts and possible the sum of int/decimal columns...

Primary Key Non-Cluster Index

Is there a case when the primary key should not be cluster index?
I have a SQL Server 2000 database that has several tables that have
non-cluster indexes for primary keys. Will this slow down the performance of
application that uses this table often?
Thank You,
Joe,
1. Yes. When the clustered index is better utilized on a different column
to support range and sorting queries.
2. It depends - on the type of queries that are being used in the
application.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
|||On Tue, 25 Oct 2005 11:16:11 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>Is there a case when the primary key should not be cluster index?
>I have a SQL Server 2000 database that has several tables that have
>non-cluster indexes for primary keys. Will this slow down the performance of
>application that uses this table often?
If there is NO clustered index on a table, and if you delete rows
(including if you update rows and because they need more space they
have to move), then you may wake up the (potentially evil) SQLServer
ghost.
Otherwise, whether a clustered index is going to help, or hurt, or do
nothing is highly dependent on application logic.
Josh
|||"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
Of course. Keep in mind that a clustered index sorts the actual data rows
in index order. It is most often used when you are selecting on a range of
data like "OrderNumber Between 10 and 100", or you are sorting your data in
clustered index order.
If you are doing a lot of INSERTs into the table, the data must be placed in
clustered index order. If you are using a monotonically increasing value
(like IDENTITY), then there will always be space at the end of the last
datapage for your next row of data and page splitting doesn't occur. If you
are not using a monotonically increasing value, for example, LastName or a
GUID, then the database will have to make space for the new row if there is
not space already. This can cause page splitting which is time consuming
and resource consuming.
The performance of other applications depends on what those applications are
querying for.
Rick Sawtell
MCT, MCSD, MCDBA
sql

Primary Key Non-Cluster Index

Is there a case when the primary key should not be cluster index?
I have a SQL Server 2000 database that has several tables that have
non-cluster indexes for primary keys. Will this slow down the performance o
f
application that uses this table often?
Thank You,Joe,
1. Yes. When the clustered index is better utilized on a different column
to support range and sorting queries.
2. It depends - on the type of queries that are being used in the
application.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,|||On Tue, 25 Oct 2005 11:16:11 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>Is there a case when the primary key should not be cluster index?
>I have a SQL Server 2000 database that has several tables that have
>non-cluster indexes for primary keys. Will this slow down the performance
of
>application that uses this table often?
If there is NO clustered index on a table, and if you delete rows
(including if you update rows and because they need more space they
have to move), then you may wake up the (potentially evil) SQLServer
ghost.
Otherwise, whether a clustered index is going to help, or hurt, or do
nothing is highly dependent on application logic.
Josh|||"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
Of course. Keep in mind that a clustered index sorts the actual data rows
in index order. It is most often used when you are selecting on a range of
data like "OrderNumber Between 10 and 100", or you are sorting your data in
clustered index order.
If you are doing a lot of INSERTs into the table, the data must be placed in
clustered index order. If you are using a monotonically increasing value
(like IDENTITY), then there will always be space at the end of the last
datapage for your next row of data and page splitting doesn't occur. If you
are not using a monotonically increasing value, for example, LastName or a
GUID, then the database will have to make space for the new row if there is
not space already. This can cause page splitting which is time consuming
and resource consuming.
The performance of other applications depends on what those applications are
querying for.
Rick Sawtell
MCT, MCSD, MCDBA

Primary Key Non-Cluster Index

Is there a case when the primary key should not be cluster index?
I have a SQL Server 2000 database that has several tables that have
non-cluster indexes for primary keys. Will this slow down the performance of
application that uses this table often?
Thank You,Joe,
1. Yes. When the clustered index is better utilized on a different column
to support range and sorting queries.
2. It depends - on the type of queries that are being used in the
application.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,|||On Tue, 25 Oct 2005 11:16:11 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>Is there a case when the primary key should not be cluster index?
>I have a SQL Server 2000 database that has several tables that have
>non-cluster indexes for primary keys. Will this slow down the performance of
>application that uses this table often?
If there is NO clustered index on a table, and if you delete rows
(including if you update rows and because they need more space they
have to move), then you may wake up the (potentially evil) SQLServer
ghost.
Otherwise, whether a clustered index is going to help, or hurt, or do
nothing is highly dependent on application logic.
Josh|||"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
Of course. Keep in mind that a clustered index sorts the actual data rows
in index order. It is most often used when you are selecting on a range of
data like "OrderNumber Between 10 and 100", or you are sorting your data in
clustered index order.
If you are doing a lot of INSERTs into the table, the data must be placed in
clustered index order. If you are using a monotonically increasing value
(like IDENTITY), then there will always be space at the end of the last
datapage for your next row of data and page splitting doesn't occur. If you
are not using a monotonically increasing value, for example, LastName or a
GUID, then the database will have to make space for the new row if there is
not space already. This can cause page splitting which is time consuming
and resource consuming.
The performance of other applications depends on what those applications are
querying for.
Rick Sawtell
MCT, MCSD, MCDBA

Primary Key in Transactional Replication

How I can publish tables wihout primary key in transactiona Replication?
Dave,
you can't If at all possible, just add the PK.
As a workaround, if schema changes are not allowed, I suppose you could
create an indexed view and transactionally replicate this, but this is not a
nice solution.
Equally not-nice is to use a trigger to synchronize another table (which
itself has a PK) and replicate that.
Finally, you could use snapshot or merge replication (EXCHANGETYPE to force
downloads only), but these may not be appropriate due to their nature of
replication.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Primary Key Help

Hi am fairly new to VS and very new to Access. I am managing somewhat. I have access to the database, I get inside my tables and I can change what needs to be changed. However I want to know how to work with primary keys. As of right now I am using 4 tables in my app. If i run my program all of the tables get filled in just fine. However if I run it again, none of the tables get updated because I have a primary key on a few of the tables columns.

Dim ADOConn As ADODB.Connection

Dim recSet As ADODB.Recordset

ADOConn = New ADODB.Connection

recSet = New ADODB.Recordset

ADOConn.Provider = "Microsoft.Jet.OLEDB.4.0"

ADOConn.ConnectionString = "C:\Documents and Settings\christopher.cornell\Desktop\MyDB.mdb"

ADOConn.Open()

recSet.Open("select * from Table1", ADOConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

recSet.AddNew("Column1", textbox1.text) 'Primary key on this. When i try to add the same thing over

'no other tables will get updated.

recSet.Update()

recSet.Close()

The first time through will be just fine, however if I run it again it wont update the rest of my tables. It seems to run everything (stepping through the debugger) but it is not reflected in the tables. I hope what I am trying to do makes sense.

Hi Chris,

The issue here is that a primary key is what is referred to as a unique constraint. There can not be any duplicate values for the primary key column. The primary key is defined in this manner so that the database engine knows how to locate the record you are looking for. Think of this as the row name for a table. Very similar to an excel spreadsheet. If I were to give you a spreadsheet with no row numbers on it and asked you to find a specific row you would have to scan all the rows in the sheet to find what I am asking you for. If you have a primary key (usually a number) and I ask you for row #456 and it is garunteed unique you can find it and once you found it you can stop looking because you know there is no other row #456.

So, you need to make sure your values you insert for your primary key are unique. Often times you will need to define multiple columns as your primary key if a combination of values makes a row or record unique.

Hope this helps you.

-David Sandor

|||

OK, that makes sense. But for instance lets say that I only want to have this one specific entry in my table. I am parsing a file to get the info for my table. So if I come to this value again and try to insert it, it obviously will not be allowed. However after it trys that, nothing else will be inserted into any of the other tables. So for table1 I have 2 columns. The first column has a primary key. Then I have table 2 with 4 columns, I do not have a primary key because I do not mind that records are the same. But when I parse my file for insertion into the tables, it will try the first table and since the entry is already in there it will not put anything else in the second table. It seems to finish executing the code however. I was thinking I may have needed an if that checks if its alredy in there, but I could not get that to work either.

I am guess this problem is caused by the primary key thing, because when I take it off, everything works fine. Thank you very much for the reply.

Chris

I still need help with the above issue. However I think that If I get help with this problem I will be able to figure it out myself. I am trying to find something in the database. This is what I am doing.

recSet.Open("select * from Table", ADOConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

recSet.Find("[Name] = '" & temp(i) & "'", 0, ADODB.SearchDirectionEnum.adSearchForward, 1)

If Not recSet.Fields("Name").Value = temp(i) Then

recSet.AddNew("Name", Apps(i))

recSet.Update()

recSet.Close()

End If

This is how I have seen to do things on other sites however I can not get this to work. When I try to do the find, all I am getting is the very first entry so I assume I am doing something wrong. Any help would be greatly appreciated. Thanks.