Jump to URL in ReportViewer- Underline not displaying

Hello,
I have a text box in a table that has its text-decoration property set to
Underline. The textbox has its value set from a column in a dataset. I have
also defined the following as its Jump to URL expression:
="http://" & Parameters!WebServerName.Value &
"/Policy/Reports/AdvisorAccountPolicyStatus.aspx?asofdate=" &
Parameters!dAsOfDate.Value & "&AdvId=" & Fields!ENCRYPTED_USER_ID.Value
The second I define the Jump to URL expression and deploy the report, the
Underline stops being displayed. If I remove the Jump to URL expression, the
Underline is displayed.
I am using the .Net ReportViewer control. I know that the HTML is being
rendered properly for the text-decoration, but the reportviewer control
refuses to display it. If I view the report using the SQL Reporting Services
management tool, the underline is displayed.
Does anyone have a solution?
Thanks,
Keith MisegadesIf anyone has a solution to this, it would be greatly appreciated.
Everything I have tried has failed and we really need a solution to this
issue.
Thanks,
Keith
"Keith" wrote:
> Hello,
> I have a text box in a table that has its text-decoration property set to
> Underline. The textbox has its value set from a column in a dataset. I have
> also defined the following as its Jump to URL expression:
> ="http://" & Parameters!WebServerName.Value &
> "/Policy/Reports/AdvisorAccountPolicyStatus.aspx?asofdate=" &
> Parameters!dAsOfDate.Value & "&AdvId=" & Fields!ENCRYPTED_USER_ID.Value
> The second I define the Jump to URL expression and deploy the report, the
> Underline stops being displayed. If I remove the Jump to URL expression, the
> Underline is displayed.
> I am using the .Net ReportViewer control. I know that the HTML is being
> rendered properly for the text-decoration, but the reportviewer control
> refuses to display it. If I view the report using the SQL Reporting Services
> management tool, the underline is displayed.
> Does anyone have a solution?
> Thanks,
> Keith Misegades
>sql

Jump to report and also URL based on parameters values

Hi,
i am doing a page whereby user can navigate to other report by clicking on
the respective column data and i am now using this navigating expression:
=Switch(Fields!Status.Value = "Pending", "EvaluatePending",
Fields!Status.Value = "Rejected", "ViewRejected", Fields!Status.Value = "Accepted", "ViewAccepted")
This expression only allows me to jump to other report but not to a URL
because i try using the following statement but got an error:
=Switch(Fields!Status.Value = "Pending",
"="http://localhost/SSS-ASP/EvaluatePending.aspx?SuggestionID=" &
Fields!SuggestionID.Value", Fields!Status.Value = "Rejected", "ViewRejected",
Fields!Status.Value = "Accepted", "ViewAccepted")
For the above statement what i want to achieved was that when user click on
the column with the Status=Pending, it will go to the
URL="http://localhost/SSS-ASP/EvaluatePending.aspx?SuggestionID=" &
Fields!SuggestionID.Value. If Status=Rejected, it will jump to the report
ViewRejected and lastly if Status=Accepted, it will jump to the report
ViewAccepted. Hope somebody can guide me with the correct expression.
Thanks in advance. =)Hello, is anybody able to help me with this problem? really need help with
this still not sure how to solve. Thanks
"JiaN" wrote:
> Hi,
> i am doing a page whereby user can navigate to other report by clicking on
> the respective column data and i am now using this navigating expression:
> =Switch(Fields!Status.Value = "Pending", "EvaluatePending",
> Fields!Status.Value = "Rejected", "ViewRejected", Fields!Status.Value => "Accepted", "ViewAccepted")
> This expression only allows me to jump to other report but not to a URL
> because i try using the following statement but got an error:
> =Switch(Fields!Status.Value = "Pending",
> "="http://localhost/SSS-ASP/EvaluatePending.aspx?SuggestionID=" &
> Fields!SuggestionID.Value", Fields!Status.Value = "Rejected", "ViewRejected",
> Fields!Status.Value = "Accepted", "ViewAccepted")
> For the above statement what i want to achieved was that when user click on
> the column with the Status=Pending, it will go to the
> URL="http://localhost/SSS-ASP/EvaluatePending.aspx?SuggestionID=" &
> Fields!SuggestionID.Value. If Status=Rejected, it will jump to the report
> ViewRejected and lastly if Status=Accepted, it will jump to the report
> ViewAccepted. Hope somebody can guide me with the correct expression.
> Thanks in advance. =)

Julian Day

I have created an auxiliary calendar table with the help of
http://www.aspfaq.com/2519
I would like to add a column for the daynumber of year (e.g. 1-365), and
populate it. I am new to sql and am not finding help with this. Any help
would be greatly appreciated.select datepart(dayofyear, getdate())
-- or
select datepart(dy, getdate())
"Nice_Out" wrote:

> I have created an auxiliary calendar table with the help of
> http://www.aspfaq.com/2519
> I would like to add a column for the daynumber of year (e.g. 1-365), and
> populate it. I am new to sql and am not finding help with this. Any help
> would be greatly appreciated.|||Thanks.
"KH" wrote:
> select datepart(dayofyear, getdate())
> -- or
> select datepart(dy, getdate())
>
> "Nice_Out" wrote:
>sql

Joins and inices

I can't understand this behavior:
I'm doing a join on a large table, and I created a
clustered index on the column in the join condition.
I would expect that this should enhance performance,
because of the merge join advantage.
It didn't perform well.
I then tried making that index non-clustered, and doing
a clustered index on an identity column (as a primary
key).
It's weird, but the join performs far better this way.
The only way I can explain this is that the non-clustered
index keeps the values of the join column in fewer pages,
and therefore closer together on disk (fewer reads).
Then again, there must still be the additional seek to
get to the rest of the "data" that row contains.
Even stranger, if the clustered index on the identity col-
umn (PK) is not there, the join doesn't perform well also.
If anyone has any experience with this, or suggestions,
I'd be really grateful for some help.
Thanks in advance,
Andrew.It would be easier to offer ideas if you posted DDL and ideally insert
statements to load some sample data...
this doesn't answer all of your questions but as an FYI...
<<
Even stranger, if the clustered index on the identity col-
umn (PK) is not there, the join doesn't perform well also.
the NC index actually keeps the clustering key as part of it's own key
information at the leaf level of the NC index. So... you're actually
changing the contents of the NC index if you get rid of the clustering key.
Also, NC index is stored entirely differently if there is no clustered index
at all...
--
Brian
"Andrew" <a@.b.com> wrote in message
news:01a401c38dc5$d91d84d0$a401280a@.phx.gbl...
> I can't understand this behavior:
> I'm doing a join on a large table, and I created a
> clustered index on the column in the join condition.
> I would expect that this should enhance performance,
> because of the merge join advantage.
> It didn't perform well.
> I then tried making that index non-clustered, and doing
> a clustered index on an identity column (as a primary
> key).
> It's weird, but the join performs far better this way.
> The only way I can explain this is that the non-clustered
> index keeps the values of the join column in fewer pages,
> and therefore closer together on disk (fewer reads).
> Then again, there must still be the additional seek to
> get to the rest of the "data" that row contains.
> Even stranger, if the clustered index on the identity col-
> umn (PK) is not there, the join doesn't perform well also.
> If anyone has any experience with this, or suggestions,
> I'd be really grateful for some help.
> Thanks in advance,
> Andrew.|||Still...
><<
>Even stranger, if the clustered index on the identity
col-
>umn (PK) is not there, the join doesn't perform well
also.
>the NC index actually keeps the clustering key as part
of it's own key
>information at the leaf level of the NC index. So...
you're actually
>changing the contents of the NC index if you get rid of
the clustering key.
>Also, NC index is stored entirely differently if there
is no clustered index
>at all...
>--
I realize the NC index keeps the clustering key as part
of it's own key, but I had understood that SQL server
keeps a RID (row identifier) internally when there is no
clustered index... so rather thank keeping the clustered
index in the leaf, the RID is kept.
Now, if accessing that RID is slow, I can understand, but
I'd imagined it as a kind of internal indexed identity
of its own. I guess this doesn't make sense, though.
As a follow up, though, is there no use for a NC index
without a clustered index when you want to access columns
outside of the NC indexed column?
Also, can a column be both a clustered and a non-clustered
index (would this help on join performance to access those
other columns?).
Thanks again,
Andrew
>Brian
>
>"Andrew" <a@.b.com> wrote in message
>news:01a401c38dc5$d91d84d0$a401280a@.phx.gbl...
>> I can't understand this behavior:
>> I'm doing a join on a large table, and I created a
>> clustered index on the column in the join condition.
>> I would expect that this should enhance performance,
>> because of the merge join advantage.
>> It didn't perform well.
>> I then tried making that index non-clustered, and doing
>> a clustered index on an identity column (as a primary
>> key).
>> It's weird, but the join performs far better this way.
>> The only way I can explain this is that the non-
clustered
>> index keeps the values of the join column in fewer
pages,
>> and therefore closer together on disk (fewer reads).
>> Then again, there must still be the additional seek to
>> get to the rest of the "data" that row contains.
>> Even stranger, if the clustered index on the identity
col-
>> umn (PK) is not there, the join doesn't perform well
also.
>> If anyone has any experience with this, or suggestions,
>> I'd be really grateful for some help.
>> Thanks in advance,
>> Andrew.
>
>.
>

Joining two large queries as derived tables

All,
I want to join two large queries together keying on a common column. I
have simplifed the queries / derived tables; but I am trying to do
something like what I have below. How do you join two derived tables?
SELECT * FROM
(SELECT Name AS 'Name1', ... other columns
FROM [TABLE1]) AS a
JOIN
SELECT * FROM
(SELECT Name AS 'Name2', ... other columns
FROM [TABLE2]) AS b
ON a.Name = b.NameTry,
SELECT *
FROM
(SELECT Name AS 'Name1', ... other columns FROM [TABLE1]) AS a
inner JOIN
(SELECT Name AS 'Name2', ... other columns FROM [TABLE2]) AS b
ON a.Name = b.Name
go
AMB
"dpless@.gmail.com" wrote:

> All,
> I want to join two large queries together keying on a common column. I
> have simplifed the queries / derived tables; but I am trying to do
> something like what I have below. How do you join two derived tables?
>
> SELECT * FROM
> (SELECT Name AS 'Name1', ... other columns
> FROM [TABLE1]) AS a
> JOIN
> SELECT * FROM
> (SELECT Name AS 'Name2', ... other columns
> FROM [TABLE2]) AS b
> ON a.Name = b.Name
>|||Is this what you are trying to do?
SELECT * FROM
(SELECT * FROM table1) a
INNER JOIN (select * from table2) b ON a.name=b.name
-- or --
SELECT * FROM
(SELECT * FROM dailychallenge) a,
(SELECT * FROM DailyChallengeResponse) b
WHERE a.challengedate=b.challengedate
Let us know how you make out.
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
--

Joining to Huge Table

Do anybody know how to get data from very small and huge table based on
common column(ex : Stae_Cd).Index on common column is not useful.
Very small table has lookup table (static data). Please suggest .
SELECT DISTINCT
VS1.coverage_id,
HT.Col_id,
VS2.Col3
FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd =
HT.State_cd and
INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
Thanks in advance.
Why are you joining to your VerySmall_Table1 twice?
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> Do anybody know how to get data from very small and huge table based on
> common column(ex : Stae_Cd).Index on common column is not useful.
> Very small table has lookup table (static data). Please suggest .
>
> SELECT DISTINCT
> VS1.coverage_id,
> HT.Col_id,
> VS2.Col3
> FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd
=
> HT.State_cd and
> INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
> Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
>
> Thanks in advance.
>
|||Table design is like that.Two tables has same column with other info.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...[color=darkblue]
> Why are you joining to your VerySmall_Table1 twice?
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
VS.State_Cd[color=darkblue]
> =
HT.State_cd
>
|||But you're joining on the same columns... Both joins are VS.State_Cd =
HT.State_cd. Why not just join once?
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Table design is like that.Two tables has same column with other info.
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message[color=darkblue]
> news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
on
> VS.State_Cd
> HT.State_cd
>
|||I also do not understand joining the small table twice... see if this works
Did you ever work for Unisys/Burroughs in Charlotte, NC?
SELECT DISTINCT
VS1.coverage_id,
HT.Col_id,
VS2.Col3
FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd =
HT.State_cd
Where HT.Col_id = 123456789
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> Do anybody know how to get data from very small and huge table based on
> common column(ex : Stae_Cd).Index on common column is not useful.
> Very small table has lookup table (static data). Please suggest .
>
> SELECT DISTINCT
> VS1.coverage_id,
> HT.Col_id,
> VS2.Col3
> FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd
=
> HT.State_cd and
> INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
> Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
>
> Thanks in advance.
>
|||OK, Let me tell in other way.
Is there any way that table level desing can help in retrieving data faster
while joining big table with small lookup table, lookup table has just 20
recordsl .
Table1( ID int Primary Key clustered,
Type_ID int Foreign key
.......
)
Table2 ( Type_ID int Primary Key
...............
)
Table1 has 30 million records
and Table2 has just 10 records.
select Table1.*
from table1,table2
where table1.type_id = table2.type_id
Is the above table design is good, or is there anyother design for this.
Thanks.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eAN8zLbIEHA.940@.tk2msftngp13.phx.gbl...
> But you're joining on the same columns... Both joins are VS.State_Cd =
> HT.State_cd. Why not just join once?
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> message
> on
>
|||What you're talking about is very typical in data warehousing scenarios
where Table1 would represent the fact table and Table2 would represent the
dimension table. What you're missing that's almost always, if not always
present in a DW, is further filtration of the dimensions, in order to filter
the facts. Executing the query you've posted will, in essence, be the same
as doing:
SELECT * FROM Table1
So what you need is something more along the lines of:
select Table1.*
from table1
join table2 on table1.type_id = table2.type_id
where table2.type_desc = 'some description'
... otherwise, there's no reason for the JOIN to begin with. Note I've
converted your query to SQL-92 syntax; just my personal preference.
As for indexing, if your dimension table has only 10 rows, clustering the
fact (big) table by that column will greatly improve select performance as
it will allow the disc to read from one contiguous block when selecting any
of the given 10 choices; however, I don't know what OTHER dimensions
(lookups) you might have that a cluster might serve better, so you'll have
to look at your schema or perhaps try out the Index Tuning Wizard to help
you with those decisions.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:ex4hR1lIEHA.2556@.TK2MSFTNGP12.phx.gbl...
> OK, Let me tell in other way.
> Is there any way that table level desing can help in retrieving data
faster
> while joining big table with small lookup table, lookup table has just 20
> recordsl .
> Table1( ID int Primary Key clustered,
> Type_ID int Foreign key
> .......
> )
> Table2 ( Type_ID int Primary Key
> ...............
> )
>
> Table1 has 30 million records
> and Table2 has just 10 records.
> select Table1.*
> from table1,table2
> where table1.type_id = table2.type_id
>
> Is the above table design is good, or is there anyother design for this.
>
> Thanks.
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message[vbcol=seagreen]
> news:eAN8zLbIEHA.940@.tk2msftngp13.phx.gbl...
based[vbcol=seagreen]
..[vbcol=seagreen]
index.
>

Joining to Huge Table

Do anybody know how to get data from very small and huge table based on
common column(ex : Stae_Cd).Index on common column is not useful.
Very small table has lookup table (static data). Please suggest .
SELECT DISTINCT
VS1.coverage_id,
HT.Col_id,
VS2.Col3
FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd =
HT.State_cd and
INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
Thanks in advance.Why are you joining to your VerySmall_Table1 twice?
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> Do anybody know how to get data from very small and huge table based on
> common column(ex : Stae_Cd).Index on common column is not useful.
> Very small table has lookup table (static data). Please suggest .
>
> SELECT DISTINCT
> VS1.coverage_id,
> HT.Col_id,
> VS2.Col3
> FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd
=
> HT.State_cd and
> INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
> Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
>
> Thanks in advance.
>|||Table design is like that.Two tables has same column with other info.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
> Why are you joining to your VerySmall_Table1 twice?
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
VS.State_Cd
> =
HT.State_cd
>|||But you're joining on the same columns... Both joins are VS.State_Cd =
HT.State_cd. Why not just join once?
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Table design is like that.Two tables has same column with other info.
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
on
> VS.State_Cd
> HT.State_cd
>|||I also do not understand joining the small table twice... see if this works
Did you ever work for Unisys/Burroughs in Charlotte, NC?
SELECT DISTINCT
VS1.coverage_id,
HT.Col_id,
VS2.Col3
FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd =
HT.State_cd
Where HT.Col_id = 123456789
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> Do anybody know how to get data from very small and huge table based on
> common column(ex : Stae_Cd).Index on common column is not useful.
> Very small table has lookup table (static data). Please suggest .
>
> SELECT DISTINCT
> VS1.coverage_id,
> HT.Col_id,
> VS2.Col3
> FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd
=
> HT.State_cd and
> INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
> Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
>
> Thanks in advance.
>|||OK, Let me tell in other way.
Is there any way that table level desing can help in retrieving data faster
while joining big table with small lookup table, lookup table has just 20
recordsl .
Table1( ID int Primary Key clustered,
Type_ID int Foreign key
......
)
Table2 ( Type_ID int Primary Key
..............
)
Table1 has 30 million records
and Table2 has just 10 records.
select Table1.*
from table1,table2
where table1.type_id = table2.type_id
Is the above table design is good, or is there anyother design for this.
Thanks.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eAN8zLbIEHA.940@.tk2msftngp13.phx.gbl...
> But you're joining on the same columns... Both joins are VS.State_Cd =
> HT.State_cd. Why not just join once?
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> message
> on
>|||What you're talking about is very typical in data warehousing scenarios
where Table1 would represent the fact table and Table2 would represent the
dimension table. What you're missing that's almost always, if not always
present in a DW, is further filtration of the dimensions, in order to filter
the facts. Executing the query you've posted will, in essence, be the same
as doing:
SELECT * FROM Table1
So what you need is something more along the lines of:
select Table1.*
from table1
join table2 on table1.type_id = table2.type_id
where table2.type_desc = 'some description'
... otherwise, there's no reason for the JOIN to begin with. Note I've
converted your query to SQL-92 syntax; just my personal preference.
As for indexing, if your dimension table has only 10 rows, clustering the
fact (big) table by that column will greatly improve select performance as
it will allow the disc to read from one contiguous block when selecting any
of the given 10 choices; however, I don't know what OTHER dimensions
(lookups) you might have that a cluster might serve better, so you'll have
to look at your schema or perhaps try out the Index Tuning Wizard to help
you with those decisions.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:ex4hR1lIEHA.2556@.TK2MSFTNGP12.phx.gbl...
> OK, Let me tell in other way.
> Is there any way that table level desing can help in retrieving data
faster
> while joining big table with small lookup table, lookup table has just 20
> recordsl .
> Table1( ID int Primary Key clustered,
> Type_ID int Foreign key
> .......
> )
> Table2 ( Type_ID int Primary Key
> ...............
> )
>
> Table1 has 30 million records
> and Table2 has just 10 records.
> select Table1.*
> from table1,table2
> where table1.type_id = table2.type_id
>
> Is the above table design is good, or is there anyother design for this.
>
> Thanks.
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eAN8zLbIEHA.940@.tk2msftngp13.phx.gbl...
based
.
index.
>sql

Joining to Huge Table

Do anybody know how to get data from very small and huge table based on
common column(ex : Stae_Cd).Index on common column is not useful.
Very small table has lookup table (static data). Please suggest .
SELECT DISTINCT
VS1.coverage_id,
HT.Col_id,
VS2.Col3
FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd = HT.State_cd and
INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
Thanks in advance.Why are you joining to your VerySmall_Table1 twice?
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> Do anybody know how to get data from very small and huge table based on
> common column(ex : Stae_Cd).Index on common column is not useful.
> Very small table has lookup table (static data). Please suggest .
>
> SELECT DISTINCT
> VS1.coverage_id,
> HT.Col_id,
> VS2.Col3
> FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd
=> HT.State_cd and
> INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
> Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
>
> Thanks in advance.
>|||Table design is like that.Two tables has same column with other info.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
> Why are you joining to your VerySmall_Table1 twice?
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> > Do anybody know how to get data from very small and huge table based on
> > common column(ex : Stae_Cd).Index on common column is not useful.
> > Very small table has lookup table (static data). Please suggest .
> >
> >
> >
> > SELECT DISTINCT
> >
> > VS1.coverage_id,
> > HT.Col_id,
> > VS2.Col3
> >
> > FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON
VS.State_Cd
> => > HT.State_cd and
> >
> > INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd =HT.State_cd
> >
> > Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
> >
> >
> > Thanks in advance.
> >
> >
>|||But you're joining on the same columns... Both joins are VS.State_Cd =HT.State_cd. Why not just join once?
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Table design is like that.Two tables has same column with other info.
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
> > Why are you joining to your VerySmall_Table1 twice?
> >
> >
> > "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> > news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> > > Do anybody know how to get data from very small and huge table based
on
> > > common column(ex : Stae_Cd).Index on common column is not useful.
> > > Very small table has lookup table (static data). Please suggest .
> > >
> > >
> > >
> > > SELECT DISTINCT
> > >
> > > VS1.coverage_id,
> > > HT.Col_id,
> > > VS2.Col3
> > >
> > > FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON
> VS.State_Cd
> > => > > HT.State_cd and
> > >
> > > INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd => HT.State_cd
> > >
> > > Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
> > >
> > >
> > > Thanks in advance.
> > >
> > >
> >
> >
>|||I also do not understand joining the small table twice... see if this works
Did you ever work for Unisys/Burroughs in Charlotte, NC?
SELECT DISTINCT
VS1.coverage_id,
HT.Col_id,
VS2.Col3
FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd =HT.State_cd
Where HT.Col_id = 123456789
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> Do anybody know how to get data from very small and huge table based on
> common column(ex : Stae_Cd).Index on common column is not useful.
> Very small table has lookup table (static data). Please suggest .
>
> SELECT DISTINCT
> VS1.coverage_id,
> HT.Col_id,
> VS2.Col3
> FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd
=> HT.State_cd and
> INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
> Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
>
> Thanks in advance.
>|||OK, Let me tell in other way.
Is there any way that table level desing can help in retrieving data faster
while joining big table with small lookup table, lookup table has just 20
recordsl .
Table1( ID int Primary Key clustered,
Type_ID int Foreign key
.......
)
Table2 ( Type_ID int Primary Key
...............
)
Table1 has 30 million records
and Table2 has just 10 records.
select Table1.*
from table1,table2
where table1.type_id = table2.type_id
Is the above table design is good, or is there anyother design for this.
Thanks.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eAN8zLbIEHA.940@.tk2msftngp13.phx.gbl...
> But you're joining on the same columns... Both joins are VS.State_Cd => HT.State_cd. Why not just join once?
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> > Table design is like that.Two tables has same column with other info.
> > "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
> message
> > news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
> > > Why are you joining to your VerySmall_Table1 twice?
> > >
> > >
> > > "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> > > news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> > > > Do anybody know how to get data from very small and huge table based
> on
> > > > common column(ex : Stae_Cd).Index on common column is not useful.
> > > > Very small table has lookup table (static data). Please suggest .
> > > >
> > > >
> > > >
> > > > SELECT DISTINCT
> > > >
> > > > VS1.coverage_id,
> > > > HT.Col_id,
> > > > VS2.Col3
> > > >
> > > > FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON
> > VS.State_Cd
> > > => > > > HT.State_cd and
> > > >
> > > > INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd => > HT.State_cd
> > > >
> > > > Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
> > > >
> > > >
> > > > Thanks in advance.
> > > >
> > > >
> > >
> > >
> >
> >
>|||What you're talking about is very typical in data warehousing scenarios
where Table1 would represent the fact table and Table2 would represent the
dimension table. What you're missing that's almost always, if not always
present in a DW, is further filtration of the dimensions, in order to filter
the facts. Executing the query you've posted will, in essence, be the same
as doing:
SELECT * FROM Table1
So what you need is something more along the lines of:
select Table1.*
from table1
join table2 on table1.type_id = table2.type_id
where table2.type_desc = 'some description'
... otherwise, there's no reason for the JOIN to begin with. Note I've
converted your query to SQL-92 syntax; just my personal preference.
As for indexing, if your dimension table has only 10 rows, clustering the
fact (big) table by that column will greatly improve select performance as
it will allow the disc to read from one contiguous block when selecting any
of the given 10 choices; however, I don't know what OTHER dimensions
(lookups) you might have that a cluster might serve better, so you'll have
to look at your schema or perhaps try out the Index Tuning Wizard to help
you with those decisions.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:ex4hR1lIEHA.2556@.TK2MSFTNGP12.phx.gbl...
> OK, Let me tell in other way.
> Is there any way that table level desing can help in retrieving data
faster
> while joining big table with small lookup table, lookup table has just 20
> recordsl .
> Table1( ID int Primary Key clustered,
> Type_ID int Foreign key
> .......
> )
> Table2 ( Type_ID int Primary Key
> ...............
> )
>
> Table1 has 30 million records
> and Table2 has just 10 records.
> select Table1.*
> from table1,table2
> where table1.type_id = table2.type_id
>
> Is the above table design is good, or is there anyother design for this.
>
> Thanks.
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eAN8zLbIEHA.940@.tk2msftngp13.phx.gbl...
> > But you're joining on the same columns... Both joins are VS.State_Cd => > HT.State_cd. Why not just join once?
> >
> >
> > "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> > news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> > > Table design is like that.Two tables has same column with other info.
> > > "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
> > message
> > > news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
> > > > Why are you joining to your VerySmall_Table1 twice?
> > > >
> > > >
> > > > "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> > > > news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> > > > > Do anybody know how to get data from very small and huge table
based
> > on
> > > > > common column(ex : Stae_Cd).Index on common column is not useful.
> > > > > Very small table has lookup table (static data). Please suggest
.
> > > > >
> > > > >
> > > > >
> > > > > SELECT DISTINCT
> > > > >
> > > > > VS1.coverage_id,
> > > > > HT.Col_id,
> > > > > VS2.Col3
> > > > >
> > > > > FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON
> > > VS.State_Cd
> > > > => > > > > HT.State_cd and
> > > > >
> > > > > INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd => > > HT.State_cd
> > > > >
> > > > > Where HT.Col_id = 123456789 -- HT.Col_id is clustered
index.
> > > > >
> > > > >
> > > > > Thanks in advance.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Joining on NULLS

I have a stored proc that matches 2 tables on a datetime column. I am
worried that matching on a NULL column is not a good idea. Should I consider
matching on a ISNULL(datecolumn1,0) = ISNULL(datecolumn2,0) a better idea?
Thanks.
DavidI believe outer joins should take care of this.
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:%23GBhm7xCGHA.3812@.TK2MSFTNGP15.phx.gbl...
>I have a stored proc that matches 2 tables on a datetime column. I am
>worried that matching on a NULL column is not a good idea. Should I
>consider matching on a ISNULL(datecolumn1,0) = ISNULL(datecolumn2,0) a
>better idea? Thanks.
> David
>|||> worried that matching on a NULL column is not a good idea. Should I
> consider matching on a ISNULL(datecolumn1,0) = ISNULL(datecolumn2,0) a
> better idea?
Does this mean that you intended to match NULLs (i.e., you consider NULL =
NULL to be true)? If so, then your logic is dependent on the current
ANSI_NULLS setting. There is a section in BOL (sigh - as there usually is)
that discusses this particular issue - Accessing and Changing Relational
Data / Query Fundamentals / Filtering Rows with WHERE and HAVING / NULL
Comparison Search Conditions.
As for whether this (or any other approach) is "better" depends on many
factors. A connection setting dependency is generally not recommended.
Your "better" approach is dependent on knowledge of the domain of the
columns - is this dependency any "better" than the connection setting? I'll
ignore the use of the implicit conversion, something that can easily trip an
unsuspecting reader.
The short answer is that it IS better to use a more defensive approach to
coding. However, this particular case will also involve the identification
of a technique that yields the best performance for your given situation.
Performance is often driven as much by the batch characteristics (e.g., use
of parameters, plan usage) as it is by the query and schema. Logically, you
should use something like:
where (col1 = col2) or (col1 is null and col2 is null)
Isnull and coalesce can be used - as you indicated. There might also be
other ways of looking at the data that would lead you to a different
approach. If you are attempting to equate NULLs, perhaps this is an
indication of a flaw in the data model. If so, the "better" approach is to
find and fix this model flaw. A better model generally improves the system
as a whole, often by orders of magnitude.|||not sure what you mean:
"matching on a null column" to me means "null = null" - if that's your
meaning, then isnull(datecolumn1,0)=isnull(datecolumn2
,0) is the same thing.
yes - joining on nulls is a bad idea -- if the other join criteria (if
any) isn't selective enough, then you'll get a cartesian product for
these (x nulls in table1 * x nulls in table2).
why would you want to match them?
David Chase wrote:
> I have a stored proc that matches 2 tables on a datetime column. I am
> worried that matching on a NULL column is not a good idea. Should I consid
er
> matching on a ISNULL(datecolumn1,0) = ISNULL(datecolumn2,0) a better idea?
> Thanks.
> David
>|||clarification: by "null = null" i mean that these would "match", not
that you would use "where null = null".
"null = null" evaluates to null unless SET ANSI_NULLS is OFF.
therefore, these would not be included in the results, whereas
isnull(datecolumn1,0)=isnull(datecolumn2
,0) would be included in the
results regardless of ANSI_NULLS setting.
but the end result in the narrative is the same - consider nulls a match
and return them in the result set.
Trey Walpole wrote:
> not sure what you mean:
> "matching on a null column" to me means "null = null" - if that's your
> meaning, then isnull(datecolumn1,0)=isnull(datecolumn2
,0) is the same
> thing.
> yes - joining on nulls is a bad idea -- if the other join criteria (if
> any) isn't selective enough, then you'll get a cartesian product for
> these (x nulls in table1 * x nulls in table2).
> why would you want to match them?
> David Chase wrote:
>|||> "null = null" evaluates to null unless SET ANSI_NULLS is OFF.
Not for a JOIN operation, though. ANSI_NULLS does not change the meaning of
NULL = NULL for a join,
the unknown will still be false in the end:
USE tempdb
CREATE TABLE t1(c1 datetime, c2 int)
CREATE TABLE t2(c1 datetime, c2 int)
INSERT INTO t1 VALUES(NULL, 1)
INSERT INTO t2 VALUES(NULL, 3)
INSERT INTO t1 VALUES('20050101', 2)
INSERT INTO t2 VALUES('20050101', 4)
INSERT INTO t1 VALUES('20050102', 5)
INSERT INTO t2 VALUES('20050103', 6)
SELECT * FROM t1
SELECT * FROM t2
SET ANSI_NULLS OFF
SELECT *
FROM t1
INNER JOIN t2 ON t1.c1 = t2.c1
SELECT *
FROM t1 ,t2
WHERE t1.c1 = t2.c1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:ejF54IzCGHA.2040@.TK2MSFTNGP14.phx.gbl...
> clarification: by "null = null" i mean that these would "match", not that
you would use "where
> null = null".
> "null = null" evaluates to null unless SET ANSI_NULLS is OFF.
> therefore, these would not be included in the results, whereas
> isnull(datecolumn1,0)=isnull(datecolumn2
,0) would be included in the resu
lts regardless of
> ANSI_NULLS setting.
> but the end result in the narrative is the same - consider nulls a match a
nd return them in the
> result set.
>
> Trey Walpole wrote:|||ah yes - thanks for the clarification
Tibor Karaszi wrote:
>
> Not for a JOIN operation, though. ANSI_NULLS does not change the meaning
> of NULL = NULL for a join, the unknown will still be false in the end:
> USE tempdb
> CREATE TABLE t1(c1 datetime, c2 int)
> CREATE TABLE t2(c1 datetime, c2 int)
> INSERT INTO t1 VALUES(NULL, 1)
> INSERT INTO t2 VALUES(NULL, 3)
> INSERT INTO t1 VALUES('20050101', 2)
> INSERT INTO t2 VALUES('20050101', 4)
> INSERT INTO t1 VALUES('20050102', 5)
> INSERT INTO t2 VALUES('20050103', 6)
> SELECT * FROM t1
> SELECT * FROM t2
> SET ANSI_NULLS OFF
> SELECT *
> FROM t1
> INNER JOIN t2 ON t1.c1 = t2.c1
> SELECT *
> FROM t1 ,t2
> WHERE t1.c1 = t2.c1
>

Joining on and Grouping by CASE function column alias (URGENT)

I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:

SELECT NewColumn=
 CASE
 WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
 END,
 Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I really appreciate any help anyone can provide.

Thanks,
DC RossYou could do it as a sub query

Select NewColumn from (Select case....) MySub group by MySub.NewColumn...etc, etc|||Not tested, but you should be able to do it like this:

SELECT NewColumn=
 CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END,
 Table2.SelectCol2
FROM Table1
JOIN Table2 ON CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I'm sure there's some rule, but I've never figured out when SQL lets you use an alias and when it doesn't. But, in this case, it apparently doesn't, so just use the CASE statement and you should be all set.|||I didn't know you could use a CASE in the JOIN syntax? Does it work?|||Works for me|||Cool I'll have to remember that one, top tip.

Joining and Conditional Column Data Help

I have 2 table that I want to join and output a row on a condition that one of the records have a null in the field. Heres what I have.

employee table (empid, name)
tasks table (taskid, empid, taskname, resolution)

If the resolution is null than I want it to be accounted for in each employee record. Heres my query so far that joins the 2 tables and accounts for each employee and counts each task they have. I need another column that counts the tasks.resolution's null values for each employee but cant figure it out. Thanks for any help!

SELECT e.empid,
e.name,
COUNT(t.ID) as 'tcount'
FROM tasks t
RIGHT JOIN employee e ON c.empid = t.empid
GROUP BY e.empid, e.name
order by 'tcount' desc

SELECT

e.empid,

e

.empname,

COUNT

(t.taskid)as'tcount'

FROM

tasks t

LEFT

JOIN employee eON e.empid= t.empid

WHERE

t.resolutionISNULL

GROUP

BY e.empid, e.empname

Order

by tcountDesc|||

limno's query doesn't quite work the way you'd expect. Because it is being filtered by where the resolution is null from within the WHERE clause, it will eliminate employees that have no records where resolution is null from the output. If you want the employees listed even if they have no tasks, then use this instead:

SELECT empid, empname, (SELECTCOUNT(*)FROM tasksWHERE tasks.empid=employee.empidAND resolutionISNULL)as'tcount'FROM employeeOrder by tcountDesc

|||

Thanks. That helped me put together someting else

selecte.empid, e.ename, count(*) as 'tcount', sum(case when t.resolution isnull and t.empid is not null then 1 else 0 end) as 'NULL resolution'
from employee as e
left join tasks as t on t.empid = e.empid
group by e.empid, e.ename
order by 3 desc

joining a varchar with a int column

just noticed this, wonder if there is a performance hit
got results when executing the following
select AgendaID
from ObjectiveAgenda
Inner Join Objective On Objective.ObjectiveID =
ObjectiveAgenda.ObjectiveID
problem is that the ObjectiveID column in one table is of SQL type
INTEGER, while in the other table is of type VARCHAR.
wonder if it is doing a translation behind the scenes, with some
performance hit.Yes, SQL Server will have to convert the value and this can cause table scan
.
Thus this can cause major negative impact.
Here is a script I have used for testing. In the Query Analyser, please
'Display Execution Plan' for the 2 queries at the end of my script. One will
cause Table Scan and another will go for Index S.
set nocount on
create table #test111
(scode int,
sdesc varchar(30))
create index idx1_test111
on #test111(sdesc)
declare @.val1 int
set @.val1 = 1
while @.val1 < 10000
begin
insert into #test111
values(@.val1, convert(varchar(30),(@.val1 * @.val1)))
set @.val1 = @.val1 + 1
end
select * from #test111 where sdesc = 25
go
select * from #test111 where sdesc = '25'
"arzewski@.hotmail.com" wrote:

> just noticed this, wonder if there is a performance hit
> got results when executing the following
> select AgendaID
> from ObjectiveAgenda
> Inner Join Objective On Objective.ObjectiveID =
> ObjectiveAgenda.ObjectiveID
> problem is that the ObjectiveID column in one table is of SQL type
> INTEGER, while in the other table is of type VARCHAR.
> wonder if it is doing a translation behind the scenes, with some
> performance hit.
>

Join to select a 'weighted' column

Perhaps is just brain drain but i cannot seem find an efficient query to join two tables (inv and supplier) such that an inv item can have multiple suppliers and i would like to choose the prefered supplier based on the current 'weight' column.

declare @.inv table (item varchar(50), supplierid int)

declare @.supplier table (supplierid int, weight int)

set nocount on

insert into @.inv values ('item1', 1)

insert into @.inv values ('item1', 2)

insert into @.inv values ('item2', 2)

insert into @.inv values ('item2', 3)

insert into @.supplier values(1, 30)

insert into @.supplier values(2, 20)

insert into @.supplier values(3, 10)

-- the query should return the item and the supplierid associated to the lowest weight
-- item1 -> supplier 2
-- item2 -> supplier 3

select item, ps2.supplierid from @.supplier ps2 join

(select item, min(ps.weight)'weight'

from @.inv inv join @.supplier ps on inv.supplierid=ps.supplierid

group by item) iw on ps2.weight=iw.weight

Is there a better alternative to this?

Thanks in advance,

Mike

You can do the following in SQL Server 2005:

select item, supplierid
from (
select i.item, s.supplierid, row_number() over(partition by i.item order by s.weight) as wt
from @.supplier as s
join @.inv as i
on i.supplierid = s.supplierid
) as si
where wt = 1

But the most efficient way is to do below:

select item, cast(substring(wt, 5, 4) as int) as supplierid
from (
select i.item, min(cast(s.weight as binary(4)) + cast(s.supplierid as binary(4))) as wt
from @.supplier as s
join @.inv as i
on i.supplierid = s.supplierid
group by i.item
) as si

The second method will work only if the weight/supplierid values are greater than or equal to zero due to the conversion to binary. You can make it work for negative values also by modifying the expression. The trick is to get a sortable value using a combination of weight/supplierid that you can apply the aggregate function on and then get the individual values out.

|||

Thank you very much,

I will look at both.

Mike

Join tables in SQL 6.5 with SQL 7.0

I have a table in SQL that contains a timestamp column and I'm traying access it from a select statement in SQL 7.0, but I have a problem..., this is the error message:Server:
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata for a column. Metadata information was changed at execution time.\
but I can access other table in the same server...Consider simply not selecting the timestamp column. (Do not attempt to update or insert values into timestamp columns either.)

Timestamp columns generally do not need to be accessed for typical user purposes. Timestamp columns are system updated each time a row is inserted or updated (in a table object containing a timestamp column)

Join Table Key to Multiple Table Names

Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:

I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.

That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.

Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.

ThxWouldn't it be a lot less trouble to have one table for stock, one table for jobs, and one table to show job-stock-usage? That way you could have a practical infinity of jobs and stocks with only three tables.

This is a fundamental database design process called normalization. It is the key to maintaining your sanity as your projects grow!

-PatP|||I totally agree...I don't like this design whatsoever but its all I have to work with because I didn't design it and it's the way things are being done right now.|||I believe that particular schema is taken directly from Chapter 6, page 142 of the ever popular and best-selling book, "WORLD'S WORST DATABASE DESIGNS".

You are going to have to use dynamic SQL to solve this. Essentially, you will construct your SQL statement as a string concatenating the value of jobID in as the table name, and then execute the string.

Developers like that ought to be shot.|||thx for the reply blindman. I was leaning that way but I'm fairly new to using ms sql. I'm currently reading up on dynamic sql and seeing how it works. I need to generate a view out of this somehow.

Thx again|||I'd join the quest for shooting such developers...And Google returns NOTHING on the search for the best-seller...Did you buy the last copy? Maybe tkat11's developer can come up with the second edition...by popular demand ;)|||-----
lol|||I'd join the quest for shooting such developers...And Google returns NOTHING on the search for the best-seller...Did you buy the last copy? Maybe tkat11's developer can come up with the second edition...by popular demand ;)I don't know that it has ever been officially published, it is more of a "work in progress" kind of thing. Every time they think they're ready to publish, somebody runs in yelling "You've got to see this one" and they go right back to editing!

-PatP|||73% of the database designs in that book, WORLD'S WORST DATABASE DESIGNS, were written by php programmers, who design tables like that all the time

18% of them were written by people who've spent too much time at dbdebumph.com and have drunk the koolaid -- not a null in sight!!

9% were written by oracle developers who thought that storing a whole nested table inside a field was a neat idea and supports their concept of object-oriented encapsulation|||Man, you NAILED it with #3!!! I'll buy you a round (12 pack or a bottle of your choice) whenever you're in town!!! Though I see so much of #1 that it's not even funny any more...I wish images from Unreal Tornament were real sometimes...|||thank you, thank you, i'll be here all week, try the veal and don't forget to tip your waitress

:cool:|||no sooner do i say it, and another example of a design in the first category pops up: this thread (http://forums.devshed.com/t199186/s.html)

happens all the time|||This is SO ironic...I just declined an offer from a real estate marketing company which excercised a similar design "strategy" ... They even asked me at the interview how I would resolve this situation... Of course my answer was to FIRE the designer of the current database first ;)

join SmallDateTime column very slow

Hello,
I posted this on programming group, but hasn't got any reply yet.
I have a table and the definition is like:
CarTable(
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
ModelID,
MakeID,
RegisterDate smallDateTime null
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ModelID and MakeID are foreign keys from a reference table (call it
refTable here).
I created an idex on MakeID, modelID and RegisterDate as:
CREATE UNIQUE CLUSTERED INDEX
[IX_vwVehicleMain_ReportingAggregate_Aggregate] ON [CarTable]
(
[MakeID] ASC,
[ModelID] ASC,
[RegisterDate] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
[INDEX_FG]
The CarTable is quite big (50 million records)
When I do query such as
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID= RefTable.ModelID)
It is fairly quick - 2 seonds
However, if I put RegisterDate in the join condition, it becomes very
slow. For example:
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID= RefTable.ModelID and
CarTable.RegisterDate > '01/01/1980')
This one is much slower 2 minutes.
My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
Do I need to add an non-clustered index just for RegisterDate?
Many ThanksReplied to in .programming.
Please do not multi-post. If you want to ask the same question in
several newsgroups then the prefered method is to cross-post. This
prevents double answers (and double effort).
--
Gert-Jan
DAXU@.hotmail.com wrote:
> Hello,
> I posted this on programming group, but hasn't got any reply yet.
> I have a table and the definition is like:
> CarTable(
> [RowNumber] [int] IDENTITY(0,1) NOT NULL,
> ModelID,
> MakeID,
> RegisterDate smallDateTime null
> PRIMARY KEY CLUSTERED
> (
> [RowNumber] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> ModelID and MakeID are foreign keys from a reference table (call it
> refTable here).
> I created an idex on MakeID, modelID and RegisterDate as:
> CREATE UNIQUE CLUSTERED INDEX
> [IX_vwVehicleMain_ReportingAggregate_Aggregate] ON [CarTable]
> (
> [MakeID] ASC,
> [ModelID] ASC,
> [RegisterDate] ASC
> )WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
> = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
> [INDEX_FG]
> The CarTable is quite big (50 million records)
> When I do query such as
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=> RefTable.ModelID)
> It is fairly quick - 2 seonds
> However, if I put RegisterDate in the join condition, it becomes very
> slow. For example:
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=> RefTable.ModelID and
> CarTable.RegisterDate > '01/01/1980')
> This one is much slower 2 minutes.
> My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
> Do I need to add an non-clustered index just for RegisterDate?
> Many Thanks

join SmallDateTime column very slow

Hello,
I posted this on programming group, but hasn't got any reply yet.
I have a table and the definition is like:
CarTable(
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
ModelID,
MakeID,
RegisterDate smallDateTime null
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ModelID and MakeID are foreign keys from a reference table (call it
refTable here).
I created an idex on MakeID, modelID and RegisterDate as:
CREATE UNIQUE CLUSTERED INDEX
& #91;IX_vwVehicleMain_ReportingAggregate_
Aggregate] ON [CarTable]
(
[MakeID] ASC,
[ModelID] ASC,
[RegisterDate] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
[INDEX_FG]
The CarTable is quite big (50 million records)
When I do query such as
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
RefTable.ModelID)
It is fairly quick - 2 seonds
However, if I put RegisterDate in the join condition, it becomes very
slow. For example:
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
RefTable.ModelID and
CarTable.RegisterDate > '01/01/1980')
This one is much slower 2 minutes.
My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
Do I need to add an non-clustered index just for RegisterDate?
Many ThanksReplied to in .programming.
Please do not multi-post. If you want to ask the same question in
several newsgroups then the prefered method is to cross-post. This
prevents double answers (and double effort).
Gert-Jan
DAXU@.hotmail.com wrote:
> Hello,
> I posted this on programming group, but hasn't got any reply yet.
> I have a table and the definition is like:
> CarTable(
> [RowNumber] [int] IDENTITY(0,1) NOT NULL,
> ModelID,
> MakeID,
> RegisterDate smallDateTime null
> PRIMARY KEY CLUSTERED
> (
> [RowNumber] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> ModelID and MakeID are foreign keys from a reference table (call it
> refTable here).
> I created an idex on MakeID, modelID and RegisterDate as:
> CREATE UNIQUE CLUSTERED INDEX
> & #91;IX_vwVehicleMain_ReportingAggregate_
Aggregate] ON [CarTable]
> (
> [MakeID] ASC,
> [ModelID] ASC,
> [RegisterDate] ASC
> )WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
> = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
> [INDEX_FG]
> The CarTable is quite big (50 million records)
> When I do query such as
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
> RefTable.ModelID)
> It is fairly quick - 2 seonds
> However, if I put RegisterDate in the join condition, it becomes very
> slow. For example:
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
> RefTable.ModelID and
> CarTable.RegisterDate > '01/01/1980')
> This one is much slower 2 minutes.
> My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
> Do I need to add an non-clustered index just for RegisterDate?
> Many Thanks

Join problem

Hello somebody,
I have a problem with my query... I am trying to display results from one table that is not in the other. Column partner_1 and partner_2 from table couples is linked to user_id on table contestants. Each time I run a query it gives me either double or triple results even results that are in the couples table which shouldn't be.

Here are some of the queries I've tried already:
SELECT contestants.user_id
FROM contestants
INNER JOIN couples
ON contestants.user_id <> couples.partner_1
AND contestants.user_id <> couples.partner_2

SELECT contestants.user_id
FROM contestants,couples
WHERE contestants.user_id <> couples.partner_1
AND contestants.user_id <> couples.partner_2

---

Here is my exported tables:
CREATE TABLE contestants (
user_id mediumint(8) unsigned NOT NULL auto_increment,
fname varchar(30) NOT NULL,
lname varchar(30) NOT NULL,
address tinytext NOT NULL,
city tinytext NOT NULL,
state varchar(30) NOT NULL,
country varchar(26) NOT NULL,
email varchar(30) NOT NULL,
sex set('M','F') NOT NULL default 'F',
DOB date NOT NULL COMMENT 'User''s date of birth (year-month-day)',
gebruiker varchar(25) character set latin1 collate latin1_general_cs NOT NULL COMMENT 'Username of the contestant',
wachtwoord tinytext character set latin1 collate latin1_general_cs NOT NULL COMMENT 'Password of the contestant',
DOR datetime NOT NULL COMMENT 'GMT Date and time of registration',
PRIMARY KEY (user_id),
UNIQUE KEY gebruiker (gebruiker),
UNIQUE KEY email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE couples (
couple_id mediumint(8) unsigned NOT NULL auto_increment,
partner_1 mediumint(8) unsigned NOT NULL,
partner_2 mediumint(8) unsigned default NULL,
PRIMARY KEY (couple_id),
KEY partner_1 (partner_1),
KEY partner_2 (partner_2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `couples`
ADD CONSTRAINT couples_ibfk_3 FOREIGN KEY (partner_2) REFERENCES contestants (user_id) ON UPDATE CASCADE,
ADD CONSTRAINT couples_ibfk_2 FOREIGN KEY (partner_1) REFERENCES contestants (user_id) ON UPDATE CASCADE;

Can anybody help me with this problem?

Thanks in advance.

If I understand the problem correctly, you're looking for contestants without partners. This is the approach I typically use, and is known as an "anti-join," i.e. you want to find all the rows that don't match the condition. The trick is to simply exploit the behavior of an outer join, and look specifically for the unmatched NULL columns (make sure you aren't putting a nullable column in the WHERE clause or you might get dubious results).

Code Snippet

SELECT contestants.user_id
FROM contestants
LEFT OUTER JOIN couples
ON contestants.user_id = couples.partner_1
OR contestants.user_id = couples.partner_2
WHERE couples.partner_1 IS NULL

I obviously don't have your data to test against, but this will probably get you going in the right direction.

Now, I see you've got 'ENGINE=InnoDB' on your CREATE TABLE scripts, which makes me think this is running against MySQL and not SQL Server. I can't be totally sure this works there, but it's pretty standard syntax so I suspect it will.
|||Hello Davidbrit2,
You really saved my life!
Thank you so much.

Terrance

Join Performance

All
I have a quick question regarding the performance of a join.
I am performing a join between two tables and one table has the opid column
as VARCHAR(100) and the other table has the opid column as VARCHAR(8). Will
there be any performance gain if I change the opid column in both tables to
VARCHAR(8).
ThanksDavid
Well , it should not be, it is more question about indexes you defined on
those columns.
"David" <David@.discussions.microsoft.com> wrote in message
news:DA2B8E32-3BAB-43D6-AF9B-0677987F2349@.microsoft.com...
> All
> I have a quick question regarding the performance of a join.
> I am performing a join between two tables and one table has the opid
> column
> as VARCHAR(100) and the other table has the opid column as VARCHAR(8).
> Will
> there be any performance gain if I change the opid column in both tables
> to
> VARCHAR(8).
> Thanks|||no performance advantages to making the columns same length.

join on column with different data types

Hi.
I have a query where a single column is used as join key.
In TableA the column is nvarchar and in TableB it is varchar.
TableB has a clustered index on that column but the join will do a full
table scan
on TableB.
When I convert the coresponding column in TableA to varchar the query use
the index.
The join query will not do an implicit convert between varchar and
nvarchar.
Perhaps as designed.
Where can I find documentation on this issue.
I have been searching books online and Googles but haven't found it.
Sqlserver 2000 or 7.0
--
/dg
----
Dan van Ginhoven
SchlumbergerSema AB
S-412 97 GÖTEBORG Sweden
Phone +46 317 51 44 13
Mob/Cell +46 708 51 44 13convert(varchar(10, column_tableA) = column_tableB
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uwiH8IuPDHA.2480@.tk2msftngp13.phx.gbl...
> Hi.
> I have a query where a single column is used as join key.
> In TableA the column is nvarchar and in TableB it is varchar.
> TableB has a clustered index on that column but the join will do a full
> table scan
> on TableB.
> When I convert the coresponding column in TableA to varchar the query use
> the index.
> The join query will not do an implicit convert between varchar and
> nvarchar.
> Perhaps as designed.
> Where can I find documentation on this issue.
> I have been searching books online and Googles but haven't found it.
> Sqlserver 2000 or 7.0
> --
> /dg
> ----
> Dan van Ginhoven
> SchlumbergerSema AB
> S-412 97 GÖTEBORG Sweden
> Phone +46 317 51 44 13
> Mob/Cell +46 708 51 44 13
>|||There is a chart which shows which data types are implicitly or explicitly
convertable in books on line search for convert... The chart indicates that
SQL can implicitly convert between nchar/nvarchar and char/varchar
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uwiH8IuPDHA.2480@.tk2msftngp13.phx.gbl...
> Hi.
> I have a query where a single column is used as join key.
> In TableA the column is nvarchar and in TableB it is varchar.
> TableB has a clustered index on that column but the join will do a full
> table scan
> on TableB.
> When I convert the coresponding column in TableA to varchar the query use
> the index.
> The join query will not do an implicit convert between varchar and
> nvarchar.
> Perhaps as designed.
> Where can I find documentation on this issue.
> I have been searching books online and Googles but haven't found it.
> Sqlserver 2000 or 7.0
> --
> /dg
> ----
> Dan van Ginhoven
> SchlumbergerSema AB
> S-412 97 GÖTEBORG Sweden
> Phone +46 317 51 44 13
> Mob/Cell +46 708 51 44 13
>|||Hi Wayne!
Yes I have seen the chart. It surprises me a bit that the query didn't use
the index.
It may be bug.
I´m looking for a text that describes in what situations the Query Planner
will not use an
index, but will do a full table scan. I think I have seen it once.
One example is when a query contains <column> like '%value%'
it won´t use an index on that column to solve the query.
/dg|||> Yes I have seen the chart. It surprises me a bit that the query didn't use
> the index.
> It may be bug.
Nope, it is how SQL Server works. Although you don't have to write any code
for an implicit conversion SQL Server still converts one datatype to another
when it creates the execution plan. If you run the following code in Query
Analyzer and look at the execution plan you will see that #B.B is converted
before the two tables are joined:
CREATE TABLE #A (A nvarchar(20))
GO
CREATE TABLE #B (B varchar(20))
GO
SELECT * FROM #A
INNER JOIN #B
ON #A.A = #B.B
GO
DROP TABLE #A, #B
GO
Why is #B.B converted and not #A.A? That is determined by the Data Type
Precedence. varchar has a lower Data Type Precedence than nvarchar, so
varchar gets converted. (You can find the complete list of the data type
precedence in Books Online under Data Type Precedence).
Because #B.B is used in a function (when it is converted), the Query
Optimizer can't use any indexes on the column and has to use a table scan.
As you saw when the other (nvarchar) column in the join is explicitly
converted, the varchar column won't be implicitly converted and the index on
the varchar column can be used.
About a text: Kalen Delaney has written a series of articles for SQL Server
Magazine (www.sqlmag.com) about which search conditions can make use of
indexes and which don't, and there is also a bit about it in her book Inside
SQL Server 2000.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uPHxWevPDHA.1720@.TK2MSFTNGP11.phx.gbl...
> Hi Wayne!
> Yes I have seen the chart. It surprises me a bit that the query didn't use
> the index.
> It may be bug.
> I´m looking for a text that describes in what situations the Query
Planner
> will not use an
> index, but will do a full table scan. I think I have seen it once.
> One example is when a query contains <column> like '%value%'
> it won´t use an index on that column to solve the query.
> /dg
>