index and key: table1

Showing posts with label table1. Show all posts

Wednesday, March 28, 2012

Index scan

If i have only one index and that being a clustered index on say a datetime
column namely date1 and if my query is
select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
If I look at the query plan and it says its doing an index scan, does that
mean its actually touching each and every page of that table or will it
somehow start at the page that has the first entry for '2/1/2008' and
continues through the linked list at the leaf level of the pages and stops
after it reaches '2/3/2008' ?
How is this different if instead of a clustered index, its a non clustered
index ?
ThanksJohn
It is doing Clustered Index Scan , you meant? If you have CI on the table
that means SQL Server logicaly orders all data by Clustered Index Key.
It depends on the query , an optimizer may or may not decide to do a scan
, for example the table is pretty small .
In this case it scans index pages toread the data which ordered (logicaly)
by date column
In you case I'd suggest t create an index on col1,col2 and dt columns called
COVERING index.
> How is this different if instead of a clustered index, its a non clustered
> index ?
The difference is that clusterd index contains at the bottom level the
actual data , while noclustetred contains pointers to the data pages.
"John Doe" <Johndoe@.jd.com> wrote in message
news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
> If i have only one index and that being a clustered index on say a
> datetime column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
> If I look at the query plan and it says its doing an index scan, does that
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered
> index ?
> Thanks
>|||Uri,
In my case, its doing a clustered index scan and wanted to know if as a
result, its touching all the pages that may have dates prior to '2/1' and
after '2/3' as my query is only seeking to obtain data between '2/1/2008 and
'2/3/2008' and as you mentioned that data in the CI is ordered. Let me know
how the storage engine fetches the pages.
Also if it was a non clustered index on date1 instead of a clustered index,
i take it that the leaf level of the non clustered index is also
ordered..right ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
> John
> It is doing Clustered Index Scan , you meant? If you have CI on the table
> that means SQL Server logicaly orders all data by Clustered Index Key.
> It depends on the query , an optimizer may or may not decide to do a scan
> , for example the table is pretty small .
> In this case it scans index pages toread the data which ordered (logicaly)
> by date column
> In you case I'd suggest t create an index on col1,col2 and dt columns
> called COVERING index.
>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
> The difference is that clusterd index contains at the bottom level the
> actual data , while noclustetred contains pointers to the data pages.
>
> "John Doe" <Johndoe@.jd.com> wrote in message
> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or will
>> it somehow start at the page that has the first entry for '2/1/2008' and
>> continues through the linked list at the leaf level of the pages and
>> stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>|||Let me get back to the original question. SQL Server may decide to go for
partial scan (reading a range of values) if data is ordered.
And yes, data is ordered logically by the key in both cases, difference
being that in the CI, ALL data is in the leaf level.
MC
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>|||John
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
Think about CI as phone book which is 'ordered' by LastName ,so if you
want to search by Lastname is easy to traverse and get it
But what if you want to search by NCI -FirstName, then you will have to
page by page which requires an 'extra work'
I'd suggest to take some course or buying a book to underastand the
structure and behaviour.
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>|||On Feb 19, 12:45=A0pm, "John Doe" <John...@.jd.com> wrote:
> If i have only one index and that being a clustered index on say a datetim=e
> column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'=
> If I look at the query plan and it says its doing an index scan, does that=
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops=
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered=
> index ?
> Thanks
With clustered indexes records are stored on the leaf level and
therefore faster but non-clustered indexes leaf level pages have
locations to the page of searched record and therefore slower. HTH.|||If you have a clustered index, yes, it will go to the first entry in the
table with a datetime between '2/1/2008' and '2/3/2008', do a partial scan
of the clustered index and stop as soon as it finds an datetime >
'2/3/2008'. BTW, it is best in SQL Server to specify datetimes as
'yyyy-mm-ddThh:mm:ss.mmm'
or 'yyyy-mm-ddThh:mm:ss'
or 'yyyymmdd'
When you use a date format like '2/1/2008', that is Feb 1 in some locations
and Jan 2 in others. But '20080201' is Feb 1 everywhere.
For a nonclustered index, the answer is, "it depends". If the index is
nonclustered, then SQL Server knows the rows for any given value of dt might
be scattered throughout the physical table. So, for example, the first row
might be in page 1000, followed by a bunch of rows in other pages, followed
by another row in page 1000. But by this time page 1000 might not be in
memory anymore, so the page must be physically read again (this could
possibly happen many times). So SQL Server attempts to keep statistics on
how many rows are in each range and will attempt to estimate what percentage
of the table your query will return. If it is a small percentage, it will
use your index on dt, go to the first entry in the index with a date >='2/1/2008', start there and scan the index until it reaches a row with a dt
> '2/3'2008' and then stop. For each row it finds in the index on dt, it
will then use the clustered index to find the actual row and return your
values. But if it is a large percentage, then SQL Server will just scan the
entire clustered index (that is, it won't use the nonclustered index on dt
at all), because it knows that way it only has to read each physical page in
the clustered index once.
As Uri points out, your nonclustered index can be what is known as a
"covering index" for your query. This occurs when every column you need for
your query is in the index (either explicitly as part of the key or in the
INCLUDED columns or implicitly because the column is in the key of the
clustered index). When that happens, SQL Server knows it can satisify the
query requirements without ever reading from the table, so it will use the
index and only scan the part of the index it needs for the range of data you
want.
Tom
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>

Monday, March 26, 2012

index question?

I do have a dump table "table1"

Table1:

UniqueId varchar(4)

scandate datetime

courseno varchar(5).

This table is not related to any other tables in the database. This is used to track the student attendance for a particular course. There can 60000 distinct uniqueid's in the table and 300 disticnt courses. I query this table most of the time on uniqueid and coursecrn combination.

This table can grow up to 5 million records.

can anybody tell on what fields do I have to create indexes and also of what type(clustered or non-clustered). This table doesnt have a primary key.

Thanks

Sandeep:

It appears to me that at the very least you need an index based on (1) uniqueID, (2) courseNo and (3) scanDate. This index looks like it might be a candidate for a primary key; however, this also looks like a staging table. If that is the case it might be best to just index it, allow duplicates so that the data can be staged and deal with the duplicates later -- but without more information it's hard to say. I choose uniqueID first simply because it has the higher cardinality. Adding this index reduced logical IO from 11,000 to 3. Time from 313 ms to 0 ms. Test query for testing this index:

My guess is that you will also need an index based on (1) courseNo, (2) scanDate and (3) uniqueId. When I ran a query that did not include uniqueId as part of the filtering criteria this index brought a big performance boost because it avoided the table scan. Additing this index reduced logical IO from 11,000 to 9. Time went from 343 ms to 0 ms.

Test query for testing this index:

You might also need an index based on scanDate. This depends on whether or not you have any queries that have scanDate as the filter criteria but do filter based on either courseNo or uniqueId. If I were creating this index I would also include the other two columns in the index to avoid potential bookmark lookups. I didn't create this hypothetical index and also did no associated tests.

|||

Dave:

I will tell you my database structure. This is simple. I have only three tables.

Student: Uniqueid int (PK), lname varchar(35), fname varchar(35)

courses: Courseno(PK), coursetime, courseday.

Table1(this is dump table): uniqueid, scandate, courseno.

This dump table is not related to student or courses table bc without a record in students table then student can attend a class.

If student attends a class for 45 days in a semester for a particular course then dump table will have 45 records. say if he swipes his id thrice (thinking something wrong ) then there will be three records in the dump table. This dump table is also the final table. If i want to see the attendance of students in a particular course i query this table. do you think I need a table out of this dump table or just adding the indexes if fine for this table.?

as far i understand from ur reply, you suggest to create non-clustered index on uniqueid, scandate, coursecrn?

|||

Sandeep:

This is not one of the indexes that I suggested. I suggested two indexes. I suggest the first index be based on (1) uniqueID, (2) courseNo and (3) scandate; I suggest the second index be based on (1) courseNo, (2) scandate and (3) uniqueID. My knee-jerk reaction is that the indexes should be sufficient for checking attendence of students in classes. It is not possible to give more than a knee-jerk response without knowing the full set of requirements and without knowing the entire usage spectrum of your application and these tables.

|||

do the order of the fields mentioned in index makes a difference?

|||

Yes, it makes a difference; moreover, there may be a good reason to want to change the order of the columns to meet particular needs of your application. Is there a particular reason that you are potentially interested in creating an index based on the order you stated -- uniqueid, scandate, coursecrn?

I chose a different order because of your first statement that " ... I query this table most of the time on uniqueid and coursecrn combination. ..." The order I chose provides the narrowest target for a query that does not include scandate as part of the filter criteria.

|||i just mentioned the order as an example...I will create two non-clustered indexes as you suggested. one more question: say, If the dump table has 10 million records and when we insert 300 more records to that ( as the table has indexes and it will re-organize the table when a new record is inserted) am i going to have a peformance issue? or not much|||

I don't think you will see much negative performance impact as long as you perform normal periodic maintenance. I was just working on something else and I realized something. It seems to me that if scanDate is a true datetime variable -- that is, the time portion of the data is generated and included -- that the logical key to your table is (1) uniqueID and (2) scanDate. The reason I believe this is that it is not physically possible for a student to be doing more than 1 badge scan at any one time. If that is true, then there is reason to set up the primary key based on these two columns.

|||

ya scandate is datetime datatype.

is this the final solution you suggest:

1. create primary key on uniqueid and scandate( is it clustered or non-clustered)

2. non-clustered on uniqueid, scandate, coursrno

3. non-clustered on courseno, scandate, uniqueid

can you please correct me if i am wrong?

|||

Sandeep:

What we need to do is run a query to verify whether or not the primary key can be applied. If it can, I dont think we will need index #2. Try running this query:

if no rows are returned, then the primary key will work; if it will not, then we need to forget about the primary key.

|||

dave,

it doesnt return any rows so i think we can create primary key on uniqueid and scandate.

one more question: why dont we need this non-clustered index on courseno, scandate, uniqueid if we can create the primary key? sorry to ask you many questions, I am new to this indexing...

Thanks

|||

Sandeep:

I think that you DO still need index you referred to as number 3 -- the one based on (1) courseno, (2), scandate and (3) uniqueID. To some extent I am "guessing" at this because you sand that "most" of your queries were based on courseno and uniqueID. Again, when I tested a select that filtered records based on courseNo and a date range but did NOT filter based on uniqueID the select performed a table scan. What that means is that SQL Server examines each and every one of the 3 million records (my mock-up was 3 million instead of 10 million) to see if it selected by our filtering criteria. When we create the index, the number of records examined is reduced from 3 million to something like 400 -- and that is a huge reduction.

The real issue is whether or not we need the index you referred to as number 2. I have a good deal of doubt that it is worth the price of the trade-off. Also, if it is needed we can always defer that decision and add it in if it turns out to be especially beneficial. The issue with index #2 is that it is almost the same as the primary key. While there might be some queries that will benefit a little from index #2 it is likely that most of these queries will still run almost as fast off the primary key. Hence, the benefit of index number 2 might not be great.

The other factor for considering an index is the cost of the index. This table will have 10 million rows. There is an overhead cost of maintaing any index. In this case I doubt that the potential gain -- probably a minimal gain -- that will come with index #2 is not worth the cost of the index. That would NOT be the case if there was no primary key!

I will run a couple of quick tests tomorrow morning to put some numbers together to do an actual comparison between the primary key and index #2 so that you can make an appraisal.

|||

Sandeep:

I used my mockup table with the primary key composed of (1) uniqueID and (2) scandate and the index composed of (1) courseNo, (2) uniqueID and (3) scanDate and loaded the mockup table with 32767 different uniqueID and 300 different courseNo with about 90 different scan dates to create a mockup table with about 3 million rows. I used this mockup for the basis of my tests.

First I tested with this query:

This query took 0 ms with 3 logical reads; the query plan was based on a clustered index seek, therefore, filtering was efficient. 22 rows were returned.

I next tested this query:

This query took 0 ms with 10 logical reads; the query plan was based on an index seek of the nonclustered index, therefore, filtering was efficient. 2290 rows were returned.

Finally, I tested this query:

This query took 0 ms with 3 logical reads; the query plan was based on a clustered index seek, therefore, filtering was efficient. 92 riws were returned.

After this, I added the index based on (1) uniqueId, (2) scanDate and (3) courseNo and reran all of the queries. The index plans of two of the queries was changed to use this index; however, there was no measurable difference. Execution time was still 0 ms and the required amount of logical reads did not change.

|||

Dave...Thanks a lot for your effort. what is the meaning of logical read?.

"the index composed of (1) courseNo, (2) uniqueID and (3) scanDate " is the order correct? because yday we discussed tht the index should be on courseno, scandate, uniqueid....

|||

Sandeep:

A "logical read" is one of the measurements of the work performed to satisfy a query. In this case the "3 logical reads" is three seeks of 8K data pages or 24K -- a very small amount of work. When we were performing either the table scan or the clustered index scan -- that is when we read without any indexes or a primary key -- we were reading 11,000 logical reads -- but my mockup was for 3 million rows of data instead of 10 million rows of data. 10 million rows of data would translate into about 38,000 logical reads -- about 308 Meg of data as opposed to 24K. This is a difference of a very large magnitude. This difference really becomes our primary motivation for indexing.

Related to the index needs:

The reason that the index needs to be based on (1) courseNo, (2) uniqueID and (3) scandate is to support queries that filter based on courseNo and uniqueID. If the order is changed to (1) courseNo, (2) scandate and (3) uniqueID there will be an increase in the number of logical reads required to fetch the data for this kind of search.

Index question

If I create two indexes for the same column, how would I know which index is
used?
for example,
create unique index UNI_INDX1 ON table1(column1 desc)
create unique index UNI_INDX1 ON table1(column1 asc)
You cannot create more than 1 index with the same name. If you run the
statement you will get the following error:
Server: Msg 1913, Level 16, State 1, Line 1
There is already an index on table 'Customers' named 'UNI_INDX1'.
If you want to see the execution plan in QA hit Ctl+k or select it from the
Query menu.
If you want to see the plan in text, use the statement
SET SHOWPLAN_ALL ON
GO
before your DML statement.
Bryan Bitzer MCP
Senior Database Administrator
Marshall & Swift / Boeckh
www.msbinfo.com
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index
is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>
|||In addition to Bryan's post:
There's absolutely no reason to create both ASC and DESC index on one column. SQL Server can
traverse an index in both directions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>
|||Bryan and Tibor,
Thanks for the idea. Bryan is right that I can't have same name for two
different index.
Well I am not understanding when, if there is a need, to create multiple
types of Index for one or muiltiple columns.
Another word, why would SQLsrvr2k allow muliple index in one column?
Ideas? Thanks again.
|||Say you have a query like:
...
ORDER BY col1 ASC, col2 DESC
In this case you'd want an index defined in the same way as your ORDER BY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:86DCF615-C593-476A-89CD-7A418F8E1436@.microsoft.com...
> Bryan and Tibor,
> Thanks for the idea. Bryan is right that I can't have same name for two
> different index.
> Well I am not understanding when, if there is a need, to create multiple
> types of Index for one or muiltiple columns.
> Another word, why would SQLsrvr2k allow muliple index in one column?
> Ideas? Thanks again.
>
|||On Thu, 2 Sep 2004 08:29:06 -0700, light_wt wrote:

>Bryan and Tibor,
>Thanks for the idea. Bryan is right that I can't have same name for two
>different index.
>Well I am not understanding when, if there is a need, to create multiple
>types of Index for one or muiltiple columns.
>Another word, why would SQLsrvr2k allow muliple index in one column?
>Ideas? Thanks again.
Hi light_wt,
Two indexes on one columns is redundancy. But two indexes on a set of two
columns may be interesting. If you have an index on (colA, colB), it can
be used for queries where both colA and colB must be equal to some value;
it can also be used if only colA is known. But this index serves no
purpose if I have to find all rows where colB = some value. If I often
have to search for colB, I might wish to create another index on only
colB, or on colB plus one or more other columns.
Another reason why SQL Server MUST allow multiple index in one column is
that indexes are used to check UNIQUE and PRIMARY KEY constraints. There
are lots of scenario's where both (Col1, Col2) and (Col1, Col3) are unique
combinations.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks, Hugo. Your answer make sense.

light_wt.

Index question

If I create two indexes for the same column, how would I know which index is
used?
for example,
create unique index UNI_INDX1 ON table1(column1 desc)
create unique index UNI_INDX1 ON table1(column1 asc)You cannot create more than 1 index with the same name. If you run the
statement you will get the following error:
Server: Msg 1913, Level 16, State 1, Line 1
There is already an index on table 'Customers' named 'UNI_INDX1'.
If you want to see the execution plan in QA hit Ctl+k or select it from the
Query menu.
If you want to see the plan in text, use the statement
SET SHOWPLAN_ALL ON
GO
before your DML statement.
Bryan Bitzer MCP
Senior Database Administrator
Marshall & Swift / Boeckh
www.msbinfo.com
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index
is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>|||In addition to Bryan's post:
There's absolutely no reason to create both ASC and DESC index on one column
. SQL Server can
traverse an index in both directions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index
is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>|||Bryan and Tibor,
Thanks for the idea. Bryan is right that I can't have same name for two
different index.
Well I am not understanding when, if there is a need, to create multiple
types of Index for one or muiltiple columns.
Another word, why would SQLsrvr2k allow muliple index in one column?
Ideas? Thanks again.|||Say you have a query like:
...
ORDER BY col1 ASC, col2 DESC
In this case you'd want an index defined in the same way as your ORDER BY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:86DCF615-C593-476A-89CD-7A418F8E1436@.microsoft.com...
> Bryan and Tibor,
> Thanks for the idea. Bryan is right that I can't have same name for two
> different index.
> Well I am not understanding when, if there is a need, to create multiple
> types of Index for one or muiltiple columns.
> Another word, why would SQLsrvr2k allow muliple index in one column?
> Ideas? Thanks again.
>|||On Thu, 2 Sep 2004 08:29:06 -0700, light_wt wrote:

>Bryan and Tibor,
>Thanks for the idea. Bryan is right that I can't have same name for two
>different index.
>Well I am not understanding when, if there is a need, to create multiple
>types of Index for one or muiltiple columns.
>Another word, why would SQLsrvr2k allow muliple index in one column?
>Ideas? Thanks again.
Hi light_wt,
Two indexes on one columns is redundancy. But two indexes on a set of two
columns may be interesting. If you have an index on (colA, colB), it can
be used for queries where both colA and colB must be equal to some value;
it can also be used if only colA is known. But this index serves no
purpose if I have to find all rows where colB = some value. If I often
have to search for colB, I might wish to create another index on only
colB, or on colB plus one or more other columns.
Another reason why SQL Server MUST allow multiple index in one column is
that indexes are used to check UNIQUE and PRIMARY KEY constraints. There
are lots of scenario's where both (Col1, Col2) and (Col1, Col3) are unique
combinations.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo. Your answer make sense.

light_wt.

Friday, March 23, 2012

Index question

If I create two indexes for the same column, how would I know which index is
used?
for example,
create unique index UNI_INDX1 ON table1(column1 desc)
create unique index UNI_INDX1 ON table1(column1 asc)You cannot create more than 1 index with the same name. If you run the
statement you will get the following error:
Server: Msg 1913, Level 16, State 1, Line 1
There is already an index on table 'Customers' named 'UNI_INDX1'.
If you want to see the execution plan in QA hit Ctl+k or select it from the
Query menu.
If you want to see the plan in text, use the statement
SET SHOWPLAN_ALL ON
GO
before your DML statement.
--
Bryan Bitzer MCP
Senior Database Administrator
Marshall & Swift / Boeckh
www.msbinfo.com
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index
is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>|||In addition to Bryan's post:
There's absolutely no reason to create both ASC and DESC index on one column. SQL Server can
traverse an index in both directions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>|||Say you have a query like:
...
ORDER BY col1 ASC, col2 DESC
In this case you'd want an index defined in the same way as your ORDER BY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:86DCF615-C593-476A-89CD-7A418F8E1436@.microsoft.com...
> Bryan and Tibor,
> Thanks for the idea. Bryan is right that I can't have same name for two
> different index.
> Well I am not understanding when, if there is a need, to create multiple
> types of Index for one or muiltiple columns.
> Another word, why would SQLsrvr2k allow muliple index in one column?
> Ideas? Thanks again.
>|||On Thu, 2 Sep 2004 08:29:06 -0700, light_wt wrote:
>Bryan and Tibor,
>Thanks for the idea. Bryan is right that I can't have same name for two
>different index.
>Well I am not understanding when, if there is a need, to create multiple
>types of Index for one or muiltiple columns.
>Another word, why would SQLsrvr2k allow muliple index in one column?
>Ideas? Thanks again.
Hi light_wt,
Two indexes on one columns is redundancy. But two indexes on a set of two
columns may be interesting. If you have an index on (colA, colB), it can
be used for queries where both colA and colB must be equal to some value;
it can also be used if only colA is known. But this index serves no
purpose if I have to find all rows where colB = some value. If I often
have to search for colB, I might wish to create another index on only
colB, or on colB plus one or more other columns.
Another reason why SQL Server MUST allow multiple index in one column is
that indexes are used to check UNIQUE and PRIMARY KEY constraints. There
are lots of scenario's where both (Col1, Col2) and (Col1, Col3) are unique
combinations.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo. Your answer make sense. :)
light_wt.

Wednesday, March 21, 2012

Index on query with both where clause and order by

What is best index for this query

Select * from Table1 whete Table1.C1 >100 and Table1.C2=23 order by Table1.C3

How many index do I need and what column order?

I would appreciate if some body can put link to complex query index guideline

Thanks for help.

It's difficult to give an optimal answer without first gaining an understanding of the distribution and volume of your data. For instance if your table contains only one row then a full table scan will be the most efficient way of returning the results. If your table contains millions of rows, yet 99% of the rows satisfy the criteria (Table1.C1 > 100 AND Table1.C2 = 23) then a table scan is still likely to be the most efficient way of obtaining the data.

As a first step, have you tried running the query past either the 'Index Tuning Wizard' or the 'Database Tuning Advisor'?

Chris

|||

Do you actually need all of the columns to be returned in the SELECT statement? Depending on the answer to that, you may be able to use a "covering index", that covers the query without having to access the base table.

In your case that means a single index would have C3, C2, C1, and all of the columns in the SELECT list included. Depending on how many columns are in the SELECT list, this may or may not be feasible. The new INCLUDED columns feature in SQL Server 2005 gives you a lot more flexibility there. You also have to consider how volatile the data is.

As far as column order for the index, it depends on the selectivity of the data in the columns. Most often, the columns in the WHERE clause are the most important (i.e. they are the first columns in the index).

I would try creating two indexes, one with C1, C2, C3, and the second with C2, C1,C3 to start.

Really, you just need to fire up a Query window in SSMS, run SET STATISTICS IO ON, turn on the graphical execution plan, and then run the query a few times with different input values, and see which gives the best results.

You would also want to parametize the query or make it a stored procedure with input parameters, so that you get one copy of it in the procedure cache. If it is hard-coded with literal values or submitted ad-hoc that way, you will have multiple copies of it in the procedure cache.

http://glennberrysqlperformance.spaces.live.com

Monday, March 12, 2012

index hints on deletes

Can I not use index hints on delete statements as below ? Using SQL 2005
delete
from dbo.table1 WITH (index(idx_test))
where col1 <= 5Are you getting an error? If so it would be nice to know what.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
> Can I not use index hints on delete statements as below ? Using SQL 2005
> delete from dbo.table1 WITH (index(idx_test))
> where col1 <= 5|||Msg 1069, Level 15, State 1, Line 3
Index hints are only allowed in a FROM clause.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Are you getting an error? If so it would be nice to know what.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> Can I not use index hints on delete statements as below ? Using SQL 2005
>> delete from dbo.table1 WITH (index(idx_test))
>> where col1 <= 5
>|||How about this:
delete a
from dbo.table1 AS a WITH (index(idx_test))
where a.col1 <= 5
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> Msg 1069, Level 15, State 1, Line 3
> Index hints are only allowed in a FROM clause.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> Are you getting an error? If so it would be nice to know what.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> Can I not use index hints on delete statements as below ? Using SQL 2005
>> delete from dbo.table1 WITH (index(idx_test))
>> where col1 <= 5
>|||or just
DELETE table1 from table1 with(index(idx_test))
WHERE col <= 5
But as far as I can tell from the BOL syntax description, the original
DELETE statement is perfectly legal. Is this some kind of bug?
Linchi
"Andrew J. Kelly" wrote:
> How about this:
> delete a
> from dbo.table1 AS a WITH (index(idx_test))
> where a.col1 <= 5
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> > Msg 1069, Level 15, State 1, Line 3
> > Index hints are only allowed in a FROM clause.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> >> Are you getting an error? If so it would be nice to know what.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Hassan" <hassan@.test.com> wrote in message
> >> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
> >> Can I not use index hints on delete statements as below ? Using SQL 2005
> >>
> >> delete from dbo.table1 WITH (index(idx_test))
> >> where col1 <= 5
> >>
> >
>|||What I'm reading is that DELETE only takes hints from a limited list
<table_hint_limited> and index hints are not in that list.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3E750688-DD9F-4567-BD65-515160B60119@.microsoft.com...
> or just
> DELETE table1 from table1 with(index(idx_test))
> WHERE col <= 5
> But as far as I can tell from the BOL syntax description, the original
> DELETE statement is perfectly legal. Is this some kind of bug?
> Linchi
> "Andrew J. Kelly" wrote:
>> How about this:
>> delete a
>> from dbo.table1 AS a WITH (index(idx_test))
>> where a.col1 <= 5
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>> > Msg 1069, Level 15, State 1, Line 3
>> > Index hints are only allowed in a FROM clause.
>> >
>> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> > news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> >> Are you getting an error? If so it would be nice to know what.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "Hassan" <hassan@.test.com> wrote in message
>> >> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> >> Can I not use index hints on delete statements as below ? Using SQL
>> >> 2005
>> >>
>> >> delete from dbo.table1 WITH (index(idx_test))
>> >> where col1 <= 5
>> >>
>> >
>>

Sunday, February 19, 2012

index and query optimization plzzz

I have Table1, Table2,
Table1 Have ID(primary key),Court_ID (clustered index)..
Table2 Have Table1_ID,Rule_No(both are clustered primary key), Text (text)
Table 2 have 1,000,000 record, I use this query:
SELECT t2.[Rule_No], t2.[Text], FROM Table2 AS t2 right join Table1 AS t1
on (t1.ID = t2.[Table1_ID]) Where t1.Court_ID =1 and t2.[Text] like '%some
text%'.
I don't talk here about the (like '%%') performance..
When i look at execution plan i found it's estimated row count 1,000,000
record of table2 which i'm sure waste of time, I tried inner join also but
it's the same..
I want 'like' operator to scan only approx 86,000 record which to Court_ID =
1 not scan the whole table then filter it.
Any help plz to correct my indexes or write better query'"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:u23gign8FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I have Table1, Table2,
> Table1 Have ID(primary key),Court_ID (clustered index)..
> Table2 Have Table1_ID,Rule_No(both are clustered primary key), Text (text)
> Table 2 have 1,000,000 record, I use this query:
> SELECT t2.[Rule_No], t2.[Text], FROM Table2 AS t2 right join Table1 AS t1
> on (t1.ID = t2.[Table1_ID]) Where t1.Court_ID =1 and t2.[Text] like '%some
> text%'.
> I don't talk here about the (like '%%') performance..
> When i look at execution plan i found it's estimated row count 1,000,000
> record of table2 which i'm sure waste of time, I tried inner join also but
> it's the same..
> I want 'like' operator to scan only approx 86,000 record which to Court_ID
> = 1 not scan the whole table then filter it.
> Any help plz to correct my indexes or write better query'
Post you actual table DDL.
David|||CREATE TABLE [AH_Master] (
[ID] [PKInt] NOT NULL ,
[Ma7kama_ID] [PKInt] NOT NULL ,
[Case_No] [int] NOT NULL ,
[Case_Year] [smallint] NOT NULL ,
[Case_Date] [datetime] NOT NULL ,
[Office_Year] [smallint] NULL ,
[Office_Sufix] [char] (2) COLLATE Arabic_CI_AI_KS_WS NULL ,
[Page_No] [smallint] NULL ,
[Master_Text] [varchar] (200) COLLATE Arabic_BIN NULL ,
[IF_Agree] [smallint] NULL CONSTRAINT [DF__AH_Master__IF_Ag__79A81403]
DEFAULT (0),
[Part_No] [smallint] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_MASTER] PRIMARY KEY NONCLUSTERED
(
[ID]
) WITH FILLFACTOR = 80 ON [PRIMARY] ,
CONSTRAINT [FK_AH_MASTE_REFERENCE_AH_MA7AK] FOREIGN KEY
(
[Ma7kama_ID]
) REFERENCES [AH_Ma7akem] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [AH_SubMaster] (
[Master_ID] [int] NOT NULL ,
[Fakra_No] [smallint] NOT NULL ,
[Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
[Tasneef_ID] [PKInt] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [MyKey_PK_1] PRIMARY KEY NONCLUSTERED
(
[Master_ID],
[Fakra_No]
) WITH FILLFACTOR = 80 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
and my query is:
SELECT Sub.[Fakra_No], Sub.[Fakra_Text], Sub.[Tasneef_ID] FROM AH_SubMaster
AS Sub right join AH_Master AS MT on (Sub.Master_ID =MT.[ID]) Where
MT.Ma7kama_ID =1 and Sub.[Fakra_Text] like '%sometext%'
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23Ptp6Fq8FHA.476@.TK2MSFTNGP15.phx.gbl...
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:u23gign8FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Post you actual table DDL.
> David
>|||According to your DDL, there are no clustered indexes on your tables. Try
clustering AH_Master on Ma7kama_ID and clustering AH_SubMaster on MasterID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:uHMFKgq8FHA.2492@.TK2MSFTNGP10.phx.gbl...
> CREATE TABLE [AH_Master] (
> [ID] [PKInt] NOT NULL ,
> [Ma7kama_ID] [PKInt] NOT NULL ,
> [Case_No] [int] NOT NULL ,
> [Case_Year] [smallint] NOT NULL ,
> [Case_Date] [datetime] NOT NULL ,
> [Office_Year] [smallint] NULL ,
> [Office_Sufix] [char] (2) COLLATE Arabic_CI_AI_KS_WS NULL ,
> [Page_No] [smallint] NULL ,
> [Master_Text] [varchar] (200) COLLATE Arabic_BIN NULL ,
> [IF_Agree] [smallint] NULL CONSTRAINT [DF__AH_Master__IF_Ag__79A81403]
> DEFAULT (0),
> [Part_No] [smallint] NULL ,
> [UserID] [int] NULL ,
> [LastModify] [datetime] NULL ,
> CONSTRAINT [PK_AH_MASTER] PRIMARY KEY NONCLUSTERED
> (
> [ID]
> ) WITH FILLFACTOR = 80 ON [PRIMARY] ,
> CONSTRAINT [FK_AH_MASTE_REFERENCE_AH_MA7AK] FOREIGN KEY
> (
> [Ma7kama_ID]
> ) REFERENCES [AH_Ma7akem] (
> [ID]
> ) ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> CREATE TABLE [AH_SubMaster] (
> [Master_ID] [int] NOT NULL ,
> [Fakra_No] [smallint] NOT NULL ,
> [Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
> [Tasneef_ID] [PKInt] NULL ,
> [UserID] [int] NULL ,
> [LastModify] [datetime] NULL ,
> CONSTRAINT [MyKey_PK_1] PRIMARY KEY NONCLUSTERED
> (
> [Master_ID],
> [Fakra_No]
> ) WITH FILLFACTOR = 80 ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> and my query is:
> SELECT Sub.[Fakra_No], Sub.[Fakra_Text], Sub.[Tasneef_ID] FROM
> AH_SubMaster AS Sub right join AH_Master AS MT on (Sub.Master_ID =MT.[ID])
> Where MT.Ma7kama_ID =1 and Sub.[Fakra_Text] like '%sometext%'
>
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23Ptp6Fq8FHA.476@.TK2MSFTNGP15.phx.gbl...
>|||"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:u23gign8FHA.2364@.TK2MSFTNGP12.phx.gbl...
> I have Table1, Table2,
> Table1 Have ID(primary key),Court_ID (clustered index)..
> Table2 Have Table1_ID,Rule_No(both are clustered primary key),
Text (text)
> Table 2 have 1,000,000 record, I use this query:
> SELECT t2.[Rule_No], t2.[Text], FROM Table2 AS t2 right join
Table1 AS t1
> on (t1.ID = t2.[Table1_ID]) Where t1.Court_ID =1 and t2.[Text]
like '%some
> text%'.
> I don't talk here about the (like '%%') performance..
> When i look at execution plan i found it's estimated row count
1,000,000
> record of table2 which i'm sure waste of time, I tried inner join
also but
> it's the same..
> I want 'like' operator to scan only approx 86,000 record which to
Court_ID =
> 1 not scan the whole table then filter it.
> Any help plz to correct my indexes or write better query'
>
Isalamegy,
The predicate:
like '%some text%'
Will always cause an index or table scan as far as I know. The
potential exists for it to equal the column value of any row, and so
the column must be scanned.
Even if the column is indexed, depending on a variety of factors,
the query optimizer may decide the cost of scanning the index
reaches the point where the whole table might as well be scanned,
and so it will switch from index scan to table scan.
Sincerely,
Chris O.|||"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:uHMFKgq8FHA.2492@.TK2MSFTNGP10.phx.gbl...
<snip>

> [Ma7kama_ID] [PKInt] NOT NULL ,
<snip>

> [Tasneef_ID] [PKInt] NULL ,
BOL and Google don't seem to mention PKInt as a data type in SQL
Server.
What am I missing here?
Sincerely,
Chris O.|||There is no such datatype as PKInt. It is probably a user defined type.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:OaOdnfGosarMMxXeRVn-hA@.comcast.com...
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:uHMFKgq8FHA.2492@.TK2MSFTNGP10.phx.gbl...
> <snip>
>
> <snip>
>
>
> BOL and Google don't seem to mention PKInt as a data type in SQL
> Server.
> What am I missing here?
>
> Sincerely,
> Chris O.
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23pfZlur8FHA.1140@.tk2msftngp13.phx.gbl...
> There is no such datatype as PKInt. It is probably a user defined
type.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
Kalen Delaney,
Ah, I didn't think of that. Thank you.
Sincerely,
Chris O.|||Yes.. PKInt is a user defined datatype..
The clustered indexs that Tom suggest is exist in my tables ..
so my question. why even i narrow the search using Ma7kama_ID from table
master it still scan the whole AH_SubMaster Text column... then filter it!!
Why it don't search only in fields with the specified Ma7kama_ID'
am i have to add Ma7kama_ID field to table AH_SubMaster, I tried it and it
work as i expected but this way will duplicate ma7kama_ID in many of my
tables with text which i need to scan with the same way..
any ideas'
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:soydnYOHY8Ew1BTeRVn-vQ@.comcast.com...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23pfZlur8FHA.1140@.tk2msftngp13.phx.gbl...
> type.
> Kalen Delaney,
> Ah, I didn't think of that. Thank you.
>
> Sincerely,
> Chris O.
>|||You may want to try index hints in this case.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:eY1jNHz8FHA.2036@.TK2MSFTNGP14.phx.gbl...
Yes.. PKInt is a user defined datatype..
The clustered indexs that Tom suggest is exist in my tables ..
so my question. why even i narrow the search using Ma7kama_ID from table
master it still scan the whole AH_SubMaster Text column... then filter it!!
Why it don't search only in fields with the specified Ma7kama_ID'
am i have to add Ma7kama_ID field to table AH_SubMaster, I tried it and it
work as i expected but this way will duplicate ma7kama_ID in many of my
tables with text which i need to scan with the same way..
any ideas'
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:soydnYOHY8Ew1BTeRVn-vQ@.comcast.com...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23pfZlur8FHA.1140@.tk2msftngp13.phx.gbl...
> type.
> Kalen Delaney,
> Ah, I didn't think of that. Thank you.
>
> Sincerely,
> Chris O.
>

Wednesday, March 28, 2012

Index scan

Index scan

Monday, March 26, 2012

index question?

Index question

Index question

Friday, March 23, 2012

Index question

Wednesday, March 21, 2012

Index on query with both where clause and order by

Monday, March 12, 2012

index hints on deletes

index hints on deletes

Sunday, February 19, 2012

index and query optimization plzzz

index and key

Blog Archive

About Me