Hi,
i have a model column and has all sort of names.
E.g.
Model names
CRUZERCROSSFIRE00512
EXTREME512MBCF
EXTREMECF00256
EXTREMEIIISECUREDIGITAL
ULTRAIIMAGICGATEMP00512
ULTRAIIMAGICGATEMP01024
X00512
I have a new column and this new column is to differentiate the model
belongs to High or Std. Any model name that starts with Extreme or Ultra will
be High and others will be Std
e.g.
Model New Column
CRUZERCROSSFIRE00512 Std
EXTREME512MBCF High
EXTREMECF00256 High
ULTRAIIMAGICGATEMP01024 High
ULTRAIIMAGICGATEMP00512 High
X00512 Std
I am not sure how to write a query to populate the data in the new column.
Kindly advise.
Thank you
Hi 'Tiffany',
It's considered a bad idea to take over another person's thread. You should start you own. Folks may not look it your problem since they will consider it a continuation of the previous thread and you may not get help.
You could try something like:
SELECT
Model,
NewColumn = CASE
WHEN Model LIKE 'EXTREME%' THEN 'High'
WHEN Model LIKE 'ULTRA%' THEN 'High'
ELSE 'Std'
END
FROM MyTable
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message news:4DF539CC-CD49-49EE-9E6B-B7084EF0B602@.microsoft.com...
> Hi,
> i have a model column and has all sort of names.
> E.g.
> Model names
> CRUZERCROSSFIRE00512
> EXTREME512MBCF
> EXTREMECF00256
> EXTREMEIIISECUREDIGITAL
> ULTRAIIMAGICGATEMP00512
> ULTRAIIMAGICGATEMP01024
> X00512
> I have a new column and this new column is to differentiate the model
> belongs to High or Std. Any model name that starts with Extreme or Ultra will
> be High and others will be Std
> e.g.
> Model New Column
> CRUZERCROSSFIRE00512 Std
> EXTREME512MBCF High
> EXTREMECF00256 High
> ULTRAIIMAGICGATEMP01024 High
> ULTRAIIMAGICGATEMP00512 High
> X00512 Std
> I am not sure how to write a query to populate the data in the new column.
> Kindly advise.
> Thank you
>
>
|||Hugo,
I wasn't trying to give Tiffany grief, just concerned that her posting would
be 'lost' and not garner any attention.
And yes, I 'forgot' that OE can combine different threads that just happen
to have the same subject.
About your request that I alter my sending settings, I have no reason not to
do what you ask, but I just can't find any settings similar to 'Settings for
Unformatted text". Also can't find any information on "Line Breaks", or
"unformatted text" in OE help. Any other ideas?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:opgnk2dp2h7t1sgovd8skqfnd3iefd14q6@.4ax.com...
> On Sun, 29 Oct 2006 19:09:36 -0800, Arnie Rowland wrote:
>
> Hi Arnie,
> Just as an FYI, Tiffany didn't take over any thread. She posted a new
> thread, with a subject that happens to be quite popular. So your news
> reading software (like mine) uses a title matching algorithm to show you
> a threaded structure that never existed.
> This technique is great to keep thread structure intact if some posts
> don't contain accurate "references" headers, but it bites you in the leg
> with unrelated postings that use a common subject.
> Oh, and while we're on the subject of news readers, could you please set
> up your news client to include line breaks, as still is standard on
> usenet? I see that you're using Outlook Express; you'll find the option
> to change this throug "Extra" / "Options" / "Send" / (Layout for sending
> news) "Settings for unformatted text"
> --
> Hugo Kornelis, SQL Server MVP
Showing posts with label names. Show all posts
Showing posts with label names. Show all posts
Monday, March 26, 2012
query
Hi
I have a following problem;
Lets say i have table with names and value in this table "hkan"
Now user enters text "hakan" for search and query must return alsow
"hkan".
Is in SQL any "special" function for this
?
one option is create own function, but there is toooo many different options
a-, o- aso aso.
Regards;
Meelis
Meelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI =
@.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "hkan"
> Now user enters text "hakan" for search and query must return alsow
> "hkan".
> Is in SQL any "special" function for this?
> one option is create own function, but there is toooo many different
> options a-, o- aso aso.
>
> Regards;
> Meelis
>
I have a following problem;
Lets say i have table with names and value in this table "hkan"
Now user enters text "hakan" for search and query must return alsow
"hkan".
Is in SQL any "special" function for this
?one option is create own function, but there is toooo many different options
a-, o- aso aso.
Regards;
Meelis
Meelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI =
@.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "hkan"
> Now user enters text "hakan" for search and query must return alsow
> "hkan".
> Is in SQL any "special" function for this
?> one option is create own function, but there is toooo many different
> options a-, o- aso aso.
>
> Regards;
> Meelis
>
Friday, March 23, 2012
query
Hi
I have a following problem;
Lets say i have table with names and value in this table "håkan"
Now user enters text "hakan" for search and query must return alsow
"håkan".
Is in SQL any "special" function for this :)?
one option is create own function, but there is toooo many different options
a-ä, o-ö aso aso.
Regards;
MeelisMeelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI = @.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "håkan"
> Now user enters text "hakan" for search and query must return alsow
> "håkan".
> Is in SQL any "special" function for this :)?
> one option is create own function, but there is toooo many different
> options a-ä, o-ö aso aso.
>
> Regards;
> Meelis
>
I have a following problem;
Lets say i have table with names and value in this table "håkan"
Now user enters text "hakan" for search and query must return alsow
"håkan".
Is in SQL any "special" function for this :)?
one option is create own function, but there is toooo many different options
a-ä, o-ö aso aso.
Regards;
MeelisMeelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI = @.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "håkan"
> Now user enters text "hakan" for search and query must return alsow
> "håkan".
> Is in SQL any "special" function for this :)?
> one option is create own function, but there is toooo many different
> options a-ä, o-ö aso aso.
>
> Regards;
> Meelis
>
query
Hi
I have a following problem;
Lets say i have table with names and value in this table "hkan"
Now user enters text "hakan" for search and query must return alsow
"hkan".
Is in SQL any "special" function for this?
one option is create own function, but there is toooo many different options
a-, o- aso aso.
Regards;
MeelisMeelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI =
@.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "hkan"
> Now user enters text "hakan" for search and query must return alsow
> "hkan".
> Is in SQL any "special" function for this?
> one option is create own function, but there is toooo many different
> options a-, o- aso aso.
>
> Regards;
> Meelis
>
I have a following problem;
Lets say i have table with names and value in this table "hkan"
Now user enters text "hakan" for search and query must return alsow
"hkan".
Is in SQL any "special" function for this
?one option is create own function, but there is toooo many different options
a-, o- aso aso.
Regards;
MeelisMeelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI =
@.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "hkan"
> Now user enters text "hakan" for search and query must return alsow
> "hkan".
> Is in SQL any "special" function for this
?> one option is create own function, but there is toooo many different
> options a-, o- aso aso.
>
> Regards;
> Meelis
>
Tuesday, March 20, 2012
Queries between databases
We have some diffrent databases on our servers and we sometimes want to
make queries between them. The problem is that we have diffrent names
for the databases. In the dev enviroment the databases is called
database1_dev and in stage it's called database2_stage and so on.
What is the best way to create some sort of alias or something so that
i can add a query to dev that i don't have to change when i move it to
stage.
For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
As it is now i must have SELECT * FROM database1_dev.table1 JOIN
database2_dev.table1 ..
And i have to change it on deployment. There must be some good solution
for this right?What is the version are you using?
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||if you deploy your database using SQL Scripts, you can use the sqlcmd
commandline tool to execute these scripts.
using this tool you can use variables like:
$(db1) and $(db2)
finally the query used to access the 2 databases will be:
SELECT * FROM $(db1).table1 JOIN $(db2).table1 ON ..
when you execute the script and you change the db1 & 2 variables values,
your query will use the correct names.
but this works fine only using the sqlcmd tool and only after you modify the
SQL script to replace database1 by $(db1).
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||Thanks for all the replies .. im sure i will go with one of the
methods.
We are using SQL Server 2005 (Enterprise i think) ..
Br, Ola
Uri Dimant wrote:
> What is the version are you using?
>
> <stuckish@.gmail.com> wrote in message
> news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> > We have some diffrent databases on our servers and we sometimes want to
> > make queries between them. The problem is that we have diffrent names
> > for the databases. In the dev enviroment the databases is called
> > database1_dev and in stage it's called database2_stage and so on.
> >
> > What is the best way to create some sort of alias or something so that
> > i can add a query to dev that i don't have to change when i move it to
> > stage.
> >
> > For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> >
> > As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> > database2_dev.table1 ..
> >
> > And i have to change it on deployment. There must be some good solution
> > for this right?
> >|||Take look at SYNONYM command as well
<stuckish@.gmail.com> wrote in message
news:1167829345.028355.29210@.i12g2000cwa.googlegroups.com...
> Thanks for all the replies .. im sure i will go with one of the
> methods.
> We are using SQL Server 2005 (Enterprise i think) ..
> Br, Ola
> Uri Dimant wrote:
>> What is the version are you using?
>>
>> <stuckish@.gmail.com> wrote in message
>> news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
>> > We have some diffrent databases on our servers and we sometimes want to
>> > make queries between them. The problem is that we have diffrent names
>> > for the databases. In the dev enviroment the databases is called
>> > database1_dev and in stage it's called database2_stage and so on.
>> >
>> > What is the best way to create some sort of alias or something so that
>> > i can add a query to dev that i don't have to change when i move it to
>> > stage.
>> >
>> > For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
>> >
>> > As it is now i must have SELECT * FROM database1_dev.table1 JOIN
>> > database2_dev.table1 ..
>> >
>> > And i have to change it on deployment. There must be some good solution
>> > for this right?
>> >
>
make queries between them. The problem is that we have diffrent names
for the databases. In the dev enviroment the databases is called
database1_dev and in stage it's called database2_stage and so on.
What is the best way to create some sort of alias or something so that
i can add a query to dev that i don't have to change when i move it to
stage.
For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
As it is now i must have SELECT * FROM database1_dev.table1 JOIN
database2_dev.table1 ..
And i have to change it on deployment. There must be some good solution
for this right?What is the version are you using?
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||if you deploy your database using SQL Scripts, you can use the sqlcmd
commandline tool to execute these scripts.
using this tool you can use variables like:
$(db1) and $(db2)
finally the query used to access the 2 databases will be:
SELECT * FROM $(db1).table1 JOIN $(db2).table1 ON ..
when you execute the script and you change the db1 & 2 variables values,
your query will use the correct names.
but this works fine only using the sqlcmd tool and only after you modify the
SQL script to replace database1 by $(db1).
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||Thanks for all the replies .. im sure i will go with one of the
methods.
We are using SQL Server 2005 (Enterprise i think) ..
Br, Ola
Uri Dimant wrote:
> What is the version are you using?
>
> <stuckish@.gmail.com> wrote in message
> news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> > We have some diffrent databases on our servers and we sometimes want to
> > make queries between them. The problem is that we have diffrent names
> > for the databases. In the dev enviroment the databases is called
> > database1_dev and in stage it's called database2_stage and so on.
> >
> > What is the best way to create some sort of alias or something so that
> > i can add a query to dev that i don't have to change when i move it to
> > stage.
> >
> > For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> >
> > As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> > database2_dev.table1 ..
> >
> > And i have to change it on deployment. There must be some good solution
> > for this right?
> >|||Take look at SYNONYM command as well
<stuckish@.gmail.com> wrote in message
news:1167829345.028355.29210@.i12g2000cwa.googlegroups.com...
> Thanks for all the replies .. im sure i will go with one of the
> methods.
> We are using SQL Server 2005 (Enterprise i think) ..
> Br, Ola
> Uri Dimant wrote:
>> What is the version are you using?
>>
>> <stuckish@.gmail.com> wrote in message
>> news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
>> > We have some diffrent databases on our servers and we sometimes want to
>> > make queries between them. The problem is that we have diffrent names
>> > for the databases. In the dev enviroment the databases is called
>> > database1_dev and in stage it's called database2_stage and so on.
>> >
>> > What is the best way to create some sort of alias or something so that
>> > i can add a query to dev that i don't have to change when i move it to
>> > stage.
>> >
>> > For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
>> >
>> > As it is now i must have SELECT * FROM database1_dev.table1 JOIN
>> > database2_dev.table1 ..
>> >
>> > And i have to change it on deployment. There must be some good solution
>> > for this right?
>> >
>
Monday, March 12, 2012
Qualifing table names with dbo
if dbo exists then the sql server resolves the object immeidately
otherwise it needs to search the databases one by one in the server.
Basically its a performance issue related.
HTH
Rajesh Peddireddy.
"Jim Abel" wrote:
> I see different examples of query statements some with the table names
> preceeded by dbo.tablename and others with only the table name. Does the
dbo
> do anything at all, especially if the user that request the query to execu
te
> is not the dbo simply a user with read only permissions?
>"Rajesh" <Rajesh@.discussions.microsoft.com> wrote in message
news:3931CB08-2AE8-4D0C-AC07-E5037BB45D92@.microsoft.com...
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
No it's not. SQL Server will never have to resolve the object name outside
of the database, and when it does this happens on at query comiliation time.
Once the query is compiled it should be reused, so this is not a performance
issue.
Prefixing with DBO is unnecesary and should be avoided in the case where all
the objects in a database reside in the DBO schema. In addition prefixing
is necessary to create schema-bound views and functions.
David|||David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> Prefixing with DBO is unnecesary and should be avoided in the case where
> all the objects in a database reside in the DBO schema. In addition
> prefixing is necessary to create schema-bound views and functions.
I have had some discussions with the SQL Server team on that one...
In SQL 2000, if a plain user issues a SELECT statement or calls a
stored procedure with qualifying the name with dbo, there is a cost.
Say that user fred issues:
SELECT ... FROM tbl
EXEC some_sp
SQL Server will first have to check whether there is a fred.tbl or a
fred.some_sp. There is a cost for this.
In SQL 2005, this may be different. This is because in SQL 2005 owner
and schema are different. In SQL 2000 fred's default schema is "fred"
by necessity. In SQL 2005, fred may have dbo as his default schema, and
in this case, it should not matter whether you say tbl or dbo.tbl.
However, this depends on how the user fred was created. If the database
was carried over from SQL 2000, or ir the user was created with
sp_adduser out of habit, it will be as on SQL 2000.
It's a different issue inside of a stored procedure, and this is where I
don't agree with some of the SQL Server folks. In a stored procedure
owned by dbo, "SELECT ... FROM tbl" is unambiguous, and adding "dbo."
is just noise for the human reader. However, the SQL Server team
claims there still is a cost in this case. I tempted to say that in
such case this is a bug, but I have not looked at the actual code.
In any case, Microsoft's recommendation is that you should always
specify dbo. also inside stored procedures.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
otherwise it needs to search the databases one by one in the server.
Basically its a performance issue related.
HTH
Rajesh Peddireddy.
"Jim Abel" wrote:
> I see different examples of query statements some with the table names
> preceeded by dbo.tablename and others with only the table name. Does the
dbo
> do anything at all, especially if the user that request the query to execu
te
> is not the dbo simply a user with read only permissions?
>"Rajesh" <Rajesh@.discussions.microsoft.com> wrote in message
news:3931CB08-2AE8-4D0C-AC07-E5037BB45D92@.microsoft.com...
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
No it's not. SQL Server will never have to resolve the object name outside
of the database, and when it does this happens on at query comiliation time.
Once the query is compiled it should be reused, so this is not a performance
issue.
Prefixing with DBO is unnecesary and should be avoided in the case where all
the objects in a database reside in the DBO schema. In addition prefixing
is necessary to create schema-bound views and functions.
David|||David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> Prefixing with DBO is unnecesary and should be avoided in the case where
> all the objects in a database reside in the DBO schema. In addition
> prefixing is necessary to create schema-bound views and functions.
I have had some discussions with the SQL Server team on that one...
In SQL 2000, if a plain user issues a SELECT statement or calls a
stored procedure with qualifying the name with dbo, there is a cost.
Say that user fred issues:
SELECT ... FROM tbl
EXEC some_sp
SQL Server will first have to check whether there is a fred.tbl or a
fred.some_sp. There is a cost for this.
In SQL 2005, this may be different. This is because in SQL 2005 owner
and schema are different. In SQL 2000 fred's default schema is "fred"
by necessity. In SQL 2005, fred may have dbo as his default schema, and
in this case, it should not matter whether you say tbl or dbo.tbl.
However, this depends on how the user fred was created. If the database
was carried over from SQL 2000, or ir the user was created with
sp_adduser out of habit, it will be as on SQL 2000.
It's a different issue inside of a stored procedure, and this is where I
don't agree with some of the SQL Server folks. In a stored procedure
owned by dbo, "SELECT ... FROM tbl" is unambiguous, and adding "dbo."
is just noise for the human reader. However, the SQL Server team
claims there still is a cost in this case. I tempted to say that in
such case this is a bug, but I have not looked at the actual code.
In any case, Microsoft's recommendation is that you should always
specify dbo. also inside stored procedures.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Qualifing table names with dbo
I see different examples of query statements some with the table names
preceeded by dbo.tablename and others with only the table name. Does the db
o
do anything at all, especially if the user that request the query to execute
is not the dbo simply a user with read only permissions?Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> I see different examples of query statements some with the table names
> preceeded by dbo.tablename and others with only the table name. Does
> the dbo do anything at all, especially if the user that request the
> query to execute is not the dbo simply a user with read only
> permissions?
Objects in a database can be referred to with three-part names on the
form catalog.schema.object.
In SQL Server "catalog" is the same as "database". Up to SQL 2000 "schema"
was the same as owner, but in SQL 2005 owner and schema have been separated.
A schema can be thought of as a namespace, so that different groups that
work in the same database can use different namespaces, so they don't
get name clashes.
In SQL 2000, each user has a default schema which is equal to the user
name - obvious since schema and owner is the same. If the user "fred" says
"SELECT * FROM tbl", SQL Server first looks up his default schema, so
if there is a table fred.tbl, this is what he will get. If there is not,
SQL Server moves to the default schema for the database, which is dbo.
By prefixing with dbo, "SELECT * FROM dbl.tbl", fred makes an unmabiguous
reference.
In practice, all objects in a database are owned by dbo in 99% of the
database world-wide, so dbo becomes a bit redudant. But as I discussed in
another post today, there can be a performance benefit, if you
specify the schema.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I think he means that dbo as the default database owner does not need to be
used, but should be for performance reasons.
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Jim Abel" wrote:
> I'm still unclear
> Please elaborate on what you mean by "if dbo exists" do yo mean if there i
s
> a dbo assigned to the database specifically as on of the users? and if one
> exists then how does it help if the query is requested by say the asp.net
> user that only has read access to the tables in the query?
> "Rajesh" wrote:
>|||Would adding dbo to db on a single server really make a difference in
performance?
(2000 or 2005)
thanks
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Rajesh" wrote:
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
> HTH
> Rajesh Peddireddy.
>
> "Jim Abel" wrote:
>|||I am very new to SQL Server but I thought dbo (DataBase Owner) always exists
,
it cannot be deleted.
Quote from MSDN:
========================================
======
The dbo user cannot be deleted and is always present in every database.
========================================
======
However, when to use it...I find very confusing.
T
"Rajesh" wrote:
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
> HTH
> Rajesh Peddireddy.
>
> "Jim Abel" wrote:
>|||I'm still unclear
Please elaborate on what you mean by "if dbo exists" do yo mean if there is
a dbo assigned to the database specifically as on of the users? and if one
exists then how does it help if the query is requested by say the asp.net
user that only has read access to the tables in the query?
"Rajesh" wrote:
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
> HTH
> Rajesh Peddireddy.
>
> "Jim Abel" wrote:
>|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> I have had some discussions with the SQL Server team on that one...
> In SQL 2000, if a plain user issues a SELECT statement or calls a
> stored procedure with qualifying the name with dbo, there is a cost.
But the order of magnitute of this cost for it to be considered a
"performance issue", or to make dbo prefixing a performance "best practice".
David|||If the sp's are called by multiple users concurrently or many times a second
the lack of owner qualification can have a huge performance impact. There is
no question this is more than just a best practice. It has definite
implications if not done.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23RIY3lcKGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> But the order of magnitute of this cost for it to be considered a
> "performance issue", or to make dbo prefixing a performance "best
> practice".
> David
>|||David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> But the order of magnitute of this cost for it to be considered a
> "performance issue", or to make dbo prefixing a performance "best
> practice".
I should have included a reference to this KB article:
http://support.microsoft.com/kb/q263889/.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23NQ3YDdKGHA.500@.TK2MSFTNGP15.phx.gbl...
> If the sp's are called by multiple users concurrently or many times a
> second the lack of owner qualification can have a huge performance impact.
> There is no question this is more than just a best practice. It has
> definite implications if not done.
>
What scenario are you claiming has a "huge performance impact"? Referencing
objects inside stored procedures? The objects are only resolved at query
parse/compile time, not on every call. IMO, basic best practices wrt shared
SQL eliminate any significant impact with object name resolution.
David
preceeded by dbo.tablename and others with only the table name. Does the db
o
do anything at all, especially if the user that request the query to execute
is not the dbo simply a user with read only permissions?Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> I see different examples of query statements some with the table names
> preceeded by dbo.tablename and others with only the table name. Does
> the dbo do anything at all, especially if the user that request the
> query to execute is not the dbo simply a user with read only
> permissions?
Objects in a database can be referred to with three-part names on the
form catalog.schema.object.
In SQL Server "catalog" is the same as "database". Up to SQL 2000 "schema"
was the same as owner, but in SQL 2005 owner and schema have been separated.
A schema can be thought of as a namespace, so that different groups that
work in the same database can use different namespaces, so they don't
get name clashes.
In SQL 2000, each user has a default schema which is equal to the user
name - obvious since schema and owner is the same. If the user "fred" says
"SELECT * FROM tbl", SQL Server first looks up his default schema, so
if there is a table fred.tbl, this is what he will get. If there is not,
SQL Server moves to the default schema for the database, which is dbo.
By prefixing with dbo, "SELECT * FROM dbl.tbl", fred makes an unmabiguous
reference.
In practice, all objects in a database are owned by dbo in 99% of the
database world-wide, so dbo becomes a bit redudant. But as I discussed in
another post today, there can be a performance benefit, if you
specify the schema.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I think he means that dbo as the default database owner does not need to be
used, but should be for performance reasons.
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Jim Abel" wrote:
> I'm still unclear
> Please elaborate on what you mean by "if dbo exists" do yo mean if there i
s
> a dbo assigned to the database specifically as on of the users? and if one
> exists then how does it help if the query is requested by say the asp.net
> user that only has read access to the tables in the query?
> "Rajesh" wrote:
>|||Would adding dbo to db on a single server really make a difference in
performance?
(2000 or 2005)
thanks
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Rajesh" wrote:
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
> HTH
> Rajesh Peddireddy.
>
> "Jim Abel" wrote:
>|||I am very new to SQL Server but I thought dbo (DataBase Owner) always exists
,
it cannot be deleted.
Quote from MSDN:
========================================
======
The dbo user cannot be deleted and is always present in every database.
========================================
======
However, when to use it...I find very confusing.
T
"Rajesh" wrote:
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
> HTH
> Rajesh Peddireddy.
>
> "Jim Abel" wrote:
>|||I'm still unclear
Please elaborate on what you mean by "if dbo exists" do yo mean if there is
a dbo assigned to the database specifically as on of the users? and if one
exists then how does it help if the query is requested by say the asp.net
user that only has read access to the tables in the query?
"Rajesh" wrote:
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
> HTH
> Rajesh Peddireddy.
>
> "Jim Abel" wrote:
>|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> I have had some discussions with the SQL Server team on that one...
> In SQL 2000, if a plain user issues a SELECT statement or calls a
> stored procedure with qualifying the name with dbo, there is a cost.
But the order of magnitute of this cost for it to be considered a
"performance issue", or to make dbo prefixing a performance "best practice".
David|||If the sp's are called by multiple users concurrently or many times a second
the lack of owner qualification can have a huge performance impact. There is
no question this is more than just a best practice. It has definite
implications if not done.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23RIY3lcKGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> But the order of magnitute of this cost for it to be considered a
> "performance issue", or to make dbo prefixing a performance "best
> practice".
> David
>|||David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> But the order of magnitute of this cost for it to be considered a
> "performance issue", or to make dbo prefixing a performance "best
> practice".
I should have included a reference to this KB article:
http://support.microsoft.com/kb/q263889/.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23NQ3YDdKGHA.500@.TK2MSFTNGP15.phx.gbl...
> If the sp's are called by multiple users concurrently or many times a
> second the lack of owner qualification can have a huge performance impact.
> There is no question this is more than just a best practice. It has
> definite implications if not done.
>
What scenario are you claiming has a "huge performance impact"? Referencing
objects inside stored procedures? The objects are only resolved at query
parse/compile time, not on every call. IMO, basic best practices wrt shared
SQL eliminate any significant impact with object name resolution.
David
Qualified table names
I have a quick question on how to qualify table names as it relates to
"dbo" vs. user names. Suppose that I am a user named "dwuser1", and
that I need to create a table named "dw_stage_1". Do I use dbo as in
"dbo.dw_stage_1" or do I use "dwuser1.dw_stage_1" for the qualified
table name? Are both OK? If so, what would be the implications of
each?php newbie (newtophp2000@.yahoo.com) writes:
> I have a quick question on how to qualify table names as it relates to
> "dbo" vs. user names. Suppose that I am a user named "dwuser1", and
> that I need to create a table named "dw_stage_1". Do I use dbo as in
> "dbo.dw_stage_1" or do I use "dwuser1.dw_stage_1" for the qualified
> table name? Are both OK? If so, what would be the implications of
> each?
"dbo" vs. user names. Suppose that I am a user named "dwuser1", and
that I need to create a table named "dw_stage_1". Do I use dbo as in
"dbo.dw_stage_1" or do I use "dwuser1.dw_stage_1" for the qualified
table name? Are both OK? If so, what would be the implications of
each?php newbie (newtophp2000@.yahoo.com) writes:
> I have a quick question on how to qualify table names as it relates to
> "dbo" vs. user names. Suppose that I am a user named "dwuser1", and
> that I need to create a table named "dw_stage_1". Do I use dbo as in
> "dbo.dw_stage_1" or do I use "dwuser1.dw_stage_1" for the qualified
> table name? Are both OK? If so, what would be the implications of
> each?
If you are user dwuser1, and you have CREATE TABLE permissions, and
you say:
CREATE TABLE dw_stage_1 (a int NOT NULL)
The full qualification for that table will be dwuser1.dw_stage_1. You
cannot refer to the table as dbo.dw_stage_1. As dwuser1 you can refer
to the table as dw_stage_1 and dwuser1.dw_stage_1. All other users,
including dbo, must refer to ut as dwuser1.dw_stage_1.
If you then log in as sa or any other login that map to dbo and say
CREATE TABLE dw_stage_1 (a int NOT NULL)
again, you have now created to dw_stage_1. As dbo you can refer to the
table as dbo.dw_stage_1 or dw_stage_1 only, and so can all other users
except for dwuser1, who must use dbo.dw_stage_1, since his only table
is ahead in the seatch path.
Best practice recommended my Microsoft is to refer to the table as
dbo.dw_stage_1. This is particularly important for loose SQL statements,
since SQL Server then can skip the search for user.dw_stage_1. They
say that this is also good in stored procedure, but in my opinion,
the dbo. becomes a four-letter line noice in a procedure that is owned
by dbo.
As for when to use objects not owned by dbo - beats me. I say, keep it
simple and only use dbo.
(In SQL 2005 where users and schema are separated, it's another story.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Friday, March 9, 2012
QA returning wrong data
SQL 7.0
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'
>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
table[vbcol=seagreen]
>
>.
>|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don
>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
> Just to make absolutely sure (nothing personal ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
Anith|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'
> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don
>.
>
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal
), how are youverifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'
>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
table[vbcol=seagreen]
>
>.
>|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don
>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
> Just to make absolutely sure (nothing personal
),how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
Anith|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'
> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don
>.
>
QA returning wrong data
SQL 7.0
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal :) ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'
>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
>> SQL 7.0
>> In QA:
>> "Select name from <table> where date = '20041010'
>> I get a list of 75,000 names. The name "Jones" is not
in
>> the list.
>> However, if in QA:
>> "Select * from <table> where name = 'Jones' and ddate
>> = '20041010'
>> The name "Jones" is returned!
>> I can't figure this out for the life of me. It's a
table
>> with 95 million records and a clustered index based on
>> name,date.
>> Any help appreciated.
>> thx,
>> Don
>
>.
>|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don
>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
>> "Select name from <table> where date = '20041010'
>> I get a list of 75,000 names. The name "Jones" is not
in
>> the list.
> Just to make absolutely sure (nothing personal :) ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
--
Anith|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'
> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don
>.
>
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal :) ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'
>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
>> SQL 7.0
>> In QA:
>> "Select name from <table> where date = '20041010'
>> I get a list of 75,000 names. The name "Jones" is not
in
>> the list.
>> However, if in QA:
>> "Select * from <table> where name = 'Jones' and ddate
>> = '20041010'
>> The name "Jones" is returned!
>> I can't figure this out for the life of me. It's a
table
>> with 95 million records and a clustered index based on
>> name,date.
>> Any help appreciated.
>> thx,
>> Don
>
>.
>|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don
>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
>> "Select name from <table> where date = '20041010'
>> I get a list of 75,000 names. The name "Jones" is not
in
>> the list.
> Just to make absolutely sure (nothing personal :) ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
--
Anith|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'
> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don
>.
>
QA returning wrong data
SQL 7.0
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.
|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>
|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'
>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
table
>
>.
>
|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don
>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in
> Just to make absolutely sure (nothing personal ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>
|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
Anith
|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO
|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?
|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'
> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don
>.
>
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal
), how are youverifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.
|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>
|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'
>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
table
>
>.
>
|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don
>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in
> Just to make absolutely sure (nothing personal
),how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>
|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
Anith
|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO
|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?
|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'
> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don
>.
>
Subscribe to:
Posts (Atom)