Any way to optimise this query?

Hi guys

Is there any way I can run this query faster? Should I take out the ORDER BY
clause? This is supposed to return 17,000 rows and takes around 30 minutes or
so. Is there no way at all to get this result faster?

select r.AttorneyName, r.sitename, r.applicationid, r.clientsurname, r.
clientinitials, r.clientidno, r.grantedamount, r.bankname,
r.accountnumber, r.status, r.grantdate, r.consultantname, r.propertyaddress,
r.erfdescription, r.commenthistory, br.expectedregdate
from bondtrak..rptdetail r
join ebondprd..bankresponse br
on br.applicationid = r.applicationid
where
r.rundate = '20051010'
and r.primarybankind = 'Y'
and r.status = 'granted'
and r.statusdate between '20020101' and '20050930'
and r.businessunit in ('bond choice', 'ppl')
order by r.sitename, r.consultantname, r.statusdate

Thanks for any help.
Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1If you don't need the result ordered then definitely drop the ORDER BY.

Why do you want to return 17,000 rows in one hit anyway? Can't you
process the data server side?

--
David Portas
SQL Server MVP
--|||Do you have any usefull indexes on these tables?

"I sense many useless updates in you... Useless updates lead to
defragmentation... Defragmentation leads to downtime...Downtime leads
to suffering..Defragmentation is the path to the darkside.. DBCC
INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with
you" -- http://sqlservercode.blogspot.com/|||Driesen via SQLMonster.com (u11907@.uwe) writes:
> Is there any way I can run this query faster? Should I take out the
> ORDER BY clause? This is supposed to return 17,000 rows and takes around
> 30 minutes or so. Is there no way at all to get this result faster?
> select r.AttorneyName, r.sitename, r.applicationid, r.clientsurname, r.
> clientinitials, r.clientidno, r.grantedamount, r.bankname,
> r.accountnumber, r.status, r.grantdate, r.consultantname,
> r.propertyaddress,
> r.erfdescription, r.commenthistory, br.expectedregdate
> from bondtrak..rptdetail r
> join ebondprd..bankresponse br
> on br.applicationid = r.applicationid
> where
> r.rundate = '20051010'
> and r.primarybankind = 'Y'
> and r.status = 'granted'
> and r.statusdate between '20020101' and '20050930'
> and r.businessunit in ('bond choice', 'ppl')
> order by r.sitename, r.consultantname, r.statusdate

There might be. But without knowledge of the tables, indexes and how
big they are, all you can get is guesses. If you don't need data to be
sorted, you can remove ORDER BY, but it doesn't take 29 minutes to sort
17000 rows, so the effect is moderate of that operation.

A clustered index on rptdetail(rundste, statusdate) or only (rundate)
should be a good start. An index on bankreponse(applicationid) is also
necessary.

You may also have problems with statistics that are out of date.
UPDATE STATISTICS WITH FULLSCAN on both table can address this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi David

I dropped the ORDER BY and copied it Excel. This is only a once off query for
the big wigs.

Thanks for the help
Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1|||Hi Erland

Thanks for the help. I did drop the ORDER BY. The only reason this takes so
long to run is becasue our DataBase sits over 500 miles away. We to get
through a whole lot of banking environment security to access our DB.

Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1|||> We to get
> through a whole lot of banking environment security to access our DB.

So you thought you'd undermine all that security by copying the data to
an insecure Excel spreadsheet... :-)

Maybe you can use DTS to export the data to Excel. Or use BCP to create
a delimited file that can be opened in Excel.

--
David Portas
SQL Server MVP
--

Any suggestions on how to revise code to accomodate order by issue

The code below needs to do the following: SELECT the TOP 5 most recent entries from the table and UNION that data with the next table which performs the same SELECT statement.

I am not getting the correct output due to: SQL does not allow ORDER BY between a UNION so I am not pulling the most recent entries.

Any suggestion on how to solve this problem?

Thanks!

SELECT TOP 5 GlobalBanking.dbo.GB_LTFLead.BankCode AS BANKCODE, GlobalBanking.dbo.GB_LTFLead.Date AS DATE,
GlobalBanking.dbo.GB_LTFLead.code AS CODE, GlobalBanking.dbo.GB_LTFLead.Deal AS DEAL,
ISNULL('Fee: $'+GlobalBanking.dbo.GB_LTFLead.Fee, '') AS FEE,
ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_LTFLead.date,4)) AS YEAR
FROM GlobalBanking.dbo.GB_LTFLead WHERE GlobalBanking.dbo.GB_LTFLead.BankCode = 1

UNION ALL

SELECT TOP 5 GlobalBanking.dbo.GB_LTFSyndicate.BankCode AS BANKCODE, GlobalBanking.dbo.GB_LTFSyndicate.Date AS DATE,
'Syndicate' AS CODE, GlobalBanking.dbo.GB_LTFSyndicate.Deal AS DEAL,
ISNULL('Fee: $' + GlobalBanking.dbo.GB_LTFSyndicate.Fee, '') AS FEE,
ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_LTFSyndicate.Date,4)) AS 'YEAR'
FROM GlobalBanking.dbo.GB_LTFSyndicate WHERE GlobalBanking.dbo.GB_LTFSyndicate.BankCode = 1

UNION ALL

SELECT TOP 5 GlobalBanking.dbo.GB_SecLead.BankCode AS BANKCODE, GlobalBanking.dbo.GB_SecLead.Date AS DATE,
GlobalBanking.dbo.GB_SecLead.code AS CODE, GlobalBanking.dbo.GB_SecLead.Deal AS DEAL,
ISNULL('Fee: $'+GlobalBanking.dbo.GB_SecLead.Fee, '') AS FEE,
ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_SecLead.date,4)) AS YEAR
FROM GlobalBanking.dbo.GB_SecLead WHERE GlobalBanking.dbo.GB_SecLead.BankCode = 1

UNION ALL

SELECT TOP 5 GlobalBanking.dbo.GB_SecSyndicate.BankCode AS BANKCODE, GlobalBanking.dbo.GB_SecSyndicate.Date AS DATE,
'Syndicate' AS CODE, GlobalBanking.dbo.GB_SecSyndicate.Deal AS DEAL,
ISNULL('Fee: $' + GlobalBanking.dbo.GB_SecSyndicate.Fee, '') AS FEE,
ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_SecSyndicate.Date,4)) AS 'YEAR'
FROM GlobalBanking.dbo.GB_SecSyndicate WHERE GlobalBanking.dbo.GB_SecSyndicate.BankCode = 1

ORDER BY YEAR DESChave you tried this:select * from
( SELECT TOP 5 GlobalBanking.dbo.GB_LTFLead.BankCode AS BANKCODE
, GlobalBanking.dbo.GB_LTFLead.Date AS DATE
, GlobalBanking.dbo.GB_LTFLead.code AS CODE
, GlobalBanking.dbo.GB_LTFLead.Deal AS DEAL
, ISNULL('Fee: $'+GlobalBanking.dbo.GB_LTFLead.Fee, '') AS FEE
, ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_LTFLead.date,4)) AS YEAR
FROM GlobalBanking.dbo.GB_LTFLead
WHERE GlobalBanking.dbo.GB_LTFLead.BankCode = 1
order by GlobalBanking.dbo.GB_LTFLead.date desc
) as derivedtable1

UNION ALL

select * from
( SELECT ...|||Code works great! I only had to add an additional Select statement at the beginning in order to combine everything I.E.:

SELECT * FROM
(select * from
( SELECT TOP 5 GlobalBanking.dbo.GB_LTFLead.BankCode AS BANKCODE
, GlobalBanking.dbo.GB_LTFLead.Date AS DATE
, GlobalBanking.dbo.GB_LTFLead.code AS CODE
, GlobalBanking.dbo.GB_LTFLead.Deal AS DEAL
, ISNULL('Fee: $'+GlobalBanking.dbo.GB_LTFLead.Fee, '') AS FEE
, ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_LTFLead.date,4)) AS YEAR
FROM GlobalBanking.dbo.GB_LTFLead
WHERE GlobalBanking.dbo.GB_LTFLead.BankCode = 1
order by GlobalBanking.dbo.GB_LTFLead.date desc
) as derivedtable1

UNION ALL

select * from
( SELECT ...)) AS DERIVETABLE2
ORDER BY DATE DESC

Other than that it runs well. Your great!!!

any order

Hi,
Is there any sort order if we don't use order by? for example,
select * from tablename
what is sort order? each time, I get the same records with the same order,
any mystery?As far as the I know, the order was the same as you add the data.
You can change the order bu add "order by" statement , I think you can get
whole information from BOL|||Wei
I don't think that order depends on insertion. (unless you have clustered
index on this column)
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...

quote:

---

> As far as the I know, the order was the same as you add the data.
> You can change the order bu add "order by" statement , I think you can get
> whole information from BOL
>

---

|||Hi ,
If you have a clusted index in that table database will be ordered based on
the Index key. So incase if you have a clustered index the
"select * from tablename" will return the result set based on the clustered
index key order.
If you donot have a clusted index the result set will be reurned based on
the way you inserted.
Thanks
Hari
MCDBA
"John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>

---

|||records are sorted in ascending order by default.
"John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>

---

|||Sorry

quote:

---

> I don't think that order depends on insertion. (unless you have clustered
> index on this column)

---

I meant if you don't have a clustered index on the column it depends on
insertion.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#JxgijW6DHA.3548@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Wei
> I don't think that order depends on insertion. (unless you have clustered
> index on this column)
>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
get[QUOTE]
>

---

|||The first reply you made me shocked |||> If you have a clusted index in that table database will be ordered based on

quote:

---

> the Index key.

---

Not necessarily. See my other post.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uPL7gkW6DHA.2628@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi ,
> If you have a clusted index in that table database will be ordered based o
n
> the Index key. So incase if you have a clustered index the
> "select * from tablename" will return the result set based on the clustere
d
> index key order.
> If you donot have a clusted index the result set will be reurned based on
> the way you inserted.
>
> Thanks
> Hari
> MCDBA
> "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
>

---

|||> I meant if you don't have a clustered index on the column it depends on

quote:

---

> insertion.

---

Not correct. I'm afraid. See my other post.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23qCR2tW6DHA.1936@.TK2MSFTNGP12.phx.gbl...[Q
UOTE]
> Sorry
> I meant if you don't have a clustered index on the column it depends on
> insertion.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#JxgijW6DHA.3548@.TK2MSFTNGP11.phx.gbl...
> get
>[/QUOTE]|||A table, is in the relation model, an unordered set of rows. Imagine your ro
ws being notes on
paper-slips and you throw them into a bag. The back is shaken from time to t
ime. Now. try to pull
the paper-slips out of this bag "in order". The term "order" doesn't make se
nse in the relational
model.
To be more specific, if you don't have order by, the optimizer is free to re
turn the rows in any
order. It will pick the order it finds most cost-effective.
It doesn't have to be in the same sequence as the rows are inserted (read ab
out IAM page etc in the
physical database chapter in Books Online). That is regardless whether you h
ave a clustered index or
not.
It doesn't have to be in the same sequence as the clustered index. The index
can be heavy
fragmented, so the optimizer can decide to instead of jumping back and forth
on the disk, it will
scan the file in physical order, according to the IAM page.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"John" <spam@.spam.com> wrote in message news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

---

[color
=darkred]
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>[/color]

---

any order

Hi,
Is there any sort order if we don't use order by? for example,
select * from tablename
what is sort order? each time, I get the same records with the same order,
any mystery?As far as the I know, the order was the same as you add the data.
You can change the order bu add "order by" statement , I think you can get
whole information from BOL|||Wei
I don't think that order depends on insertion. (unless you have clustered
index on this column)
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> As far as the I know, the order was the same as you add the data.
> You can change the order bu add "order by" statement , I think you can get
> whole information from BOL
>|||Hi ,
If you have a clusted index in that table database will be ordered based on
the Index key. So incase if you have a clustered index the
"select * from tablename" will return the result set based on the clustered
index key order.
If you donot have a clusted index the result set will be reurned based on
the way you inserted.
Thanks
Hari
MCDBA
"John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>|||records are sorted in ascending order by default.
"John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>|||Sorry
> I don't think that order depends on insertion. (unless you have clustered
> index on this column)
I meant if you don't have a clustered index on the column it depends on
insertion.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#JxgijW6DHA.3548@.TK2MSFTNGP11.phx.gbl...
> Wei
> I don't think that order depends on insertion. (unless you have clustered
> index on this column)
>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > As far as the I know, the order was the same as you add the data.
> > You can change the order bu add "order by" statement , I think you can
get
> > whole information from BOL
> >
> >
>|||The first reply you made me shocked :)|||Incorrect, I'm afraid. See my other post.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"harsh" <harshalmistry@.hotmail.com> wrote in message
news:%23RaX%23pW6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> records are sorted in ascending order by default.
>
> "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same order,
> > any mystery?
> >
> >
>|||> If you have a clusted index in that table database will be ordered based on
> the Index key.
Not necessarily. See my other post.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uPL7gkW6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> If you have a clusted index in that table database will be ordered based on
> the Index key. So incase if you have a clustered index the
> "select * from tablename" will return the result set based on the clustered
> index key order.
> If you donot have a clusted index the result set will be reurned based on
> the way you inserted.
>
> Thanks
> Hari
> MCDBA
> "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same order,
> > any mystery?
> >
> >
>|||> I meant if you don't have a clustered index on the column it depends on
> insertion.
Not correct. I'm afraid. See my other post.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23qCR2tW6DHA.1936@.TK2MSFTNGP12.phx.gbl...
> Sorry
> > I don't think that order depends on insertion. (unless you have clustered
> > index on this column)
> I meant if you don't have a clustered index on the column it depends on
> insertion.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#JxgijW6DHA.3548@.TK2MSFTNGP11.phx.gbl...
> > Wei
> > I don't think that order depends on insertion. (unless you have clustered
> > index on this column)
> >
> >
> >
> > "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> > news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > > As far as the I know, the order was the same as you add the data.
> > > You can change the order bu add "order by" statement , I think you can
> get
> > > whole information from BOL
> > >
> > >
> >
> >
>|||A table, is in the relation model, an unordered set of rows. Imagine your rows being notes on
paper-slips and you throw them into a bag. The back is shaken from time to time. Now. try to pull
the paper-slips out of this bag "in order". The term "order" doesn't make sense in the relational
model.
To be more specific, if you don't have order by, the optimizer is free to return the rows in any
order. It will pick the order it finds most cost-effective.
It doesn't have to be in the same sequence as the rows are inserted (read about IAM page etc in the
physical database chapter in Books Online). That is regardless whether you have a clustered index or
not.
It doesn't have to be in the same sequence as the clustered index. The index can be heavy
fragmented, so the optimizer can decide to instead of jumping back and forth on the disk, it will
scan the file in physical order, according to the IAM page.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John" <spam@.spam.com> wrote in message news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>|||Tibor
I have just finished to read the article, you are absolutely right. Sorry
for giniven incorrect information.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OS1xRJX6DHA.2712@.tk2msftngp13.phx.gbl...
> A table, is in the relation model, an unordered set of rows. Imagine your
rows being notes on
> paper-slips and you throw them into a bag. The back is shaken from time to
time. Now. try to pull
> the paper-slips out of this bag "in order". The term "order" doesn't make
sense in the relational
> model.
> To be more specific, if you don't have order by, the optimizer is free to
return the rows in any
> order. It will pick the order it finds most cost-effective.
> It doesn't have to be in the same sequence as the rows are inserted (read
about IAM page etc in the
> physical database chapter in Books Online). That is regardless whether you
have a clustered index or
> not.
> It doesn't have to be in the same sequence as the clustered index. The
index can be heavy
> fragmented, so the optimizer can decide to instead of jumping back and
forth on the disk, it will
> scan the file in physical order, according to the IAM page.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same
order,
> > any mystery?
> >
> >
>|||Tibor.
Greate Poster, let me learn many thing.
I do not think you are right after you poster, but after read the
information on BOL. Yes you are right.|||>The term "order" doesn't make sense in the relational
> model.
I understand that the order and PHYSICAL placement of the data is not a
concern in the relational model but surely the RM addresses returning
ordered data? Isn't this called sort sets in the RM?
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OS1xRJX6DHA.2712@.tk2msftngp13.phx.gbl...
> A table, is in the relation model, an unordered set of rows. Imagine your
rows being notes on
> paper-slips and you throw them into a bag. The back is shaken from time to
time. Now. try to pull
> the paper-slips out of this bag "in order". The term "order" doesn't make
sense in the relational
> model.
> To be more specific, if you don't have order by, the optimizer is free to
return the rows in any
> order. It will pick the order it finds most cost-effective.
> It doesn't have to be in the same sequence as the rows are inserted (read
about IAM page etc in the
> physical database chapter in Books Online). That is regardless whether you
have a clustered index or
> not.
> It doesn't have to be in the same sequence as the clustered index. The
index can be heavy
> fragmented, so the optimizer can decide to instead of jumping back and
forth on the disk, it will
> scan the file in physical order, according to the IAM page.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same
order,
> > any mystery?
> >
> >
>|||As far as I know, RM does not have this concept, as this would mean that RM defines other concepts
than relations. I might be wrong, of course and I welcome (as always :-) ) pointers etc...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:eNG0m9X6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> >The term "order" doesn't make sense in the relational
> > model.
> I understand that the order and PHYSICAL placement of the data is not a
> concern in the relational model but surely the RM addresses returning
> ordered data? Isn't this called sort sets in the RM?
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OS1xRJX6DHA.2712@.tk2msftngp13.phx.gbl...
> > A table, is in the relation model, an unordered set of rows. Imagine your
> rows being notes on
> > paper-slips and you throw them into a bag. The back is shaken from time to
> time. Now. try to pull
> > the paper-slips out of this bag "in order". The term "order" doesn't make
> sense in the relational
> > model.
> >
> > To be more specific, if you don't have order by, the optimizer is free to
> return the rows in any
> > order. It will pick the order it finds most cost-effective.
> >
> > It doesn't have to be in the same sequence as the rows are inserted (read
> about IAM page etc in the
> > physical database chapter in Books Online). That is regardless whether you
> have a clustered index or
> > not.
> >
> > It doesn't have to be in the same sequence as the clustered index. The
> index can be heavy
> > fragmented, so the optimizer can decide to instead of jumping back and
> forth on the disk, it will
> > scan the file in physical order, according to the IAM page.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > >
> > > Is there any sort order if we don't use order by? for example,
> > >
> > > select * from tablename
> > >
> > > what is sort order? each time, I get the same records with the same
> order,
> > > any mystery?
> > >
> > >
> >
> >
>

Anti-virus Exclusion list

Currently, I install an Anti-virus solution on MS SQL Server. It protects windows OS. In order to speed up the SQL performance, I dont want this Anti-virus to scan my SQL database. I want to know what's the exclusion list for the Anti-virus application. Thanks,

Hi,

all file extension that are used for SQL Server, by default this is mdf,ndf,ldf but this can be changed by the user, so you will need to have a look at your database. The logfiles .log can be dismissed, as there is no heavy load on them.

The information about the datafiles is stored in the sysfiles table, therefore you will have to query this table for each database in order to get the extensions. I prepared something for you which should help you to identity the needed extensions.

CREATE Table #Extensions

(

Extension VARCHAR(10)

)

INSERT INTO #Extensions

EXEC sp_msforeachdb 'Select REVERSE(LEFT(REVERSE(filename),CHARINDEX(CHAR(46),REVERSE(filename))-1)) from sysfiles'

SELECT DISTINCT Extension FROM #Extensions

If you data is stored in specific folders, you could also try to exclude this folder instead of using the file extension filter (if you application is able to do this)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

All anti virus server have Exclusion List which means it won't scan those files(Extension) listed , you have to enter .mdf,.ldf & .ndf to its Extention Exclusion List and then it won't scan your Database. http://www.nus.edu.sg/comcen/antivirus/faq.htm#10 FYI & E.G.

Hemantgiri S. Goswami