Query longer in SP: create

Showing posts with label create. Show all posts

Friday, March 30, 2012

Query programmable

Hi,
I need to create a stored procedures that can pass table parameters in
varchar format.
for example:
CREATE FUNCTION MyFunc (@.table varchar(200)
AS
UPDATE @.table + 'aaa' -> How can I do this?
SET field1=tbl2.field2
FROM tbl2
WHERE tbl2.field3>10Use Dynamic SQL
Madhivanan|||You can try using dynamic SQLs
like this ..
declare @.t nvarchar(1000)
set @.t = 'UPDATE ' +@.table + 'aaa ' +
'SET field1=tbl2.field2 FROM tbl2 WHERE tbl2.field3>10 '
exec sp_executeSQL @.t|||UPDATE statements are not allowed in functions. Dynamic SQL is also
disallowed in functions. Perhaps a stored procedure will address your
needs.
Check out http://www.sommarskog.se/dynamic_sql.html for dynamic SQL
considerations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dario Concilio" <DarioConcilio@.discussions.microsoft.com> wrote in message
news:F6C4572F-6306-47CB-9087-5C3E2204C824@.microsoft.com...
> Hi,
> I need to create a stored procedures that can pass table parameters in
> varchar format.
> for example:
> CREATE FUNCTION MyFunc (@.table varchar(200)
> AS
> UPDATE @.table + 'aaa' -> How can I do this?
> SET field1=tbl2.field2
> FROM tbl2
> WHERE tbl2.field3>10|||I can't see why that would make sense as a requirement unless your
tables duplicated lots of non-key columns. In that case it's really a
design problem rather than something to solve with a parameterized
UPDATE.
Dynamic SQL is one way you can do this but in a well-designed database
it should rarely be necessary.
David Portas
SQL Server MVP
--|||>> I need to create a stored procedures that can pass table parameters
in varchar format. <<
The short answer is use slow, proprietrary dynamic SQL to kludge a
query together on the fly with your table name in the FROM clause.
The right answer is never pass a table name as a parameter. You need
to understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A
model of a set of entities or relationships. EACH TABLE SHOULD BE A
DIFFERENT KIND OF ENTITY. What having a generic procedure works
equally on automobiles, octopi or Britney Spear's discology is saying
that your applications a disaster of design.
1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.
2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember
the basics of Software Engineering? Modules need weak coupling and
strong cohesion, etc. This is far more fundamental than just SQL; it
has to do with learning to programming at all.
3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw. Look up the term
attribute splitting.
4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and applications
do not work at that level, if you want to have any data integrity.
Yes, you can write a program with dynamic SQL to kludge something like
this. It will last about a year in production and then your data
integrity is shot.sql

Wednesday, March 28, 2012

Query Problem

I am stuck with a query. May be I am missing something.Plz have a look...

CREATE TABLE [Order](
Orderid VARCHAR(10),
orderdate DATETIME)

CREATE TABLE Product(
Prodid VARCHAR(10),
ProdDes VARCHAR(20),
ProdPrice INT)

CREATE TABLE OrdDetails(
OrddetailsID VARCHAR(10),
OrdID VARCHAR(10),
ProdID VARCHAR(10))

INSERT INTO [Order] VALUES ('1','5/11/2006')
INSERT INTO [Order] VALUES ('2','5/11/2006')
INSERT INTO [Order] VALUES ('3','6/11/2006')

INSERT INTO Product VALUES ('1','TV',16)
INSERT INTO Product VALUES ('2','LCD',20)
INSERT INTO Product VALUES ('3','DVD',9)
INSERT INTO Product VALUES ('4','MP',19)

INSERT INTO OrdDetails VALUES ('1','1','1')
INSERT INTO OrdDetails VALUES ('2','1','2')
INSERT INTO OrdDetails VALUES ('3','1','3')
INSERT INTO OrdDetails VALUES ('4','2','2')
INSERT INTO OrdDetails VALUES ('5','2','4')
INSERT INTO OrdDetails VALUES ('6','3','2')

--Query

select dd.orderdate,

max(dd.totprice)

FROM

(select TOP 100 PERCENT dbo.[Order].orderdate,
dbo.[Order].Orderid,
SUM(dbo. Product .ProdPrice) AS TotPrice
FROM dbo.OrdDetails INNER JOIN
dbo.[Order] ON dbo.OrdDetails.OrdID = dbo.[Order].Orderid INNER JOIN
dbo. Product ON dbo.OrdDetails.ProdID = dbo. Product .Prodid
GROUP BY dbo.[Order].orderdate, dbo.[Order].Orderid
ORDER BY dbo.[Order].orderdate, dbo.[Order].Orderid)dd
group by dd.orderdate

--Query
Drop table [Order]
Drop Table Product
Drop Table OrdDetails

The output needed is maximum summation of the order no ,I mean group by orderdate then orderno

Orderdate Ordernumber
5 nov 1
6 nov 3

at this momnet I am getting the orderdate and price
Plz help
Thanks!!"maximum summation of the order no" ???|||"maximum summation of the order no" ???
Thanks Rudy,
The requirement is -
Date Orderid Sum(price)

5/11/2006 1 41
5/11/2006 2 39
6/11/2006 3 20

Now in each date we need to pick the highest sum(price) with respective to orderid
So the final ouptput should be
Orderdate Orderid
5/11/2006 1
6/11/2006 3
I think now its a bit more clear...|||select O.orderdate
, O.Orderid
, sum(P.ProdPrice) AS TotPrice
from dbo.[Order] as O
inner
join dbo.OrdDetails as OD
on OD.OrdID = O.Orderid
inner
join dbo.Product as P
on P.Prodid = OD.ProdID
group
by O.orderdate
, O.Orderid
having sum(P.ProdPrice) =
( select max(TP)
from (
select sum(dbo.Product.ProdPrice) AS TP
from dbo.[Order]
inner
join dbo.OrdDetails
on dbo.OrdDetails.OrdID
= dbo.[Order].Orderid
inner
join dbo.Product
on dbo.Product.Prodid
= dbo.OrdDetails.ProdID
where dbo.[Order].orderdate
= O.orderdate
group
by dbo.[Order].Orderid
) as same_day_orders
)
order
by O.orderdateresults:

2006-05-11 00:00:00 1 45
2006-06-11 00:00:00 3 20|||So what you really want is to find the orderid and orderdate for the order with the largest total price for a given day? If that is the case, then the best way that I know to get that answer is in stages, something like:CREATE TABLE #ordersByDate (
orderdate DATETIME
, orderid INT
, TotPrice INT
)

INSERT INTO #ordersByDate (
orderdate, orderid, TotPrice
) SELECT Convert(DATETIME, Convert(CHAR(10), o.orderdate, 121)) AS orderdate
, o.orderid
, Sum(p.ProdPrice) AS TotPrice
FROM dbo.[Order] AS o
INNER JOiN dbo.ordDetails AS od
ON (od.ordID = o.orderid)
INNER JOIN dbo.Product AS p
ON (p.prodId = od.ProdId)
GROUP BY o.orderdate, o.orderid

SELECT orderdate, orderid
FROM #ordersByDate AS obd
WHERE obd.TotPrice = (SELECT Max(z1.TotPrice)
FROM #ordersByDate AS z1
WHERE z1.orderdate = obd.orderdate)
ORDER BY obd.orderdate, obd.orderid

DROP TABLE #ordersByDateThe problem is that this kind of question "pokes at the seams" of the SQL Engine. Because this kind of query requires careful sequencing of query evaluation in order to determine what occurs where/when, and SQL (like most query languages) doesn't offer explicit control of sub-expression evaluation. That's actually a blessing, because you get really complicated really fast when you try to handle things like that in a language, and simply making sequential steps is easy to write, read, and understand.

I build a temp table, and populate it very similar to your derived table dd. Once I've populated that, I make a two stage pass through it to compare this row with the largest order for this row's date, and only return this row in the result set if it is the largest. Just a word of warning, this code does NOT eliminate ties, so it is possible to get multiple rows returned for a given date.

R937's solution is elegant, and it is a single SQL operation, but for large result sets I think it will be rather slow because of the work that it needs to do to generate every row.

-PatP|||Thank you Great Guys!! :) :)
Thanks for those wonderful solutions...
Wishing you both A very HAPPY NEW YEAR 2007!!
And wish you all a wonderful and prosperous New Year 2007.
Enjoy !!!
:beer:

Query problem

CREATE TABLE [Table1] (
[abc] [int] ,
[xyz] [char] (10)
)

insert into abc (1,'z')
insert into abc (2,'y')
insert into abc (3,'z')
select * from table1 where abc in (3,2)
i want the output as follows
3 z
2 y
not

2 y
3 z
please help me out
You should use an ORDER BY:
SELECT * FROM table1 WHERE abc IN (3,2) ORDER BY abc DESC
|||it seems i haven't posted the question properly,
it's not the case of 3 and 2
it may be
SELECT * FROM table1 WHERE abc IN (3,2,8,4,1,7)
then it won't work
i hope i made more clear the Q
thanks

|||No, I'm sorry it's not clear. I really have no idea what you arelooking for. Maybe you can explain with more examples.
|||Do you just want to order it in descending rather than ascending order?

Monday, March 26, 2012

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
--
How about changing it to:
dbo.LeadFactfindCompleted Between 01/01/2004 and 31/12/9999
Does it change the result? Is there an index on the LeadFactfindCompleted?
If so, does reindexing change the result?
Hope this helps,
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.|||You need to enclose date in single quotes. If you don't, you are doing integer arethmics, leading up to an
integer, then converting that integer to datetime and do the comparison (>, <, or whatever you do). Also,
please is a language neutral datetime format. See:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:1kjrc.17$_07.4@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
>
>sql

query problem

Hi,
we have a problem with query and data can be reproduced
as :
create table dd1(dd int,dd1 int)
create table dd(dd int,dd1 int)
insert into dd values(1,10)
insert into dd values(2,20)
insert into dd1 values(2,20)
insert into dd1 values(3,20)
THIS STATEMENT WORKS
--
select dd,sum(dd1) dd1,count(*) dd2 from dd
group by dd
union all
select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
group by dd
THIS STATEMENT IS GIVING ERROR AT GROUP BY CLAUSE
----
select dd,sum(dd1),sum(dd2)
from
((
select dd,sum(dd1) dd1,count(*) dd2 from dd
group by dd)
union all
(select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
group by dd)
)
group by dd
How to rewrite this query?
Thanks
--HarvinderHarvinder,
select dd,sum(dd1),sum(dd2)
from
((
select dd,sum(dd1) dd1,count(*) dd2 from dd
group by dd)
union all
(select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
group by dd)
) a
group by dd
the derived table needs an alias.
Quentin
"harvinder" <hs@.metratech.com> wrote in message
news:02b301c33f3b$1a33ab40$a301280a@.phx.gbl...
> Hi,
> we have a problem with query and data can be reproduced
> as :
> create table dd1(dd int,dd1 int)
> create table dd(dd int,dd1 int)
> insert into dd values(1,10)
> insert into dd values(2,20)
> insert into dd1 values(2,20)
> insert into dd1 values(3,20)
> THIS STATEMENT WORKS
> --
> select dd,sum(dd1) dd1,count(*) dd2 from dd
> group by dd
> union all
> select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
> group by dd
> THIS STATEMENT IS GIVING ERROR AT GROUP BY CLAUSE
> ----
> select dd,sum(dd1),sum(dd2)
> from
> ((
> select dd,sum(dd1) dd1,count(*) dd2 from dd
> group by dd)
> union all
> (select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
> group by dd)
> )
> group by dd
> How to rewrite this query?
> Thanks
> --Harvinder
>sql

Wednesday, March 21, 2012

Query Performance and Index

Dear Forum,
Please help me modify this query for optimum performance and suggest indexes
to create. Thanks. James
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinitionHi James
I added parenthesis to your query:
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,
MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND
BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
(BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
GROUP BY
BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
Please let me know if this is correct.
If it is correct, then there is no need of changing the query, your query
looks perfect.
There might be Primary Keys declared on the tables that are used here.
Else create Index on the columns that are involved in the where clause.
please let me know your comments
thanks and regards
Chandra
"James Juno" wrote:
> Dear Forum,
> Please help me modify this query for optimum performance and suggest indexes
> to create. Thanks. James
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition
>|||Adding to my previous post, what i feel is, the first OR should be replace
with AND:
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode AND
BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition
"Chandra" wrote:
> Hi James
> I added parenthesis to your query:
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,
> MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
> MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND
> BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> GROUP BY
> BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> Please let me know if this is correct.
> If it is correct, then there is no need of changing the query, your query
> looks perfect.
> There might be Primary Keys declared on the tables that are used here.
> Else create Index on the columns that are involved in the where clause.
> please let me know your comments
> thanks and regards
> Chandra
> "James Juno" wrote:
> > Dear Forum,
> >
> > Please help me modify this query for optimum performance and suggest indexes
> > to create. Thanks. James
> >
> > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition,
> > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition
> >|||Chandra,
This is great. Thank you so much. I haven't tried it yet - but it looks good.
James.
"Chandra" wrote:
> Hi James
> I added parenthesis to your query:
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,
> MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
> MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND
> BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> GROUP BY
> BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> Please let me know if this is correct.
> If it is correct, then there is no need of changing the query, your query
> looks perfect.
> There might be Primary Keys declared on the tables that are used here.
> Else create Index on the columns that are involved in the where clause.
> please let me know your comments
> thanks and regards
> Chandra
> "James Juno" wrote:
> > Dear Forum,
> >
> > Please help me modify this query for optimum performance and suggest indexes
> > to create. Thanks. James
> >
> > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition,
> > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition
> >|||To avoid confusion, use ansi join instead old style. Also use alias to make
the statement more readable.
SELECT
cc.MST_COURT_NAME,
ct.CTdefinition,
rt.RTdefinition,
c.StateFips,
MIN(substring(c.JudgmentDate, 1,6)),
MAX(substring(c.JudgmentDate, 1,6))
FROM
BBNew.dbo.fffCases as c
inner join
BBNew.dbo.fffCourtCodes as cc
on c.CourtCode = cc.MST_COURT_CODE
inner join
BBNew.dbo.fffCourtTypes as ct
on c.CourtType = ct.CTcode
inner join
BBNew.dbo.fffRecordTypes as rt
on c.FilingType = rt.RTcode
WHERE
c.StateFips LIKE :Param_State
OR c.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY
c.StateFips,
cc.MST_COURT_NAME,
ct.CTdefinition,
rt.RTdefinition;
Be sure to have an index by:
- BBNew.dbo.fffCases.PostedDate <-- clustered one
- BBNew.dbo.fffCases.StateFips
- BBNew.dbo.fffCases.CourtCode
- BBNew.dbo.fffCases.CourtType
- BBNew.dbo.fffCases.FilingType
- BBNew.dbo.fffCourtCodes.MST_COURT_CODE
- BBNew.dbo.fffCourtTypes.CTcode
- BBNew.dbo.fffRecordTypes.rt.RTcode
AMB
"James Juno" wrote:
> Chandra,
> This is great. Thank you so much. I haven't tried it yet - but it looks good.
> James.
> "Chandra" wrote:
> > Hi James
> > I added parenthesis to your query:
> > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition,
> > BBNew.dbo.fffCases.StateFips,
> > MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
> > MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> >
> > FROM
> > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> > BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> >
> > WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > AND
> > BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> >
> > (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> > OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> > OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> >
> > GROUP BY
> > BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> >
> > Please let me know if this is correct.
> >
> > If it is correct, then there is no need of changing the query, your query
> > looks perfect.
> > There might be Primary Keys declared on the tables that are used here.
> >
> > Else create Index on the columns that are involved in the where clause.
> >
> > please let me know your comments
> >
> > thanks and regards
> > Chandra
> >
> > "James Juno" wrote:
> >
> > > Dear Forum,
> > >
> > > Please help me modify this query for optimum performance and suggest indexes
> > > to create. Thanks. James
> > >
> > > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > BBNew.dbo.fffRecordTypes.RTdefinition,
> > > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > BBNew.dbo.fffRecordTypes.RTdefinition
> > >|||Alejandro,
Great. Thank you.
James
"Alejandro Mesa" wrote:
> To avoid confusion, use ansi join instead old style. Also use alias to make
> the statement more readable.
> SELECT
> cc.MST_COURT_NAME,
> ct.CTdefinition,
> rt.RTdefinition,
> c.StateFips,
> MIN(substring(c.JudgmentDate, 1,6)),
> MAX(substring(c.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCases as c
> inner join
> BBNew.dbo.fffCourtCodes as cc
> on c.CourtCode = cc.MST_COURT_CODE
> inner join
> BBNew.dbo.fffCourtTypes as ct
> on c.CourtType = ct.CTcode
> inner join
> BBNew.dbo.fffRecordTypes as rt
> on c.FilingType = rt.RTcode
> WHERE
> c.StateFips LIKE :Param_State
> OR c.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY
> c.StateFips,
> cc.MST_COURT_NAME,
> ct.CTdefinition,
> rt.RTdefinition;
> Be sure to have an index by:
> - BBNew.dbo.fffCases.PostedDate <-- clustered one
> - BBNew.dbo.fffCases.StateFips
> - BBNew.dbo.fffCases.CourtCode
> - BBNew.dbo.fffCases.CourtType
> - BBNew.dbo.fffCases.FilingType
> - BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> - BBNew.dbo.fffCourtTypes.CTcode
> - BBNew.dbo.fffRecordTypes.rt.RTcode
>
> AMB
>
> "James Juno" wrote:
> > Chandra,
> >
> > This is great. Thank you so much. I haven't tried it yet - but it looks good.
> >
> > James.
> >
> > "Chandra" wrote:
> >
> > > Hi James
> > > I added parenthesis to your query:
> > > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > BBNew.dbo.fffRecordTypes.RTdefinition,
> > > BBNew.dbo.fffCases.StateFips,
> > > MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
> > > MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> > >
> > > FROM
> > > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> > > BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> > >
> > > WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > > AND
> > > BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > >
> > > (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> > > OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> > > OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> > >
> > > GROUP BY
> > > BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> > >
> > > Please let me know if this is correct.
> > >
> > > If it is correct, then there is no need of changing the query, your query
> > > looks perfect.
> > > There might be Primary Keys declared on the tables that are used here.
> > >
> > > Else create Index on the columns that are involved in the where clause.
> > >
> > > please let me know your comments
> > >
> > > thanks and regards
> > > Chandra
> > >
> > > "James Juno" wrote:
> > >
> > > > Dear Forum,
> > > >
> > > > Please help me modify this query for optimum performance and suggest indexes
> > > > to create. Thanks. James
> > > >
> > > > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > > BBNew.dbo.fffRecordTypes.RTdefinition,
> > > > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > > > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > > > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > > > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > > > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > > > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > > > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > > > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > > > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > > > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > > BBNew.dbo.fffRecordTypes.RTdefinition
> > > >|||In general, it is a good practice to create a Primary Key constraint on
all tables (which will automatically create a unique index), and to
create indexes on foreign key constraints.
You did not post any DDL, so the keys and indexes cannot be reviewed.
But the query would benefit if all join columns were indexed.
Gert-Jan
James Juno wrote:
> Dear Forum,
> Please help me modify this query for optimum performance and suggest indexes
> to create. Thanks. James
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition|||Gert-Jan,
I got the script that works. Thanks for your contribution.
James
"Gert-Jan Strik" wrote:
> In general, it is a good practice to create a Primary Key constraint on
> all tables (which will automatically create a unique index), and to
> create indexes on foreign key constraints.
> You did not post any DDL, so the keys and indexes cannot be reviewed.
> But the query would benefit if all join columns were indexed.
> Gert-Jan
>
> James Juno wrote:
> >
> > Dear Forum,
> >
> > Please help me modify this query for optimum performance and suggest indexes
> > to create. Thanks. James
> >
> > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition,
> > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition
>

Query Performance and Index

Tuesday, March 20, 2012

query parameter

I have to create a stored procedure where the criteria is: "All", specific value and, all except one value

so far this is what I have:

@.Status varchar (50) -- as parameter from a dropdown box

DECLARE @.NewStatusvarchar(50)SET @.NewStatus =CASEWHEN @.Status ='All'AND @.Status <>'All but closed'THEN'%'WHEN @.Status <>'All'AND @.Status <>'All but closed'THEN @.StatusENDand in the storedprocedure.......WHERE Statuslike @.NewStatus

I am a little confused as to how could I return all values except those that have the value "Closed"

Thanks.

in your where clause just add

AND status <> 'closed' or if you had multiple status' status: not in (x, y, z) if that is not helpful, give me a little more info and i will try and help you some more...--jp

|||

Finally I came up with this solution:

DECLARE @.NewStatusvarchar(50)SET @.NewStatus =CASEWHEN @.Status <>'All'AND @.Status <>'All but closed'THEN @.Statuselse'%'ENDDECLARE @.NewStatusNotvarchar(50)SET @.NewStatusNot =CASE @.StatusWHEN'All but closed'THEN'Closed'else'zz'END.........WHEREStatus.Statuslike @.NewStatusANDStatus.Statusnot like @.NewStatusNot

Not sure if this the best solution but it works Smile

It is the same idea that you have suggested.

Friday, March 9, 2012

Query Optimization

Hi,
I have a DB-based application, which has a UDF like this:
CREATE FUNCTION fn_concat(@.A varchar(255), @.B varchar(255))
RETURNS varchar(255)
AS BEGIN
RETURN coalesce(@.A,@.B)
END
Using SQL-Server 2000 I execute the following statement:
SELECT DISTINCT
A.a, dbo.sp_concat(A.b, A.c) as x
FROM
A LEFT OUTER JOIN B ON
A.id = B.id
Since the statement is only selecting columns from table "A", it is
being optimized so that the join with table "B" is not being executed.
Because table "B" is quite large, this saves quite some execution-time.
When this statement is being executed on a SQL-Server 2005 the join is
being executed, resulting in a much longer execution-time. This seems to
be because of the UDF, because if this is being left out, the optimizer
eliminates the processing of table "B".
Background: I have a view, which consists of a lot of joins of several
tables, and I dynamically build the select-clause of the statement in my
application. Because the optimizer only processes the tables that are
actually being used in the select-statement this is an easy way to not
deal with the joins in the application itself.
But why is the use of the function changing the behavior of the 2005
optimizer?
--
Henning Eiben
busitec GmbH
Consultant
e-mail: eiben@.busitec.de
+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax
Rudolf-Diesel-Straße 59
48157 Münster
www.busitec.de
Sitz der Gesellschaft: Münster
HR B 55 75 - Amtsgericht Münster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschäftsführer: Simon Böwer, Henning Eiben, Stefan Kühn, Martin Saalmann
--
... There are 10 kinds of people. Those who know binary code, and those
who don't.I'm going to step out a limb here...
Does this not return the same result?
SELECT DISTINCT
A.a, dbo.sp_concat(A.b, A.c) as x
FROM
A
Why bother with the join if this is what you're doing?
Cheers,
Jason Lepack
On Aug 1, 5:35 am, Henning Eiben <ei...@.busitec.de> wrote:
> Hi,
> I have a DB-based application, which has a UDF like this:
> CREATE FUNCTION fn_concat(@.A varchar(255), @.B varchar(255))
> RETURNS varchar(255)
> AS BEGIN
> RETURN coalesce(@.A,@.B)
> END
> Using SQL-Server 2000 I execute the following statement:
> SELECT DISTINCT
> A.a, dbo.sp_concat(A.b, A.c) as x
> FROM
> A LEFT OUTER JOIN B ON
> A.id =3D B.id
> Since the statement is only selecting columns from table "A", it is
> being optimized so that the join with table "B" is not being executed.
> Because table "B" is quite large, this saves quite some execution-time.
> When this statement is being executed on a SQL-Server 2005 the join is
> being executed, resulting in a much longer execution-time. This seems to
> be because of the UDF, because if this is being left out, the optimizer
> eliminates the processing of table "B".
> Background: I have a view, which consists of a lot of joins of several
> tables, and I dynamically build the select-clause of the statement in my
> application. Because the optimizer only processes the tables that are
> actually being used in the select-statement this is an easy way to not
> deal with the joins in the application itself.
> But why is the use of the function changing the behavior of the 2005
> optimizer?
> --
> Henning Eiben
> busitec GmbH
> Consultant
> e-mail: ei...@.busitec.de
> +49 (251) 13335-0 Tel
> +49 (251) 13335-35 Fax
> Rudolf-Diesel-Stra=DFe 59
> 48157 M=FCnsterwww.busitec.de
> Sitz der Gesellschaft: M=FCnster
> HR B 55 75 - Amtsgericht M=FCnster
> USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
> Gesch=E4ftsf=FChrer: Simon B=F6wer, Henning Eiben, Stefan K=FChn, Martin =Saalmann
> --
> ... There are 10 kinds of people. Those who know binary code, and those
> who don't.|||Jason Lepack wrote:
> I'm going to step out a limb here...
> Does this not return the same result?
> SELECT DISTINCT
> A.a, dbo.sp_concat(A.b, A.c) as x
> FROM
> A
> Why bother with the join if this is what you're doing?
>
actually I have a view
CREATE VIEW dbo.SomeView AS
SELECT A.*, B.*
FROM
A LEFT OUTER JOIN B ON
A.id = B.id
and my SQL-Statement looks like this:
SELECT DISTINCT
A.a, dbo.sp_concat(A.b, A.c) as x
FROM
dbo.SomeView
This way I can create the select-clause in my app, and since I'm using
the view in the from-clause, I don't have to deal with the join ...
--
Henning Eiben
busitec GmbH
Consultant
e-mail: eiben@.busitec.de
+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax
Rudolf-Diesel-Straße 59
48157 Münster
www.busitec.de
Sitz der Gesellschaft: Münster
HR B 55 75 - Amtsgericht Münster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschäftsführer: Simon Böwer, Henning Eiben, Stefan Kühn, Martin Saalmann
... If it wasn't for C, we would be using BASI, PASAL and OBOL!|||But the join is still there...
I have no idea what you are trying to do. You call your function
concat, which I think means concatenate, but then all you do is select
the first of the two values that isn't null, that isn't concatenation.
If you want assistance then you have to give information about what
you are actually trying to do.
Cheers,
Jason Lepack
On Aug 1, 10:01 am, Henning Eiben <ei...@.busitec.de> wrote:
> Jason Lepack wrote:
> > I'm going to step out a limb here...
> > Does this not return the same result?
> > SELECT DISTINCT
> > A.a, dbo.sp_concat(A.b, A.c) as x
> > FROM
> > A
> > Why bother with the join if this is what you're doing?
> actually I have a view
> CREATE VIEW dbo.SomeView AS
> SELECT A.*, B.*
> FROM
> A LEFT OUTER JOIN B ON
> A.id =3D B.id
> and my SQL-Statement looks like this:
> SELECT DISTINCT
> A.a, dbo.sp_concat(A.b, A.c) as x
> FROM
> dbo.SomeView
> This way I can create the select-clause in my app, and since I'm using
> the view in the from-clause, I don't have to deal with the join ...
> --
> Henning Eiben
> busitec GmbH
> Consultant
> e-mail: ei...@.busitec.de
> +49 (251) 13335-0 Tel
> +49 (251) 13335-35 Fax
> Rudolf-Diesel-Stra=DFe 59
> 48157 M=FCnsterwww.busitec.de
> Sitz der Gesellschaft: M=FCnster
> HR B 55 75 - Amtsgericht M=FCnster
> USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
> Gesch=E4ftsf=FChrer: Simon B=F6wer, Henning Eiben, Stefan K=FChn, Martin =Saalmann
> --
> ... If it wasn't for C, we would be using BASI, PASAL and OBOL!|||Henning,
Interesting case. Add WITH SCHEMABINDING to your UDF's definition, and
all thy troubles are solved :-)
Maybe in some weird twisted way, the optimizer thinks it cannot rule out
the use of table B if it is unknown whether the UDF accesses B.
Gert-Jan
Henning Eiben wrote:
> Hi,
> I have a DB-based application, which has a UDF like this:
> CREATE FUNCTION fn_concat(@.A varchar(255), @.B varchar(255))
> RETURNS varchar(255)
> AS BEGIN
> RETURN coalesce(@.A,@.B)
> END
> Using SQL-Server 2000 I execute the following statement:
> SELECT DISTINCT
> A.a, dbo.sp_concat(A.b, A.c) as x
> FROM
> A LEFT OUTER JOIN B ON
> A.id = B.id
> Since the statement is only selecting columns from table "A", it is
> being optimized so that the join with table "B" is not being executed.
> Because table "B" is quite large, this saves quite some execution-time.
> When this statement is being executed on a SQL-Server 2005 the join is
> being executed, resulting in a much longer execution-time. This seems to
> be because of the UDF, because if this is being left out, the optimizer
> eliminates the processing of table "B".
> Background: I have a view, which consists of a lot of joins of several
> tables, and I dynamically build the select-clause of the statement in my
> application. Because the optimizer only processes the tables that are
> actually being used in the select-statement this is an easy way to not
> deal with the joins in the application itself.
> But why is the use of the function changing the behavior of the 2005
> optimizer?
> --
> Henning Eiben
> busitec GmbH
> Consultant
> e-mail: eiben@.busitec.de
> +49 (251) 13335-0 Tel
> +49 (251) 13335-35 Fax
> Rudolf-Diesel-Straße 59
> 48157 Münster
> www.busitec.de
> Sitz der Gesellschaft: Münster
> HR B 55 75 - Amtsgericht Münster
> USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
> Geschäftsführer: Simon Böwer, Henning Eiben, Stefan Kühn, Martin Saalmann
> --
> ... There are 10 kinds of people. Those who know binary code, and those
> who don't.|||Gert-Jan Strik wrote:
> Henning,
> Interesting case. Add WITH SCHEMABINDING to your UDF's definition, and
> all thy troubles are solved :-)
Wow! That did it!
> Maybe in some weird twisted way, the optimizer thinks it cannot rule out
> the use of table B if it is unknown whether the UDF accesses B.
Seems that SQL2005 is more cautious than SQL2000 :)
--
Henning Eiben
busitec GmbH
Consultant
e-mail: eiben@.busitec.de
+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax
Rudolf-Diesel-Straße 59
48157 Münster
www.busitec.de
Sitz der Gesellschaft: Münster
HR B 55 75 - Amtsgericht Münster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschäftsführer: Simon Böwer, Henning Eiben, Stefan Kühn, Martin Saalmann
... If it wasn't for C, we would be using BASI, PASAL and OBOL!

Wednesday, March 7, 2012

Query only new records

Hello,
Can I have some advise on how to do the following.
I need to create a csv file based on a query of new records from a table. In
other words the query will run periodically and I dont want it to pick up
the records that were already queried.
ThanksSorry Wont way to put it.
I need to insert not update...
"Al" wrote:
> Hello,
> Can I have some advise on how to do the following.
> I need to create a csv file based on a query of new records from a table. In
> other words the query will run periodically and I dont want it to pick up
> the records that were already queried.
> Thanks|||Several ways you can do this, but might require some schema changes on your
part. If you have a DATETIME column, you can use that to determine which
ones are new and which aren't. You could add a CHAR(1) column to indicate
if a row has been queried already or not (you would need to change the value
after the query to indicate that it's been queried). If you have some sort
of incremental key, such as an IDENTITY column you can store the highest
value for that column at query time and the next time you query just grab
any rows with a higher value in that column.
Basically you'll have to store some sort of "state" value so your app will
know where it left off the last time it ran.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:EB5EFD0D-8C92-4EFB-B6F2-1D041113852D@.microsoft.com...
> Sorry Wont way to put it.
> I need to insert not update...
> "Al" wrote:
>> Hello,
>> Can I have some advise on how to do the following.
>> I need to create a csv file based on a query of new records from a table.
>> In
>> other words the query will run periodically and I dont want it to pick
>> up
>> the records that were already queried.
>> Thanks|||Use some timestamp column..
Jayesh
"Al" <Al@.discussions.microsoft.com> wrote in message
news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
> Hello,
> Can I have some advise on how to do the following.
> I need to create a csv file based on a query of new records from a table.
> In
> other words the query will run periodically and I dont want it to pick up
> the records that were already queried.
> Thanks|||You still need to store the highest timestamp used for the last batch
somewhere...
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:%239FsEdDjGHA.4304@.TK2MSFTNGP03.phx.gbl...
> Use some timestamp column..
> Jayesh
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
>> Hello,
>> Can I have some advise on how to do the following.
>> I need to create a csv file based on a query of new records from a table.
>> In
>> other words the query will run periodically and I dont want it to pick
>> up
>> the records that were already queried.
>> Thanks
>

Query only new records

Hello,
Can I have some advise on how to do the following.
I need to create a csv file based on a query of new records from a table. In
other words the query will run periodically and I dont want it to pick up
the records that were already queried.
ThanksSorry Wont way to put it.
I need to insert not update...
"Al" wrote:

> Hello,
> Can I have some advise on how to do the following.
> I need to create a csv file based on a query of new records from a table.
In
> other words the query will run periodically and I dont want it to pick up
> the records that were already queried.
> Thanks|||Several ways you can do this, but might require some schema changes on your
part. If you have a DATETIME column, you can use that to determine which
ones are new and which aren't. You could add a CHAR(1) column to indicate
if a row has been queried already or not (you would need to change the value
after the query to indicate that it's been queried). If you have some sort
of incremental key, such as an IDENTITY column you can store the highest
value for that column at query time and the next time you query just grab
any rows with a higher value in that column.
Basically you'll have to store some sort of "state" value so your app will
know where it left off the last time it ran.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:EB5EFD0D-8C92-4EFB-B6F2-1D041113852D@.microsoft.com...[vbcol=seagreen]
> Sorry Wont way to put it.
> I need to insert not update...
> "Al" wrote:
>|||Use some timestamp column..
Jayesh
"Al" <Al@.discussions.microsoft.com> wrote in message
news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
> Hello,
> Can I have some advise on how to do the following.
> I need to create a csv file based on a query of new records from a table.
> In
> other words the query will run periodically and I dont want it to pick up
> the records that were already queried.
> Thanks|||You still need to store the highest timestamp used for the last batch
somewhere...
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:%239FsEdDjGHA.4304@.TK2MSFTNGP03.phx.gbl...
> Use some timestamp column..
> Jayesh
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
>|||You still need to store the highest timestamp used for the last batch
somewhere...
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:%239FsEdDjGHA.4304@.TK2MSFTNGP03.phx.gbl...
> Use some timestamp column..
> Jayesh
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
>

Query only Dimension Data

We Have a case where we have to create a report only with the dimension data from SSAS.
Is there any way in doing it in Excel 2007.

I see a pivot table option which says " Display item labels when no fields are in the value area"
but this is disabled. Can anyone point me to a way of doin this or enabling this.

Thankyou
VidyaAre you creating the report through excel or programmatically? If programmatically $Dimension exposes the dimension as a cube and you can use it as you would any cube...

query on procedure

Hi,

Please see the below procedure.

create procedure a1
as
begin

create table #table1
{
empid int;
empname varchar
}
insert into #table1 select empid,empname from employee where
empcode='50'

select e.* from employee e, #table1 as t1 where e.empid=t1.empid and
e.empname=t1.empname; /* query1 */

end

In location query1,empid , empname in #table1 substitutes all the
values at the time, i need to substitute each value individually in
location query1.

Is there any way to do this?meendar wrote:

Quote:

Originally Posted by

create procedure a1

I trust your production code will have meaningful procedure names.

Quote:

Originally Posted by

create table #table1
{
empid int;
empname varchar
}

Should be

empid int,
empname varchar(30) -- or whatever

Quote:

Originally Posted by

insert into #table1 select empid,empname from employee where
empcode='50'
>
select e.* from employee e, #table1 as t1 where e.empid=t1.empid and
e.empname=t1.empname; /* query1 */

Why are you doing this, instead of simply

select * -- you should really have an explicit list of fields
from employee
where empcode = '50'

Does the 'employee' table really have both empcode and empid? If
so, then are they both enforced as unique?

Quote:

Originally Posted by

In location query1,empid , empname in #table1 substitutes all the
values at the time, i need to substitute each value individually in
location query1.
>
Is there any way to do this?

I don't understand what you mean. Please provide an example of what
it does now, and of what you want it to do instead.

Saturday, February 25, 2012

Query Notification - checking permissions

Hi all,

I am looking at replacing a polled system with Query Notification. However when I create the SqlDependency I need to be sure I have the correct permissions. I check the SqlClientPermissions via the Demand() method, but also want to ensure I have the correct DB permission given my current connection string. As I understand it I need to have the following permissions:

CREATE PROCEDURE, QUEUE, and SERVICE permissions
SUBSCRIBE QUERY NOTIFICATIONS
SELECT on underlying tables
RECEIVE on QueryNotificationErrorsQueue

I check most of these via the 'has_perms_by_name' function, but cannot find the correct syntax to check for RECEIVE on QueryNotificationErrorsQueue. I would also love to find a way to do this via SMO instead of issuing SQl commands. Also am I missing any checks ....

Finally, I have also run into the problem whereby SQL issues the following error:

The activated proc [dbo].[SqlQueryNotificationStoredProcedure-1fd90369-7781-4bad-a1b7-e1b56e328374] running on queue ImlHostDB.dbo.SqlQueryNotificationService-1fd90369-7781-4bad-a1b7-e1b56e328374 output the following: 'Could not obtain information about Windows NT group/user 'EMEA\DyerN', error code 0x54b.'

I ran into this issue when I bought my machine out of sleep mode with it no longer connected to the network. Is their no way to get a error notification. In this situation I will just stop seeing notification and without looing at the ErrorLog believe their is nothing wrong.

Many Thanks, Nick

The SQL Server instance in question cannot communicate with the Active Directory to validate the Windows accounts (like EMEA\DyerN) and hits error 0x54b, which is ERROR_NO_SUCH_DOMAIN. You should contact your network/security administrator to diagnose and troubleshoot the problem, as is unrelated to Service Broker or Query Notifications (are you working on a laptop disconnected from domain by any chance?) Alternatively, you could use SQL users/logins instead of Windows users/logins.|||

This was true, I was disconnected from the domain. My hope however was for the query notification to fire signally that an error had occurred and change notifcations could not be delivered. This would allow me to handle it programatically and re-register with different credentials or fall back to polling for data changes.

Currently as it stands if I get disconnected from the domain my query notifications will simply stop and I will receive no notification that an error has occurred. This does not leave me with a robust solution.

Thanks, Nick

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

|||

NickUk wrote:

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

select * from sys.fn_my_permissions('dbo.QueryNotificationErrorsQueue','object')

|||

Query Notifications run 'execute as owner' so if a database is owned by a domain account, query notifications fail in the event a domain control cannot be contacted

Basically, change owner to SA to avoid the issue.

See this writeup

http://aspadvice.com/blogs/ssmith/archive/2006/11/06/SqlDependency-Issue-Resolved.aspx

Query Notification - checking permissions

Hi all,

CREATE PROCEDURE, QUEUE, and SERVICE permissions
SUBSCRIBE QUERY NOTIFICATIONS
SELECT on underlying tables
RECEIVE on QueryNotificationErrorsQueue

Finally, I have also run into the problem whereby SQL issues the following error:

Many Thanks, Nick

Thanks, Nick

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

|||

NickUk wrote:

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

select * from sys.fn_my_permissions('dbo.QueryNotificationErrorsQueue','object')

|||

Query Notifications run 'execute as owner' so if a database is owned by a domain account, query notifications fail in the event a domain control cannot be contacted

Basically, change owner to SA to avoid the issue.

See this writeup

http://aspadvice.com/blogs/ssmith/archive/2006/11/06/SqlDependency-Issue-Resolved.aspx

Monday, February 20, 2012

Query name change

sp_rename doesn't change the name inside the source code for an object. You
can try this yourself,
create a view, look in syscomments, rename it and then look again. You will
have the old object name
in the stored source code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JerryWendell" <JerryWendell@.discussions.microsoft.com> wrote in message
news:0DBB3987-AABB-4B13-A3BF-A357B09A3EB2@.microsoft.com...
>I am using SQLServer 2000 with an Access 2003 .adp front end.
> I created a view. And then I changed the name of the view.
> In another SQLServer database (in the same server) using a different .adp
> front-end, I imported the view (using File->Get External Data->Import).
> After it was imported into the second database, it had the original name.
> Any ideas on why this happened? Or how I can keep it from happening?
> Thanks!
> JerryI am using SQLServer 2000 with an Access 2003 .adp front end.
I created a view. And then I changed the name of the view.
In another SQLServer database (in the same server) using a different .adp
front-end, I imported the view (using File->Get External Data->Import).
After it was imported into the second database, it had the original name.
Any ideas on why this happened? Or how I can keep it from happening?
Thanks!
Jerry|||sp_rename doesn't change the name inside the source code for an object. You
can try this yourself,
create a view, look in syscomments, rename it and then look again. You will
have the old object name
in the stored source code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JerryWendell" <JerryWendell@.discussions.microsoft.com> wrote in message
news:0DBB3987-AABB-4B13-A3BF-A357B09A3EB2@.microsoft.com...
>I am using SQLServer 2000 with an Access 2003 .adp front end.
> I created a view. And then I changed the name of the view.
> In another SQLServer database (in the same server) using a different .adp
> front-end, I imported the view (using File->Get External Data->Import).
> After it was imported into the second database, it had the original name.
> Any ideas on why this happened? Or how I can keep it from happening?
> Thanks!
> Jerry

Query logs

Does SQL Server create a log of all the queries run against a database? Or,
alternatively, is there a way to retrieve deleted data from a table?
thnx,
Christoph
No it doesn't keep a log of queries (as in selects) but all dml is recorded
in the transaction log. If you have backups you can restore to a previous
point in time but I guess you don't in which case you pretty much need a
third party tool like Lumigent's log explorer which is able to read the
transaction log and reconstruct deleted data/truncated tables
http://www.lumigent.com/products/le_sql_faq.html
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Christoph" <jcboget@.yahoo.com> wrote in message
news:OhkjsXXKFHA.2852@.TK2MSFTNGP14.phx.gbl...
> Does SQL Server create a log of all the queries run against a database?
> Or,
> alternatively, is there a way to retrieve deleted data from a table?
> thnx,
> Christoph
>

Query Limit?

Is there a limit to the size of the query for a report? I can create simple reports but when I paste in a larger query (say about 4500 characters and we will need larger queries) and try to execute the query, the report hangs in the designer. The source is a SQL Server 2000 database and this particular query only returns about 400 rows.

Thanks in advance.

Scott Lezberg
DeltekI've worked with RS2000 and the query limit is 32KB of plain text. For if I need a bigger query I create a view (or an SP, whatever you like most) and then I call it from RS.
Since I haven't used RS2005 I couldn't tell if the query is limited to 32KB.

I hope it helps.

Friday, March 30, 2012

Query programmable

Wednesday, March 28, 2012

Query Problem

Query problem

Monday, March 26, 2012

query problem

query problem

query problem

Wednesday, March 21, 2012

Query Performance and Index

Query Performance and Index

Query Performance and Index

Tuesday, March 20, 2012

query parameter

Friday, March 9, 2012

Query Optimization

Wednesday, March 7, 2012

Query only new records

Query only new records

Query only Dimension Data

query on procedure

Saturday, February 25, 2012

Query Notification - checking permissions

Query Notification - checking permissions

Monday, February 20, 2012

Query name change

Query logs

Query Limit?

Query longer in SP

Blog Archive

About Me