Wednesday, March 28, 2012
Query work in sql 200 fails in sql 2005
The query below, has been succesfully working in sql 2000 for months.
While I recongnize that the isnumeric attribute is reference twice
(this has since been corrected).
My concern is why did this generate an error in sql 2005 and not sql
2000.
Error = (Duplicate column names are not allowed in result sets obtained
through OPENQUERY and OPENROWSET.)
I am concerned that I've missed a server setting.
If this is just a case where it should of failed in 2000 I would feel
much better.
Any insight into this would be greatly appreciated.
Thanks,
Henry Lovera
--
insert into @.deal_properties (deal_property_id,
parent_deal_property_id, is_numeric, property_name, property_format,
display_order, string_value, numeric_value)
select
deal_property_id
,parent_deal_property_id
,is_numeric
,property_name
,property_format
,display_order
,string_value = case when is_numeric = 0 then value
end
,numeric_value = case when is_numeric = 1 then convert(float,
value) end
from
openxml(@.xml_doc, '//deal_property', 1)
with( deal_property_id int
,parent_deal_property_id int
,is_numeric int
,property_name varchar(32)
,property_format varchar(16)
,display_order int
,is_numeric bit
,value varchar(100))Henry
I was not able to test it because the script throws lots of errors.
Please post proper DDL+ sample data.
<hanklvr@.yahoo.com> wrote in message
news:1143684314.846726.121030@.t31g2000cwb.googlegroups.com...
> Hello all,
> The query below, has been succesfully working in sql 2000 for months.
> While I recongnize that the isnumeric attribute is reference twice
> (this has since been corrected).
> My concern is why did this generate an error in sql 2005 and not sql
> 2000.
> Error = (Duplicate column names are not allowed in result sets obtained
> through OPENQUERY and OPENROWSET.)
> I am concerned that I've missed a server setting.
> If this is just a case where it should of failed in 2000 I would feel
> much better.
> Any insight into this would be greatly appreciated.
> Thanks,
> Henry Lovera
> --
> insert into @.deal_properties (deal_property_id,
> parent_deal_property_id, is_numeric, property_name, property_format,
> display_order, string_value, numeric_value)
> select
> deal_property_id
> ,parent_deal_property_id
> ,is_numeric
> ,property_name
> ,property_format
> ,display_order
> ,string_value = case when is_numeric = 0 then value
> end
> ,numeric_value = case when is_numeric = 1 then convert(float,
> value) end
> from
> openxml(@.xml_doc, '//deal_property', 1)
> with( deal_property_id int
> ,parent_deal_property_id int
> ,is_numeric int
> ,property_name varchar(32)
> ,property_format varchar(16)
> ,display_order int
> ,is_numeric bit
> ,value varchar(100))
>
Monday, March 26, 2012
Query with a lot of left joins - Rewrite?
Hello,
I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?
How would I rewrite left joins? Any examples?
Thanks.
Moving to T-SQL inside SQL Server, where you are most likely to find experts to answer this question.|||When you have that many referenced tables/joins, you will get a perf hit, espcially if you have large tables involved. The only trick in the book that helps is to filter large tables down to smaller #temp tables. Then use these #temp tables for your join. You will reduce the log need to process your large tables.
e.g.
Code Snippet
select *
into #tmp1
from large_table1
where filter=123
select *
into #tmp2
from large_table2
where filter=xyz
select *
from #tmp1 left join #tmp2 on ...
|||Before applying any kind of performance tuning to your query, first answer this question. Is there any performance issue when testing it? There is nothing particularly slow about a query with 11 left joins, if your data is structured correctly and (very important) indexex correctly.
The kinds of things you can "guess" and do (like breaking down into temp tables, as OJ states) are excellent strategies if the optimizer fails you, but 9 times out of 10, even for really complex queries, SQL Server will do whatever you are trying to do to improve performance of the joins on their own.
If you don't have a test environment with adeqate amounts of data to do performance testing, this process is a heck of a lot more work, but as a rule, I don't like to do any performance guessing, do performance testing/reactive tuning BEFORE you get to your production environment if at all possible.
Query with a lot of left joins - Rewrite?
Hello,
I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?
How would I rewrite left joins? Any examples?
Thanks.
Moving to T-SQL inside SQL Server, where you are most likely to find experts to answer this question.|||When you have that many referenced tables/joins, you will get a perf hit, espcially if you have large tables involved. The only trick in the book that helps is to filter large tables down to smaller #temp tables. Then use these #temp tables for your join. You will reduce the log need to process your large tables.
e.g.
Code Snippet
select *
into #tmp1
from large_table1
where filter=123
select *
into #tmp2
from large_table2
where filter=xyz
select *
from #tmp1 left join #tmp2 on ...
|||Before applying any kind of performance tuning to your query, first answer this question. Is there any performance issue when testing it? There is nothing particularly slow about a query with 11 left joins, if your data is structured correctly and (very important) indexex correctly.
The kinds of things you can "guess" and do (like breaking down into temp tables, as OJ states) are excellent strategies if the optimizer fails you, but 9 times out of 10, even for really complex queries, SQL Server will do whatever you are trying to do to improve performance of the joins on their own.
If you don't have a test environment with adeqate amounts of data to do performance testing, this process is a heck of a lot more work, but as a rule, I don't like to do any performance guessing, do performance testing/reactive tuning BEFORE you get to your production environment if at all possible.
Monday, March 12, 2012
Query to get name of views which reference a give column from a ta
Can somebody give me a query to find the name of all the views which referen
ce
a give column name of a table
ex: i want to find names of all views which have the column account_type
from the table accounts.
Thanks
RodgerRodger,
check view INFORMATION_SCHEMA.COLUMNS
Example:
use northwind
go
declare @.cn sysname
set @.cn = N'OrderID'
select TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = @.cn and
objectproperty(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'),
'IsView') = 1
go
AMB
"Rodger" wrote:
> Hi
> Can somebody give me a query to find the name of all the views which refer
ence
> a give column name of a table
> ex: i want to find names of all views which have the column account_type
> from the table accounts.
> Thanks
> Rodger
Query to get name of views which reference a give column from
l
the tables ,
any additions '
Thanks a Lot
"Alejandro Mesa" wrote:
> Rodger,
> check view INFORMATION_SCHEMA.COLUMNS
> Example:
> use northwind
> go
> declare @.cn sysname
> set @.cn = N'OrderID'
> select TABLE_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where COLUMN_NAME = @.cn and
> objectproperty(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'),
> 'IsView') = 1
> go
>
> AMB
> "Rodger" wrote:
>use information_Schema.view_column_usage
"Rodger" wrote:
> I have 3 to 4 tables with the same column names, so it displays views for
all
> the tables ,
> any additions '
> Thanks a Lot
> "Alejandro Mesa" wrote:
>|||It worked...great... never tried these wonderful views.
Thanks
rodger
"Omnibuzz" wrote:
> use information_Schema.view_column_usage
> "Rodger" wrote:
>|||Check out all the view in INFORMATION_SCHEMA. if you are interested. gives a
lot of metadata. And if you have no further questions can you close this
thred.
Thanks
Omnibuzz
"Rodger" wrote:
> It worked...great... never tried these wonderful views.
> Thanks
> rodger
> "Omnibuzz" wrote:
>