Query plans

I am a bit confused after reading a SQL Server Magazine
article about query plans
If i have a query
DECLARE @.odate AS DATETIME
SET @.odate = '19980506'
SELECT * FROM Orders
WHERE OrderDate >= @.odate
GO
Will above query planbe different than
SELECT * FROM Orders
WHERE OrderDate >= '19980506'
If yes why ?
Bot abobe queries will be compliled and execution plan
will be prepared and then executed
Thanks
Sanjaythere may or may not be a different plan,
there may be slightly difference statistical estimates of
the row count involved
in the first, the optimizer will estimate the number of
rows involved for any generic value of OrderDate.
in the second, it will try to get a better estimate based
on the Orderdate value or '19980506'
>--Original Message--
>I am a bit confused after reading a SQL Server Magazine
>article about query plans
>If i have a query
> DECLARE @.odate AS DATETIME
> SET @.odate = '19980506'
> SELECT * FROM Orders
> WHERE OrderDate >= @.odate
> GO
>Will above query planbe different than
> SELECT * FROM Orders
> WHERE OrderDate >= '19980506'
>If yes why ?
>Bot abobe queries will be compliled and execution plan
>will be prepared and then executed
>Thanks
>Sanjay
>
>.
>|||Sanjay
For a variable in a open-ended range (i.e. < or >) the optimizer will
estimate that 30% of the rows in the table will meet the cristeria. That is
far to many to use an index seek with a nonclustered index, but a clustered
index could be considered.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:311501c373f8$ebe59080$a501280a@.phx.gbl...
> What is that estimate in first case, is it fixed like 10%
> 20% etc, is it selective enough to do a index seek
> Sanjay
> >--Original Message--
> >there may or may not be a different plan,
> >there may be slightly difference statistical estimates of
> >the row count involved
> >in the first, the optimizer will estimate the number of
> >rows involved for any generic value of OrderDate.
> >in the second, it will try to get a better estimate based
> >on the Orderdate value or '19980506'
> >
> >
> >>--Original Message--
> >>I am a bit confused after reading a SQL Server Magazine
> >>article about query plans
> >>
> >>If i have a query
> >> DECLARE @.odate AS DATETIME
> >> SET @.odate = '19980506'
> >> SELECT * FROM Orders
> >> WHERE OrderDate >= @.odate
> >> GO
> >>
> >>Will above query planbe different than
> >> SELECT * FROM Orders
> >> WHERE OrderDate >= '19980506'
> >>
> >>If yes why ?
> >>Bot abobe queries will be compliled and execution plan
> >>will be prepared and then executed
> >>
> >>Thanks
> >>Sanjay
> >>
> >>
> >>
> >>.
> >>
> >.
> >

Query output format question

Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
--
353
Date
--
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SBI have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>|||Oh My Keith,
I feel like a fool. I should have known that, it was so easy.
Thank you so very much,
Sara B
"Keith Kratochvil" wrote:

> I have not read the article yet, so I don't know what the intended purpose
> of the Milliseconds and Date results are...
> However, have you tried making the three select statements one statement?
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess,
> datepart(ms,getdate()) AS Milliseconds,
> getdate() AS Date'
> --
> Keith
>
> "Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
> news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
>

Query output format question

Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
--
353
Date
--
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SBI have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
--
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>

Query output format question

Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
353
Date
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SB
I have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>
|||Oh My Keith,
I feel like a fool. I should have known that, it was so easy.
Thank you so very much,
Sara B
"Keith Kratochvil" wrote:

> I have not read the article yet, so I don't know what the intended purpose
> of the Milliseconds and Date results are...
> However, have you tried making the three select statements one statement?
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess,
> datepart(ms,getdate()) AS Milliseconds,
> getdate() AS Date'
> --
> Keith
>
> "Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
> news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
>