configure Defaultmaxbuffersize and DefaultmaxbufferRows

i want to improve the performance of my ssis...im left with last 3 bottleneks...3 huge tables ...here are the details :

table 1 :rows : 40 million +

size of each row..1 -2 KB

i had set the default max buffer to the max..100 MB and default max buffer rows to 100,000 ... considering that average row size is 1 kb.

table 2:

rows :17 million

row size : 280 bytes

again i have set the max buffer to 100 MB and default max buffer row to 300,000 .applyin the same logic..

table 3

rows: 59 million

size per row : 85 bytes..

as the row size was small...i made default max buffer row to 1000000 that'll still be less than 100 mb..which i had set as default max buffer size....

the first 2 have given a considerable speedup..but the 3rd one (as i feared) has gone dead slow...

ne ideas..and is one huge buffer better..or many small buffers...

Can you describe what transformations you're applying to the data from table 3? And what source and destination adapters you're using?|||its a oledb source and destination....the data flow tasks i mentioned (and few other similar) r called from a sequence container in control flow... the transformation is minimal...thouh there r 2 columns compared in the where clause(just 1 in others)..and i'm not sure of indexes on that as im not in control of that DB ... ya that may be a reason...but can u suggest anything apart from that..|||

I'd suggest that you try to determine the location of the bottleneck:

To check the source, try writing out the data from the source into a Raw File, and replacing the source adapter in your existing package with a Raw File Source adapter that's pointed at this file.

To check the destination, replace the existing destination adapter with an unconfigured Export Column transform.

To check the transforms, do both of the previous things.

Conecting linked servers using VB6.0

I have an SQL web site linked to may SQL Server. I am using the IP address t
o
do this.
Then I tried to make an ODBC to link VB6.0 to update some tables in the
linked server, but it always give me an error message saying that the server
doesn't exist or i do not have permitions. I am using the same permitions I
used to create the linked server, so i do not know what else i can do.
can any body help me?Why aren't you simply connecting directly to the server instead of through S
QL
Server's linked server?
If you want to connect to a single source but have data from multiple source
s,
then create stored proces and views that query the linked server. Granted, t
his
will be slower than querying the linked server directly.
HTH
Thomas
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:0C040BE5-A598-489C-9EC9-F681841EE641@.microsoft.com...
>I have an SQL web site linked to may SQL Server. I am using the IP address
to
> do this.
> Then I tried to make an ODBC to link VB6.0 to update some tables in the
> linked server, but it always give me an error message saying that the serv
er
> doesn't exist or i do not have permitions. I am using the same permitions
I
> used to create the linked server, so i do not know what else i can do.
> can any body help me?|||Of course, why didn't I think about it before?
Thanks a lot!
"Thomas" wrote:

> Why aren't you simply connecting directly to the server instead of through
SQL
> Server's linked server?
> If you want to connect to a single source but have data from multiple sour
ces,
> then create stored proces and views that query the linked server. Granted,
this
> will be slower than querying the linked server directly.
>
> HTH
>
> Thomas
>
> "Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in messag
e
> news:0C040BE5-A598-489C-9EC9-F681841EE641@.microsoft.com...
>
>sqlsql

Conditional SQL Query?

Iin SQL Server 2000 I have two tables that I need to join table
A and table B. The result set is a little tricky though. Table A has a set
of columns that are duplicated in table B. The reason is if there is no
data in these columns in table A, then that means that the data "defaults"
to the same named columns in table B. There is a many-to-one relationship
from A to B. What I would like to do is build this join query such that I
would return X number of columns using alias column names. I would like the
query to be able to populate those alias columns with the column values from
table A if there is data in those columns, but if there is no data then
populate those alias columns with the column values from table B. So in
essence, I have something like this:
Table A
ID
B_ID
A_1
A_2
Table B
ID
B_1
B_2
I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
return these alias columns:
COL_1: This retuns data from A_1 if data exists in this column, otherwise
returns data from B_1.
COL_2: This retuns data from A_2 if data exists in this column, otherwise
returns data from B_2.
Any help would be much appreciated.
Thanks!Try:
select
isnull (A_1, B_1)
, isnull (A2, B_2)
from
A
left join
B on B.ID = A.ID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"epigram" <nospam@.spammy.com> wrote in message
news:1112011902. 9f7c78e9def104fa4579b49849e7cce5@.bubbane
ws...
Iin SQL Server 2000 I have two tables that I need to join table
A and table B. The result set is a little tricky though. Table A has a set
of columns that are duplicated in table B. The reason is if there is no
data in these columns in table A, then that means that the data "defaults"
to the same named columns in table B. There is a many-to-one relationship
from A to B. What I would like to do is build this join query such that I
would return X number of columns using alias column names. I would like the
query to be able to populate those alias columns with the column values from
table A if there is data in those columns, but if there is no data then
populate those alias columns with the column values from table B. So in
essence, I have something like this:
Table A
ID
B_ID
A_1
A_2
Table B
ID
B_1
B_2
I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
return these alias columns:
COL_1: This retuns data from A_1 if data exists in this column, otherwise
returns data from B_1.
COL_2: This retuns data from A_2 if data exists in this column, otherwise
returns data from B_2.
Any help would be much appreciated.
Thanks!|||Hello, epigram!
You wrote on Mon, 28 Mar 2005 07:28:22 -0500:
e> I'd like to build a query that joins these tables on (A.B_ID = B.ID),
e> and return these alias columns:
e> COL_1: This retuns data from A_1 if data exists in this column,
e> otherwise returns data from B_1.
e> COL_2: This retuns data from A_2 if data exists in this column,
e> otherwise returns data from B_2.
SELECT B.ID,
COALESCE(A_1, B_1) as COL_1,
COALESCE(A_2, B_2) as COL_2,
FROM A JOIN B ON A.B_ID = B.ID
e> Any help would be much appreciated.
e> Thanks!
With best regards, Alexander Sinitsin. E-mail: al_sin[dog]ukr.net|||Did you read your last post?
http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"epigram" wrote:

> Iin SQL Server 2000 I have two tables that I need to join table
> A and table B. The result set is a little tricky though. Table A has a s
et
> of columns that are duplicated in table B. The reason is if there is no
> data in these columns in table A, then that means that the data "defaults"
> to the same named columns in table B. There is a many-to-one relationship
> from A to B. What I would like to do is build this join query such that I
> would return X number of columns using alias column names. I would like t
he
> query to be able to populate those alias columns with the column values fr
om
> table A if there is data in those columns, but if there is no data then
> populate those alias columns with the column values from table B. So in
> essence, I have something like this:
> Table A
> ID
> B_ID
> A_1
> A_2
> Table B
> ID
> B_1
> B_2
> I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
> return these alias columns:
> COL_1: This retuns data from A_1 if data exists in this column, otherwise
> returns data from B_1.
> COL_2: This retuns data from A_2 if data exists in this column, otherwise
> returns data from B_2.
> Any help would be much appreciated.
> Thanks!
>
>|||I couldn't. For some reason, my newsreader program was telling me that the
responses to that original post were unavailabe.
Thanks.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:90602A4C-1EB2-4693-BC4C-79D35C8FA263@.microsoft.com...
> Did you read your last post?
> [url]http://support.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserv
er.programming&mid=91835d68-563b-4260-a3ae-83dfdd2e8b2e&sloc=en-us&sloc=en-us[/url
]
>
> AMB
>
> "epigram" wrote:
>

Conditional SQL Query

I'm learning SQL Server 2000. I have two tables that I need to join, table
A and table B. The result set is a little tricky though. Table A has a set
of columns that are duplicated in table B. The reason is if there is no
data in these columns in table A, then that means that the data "defaults"
to the same named columns in table B. There is a many-to-one relationship
from A to B. What I would like to do is build this join query such that I
would return X number of columns using alias column names. I would like the
query to be able to populate those alias columns with the column values from
table A if there is data in those columns, but if there is no data then
populate those alias columns with the column values from table B. So in
essence, I have something like this:
Table A
ID
B_ID
A_1
A_2
Table B
ID
B_1
B_2
I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
return these alias columns:
COL_1: This retuns data from A_1 if data exists in this column, otherwise
returns data from B_1.
COL_2: This retuns data from A_2 if data exists in this column, otherwise
returns data from B_2.
Any help would be much appreciated.
Thanks!do your join and use the following in your select
select coalesce(A_1,B_1) as COL_1,coalesce(A_2,B_2) as COL_2
from ......

> COL_2: This retuns data from A_2 if data exists in this column, otherwise
> returns data from B_2.
"epigram" <nospam@.spammy.com> wrote in message
news:1111783245. cf22b6774ccf116a3d34ad39ea96b967@.bubbane
ws...
> I'm learning SQL Server 2000. I have two tables that I need to join,
> table A and table B. The result set is a little tricky though. Table A
> has a set of columns that are duplicated in table B. The reason is if
> there is no data in these columns in table A, then that means that the
> data "defaults" to the same named columns in table B. There is a
> many-to-one relationship from A to B. What I would like to do is build
> this join query such that I would return X number of columns using alias
> column names. I would like the query to be able to populate those alias
> columns with the column values from table A if there is data in those
> columns, but if there is no data then populate those alias columns with
> the column values from table B. So in essence, I have something like
> this:
> Table A
> ID
> B_ID
> A_1
> A_2
> Table B
> ID
> B_1
> B_2
> I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
> return these alias columns:
> COL_1: This retuns data from A_1 if data exists in this column, otherwise
> returns data from B_1.
> COL_2: This retuns data from A_2 if data exists in this column, otherwise
> returns data from B_2.
> Any help would be much appreciated.
> Thanks!
>|||but using a LEFT OUTER JOIN.
AMB
"Denis" wrote:

> do your join and use the following in your select
> select coalesce(A_1,B_1) as COL_1,coalesce(A_2,B_2) as COL_2
> from ......
>
>
> "epigram" <nospam@.spammy.com> wrote in message
> news:1111783245. cf22b6774ccf116a3d34ad39ea96b967@.bubbane
ws...
>
>

Conditional split with dependence?

I have setup a SSIS package that takes a flat file fixed width input, and stores it to two SQL server tables in the same database. The flat file contains two types of records, lets call them Type1 and Type2. The two types of records are formatted differently, and the first character determines what type the record is. I used a conditional split to send record type1 down one path, and type2 down the other. On each of those I use a derived column task to build all the fields and then store to the table with the OLE destination. I put any errors that occur (like truncation) into an error table by setting the "redirected row" feature vs "Fail Component". This all works well and I have no issues.

The dilema is as follows. Type1 is essentially a parent record and the Type2 record is a child. There is a shared primary key / foreign key relationship field. I want errors when processing type1 to cause the associated type2 to also be redirected to the error table vs being inserted.

If anyone has suggestions on how this could be done, reference articles, etc... please let me know.

Thanks.

Perhaps use a merge join on the error output of the Type1 flow together with the Type 2 data flow. Then use a conditional split to look for matches. If you have a match, you direct the Type 2 record (along with the Type1 record) down a separate error-handling flow. If you don't have a match, the Type2 records can be processed accordingly.|||

I'm trying your suggestion and I think it will work. But I am having an issue. I have my original flat file source, which I read into the SSIS package as just rows. So I do CRLF search to bring in as one column. I then send it to a derived column component after a conditional split to perform all the "substrings" to get the actual columns out of the data.

In order to do a merge join you must use sorted columns. I was able to set sorted column on the flat file data source and single column, which does me no good. I need to be able to set the sorted column on the derived columns after the data has been put into columns. Is there any way to set the sort column on a derived column? If I can do that it will solve my issues.

Thanks.

conditional relationship to multiple tables

Hello all.

I have what I think is an interesting database issue. In a nutshell, I want to know if it is possible and if so how one can setup a table to optionally relate to different tables. Let me explain, consider the following two tables (in simple psuedo-sql syntax):

Table Messages
------
ID - Int, PK
Name - varchar
Type - varchar

Table MessageFields
------
ID - Int, PK
PID - Int, FK
Name - varchar
Type - varchar
Size - int

Relationship:
MessageFields.PID relates to Messages.ID

These tables store information used to parse messages. They are related via a straight forward one-to-many relationship where the PID in MessageFields is the FK that relates to ID in Messages. In this simple kind of relationship, it is easy to setup referential integrity and cascaded deletes, etc...

Now, this worked fine as long as each message simply had it's fields and that was it. However, some fields can have sub-fields (if field is an array, it will have x number of subfields corresponding to each array element). Also, those sub-fields can have sub-fields. In fact, there is no set limit, although in practice it will probably only go 3 levels deep in subfields.

Anyway, the way to represent an arbirary subfield structure like this is to use a recursive table structure, where the FK field in the table (PID in this case) refers to the PK field in the same table (ID), like so:

Table MessageFields
------
ID - Int, PK
PID - Int, FK
Name - varchar
Type - varchar
Size - int

Relationship:
MessageFields.PID relates to MessageFields.ID

I believe you can even setup referential integrity and cascaded deletes on such a self-referecing, recursive setup.

The problem is, we still need to relate the MessageFields table to the Messages table. Sooo, the only way to do this that I have come up with is a setup like this:

Table Messages
------
ID - Int, PK
Name - varchar
Type - varchar

Table MessageFields
------
ID - Int, PK
PID - Int, FK
ParType - char(1)
Name - varchar
Type - varchar
Size - int

Relationship:
If ParType = 'M' then
MessageFields.PID relates to Messages.ID
elseif ParType = 'F' then
MessageFields.PID relates to MessageFields.ID
endif

Problem is, I don't think it is possible to setup a relationship (and referential integrity) on a condition like this.

So, my question is, is there a way to setup such a relationship? Is this even a good idea, or is there some standard, better way to setup these tables? Of course, I know I can just setup the tables this way and NOT use a defined relationship, and just be careful in the code that I'm not inserting something incorrect, but I'd rather not. One idea I did have was use a trigger to enforce my referential integrity. The trigger could check inserts into the messagefields table and test the value of partype, then test to see if the inserted row matches the appropriate column in the appropriate table. But before I go down that road, I'd like to see what someone else thinks.

Thanks much for any info/insight someone can give me on this.It's an interesting problem and one I've recently encountered. In our logical model we used subtypes. We have a 'type' of locator with 'subtypes' of physical, tele and postal. So we need to relate entities to locators (many-to-many) based on the type of locator.

entity:
entity_id

entity_locator_participation
entity_id
locator_id
locator_type

tele_locator
locator_id
phone_nbr
email_addr
etc...

postal_locator
locator_id
addr1
addr2
city
etc...

We need to be able to relate entity_locator_participation to tele_locator, postal_locator, or physical_locator depending on the value of the locator_type. For now, we just have no relationship and are maintaining it through code(stored procedures), but it's ugly. We've also thought about encapsulating the logic in triggers.

It's very similar to the probem you bring up. To the best of my knowledge, there is no way to do this, so I'm interested to see what other solutions people have come up with!

-Loach|||Yes, I don't think it is acutally going to be possible to define the conditional relationship directly in sqlserver. I'm leaning towards using a trigger, so at least you can still control the relationship at a db level, and the front end programmers don't have to perform the check. I tested the following trigger, which seems to work for check referential integrity on inserts:

CREATE TRIGGER [trigger1] ON [dbo].[MessageFields]
FOR INSERT
AS
begin
declare @.id int
declare @.partype varchar
declare @.pid int
declare @.result int

set @.id = (select id from inserted)
set @.pid = (select pid from inserted)
set @.partype = (select partype from inserted)

if @.partype = 'm' or @.partype = 'M'
begin
set @.result = (select count(*) from message where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in message!'
delete from messagefields where id = @.id
end
end
else if @.partype = 'f'
begin
set @.result = (select count(*) from messagefields where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in messagefields!'
delete from messagefields where id = @.id
end
end

end

Now I guess I need to setup the triggers for the update and especially the delete. The delete trigger will be a nested/recursive trigger. This shouldn't be a problem, as sql server allows like 32 levels of trigger nesting, and we'll never get that deep in our hierarchy.

Tony|||Originally posted by foxybanjo
Yes, I don't think it is acutally going to be possible to define the conditional relationship directly in sqlserver. I'm leaning towards using a trigger, so at least you can still control the relationship at a db level, and the front end programmers don't have to perform the check. I tested the following trigger, which seems to work for check referential integrity on inserts:

CREATE TRIGGER [trigger1] ON [dbo].[MessageFields]
FOR INSERT
AS
begin
declare @.id int
declare @.partype varchar
declare @.pid int
declare @.result int

set @.id = (select id from inserted)
set @.pid = (select pid from inserted)
set @.partype = (select partype from inserted)

if @.partype = 'm' or @.partype = 'M'
begin
set @.result = (select count(*) from message where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in message!'
delete from messagefields where id = @.id
end
end
else if @.partype = 'f'
begin
set @.result = (select count(*) from messagefields where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in messagefields!'
delete from messagefields where id = @.id
end
end

end

Now I guess I need to setup the triggers for the update and especially the delete. The delete trigger will be a nested/recursive trigger. This shouldn't be a problem, as sql server allows like 32 levels of trigger nesting, and we'll never get that deep in our hierarchy.

Tony

I had a similar problem in my database and I used this approach:

I wanted to create some tables to hold some survey template data. The main table held the name of the template and some other general information. There were a number of other tables representing each type of template. A one-many relationship was created between the template table and each of the survey tables. A template type Id in the template table would identify which survey table was to be used and this was set in a view. The table set up looks like:

Template Table:
TemplateID - PK
TemplateName
TemplateTypeID - identifies which template table you are using

Customer Survey Template
CustomerSurveyTemplateID - PK
TemplateID - FK (one - many with the template table)
{other customer survey template columns}

Void Log Survey Template
VoidLogSurveyTemplateID - PK
TemplateID - FK (one - many with the template table)
{other void log survey template columns)

A view is used for each survey filtered by the templatetypeId . I found this to be very flexible and versatile and easy to use in the front end (in my case an Access database with a data grid)

Conditional Lookup & Returning Undefined Values on Error

Hi,

I have a data flow task and trying to transform datas OLTP to STG db and i have lookup tables.

I do lookuping like this

first a lookup that lookup my table with connected input column parameter

second a derived column is connected to lookup's error output for when lookup can't find the value and this derived column returned "0" or "-1" this means that lookuped value can't find and insert this value to my table

third a union that union lookup and derived column

i want to ask this is there any different solution for doing this, because if i more than 5 or 6 lookup in my ssis package i add all of them derived columns and unions and when i change something i have to change or correct the unions step by step.

thanks

This link discusses two different ways to address this problem: http://blogs.msdn.com/ashvinis/archive/2005/08/04/447859.aspx

1. Lookup is configured to redirect rows that have no match in the reference table to a separate output (error output), then use a derived column to specify a default value, and finally merge both the lookup success output and the output of the derived column using a union all transform.

2. Lookup is configured to ignore lookup failures and pass the row out with null values for reference data. A derived column downstream of the lookup then checks for null reference data using 'ISNULL' and replaces the value with a default value.

The first way is what you're doing now; the second way is what you probably want to do.

With that said, making upstream changes in a data flow is almost always going to require tweaking to downstream tasks. That's just the nature of how SSIS relies on metadata...

Conditional Joins

Hi all,

I have 4 tables with the structure shown below

Main Table :

Create Table TestMain
(TestMainId INT , TestCompanyID INT )

Other Tables :

Create Table TestCompany1
(Id INT , TestCompanyID INT )

Create Table TestCompany2
(Id INT , TestCompanyID INT )

Create Table TestCompany3
(Id INT , TestCompanyID INT )

In this above tables.. I would have a record in the table TestMain and a entry for that specific record would be in any of the tables like TestCompany1,TestCompany2,TestCompany3

Sample Records :

In the table TestMain

1 1000
2 2000
3 3000
4 4000
5 5000
6 6000
7 7000

In the table TestCompany1

1 1000
2 6000

In the table TestCompany2

1 3000
2 4000
3 5000

In the table TestCompany3

1 7000

How do I join those tables and fetch the main record with its subsequent entry from the other tables ?

Thanks in advance,

HHADo you have defined any relationship between the tables..? It's basic requirement for data integrity.

Anyway you can join the two tables this way...

Select Testmain.TestMainId, TestMain.TestCompanyID From TestMain
JOIN TestCompany1 ON TestMain.TestCompanyID = TestCompany1.TestCompanyID

You can join more than two tables using different join types...|||Hi

You would need to use left joins and maybe COALESCE but it is hard to know without more details. The fact that you are doing this hints that your design may not be sound too (although it may be - this looks like a mock up yes?).

HTH|||I think your best bet would be to inner join against each table and UNION or UNION ALL the results - your design does look dubious though, why are you segmenting companies across 3 tables - do they have different attributes per collection or is there another reason?|||Hi all,

There is a main table say COMPANY and they other tables CompanyA , CompanyB , CompanyC.

The main table COMPANY has the general info about the company ( like address , contact info) and there are 3 BIT columns to indicate what all type of company it falls under.If it falls under A & B , then the relevant information
are stored in CompanyA & CompanyB.

Now I need to write a proc which gets few input parameters and searches
for the company details.

1. If no parameters where passed , I need to get all the company from the
COMPANY with relevant information from the CompanyA, CompanyB,CompanyC.

2.If I get a parameter which says I should fetch only companys falling under
CompanyA , I should be able to get them too.

Still , the DB is in production , I cant touch the design.

Thanks for all your help ,

HHA|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71565

Terrible design - I'm sure you know or are at least becoming aware of.

Playing with post three from the link (not efficient - there may be better solutions):

CREATE TABLE #TESTMAIN
(TESTMAINID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY1
(ID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY2
(ID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY3
(ID INT , TESTCOMPANYID INT )

INSERT #TESTMAIN
SELECT 1, 1000 UNION ALL
SELECT 2, 2000 UNION ALL
SELECT 3, 3000 UNION ALL
SELECT 4, 4000 UNION ALL
SELECT 5, 5000 UNION ALL
SELECT 6, 6000 UNION ALL
SELECT 7, 7000

INSERT #TESTCOMPANY1
SELECT 1, 1000 UNION ALL
SELECT 2, 6000

INSERT #TESTCOMPANY2
SELECT 1, 3000 UNION ALL
SELECT 2, 4000 UNION ALL
SELECT 3, 5000

INSERT #TESTCOMPANY3
SELECT 1, 7000

DECLARE @.CompanyOneOnly AS Bit
SET @.CompanyOneOnly = 1

SELECT *
FROM -- Relvent companys
(SELECT X1.TESTMAINID,
X1.TESTCOMPANYID,
ISNULL(X2.ID,0) AS Comp1,
ISNULL(X3.ID,0) AS Comp2,
ISNULL(X4.ID,0) AS Comp3
FROM #TESTMAIN X1
LEFT JOIN #TESTCOMPANY1 X2 ON X1.TESTCOMPANYID = X2.TESTCOMPANYID
LEFT JOIN #TESTCOMPANY2 X3 ON X1.TESTCOMPANYID = X3.TESTCOMPANYID
LEFT JOIN #TESTCOMPANY3 X4 ON X1.TESTCOMPANYID = X4.TESTCOMPANYID) AS DerT
WHERE CAST(Comp1 AS Bit) = @.CompanyOneOnly OR @.CompanyOneOnly = 0

DROP TABLE #TESTMAIN
DROP TABLE #TESTCOMPANY1
DROP TABLE #TESTCOMPANY2
DROP TABLE #TESTCOMPANY3

Conditional Join on Data Flow?

Hi,

Can we make conditional joins on the data flow?

Imagine i want to join 2 tables based on a value and an interval... Imagine i have a positioning number in one table and in the other i have a price, from_position, to_position and i want to join the to tables like position >= from_position and position <= to_position

Can we do this in SSIS?

Best Regards,

You can't do a join like that using a single component. To achieve this you might try doing a full join and then using a conditional split or a custom script to drop the rows that don't meet the desired join criteria.

Conditional Join

I have three tables ...

tblWine tblSpecialOfferWine tblSpecialOffer
ID Name ID WineID SpecialOfferID ID Name IsLive
===================== ============================ ==========================
1 Mouton Rothschild 1 1 1 1 February Offer 0
2 Lafite Rothschild 2 1 2 2 March Offer 1
3 Chateau Teyssier 3 2 1

... and the current query I am using is the following along with it's result set ...

SELECT
tblWine.ID AS WineID,
tblWine.Name AS WineName,
tblSpecialOffer.ID AS SpecialOfferID,
tblSpecialOffer.Name AS SpecialOfferName

FROM
tblWine
LEFT OUTER JOIN tblSpecialOfferWine ON tblSpecialOfferWine.WineID = tblWine.ID
LEFT OUTER JOIN tblSpecialOffer ON tblSpecialOfferWine.SpecialOfferID = tblSpecialOffer.ID

Results
WineID WineName SpecialOfferID SpecialOfferName
================================================== =========
1 Mouton Rothschild 1 February Offer
1 Mouton Rothschild 2 March Offer
2 Lafite Rothschild 1 February Offer
3 Chateau Teyssier NULL NULL

... but the result set I want is All wines and their associated specials offers but only show details of the offer if the offer is live like so ...

Results
WineID WineName SpecialOfferID SpecialOfferName
================================================== =========
1 Mouton Rothschild 2 March Offer
2 Lafite Rothschild NULL NULL
3 Chateau Teyssier NULL NULL

... I've tried putting where clauses like ...

WHERE tblSpecialOffer.IsLive = 1 OR tblSpecialOffer.IsLive IS NULL

... but then that hides the wines that were on a previously associated on a special offer but is no longer live (see Wine #2).

Any ideas on the query I should be using?

Note: The queries and data above were made off the top of my head so may contain mistakes.You are very close:
SELECT
tblWine.ID AS WineID,
tblWine.Name AS WineName,
tblSpecialOffer.ID AS SpecialOfferID,
tblSpecialOffer.Name AS SpecialOfferName

FROM
tblWine
LEFT OUTER JOIN tblSpecialOfferWine ON tblSpecialOfferWine.WineID = tblWine.ID
LEFT OUTER JOIN tblSpecialOffer
ON tblSpecialOfferWine.SpecialOfferID = tblSpecialOffer.ID
AND tblSpecialOffer.IsLive = 1|||Thanks, I did and it worked ... kind of. Here are the results ...

Results
WineID WineName SpecialOfferID SpecialOfferName
================================================== =========
1 Mouton Rothschild NULL NULL
1 Mouton Rothschild 2 March Offer
2 Lafite Rothschild NULL NULL
3 Chateau Teyssier NULL NULL

... I'd want it so it would only show Wine #1 once. So it should join rows from tblSpecialOfferWine if the associated special offer isn't live.|||Then you will nee a subquery:

SELECT
tblWine.ID AS WineID,
tblWine.Name AS WineName,
SpecialOffers.SpecialOfferID,
SpecialOffers.SpecialOfferName

FROM
tblWine
LEFT OUTER JOIN --SpecialOffers
(SELECT tblSpecialOfferWine.WineID
tblSpecialOffer.ID AS SpecialOfferID,
tblSpecialOffer.Name AS SpecialOfferName
FROM tblSpecialOfferWine ON tblSpecialOfferWine.WineID = tblWine.ID
INNER JOIN tblSpecialOffer
ON tblSpecialOfferWine.SpecialOfferID = tblSpecialOffer.ID
WHERE tblSpecialOffer.IsLive = 1) SpecialOffers
ON tblWine.WineID = SpecialOffers.WineID|||Works a treat! Thanks a lot :)

Conditional Join

Hello,
I have the following problem...
I am musing ADO.NET with SQL Server. I have a table that stores events
that are linked to different tables.. for example, lets suppose that I
have the following tables : Airplanes, Cars, Trains. Each table is
different, however, events produced by any of these are stored on a
single Event Table with the following fields:
EventID, OwnerTable, OwnerID, EventTime, Description.
Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
unique ID within the given table. I now want to create a SELECT
statement that shows all events and joins the particular row to the
correct record based on the OwnerTable and OwnerID to show common fields
among all. For example, assume that all three tables have a COLOR
field, the resulting query should produce the following:
Event Table
Event ID: 1
OwnerTable: Airplanes
OwnerID: 1
Color : Red ( This is the COLOR field on the Airplanes table with
AirplaneID = 1)
Event Table
Event ID: 2
OwnerTable: Trains
OwnerID: 1
Color : Blue ( This is the COLOR field on the Trains table with
TrainID = 1)
Thanks,
Jeronimo BertranHi J,
As per the info you have porvided I have created tables and inserted the
data.
The query you require is at the end.
Create table plane (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table car (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table train (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table Events (EventID Int Primary Key Identity, OwnerTable
Varchar(1000),
OwnerID Int, EventTime DateTime, Description Varchar(1000))
go
Insert Into plane values ('Blue')
go
Insert Into plane values ('Blue1')
go
Insert Into plane values ('Blue2')
go
Insert Into car values ('Red')
go
Insert Into car values ('Red1')
go
Insert Into car values ('Red2')
go
Insert Into train values ('Yellow')
go
Insert Into train values ('Yellow1')
go
Insert Into train values ('Yellow2')
go
Insert into Events Select 'Plane',ObjectID,GetDate(),'Planes has added this'
from plane
go
Insert into Events
Select 'Train',ObjectID,GetDate(),'Trains has added this' from train
Union
Select 'Car',ObjectID,GetDate(),'Cars has added this' from Car
go
--This is your query
Select Events.* , Case Events.OwnerTable
When 'Plane' Then Plane.Color
When 'Train' Then Train.Color
When 'Car' Then Car.Color
Else '{Blank}'
End
From Events Left Outer Join Plane on Events.OwnerId = Plane.ObjectID And
Events.OwnerTable = 'Plane'
Left Outer Join Train on Events.OwnerId = Train.ObjectID And
Events.OwnerTable = 'Train'
Left Outer Join Car on Events.OwnerId = Car.ObjectID And Events.OwnerTable
= 'Car'
Please respond if it solves your problem
Thanks,
Vishal Khajuria
Sungard SCT India
"Jeronimo Bertran" wrote:

> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran
>|||Hi Jeronimo Bertran,
As per the info you have provided, I have created table and populated them
with data. The query you are looking for is at end. I am giving you all the
scripts so that there is no misinterpertation.
Create table plane (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table car (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table train (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table Events (EventID Int Primary Key Identity, OwnerTable
Varchar(1000), OwnerID Int, EventTime DateTime, Description Varchar(1000))
GO
Insert Into plane values ('Blue')
GO
Insert Into plane values ('Blue1')
GO
Insert Into plane values ('Blue2')
GO
Insert Into car values ('Red')
GO
Insert Into car values ('Red1')
GO
Insert Into car values ('Red2')
GO
Insert Into train values ('Yellow')
GO
Insert Into train values ('Yellow1')
GO
Insert Into train values ('Yellow2')
GO
Insert into Events Select 'Plane',ObjectID,GetDate(),'Planes has added this'
from plane
GO
Insert into Events
Select 'Train',ObjectID,GetDate(),'Trains has added this' from train
Union
Select 'Car',ObjectID,GetDate(),'Cars has added this' from Car
GO
--This is the query you are lookin for
Select Events.* , Case Events.OwnerTable
When 'Plane' Then Plane.Color
When 'Train' Then Train.Color
When 'Car' Then Car.Color
Else '{Blank}'
End
From Events Left Outer Join Plane on Events.OwnerId = Plane.ObjectID And
Events.OwnerTable = 'Plane'
Left Outer Join Train on Events.OwnerId = Train.ObjectID And
Events.OwnerTable = 'Train'
Left Outer Join Car on Events.OwnerId = Car.ObjectID And Events.OwnerTable
= 'Car'
Please respond if it solves your problem.
Regard ,
Vishal Khajuria
Sungard SCT India
"Jeronimo Bertran" wrote:

> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran
>|||Jeronimo
SELECT <column lists> FROM Airplanes A JOIN Events E
ON A.EventId=E.EventId AND A.OwnerID=E.OwnerID
If it does not help please post DDL+ sample data + expected result
"Jeronimo Bertran" <jeronimo.bertran@.newsgroup.nospam> wrote in message
news:eexdtw7FFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran|||Try this:
SELECT E.eventid, E.ownertable, E.ownerid,
COALESCE(A.color, T.color) AS color
FROM Events AS E
LEFT JOIN AirPlanes AS A
ON E.ownerid = A.airplaneid
AND E.ownertable = 'Airplanes'
LEFT JOIN Trains AS T
ON E.ownerid = T.trainid
AND E.ownertable = 'Trains'
Alternatively, why not put those common columns in a single table
across all the types of transport and in the separate tables just have
columns that are specific to that subtype - that would greatly simplify
this type of query.
If Event is some kind of system-maintained audit trail then its maybe
reasonable to hold the applicable table name in there but otherwise I
would suggest that the table name is probably a poor way to identify
the entity. Don't you have identifying codes for the types "Airplanes",
"Trains", etc? In general its best not to mix data and metadata in a
table - doing so suggests that there may be something missing from the
data model.
David Portas
SQL Server MVP
--|||Thanks David,
The COALESCE will do the job. Yes I do have identifying codes for the
different types whcih I can use instead of the table name.|||Thanks Vishal,
Especially for being so thorough. Yor implementation solved my problem.

Conditional Join

I want to join 2 tables conditionally. One order needs to join with one
instruction. The three potential join fields are: Country, Exchange, and
Type. These fields are required in the Orders table but only Country is
required in Instructions. The data entry requirements of the application are
such that if an instruction has a Type it must have an Exchange.
The logic of the join is that:
1-if all three fields match, Country, Exchange, and Type then join those
records.
2-if two fields match, Country, and Exchange then join those records.
3-if one field matches, Country, then join those records.
My expected results given the sample data is as follows.
SELECT OrderID,InstructionID FROM Orders
JOIN ...
--Expected Results
OrderID,InstructionID
1,1
2,5
3,5
4,7
5,8
6,9
7,13
CREATE TABLE Orders
(
OrderID int NOT NULL,
Country char (3)NOT NULL,
Exchange char (3)NOT NULL,
Type char (3)NOT NULL,
)
CREATE TABLE Instructions
(
InstructionID int NOT NULL,
Country char (3) NOT NULL,
Exchange char (3) NULL,
Type char (3) NULL,
Instructions varchar (15)NOT NULL
)
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (1,'USA','NYS','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (2,'CAN','TSE','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (3,'CAN','TSE','Sel')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (4,'ESP','BAR','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (5,'ESP','MAD','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (6,'IRQ','BAG','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (7,'DUE','HAM','Buy')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(1,'USA','NYS','Buy','Instruction 1')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(2,'USA','NYS','Sel','Instruction 2')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(3,'USA','NYS',NULL,'Instruction 3')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(4,'USA',NULL,NULL,'Instruction 4')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(5,'CAN','TSE',NULL,'Instruction 5')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(6,'CAN','ALB',NULL,'Instruction 6')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(7,'ESP',NULL,NULL,'Instruction 7')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(8,'ESP','MAD',NULL,'Instruction 8')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(9,'IRQ','BAG','Buy','Instruction 9')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(10,'IRQ','BAG','Sel','Instruction 10 ')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(11,'DUE',NULL,NULL,'Instruction 11')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(12,'DUE','HAM',NULL,'Instruction 12')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(13,'DUE','HAM','Buy','Instruction 13')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(14,'DUE','HAM','Sel','Instruction 14')Terri (terri@.cybernets.com) writes:
> I want to join 2 tables conditionally. One order needs to join with one
> instruction. The three potential join fields are: Country, Exchange, and
> Type. These fields are required in the Orders table but only Country is
> required in Instructions. The data entry requirements of the application
> are such that if an instruction has a Type it must have an Exchange.
> The logic of the join is that:
> 1-if all three fields match, Country, Exchange, and Type then join those
> records.
> 2-if two fields match, Country, and Exchange then join those records.
> 3-if one field matches, Country, then join those records.
> My expected results given the sample data is as follows.
Thanks a lot for table and test data. This may not be the smartest
query, but it's easy to understand:
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
WHERE NOT EXISTS (SELECT *
FROM Instructions I2
WHERE O.Country = I2.Country
AND O.Exchange = I2.Exchange )
UNION ALL
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
AND O.Exchange = I.Exchange
WHERE NOT EXISTS (SELECT *
FROM Instructions I2
WHERE O.Country = I2.Country
AND O.Exchange = I2.Exchange
AND O.Type = I2.Type)
UNION ALL
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
AND O.Exchange = I.Exchange
AND O.Type = I.Type
ORDER BY OrderID
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|||>> I want to join 2 tables conditionally. <<
That makes NO sense as you have to join or not join a table.
Okay.
Gee, too bad that SQL and RDBMS has columns and not fields; rows are
not records. They are nothing alike in concept or execution. Also,
"type" is too vague to be a valid column name - "type" of
what? This is basics, damn it!!
xchange.
The logic of the join is that:
1-if all three fields [sic] match, Country, Exchange, and Type [sic]
then join those records [sic].
2-if two fields [sic] match, Country, and Exchange then join those
records [sic]. <
3-if one field [sic] matches, Country, then join those records [sic].
<<
Okay, try this:
SELECT ..
FROM Foo, Bar, etc.
WHERE CASE WHEN Foo.country_code= Bar.country_code
THEN 1 ELSE 0 END
+ CASE WHEN Foo.exchange_code = Bar.exchange_code
THEN 1 ELSE 0 END
+ CASE WHEN Foo.vague_type =Bar.vague_type
THEN 1 ELSE 0 END > 1 ;|||Terri
CREATE VIEW myView
AS
SELECT
OrderID, InstructionID,
I.Country ,I.Exchange ,I.Type FROM Orders O
JOIN Instructions I ON O.Country=COALESCE(I.Country,O.Country)
AND O.Exchange=COALESCE(I.Exchange,O.Exchange) AND
O.Type=COALESCE(I.Type,O.Type)
WHERE I.Exchange IS NOT NULL
--Final Select
SELECT OrderID,
InstructionID,
Country, Exchange, Type
FROM myView WHERE Type IS NOT NULL
UNION
SELECT OrderID,
InstructionID,
Country, Exchange, Type
FROM myView WHERE (SELECT COUNT(*) AS dp
FROM myView V WHERE OrderID=myView.OrderID ) =1
"Terri" <terri@.cybernets.com> wrote in message
news:dr3mjc$r6g$1@.reader2.nmix.net...
>I want to join 2 tables conditionally. One order needs to join with one
> instruction. The three potential join fields are: Country, Exchange, and
> Type. These fields are required in the Orders table but only Country is
> required in Instructions. The data entry requirements of the application
> are
> such that if an instruction has a Type it must have an Exchange.
> The logic of the join is that:
> 1-if all three fields match, Country, Exchange, and Type then join those
> records.
> 2-if two fields match, Country, and Exchange then join those records.
> 3-if one field matches, Country, then join those records.
> My expected results given the sample data is as follows.
> SELECT OrderID,InstructionID FROM Orders
> JOIN ...
>
> --Expected Results
> OrderID,InstructionID
> 1,1
> 2,5
> 3,5
> 4,7
> 5,8
> 6,9
> 7,13
> CREATE TABLE Orders
> (
> OrderID int NOT NULL,
> Country char (3)NOT NULL,
> Exchange char (3)NOT NULL,
> Type char (3)NOT NULL,
> )
> CREATE TABLE Instructions
> (
> InstructionID int NOT NULL,
> Country char (3) NOT NULL,
> Exchange char (3) NULL,
> Type char (3) NULL,
> Instructions varchar (15)NOT NULL
> )
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (1,'USA','NYS','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (2,'CAN','TSE','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (3,'CAN','TSE','Sel')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (4,'ESP','BAR','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (5,'ESP','MAD','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (6,'IRQ','BAG','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (7,'DUE','HAM','Buy')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (1,'USA','NYS','Buy','Instruction 1')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (2,'USA','NYS','Sel','Instruction 2')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (3,'USA','NYS',NULL,'Instruction 3')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (4,'USA',NULL,NULL,'Instruction 4')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (5,'CAN','TSE',NULL,'Instruction 5')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (6,'CAN','ALB',NULL,'Instruction 6')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (7,'ESP',NULL,NULL,'Instruction 7')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (8,'ESP','MAD',NULL,'Instruction 8')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (9,'IRQ','BAG','Buy','Instruction 9')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (10,'IRQ','BAG','Sel','Instruction 10 ')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (11,'DUE',NULL,NULL,'Instruction 11')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (12,'DUE','HAM',NULL,'Instruction 12')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (13,'DUE','HAM','Buy','Instruction 13')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (14,'DUE','HAM','Sel','Instruction 14')
>
>

Conditional Join

What I have are two different tables that I am going to join. Everything in
the first table should be pulled. The second table should only pull the
information when the field ACTION equals the word "ORDERED". Is there a way
to join two tables on just a literal
Table A
Tran Date_ORD Date_Rec ID Rate
4756 10/23/05 99/99/99 J234 1.26
7364 10/23/05 10/26/05 H342 3.23
9834 09/23/04 10 05/04 J234 1.74
8374 08/29/05 09/03/05 K834 2.85
6756 09/21/05 99/99/99 J234 4.26
7263 11/01/05 11/06/05 H342 2.23
1844 10/02/05 10/05/05 J234 3.74
2333 06/27/05 07/01/05 K834 5.85
Table B
Tran Action
4756 ORDERED
7364 RECEIVE
9834 CANCELE
8374 BACKORD
6756 ORDERED
7263 RECEIVE
1844 RECEIVE
Output
4756 10/23/05 10/25/05 J234 1.26 ORDERED
7364 10/23/05 10/26/05 H342 3.23
9834 09/23/04 10 05/04 J234 1.74
8374 08/29/05 09/03/05 K834 2.85
6756 09/21/05 09/24/05 J234 4.26 ORDERED
7263 11/01/05 11/06/05 H342 2.23
1844 10/02/05 10/05/05 J234 3.74
2333 06/27/05 07/01/05 K834 5.85
On Fri, 11 Nov 2005 12:17:02 -0800, Daniell wrote:

>What I have are two different tables that I am going to join. Everything in
>the first table should be pulled. The second table should only pull the
>information when the field ACTION equals the word "ORDERED". Is there a way
>to join two tables on just a literal
>Table A
>Tran Date_ORD Date_Rec ID Rate
>4756 10/23/05 99/99/99 J234 1.26
>7364 10/23/05 10/26/05 H342 3.23
>9834 09/23/04 10 05/04 J234 1.74
>8374 08/29/05 09/03/05 K834 2.85
>6756 09/21/05 99/99/99 J234 4.26
>7263 11/01/05 11/06/05 H342 2.23
>1844 10/02/05 10/05/05 J234 3.74
>2333 06/27/05 07/01/05 K834 5.85
>Table B
>Tran Action
>4756 ORDERED
>7364 RECEIVE
>9834 CANCELE
>8374 BACKORD
>6756 ORDERED
>7263 RECEIVE
>1844 RECEIVE
>Output
>4756 10/23/05 10/25/05 J234 1.26 ORDERED
>7364 10/23/05 10/26/05 H342 3.23
>9834 09/23/04 10 05/04 J234 1.74
>8374 08/29/05 09/03/05 K834 2.85
>6756 09/21/05 09/24/05 J234 4.26 ORDERED
>7263 11/01/05 11/06/05 H342 2.23
>1844 10/02/05 10/05/05 J234 3.74
>2333 06/27/05 07/01/05 K834 5.85
Hi Daniell,
I think that this is what you want:
SELECT a.Tran, a.Date_ORD, a.Date_Rec, a.ID, a.Rate,
COALESCE(b.Action, '') AS Action
FROM TableA AS a
LEFT OUTER JOIN TableB AS b
ON b.Tran = a.Tran
AND b.Action = 'ORDERED'
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Conditional join

Given 2 tables, Transactions and Instructions, my goal is to join a
transaction to its appropriate instruction. The 3 fields I can possibly join
on are: Exchange, SecType, and Country. All of these fields are nullable
in the instruction table. Not my design, I can't change this.
How can I join these tables so that any null values are disregarded. Given
my sample data I want to join the following records.
TransactionID,InstructionID
1,1
2,4
3,7
4,10
CREATE TABLE [dbo].[#Transactions] (
[TransactionID] [int] NOT NULL ,
[Exchange] [char] (3) NOT NULL ,
[SecType] [char] (4) NOT NULL ,
[Country] [char] (3) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
(1,'NYS','COM','USA')
INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
(2,'LSE','COM','GBR')
INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
(3,'TSE','ADR','CAN')
INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
(4,'NAS','COM','USA')
CREATE TABLE [dbo].[#Instructions] (
[TransactionID] [int] NOT NULL ,
[Exchange] [char] (3)NOT NULL ,
[SecType] [char] (4),
[Country] [char] (3),
[Instruction][char] (25)
) ON [PRIMARY]
GO
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(1,'NYS',NULL,NULL,'Instruction#1')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(2,'NYS','ADR',NULL,'Instruction#2')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(3,'LSE','ADR','GBR','Instruction#3')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(4,'LSE','COM',NULL,'Instruction#4')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(5,'LSE','COM','IRL','Instruction#5')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(6,'TSE','COM','CAN','Instruction#6')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(7,'TSE','ADR','CAN','Instruction#7')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(8,'TSE',NULL,NULL,'Instruction#8')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(9,'NAS',NULL,NULL,'Instruction#9')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(10,'NAS',NULL,'USA','Instruction#10')Terri wrote:
> Given 2 tables, Transactions and Instructions, my goal is to join a
> transaction to its appropriate instruction. The 3 fields I can possibly jo
in
> on are: Exchange, SecType, and Country. All of these fields are nullable
> in the instruction table. Not my design, I can't change this.
> How can I join these tables so that any null values are disregarded. Given
> my sample data I want to join the following records.
> TransactionID,InstructionID
> 1,1
> 2,4
> 3,7
> 4,10
> CREATE TABLE [dbo].[#Transactions] (
> [TransactionID] [int] NOT NULL ,
> [Exchange] [char] (3) NOT NULL ,
> [SecType] [char] (4) NOT NULL ,
> [Country] [char] (3) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
> (1,'NYS','COM','USA')
> INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
> (2,'LSE','COM','GBR')
> INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
> (3,'TSE','ADR','CAN')
> INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
> (4,'NAS','COM','USA')
> CREATE TABLE [dbo].[#Instructions] (
> [TransactionID] [int] NOT NULL ,
> [Exchange] [char] (3)NOT NULL ,
> [SecType] [char] (4),
> [Country] [char] (3),
> [Instruction][char] (25)
> ) ON [PRIMARY]
> GO
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (1,'NYS',NULL,NULL,'Instruction#1')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (2,'NYS','ADR',NULL,'Instruction#2')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (3,'LSE','ADR','GBR','Instruction#3')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (4,'LSE','COM',NULL,'Instruction#4')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (5,'LSE','COM','IRL','Instruction#5')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (6,'TSE','COM','CAN','Instruction#6')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (7,'TSE','ADR','CAN','Instruction#7')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (8,'TSE',NULL,NULL,'Instruction#8')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (9,'NAS',NULL,NULL,'Instruction#9')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (10,'NAS',NULL,'USA','Instruction#10')
As I expect you realise, both tables look like they badly need a
redesign. Meantime, try this:
SELECT T.transactionid,
COALESCE(I1.instructionid, I2.instructionid,
I3.instructionid, I4.instructionid)
FROM #transactions AS T
LEFT JOIN #instructions AS I1
ON T.exchange = I1.exchange
AND T.sectype = I1.sectype
AND T.country = I1.country
LEFT JOIN #instructions AS I2
ON I2.sectype IS NULL
AND T.exchange = I2.exchange
AND T.country = I2.country
LEFT JOIN #instructions AS I3
ON I3.country IS NULL
AND T.exchange = I3.exchange
AND T.sectype = I3.sectype
LEFT JOIN #instructions AS I4
ON I4.sectype IS NULL
AND I4.country IS NULL
AND T.exchange = I4.exchange ;
David Portas
SQL Server MVP
--

Conditional inserts in trigger

Can I have a trigger that inserts into 1 of 3 different tables based on a
column being inserted on the driving table?
For example, I issue
INSERT INTO dbo.People (SSN, CategoryCode)
VALUES (123456789, 3)
If CategoryCode inserted is 3, 10 or 11 then I need to
INSERT INTO dbo.ClientInfo (PeopleID)
VALUES (inserted.PeopleID)
If CategoryCode inserted is 1 then I need to
INSERT INTO dbo.ApplicantInfo (PeopleID)
VALUES (inserted.PeopleID)
etc.
One point that may or may not be important is that the original PeopleID is
assigned in an existing insert trigger and is a random number.
Thanks.
Davidyes - see BOL for more on CREATE TRIGGER, but e.g.
create trigger yourtrigger on People for insert
as
begin
insert into dbo.ApplicantInfo (PeopleID)
select PeopleID
from inserted
where CategoryCode=1
insert into dob.ClientInfo (PeopleID)
select PeopleID
from inserted
where CategoryCode in (3, 10, 11)
end
David Chase wrote:
> Can I have a trigger that inserts into 1 of 3 different tables based on a
> column being inserted on the driving table?
> For example, I issue
> INSERT INTO dbo.People (SSN, CategoryCode)
> VALUES (123456789, 3)
> If CategoryCode inserted is 3, 10 or 11 then I need to
> INSERT INTO dbo.ClientInfo (PeopleID)
> VALUES (inserted.PeopleID)
> If CategoryCode inserted is 1 then I need to
> INSERT INTO dbo.ApplicantInfo (PeopleID)
> VALUES (inserted.PeopleID)
> etc.
> One point that may or may not be important is that the original PeopleID i
s
> assigned in an existing insert trigger and is a random number.
> Thanks.
> David
>|||That doesn't work. I get an error when it tries to create ClientInfo
because ClientInfo table has referrential integrity rule that requires
matching record in People table. Evidently, ref. integrity check does not
know that People table record exists yet. Below is my trigger code, if that
helps.
CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT PersonID FROM inserted)
UPDATE People SET PersonID = @.randc WHERE PersonID = @.newc
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:uHtw5DHHGHA.1180@.TK2MSFTNGP09.phx.gbl...
> yes - see BOL for more on CREATE TRIGGER, but e.g.
> create trigger yourtrigger on People for insert
> as
> begin
> insert into dbo.ApplicantInfo (PeopleID)
> select PeopleID
> from inserted
> where CategoryCode=1
> insert into dob.ClientInfo (PeopleID)
> select PeopleID
> from inserted
> where CategoryCode in (3, 10, 11)
> end
> David Chase wrote:|||David Chase (dlchase@.lifetimeinc.com) writes:
> That doesn't work. I get an error when it tries to create ClientInfo
> because ClientInfo table has referrential integrity rule that requires
> matching record in People table. Evidently, ref. integrity check does
> not know that People table record exists yet. Below is my trigger code,
> if that helps.
Set up the FK to have UPDATE ON CASCADE.
Or instead of an UPDATE, perform first an INSERT, update the childre,
and then delete the original.

> CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
> SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT PersonID FROM inserted)
> UPDATE People SET PersonID = @.randc WHERE PersonID = @.newc
Keep in mind that a trigger fires once per statement, and thus inserted
can hold many rows.
A better bet for a random number is probably checksum(newid()).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Conditional index creation

In my stored proc, I create a bunch of temp tables and when the temp
table exceeds 1000 rows, I create an index on one of the rows. So
basically:
insert #ttt
select * from bbb
if @.@.ROWCOUNT > 1000 begin
Create NonClustered Index #ttt_IX1 on #ttt (ID)
end
My question is whether the conditional creation of the index messes up
the SQL engine. Would it not create an optimal plan because it doesn't
know for sure whether an Index will be there?
Thanks.
Creating an index over a table causes its schema to change, and in turn this
causes queries that reference the table to be recompiled. So, the short
answer to "will the conditional index creation mess up the SQL engine" is
no.
SQL Server will first compile the procedure, and then start executing it. If
the schema of a table changes between the compilation and execution of a
statement referencing it, the statement will be recompiled.
Actually the behavior changed significantly between SQL 2000 and 2005. In
2000, the recompilations would affect the entire batch or procedure. A
significant improvement has been made in SQL 2005 with statement-level
recompiles. As the name suggests, in SQL 2005 only the affected statements
are recompiled, rather than the entire batch or procedure.
For more information on the subject, we have a very good whitepaper here:
http://www.microsoft.com/technet/pro...05/recomp.mspx
The consequence of what you are doing is that if you interleave executions
of the procedure that do not cause the index creation with others where the
index is created, you will incur in a significant number of recompiles,
because the schema of the temp table won't match the previous compiled plan.
In SQL 2000, this will be exacerbated with the lack of statement level
recompiles. This might easily negate the benefits of saving the overhead of
creating an index when the table is small. Also, creating an index on a
small table is a low overhead operation anyway. I'd consider always creating
the index, and seeing if you can make it part of the table definition
altogether if applicable.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uVQnSFN5FHA.3540@.TK2MSFTNGP10.phx.gbl...
> In my stored proc, I create a bunch of temp tables and when the temp table
> exceeds 1000 rows, I create an index on one of the rows. So basically:
> insert #ttt
> select * from bbb
> if @.@.ROWCOUNT > 1000 begin
> Create NonClustered Index #ttt_IX1 on #ttt (ID)
> end
> My question is whether the conditional creation of the index messes up the
> SQL engine. Would it not create an optimal plan because it doesn't know
> for sure whether an Index will be there?
> Thanks.
|||The indexes I was talking about are being created on a temp table that
was created inside a stored proc. Would that cause any repercussions?
Stefano Stefani [MSFT] wrote:
> Creating an index over a table causes its schema to change, and in turn this
> causes queries that reference the table to be recompiled. So, the short
> answer to "will the conditional index creation mess up the SQL engine" is
> no.
> SQL Server will first compile the procedure, and then start executing it. If
> the schema of a table changes between the compilation and execution of a
> statement referencing it, the statement will be recompiled.
> Actually the behavior changed significantly between SQL 2000 and 2005. In
> 2000, the recompilations would affect the entire batch or procedure. A
> significant improvement has been made in SQL 2005 with statement-level
> recompiles. As the name suggests, in SQL 2005 only the affected statements
> are recompiled, rather than the entire batch or procedure.
> For more information on the subject, we have a very good whitepaper here:
> http://www.microsoft.com/technet/pro...05/recomp.mspx
> The consequence of what you are doing is that if you interleave executions
> of the procedure that do not cause the index creation with others where the
> index is created, you will incur in a significant number of recompiles,
> because the schema of the temp table won't match the previous compiled plan.
> In SQL 2000, this will be exacerbated with the lack of statement level
> recompiles. This might easily negate the benefits of saving the overhead of
> creating an index when the table is small. Also, creating an index on a
> small table is a low overhead operation anyway. I'd consider always creating
> the index, and seeing if you can make it part of the table definition
> altogether if applicable.
>
|||No functional repercussions - everything will work and nothing will break.
But like i wrote below, it will likely trigger a high number of recompiles,
which in turn can negatively affect performances.
It might be worth for you trying with a version of the stored procedure
where the index is always created, and compare performances in your workload
against the current version you have.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uLP6xOV5FHA.3760@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> The indexes I was talking about are being created on a temp table that was
> created inside a stored proc. Would that cause any repercussions?
>
> Stefano Stefani [MSFT] wrote:

Conditional index creation

In my stored proc, I create a bunch of temp tables and when the temp
table exceeds 1000 rows, I create an index on one of the rows. So
basically:
insert #ttt
select * from bbb
if @.@.ROWCOUNT > 1000 begin
Create NonClustered Index #ttt_IX1 on #ttt (ID)
end
My question is whether the conditional creation of the index messes up
the SQL engine. Would it not create an optimal plan because it doesn't
know for sure whether an Index will be there?
Thanks.Creating an index over a table causes its schema to change, and in turn this
causes queries that reference the table to be recompiled. So, the short
answer to "will the conditional index creation mess up the SQL engine" is
no.
SQL Server will first compile the procedure, and then start executing it. If
the schema of a table changes between the compilation and execution of a
statement referencing it, the statement will be recompiled.
Actually the behavior changed significantly between SQL 2000 and 2005. In
2000, the recompilations would affect the entire batch or procedure. A
significant improvement has been made in SQL 2005 with statement-level
recompiles. As the name suggests, in SQL 2005 only the affected statements
are recompiled, rather than the entire batch or procedure.
For more information on the subject, we have a very good whitepaper here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
The consequence of what you are doing is that if you interleave executions
of the procedure that do not cause the index creation with others where the
index is created, you will incur in a significant number of recompiles,
because the schema of the temp table won't match the previous compiled plan.
In SQL 2000, this will be exacerbated with the lack of statement level
recompiles. This might easily negate the benefits of saving the overhead of
creating an index when the table is small. Also, creating an index on a
small table is a low overhead operation anyway. I'd consider always creating
the index, and seeing if you can make it part of the table definition
altogether if applicable.
--
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uVQnSFN5FHA.3540@.TK2MSFTNGP10.phx.gbl...
> In my stored proc, I create a bunch of temp tables and when the temp table
> exceeds 1000 rows, I create an index on one of the rows. So basically:
> insert #ttt
> select * from bbb
> if @.@.ROWCOUNT > 1000 begin
> Create NonClustered Index #ttt_IX1 on #ttt (ID)
> end
> My question is whether the conditional creation of the index messes up the
> SQL engine. Would it not create an optimal plan because it doesn't know
> for sure whether an Index will be there?
> Thanks.|||The indexes I was talking about are being created on a temp table that
was created inside a stored proc. Would that cause any repercussions?
Stefano Stefani [MSFT] wrote:
> Creating an index over a table causes its schema to change, and in turn this
> causes queries that reference the table to be recompiled. So, the short
> answer to "will the conditional index creation mess up the SQL engine" is
> no.
> SQL Server will first compile the procedure, and then start executing it. If
> the schema of a table changes between the compilation and execution of a
> statement referencing it, the statement will be recompiled.
> Actually the behavior changed significantly between SQL 2000 and 2005. In
> 2000, the recompilations would affect the entire batch or procedure. A
> significant improvement has been made in SQL 2005 with statement-level
> recompiles. As the name suggests, in SQL 2005 only the affected statements
> are recompiled, rather than the entire batch or procedure.
> For more information on the subject, we have a very good whitepaper here:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> The consequence of what you are doing is that if you interleave executions
> of the procedure that do not cause the index creation with others where the
> index is created, you will incur in a significant number of recompiles,
> because the schema of the temp table won't match the previous compiled plan.
> In SQL 2000, this will be exacerbated with the lack of statement level
> recompiles. This might easily negate the benefits of saving the overhead of
> creating an index when the table is small. Also, creating an index on a
> small table is a low overhead operation anyway. I'd consider always creating
> the index, and seeing if you can make it part of the table definition
> altogether if applicable.
>|||No functional repercussions - everything will work and nothing will break.
But like i wrote below, it will likely trigger a high number of recompiles,
which in turn can negatively affect performances.
It might be worth for you trying with a version of the stored procedure
where the index is always created, and compare performances in your workload
against the current version you have.
--
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uLP6xOV5FHA.3760@.TK2MSFTNGP14.phx.gbl...
> The indexes I was talking about are being created on a temp table that was
> created inside a stored proc. Would that cause any repercussions?
>
> Stefano Stefani [MSFT] wrote:
>> Creating an index over a table causes its schema to change, and in turn
>> this causes queries that reference the table to be recompiled. So, the
>> short answer to "will the conditional index creation mess up the SQL
>> engine" is no.
>> SQL Server will first compile the procedure, and then start executing it.
>> If the schema of a table changes between the compilation and execution of
>> a statement referencing it, the statement will be recompiled.
>> Actually the behavior changed significantly between SQL 2000 and 2005. In
>> 2000, the recompilations would affect the entire batch or procedure. A
>> significant improvement has been made in SQL 2005 with statement-level
>> recompiles. As the name suggests, in SQL 2005 only the affected
>> statements are recompiled, rather than the entire batch or procedure.
>> For more information on the subject, we have a very good whitepaper here:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> The consequence of what you are doing is that if you interleave
>> executions of the procedure that do not cause the index creation with
>> others where the index is created, you will incur in a significant number
>> of recompiles, because the schema of the temp table won't match the
>> previous compiled plan. In SQL 2000, this will be exacerbated with the
>> lack of statement level recompiles. This might easily negate the benefits
>> of saving the overhead of creating an index when the table is small.
>> Also, creating an index on a small table is a low overhead operation
>> anyway. I'd consider always creating the index, and seeing if you can
>> make it part of the table definition altogether if applicable.

Conditional index creation

In my stored proc, I create a bunch of temp tables and when the temp
table exceeds 1000 rows, I create an index on one of the rows. So
basically:
insert #ttt
select * from bbb
if @.@.ROWCOUNT > 1000 begin
Create NonClustered Index #ttt_IX1 on #ttt (ID)
end
My question is whether the conditional creation of the index messes up
the SQL engine. Would it not create an optimal plan because it doesn't
know for sure whether an Index will be there?
Thanks.Creating an index over a table causes its schema to change, and in turn this
causes queries that reference the table to be recompiled. So, the short
answer to "will the conditional index creation mess up the SQL engine" is
no.
SQL Server will first compile the procedure, and then start executing it. If
the schema of a table changes between the compilation and execution of a
statement referencing it, the statement will be recompiled.
Actually the behavior changed significantly between SQL 2000 and 2005. In
2000, the recompilations would affect the entire batch or procedure. A
significant improvement has been made in SQL 2005 with statement-level
recompiles. As the name suggests, in SQL 2005 only the affected statements
are recompiled, rather than the entire batch or procedure.
For more information on the subject, we have a very good whitepaper here:
http://www.microsoft.com/technet/pr...005/recomp.mspx
The consequence of what you are doing is that if you interleave executions
of the procedure that do not cause the index creation with others where the
index is created, you will incur in a significant number of recompiles,
because the schema of the temp table won't match the previous compiled plan.
In SQL 2000, this will be exacerbated with the lack of statement level
recompiles. This might easily negate the benefits of saving the overhead of
creating an index when the table is small. Also, creating an index on a
small table is a low overhead operation anyway. I'd consider always creating
the index, and seeing if you can make it part of the table definition
altogether if applicable.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uVQnSFN5FHA.3540@.TK2MSFTNGP10.phx.gbl...
> In my stored proc, I create a bunch of temp tables and when the temp table
> exceeds 1000 rows, I create an index on one of the rows. So basically:
> insert #ttt
> select * from bbb
> if @.@.ROWCOUNT > 1000 begin
> Create NonClustered Index #ttt_IX1 on #ttt (ID)
> end
> My question is whether the conditional creation of the index messes up the
> SQL engine. Would it not create an optimal plan because it doesn't know
> for sure whether an Index will be there?
> Thanks.|||The indexes I was talking about are being created on a temp table that
was created inside a stored proc. Would that cause any repercussions?
Stefano Stefani [MSFT] wrote:
> Creating an index over a table causes its schema to change, and in turn th
is
> causes queries that reference the table to be recompiled. So, the short
> answer to "will the conditional index creation mess up the SQL engine" is
> no.
> SQL Server will first compile the procedure, and then start executing it.
If
> the schema of a table changes between the compilation and execution of a
> statement referencing it, the statement will be recompiled.
> Actually the behavior changed significantly between SQL 2000 and 2005. In
> 2000, the recompilations would affect the entire batch or procedure. A
> significant improvement has been made in SQL 2005 with statement-level
> recompiles. As the name suggests, in SQL 2005 only the affected statements
> are recompiled, rather than the entire batch or procedure.
> For more information on the subject, we have a very good whitepaper here:
> http://www.microsoft.com/technet/pr...005/recomp.mspx
> The consequence of what you are doing is that if you interleave executions
> of the procedure that do not cause the index creation with others where th
e
> index is created, you will incur in a significant number of recompiles,
> because the schema of the temp table won't match the previous compiled pla
n.
> In SQL 2000, this will be exacerbated with the lack of statement level
> recompiles. This might easily negate the benefits of saving the overhead o
f
> creating an index when the table is small. Also, creating an index on a
> small table is a low overhead operation anyway. I'd consider always creati
ng
> the index, and seeing if you can make it part of the table definition
> altogether if applicable.
>|||No functional repercussions - everything will work and nothing will break.
But like i wrote below, it will likely trigger a high number of recompiles,
which in turn can negatively affect performances.
It might be worth for you trying with a version of the stored procedure
where the index is always created, and compare performances in your workload
against the current version you have.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uLP6xOV5FHA.3760@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> The indexes I was talking about are being created on a temp table that was
> created inside a stored proc. Would that cause any repercussions?
>
> Stefano Stefani [MSFT] wrote:

Concerning .net and SQL Procedures

Recently i had to write a script in sql to compare multiple tables to get a result of items that do not conform to certain business logic. In doing so i wrote all of this information into a sql parameter which branches out to a few other parameters within the parameter.

Now if you need the code just let me ask, but this is a general question to see if it has occured for anyone else.

The problem i am recieving is when i access the code from a .net windows application it tells me:

Error Message:
Insert Error: Column name or number of supplied values does not match table definition.
Insert Error: Column name or number of supplied values does not match table definition.

Procedure Errored On: val_GetDuplicateItemsFromAssignment
Line Number: 16

However when i run the sql parameter within SQL it accesses it just find. This is using the same parameter values.

Does anyone know why this could be happening?

Please do show the code used to insert the values.

Conceptual question about "dimension usage"

I have a 2 dimensions:

UNDERLYING 0-8 INSTRUMENT [one underlying has zero or many instruments]

then I have several fact tables, say FACT1, FACT2, FACT3, which reference instrument_id as a foreign key.

Is it the case that in order to aggregate the fact tables by underlying, I need to define a "Referenced' relationship using the dimension usage tab for each fact table separately?

Either I'm missing something (probable!) but this seems unnecessary to me. Why can't I just define the UNDERLYING dimension as the parent of the INSTRUMENT dimension, and then every time the instrument dimension gets joined to the fact table, it simply follows that the corresponding fact can be rolled up by <correction>underlying [was: instrument]</correction>.

Any clarifications gratefully received.

tx,

JG

Are the Underlying and Instrument dimensions always paired like this? If so, one option is to build a single dimension based on the two "tables" in your DSV.

SSAS gives you a lot of flexibility in how you structure your cube. The trade-off is you must be explicit about that structure. So, one rule is that for a dimension to be associated with a fact, there must be an explicit relationship ("path") defined betewen the measure group and the dimension. Think about the alternative. Given any dimension in a conformed data warehouse, you could probably find a path to any measure group using intermediary measure groups and their dimension relationships. You'd end up with a real mess (and inappropriate results).

One thing you can do to ease the burden of setting these up is to insure you have relationships defined in your DSV. The Cube Designer will detect these and take a first stab at setting up relationships in the cube. (Still, I don't believe it will detect referenced relationships per the reason above.)

Good luck,
Bryan