Index on bit column

Hi,

MSSql2000: According to docs:
"Columns of type bit cannot have indexes on them"

Its impossible to define index on bit column using EM but create index command in QA is working and the index is created.
I understand why not to create index like this but its valid or invalid operation to create index on BIT column ?

ThanksWhile it does not make sense to create an index on a bit column, it is a valid operation. EM filters out the bit columns when it shows you the columns available to create indexes.

Bible also says:
(Under the topic "Create Index")
Columns consisting of the ntext, text, or image data types cannot be specified as columns for an index.

Not that Bit is not mentioned in this list.|||Hi sbaru, thanks for the reply.

I would like to know why EM filters out the bit columns for index creation.

I found the limitation about bit as index field under the topic "bit data type, described"

Index hint in delete statement?

According to what I see in BOL, the following should work:

delete

from dbo.tbl1 WITH (INDEX(idx_un01))

where tbl1_no = 1

Yet when I syntax check this I get:

Msg 1069, Level 15, State 1, Line 2

Index hints are only allowed in a FROM clause.

(Please ignore the fact that index hints are unnecessary / a bad idea / etc.)

What you wrote is the equivalent of:

delete

dbo.tbl1 WITH (INDEX(idx_un01))

where tbl1_no = 1

So really there's no FROM clause, and the error message returned is correct. If you're going to use a FROM clause, you need to specify what tables you're deleting from. i.e.:

delete dbo.tbl1

from dbo.tbl1 WITH (INDEX(idx_un01))

where tbl1_no = 1

In theory, you can join multiple tables in your FROM clause, which is why you need to indicate what table you want to delete.

|||

Hmmm. Interesting. What is even more interesting is that it accepts the WITH (ROWLOCK) hint without a quibble. I also don't see this syntax as being required in BOL. (It is only mentioned for joins / correlated subqueries.)

ref: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ed6b2105-0f35-408f-ba51-e36ade7ad5b2.htm

|||

The syntax is TSQL extension for UPDATE/DELETE statement. The ANSI SQL syntax for UPDATE and DELETE does not have a FROM clause at all. So there are two FROM clauses in the TSQL version of DELETE. I think it is a bug that we allow the locking hint and not the INDEX hint. IMO, both should be allowed without the FROM clause containing the table sources. You may want to file a bug at http://connect.microsoft.com. And both FROM clauses are optional also. See the DELETE statement topic in BOL where it is documented completely.

Generally speaking you should try to use the ANSI SQL syntax as far as possible and avoid using hints in most DML statements. The hint is just a hint and it can be overridden due to resource constraints for example. And by forcing certain indexes you are restricting the plan choices for the query optimizer also. Is there any specific reason why you want to use the hint in the DELETE statement? Are you having some wrong plan choices for the DELETE statement? If so, that might be a bug and it will be good if you can post a repro for that.

index & statistics

sql2000
is there some statistics that will show me if some index is missing?
i mean, according to table usage, queries etc, that overall performance
[respond time] would be improved if some column being indexed
i've read that server may refuse to use some existing index during query if
estimated that no performance would be gained?
is server able to by itself create and use temporary index if estimate that
flat table scan is time & disk i/o wasting?
any comments?
thnx
SQL Server 2005 keep track of index usage over time and also can suggest you to create new useful
indexes (through some dynamic management views). 2000 do not have that functionality.

> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
Yes, SQL Server estimate cost for different ways of executing a query and will use the plan it
considers the cheapest.

> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
To create an index, it would have to scan all data. IF the alternative is a table scan in the first
place, why scan all data, create the index, if the alternative was to scan all data in the first
place? Having said that, there are strategies that SQL Server can use which are similar to creating
an index on the fly.
Say you have a join between two tables, and if you were to execute this so that "for each row in
tableA, look for matches in tableB". Now, tableB would be scanned over *several times*. SQL Server
6.5 and earlier could create a "temporary" index on tableB. As of 7.0, we have more modern methods,
like a "hash join", which at a *very* high level could be considered like creating a temporary
index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23W2tx1M1HHA.3788@.TK2MSFTNGP02.phx.gbl...
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance [respond time] would be
> improved if some column being indexed
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
> any comments?
> thnx
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je
napisao u poruci interesnoj
grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...

> To create an index, it would have to scan all data. IF the alternative is
> a table scan in the first place, why scan all data, create the index, if
> the alternative was to scan all data in the first place?
no! no!
i meant in relation with statistics [having history of usage]
if some query is repeatedly [many times per day] used as flat scan, f.e
like:
select *
from tbl1
where fld1 between val1 and val2
is sql server able to recognize that having index on fld1 may *dramaticaly*
improve performances?
|||SQL Server 2005 will recognize that fact and you can query the dynamic management views to get this
historical information, and create indexes based on that information. SQL Server 2005 will not
create indexes by itself.
SQL Server 2000 does not have any such functionality, so Index Tuning Wizard, as suggested by
Jonathan can be a tool to consider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:uSvVWrO1HHA.6072@.TK2MSFTNGP03.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je napisao u poruci interesnoj
> grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>
> no! no!
> i meant in relation with statistics [having history of usage]
> if some query is repeatedly [many times per day] used as flat scan, f.e like:
> select *
> from tbl1
> where fld1 between val1 and val2
> is sql server able to recognize that having index on fld1 may *dramaticaly* improve performances?
>
>

index & statistics

sql2000
is there some statistics that will show me if some index is missing?
i mean, according to table usage, queries etc, that overall performance
[respond time] would be improved if some column being indexed
i've read that server may refuse to use some existing index during query if
estimated that no performance would be gained?
is server able to by itself create and use temporary index if estimate that
flat table scan is time & disk i/o wasting?
any comments?
thnxSQL Server 2005 keep track of index usage over time and also can suggest you
to create new useful
indexes (through some dynamic management views). 2000 do not have that funct
ionality.

> i've read that server may refuse to use some existing index during query i
f estimated that no
> performance would be gained?
Yes, SQL Server estimate cost for different ways of executing a query and wi
ll use the plan it
considers the cheapest.

> is server able to by itself create and use temporary index if estimate tha
t flat table scan is
> time & disk i/o wasting?
To create an index, it would have to scan all data. IF the alternative is a
table scan in the first
place, why scan all data, create the index, if the alternative was to scan a
ll data in the first
place? Having said that, there are strategies that SQL Server can use which
are similar to creating
an index on the fly.
Say you have a join between two tables, and if you were to execute this so t
hat "for each row in
tableA, look for matches in tableB". Now, tableB would be scanned over *seve
ral times*. SQL Server
6.5 and earlier could create a "temporary" index on tableB. As of 7.0, we ha
ve more modern methods,
like a "hash join", which at a *very* high level could be considered like cr
eating a temporary
index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23W2tx1M1HHA.3788@.TK2MSFTNGP02.phx.gbl...[v
bcol=seagreen]
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance &#
91;respond time] would be
> improved if some column being indexed
> i've read that server may refuse to use some existing index during query i
f estimated that no
> performance would be gained?
> is server able to by itself create and use temporary index if estimate tha
t flat table scan is
> time & disk i/o wasting?
> any comments?
> thnx
>[/vbcol]|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je
napisao u poruci interesnoj
grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...

> To create an index, it would have to scan all data. IF the alternative is
> a table scan in the first place, why scan all data, create the index, if
> the alternative was to scan all data in the first place?
no! no!
i meant in relation with statistics [having history of usage]
if some query is repeatedly [many times per day] used as flat scan, f.e
like:
select *
from tbl1
where fld1 between val1 and val2
is sql server able to recognize that having index on fld1 may *dramaticaly*
improve performances?|||Hi,
I think you need to consider using the Index Tuning Wizard to help you
out here -
http://www.microsoft.com/technet/pr...n/tunesql.mspx.
Jonathan
sali wrote:
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance
> [respond time] would be improved if some column being indexed
> i've read that server may refuse to use some existing index during query i
f
> estimated that no performance would be gained?
> is server able to by itself create and use temporary index if estimate tha
t
> flat table scan is time & disk i/o wasting?
> any comments?
> thnx
>|||SQL Server 2005 will recognize that fact and you can query the dynamic manag
ement views to get this
historical information, and create indexes based on that information. SQL Se
rver 2005 will not
create indexes by itself.
SQL Server 2000 does not have any such functionality, so Index Tuning Wizard
, as suggested by
Jonathan can be a tool to consider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:uSvVWrO1HHA.6072@.TK2MSFTNGP03.phx.gbl...[vbc
ol=seagreen]
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je napi
sao u poruci interesnoj
> grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>
> no! no!
> i meant in relation with statistics [having history of usage]
> if some query is repeatedly [many times per day] used as flat scan, f.
e like:
> select *
> from tbl1
> where fld1 between val1 and val2
> is sql server able to recognize that having index on fld1 may *dramaticaly
* improve performances?
>
>[/vbcol]

index & statistics

sql2000
is there some statistics that will show me if some index is missing?
i mean, according to table usage, queries etc, that overall performance
[respond time] would be improved if some column being indexed
i've read that server may refuse to use some existing index during query if
estimated that no performance would be gained?
is server able to by itself create and use temporary index if estimate that
flat table scan is time & disk i/o wasting?
any comments?
thnxSQL Server 2005 keep track of index usage over time and also can suggest you to create new useful
indexes (through some dynamic management views). 2000 do not have that functionality.
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
Yes, SQL Server estimate cost for different ways of executing a query and will use the plan it
considers the cheapest.
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
To create an index, it would have to scan all data. IF the alternative is a table scan in the first
place, why scan all data, create the index, if the alternative was to scan all data in the first
place? Having said that, there are strategies that SQL Server can use which are similar to creating
an index on the fly.
Say you have a join between two tables, and if you were to execute this so that "for each row in
tableA, look for matches in tableB". Now, tableB would be scanned over *several times*. SQL Server
6.5 and earlier could create a "temporary" index on tableB. As of 7.0, we have more modern methods,
like a "hash join", which at a *very* high level could be considered like creating a temporary
index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23W2tx1M1HHA.3788@.TK2MSFTNGP02.phx.gbl...
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance [respond time] would be
> improved if some column being indexed
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
> any comments?
> thnx
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je
napisao u poruci interesnoj
grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>> is server able to by itself create and use temporary index if estimate
>> that flat table scan is time & disk i/o wasting?
> To create an index, it would have to scan all data. IF the alternative is
> a table scan in the first place, why scan all data, create the index, if
> the alternative was to scan all data in the first place?
no! no!
i meant in relation with statistics [having history of usage]
if some query is repeatedly [many times per day] used as flat scan, f.e
like:
select *
from tbl1
where fld1 between val1 and val2
is sql server able to recognize that having index on fld1 may *dramaticaly*
improve performances?|||Hi,
I think you need to consider using the Index Tuning Wizard to help you
out here -
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspx.
Jonathan
sali wrote:
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance
> [respond time] would be improved if some column being indexed
> i've read that server may refuse to use some existing index during query if
> estimated that no performance would be gained?
> is server able to by itself create and use temporary index if estimate that
> flat table scan is time & disk i/o wasting?
> any comments?
> thnx
>|||SQL Server 2005 will recognize that fact and you can query the dynamic management views to get this
historical information, and create indexes based on that information. SQL Server 2005 will not
create indexes by itself.
SQL Server 2000 does not have any such functionality, so Index Tuning Wizard, as suggested by
Jonathan can be a tool to consider.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:uSvVWrO1HHA.6072@.TK2MSFTNGP03.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je napisao u poruci interesnoj
> grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>> is server able to by itself create and use temporary index if estimate that flat table scan is
>> time & disk i/o wasting?
>> To create an index, it would have to scan all data. IF the alternative is a table scan in the
>> first place, why scan all data, create the index, if the alternative was to scan all data in the
>> first place?
> no! no!
> i meant in relation with statistics [having history of usage]
> if some query is repeatedly [many times per day] used as flat scan, f.e like:
> select *
> from tbl1
> where fld1 between val1 and val2
> is sql server able to recognize that having index on fld1 may *dramaticaly* improve performances?
>
>