Sql Comparing two tables: composite

Showing posts with label composite. Show all posts

Tuesday, March 20, 2012

Composite vs Single Primary Key

Our DBA has chosen to use an autonumber column as the primary key for a linking table consisting of 2 other IDs, I'll call them x and y. Another developer is concerned that since our application only keeps track of IDs x and y, not the autonumber ID, that searches will be less efficient because if x and y were a composite key they would be indexed. Is this correct? If so, couldn't you just create an index for these columns to make it equally efficient?

I believe that to eliminate all composite keys is a requirement for second normal form (correct me if I'm wrong), but does assigning an autonumber primary key to a linking table made up of solely 2 IDs have any benefits?

First of all, it's entirely possible to have the primary key (in the relational database sense, the one you can link tables with) different from the CLUSTERING key (the key for the main index of the table), e.g.:

Create Table T(p int primary key nonclustered, x int, y int);

Create Clustered Index T_i on T(x);

Also, you can always create secondary nonclustered indexes to improve efficiency, please look up indexing strategies articles (e.g. Books On-Line article http://msdn2.microsoft.com/en-us/library/ms189271.aspx), although I think for a linking table a single clustered index should be fine.

Now, for your particular issue I can't think of any benefit to an autonumber primary key except that if a lot of INSERT operations occur, and if the two other ID's are not naturally increasing (on average), then the autonumbering clustering key would keep the index from being fragmented and INSERT's would be quicker since you always insert at the end. Having said that, my gut feel is you shouldn't do it and you should keep the composite key. The composite key (if it's a primary key not just a clustereing key) also would enforce that no duplicate combinations would be inserted into the linking table.

|||

2nf just states that all data in a table must rely on the entire key.

that doesnt mean the key cant be composite.

if this table resolves a many to many relationship (Which sounds to be the case) then it is correctly designed for 2nd (even 3nf)

composite stored procedure

Hi,
I am trying to write a procedure in SQL which will composite values in a table. I have an increment list, which increments by 0.1, I need to average values so I produce composites for 0-1, 1-2 etc grouping by id.
The script below produces a table then a procedure, but I'm stuck on the syntax and get the error

Column 'Increment.increment' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

The script will run on the master database.
The end result should look like the following:

thanks for any help

id inc_from inc_to comp_val
-
1a 0 1 4.6

1a 1 2 4

1b 0 1 3.9

1b 1 2 4.5

drop table increment

create table increment(id varchar(10),increment float,value float)

insert into increment(id,increment,value) values('1a','0','6')
insert into increment(id,increment,value) values('1a','0.1','7')
insert into increment(id,increment,value) values('1a','0.2','4')
insert into increment(id,increment,value) values('1a','0.3','6')
insert into increment(id,increment,value) values('1a','0.4','2')
insert into increment(id,increment,value) values('1a','0.5','5')
insert into increment(id,increment,value) values('1a','0.6','8')
insert into increment(id,increment,value) values('1a','0.7','5')
insert into increment(id,increment,value) values('1a','0.8','1')
insert into increment(id,increment,value) values('1a','0.9','2')
insert into increment(id,increment,value) values('1a','1','3')
insert into increment(id,increment,value) values('1a','1.1','5')
insert into increment(id,increment,value) values('1a','1.2','4')
insert into increment(id,increment,value) values('1a','1.3','3')
insert into increment(id,increment,value) values('1a','1.4','6')
insert into increment(id,increment,value) values('1a','1.5','2')
insert into increment(id,increment,value) values('1a','1.6','1')
insert into increment(id,increment,value) values('1a','1.7','6')
insert into increment(id,increment,value) values('1a','1.8','6')
insert into increment(id,increment,value) values('1a','1.9','4')
insert into increment(id,increment,value) values('1a','2','2')
insert into increment(id,increment,value) values('1b','0','4')
insert into increment(id,increment,value) values('1b','0.1','7')
insert into increment(id,increment,value) values('1b','0.2','2')
insert into increment(id,increment,value) values('1b','0.3','1')
insert into increment(id,increment,value) values('1b','0.4','3')
insert into increment(id,increment,value) values('1b','0.5','5')
insert into increment(id,increment,value) values('1b','0.6','6')
insert into increment(id,increment,value) values('1b','0.7','4')
insert into increment(id,increment,value) values('1b','0.8','3')
insert into increment(id,increment,value) values('1b','0.9','6')
insert into increment(id,increment,value) values('1b','1','3')
insert into increment(id,increment,value) values('1b','1.1','5')
insert into increment(id,increment,value) values('1b','1.2','4')
insert into increment(id,increment,value) values('1b','1.3','3')
insert into increment(id,increment,value) values('1b','1.4','6')
insert into increment(id,increment,value) values('1b','1.5','8')
insert into increment(id,increment,value) values('1b','1.6','1')
insert into increment(id,increment,value) values('1b','1.7','6')
insert into increment(id,increment,value) values('1b','1.8','6')
insert into increment(id,increment,value) values('1b','1.9','4')
insert into increment(id,increment,value) values('1b','2','2')

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'usp_composite' AND type = 'FN')
DROP FUNCTION [dbo].[usp_composite]
GO

/*******************************************************************************
Usage: exec usp_composite
********************************************************************************/

Create Procedure usp_composite
As
Select Id,
Ceiling(Increment) - 1 As Inc_From,
Ceiling(Increment) As Inc_To,
Avg(Value * 1.0) as comp_val
From Increment
Group By Id, Ceiling(Increment),Increment.increment
Order By Id, Ceiling(Increment)

You could do something like below:

select id, inc_from, inc_to, avg(value)
from (
select id
, case when ceiling(increment) - 1 < 0 then 0 else ceiling(increment) - 1 end as inc_from
, case when ceiling(increment) - 1 < 0 then ceiling(increment) + 1 else ceiling(increment) end as inc_to
, value
from increment
) as t
group by id, inc_from, inc_to;

You are getting the error because your GROUP BY clause doesn't include the expressions that are in any of the aggregate functions. So it will work if you include ceiling(Increment) -1 also in the GROUP BY clause.

|||

Thanks for you help.

How do I modify the sql if the last depth value is correct, at the moment the sql returns 1 - 2 and not 1 - 1.95, see the code below with the create table.

drop table increment

create table increment(id varchar(10),increment float,value float)

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_composite')

DROP PROCEDURE usp_composite

/*******************************************************************************
Usage: exec usp_composite
********************************************************************************/

Create Procedure usp_composite
As
select id, inc_from, inc_to, avg(value)
from (
select id
, case when ceiling(increment) - 1 < 0 then 0 else ceiling(increment) - 1 end as inc_from
, case when ceiling(increment) - 1 < 0 then ceiling(increment) + 1 else ceiling(increment) end as inc_to
, value
from increment
) as t
group by id, inc_from, inc_to
go

|||

CELING will give the next closest integer value. So that value may not be in your table. You could check each max or min value against the values in your table. But it becomes complicated if you want to do it for each boundary value like 0 or 1 in 0-1, 1 or 2 in 1-2 and so on. And depending on how many possible values you have in your table it is going to be difficult. Can you answer some of the questions below?

1. What is the min/max range for the values in your Value column?

2. Do you want each range to be only the values in the column (like 0-1, 1-2, 2-3 and so on)?

If you always want only the actual min/max value within each ID as part of the min/max range buckets (inc_from , inc_to) then you can do something like below. The code will adjust the min/max values of each id based on the values in the table:

select id, inc_from, inc_to, avg(value)
from (
select i.id

/* adjust min if necessary based on the actual min value */
, case when ceiling(i.increment) - 1 < 0
then (case when 0 < i2.min_inc then i2.min_inc else 0 end)
else (case when ceiling(i.increment) - 1 < i2.min_inc then i2.min_inc else ceiling(i.increment) - 1 end)
end as inc_from
/* adjust min if necessary based on the actual max value */

, case when ceiling(i.increment) - 1 < 0
then (case when ceiling(i.increment) + 1 > i2.max_inc then i2.max_inc else ceiling(i.increment) + 1 end)
else (case when ceiling(i.increment) > i2.max_inc then i2.max_inc else ceiling(i.increment) end)
end as inc_to
, i.value
from increment as i
join (
select i1.id, min(i1.increment) as min_inc, max(i1.increment) as max_inc
from increment as i1
group by i1.id
) as i2
on i2.id = i.id
) as t
group by id, inc_from, inc_to;

|||

Many thanks, this works fine. I need to still average the data between 0-1,1-2,2-3 and so on but I need to display the correct increment values i.e. 1-1.95 and also if for example 0.5-1 rather than 0-1.

thanks

|||Put the increment value in your derived table. Now in you outer select, add Min(increment) and Max(increment). You get both the to and from values and the actual increments in your table.

Composite Primay Key and foreign Key

Hi all,

I get a big problem when I doing my assignment.
It told me to:

- Create a table with one composite primary key
- Create another table with one primary key and one foreign key refer to last table

Here is my SQL statement
CREATE TABLE table1
(
FirstName varchar(10)
LastName varchar(10)
PRIMARY KEY (FirstName, LastName)
)

CREATE TABLE table2
(
PlanID int,
PRIMARY KEY (PlanID)
FirstName varchar(10) FOREIGN KEY REFERENCES table1(FirstName)
LastName varchar(10) FOREIGN KEY REFERENCES table1(LastName)
)

It failed. I have searched for google long time but no result for that.
Hope you can save me. Thanks!!!Your problem is probably caused because you reference a column to a partial key. What exact error do you get?

Another solution could be:

CREATE TABLE table1
(
keyField int not null
FirstName varchar(10)
LastName varchar(10)
PRIMARY KEY (keyField)
)

CREATE TABLE table2
(
PlanID int not null,
PRIMARY KEY (PlanID)
t1_keyField int FOREIGN KEY REFERENCES table1(keyField)
)

If you want the combination of the first and last name to be unique you can always create a unique constraint on the two columns.|||[QUOTE][SIZE=1]Originally posted by jora
Your problem is probably caused because you reference a column to a partial key. What exact error do you get?

The error said
"There are no primary or candidate keys in the referenced table 'table1'
that match the referencing column list in the foreign key 'FK_table2_FirstName'

Thanks for your suggestion but table1 should have a composite primary key instead of a single primary key.|||Oh! I get it.

Here is the code:
CREATE TABLE table1
(
FirstName varchar(10),
LastName varchar(10),
PRIMARY KEY (FirstName, LastName),
)

CREATE TABLE table2
(
PlanID int,
FirstName varchar(10),
LastName varchar(10),
PRIMARY KEY (PlanID),
FOREIGN KEY(FirstName,LastName) REFERENCES table1 FirstName,LastName),
)

composite primary keys versus composite unique indexes

Hello,

I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.

The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold

1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.

A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.

I would like to have some tips, recommendations and alternatives for what I should do in this case.

One item that is not always immediately apparent for this type of scenario has to do with clustering. Is your primary key also presently your clustered index? If the answer is yes and you change to a primary key based on an identity column and leave the CLUSTERED / NOT CLUSTERED aspect of this primary key to be defaulted then any query that still accesses data based on your current primary key will experience a new performance problem -- these accesses will now also require a bookmark lookup.

The good news is that this is might not grieve you for most OLTP activity, but it is likely to impact some reports and perhaps some record lookups; however, this is rarely enough of a drawback to preclude the change your are considering. Just file keep this in mind and if some reports or lookups become slower there is a chance that this modification might be the reason.

The bigger problem usually is modifying tables that reference your table with foreign key constraints. You have two alternatives: First, you can replicate the identity column into the foreign table and now use this value as the foreign key reference; however, if you do this you should also drop the columns that references the old foreign key columns of the table that you are changing. The second alternative is to continue to reference your modified table by what has now become the UNIQUE constraint rather than the primary key. This works and I have used this a number of times but it can be unsavory. Also, if you choose this alternative you need to know the that REFERENCES clause of the foreign key constraint will now need to explicitly list the columns of the UNIQUE constraint or your FK constraint will not compile.

Also, you might have some reports, functions, or stored procedures that filter based on one of these columns in one of your foreign tables. Changing the foreign key reference might be an impact here also.

The moral of this second problem is that you want to know all tables that have foreign key constraints to the table you are considering modifying. There might be a lot of hidden additional work if your table is referenced from a couple of dozen other tables.

COMPOSITE PRIMARY KEYS

Hi,
I have 150 tables in a database.
Composite primary key is used in some of those tables.
Can I list out the table names having composite primary key.
Thanks,
Soura
SouRa
Run the following in Query Analyzer-
select name from sysindexes where keycnt <> 0
Hope that helps,
Donna
"SouRa" wrote:

> Hi,
> I have 150 tables in a database.
> Composite primary key is used in some of those tables.
> Can I list out the table names having composite primary key.
> Thanks,
> Soura

composite primary key...

I have two fields in a table,first name and last name.Both the fields contain duplicate values.Say, there are two people with the firstname mary.their last names are andrews and mathews.SQL Server wouldn't let me assign these fields a composite primary key.gives me an error saying that the first name field contains duplicate values (Duh...)....
Any help would be appreciated.No le creaste algun indice?, podrias poner el DDL para ver la tabla, ya sea desde el enterprise manager, seleccionando la tabla, click derecho, Todas las tareas, generar sql script.|||

Quote:

Originally Posted by Arkhels

No le creaste algun indice?, podrias poner el DDL para ver la tabla, ya sea desde el enterprise manager, seleccionando la tabla, click derecho, Todas las tareas, generar sql script.

???????:(|||

Quote:

Originally Posted by TheIntern

making these fields a composite primary key is not too smart. Add an ID field - data type int identity (1,1) not null.

Monday, March 19, 2012

Composite primary key on a table variable?

Is is possible to create a composite primary key on a table variable?
Neither of these two statements are successful:

DECLARE @.opmcjf TABLE (
jobdetailid INT NOT NULL,
cjfid INT NOT NULL,
cjfvalue VARCHAR(100) NULL
)

ALTER TABLE @.opmcjf ADD CONSTRAINT [PK_opmcjf] PRIMARY KEY CLUSTERED
(
[jobdetailid],
[cjfid]
)

and

DECLARE @.opmcjf TABLE (
jobdetailid INT PRIMARY KEY,
cjfid INT PRIMARY KEY,
cjfvalue VARCHAR(100) NULL
)

Thanks,
Shaun"Shaun Evans" <sevans2001@.hotmail.com> wrote in message
news:375ac918.0402240644.261460f2@.posting.google.c om...
> Is is possible to create a composite primary key on a table variable?
> Neither of these two statements are successful:
> DECLARE @.opmcjf TABLE (
> jobdetailid INT NOT NULL,
> cjfid INT NOT NULL,
> cjfvalue VARCHAR(100) NULL
> )
> ALTER TABLE @.opmcjf ADD CONSTRAINT [PK_opmcjf] PRIMARY KEY CLUSTERED
> (
> [jobdetailid],
> [cjfid]
> )
> and
> DECLARE @.opmcjf TABLE (
> jobdetailid INT PRIMARY KEY,
> cjfid INT PRIMARY KEY,
> cjfvalue VARCHAR(100) NULL
> )
> Thanks,
> Shaun

This should work - see "table" in Books Online.

DECLARE @.opmcjf TABLE (
jobdetailid INT NOT NULL,
cjfid INT NOT NULL,
cjfvalue VARCHAR(100) NULL,
primary key ([jobdetailid],[cjfid])
)

Simon|||This is the syntax:

DECLARE @.opmcjf TABLE (jobdetailid INTEGER, cjfid INTEGER, cjfvalue
VARCHAR(100) NULL, PRIMARY KEY (jobdetailid,cjfid))

AFAIK it isn't possible to alter a table-variable after it's declared. Since
a table-variable is scoped to a batch I guess that functionality wouldn't be
very useful.

--
David Portas
SQL Server MVP
--

Composite Primary Key in fact table

I have a sales fact table with dimensions: dimDate, dimCustomer, dimSalesRep, dimProduct. I am using the surrogate key from each dimension as the composite primary key in my Sales fact table. I had a problem in that the same customer may order the same product multiple times from the same sales rep on the same date. In other words I had a situation where my composite primary key would not have been unique.

It was suggested in another thread that I add the Sales Order Number, which is a bigint identity field in the OLTP, as an additional field in the PK in order to guarantee uniqueness. This I've done.

My question is this: The Sales Order Number will guarantee a unique factSales row so do I need to include all of the surrogate keys in my composite primary key or just just Sales Order Number as the primary key and maintain the surrogate keys as attributes for use in loading data?

Thanks.

Logically speaking, the fact table does not have a composite key. You may have a degenerate dimension in the fact table (such as a Sales Order Number) but logically the primary key is really associated with that dimension. Lots of folks try to put a primary key constraint on their fact tables but you do not necessarily need it from a logical modeling perspective.

In your specific situation, I'd recommend your fact table include the Sales Order Number which would have a PK on it. Foreign key references to your other dimensions would reside in your fact table as well.

|||

Hello! Have a look at www.kimballgroup.com for help with general design issues.

Normally you will create foreign keys from each dimension table to the fact table. Not a composite key.

If the customer order the same product several times on the same date from the sales representative you have the option to aggregate that information to the date level(you will only see totals for one date) or to use the time part of the date column.

It is an design issues for your business requirement.

HTH

Thomas Ivarsson

|||Thanks. I'm pulling data from 8 or 10 different OLTP tables so it'd me next to impossible to aggregate. The Sales Order ID is an identity field in the OLTP so it will always be unique. I'm using that as my primary key in my fact table.

I'm not sure where I got the idea for the composite Primary Key.......must be relational hangover from my OLTP design days......

I've used the Kimball group vid on the SSIS forum as the basis of my ETL design. I'll check out their design ideas. (I'm trying to work through the Melomed, et. all. SSAS book but I get the impression that the authors never heard of Visual Studio....Call me lazy but I'm not going to design my enterprise DW solution writing several hundred thousand lines of XMLA code.)

|||

The Melomed et al. book is really an "Inside SSAS"-type exploration of the technology. BIDS/VS really is just hiding the XMLA stuff from you with a slick interface.

Regarding the Kimball-standards, check out Chapter 5 of "The Data Warehouse Toolkit". I think it covers the exact star-schema you are attempting to create.

Good luck,

|||I use the code veiw when I have to. I just wish they could relate the inside information to modern tools, perhaps refer to both the actual desing interface AND the underlying code. They could also refer to all the examples in binary but we have modern tools these days.....|||

From "The Microsoft Data Warehouse Toolkit" by Kimball Group, page 42: "The key to the fact table is a multi-part key made up of a subset of the foreign keys from each dimension table involved in the business event."

That would seem to say something different.

|||

Kimball also says you should model your data at the lowest level of granularity available.

So, if you assemble a model at the transaction level but do not include the transaction's primary key elements in your dimensions, setting a primary key on your foreign key may result in constraint violations. The only way to avoid that is to pre-aggregate your data to the level supported by your dimensions but you don't want to do that because of data lose (hence Kimball's mandate).

If you also review the models in The Data Warehouse Toolkit, you will not find many fact tables with primary keys defined on them.

Composite Primary Key in fact table

It was suggested in another thread that I add the Sales Order Number, which is a bigint identity field in the OLTP, as an additional field in the PK in order to guarantee uniqueness. This I've done.

Thanks.

|||

Hello! Have a look at www.kimballgroup.com for help with general design issues.

Normally you will create foreign keys from each dimension table to the fact table. Not a composite key.

It is an design issues for your business requirement.

HTH

Thomas Ivarsson

I'm not sure where I got the idea for the composite Primary Key.......must be relational hangover from my OLTP design days......

|||

The Melomed et al. book is really an "Inside SSAS"-type exploration of the technology. BIDS/VS really is just hiding the XMLA stuff from you with a slick interface.

Regarding the Kimball-standards, check out Chapter 5 of "The Data Warehouse Toolkit". I think it covers the exact star-schema you are attempting to create.

Good luck,

That would seem to say something different.

|||

Kimball also says you should model your data at the lowest level of granularity available.

If you also review the models in The Data Warehouse Toolkit, you will not find many fact tables with primary keys defined on them.

Composite Primary Key in fact table

It was suggested in another thread that I add the Sales Order Number, which is a bigint identity field in the OLTP, as an additional field in the PK in order to guarantee uniqueness. This I've done.

Thanks.

|||

Hello! Have a look at www.kimballgroup.com for help with general design issues.

Normally you will create foreign keys from each dimension table to the fact table. Not a composite key.

It is an design issues for your business requirement.

HTH

Thomas Ivarsson

I'm not sure where I got the idea for the composite Primary Key.......must be relational hangover from my OLTP design days......

|||

The Melomed et al. book is really an "Inside SSAS"-type exploration of the technology. BIDS/VS really is just hiding the XMLA stuff from you with a slick interface.

Regarding the Kimball-standards, check out Chapter 5 of "The Data Warehouse Toolkit". I think it covers the exact star-schema you are attempting to create.

Good luck,

That would seem to say something different.

|||

Kimball also says you should model your data at the lowest level of granularity available.

If you also review the models in The Data Warehouse Toolkit, you will not find many fact tables with primary keys defined on them.

Composite Primary Key Constraint

Hi All,

I have a table that has 3 columns, two of them make a composite primary key. The table is populated with data. What I need to do is to add a third column to a composite primary key. I have tried to do that with the following command:

alter table databasesize
add constraint pk_dbsize primary key (dbid)

But I get the error message:

Table 'databasesize' already has a primary key defined on it.

How can I do this?You'll need to drop the existing primary key, of course.

Try making the change using enterprise manager, and then scripting the change before you save it (there is a button in the alter database dialog box that will generate the script for you).|||Thanks. It worked.

Composite primary key autoincrement from 1

Hi,

I'm currently writing a small application that is using SQL Server as a back-end database. A part of my database looks something lie the following:

Tables:
Orders(OrderId[int, PK], OrderDescription[varchar] etc...)
OrderLines(OrderId[int, PK, FK], OrderLineId[int, PK], etc...)

What I need to achieve is - everytime that a new line is inserted into an orderlines table part of the primary key will be the OrderId and the OrderLineId should be auto-incremented from 1 for each OrderId in the OrderLines table.

I know i can do this manually in my program, but i'm just wondering if theres a way to achive this in SQL Server?

Thanks,

Nick Goloborodko

Hi BushGates :-)

The two options are:

-Doing it manually within your application (like you already netioned)

-Doing it through a trigger which would evaluate the next ids for your inserted data.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens :)

Thanks for your reply - i juess i'll stick with generating the line IDs in my code :)

Nick

Composite primary key

Hello,

Does composite primary key affect performance on the table that contains the composite primary key or tables that references this table?

When composite primary key should be used?

In my experience composite keys are a through back to old systems that where converted to databases such as SQL Server / Oracle.

As a developer, rather than a DBA, composite keys are a real pain to program against especially in the modern world of object programming, data objects etc. I would always use a single unique value to identify a row rather than using composite keys. If you regularly query dataing using mulitple values from columns in the where statement then by all means create a normal index based on these columns (in the order that you use them in the where statement).

|||

so considering performance issue!! does databases with such structure perform worse than ones with single keys!! i mean directly or indirectly.

*Directly: if it has a bad performance in join clauses or in search criteria.

*Indirectly: in programming specially in .Net when using datagrids and such controls that generate relations and act with whole tables in runtime.

Thnx

composite primary key

i have a master table with around 15 columns and i am trying to find
the appropriate primary keys and indexes for the table.

To make the records unique, i need to include two datetime columns (
start and end dates ) and two integer columns ( attributes of the
record ) to make up a composite primary key. Both of these four
columns are found in the WHERE clause of my queries.

Is it acceptable in the view of performance and how should i create
the indexes?Hi

I think this is probably a candidate for a surrogate key.

John
"onder" <okeen1@.hotmail.com> wrote in message
news:9c3d3756.0309022355.10656428@.posting.google.c om...
> i have a master table with around 15 columns and i am trying to find
> the appropriate primary keys and indexes for the table.
> To make the records unique, i need to include two datetime columns (
> start and end dates ) and two integer columns ( attributes of the
> record ) to make up a composite primary key. Both of these four
> columns are found in the WHERE clause of my queries.
> Is it acceptable in the view of performance and how should i create
> the indexes?|||Hi John,

sorry for this but can you tell a little more on that?|||Hi

There is usually quite a debate in the newsgroups when surrogate keys are
mentioned. Basically it is when you add a column that you populate with
unique values, so you can reference a row by that instead of the 4 columns
you mentioned. This is a way to reduce the width of any table that refers to
your table with a foreign key (you would need all 4 columns replicated in
that table if you did not use a surrogate key). If you searched google for
"Surrogate SQLServer" it will turn up a few debates.

Quite often a surrogate key is given the IDENTITY property, so SQL Server
will populate the values for you,
http://msdn.microsoft.com/library/d...asp?frame=true
but it is possible to maintain the values your self.

HTH

John

"onder" <okeen1@.hotmail.com> wrote in message
news:9c3d3756.0309030711.5f4f582c@.posting.google.c om...
> Hi John,
> sorry for this but can you tell a little more on that?|||Ok about the surrogate keys. But i have an issue to ask:

In my 'select' queries, i always have the three columns which defines
a unique combination. So, i think the optimization of the table design
should bear this in mind.
If i add a surrogate key to the table to be a primary key, then i will
need to get that column to my select queries and then do the updates
based on that column.

What would you recommend for the index of the table?|||Hi

You would need to have the surrogate key in the JOIN or WHERE clause. This
could be totally transparent to the user. As a Primary Key there will
already be an index on the surrogate key column,

It is possible a covering index on the surrogate and candidate key columns
will be useful, but like any index you should investigate if it is being
used and if the speed improvements are worth while.

John

"onder" <okeen1@.hotmail.com> wrote in message
news:9c3d3756.0309080039.65cccc67@.posting.google.c om...
> Ok about the surrogate keys. But i have an issue to ask:
> In my 'select' queries, i always have the three columns which defines
> a unique combination. So, i think the optimization of the table design
> should bear this in mind.
> If i add a surrogate key to the table to be a primary key, then i will
> need to get that column to my select queries and then do the updates
> based on that column.
> What would you recommend for the index of the table?

composite primary key

How can i implement :
Foreign key of one table referencing composite primary key of other table?Here's a sample of ours...

Check out BOL though

ALTER TABLE [dbo].[WH_Proof] ADD
FOREIGN KEY
(
[Company_Name],
[Tax_Agency_ID],
[Region],
[Tax_Class],
[Sub_Region]
) REFERENCES [dbo].[WH_Proof_List] (
[Company_Name],
[Tax_Agency_ID],
[Region],
[Tax_Class],
[Sub_Region]
),
FOREIGN KEY
(
[Company_Name],
[Tax_Agency_ID],
[Region],
[Tax_Class],
[Proof_Year],
[Proof_Period]
) REFERENCES [dbo].[Disb_Proof] (
[Company_Name],
[Tax_Agency_ID],
[Region],
[Tax_Class],
[Proof_Year],
[Proof_Period]
),
FOREIGN KEY
(
[Period_Type]
) REFERENCES [dbo].[Period_Type] (
[Period_Type]
),
FOREIGN KEY
(
[Status]
) REFERENCES [dbo].[Proof_Status] (
[Status]
)
GO|||can i define the composite foreign key while creating the table?

Composite Primary Key

WHile designing a Database should one go for composite Primary Keys.
Or what are the Pros and Corns of Composite Primary Keys
ThanxNo, you should only use IDENTITY columns...|||Just kidding...

http://www.datamodel.org/

Define what it takes to uniquely identify a row in a table, no matter how many columns...

And then establish relationships between these objects...

Then you'll have a logical model...

How you physically implement is another story...but start there...|||Come on...I thought that was funny...

..."but what if I need to change the name of a City? Like when New Amsterdam became New York City...IDENTITY would be good there, no?"|||identity? heh, yes, i thought that was funny

and the delivery was excellent

create table constants
( id smallint not null primary key
, name varchar(5) not null
, constant double precision not null
)
insert into constants (name,constant)
values ('pi', 3.141592653589793)

always reference a row by its primary key

which for pi is 1 in the above case, since that was the first constant entered into the table

so whatever id number it gets when you insert that row in your table, just remember the id number and always use it in your sql

the benefit of this is that your sql will not have to change in case pi ever changes its name or value|||http://www.straightdope.com/classics/a3_341.html

A classic I had to look up once more....

Composite or Primary Key

How do determine within SQL Server if key is a Primary Key
or Composite Key (Take two or more fields to uniquely
adentify a record.)? List Primary Key properties?
Thank You,
JimHi,
If a primary key is constructed with more than 1 columns then it is named as
Composite primary key.
Use the system stored procedure to identify the primary key.
sp_primary_keys_rowset @.table_name
If we have multiple entries with Ordinal (1,2,3..) then it is Composite key.
Note: you can even use sp_help <table name> to get the key information.
Thanks
Hari
MCDBA
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:04ea01c3da31$7f6983e0$a401280a@.phx.gbl...

quote:

> How do determine within SQL Server if key is a Primary Key
> or Composite Key (Take two or more fields to uniquely
> adentify a record.)? List Primary Key properties?
> Thank You,
> Jim

Composite or Primary Key

composite operators

I was reading some sql server document, I though I see
the "composite operator" somewhere. But now I could not
find it anywhere. Is there such a term in SQL server? or
is there anything so that I can send to multiple
operations when a job succeed?
Thanks.
PeterHaven't tried it but pretty sure you could specify an exchange group as
the email for an operator.
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You can use SQLDMO for this. Add a project reference to the Microsoft
SQLDMO COM object and invoke the ListAvailableSQLServers method like the
example below.
SQLDMO.Application sqlDmoApplication = new SQLDMO.Application();
SQLDMO.NameList serverList;
serverList = sqlDmoApplication.ListAvailableSQLServers();
foreach(string serverName in serverList)
{
comboBoxServerList.Items.Add(serverName);
}
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:%23xfE36xSDHA.1552@.TK2MSFTNGP10.phx.gbl...
> Such a term does not exist in SQL server. When you define an operator,
it
> could be a single mailbox or a distribution list (a group of
mailboxes). You
> can xp_sendmail to either one.
>
> Richard
>
> "Peter" <peter.zhang@.noetix.com> wrote in message
> news:052301c3481f$a58049a0$a401280a@.phx.gbl...
> > I was reading some sql server document, I though I see
> > the "composite operator" somewhere. But now I could not
> > find it anywhere. Is there such a term in SQL server? or
> > is there anything so that I can send to multiple
> > operations when a job succeed?
> >
> > Thanks.
> >
> > Peter
>

composite nonclustered index

Hi everyone,
I have some problems on composite nonclustered indexes. I could not exatly understand their logic.
In my opininon, suppose that we have a table called Order and we create a composite nonclustered index on this table for OrderID column and OrderDate column. So I am using this query;

SELECT * FROM Order WHERE OrderID > 12 ORDER BY OrderDate
So in here, I think our first research is based on OrderID and ten after ordering our data pointer according to the OrderID and then our index is converted to an index which is based on OrderDate while performing ordering. So is this correct ?
Would you please explain this ?

Thanks

There would be an index seek on OrderID, then a bookmark lookup on the table to find the actual data. And then the order is done on the resultset by OrderDate.

You can see all this by highlight the query (in Query Analyzer) and press Ctrl+L.|||

i dont know what exactly it is that you don't understand.

lets start with the clustered and non clustered index. you can only create one clustered index per table. however there can be queries that may run against the clustered index so non clustered index was introduced ...

Clustered Indexes

There can be only one clustered index on a table or view, because the clustered index key physically sorts the table or view. This type of index is particularly efficient for queries, because data records—also known as data pages—are stored in the leaf level of the B-tree. The sort order and storage location of a clustered index is analogous to a dictionary in that the words in a dictionary are sorted alphabetically and definitions appear next to the words.

When you create a primary key constraint in a table that does not contain a clustered index, SQL Server will use the primary key column for the clustered index key. If a clustered index already exists in a table, a nonclustered index is created on the column defined with a primary key constraint. A column defined as the PRIMARY key is a useful index because the column values are guaranteed to be unique. Unique values create smaller B-trees than redundant values and thus make more efficient lookup structures.

Nonclustered Indexes

You can create up to 250 nonclustered indexes (or 249 nonclustered indexes and one clustered index) on a table or view. You must first create a unique clustered index on a view before you can create nonclustered indexes. This restriction does not apply to tables, however. A nonclustered index is analogous to an index in the back of a book. You can use a book's index to locate pages that match an index entry. The database uses a nonclustered index to locate matching records in the database.

If a clustered index does not exist on a table, the table is unsorted and is called a heap. A nonclustered index created on a heap contains pointers to table rows. Each entry in an index page contains a row ID (RID). The RID is a pointer to a table row in a heap, and it consists of a page number, a file number, and a slot number. If a clustered index exists on a table, the index pages of a nonclustered index contain clustered index keys rather than RIDs. An index pointer, whether it is a RID or an index key, is called a bookmark. .

|||

Hi,
First of all, thanks for your replies.
Infact, I ask that what is the difference between using two different nonclustered index for each column and using a composite nonclustered index for the above example(in my first reply)

Thanks

|||

There is huge difference merit.
If you have a composite index on (fCOL1, fCOL2) and a query

select * from T where fCOL2 = @.Value

Then the optimizer has no way to use your index. If you have 2 separate indexes, the optimizer will most likely use the index on fCOL2.
I'd reccomend that you refer to BOL for datailed coverage of indexes.

|||

Hi,
Thanks for your reply but it came to me a little bit confusing.
By saying this, "Then the optimizer has no way to use your index"; I could not understand whay you meant completely. Would you please make it clear ?
And besides, I read the BOL and this is the question that I could not find the answer.

Thanks

|||

Infact, What is the logic of composite nonclustered indexes ?
How can I imagine its algorithm in my mind ?

Thanks

|||Ok merit, but let's begin from clustered index.
Imagine an encyclopedia. Encyclopedia is sorted by topics, in alphabetic(lexicographic) order. This is the phisical or clustered index. It corresponds volume number and page number to each topic in it. E.g. if you want to search for topic "Database", what should you do? You look at the volumes. Each volume has the names of first and last topics written in hardcover. For example, the 3-rd volume of encylopedia might have topics "Cat" as first and "Easter" as last on the cover. As "Cat"<"Database"<"Easter" in ordinal lexicographical order, you can safely assume that the article "Database" is located in 3-rd volume.
This is analogous to clustered index root level lookup.
What do you do next? You open the volume somewhere in the middle, look at the topic of article. If it is, say, "Dog", then you have to look for "Database" in the first half of the book. If it is "Dao", then you should consider only the second part if the volume. Using this technique you can easily navigate to "Databases" in quite a few steps.
Now, how can we imagine a non-clustered index?
You surely know that each article in encyclopedia has an author. In the encylopedias I have been lucky to look at, each author is pointed at the end of the article.
Now, what if I want to navigate to all topics that author "merit" has written? Well, at the end of most books, there is a list called "index", which shows correspondence between <specific-names> and pages on which that <specific-name> was mentioned. For example, the philosophy book I have just finished, has a whole page of index for "Plato". It's like Plato-2,3, 5,6,10,20... That is, if I want to look for an article by "merit" in encyclopedia I have to just open the end-book index, and look at the pages where "merit" has articles.
To this point, there is a nuance that makes a difference between most of book indexes and non-clustered indexes in database. While indexes in books point to actual pages, database non-clustered index points to clustered index entries. This means that if merit has authored the article "Database" in encylopedia, which is located in page 253 of volume 3, the encyclopedia index contains the entry "merit-volume 3, page 253", while a non-clustered index contains the entry 'merit-Database'. So, navigating in non-clustered index structure is like navigating the authors list of encyclopedia. As soon as you have found the author, you get all the articles he has authored, and, using the clustered index, easily navigate to the desired article.
Now assume that the authors index is organized in format <Name-Surname>. In that case, it will contain entry Andranik Khachatryan-"Indexes pseudo-explained using Plato" ;).
Now you see why you cannot use the index to navigate to authors whose surname is "Khachatryan"? Because the authors index structure is sorted by Firstname, so, if you wish to look for a specific surname, you must look up all entries! In language of databases, this is always much less effective than just scanning the encylopedia ... oops ... database for Khachatryan.
The analogy I described here is not full and contains some inaccuracies. It works only at first appoximation.|||

REAL WORLD SCENARIO

lets say i have a junction table for student and subject

which is a many to many relation

and i call it table "studentsgrades"

here's how the table look like
recno | studentno | subjectid | prelim | midterm |final

studentgrade has "recordno" as primary key

now i want to impose a unique index on studentno and subjectid

where studentno is a foreign key from students table and subjectid is a foreign key

from subjects table. the solution requires that for every subject there could only be one instance of "student 1" though different students such as student 2 and 3 maybe in the same subject.

now all i have to do to achive this solution is to create a unique composite index on students and subjects.

"recno" is clustered pk because of many inserts and that recno is used to relate to table "adviserscomments" which has recno as fk.

now there are two requirements which are to create a report that will print the "students grades" for all of his subject and the "class record" which will display the grades of every student in the class

the "student grades" uses the query

select studentno,subjectid,prelim,midterm, finals where studentno = @.studentno

the class record uses this query

select subjectid,studentno,prelim,midterm,final where subjectid = @.subjectid

these query will not make use of the pk so to make the solution work fast an index must be made.

to make it realy fast i make a covering index A on studentno,subjectid,prelim,midterm,final

only "student grades" requirement will benefit from this index so

i have to make covering index B on
subjectid,studentno,prelim,midterm,final

A covering index, which is a form of a composite index, includes all of the columns referenced in SELECT, JOIN, and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL server doesn't have to look up the actual data in the table, reducing logical and/or physical I/O, and boosting performance.

to summarize composite non clustered index can be used to

1. implement uniqueness of more than 2 columns
2. implement covering index

for more reading on composite index pls consult this link

http://www.sql-server-performance.com/composite_indexes.asp

|||

Hi,
Indeed

thanks very much to both of you. Both of your post's are indeed

beneficial for me however, something still make me confused which I

would like to share with you.
I learned that while using composite

indexes, we must give importance to the order of the indexes. So if we

use where clause , we must put the column leftmost which we use in the

where clause. So, I am confused that what is the usage of other column in the index part in here ?

Thanks

|||

thats the index order of selectivity and sort-priority order starting from the left

A composite index is an index on two or more columns of a table. You should consider performance when creating a composite index because the order of columns in the index has a measurable effect on data retrieval speed. Generally, the most restrictive value should be placed first for optimum performance. However, the columns that will always be specified should be placed first.

About selectivity,Sometimes two or more columns, each with poor selectivity, can be combined to form a composite index with good selectivity.

|||

Hi,
Thanks for this reply again.
In here,
SELECT * FROM Order WHERE OrderID > 12 ORDER BY OrderDate

we create composite nonclustered index for both OrderID and OrderDate column.
Leftmost is OrderID.
So when our first research is happening(Where clause), the only nonclustered index which is used is OrderID index. And then, when we pass through the second search(ORDER BY clause), OrderDate index become activated.
So we can say that the seleection of indexes in composite indexes is determined according to the situation of the query at that time.

Hence, is this all correct ?

Thanks again for all clarifications.

Best wishes,
Mert

|||

Also,
we should not forget that first member of the composite index must be the most selective column for increasion of the performance.

Tuesday, March 20, 2012

Composite vs Single Primary Key

composite stored procedure

Composite Primay Key and foreign Key

composite primary keys versus composite unique indexes

COMPOSITE PRIMARY KEYS

composite primary key...

Monday, March 19, 2012

Composite primary key on a table variable?

Composite Primary Key in fact table

Composite Primary Key in fact table

Composite Primary Key in fact table

Composite Primary Key Constraint

Composite primary key autoincrement from 1

Composite primary key

composite primary key

composite primary key

Composite Primary Key

Composite or Primary Key

Composite or Primary Key

composite operators

composite nonclustered index

Clustered Indexes

Nonclustered Indexes

Sql Comparing two tables

Blog Archive

About Me