is it possible to number the records?

I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>

is it possible to number the records?

I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>

Is it possible to merge the 2 datasets into 3 dataset/into 2nd dat

Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
dataset rows into into 2nd dataset?
Actually i don't have access to opendataset and openrowset query execution
on different server. so, i am planning to create dataset for each server and
want merge the 2 dataset output into 3 rd dataset.Check out subreports. That is how this sort of thing is handled. Or you do
the merging in a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sriman" <Sriman@.discussions.microsoft.com> wrote in message
news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
> Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
> dataset rows into into 2nd dataset?
> Actually i don't have access to opendataset and openrowset query execution
> on different server. so, i am planning to create dataset for each server
> and
> want merge the 2 dataset output into 3 rd dataset.|||Hi,
We have 2 servers, one in USA and another in Canada. How can i merge
without using openrowset ... ? How can i use subreport to show the all data
into one table?
Regards,
sri.
"Bruce L-C [MVP]" wrote:
> Check out subreports. That is how this sort of thing is handled. Or you do
> the merging in a stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
> > Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
> > dataset rows into into 2nd dataset?
> > Actually i don't have access to opendataset and openrowset query execution
> > on different server. so, i am planning to create dataset for each server
> > and
> > want merge the 2 dataset output into 3 rd dataset.
>
>|||You can put a subreport into a cell of the table object.
Create your main report and test.
Create the report to be the subreport separately and test.
Drag and drop the subreport into a cell of the table control, right mouse
click and map the report parameter of the subreport to a field of the
dataset in the main report.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sriman" <Sriman@.discussions.microsoft.com> wrote in message
news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
> Hi,
> We have 2 servers, one in USA and another in Canada. How can i merge
> without using openrowset ... ? How can i use subreport to show the all
> data
> into one table?
> Regards,
> sri.
> "Bruce L-C [MVP]" wrote:
>> Check out subreports. That is how this sort of thing is handled. Or you
>> do
>> the merging in a stored procedure.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
>> > Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
>> > dataset rows into into 2nd dataset?
>> > Actually i don't have access to opendataset and openrowset query
>> > execution
>> > on different server. so, i am planning to create dataset for each
>> > server
>> > and
>> > want merge the 2 dataset output into 3 rd dataset.
>>|||Hi bruce,
thanks for the response.
i don't want to show the 2 datasets records. I have to check the 1st dataset
rows are available in 2nd dataset or not. If exists then show, otherwise
don't show . that is my task.
actually requirement is to list down the common server names from the 2
database(i.e 2 datasets).
Regards,
Sri
"Bruce L-C [MVP]" wrote:
> You can put a subreport into a cell of the table object.
> Create your main report and test.
> Create the report to be the subreport separately and test.
> Drag and drop the subreport into a cell of the table control, right mouse
> click and map the report parameter of the subreport to a field of the
> dataset in the main report.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
> > Hi,
> >
> > We have 2 servers, one in USA and another in Canada. How can i merge
> > without using openrowset ... ? How can i use subreport to show the all
> > data
> > into one table?
> >
> > Regards,
> > sri.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Check out subreports. That is how this sort of thing is handled. Or you
> >> do
> >> the merging in a stored procedure.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> >> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
> >> > Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
> >> > dataset rows into into 2nd dataset?
> >> > Actually i don't have access to opendataset and openrowset query
> >> > execution
> >> > on different server. so, i am planning to create dataset for each
> >> > server
> >> > and
> >> > want merge the 2 dataset output into 3 rd dataset.
> >>
> >>
> >>
>
>|||Ahh, this is very easy from stored procedure. Bring in the first resultset
into a temp table (use linked servers). Then join the temp table with the
table(s) for the second resultset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sriman" <Sriman@.discussions.microsoft.com> wrote in message
news:A30D9C21-F110-4DA0-BE54-A3D830805B5F@.microsoft.com...
> Hi bruce,
> thanks for the response.
> i don't want to show the 2 datasets records. I have to check the 1st
> dataset
> rows are available in 2nd dataset or not. If exists then show, otherwise
> don't show . that is my task.
> actually requirement is to list down the common server names from the 2
> database(i.e 2 datasets).
> Regards,
> Sri
> "Bruce L-C [MVP]" wrote:
>> You can put a subreport into a cell of the table object.
>> Create your main report and test.
>> Create the report to be the subreport separately and test.
>> Drag and drop the subreport into a cell of the table control, right mouse
>> click and map the report parameter of the subreport to a field of the
>> dataset in the main report.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
>> > Hi,
>> >
>> > We have 2 servers, one in USA and another in Canada. How can i merge
>> > without using openrowset ... ? How can i use subreport to show the all
>> > data
>> > into one table?
>> >
>> > Regards,
>> > sri.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Check out subreports. That is how this sort of thing is handled. Or
>> >> you
>> >> do
>> >> the merging in a stored procedure.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> >> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
>> >> > Is it possible to merge the 2 datasets into 3 dataset or merge the
>> >> > 1st
>> >> > dataset rows into into 2nd dataset?
>> >> > Actually i don't have access to opendataset and openrowset query
>> >> > execution
>> >> > on different server. so, i am planning to create dataset for each
>> >> > server
>> >> > and
>> >> > want merge the 2 dataset output into 3 rd dataset.
>> >>
>> >>
>> >>
>>|||Yea, i know. but don't have rights to create linkedserver,no permission to
create DTS.
"Bruce L-C [MVP]" wrote:
> Ahh, this is very easy from stored procedure. Bring in the first resultset
> into a temp table (use linked servers). Then join the temp table with the
> table(s) for the second resultset.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> news:A30D9C21-F110-4DA0-BE54-A3D830805B5F@.microsoft.com...
> > Hi bruce,
> > thanks for the response.
> > i don't want to show the 2 datasets records. I have to check the 1st
> > dataset
> > rows are available in 2nd dataset or not. If exists then show, otherwise
> > don't show . that is my task.
> > actually requirement is to list down the common server names from the 2
> > database(i.e 2 datasets).
> >
> > Regards,
> > Sri
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You can put a subreport into a cell of the table object.
> >> Create your main report and test.
> >> Create the report to be the subreport separately and test.
> >> Drag and drop the subreport into a cell of the table control, right mouse
> >> click and map the report parameter of the subreport to a field of the
> >> dataset in the main report.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> >> news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
> >> > Hi,
> >> >
> >> > We have 2 servers, one in USA and another in Canada. How can i merge
> >> > without using openrowset ... ? How can i use subreport to show the all
> >> > data
> >> > into one table?
> >> >
> >> > Regards,
> >> > sri.
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> Check out subreports. That is how this sort of thing is handled. Or
> >> >> you
> >> >> do
> >> >> the merging in a stored procedure.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> >> >> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
> >> >> > Is it possible to merge the 2 datasets into 3 dataset or merge the
> >> >> > 1st
> >> >> > dataset rows into into 2nd dataset?
> >> >> > Actually i don't have access to opendataset and openrowset query
> >> >> > execution
> >> >> > on different server. so, i am planning to create dataset for each
> >> >> > server
> >> >> > and
> >> >> > want merge the 2 dataset output into 3 rd dataset.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||You don't need DTS. However, you would need the DBA to create a linked
server for you and you would need rights to create a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sriman" <Sriman@.discussions.microsoft.com> wrote in message
news:868070F0-4165-4CA2-A473-23D2925B2932@.microsoft.com...
> Yea, i know. but don't have rights to create linkedserver,no permission to
> create DTS.
> "Bruce L-C [MVP]" wrote:
>> Ahh, this is very easy from stored procedure. Bring in the first
>> resultset
>> into a temp table (use linked servers). Then join the temp table with the
>> table(s) for the second resultset.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> news:A30D9C21-F110-4DA0-BE54-A3D830805B5F@.microsoft.com...
>> > Hi bruce,
>> > thanks for the response.
>> > i don't want to show the 2 datasets records. I have to check the 1st
>> > dataset
>> > rows are available in 2nd dataset or not. If exists then show,
>> > otherwise
>> > don't show . that is my task.
>> > actually requirement is to list down the common server names from the 2
>> > database(i.e 2 datasets).
>> >
>> > Regards,
>> > Sri
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> You can put a subreport into a cell of the table object.
>> >> Create your main report and test.
>> >> Create the report to be the subreport separately and test.
>> >> Drag and drop the subreport into a cell of the table control, right
>> >> mouse
>> >> click and map the report parameter of the subreport to a field of the
>> >> dataset in the main report.
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> >> news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > We have 2 servers, one in USA and another in Canada. How can i
>> >> > merge
>> >> > without using openrowset ... ? How can i use subreport to show the
>> >> > all
>> >> > data
>> >> > into one table?
>> >> >
>> >> > Regards,
>> >> > sri.
>> >> >
>> >> > "Bruce L-C [MVP]" wrote:
>> >> >
>> >> >> Check out subreports. That is how this sort of thing is handled. Or
>> >> >> you
>> >> >> do
>> >> >> the merging in a stored procedure.
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Bruce Loehle-Conger
>> >> >> MVP SQL Server Reporting Services
>> >> >>
>> >> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> >> >> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
>> >> >> > Is it possible to merge the 2 datasets into 3 dataset or merge
>> >> >> > the
>> >> >> > 1st
>> >> >> > dataset rows into into 2nd dataset?
>> >> >> > Actually i don't have access to opendataset and openrowset query
>> >> >> > execution
>> >> >> > on different server. so, i am planning to create dataset for each
>> >> >> > server
>> >> >> > and
>> >> >> > want merge the 2 dataset output into 3 rd dataset.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Hi,
I have 2 datasets.
One dataset is ProdSales
in which i use the query ="with SET ATCPRODS AS '{FILTER([PRODUCT].[ATC].[PACK].members,[PRODUCT].[ATC].currentmember.parent.name = """ + parameters!pProductName.Value + """)}' member [PRODUCT].[ATC].packname as '[PRODUCT].[ATC].currentmember.uniquename' select {[REIMBURSEMENT FLAGS].[All REIMBURSEMENT FLAGS]} on 0, crossjoin({[Measures].[Values],[Measures].[Units]},
crossjoin(ATCPRODS,[BENCHMARK].[BENCHMARK TYPE].allmembers)) on 1 from Austria WHERE ([GEOGRAPHY].[" + parameters!pShop.value + "]," + parameters!pPeriod.value + ")"
and the other dataset is ProdSalesOTC in which i use query
="with SET OTCPRODS AS '{FILTER([PRODUCT].[OTC].[PACK].members,[PRODUCT].[OTC].currentmember.parent.name = """ + parameters!pProductName.Value + """)}' member [PRODUCT].[OTC].packname as '[PRODUCT].[OTC].currentmember.uniquename' select {[REIMBURSEMENT FLAGS].[All REIMBURSEMENT FLAGS]} on 0, crossjoin({[Measures].[Values],[Measures].[Units]},
crossjoin(OTCPRODS,[BENCHMARK].[BENCHMARK TYPE].allmembers)) on 1 from Austria WHERE ([GEOGRAPHY].[" + parameters!pShop.value + "]," + parameters!pPeriod.value + ")"
How to write a new datset by merging the above dataset so that i can use the fields in the new dataset in report?

Is it possible to have more than one table footer rows ?

Well, we can add another table footer row by right-clicking on the footer row of the table and select, Inser Row Above, or, Insert Row Below.

But, I want to set different values to same properties of rows. Such as;

Row3.RepeatOnNewPage = False

Row4.RepeatOnNewPage = True

What do you say ?

-SMang the Frustrated Developer

SMang,

You can't hide your table rows independently with the RepeatOnNewPage, but you can set the rows "hidden" property to true or false. The catch here is that you will need to tie it to a toggle item.

Ham

sql

is it possible to freeze column or row in a report?

I am using sql 2000 reporting service. One of the matrix report which has so
many columns and rows and it stretches beyond the width and height of the
paper size specified and the first row and column of the matrix report
showing the description of the data like the column/row header and I wonder
if it is possible to freeze this row and column in the report so that it is
visible when scrolling down or to the right of the report. Thanks.That is possible in RS 2005, but not in RS 2000
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <paul_mak@.shaw.ca> wrote in message
news:%23O6$fs7EGHA.3700@.TK2MSFTNGP15.phx.gbl...
>I am using sql 2000 reporting service. One of the matrix report which has
>so many columns and rows and it stretches beyond the width and height of
>the paper size specified and the first row and column of the matrix report
>showing the description of the data like the column/row header and I wonder
>if it is possible to freeze this row and column in the report so that it is
>visible when scrolling down or to the right of the report. Thanks.
>

Is it possible or Is it too much ?

Hi,
This is what i want to do. Is it possible ?
I have a report1 with a textbox button - and a table with 10 rows. when the
user clicks on the textbox button - it should open another report - report2 -
IN ANOTHER BROWSER. report2 takes filter values and has a close button - when
close button is clicked it has to close and come back to REPORT1 with data
passed to table on report1 .
Is it too much ?
RPTake a look at
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=fcfb2f99-9acf-4374-b939-42811f321d66
It's not a complete solution (you can't return to you first window), I think
that is also possible because the window.open method returns a pointer to the
window. So if you could store that in a global variable of some custom code,
I presume you coukd return to the origanl window. I'm not good enough with
VB.Net to pull that off. I hope somebody can tell me how to do it.
Hope this helps
"RP" wrote:
> Hi,
> This is what i want to do. Is it possible ?
> I have a report1 with a textbox button - and a table with 10 rows. when the
> user clicks on the textbox button - it should open another report - report2 -
> IN ANOTHER BROWSER. report2 takes filter values and has a close button - when
> close button is clicked it has to close and come back to REPORT1 with data
> passed to table on report1 .
> Is it too much ?
> RP

is it possibl to avoid cursor (returned rows)

Normal set up

cursor
Select ( a bunch of crap..)

Usually I have to use the cursor to go through each record and do more crap with each record, for example I may email each user returned in the select. Is it possible to somehow use a stored procedure instead of a cursor or any other way to make this more efficient?

It's a batch job (run at night) and it'll return about 7,000 rows roughly each time so it's not terrible to the point where i need it to be super efficient, i'd just like to keep as little load on the sql server as much as possible. I assume it's one of those situations where I just can't avoid using the cursor.If your select is simple, you can define a variable to hold the table key(s) and use a while loop to walk through the table.

If your select is complex you could dump the result set of the select into a table and then use a while loop/variable to walk the table.

declare @.au_id varchar(11)
select @.au_id = min(au_id) from pubs.dbo.authors
while @.au_id is not null begin
select * from authors where au_id = @.Au_id
select @.au_id = min(au_id) from pubs.dbo.authors where au_id > @.au_id
end

granted this is simple but it ilistrattes the point

Is it ever possible that 2 different rows are inserted at same time into a table?

There is a stored procedure that inserts a row into 'Vendors' table. Is it possible that two different calls to this sp happen at the same time and as a result, each sp inserts into the table its row atexactly the same time?

How you can insert two records same time as your table will be locked while inserting data.

Is Index useful?

Hi
In a table A (currently holding approx. 4700 rows but will
expand during use) the primary key is a combination of
three columns col1, col2, col3.
The primary key is indexed with a clustered index
(FillFactor 90).
On the columns col1, col2 an nonclustred index have been
added (FillFactor 90) too, this because I guess these two
columns together form a foreign key to another table B
(currently holding approx 1000 rows - number will raise).
The two indexes are both sorted ascending for all values.
My assumption is that the nonclustered index are of no use
and only adds overhead to inserts etc., since both col1
and col2 allready are indexed (in same sequence and sort
order) in the clustered index, so a query on col1 and/or
col2, and a join between table A and B can already use the
clustered index values for the col1, col2 to speed up the
query and the join using a merge join.
Infact the DBCC SHOWSTATISTIC returns the exact same
result (density etc.) for both the clusterd and
nonclustered index (propably because the nonclustered
index after having scanned its only structure will point
to the clustered index anyway and use this afterwards - to
my recollection).
But does the extra col3 of the clustered index make it
useless for queries/order by/joins on only col1, col2?
or
Can I - with a good nights sleep in mind - just delete the
nonclustered index as it is superflous?
Kind regards
Jakob PerssonJacob
On which column(s) do you have clustered index?
What is mostly WHERE caluse in your queries?
If all three columns are participated in your query you probably want to
consider using COVERING index.
A clustered index is more useful where you try to retrive a set of data ( a
lot of rows) on other hand a non clustered index will be more useful where
you retrieve a single row.
Again it is depends upon you requriments, you will have to monitor your
query to find out an appropritate indexes
SET STATISTICS IO may be useful ,also look at excution plan of the query.
"Jakob Persson" <jakobpersson@.yahoo.dk> wrote in message
news:084601c3b8bc$173b71c0$a501280a@.phx.gbl...
> Hi
> In a table A (currently holding approx. 4700 rows but will
> expand during use) the primary key is a combination of
> three columns col1, col2, col3.
> The primary key is indexed with a clustered index
> (FillFactor 90).
> On the columns col1, col2 an nonclustred index have been
> added (FillFactor 90) too, this because I guess these two
> columns together form a foreign key to another table B
> (currently holding approx 1000 rows - number will raise).
> The two indexes are both sorted ascending for all values.
> My assumption is that the nonclustered index are of no use
> and only adds overhead to inserts etc., since both col1
> and col2 allready are indexed (in same sequence and sort
> order) in the clustered index, so a query on col1 and/or
> col2, and a join between table A and B can already use the
> clustered index values for the col1, col2 to speed up the
> query and the join using a merge join.
> Infact the DBCC SHOWSTATISTIC returns the exact same
> result (density etc.) for both the clusterd and
> nonclustered index (propably because the nonclustered
> index after having scanned its only structure will point
> to the clustered index anyway and use this afterwards - to
> my recollection).
> But does the extra col3 of the clustered index make it
> useless for queries/order by/joins on only col1, col2?
> or
> Can I - with a good nights sleep in mind - just delete the
> nonclustered index as it is superflous?
> Kind regards
> Jakob Persson
>|||Jakob,
>But does the extra col3 of the clustered index make it
>useless for queries/order by/joins on only col1, col2?
No. The index with col1, col2, col3 is just as useful as the one with col1, col2. So your thinking
is spot on. One thing is of course if col1 and col2 are very narrow and col3 is very wide, but that
is obvious.
Another, more important issue, is that the index on col1, col2 is an NC index which has other
characteristics than a clustered index. The optimizer might use this index to cover queries (or
partially cover), which can have significant performance gains compared to a clustered index.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jakob Persson" <jakobpersson@.yahoo.dk> wrote in message
news:084601c3b8bc$173b71c0$a501280a@.phx.gbl...
> Hi
> In a table A (currently holding approx. 4700 rows but will
> expand during use) the primary key is a combination of
> three columns col1, col2, col3.
> The primary key is indexed with a clustered index
> (FillFactor 90).
> On the columns col1, col2 an nonclustred index have been
> added (FillFactor 90) too, this because I guess these two
> columns together form a foreign key to another table B
> (currently holding approx 1000 rows - number will raise).
> The two indexes are both sorted ascending for all values.
> My assumption is that the nonclustered index are of no use
> and only adds overhead to inserts etc., since both col1
> and col2 allready are indexed (in same sequence and sort
> order) in the clustered index, so a query on col1 and/or
> col2, and a join between table A and B can already use the
> clustered index values for the col1, col2 to speed up the
> query and the join using a merge join.
> Infact the DBCC SHOWSTATISTIC returns the exact same
> result (density etc.) for both the clusterd and
> nonclustered index (propably because the nonclustered
> index after having scanned its only structure will point
> to the clustered index anyway and use this afterwards - to
> my recollection).
> But does the extra col3 of the clustered index make it
> useless for queries/order by/joins on only col1, col2?
> or
> Can I - with a good nights sleep in mind - just delete the
> nonclustered index as it is superflous?
> Kind regards
> Jakob Persson
>