Determine Table's PK Columns

I want to do a query on a db's system tables to determine what column(s) are
part of the primary key.
Example.
Code:
SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
sysobjects.name='tablename'
Yields all the column names. But I just want to see those that are part of
the primary key. How?
Ryan
> SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
> sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
> sysobjects.name='tablename'
shuldn't you use information_schema?
.~. Might, Courage, Vision. Sincerity. http://www.linux-sxs.org
/ v \
/( _ )\ (Ubuntu 5.10) Linux 2.6.14.3
^ ^ 22:37:07 up 9 days 2:31 load average: 3.12 1.63 0.80|||Try:
select
*
from
information_schema.constraint_column_usage
where
table_name = 'Order Details'
and objectproperty (object_id (constraint_name), 'IsPrimaryKey') = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:037F8848-C843-4A77-B385-6994E6235CDA@.microsoft.com...
>I want to do a query on a db's system tables to determine what column(s)
>are
> part of the primary key.
> Example.
> Code:
> SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
> sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
> sysobjects.name='tablename'
> Yields all the column names. But I just want to see those that are part of
> the primary key. How?
> Ryan|||Thank you both of you. Yes, Man-wai Chang, the information_schema looks lik
e
it's a lot easier than joining the system tables like I was doing. Thank
you, both.

determin name of primary column

given the name of any table how can i programmatically get the name of the primary key column?

You can use the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view like:

SELECT tc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc

WHERE tc.TABLE_SCHEMA = 'dbo'

and tc.TABLE_NAME = '<your_table>'

and tc.CONSTRAINT_TYPE = 'PRIMARY KEY';

sql

Designing Primary Key and Clustered Index and Performance

I have several tables where the clustered index (the
physical way the data is stored) is different from the
Primary Key (which is just a unique number). It seems to
me that this will help me the most, as the clustered
index supports my SELECT statements, and the Primary Key
column will support my UPDATE and DELETE statements. I
am very new to SQL Server. My question is this. Is
designing the tables this way ok to do, or is this not
how it should be done?
Second part of my question is, If what I am doing is
fine, then does it make a difference (performance wise)
if I have the Primary Key column the first column table
or about the seventh column in?
Thanks so much for your help.Depends on who you ask. Zealots will say that you should never, ever use an
arbitrary unique integer as a row identifier. I disagree. However,
whenever possible, even if you are using a unique identifier you should
attempt to set a 'natural' primary key based on uniqueness in your data, and
use this for your primary key rather than the row identifier. You can still
use the ID to make life simpler (e.g. passing back lists of rows to client
applications for singleton selection), but the primary key will help
maintain and validate the table's data.
As for location within the column list, it makes no difference where
anything is. Don't rely on the ordering of your column list. Always
specify explicit column lists in the order you want them for selects and
inserts.
"Nancy" <anonymous@.discussions.microsoft.com> wrote in message
news:052b01c3aee5$09ea4e00$a301280a@.phx.gbl...
> I have several tables where the clustered index (the
> physical way the data is stored) is different from the
> Primary Key (which is just a unique number). It seems to
> me that this will help me the most, as the clustered
> index supports my SELECT statements, and the Primary Key
> column will support my UPDATE and DELETE statements. I
> am very new to SQL Server. My question is this. Is
> designing the tables this way ok to do, or is this not
> how it should be done?
> Second part of my question is, If what I am doing is
> fine, then does it make a difference (performance wise)
> if I have the Primary Key column the first column table
> or about the seventh column in?
> Thanks so much for your help.|||Placing the clustered index on a column other than the primary key is often
wise.
You only get ONE clustered index per table so use it wisely (Which is what I
believe you are doing).
If you are new to SQL Server, welcome to the community.
I recommend starting with these 3 books:
1. Inside SQL Server 2000 by Kalen Delaney
2. Professional SQL Server 2000 Programming By Robert Vieira
3. Transact-SQL Programming by Kline
Cheers
Greg Jackson
Portland, OR

Designating an identity column when you already have primary key

Hello --

Following normal practice, I have an autoincrementing identity column designated as primary key in my table. I have two other columns that should also contain unique values per record, but the Identity option is greyed out (in Management Studio) for all columns other than the primary key.

I'm enforcing this programmatically (in my C# code) at this point, but I'd like to back that up with a constraint in the database itself.

Any help is appreciated.

Eric

Hello my friend,

Turn Allow Nulls off for these other 2 columns. In addition to this, here is some SQL you can run at any time to check for duplicate values: -

SELECT ErrorTypeID, COUNT(*) AS Counter
FROM TBLERROR
GROUP BY ErrorTypeID
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Kind regards

Scotty

|||tblErrror is a custom table in my database. Substitute it for your table and the field you want to check|||

Thanks, Scotty.

Is there any way to apply manual constraints so the duplicate value doesn't end up in the table in the first place, or would I instead have to insert new rows through a stored procedure and apply the check/abort in there?

|||

Hello again my friend,

Put this index on your table: -

CREATE UNIQUE INDEX MyIndexName ON MyTableName (MyFieldName)

If there are duplicates already, the above statement will fail. If there are not, it will work and if someone, or some code, tries to insert a duplicate, the record will not be inserted/updated and an error will result.

Kind regards

Scotty

|||

If you want to remove an index you have added, do the following: -

DROP INDEX MyTableName.MyIndexName

Kind regards

Scotty

|||

EricLaszlo:

Hello --

Following normal practice, I have an autoincrementing identity column designated as primary key in my table. I have two other columns that should also contain unique values per record, but the Identity option is greyed out (in Management Studio) for all columns other than the primary key.

I'm enforcing this programmatically (in my C# code) at this point, but I'd like to back that up with a constraint in the database itself.

Any help is appreciated.

Eric

The IDENTITY property can generate auto numbers for your column however it does not guarantee uniqueness because IDENTITY can generate gaps, so Microsoft recommend you use GUID instead. But you cannot use GUID because you have three columns that need to be Unique so that leave only one option Unique constraint because you can define more than one Unique constraint in a table. I have seen table definitions with many constraints so you could run some tests in relation to ADO.NET as needed for your application. Try the link below for details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms191166.aspx

|||

Thank you both very much for your input.

Based on that, I was able to apply the contstraints with the following syntax:

ALTER

TABLE Profiles

ADD

CONSTRAINT ncEmail

UNIQUE

NONCLUSTERED(Email)

Having read a little about clustered indices in my travels, I tried to apply one to my Id column - didn't notice that this column already had a clustered index - not sure if that was from designating is as and Identity column or applying primary key.

In any case, I think I'm good for now...

Design: multiple columns for primary key

Hi,
A very basic question:
Imagine I have a table with multiple fields, some strings, some integers
etc.
None of these fields are unique on their own, but all fields together is
unique per record and thus can be used as the primary key.
However, referring to a record based on all these columns is quite
cumbersome (and not efficient, or does indexing take care of this nearly
entirely compared to a single identity field?), so how is this situation
best solved in practice.
Do I use an ugly IDENTITY column just for 'convenience' or continue to use
all fields, resulting in huge queries that lose legibility, or is there
another way?
LisaLisa Pearlson wrote:

> Do I use an ugly IDENTITY column just for 'convenience' or continue
> to use all fields, resulting in huge queries that lose legibility, or
> is there another way?
I would use "an ugle IDENTITY column" without doubt! Maybe others have
different opinions. It'll make your life a less easier. Otherwise if
you want to refer to that row in a foreign key you need to include all
the columns of the key which isn't really doable.
Kind regards,
Stijn Verrept.|||Lisa Pearlson (no@.spam.plz) writes:
> A very basic question:
> Imagine I have a table with multiple fields, some strings, some integers
> etc.
> None of these fields are unique on their own, but all fields together is
> unique per record and thus can be used as the primary key.
> However, referring to a record based on all these columns is quite
> cumbersome (and not efficient, or does indexing take care of this nearly
> entirely compared to a single identity field?), so how is this situation
> best solved in practice.
> Do I use an ugly IDENTITY column just for 'convenience' or continue to use
> all fields, resulting in huge queries that lose legibility, or is there
> another way?
It doesn't have to be IDENTITY, you can roll your own as well. But judging
from the shallow description you give, it appears that this could be a
solution. But you should add UNIQUE constraint on the other columns as well
to ensure their uniqueness.
I like to point out that adding a surrogate key does not always make things
simpler. I had a table with a four-column key, and then I needed to add a
sub-table with two more keys. Since a six-column key sounded too much, I
added a surrogate key to the main table. Years later I had reason to
write code to maintain these tables. Turned out that the surrogate key
made this a whole lot more complex. So the next time I had revise those
tables, I removed the surrogate key. (I was also able to remove one the
columns in the four-column key, and one of the keys in the sub-table.)
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|||Using the terms "field" and "record" will bring down the wrath of Celko!
I use IDENTITY for a number of reasons, and none of them are for
convenience:
(1) An IDENTITY value is immutable.
(2) Cascading updates are not required to maintain integrity.
(3) IDENTITY reduces redundancy.
(4) Joins generally perform better on IDENTITY values.
(5) IDENTITY reduces lock contention.
There are other reasons, and I could expound further on the merits of each
of the above.
It is not unusual in a logical database design to have tables with compound
primary keys; however, when it comes time to implement the design,
surrogates--including IDENTITY--make a lot more sense.
One more thing: if you do use an IDENTITY PRIMARY KEY, be sure to create a
UNIQUE constraint or index on the combination of columns that are together
unique per row. Alternate keys should be enforced by the database with a
UNIQUE constraint.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23hOXsSz9FHA.3928@.TK2MSFTNGP11.phx.gbl...
> Hi,
> A very basic question:
> Imagine I have a table with multiple fields, some strings, some integers
> etc.
> None of these fields are unique on their own, but all fields together is
> unique per record and thus can be used as the primary key.
> However, referring to a record based on all these columns is quite
> cumbersome (and not efficient, or does indexing take care of this nearly
> entirely compared to a single identity field?), so how is this situation
> best solved in practice.
> Do I use an ugly IDENTITY column just for 'convenience' or continue to use
> all fields, resulting in huge queries that lose legibility, or is there
> another way?
> Lisa
>|||I understand 3, 4 and take 5 for granted (some internal DBMS matter I
presume), but could you elaborate a bit on 1 and 2?
1) immutable means you can't do UPDATE MyTable SET identcol=123 WHERE
identcol=456 ?
2) What does cascading have to do with it? (as I understand cascading, it's
like triggers where change in one record triggers changes in other
tables/records?)
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:OIHDWpz9FHA.3168@.TK2MSFTNGP10.phx.gbl...
> Using the terms "field" and "record" will bring down the wrath of Celko!
> I use IDENTITY for a number of reasons, and none of them are for
> convenience:
> (1) An IDENTITY value is immutable.
> (2) Cascading updates are not required to maintain integrity.
> (3) IDENTITY reduces redundancy.
> (4) Joins generally perform better on IDENTITY values.
> (5) IDENTITY reduces lock contention.
> There are other reasons, and I could expound further on the merits of each
> of the above.
> It is not unusual in a logical database design to have tables with
> compound primary keys; however, when it comes time to implement the
> design, surrogates--including IDENTITY--make a lot more sense.
> One more thing: if you do use an IDENTITY PRIMARY KEY, be sure to create a
> UNIQUE constraint or index on the combination of columns that are together
> unique per row. Alternate keys should be enforced by the database with a
> UNIQUE constraint.
>
> "Lisa Pearlson" <no@.spam.plz> wrote in message
> news:%23hOXsSz9FHA.3928@.TK2MSFTNGP11.phx.gbl...
>|||Lisa Pearlson (no@.spam.plz) writes:
> I understand 3, 4 and take 5 for granted (some internal DBMS matter I
> presume), but could you elaborate a bit on 1 and 2?
> 1) immutable means you can't do UPDATE MyTable SET identcol=123 WHERE
> identcol=456 ?
> 2) What does cascading have to do with it? (as I understand cascading,
> it's like triggers where change in one record triggers changes in other
> tables/records?)
Actually, I think of the reasons that Brian listed, only 5 is really
applicable to IDENTITY columns, although 1 has a touch to it: if you
have an IDENTITY property on the key, you know that the value cannot
be updated, not even by mistake.
Points 2-4 applies to surrogate keys in general, no matter if they have
IDENTITY or not.
I guess what Brian means with cascading is that if you use entirely
natural keys these can change. Say that you get the idea to use the
stock symbol as the key for financial instruments. Then the company changes
the name, and gets a new symbol. With the symbol as key, you have to update
all tables where the symbol appears. With a surrogate key, there is no
need to.
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|||Immutable primary keys yield many benefits.
A mutable primary key will make your code less stable. It's possible in a
concurrent environment to read a row with a particular primary key value and
while you're working on it for another transaction or transactions to make
changes so that when you initiate your update, it appears that the row no
longer exists, or even worse, that the row exists even though it's really
another row. Here's a simple example: Assume that you're tracking a part
as it moves from machine to machine on an assembly line. Assume also that
only one part can be manipulated by one machine at one time. The mutable
primary key is this example is {PartNo, Location}. Now, you read the row
with key {'45G', 'PRESS01'} prior to some manipulation. While you're
working on the row, the part is moved from 'PRESS01' to 'FORMER01', so it
now appears to you that the row no longer exists. If another part '45G' is
moved onto 'PRESS01' prior to your update, then it will appear to you that
the row still exists even though it's a different part, and you may
erroneously update the row for the wrong part. To counter this, you must
either lock and hold the row when you read it (not a very attractive
prospect because it will severely reduce concurrency and will preclude the
use of disconnected datasets, message queues, etc.), or write a ton of code
on the client end to detect the change--which may not always be possible or
practical. Note that this problem increases in complexity when there are
related tables, because it's possible for a row to look the same on the
primary key table, but to actually refer to a different row with a different
set of related rows. With an IDENTITY primary key, as each part is placed
in production a new row is added and a new IDENTITY value is generated.
Because that value cannot change, when you go to perform your update, you
can determine not only that the part has moved, but exactly where it is now.
In addition, the problem with related tables cannot occur, because the
related rows refer to a value that cannot change.
Another problem lies with UPDATE triggers that are used for auditing or to
implement transition constraints. SQL Server update triggers have two
pseudotables, deleted and inserted, which contain the old and new values for
each row that was updated. When an update affects more than one row, there
is no supported mechanism to determine which row in the inserted pseudotable
corresponds to each row in the deleted pseudotable. (Oracle has a FOR EACH
ROW trigger, which I've been begging Microsoft to implement.) IDENTITY
solves this problem because since the key cannot change, you can join the
deleted and inseted pseudotables on the IDENTITY column and determine
exactly what happened to each row.
Cascading updates cause more problems than they're worth.
The most common form of avoidable deadlock is caused by multiple
transactions obtaining and holding locks on rows in more than one table in a
different order. To combat this, you must make sure that you obtain locks
on tables in the same order in every procedure, function, trigger, and
batch. Cascading updates throw a wrench into this. There is no way to
determine with any degree of certainty the order in which related rows will
be locked when there is a cascading heirarchy present. This makes it much
more difficult--if not impossible--to determine a locking order that will
eliminate avoidable deadlocks.
Another problem with cascading updates is that whenever a change is made,
the rowversion (timestamp) on each affected row is updated. Do you really
want to indicate that a Sales Order has been changed when only the
salesperson's employee number has been changed? It makes sense to indicate
a change when the Sales Order is assigned to another salesperson, but in
this case the change is cosmetic, not material, and in my opinion should not
occur.
This brings up another problem: triggers on every affected table throughout
the cascading heirarchy also fire. It's much more difficult to determine
whether they fired for simply a cosmetic change, or if there is a material
change that should be validated against business rules.
All of these problems occur because a database that uses natural primary
keys is riddled with redundancy. For this reason among others, I advocate
the use of surrogate keys and in particular, IDENTITY at the physical level.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23nPAi%2309FHA.3884@.TK2MSFTNGP10.phx.gbl...
>I understand 3, 4 and take 5 for granted (some internal DBMS matter I
>presume), but could you elaborate a bit on 1 and 2?
> 1) immutable means you can't do UPDATE MyTable SET identcol=123 WHERE
> identcol=456 ?
> 2) What does cascading have to do with it? (as I understand cascading,
> it's like triggers where change in one record triggers changes in other
> tables/records?)
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OIHDWpz9FHA.3168@.TK2MSFTNGP10.phx.gbl...
>|||>> Let's get back to the basics of an RDBMS. Rows are not records; fields ar
e not columns; tables are not files.
If (a,b,c) is a key in the data model, then you have to make it a key
in the schema. Well, you want things screwed.
Ignoring that BY DEFINITION, the proprietary IDENTITY is not a data
type, not a relational and not verifiable, it is redundant in the face
of a real key.
How do you guarantee that you have the IDENTITY and the real key in
synch? Most "ID-iots" do not bother with a real key (they mimic a
sequential file and pointer chains instead) and get redundant rows when
someone posts the same data multiple times.
Answer: it is impossible and therefore data integrity is impossible. I
am just starting to do some SOX consulting work; I will flunk your
database for this. There was some discussion of this at CA a few ws
ago.
A good RDBMS will handle the access for you, so that you do not have to
drop down to that level.|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Ignoring that BY DEFINITION, the proprietary IDENTITY is not a data
> type, not a relational and not verifiable, it is redundant in the face
> of a real key.
New readers should note that this is Joe Celko's private definition, and
thus nothing to bother about.
I could say that by definition Joe Celko is always wrong, but that would
not be a very strong argument. A much stronger argument is that far too many
of his posts consists of inaccurate standard rants, and where the main
rationale for the post is to insult the person who asked.

> How do you guarantee that you have the IDENTITY and the real key in
> synch?
In Lisa's case, it appears to be simple: she should have a UNIQUE
cosntraints on the real key. However, there are plentyful of cases where
there is no real key, or where any "real key" is far beyond being
practically usable.
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|||So, DB2, Oracle, Sybase and Microsoft SQL Server are not 'good RDBMS'? Seems
the market disagrees.
The whole point about using a surrogate key with the IDENTITY property is so
that you gain better performance, reduce complexity for backups, security
etc... as well as being able to change your natural key without having to
build a horrendous transaction of multiple update statements that will lock
up your tables while its being done - usually people cluster on the primary
key (natural key).
People only get redundant rows if they forget to add constraints to their
table, the surrogate key definition would be,..
create table individual (
id int not null identity constraint sk_individual unique
clustered,
last_name varchar(50) not null,
first_name varchar(50) not null,
dob int not null,
constraint pk_individual primary key nonclustered( last_name,
first_name, dob )
)
Mind you, we both know that the natural key on individual isn't the above,
in fact their isn't an easy one - consider a user group like my own, do you
want me to ask people for the NI (social security) number ? I wouldn't get
many members, I'd probably not even get my registration under the Data
Protection act here in the UK passed either.
Well fud - go out and get some bloody experience on real systems please!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133661996.695550.257580@.o13g2000cwo.googlegroups.com...
> If (a,b,c) is a key in the data model, then you have to make it a key
> in the schema. Well, you want things screwed.
> Ignoring that BY DEFINITION, the proprietary IDENTITY is not a data
> type, not a relational and not verifiable, it is redundant in the face
> of a real key.
> How do you guarantee that you have the IDENTITY and the real key in
> synch? Most "ID-iots" do not bother with a real key (they mimic a
> sequential file and pointer chains instead) and get redundant rows when
> someone posts the same data multiple times.
> Answer: it is impossible and therefore data integrity is impossible. I
> am just starting to do some SOX consulting work; I will flunk your
> database for this. There was some discussion of this at CA a few ws
> ago.
> A good RDBMS will handle the access for you, so that you do not have to
> drop down to that level.
>

Design question SQL Server 2005

I am very new to SQL Server 2005. I have a database of criminal records consisting of a master table of names where the primary key is a case number and 3 related minor tables: violations, charges, and appeals. They are linked by the case number with a one-to-many relationship. Not all of these tables have a record for every master record.

I am designing a simple lookup program in VB 2005 in which info from all tables will be on one screen. A search will be done on either name or case number.

What is the best way so set up views and/or stored procs for such a program? Do I need a stored proc for each minor table or is there a way to set up one proc to pull all of the info? Did I mention I was new at this? I have worked a lot with Access and recognize just enough things in SQL Server to be really confused.

Thanks for the help!

To get you started, you can get all the info with a single query. eg if we assume case number:

SELECT m.col1, v.col1, c.col1, a.col1
FROM masterTable
LEFT JOIN violations v
ON m.caseid = v.caseid
LEFT JOIN charges c
ON m.caseid = c.caseid
LEFT JOIN appeals a
ON m.caseid = a.caseid
WHERE m.caseid = @.caseid

Depending on how your app should work, you may not want to display all details if name is serached for, but rather use name as a way to look up the case number first, then go query for the details.

/Kenneth

Design Question - Primary Keys

Hello All,
I am in the midst of designing a database, and I have a very general
(pseudo-newbie) question.
Should all tables have a Primary Key defined?
As I understand it, there are two reasons to have a primary key:
- It is used in conjunction with a Foreign Key on a table, and
- It automatically creates an Index on a table
I would guess that most tables fit one or both of the criteria listed above,
but is it good practice for all tables, or is it unneeded overhead? For
instance, I will have a few tables that are small (maybe 50 rows, max) and
generally fixed (the data is permanent, or nearly permanent, and won't
change), and probably won't be accessed in a multi-table query.
Thanks,
pagates"pagates" <pagates@.discussions.microsoft.com> wrote in message
news:5F8D4C00-28F8-4DE9-884D-7300F7469329@.microsoft.com...
> Hello All,
> I am in the midst of designing a database, and I have a very general
> (pseudo-newbie) question.
> Should all tables have a Primary Key defined?
> As I understand it, there are two reasons to have a primary key:
> - It is used in conjunction with a Foreign Key on a table, and
> - It automatically creates an Index on a table
> I would guess that most tables fit one or both of the criteria listed
> above,
> but is it good practice for all tables, or is it unneeded overhead? For
> instance, I will have a few tables that are small (maybe 50 rows, max) and
> generally fixed (the data is permanent, or nearly permanent, and won't
> change), and probably won't be accessed in a multi-table query.
> Thanks,
> pagates
You forgot the most important reason.
...whose values uniquely identify each row in the table and enforces the
entity integrity of the table.
Some would argue that a table without a primary key is not even a table.
Why would you need such a table?|||Hi Raymond,

> You forgot the most important reason.
> ...whose values uniquely identify each row in the table and enforces the
> entity integrity of the table.
> Some would argue that a table without a primary key is not even a table.
> Why would you need such a table?
Good point. I guess I was thinking of the case of a small table (2 or 3
columns) of relatively small size and constant data, and where the
combination of data on all columns comprises the uniqueness of the table.
For instance, a table that consists of "header information": maybe a company
name, and an "EstablishedIn" date. Maybe this is kept for a web site
provider company to generate some header information on a page for its
customers. Assuming that this is a small company that has only a few
customers (and plans to stay this way), this is a small table with constant
data, and the combo of CompanyName and EstablishedIn date will always be
unique.
(OK, perhaps not the best example, but the first thing that comes to mind.)
Mind you, it was my "gut feeling" that tables should always define primary
keys, but I didn't know if there was a reason not to (for instance,
overhead), especially if it was known that all rows are unique, and that the
uniqueness is the combination of all columns of a table.
Thanks,
pagates
"Raymond D'Anjou" wrote:

> "pagates" <pagates@.discussions.microsoft.com> wrote in message
> news:5F8D4C00-28F8-4DE9-884D-7300F7469329@.microsoft.com...
> You forgot the most important reason.
> ...whose values uniquely identify each row in the table and enforces the
> entity integrity of the table.
> Some would argue that a table without a primary key is not even a table.
> Why would you need such a table?
>
>|||pagates wrote:
> Hello All,
> I am in the midst of designing a database, and I have a very general
> (pseudo-newbie) question.
> Should all tables have a Primary Key defined?
> As I understand it, there are two reasons to have a primary key:
> - It is used in conjunction with a Foreign Key on a table, and
> - It automatically creates an Index on a table
> I would guess that most tables fit one or both of the criteria listed abov
e,
> but is it good practice for all tables, or is it unneeded overhead? For
> instance, I will have a few tables that are small (maybe 50 rows, max) and
> generally fixed (the data is permanent, or nearly permanent, and won't
> change), and probably won't be accessed in a multi-table query.
> Thanks,
> pagates
If the data in your small tables is truly fixed and unchanging then
there is no reason NOT to add a primary key. Keys = integrity. One day
you WILL update those tables and how many things will break if you get
duplicates in there because you didn't bother to declare the key?
Another reason: keys are useful metadata for those who will have to
support and develop your system in future and for those who will
utilise the data. Leaving out keys tells them only that you didn't know
or didn't care what infromation the table was supposed to represent. If
you are learning then start learning good habits now.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> Mind you, it was my "gut feeling" that tables should always define primary
> keys, but I didn't know if there was a reason not to (for instance,
> overhead), especially if it was known that all rows are unique, and that
> the
> uniqueness is the combination of all columns of a table.
Once you decide what information you need to store in a database, as a
database designer your focus should change to how to keep garbage out. It
doesn't matter how the database performs if the answers it returns are
incorrect. Integrity constraints are extremely important. Murphy's Law
applies to databases, too: if it can happen, it will. You must make sure
that it can't. The only way to do this is to apply constraints to the
database, but that's not enough. You should also normalize and
orthoganalize the database to minimize, or if possible, to eliminate
redundancy. Redundancy cedes the database's constraint checking
responsibility to the application, so it's extremely important to minimize
it. You should also be careful with your queries: many use WITH(NOLOCK) to
improve performance, but as I've posted several times on this forum, NOLOCK
will make your queries return incorrect results at lightning speed. If
performance is a problem, add hardware, add indexes--add both, but don't
compromise integrity.
Whenever I come across a database without constraints, I have to spend days,
if not ws, cleaning out the garbage. A poorly designed database can
double or even triple the amount of time needed to develop an application,
and even then, because there's no way to be sure that the database doesn't
contain garbage, it's much, much more difficult to test and troubleshoot
application code. A poor foundation can turn a two-month project into a
two-year project.
"pagates" <pagates@.discussions.microsoft.com> wrote in message
news:67F5B67A-629E-45E0-A130-DB0582754000@.microsoft.com...
> Hi Raymond,
>
> Good point. I guess I was thinking of the case of a small table (2 or 3
> columns) of relatively small size and constant data, and where the
> combination of data on all columns comprises the uniqueness of the table.
> For instance, a table that consists of "header information": maybe a
> company
> name, and an "EstablishedIn" date. Maybe this is kept for a web site
> provider company to generate some header information on a page for its
> customers. Assuming that this is a small company that has only a few
> customers (and plans to stay this way), this is a small table with
> constant
> data, and the combo of CompanyName and EstablishedIn date will always be
> unique.
> (OK, perhaps not the best example, but the first thing that comes to
> mind.)
> Mind you, it was my "gut feeling" that tables should always define primary
> keys, but I didn't know if there was a reason not to (for instance,
> overhead), especially if it was known that all rows are unique, and that
> the
> uniqueness is the combination of all columns of a table.
> Thanks,
> pagates
> "Raymond D'Anjou" wrote:
>|||I see no reason why any table would not have a unique key, even if it's just
the datetime that the row was inserted.
The real debate is whether or not natural keys or an additional integer
based surrogate key (ex: identity column) should enfore foreign key
relationships.
"pagates" <pagates@.discussions.microsoft.com> wrote in message
news:5F8D4C00-28F8-4DE9-884D-7300F7469329@.microsoft.com...
> Hello All,
> I am in the midst of designing a database, and I have a very general
> (pseudo-newbie) question.
> Should all tables have a Primary Key defined?
> As I understand it, there are two reasons to have a primary key:
> - It is used in conjunction with a Foreign Key on a table, and
> - It automatically creates an Index on a table
> I would guess that most tables fit one or both of the criteria listed
> above,
> but is it good practice for all tables, or is it unneeded overhead? For
> instance, I will have a few tables that are small (maybe 50 rows, max) and
> generally fixed (the data is permanent, or nearly permanent, and won't
> change), and probably won't be accessed in a multi-table query.
> Thanks,
> pagates

Design question - Foreign key indexes

A rather elementary design question - is it a good idea to index foreign keys in fact tables? I understand this will slow down inserts/deletes/updates, but when the cube is processing will it speed up processing time? Does the cube generate all dimension members then query fact tables for them? Does it query fact tables with group by's on participating dimensions? I see example cubes that have this, but am unable to find documentation saying this is a good idea. I would hate to miss out on a big performance boost, but don't want to take the chance to slow things down... Any suggestions / supporting documentation is very much welcomed.

Thanks in advance,

John Hennesey

It is an interesting question.

General assumption is Analysis Services cubes are being built based on the relational data warehouse which data is coming from OLTP system. The relationa data warehouse is not the place where you expecting lots of individual atomic updates. That is in theory. In practice depending on the size I have seen implmentations where cubes are built based on raw OLTP shemas.

It is dependant on your case and it is really tradeoff you will be making. If you would like to speed up processind of cubes , you should go and run, for example index tuning wizard and build your relational database indexes that match the queries Analysis Server sends during processing. I wouldnt go and try analyzing every query by yourself. Index tuning wizard makes work easier for you.

But if you see indexed built slowing down your ETL update process, you can always drop these indexes.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi:

I think there is another consideration and it is performance -not on insertion of data - but at ETL time, when you want to determine if a given row is already present in a dimension (locating a row).

Most of the times, I just index for ETL speed. OLAP Processing , because of the amount of rows read, may easily go to be a table/partition scan, making indexes almost useless.However, that also depends on the cardinality of your attributes, and what kind of processing you use in your OLAP structures

Alejandro Leguizamo

SQL Server MVP

Colombia

|||

Alejandro / Edward -

Thank you very much for your quick responses - both very good points. Edward - to clarify, we are using service broker which receives xml messages from our source system, which uses stored procs to update our data warehouse. During peak times we receive hundreds of thousands of messages each day, and each one will cause updates / inserts / deletes in our dimension and fact tables. Hence my consideration about index changes slowing things down. When we process, we are doing a full process, so Alejandro's response about doing full table scans is a very realistic possibility. The only way to truly tell what is going on and if there can be some performance boost is to run the index tuning wizard - a very good suggestion. I will check it out!

Thanks again,

John

p.s. Alejandro - you would be suprised what the Enterprise Reporting solution has evolved into - I believe Robert Skoglund gave you guys a nice presentation a few months back...

Design Question

Dear All,
I have the following design issue which I am unsure how to solve.
Here's the key information:
I am writing a tool which will send emails/SMS to registered users. The
registered user data will be stored in one database (we'll call it
DB1). No changes can be made to DB1. All information related to the
tool will be stored in a different database (we'll call it DB2). This
will include the following tables (among others):
- "Sent Email" table to store which user (from DB1) received what email
- "User" table to store individuals who have permission to login to the
tool
- "Email Template" table to store email templates
- "Email Template Log" table to record which user created/edited the
template
My specific problem is how to model these relationships in DB2. Will
DB2 contain a "User Look Up" table which stores the ID (PK) of the user
from DB1? Therefore my "Sent Email" table will have a FK from this look
up table? In addition if the scenario was that I needed to record an
attribute "Don't send me messages" would this be stored on the look up
table as well?
Thanks for any help in advance.
Jose"Jose" <discussions@.avandis.co.uk> wrote in message
news:1135436233.215881.325210@.g14g2000cwa.googlegroups.com...
> Dear All,
> I have the following design issue which I am unsure how to solve.
> Here's the key information:
> I am writing a tool which will send emails/SMS to registered users. The
> registered user data will be stored in one database (we'll call it
> DB1). No changes can be made to DB1. All information related to the
> tool will be stored in a different database (we'll call it DB2). This
> will include the following tables (among others):
> - "Sent Email" table to store which user (from DB1) received what email
> - "User" table to store individuals who have permission to login to the
> tool
> - "Email Template" table to store email templates
> - "Email Template Log" table to record which user created/edited the
> template
> My specific problem is how to model these relationships in DB2. Will
> DB2 contain a "User Look Up" table which stores the ID (PK) of the user
> from DB1? Therefore my "Sent Email" table will have a FK from this look
> up table? In addition if the scenario was that I needed to record an
> attribute "Don't send me messages" would this be stored on the look up
> table as well?
> Thanks for any help in advance.
> Jose
>
I guess you'll need a Users table in DB2. What you won't be able to do is
create a foreign key on it that references DB1. Cross-database constraints
aren't supported. You could create a view in DB2 that references the Users
table in DB1.
Have you considered using Notification Services? All you've described and
more ...
http://www.microsoft.com/sql/techno...on/default.mspx
Both 2000 and 2005 editions of NS are available.
David Portas
SQL Server MVP
--

Design problem

Hello. I'm using SQL2000, and I have a design problem.
I know that every time I make a primary key, sql server makes it by default
a clustered index. Since I have a large composite key in the table I don't
know if it's smart to leave it as a clustered index. This is the table:

CREATE TABLE [InputOutputItems] (
[FromStorage] [smallint] NOT NULL ,
[ToStorage] [smallint] NOT NULL ,
[DocumentTypeID] [int] NOT NULL ,
[DocumentNumber] [int] NOT NULL ,
[StorageDocYear] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[SerialNumber] [varchar] (50) COLLATE Croatian_CI_AI NOT NULL ,
[PartnerID] [int] NULL ,
[Barcode] [varchar] (50) COLLATE Croatian_CI_AI NULL ,
[DaysOfExpiration] [int] NULL ,
[DateOfValidation] [datetime] NULL ,
[Row] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[Column] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[Level] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[UnitDimensionID] [int] NULL ,
[UnitPack] [decimal](18, 4) NULL ,
[TotalWeight] [decimal](18, 4) NULL ,
[PackageMachineID] [int] NOT NULL ,
[PackageEmployeeID] [int] NULL ,
[UserIDCreated] [int] NULL ,
[DateCreated] [datetime] NULL ,
[UserIDChanged] [int] NULL ,
[DateChanged] [datetime] NULL ,
[PaleteNumber] [int] NULL ,
[LinkedDocument] [int] NULL ,
CONSTRAINT [PK_InputOutputItems] PRIMARY KEY CLUSTERED
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear],
[ProductID],
[SerialNumber],
[PackageMachineID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_InputOutputItems_InputOutput] FOREIGN KEY
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
) REFERENCES [InputOutput] (
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
)
) ON [PRIMARY]

InputOutPutItems is actually an Item table for documents, here is the header
table for documents:

CREATE TABLE [InputOutput] (
[FromStorage] [smallint] NOT NULL ,
[ToStorage] [smallint] NOT NULL ,
[DocumentTypeID] [int] NOT NULL ,
[DocumentNumber] [int] NOT NULL ,
[StorageDocYear] [int] NOT NULL ,
[AppUserID] [smallint] NULL ,
[ManufactureIndent] [int] NULL ,
[DeliveryDate] [datetime] NULL ,
[OrgUnitID] [int] NULL ,
[TypeOfTransferID] [int] NULL ,
[Note] [varchar] (500) COLLATE Croatian_CI_AI NULL ,
[Status] [int] NULL ,
[UserIDCreated] [int] NULL ,
[DateCreated] [datetime] NULL ,
[UserIDChanged] [int] NULL ,
[DateChanged] [datetime] NULL ,
[TypeOfIndent] [char] (2) COLLATE Croatian_CI_AI NULL ,
[TMOrgUnitID] [smallint] NULL ,
CONSTRAINT [PK_InputOutput] PRIMARY KEY CLUSTERED
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_InputOutput_DocumentTypes] FOREIGN KEY
(
[DocumentTypeID]
) REFERENCES [DocumentTypes] (
[DocumentTypeID]
),
CONSTRAINT [FK_InputOutput_OrgUnits] FOREIGN KEY
(
[FromStorage]
) REFERENCES [OrgUnits] (
[OrgUnitID]
),
CONSTRAINT [FK_InputOutput_OrgUnits1] FOREIGN KEY
(
[ToStorage]
) REFERENCES [OrgUnits] (
[OrgUnitID]
)
) ON [PRIMARY]

These tabels are used for documents in a Warehouse Managament System with
barcode scanners.
Because of the way that the system works the primary keys cannot be any
smaller. I never use the whole
primary key in a where clause, I just use parts of it.
Should I make somethig else a clustered index, or leave the clustered index
as a primary key?

Thanks in advance.
Drazen Grabovac.Grabi (drazen@.git.hr) writes:
> Hello. I'm using SQL2000, and I have a design problem. I know that every
> time I make a primary key, sql server makes it by default a clustered
> index. Since I have a large composite key in the table I don't know if
> it's smart to leave it as a clustered index. This is the table:

This question already has answers in microsoft.public.sqlserver.programming.
Please do not post the same question independly to several newsgroups,
as this can result in people wasting on a time on a problem that has
already been addressed.

--
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

Design problem

Hello. I'm using SQL2000, and I have a design problem.
I know that every time I make a primary key, sql server makes it by default
a clustered index. Since I have a large composite key in the table I don't
know if it's smart to leave it as a clustered index. This is the table:
CREATE TABLE [InputOutputItems] (
[FromStorage] [smallint] NOT NULL ,
[ToStorage] [smallint] NOT NULL ,
[DocumentTypeID] [int] NOT NULL ,
[DocumentNumber] [int] NOT NULL ,
[StorageDocYear] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[SerialNumber] [varchar] (50) COLLATE Croatian_CI_AI NOT NULL ,
[PartnerID] [int] NULL ,
[Barcode] [varchar] (50) COLLATE Croatian_CI_AI NULL ,
[DaysOfExpiration] [int] NULL ,
[DateOfValidation] [datetime] NULL ,
[Row] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[Column] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[Level] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[UnitDimensionID] [int] NULL ,
[UnitPack] [decimal](18, 4) NULL ,
[TotalWeight] [decimal](18, 4) NULL ,
[PackageMachineID] [int] NOT NULL ,
[PackageEmployeeID] [int] NULL ,
[UserIDCreated] [int] NULL ,
[DateCreated] [datetime] NULL ,
[UserIDChanged] [int] NULL ,
[DateChanged] [datetime] NULL ,
[PaleteNumber] [int] NULL ,
[LinkedDocument] [int] NULL ,
CONSTRAINT [PK_InputOutputItems] PRIMARY KEY CLUSTERED
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear],
[ProductID],
[SerialNumber],
[PackageMachineID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_InputOutputItems_InputOutput] FOREIGN KEY
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
) REFERENCES [InputOutput] (
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
)
) ON [PRIMARY]
The tabel is used for document items in a WMS system. I never use the whole
primary key in a where clause, I just use parts of it.
Should I make somethig else a clustered index, or leave the clustered index
as a primary key?
Drazen Grabovac.If you use EM (Enterprise Manager) and set the PK first then by default a
clustred index will be created on the PK column(s). You can first create the
clustred index as desired and then set the PK.
When defining the clustred index take into concidaration how will you being
querying data. If you are going to have range queries i.e. by dates then
concider a clustred index on the relevant datetime column.
For a single row access sql will use your PK and for range queries the
clustered index.
In addition you may create other noneclustred indexs as required to server
data retreival.
"Grabi" <drazen@.git.hr> wrote in message news:dvjpor$5nq$1@.ss405.t-com.hr...
> Hello. I'm using SQL2000, and I have a design problem.
> I know that every time I make a primary key, sql server makes it by
> default a clustered index. Since I have a large composite key in the table
> I don't know if it's smart to leave it as a clustered index. This is the
> table:
> CREATE TABLE [InputOutputItems] (
> [FromStorage] [smallint] NOT NULL ,
> [ToStorage] [smallint] NOT NULL ,
> [DocumentTypeID] [int] NOT NULL ,
> [DocumentNumber] [int] NOT NULL ,
> [StorageDocYear] [int] NOT NULL ,
> [ProductID] [int] NOT NULL ,
> [SerialNumber] [varchar] (50) COLLATE Croatian_CI_AI NOT NULL ,
> [PartnerID] [int] NULL ,
> [Barcode] [varchar] (50) COLLATE Croatian_CI_AI NULL ,
> [DaysOfExpiration] [int] NULL ,
> [DateOfValidation] [datetime] NULL ,
> [Row] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
> [Column] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
> [Level] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
> [UnitDimensionID] [int] NULL ,
> [UnitPack] [decimal](18, 4) NULL ,
> [TotalWeight] [decimal](18, 4) NULL ,
> [PackageMachineID] [int] NOT NULL ,
> [PackageEmployeeID] [int] NULL ,
> [UserIDCreated] [int] NULL ,
> [DateCreated] [datetime] NULL ,
> [UserIDChanged] [int] NULL ,
> [DateChanged] [datetime] NULL ,
> [PaleteNumber] [int] NULL ,
> [LinkedDocument] [int] NULL ,
> CONSTRAINT [PK_InputOutputItems] PRIMARY KEY CLUSTERED
> (
> [FromStorage],
> [ToStorage],
> [DocumentTypeID],
> [DocumentNumber],
> [StorageDocYear],
> [ProductID],
> [SerialNumber],
> [PackageMachineID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_InputOutputItems_InputOutput] FOREIGN KEY
> (
> [FromStorage],
> [ToStorage],
> [DocumentTypeID],
> [DocumentNumber],
> [StorageDocYear]
> ) REFERENCES [InputOutput] (
> [FromStorage],
> [ToStorage],
> [DocumentTypeID],
> [DocumentNumber],
> [StorageDocYear]
> )
> ) ON [PRIMARY]
> The tabel is used for document items in a WMS system. I never use the
> whole primary key in a where clause, I just use parts of it.
> Should I make somethig else a clustered index, or leave the clustered
> index as a primary key?
>
> Drazen Grabovac.
>|||Your design looks wrong.
Call you explain what a "type_id" means? An attribute can be a type or
an identifer, but NEVER both (see ISO-11179).
Can you give an example of varyign length 50 character barcode?
What is a user_id history being kept in this table? Where is the User
table that should have that kind of information?
What is "InputOutputItems" -- other than very vague.
Some of the other names also suggest LOTS of problems, which might be
why you have such a large key.|||Hello.
DocumentTypeID means ID, but it's named that way because it's a FOREIGN KEY
to this table:
CREATE TABLE [DocumentTypes] (
[DocumentTypeID] [int] NOT NULL ,
[AppUserID] [smallint] NULL ,
[Name] [varchar] (50) COLLATE Croatian_CI_AI NULL ,
[Description] [varchar] (500) COLLATE Croatian_CI_AI NULL ,
[OweInput] [int] NULL ,
[LookupInput] [int] NULL ,
[ActiveUntil] [datetime] NULL ,
[Label] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[ChargeTypeID] [int] NULL ,
[UserIDCreated] [int] NULL ,
[DateCreated] [datetime] NULL ,
[UserIDChanged] [int] NULL ,
[DateChanged] [datetime] NULL ,
CONSTRAINT [PK_DocumentTypes] PRIMARY KEY CLUSTERED
(
[DocumentTypeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
The remark about the Barcode is ok. It's a varyign length 50 character
barcode because
we made a lot of changes to the structure of the barcode.
This is the user table:
CREATE TABLE [Users] (
[UserID] [MyKey] NOT NULL ,
[EmployeeID] [MyKey] NULL ,
[PartnerID] [MyKey] NULL ,
[OrgUnitID] [smallint] NULL ,
[Type1] [MyKey] NULL ,
[Type2] [MyKey] NULL ,
[UserName] [MyString] NOT NULL ,
[Password] [MyString] NOT NULL ,
[LogTime] [smalldatetime] NULL ,
[LogCount] [MyKey] NULL ,
[LastLog] [datetime] NULL ,
[Active] [bit] NULL ,
[Activation] [datetime] NULL ,
[ActivationNote] [MyLongString] NULL ,
[LanguageID] [MyKey] NULL ,
[Sms] [MyKey] NULL ,
[SmsSent] [MyKey] NULL ,
[Note] [MyText] NULL ,
[PiccoLinkPassword] [varchar] (10) COLLATE Croatian_CI_AI NULL ,
[PActive] [char] (1) COLLATE Croatian_CI_AI NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_Users_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [Employees] (
[EmployeeID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Users_Languages] FOREIGN KEY
(
[LanguageID]
) REFERENCES [Languages] (
[LanguageID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Users_OrgUnits] FOREIGN KEY
(
[OrgUnitID]
) REFERENCES [OrgUnits] (
[OrgUnitID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Users_Partners] FOREIGN KEY
(
[PartnerID]
) REFERENCES [Partners] (
[PartnerID]
) NOT FOR REPLICATION
) ON [PRIMARY]
InputOutPutItems is actually an Item table for documents, here is the header
table for documents:
CREATE TABLE [InputOutput] (
[FromStorage] [smallint] NOT NULL ,
[ToStorage] [smallint] NOT NULL ,
[DocumentTypeID] [int] NOT NULL ,
[DocumentNumber] [int] NOT NULL ,
[StorageDocYear] [int] NOT NULL ,
[AppUserID] [smallint] NULL ,
[ManufactureIndent] [int] NULL ,
[DeliveryDate] [datetime] NULL ,
[OrgUnitID] [int] NULL ,
[TypeOfTransferID] [int] NULL ,
[Note] [varchar] (500) COLLATE Croatian_CI_AI NULL ,
[Status] [int] NULL ,
[UserIDCreated] [int] NULL ,
[DateCreated] [datetime] NULL ,
[UserIDChanged] [int] NULL ,
[DateChanged] [datetime] NULL ,
[TypeOfIndent] [char] (2) COLLATE Croatian_CI_AI NULL ,
[TMOrgUnitID] [smallint] NULL ,
CONSTRAINT [PK_InputOutput] PRIMARY KEY CLUSTERED
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_InputOutput_DocumentTypes] FOREIGN KEY
(
[DocumentTypeID]
) REFERENCES [DocumentTypes] (
[DocumentTypeID]
),
CONSTRAINT [FK_InputOutput_OrgUnits] FOREIGN KEY
(
[FromStorage]
) REFERENCES [OrgUnits] (
[OrgUnitID]
),
CONSTRAINT [FK_InputOutput_OrgUnits1] FOREIGN KEY
(
[ToStorage]
) REFERENCES [OrgUnits] (
[OrgUnitID]
)
) ON [PRIMARY]
So now that you have an idea of how the document tabels look in my database,
could you give me an advice.
Should I put the primary key as a clustered index or not? InputOutPutItems
is pretty large and has millions or records,
and query's can sometimes be slow.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1142780014.593696.168280@.g10g2000cwb.googlegroups.com...
> Your design looks wrong.
> Call you explain what a "type_id" means? An attribute can be a type or
> an identifer, but NEVER both (see ISO-11179).
> Can you give an example of varyign length 50 character barcode?
> What is a user_id history being kept in this table? Where is the User
> table that should have that kind of information?
> What is "InputOutputItems" -- other than very vague.
> Some of the other names also suggest LOTS of problems, which might be
> why you have such a large key.
>
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1142780014.593696.168280@.g10g2000cwb.googlegroups.com...
> Your design looks wrong.
> Call you explain what a "type_id" means? An attribute can be a type or
> an identifer, but NEVER both (see ISO-11179).
> Can you give an example of varyign length 50 character barcode?
> What is a user_id history being kept in this table? Where is the User
> table that should have that kind of information?
> What is "InputOutputItems" -- other than very vague.
> Some of the other names also suggest LOTS of problems, which might be
> why you have such a large key.
>|||Grabi (drazen@.git.hr) writes:
> Hello. I'm using SQL2000, and I have a design problem. I know that every
> time I make a primary key, sql server makes it by default a clustered
> index. Since I have a large composite key in the table I don't know if
> it's smart to leave it as a clustered index. This is the table:
Whether it is a good idea or not that the PK by default is clustered
is disputed. Some people think it's bad. Personally, I think it's good
for the simple reason that many tables have no other index than the PK,
and in the verymost cases it's a good idea that all tables have a
clustered index.
Whatever, in very many cases, the clustered index shold not be on the
primary key. Typically you put the clustered index on a column that
appears commonly in range queries. For instance in an Orders table,
you would probably cluster on OrderDate or CustomerID depending on what
you most often want to view queries by.
What you should cluster on in InputOutputItems, I can't tell, because
I don't know the business. If most queries will by ProductID, maybe that
is the guy. But I don't know.
One thing to keep in mind, is that in non-clustered index, the clustered
index key is used as row locator. This means that a wide clustered key, also
increases the size of the non-clustered indexes.
As for the general design I note that all your non-key columns are nullable,
but is really that way? Could there really exist a row where DateCreated
and UserIDCreated are NULL? Or for that matter the BarCode? Strictly
specifying which columns that may be nullable and which may be not, will
help you to get better quality of the data.
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

Design of Dimension table ID in a datamart - best practise

Best practise wanted!

In a datamart (DM) a fact row is linked to a dimension by a dimension ID (key). If a DM is build on top of a (enterprise) datawarehouse (EDW), and the dimension table has a corresponding EDW table with a unique ID (dummy key), will there then be any DM design conciderations for not using that EDW table key.

In other words when you design the DM, should the dimensions then have there own (new) ID's regardless of you allready have modelled (unique) ID in the EDW. I have these considerations in favour for using the EDW table ID keys i a dimension:

1. Staging the DM is easy, the EDW table staging is handling the correctness of the dimension ID's.

2. Backtracing concering DM quality checking, errors and so on, is simplyfied by using the same ID in both DM and EDW.

No. Use the same technical key in DM as in EDW.

If you have a product dimension that is used in several data marts, like sales or production planning, you would like to use the same key in order to consolidate information in these data marts. In SSAS 2000/2005 you can then build cubes with the same product dimension which also means less dimension design and maintenance.

HTH

Thomas Ivarsson

|||Thanks

Design issue please help

Hi all,
I do have four combination key for eg:1111-1111-1111-1111
the values can range from 0-0, based upon this i can create any number
of keys eg:1234-4567-4352-6534, these four values comes from different
tables, while joining the four i insert the new combination in a new
table .
In order to give security, i can give access rights to each parts for
eg: a user can have rights to 1234, but dont have the rights to 4567.
Iam using these keys in lot of my records , but i need to give a
checking based on the user , if he does not have the rights to a
particular segment , he wont be able to view , nor edit the record.
How do i implement this security on segment based,
Thanks in advance
Cheers
thomsonthomson
Look, I don't know your business requirements and obviously it depends on
bit permit me to suggest something
Please look at application role implemented by SQL Server as well as visit
on Vyas's greate web site to read below article
<http://vyaskn.tripod.com/sql_server...t_practices.htm> --se
curity best practices
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1114056973.285659.247590@.g14g2000cwa.googlegroups.com...
> Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
> the values can range from 0-0, based upon this i can create any number
> of keys eg:1234-4567-4352-6534, these four values comes from different
> tables, while joining the four i insert the new combination in a new
> table .
>
> In order to give security, i can give access rights to each parts for
> eg: a user can have rights to 1234, but dont have the rights to 4567.
> Iam using these keys in lot of my records , but i need to give a
> checking based on the user , if he does not have the rights to a
> particular segment , he wont be able to view , nor edit the record.
>
> How do i implement this security on segment based,
> Thanks in advance
> Cheers
> thomson
>|||Dear Thomson,
The same problem i faced in my project and we have implemented in the
project manually[no supported my MS SQL].
For segment based manual security U need to have a separate table for this
which will let U know the which Key is accced by which User
KeyVal UserID SecurityLevel(RoleID)
1234 1 1
1234 3 2
6534 1 4
... ... ...
... ... ...
... ... ...
Hee SecurityLevel(RoleID) field has the ID/Level for Security.Security Level
may be like enum... or as u wish.
I think From this table you help you in getting the user ID with their
rights and role for a desired Keyval.
Am i clear to you ?
Please let me know.
NB: Please reply me on rakesh.ranjan@.3i-infotech.com
Thanking you
Rakesh Ranjan
rakesh.ranjan@.3i-infotech.com
Mumbai[India]
"thomson" wrote:

> Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
> the values can range from 0-0, based upon this i can create any number
> of keys eg:1234-4567-4352-6534, these four values comes from different
> tables, while joining the four i insert the new combination in a new
> table .
>
> In order to give security, i can give access rights to each parts for
> eg: a user can have rights to 1234, but dont have the rights to 4567.
> Iam using these keys in lot of my records , but i need to give a
> checking based on the user , if he does not have the rights to a
> particular segment , he wont be able to view , nor edit the record.
>
> How do i implement this security on segment based,
> Thanks in advance
> Cheers
> thomson
>|||Dear Rakesh,
I think you got my point would you please explain it to me
the role of KeyVal and the Security Role id in Detail
Regards
thomson
RakeshRanjan wrote:
> Dear Thomson,
> The same problem i faced in my project and we have implemented in the

> project manually[no supported my MS SQL].
> For segment based manual security U need to have a separate table for
this
> which will let U know the which Key is accced by which User
> KeyVal UserID SecurityLevel(RoleID)
> 1234 1 1
> 1234 3 2
> 6534 1 4
> ... ... ...
> ... ... ...
> ... ... ...
> Hee SecurityLevel(RoleID) field has the ID/Level for
Security.Security Level
> may be like enum... or as u wish.
>
> I think From this table you help you in getting the user ID with
their
> rights and role for a desired Keyval.
> Am i clear to you ?
> Please let me know.
> NB: Please reply me on rakesh.ranjan@.3i-infotech.com
> Thanking you
> Rakesh Ranjan
> rakesh.ranjan@.3i-infotech.com
> Mumbai[India]
>
> "thomson" wrote:
>
number
different
new
for
4567.|||One more thing is that If you have mentioned the KeyVal value, as the
one like 1111-1111-1111-1111, Then the issue is there can be a Hypen
between segments, or there can be any other Characters in between the
segments, So how will i tackle this
Regards
thomson|||Hi Tjhomson,
Key val is nothing but ur primary Key of all other table which make ur newly
generated Segmentkey(1234-4567-4352-6534).
You can have any charecter other than "-", It depends on you, how you want
to program it. This will be treated as a charecter for spliting your segment
key (which u have created from the four IDs).
Here the SecurityLevel(RoleID) field is rthe ID(Primary Key) of ur
security/role table which a user has, so that u can come to know the level o
f
user security.
Hope this much help u in better understanding. For further clarification
mail me on rakesh.ranjan@.3i-infotech.com
With regards,
Rakesh.
"thomson" wrote:

> Dear Rakesh,
> I think you got my point would you please explain it to me
> the role of KeyVal and the Security Role id in Detail
> Regards
> thomson
> RakeshRanjan wrote:
>
> this
> Security.Security Level
> their
> number
> different
> new
> for
> 4567.
>|||Hi rakesh,
The problem lies here that i dont want to give the security
to the whole key 1111-1111-1111-1111.
My issue is i have to give the security to each segments eg:1111
this 4 key can be generated by any one, its a one time setup, Then i
have to give security to each segments for each user.
suppose if i create a segment 1111-2222-3333-4444.
If i dont have the access to 1111, what i should not able to access the
records having 1111-2222-3333-4444 or 2222-1111-3333-4444, cos i dont
have access to the particular segment 1111.
Thanks in advance
thomson
thomson wrote:
> One more thing is that If you have mentioned the KeyVal value, as the
> one like 1111-1111-1111-1111, Then the issue is there can be a Hypen
> between segments, or there can be any other Characters in between the
> segments, So how will i tackle this
> Regards
> thomson|||Hello Thomson,
Its little bit different than what I have Implemented. Though no issue , I
have a clue.
As u said the segment "1111-2222-3333-4444" is one time creation. Even
multiple time , I have no issue at all.
What u need to do is, after creation of the key "1111-2222-3333-4444" split
it into four parts i.e. 1111, 2222, 3333, 4444 and then Insert four records
in the said table with their rights( as i explained earlier), Now you know
which key has what access. so can can proceed further as per ur requirement.
For further please chat with me on mail "rakesh.ranjan@.3i-infotech.com".
With Regards,
Rakesh R.
"thomson" wrote:

> Hi rakesh,
> The problem lies here that i dont want to give the security
> to the whole key 1111-1111-1111-1111.
> My issue is i have to give the security to each segments eg:1111
> this 4 key can be generated by any one, its a one time setup, Then i
> have to give security to each segments for each user.
> suppose if i create a segment 1111-2222-3333-4444.
> If i dont have the access to 1111, what i should not able to access the
> records having 1111-2222-3333-4444 or 2222-1111-3333-4444, cos i dont
> have access to the particular segment 1111.
> Thanks in advance
>
> thomson
> thomson wrote:
>|||thomson,
You'll have to parse the key. If it's just 4 4-character pieces, it's
not hard.
select key from tableOfKeys
where not exists (
select deniedPermission from Permissions
where Permissions.theUser = @.theUser
and deniedPermission in (
substring(key,1,4),
substring(key,6,4),
substring(key,11,4),
substring(key,16,4)
)
)
If permissions are assigned positively, it's something like
select key from tableOfKeys
where not exists (
select keyPermission from (
select substring(key,1,4) union all
select substring(key,6,4) union all
select substring(key,11,4) union all
select substring(key,16,4)
) as K(keyPermission)
where keyPermission not in (
select permission from Permissions
where permission.theUser = @.theUser
)
)
[both untested]
Steve Kass
Drew University
thomson wrote:

>Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
>the values can range from 0-0, based upon this i can create any number
>of keys eg:1234-4567-4352-6534, these four values comes from different
>tables, while joining the four i insert the new combination in a new
>table .
>
> In order to give security, i can give access rights to each parts for
>eg: a user can have rights to 1234, but dont have the rights to 4567.
>Iam using these keys in lot of my records , but i need to give a
>checking based on the user , if he does not have the rights to a
>particular segment , he wont be able to view , nor edit the record.
>
>How do i implement this security on segment based,
>Thanks in advance
>Cheers
>thomson
>
>