changing report from VB

I'm trying to learn how to control a report from within a VB 2005 program.
My program has a windows form with a ReportViewer on it referencing a report
(.rdlc). The report has features in it which I would like to change per my
VB program. For example, I'd like to add a checkbox to the windows form that
would change the visibility of a column on the report (i.e. when checked, the
Visibility.Hidden property of the report column would be set to False, and
vica-versa). I assume this can be done, but I can't figure out how to obtain
a reference to the items on the report from within VB. Is this possible, and
if so, how do I do this?
--
EdHello Ed,
The only way you could do this is that you add a parameter in the local
report to control the visibility of the report item and then in the windows
application, you could set the parameter value and refresh the report to
get the result.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||While I haven't tried it yet, I suppose you could load in the XML in the
.rdlc file, change the XML for the visibility of the column, and save it back
to the file, and then refresh the Report...the only problem might be that it
may produce an error message that the file is in use. This would not be an
elegant solution, particularly since if I wanted to modify the layout of the
report, I'd probably have to re-write the XML change code. I must say, it
would be very handy to be able to just click a button or checkbox to hide or
view certain parts of the reports.
Another thing I'm interested in is being able to easily show or hide group
detail on the report easily--similar to a TreeView control in Windows forms,
but with the '+' signs opening or closing levels of details. Is that
available in the Reporting Services?
--
Ed
"Wei Lu [MSFT]" wrote:
> Hello Ed,
> The only way you could do this is that you add a parameter in the local
> report to control the visibility of the report item and then in the windows
> application, you could set the parameter value and refresh the report to
> get the result.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello ED,
You could do it.
It called drilldown report.
How to: Add a Visibility Toggle to an Item (Report Designer)
http://msdn2.microsoft.com/en-us/library/ms156456.aspx
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Changing outer joins to inner join

Hi,
I have a main table that references many tables (something like Orders
table, referencing Customers,Employees...).
As an example, there are many orders that have Null value for their
CustomerID therefore I need an Outer Join from Orders table to retrieve all
rows.
Now a View that joins Customers to Orders cannot be indexed because of the
Outer Join.
I was thinking of adding a row to Customers with value of (#) for
CompanyName column and assigning its ID to all Orphan rows in Orders.
I must:
- Write trigger on Orders table to insert ID of # record for new orphan
orders instead of Null.
- Filter # record when I need a real Inner Join.
- and some other considerations...
Instead an Inner Join between Customers and Orders can return all orders and
this query can be used in indexed view.
The performance of queries from main(Orders) table is the most critical
issue. Is it a good decision for changing such queries to Inner Join?
I appreciated any suggestion.
LeilaDo you really have to use an index view ? I just wanna keep track that even
it is an outer join the indexes will be used if possible and if existing. I
wouldt got the way you are describing although this situation is uncommon
to me, to enter an order where no customer is assigned to it. (?) Ok, but i
wouldt worsen up the situation by building a trigger and doing this kind of
workaround. I would build my best on the possibiliies i have, perhaps there
is a way to tune up our queries ?!
http://www.sql-server-performance.com/tuning_joins.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Hi
You can change your outer joins to a union of a query that uses an inner
join plus a join where the where clause eliminates those returned by the
inner join.
If you want to use a default for your order id, make the column not nullable
with the default.
I would have to ask why you have orders without customers?
John
"Leila" <leilas@.hotpop.com> wrote in message
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Thanks Jens,

>I just wanna keep track that even
> it is an outer join the indexes will be used if possible and if existing
- I do agree, but indexes and tuning the queries can help to limited extent.
If they could be ultimate solution for all situations, Indexed Views
wouldn't be invented! What if your query needs a covering index with more
that 16 columns? What if the index length exceeds 900 bytes? You will not be
able to create useful indexes.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:#0Fi1lCSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Do you really have to use an index view ? I just wanna keep track that
even
> it is an outer join the indexes will be used if possible and if existing.
I
> wouldt got the way you are describing although this situation is uncommon
> to me, to enter an order where no customer is assigned to it. (?) Ok, but
i
> wouldt worsen up the situation by building a trigger and doing this kind
of
> workaround. I would build my best on the possibiliies i have, perhaps
there
> is a way to tune up our queries ?!
> http://www.sql-server-performance.com/tuning_joins.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the
>|||Thanks John,
Using union is great idea but in my query, the main table(orders) is joined
with other parent tables(like customers, employees..).
The same situation can exist: orders with EmployeeID=Null as well.
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3LJqvCSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You can change your outer joins to a union of a query that uses an inner
> join plus a join where the where clause eliminates those returned by the
> inner join.
> If you want to use a default for your order id, make the column not
nullable
> with the default.
> I would have to ask why you have orders without customers?
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the
>|||"Leila" <leilas@.hotpop.com> wrote in message
news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> - I do agree, but indexes and tuning the queries can help to limited
> extent.
> If they could be ultimate solution for all situations, Indexed Views
> wouldn't be invented! What if your query needs a covering index with more
> that 16 columns? What if the index length exceeds 900 bytes? You will not
> be
> able to create useful indexes.
Not familiar with your DDL, or exactly how you're relating these tables to
one another, but columns with a lot of NULL values don't tend to make very
good indexes either... Are you really relating these tables to one another
using a CustomerID field that can be NULL? How do you keep track of who to
ship the order to? Or, more importantly, where to send the bill? In a
situation like this, would it make sense to generate all your NULL
customerid orders in a separate 'exception'-type query instead of plopping
them down in the middle of orders that were actually placed by customers?
Just wondering...
Instead of going through all the trigger-writing, etc., why not just UPDATE
all current NULL customerid's with the default value you specified; then
define the column as NON-NULL with a Default?|||Thank Michael,
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:K%Fae.3416$RP1.1452@.fe10.lga...
> "Leila" <leilas@.hotpop.com> wrote in message
> news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
more
not
> Not familiar with your DDL, or exactly how you're relating these tables to
> one another, but columns with a lot of NULL values don't tend to make very
> good indexes either... Are you really relating these tables to one
another
> using a CustomerID field that can be NULL? How do you keep track of who
to
> ship the order to? Or, more importantly, where to send the bill? In a
> situation like this, would it make sense to generate all your NULL
> customerid orders in a separate 'exception'-type query instead of plopping
> them down in the middle of orders that were actually placed by customers?
> Just wondering...
> Instead of going through all the trigger-writing, etc., why not just
UPDATE
> all current NULL customerid's with the default value you specified; then
> define the column as NON-NULL with a Default?
>
>

Changing outer joins to inner join

Hi,
I have a main table that references many tables (something like Orders
table, referencing Customers,Employees...).
As an example, there are many orders that have Null value for their
CustomerID therefore I need an Outer Join from Orders table to retrieve all
rows.
Now a View that joins Customers to Orders cannot be indexed because of the
Outer Join.
I was thinking of adding a row to Customers with value of (#) for
CompanyName column and assigning its ID to all Orphan rows in Orders.
I must:
- Write trigger on Orders table to insert ID of # record for new orphan
orders instead of Null.
- Filter # record when I need a real Inner Join.
- and some other considerations...
Instead an Inner Join between Customers and Orders can return all orders and
this query can be used in indexed view.
The performance of queries from main(Orders) table is the most critical
issue. Is it a good decision for changing such queries to Inner Join?
I appreciated any suggestion.
LeilaDo you really have to use an index view ? I just wanna keep track that even
it is an outer join the indexes will be used if possible and if existing. I
wouldt got the way you are describing although this situation is uncommon
to me, to enter an order where no customer is assigned to it. (?) Ok, but i
wouldt worsen up the situation by building a trigger and doing this kind of
workaround. I would build my best on the possibiliies i have, perhaps there
is a way to tune up our queries ?!
http://www.sql-server-performance.com/tuning_joins.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Hi
You can change your outer joins to a union of a query that uses an inner
join plus a join where the where clause eliminates those returned by the
inner join.
If you want to use a default for your order id, make the column not nullable
with the default.
I would have to ask why you have orders without customers?
John
"Leila" <leilas@.hotpop.com> wrote in message
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Thanks Jens,

>I just wanna keep track that even
> it is an outer join the indexes will be used if possible and if existing
- I do agree, but indexes and tuning the queries can help to limited extent.
If they could be ultimate solution for all situations, Indexed Views
wouldn't be invented! What if your query needs a covering index with more
that 16 columns? What if the index length exceeds 900 bytes? You will not be
able to create useful indexes.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:#0Fi1lCSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Do you really have to use an index view ? I just wanna keep track that
even
> it is an outer join the indexes will be used if possible and if existing.
I
> wouldt got the way you are describing although this situation is uncommon
> to me, to enter an order where no customer is assigned to it. (?) Ok, but
i
> wouldt worsen up the situation by building a trigger and doing this kind
of
> workaround. I would build my best on the possibiliies i have, perhaps
there
> is a way to tune up our queries ?!
> http://www.sql-server-performance.com/tuning_joins.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
>|||Thanks John,
Using union is great idea but in my query, the main table(orders) is joined
with other parent tables(like customers, employees..).
The same situation can exist: orders with EmployeeID=Null as well.
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3LJqvCSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You can change your outer joins to a union of a query that uses an inner
> join plus a join where the where clause eliminates those returned by the
> inner join.
> If you want to use a default for your order id, make the column not
nullable
> with the default.
> I would have to ask why you have orders without customers?
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
>|||"Leila" <leilas@.hotpop.com> wrote in message
news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> - I do agree, but indexes and tuning the queries can help to limited
> extent.
> If they could be ultimate solution for all situations, Indexed Views
> wouldn't be invented! What if your query needs a covering index with more
> that 16 columns? What if the index length exceeds 900 bytes? You will not
> be
> able to create useful indexes.
Not familiar with your DDL, or exactly how you're relating these tables to
one another, but columns with a lot of NULL values don't tend to make very
good indexes either... Are you really relating these tables to one another
using a CustomerID field that can be NULL? How do you keep track of who to
ship the order to? Or, more importantly, where to send the bill? In a
situation like this, would it make sense to generate all your NULL
customerid orders in a separate 'exception'-type query instead of plopping
them down in the middle of orders that were actually placed by customers?
Just wondering...
Instead of going through all the trigger-writing, etc., why not just UPDATE
all current NULL customerid's with the default value you specified; then
define the column as NON-NULL with a Default?|||Thank Michael,
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:K%Fae.3416$RP1.1452@.fe10.lga...
> "Leila" <leilas@.hotpop.com> wrote in message
> news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
more[vbcol=seagreen]
not[vbcol=seagreen]
> Not familiar with your DDL, or exactly how you're relating these tables to
> one another, but columns with a lot of NULL values don't tend to make very
> good indexes either... Are you really relating these tables to one
another
> using a CustomerID field that can be NULL? How do you keep track of who
to
> ship the order to? Or, more importantly, where to send the bill? In a
> situation like this, would it make sense to generate all your NULL
> customerid orders in a separate 'exception'-type query instead of plopping
> them down in the middle of orders that were actually placed by customers?
> Just wondering...
> Instead of going through all the trigger-writing, etc., why not just
UPDATE
> all current NULL customerid's with the default value you specified; then
> define the column as NON-NULL with a Default?
>
>

Changing outer joins to inner join

Hi,
I have a main table that references many tables (something like Orders
table, referencing Customers,Employees...).
As an example, there are many orders that have Null value for their
CustomerID therefore I need an Outer Join from Orders table to retrieve all
rows.
Now a View that joins Customers to Orders cannot be indexed because of the
Outer Join.
I was thinking of adding a row to Customers with value of (#) for
CompanyName column and assigning its ID to all Orphan rows in Orders.
I must:
- Write trigger on Orders table to insert ID of # record for new orphan
orders instead of Null.
- Filter # record when I need a real Inner Join.
- and some other considerations...
Instead an Inner Join between Customers and Orders can return all orders and
this query can be used in indexed view.
The performance of queries from main(Orders) table is the most critical
issue. Is it a good decision for changing such queries to Inner Join?
I appreciated any suggestion.
Leila
Do you really have to use an index view ? I just wanna keep track that even
it is an outer join the indexes will be used if possible and if existing. I
wouldt got the way you are describing although this situation is uncommon
to me, to enter an order where no customer is assigned to it. (?) Ok, but i
wouldt worsen up the situation by building a trigger and doing this kind of
workaround. I would build my best on the possibiliies i have, perhaps there
is a way to tune up our queries ?!
http://www.sql-server-performance.com/tuning_joins.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>
|||Hi
You can change your outer joins to a union of a query that uses an inner
join plus a join where the where clause eliminates those returned by the
inner join.
If you want to use a default for your order id, make the column not nullable
with the default.
I would have to ask why you have orders without customers?
John
"Leila" <leilas@.hotpop.com> wrote in message
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>
|||Thanks Jens,

>I just wanna keep track that even
> it is an outer join the indexes will be used if possible and if existing
- I do agree, but indexes and tuning the queries can help to limited extent.
If they could be ultimate solution for all situations, Indexed Views
wouldn't be invented! What if your query needs a covering index with more
that 16 columns? What if the index length exceeds 900 bytes? You will not be
able to create useful indexes.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:#0Fi1lCSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Do you really have to use an index view ? I just wanna keep track that
even
> it is an outer join the indexes will be used if possible and if existing.
I
> wouldt got the way you are describing although this situation is uncommon
> to me, to enter an order where no customer is assigned to it. (?) Ok, but
i
> wouldt worsen up the situation by building a trigger and doing this kind
of
> workaround. I would build my best on the possibiliies i have, perhaps
there[vbcol=seagreen]
> is a way to tune up our queries ?!
> http://www.sql-server-performance.com/tuning_joins.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the
>
|||Thanks John,
Using union is great idea but in my query, the main table(orders) is joined
with other parent tables(like customers, employees..).
The same situation can exist: orders with EmployeeID=Null as well.
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3LJqvCSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You can change your outer joins to a union of a query that uses an inner
> join plus a join where the where clause eliminates those returned by the
> inner join.
> If you want to use a default for your order id, make the column not
nullable[vbcol=seagreen]
> with the default.
> I would have to ask why you have orders without customers?
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the
>
|||"Leila" <leilas@.hotpop.com> wrote in message
news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> - I do agree, but indexes and tuning the queries can help to limited
> extent.
> If they could be ultimate solution for all situations, Indexed Views
> wouldn't be invented! What if your query needs a covering index with more
> that 16 columns? What if the index length exceeds 900 bytes? You will not
> be
> able to create useful indexes.
Not familiar with your DDL, or exactly how you're relating these tables to
one another, but columns with a lot of NULL values don't tend to make very
good indexes either... Are you really relating these tables to one another
using a CustomerID field that can be NULL? How do you keep track of who to
ship the order to? Or, more importantly, where to send the bill? In a
situation like this, would it make sense to generate all your NULL
customerid orders in a separate 'exception'-type query instead of plopping
them down in the middle of orders that were actually placed by customers?
Just wondering...
Instead of going through all the trigger-writing, etc., why not just UPDATE
all current NULL customerid's with the default value you specified; then
define the column as NON-NULL with a Default?
|||Thank Michael,
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:K%Fae.3416$RP1.1452@.fe10.lga...[vbcol=seagreen]
> "Leila" <leilas@.hotpop.com> wrote in message
> news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
more[vbcol=seagreen]
not
> Not familiar with your DDL, or exactly how you're relating these tables to
> one another, but columns with a lot of NULL values don't tend to make very
> good indexes either... Are you really relating these tables to one
another
> using a CustomerID field that can be NULL? How do you keep track of who
to
> ship the order to? Or, more importantly, where to send the bill? In a
> situation like this, would it make sense to generate all your NULL
> customerid orders in a separate 'exception'-type query instead of plopping
> them down in the middle of orders that were actually placed by customers?
> Just wondering...
> Instead of going through all the trigger-writing, etc., why not just
UPDATE
> all current NULL customerid's with the default value you specified; then
> define the column as NON-NULL with a Default?
>
>

Changing outer joins to inner join

Hi,
I have a main table that references many tables (something like Orders
table, referencing Customers,Employees...).
As an example, there are many orders that have Null value for their
CustomerID therefore I need an Outer Join from Orders table to retrieve all
rows.
Now a View that joins Customers to Orders cannot be indexed because of the
Outer Join.
I was thinking of adding a row to Customers with value of (#) for
CompanyName column and assigning its ID to all Orphan rows in Orders.
I must:
- Write trigger on Orders table to insert ID of # record for new orphan
orders instead of Null.
- Filter # record when I need a real Inner Join.
- and some other considerations...
Instead an Inner Join between Customers and Orders can return all orders and
this query can be used in indexed view.
The performance of queries from main(Orders) table is the most critical
issue. Is it a good decision for changing such queries to Inner Join?
I appreciated any suggestion.
LeilaDo you really have to use an index view ? I just wanna keep track that even
it is an outer join the indexes will be used if possible and if existing. I
would´t got the way you are describing although this situation is uncommon
to me, to enter an order where no customer is assigned to it. (?) Ok, but i
would´t worsen up the situation by building a trigger and doing this kind of
workaround. I would build my best on the possibiliies i have, perhaps there
is a way to tune up our queries ?!
http://www.sql-server-performance.com/tuning_joins.asp
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Hi
You can change your outer joins to a union of a query that uses an inner
join plus a join where the where clause eliminates those returned by the
inner join.
If you want to use a default for your order id, make the column not nullable
with the default.
I would have to ask why you have orders without customers?
John
"Leila" <leilas@.hotpop.com> wrote in message
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Thanks Jens,
>I just wanna keep track that even
> it is an outer join the indexes will be used if possible and if existing
- I do agree, but indexes and tuning the queries can help to limited extent.
If they could be ultimate solution for all situations, Indexed Views
wouldn't be invented! What if your query needs a covering index with more
that 16 columns? What if the index length exceeds 900 bytes? You will not be
able to create useful indexes.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:#0Fi1lCSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Do you really have to use an index view ? I just wanna keep track that
even
> it is an outer join the indexes will be used if possible and if existing.
I
> would´t got the way you are describing although this situation is uncommon
> to me, to enter an order where no customer is assigned to it. (?) Ok, but
i
> would´t worsen up the situation by building a trigger and doing this kind
of
> workaround. I would build my best on the possibiliies i have, perhaps
there
> is a way to tune up our queries ?!
> http://www.sql-server-performance.com/tuning_joins.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I have a main table that references many tables (something like Orders
> > table, referencing Customers,Employees...).
> > As an example, there are many orders that have Null value for their
> > CustomerID therefore I need an Outer Join from Orders table to retrieve
> > all
> > rows.
> > Now a View that joins Customers to Orders cannot be indexed because of
the
> > Outer Join.
> > I was thinking of adding a row to Customers with value of (#) for
> > CompanyName column and assigning its ID to all Orphan rows in Orders.
> > I must:
> > - Write trigger on Orders table to insert ID of # record for new orphan
> > orders instead of Null.
> > - Filter # record when I need a real Inner Join.
> > - and some other considerations...
> >
> > Instead an Inner Join between Customers and Orders can return all orders
> > and
> > this query can be used in indexed view.
> > The performance of queries from main(Orders) table is the most critical
> > issue. Is it a good decision for changing such queries to Inner Join?
> > I appreciated any suggestion.
> > Leila
> >
> >
>|||Thanks John,
Using union is great idea but in my query, the main table(orders) is joined
with other parent tables(like customers, employees..).
The same situation can exist: orders with EmployeeID=Null as well.
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3LJqvCSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You can change your outer joins to a union of a query that uses an inner
> join plus a join where the where clause eliminates those returned by the
> inner join.
> If you want to use a default for your order id, make the column not
nullable
> with the default.
> I would have to ask why you have orders without customers?
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I have a main table that references many tables (something like Orders
> > table, referencing Customers,Employees...).
> > As an example, there are many orders that have Null value for their
> > CustomerID therefore I need an Outer Join from Orders table to retrieve
> > all
> > rows.
> > Now a View that joins Customers to Orders cannot be indexed because of
the
> > Outer Join.
> > I was thinking of adding a row to Customers with value of (#) for
> > CompanyName column and assigning its ID to all Orphan rows in Orders.
> > I must:
> > - Write trigger on Orders table to insert ID of # record for new orphan
> > orders instead of Null.
> > - Filter # record when I need a real Inner Join.
> > - and some other considerations...
> >
> > Instead an Inner Join between Customers and Orders can return all orders
> > and
> > this query can be used in indexed view.
> > The performance of queries from main(Orders) table is the most critical
> > issue. Is it a good decision for changing such queries to Inner Join?
> > I appreciated any suggestion.
> > Leila
> >
> >
>|||"Leila" <leilas@.hotpop.com> wrote in message
news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> - I do agree, but indexes and tuning the queries can help to limited
> extent.
> If they could be ultimate solution for all situations, Indexed Views
> wouldn't be invented! What if your query needs a covering index with more
> that 16 columns? What if the index length exceeds 900 bytes? You will not
> be
> able to create useful indexes.
Not familiar with your DDL, or exactly how you're relating these tables to
one another, but columns with a lot of NULL values don't tend to make very
good indexes either... Are you really relating these tables to one another
using a CustomerID field that can be NULL? How do you keep track of who to
ship the order to? Or, more importantly, where to send the bill? In a
situation like this, would it make sense to generate all your NULL
customerid orders in a separate 'exception'-type query instead of plopping
them down in the middle of orders that were actually placed by customers?
Just wondering...
Instead of going through all the trigger-writing, etc., why not just UPDATE
all current NULL customerid's with the default value you specified; then
define the column as NON-NULL with a Default?|||Thank Michael,
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:K%Fae.3416$RP1.1452@.fe10.lga...
> "Leila" <leilas@.hotpop.com> wrote in message
> news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> > - I do agree, but indexes and tuning the queries can help to limited
> > extent.
> > If they could be ultimate solution for all situations, Indexed Views
> > wouldn't be invented! What if your query needs a covering index with
more
> > that 16 columns? What if the index length exceeds 900 bytes? You will
not
> > be
> > able to create useful indexes.
> Not familiar with your DDL, or exactly how you're relating these tables to
> one another, but columns with a lot of NULL values don't tend to make very
> good indexes either... Are you really relating these tables to one
another
> using a CustomerID field that can be NULL? How do you keep track of who
to
> ship the order to? Or, more importantly, where to send the bill? In a
> situation like this, would it make sense to generate all your NULL
> customerid orders in a separate 'exception'-type query instead of plopping
> them down in the middle of orders that were actually placed by customers?
> Just wondering...
> Instead of going through all the trigger-writing, etc., why not just
UPDATE
> all current NULL customerid's with the default value you specified; then
> define the column as NON-NULL with a Default?
>
>