hi,
do i have access to subreports '
for example: how many rows were created?
a have a tablerow with a sub report.
i want to set the visibility to hidden, if no rows were created in the
subreport...
regards
PatrickThe parent report doesn't have access to properties of the subreport.
You may want to consider setting the NoRows property on the subreport
control in the master report.
Or make the subreport control in the master report very small and then have
the subreport hide its own contents if there are no rows of data.
As a last resort, you could put a query in the master report that returns a
count of the rows of the subreport (or if you're calling the subreport
inside of a list, you would include the count as a column in your master
query) and then base the visibility on that count.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Patrick Ruhnow" <pruhnow@.dornbracht.de> wrote in message
news:uOSD0kAaEHA.556@.tk2msftngp13.phx.gbl...
> hi,
> do i have access to subreports '
> for example: how many rows were created?
> a have a tablerow with a sub report.
> i want to set the visibility to hidden, if no rows were created in the
> subreport...
>
> regards
> Patrick
>
Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts
Thursday, March 29, 2012
Sunday, February 26, 2012
Eliminating the expensive remote join
I've got a table (dexIndex) with 9 million rows in it. It's got an
identity column (index_id) and a column that's full-text indexed
(insureds_name) and a column that I want to filter on (agency_number).
When I query like this:
select * from decIndex where agency_number = '0349323' and
contains(insureds_name,'"SMITH"')
The execution plan involves a remote scan (98%) and a clustered index
seek (0%) which lead into a hash match/inner join (2%). The problem is
that the remote scan is returning 51,000 rows even though only 1 comes
out of the hash match/inner join. Thus, the query takes over a minute
on a built-to-the-hilt server.
Is there a way to have the FT engine only look through or return a
subset of the FT index, like only those rows which correspond to
agency_number = '0349323'?
I'd like to avoid adding agency_number to the FT index.
Thanks!
Regretably there is no way of doing this. One of the strategies of getting
around this problem is to partition your table according to the other
elements of you where clause. Another option is to build full text indexes
on indexed views in SQL 2005. Again the view would be of a section of your
table which would match your where conditions.
"Ryan Walberg [MCSD]" <generalpf@.nospam.yahoo.reallynospam.com> wrote in
message news:%233kbwJZJFHA.588@.TK2MSFTNGP15.phx.gbl...
> I've got a table (dexIndex) with 9 million rows in it. It's got an
> identity column (index_id) and a column that's full-text indexed
> (insureds_name) and a column that I want to filter on (agency_number).
> When I query like this:
> select * from decIndex where agency_number = '0349323' and
> contains(insureds_name,'"SMITH"')
> The execution plan involves a remote scan (98%) and a clustered index seek
> (0%) which lead into a hash match/inner join (2%). The problem is that
> the remote scan is returning 51,000 rows even though only 1 comes out of
> the hash match/inner join. Thus, the query takes over a minute on a
> built-to-the-hilt server.
> Is there a way to have the FT engine only look through or return a subset
> of the FT index, like only those rows which correspond to agency_number =
> '0349323'?
> I'd like to avoid adding agency_number to the FT index.
> Thanks!
|||Hilary Cotter wrote:
> Regretably there is no way of doing this. One of the strategies of getting
> around this problem is to partition your table according to the other
> elements of you where clause. Another option is to build full text indexes
> on indexed views in SQL 2005. Again the view would be of a section of your
> table which would match your where conditions.
I ended up clustering the index that corresponded to the unique column
for the full-text index and that sped it up a great deal.
Thanks,
Ryan
identity column (index_id) and a column that's full-text indexed
(insureds_name) and a column that I want to filter on (agency_number).
When I query like this:
select * from decIndex where agency_number = '0349323' and
contains(insureds_name,'"SMITH"')
The execution plan involves a remote scan (98%) and a clustered index
seek (0%) which lead into a hash match/inner join (2%). The problem is
that the remote scan is returning 51,000 rows even though only 1 comes
out of the hash match/inner join. Thus, the query takes over a minute
on a built-to-the-hilt server.
Is there a way to have the FT engine only look through or return a
subset of the FT index, like only those rows which correspond to
agency_number = '0349323'?
I'd like to avoid adding agency_number to the FT index.
Thanks!
Regretably there is no way of doing this. One of the strategies of getting
around this problem is to partition your table according to the other
elements of you where clause. Another option is to build full text indexes
on indexed views in SQL 2005. Again the view would be of a section of your
table which would match your where conditions.
"Ryan Walberg [MCSD]" <generalpf@.nospam.yahoo.reallynospam.com> wrote in
message news:%233kbwJZJFHA.588@.TK2MSFTNGP15.phx.gbl...
> I've got a table (dexIndex) with 9 million rows in it. It's got an
> identity column (index_id) and a column that's full-text indexed
> (insureds_name) and a column that I want to filter on (agency_number).
> When I query like this:
> select * from decIndex where agency_number = '0349323' and
> contains(insureds_name,'"SMITH"')
> The execution plan involves a remote scan (98%) and a clustered index seek
> (0%) which lead into a hash match/inner join (2%). The problem is that
> the remote scan is returning 51,000 rows even though only 1 comes out of
> the hash match/inner join. Thus, the query takes over a minute on a
> built-to-the-hilt server.
> Is there a way to have the FT engine only look through or return a subset
> of the FT index, like only those rows which correspond to agency_number =
> '0349323'?
> I'd like to avoid adding agency_number to the FT index.
> Thanks!
|||Hilary Cotter wrote:
> Regretably there is no way of doing this. One of the strategies of getting
> around this problem is to partition your table according to the other
> elements of you where clause. Another option is to build full text indexes
> on indexed views in SQL 2005. Again the view would be of a section of your
> table which would match your where conditions.
I ended up clustering the index that corresponded to the unique column
for the full-text index and that sped it up a great deal.
Thanks,
Ryan
Eliminating rows from select results
Hi,
is there any way to eliminate a row from the results returned from a select statement?
I'm using some aggregate functions and doing some division and occassionally I get a divide by zero error. Is there a way I can leave the row out if the divisor is zero? Briefly, my query looks like this:
select sum(a*b)/sum(b), c
from TableA
group by c
If sum(b) is zero I want to leave that row out.
Thanks for your help!
WallaceDoes your database engine supporr the HAVING clause?
-PatP|||This is what HAVING is doing essentially:
SELECT sum_a / sum_b, c
FROM ( SELECT SUM(a*b), SUM(b), c
FROM tableA
GROUP BY c ) AS t(sum_a, sum_b, c)
WHERE sum_b <> 0|||Having should do the trick.
Thanks for your help!|||This is what HAVING is doing essentially:
SELECT sum_a / sum_b, c
FROM ( SELECT SUM(a*b), SUM(b), c
FROM tableA
GROUP BY c ) AS t(sum_a, sum_b, c)
WHERE sum_b <> 0
It is answers like the one you provided above that encourage poor software development, in this instance, database development.
There is no need to create an inline view and filter the result set by applying a where clause to the outer query, when in this instance, the exact same result can be produced by using the Having clause.
Using the having clause is the preferred method for applying criteria to aggregate results, it was designed for this exact purpose. Although the where clause can be used in certain situations to filter aggregates, it was not designed so.
To follow your example, perhaps when I provide examples I should replace all column names with some arbitrary name in an attempt to make the solution appear complex and worthy of admiration, when in fact these types of responses would only cause unnecessary confusion to the reader.|||robert, take it easy, he was showing what the HAVING clause does, not suggesting that you avoid using it|||I do apologise if my response appears rude and overtly frank. However, I consider it to be totally inappropriate to hijack a thread with the intention to be a "clever dick" and publish a random solution with complete disregard for the original question.
stolze posted an unnecessary solution to a simple question, which could have easily been answered using a HAVING clause, but did not consider it proper to provide in addition, a solution using the Having clause.
Stolze: The poster was not asking for ways to replace the functionality of the Having clause, but instead the question asked how to remove rows from a set, and in this particular instance, the condition was to be applied after the aggregation. In future, perhaps you could assist the poster with their question before trying to prove a point with respect to your technical ability.
You wouldn't speak to your fellow colleagues in such a patronising tone, so why do it here?|||You wouldn't speak to your fellow colleagues in such a patronising tone, so why do it here?so why are you doing it?|||My comments were not said with a patronising tone.|||perhaps you did not intend them to be, but that is how they came across|||I apologise to all those who may find my comments in this thread rude and patronising. My intent was just to point out that if a simple and easy to understand solution exists, then it should be provided first, before suggesting other less obvious methods.
That's all.|||select sum(a*b)/sum(b), c
from TableA
group by c
If sum(b) is zero I want to leave that row out.
As already suggested, the following is indeed what you need:SELECT sum(a*b)/sum(b), c
FROM TableA
GROUP BY c
HAVING sum(b) <> 0
Note that this also avoids zero division, i.e., the expression sum(a*b)/sum(b) is never executed when sum(b) is 0.|||I do apologise if my response appears rude and overtly frank. However, I consider it to be totally inappropriate to hijack a thread with the intention to be a "clever dick" and publish a random solution with complete disregard for the original question.
stolze posted an unnecessary solution to a simple question, which could have easily been answered using a HAVING clause, but did not consider it proper to provide in addition, a solution using the Having clause.
I just gave an explanation on how HAVING works. That's all...
Stolze: The poster was not asking for ways to replace the functionality of the Having clause, but instead the question asked how to remove rows from a set, and in this particular instance, the condition was to be applied after the aggregation. In future, perhaps you could assist the poster with their question before trying to prove a point with respect to your technical ability.
I have no idea why you are reacting so aggressively.
The OP apparently didn't know about the existence of HAVING and what it does. While the answer given by Pat was correct, I felt it is a good idea to provide more background information.
I had the chance to give some database courses at universities in the past. One thing I learned there is that students (they being university students or professionals doesn't matter in this respect) first have to learn basic concepts. Later you can use those basic concepts to explain other things. For example, once someone understands sub-selects, he/she will grasp the idea of having-clauses right away if you can show such a reformulated query. And that was my intention here as well. (I don't know where this was "patronizing".)
Also, I believe it is essential that people working with an RDBMS understand what is going on internally in order to avoid bad queries or to know what can be done for efficiently and what may not be such a great idea. For example, just look at the comments here: http://us2.php.net/odbc_num_rows All those solutions show a distinct lack of understanding of relational database systems. What's my point? It is that a good idea would be that people implement their own simple DBMS because it helps tremendously to understand how a DBMS works internally or how queries can be rephrased. A simple approach to deal with HAVING is to internally rewrite a query to the construct I posted - without loosing any functionality. (At the end of the day, the HAVING clause is very helpful but it just remains a bit syntactic sugar in SQL.) However, I'm fully aware that not everyone has the time or access to the necessary expertise to implement their own small DBMS...
I suggest that you read a few more threads in this forum and other newsgroup. You will find that questions range from very basic stuff on transactions, the relational model, etc. to the meaning of specific options or error messages and their possible causes. I found that additional explanations are a good way to answer question to the extent that the poster knows how to proceed.|||I just gave an explanation on how HAVING works. That's all...
A very handy explanation... I knew how HAVING works, and I'm sure if someone had asked me I could have shown how it's non-primitive, but I never actually broke it down like that.
I have no idea why you are reacting so aggressively.
Even if you could figure out why people say what they do, who cares? It's just words.
is there any way to eliminate a row from the results returned from a select statement?
I'm using some aggregate functions and doing some division and occassionally I get a divide by zero error. Is there a way I can leave the row out if the divisor is zero? Briefly, my query looks like this:
select sum(a*b)/sum(b), c
from TableA
group by c
If sum(b) is zero I want to leave that row out.
Thanks for your help!
WallaceDoes your database engine supporr the HAVING clause?
-PatP|||This is what HAVING is doing essentially:
SELECT sum_a / sum_b, c
FROM ( SELECT SUM(a*b), SUM(b), c
FROM tableA
GROUP BY c ) AS t(sum_a, sum_b, c)
WHERE sum_b <> 0|||Having should do the trick.
Thanks for your help!|||This is what HAVING is doing essentially:
SELECT sum_a / sum_b, c
FROM ( SELECT SUM(a*b), SUM(b), c
FROM tableA
GROUP BY c ) AS t(sum_a, sum_b, c)
WHERE sum_b <> 0
It is answers like the one you provided above that encourage poor software development, in this instance, database development.
There is no need to create an inline view and filter the result set by applying a where clause to the outer query, when in this instance, the exact same result can be produced by using the Having clause.
Using the having clause is the preferred method for applying criteria to aggregate results, it was designed for this exact purpose. Although the where clause can be used in certain situations to filter aggregates, it was not designed so.
To follow your example, perhaps when I provide examples I should replace all column names with some arbitrary name in an attempt to make the solution appear complex and worthy of admiration, when in fact these types of responses would only cause unnecessary confusion to the reader.|||robert, take it easy, he was showing what the HAVING clause does, not suggesting that you avoid using it|||I do apologise if my response appears rude and overtly frank. However, I consider it to be totally inappropriate to hijack a thread with the intention to be a "clever dick" and publish a random solution with complete disregard for the original question.
stolze posted an unnecessary solution to a simple question, which could have easily been answered using a HAVING clause, but did not consider it proper to provide in addition, a solution using the Having clause.
Stolze: The poster was not asking for ways to replace the functionality of the Having clause, but instead the question asked how to remove rows from a set, and in this particular instance, the condition was to be applied after the aggregation. In future, perhaps you could assist the poster with their question before trying to prove a point with respect to your technical ability.
You wouldn't speak to your fellow colleagues in such a patronising tone, so why do it here?|||You wouldn't speak to your fellow colleagues in such a patronising tone, so why do it here?so why are you doing it?|||My comments were not said with a patronising tone.|||perhaps you did not intend them to be, but that is how they came across|||I apologise to all those who may find my comments in this thread rude and patronising. My intent was just to point out that if a simple and easy to understand solution exists, then it should be provided first, before suggesting other less obvious methods.
That's all.|||select sum(a*b)/sum(b), c
from TableA
group by c
If sum(b) is zero I want to leave that row out.
As already suggested, the following is indeed what you need:SELECT sum(a*b)/sum(b), c
FROM TableA
GROUP BY c
HAVING sum(b) <> 0
Note that this also avoids zero division, i.e., the expression sum(a*b)/sum(b) is never executed when sum(b) is 0.|||I do apologise if my response appears rude and overtly frank. However, I consider it to be totally inappropriate to hijack a thread with the intention to be a "clever dick" and publish a random solution with complete disregard for the original question.
stolze posted an unnecessary solution to a simple question, which could have easily been answered using a HAVING clause, but did not consider it proper to provide in addition, a solution using the Having clause.
I just gave an explanation on how HAVING works. That's all...
Stolze: The poster was not asking for ways to replace the functionality of the Having clause, but instead the question asked how to remove rows from a set, and in this particular instance, the condition was to be applied after the aggregation. In future, perhaps you could assist the poster with their question before trying to prove a point with respect to your technical ability.
I have no idea why you are reacting so aggressively.
The OP apparently didn't know about the existence of HAVING and what it does. While the answer given by Pat was correct, I felt it is a good idea to provide more background information.
I had the chance to give some database courses at universities in the past. One thing I learned there is that students (they being university students or professionals doesn't matter in this respect) first have to learn basic concepts. Later you can use those basic concepts to explain other things. For example, once someone understands sub-selects, he/she will grasp the idea of having-clauses right away if you can show such a reformulated query. And that was my intention here as well. (I don't know where this was "patronizing".)
Also, I believe it is essential that people working with an RDBMS understand what is going on internally in order to avoid bad queries or to know what can be done for efficiently and what may not be such a great idea. For example, just look at the comments here: http://us2.php.net/odbc_num_rows All those solutions show a distinct lack of understanding of relational database systems. What's my point? It is that a good idea would be that people implement their own simple DBMS because it helps tremendously to understand how a DBMS works internally or how queries can be rephrased. A simple approach to deal with HAVING is to internally rewrite a query to the construct I posted - without loosing any functionality. (At the end of the day, the HAVING clause is very helpful but it just remains a bit syntactic sugar in SQL.) However, I'm fully aware that not everyone has the time or access to the necessary expertise to implement their own small DBMS...
I suggest that you read a few more threads in this forum and other newsgroup. You will find that questions range from very basic stuff on transactions, the relational model, etc. to the meaning of specific options or error messages and their possible causes. I found that additional explanations are a good way to answer question to the extent that the poster knows how to proceed.|||I just gave an explanation on how HAVING works. That's all...
A very handy explanation... I knew how HAVING works, and I'm sure if someone had asked me I could have shown how it's non-primitive, but I never actually broke it down like that.
I have no idea why you are reacting so aggressively.
Even if you could figure out why people say what they do, who cares? It's just words.
Eliminating Duplicates
I have a table with 580 rows of duplicate on total of 1101 rows. The
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/defaul...kb;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/defaul...kb;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.
Eliminating Duplicates
I have a table with 580 rows of duplicate on total of 1101 rows. The
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.
Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/default...b;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.
Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/default...b;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.
Eliminating Duplicates
I have a table with a field called PID. This field can have duplicate
values in it. I have 2 other fields called CAU and SUF. I may have 3 rows
with the same PID and same CAU, but the SUF field when concatenated with the
2 aforementioned fields make the row unique.
I want to know how to select 1 of the 3 rows but not the other 2. In
otherwords I need 1 row for each SUF. Hopes this makes sense and I would
appreciate any help.Try the following query. Work from the inner select out.
CREATE TABLE OneSUF
(
PID INT NOT NULL,
CAU INT NOT NULL,
SUF INT NOT NULL,
Description VARCHAR(255) NOT NULL
)
INSERT INTO OneSUF VALUES (1, 2, 3, '1, 2, 3')
INSERT INTO OneSUF VALUES (1, 2, 4, '1, 2, 4')
INSERT INTO OneSUF VALUES (1, 2, 5, '1, 2, 5')
INSERT INTO OneSUF VALUES (1, 3, 1, '1, 3, 1')
INSERT INTO OneSUF VALUES (1, 3, 2, '1, 3, 2')
INSERT INTO OneSUF VALUES (1, 3, 3, '1, 3, 3')
INSERT INTO OneSUF VALUES (1, 4, 3, '1, 4, 3')
INSERT INTO OneSUF VALUES (1, 4, 4, '1, 4, 4')
INSERT INTO OneSUF VALUES (1, 4, 5, '1, 4, 5')
INSERT INTO OneSUF VALUES (1, 4, 6, '1, 4, 6')
SELECT SUF3.SUF, SUF3.CAU, SUF3.PID, SUF3.Description
FROM OneSUF AS SUF3 INNER JOIN
(
SELECT SUF1.SUF, SUF1.CAU, MIN(SUF1.PID) AS PID FROM OneSUF AS SUF1 INNER
JOIN
(SELECT SUF, MIN(CAU) AS CAU FROM OneSUF GROUP BY SUF) AS SUF2
ON SUF1.SUF = SUF2.SUF AND SUF1.CAU = SUF2.CAU
GROUP BY SUF1.SUF, SUF1.CAU) AS SUF4
ON SUF3.SUF = SUF4.SUF AND SUF3.CAU = SUF4.CAU AND SUF3.PID = SUF4.PID
ORDER BY 1,2,3
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Jeff Humphrey" <jeffhumphrey@.cox-internet.com> wrote in message
news:%23OzmAYOmDHA.2416@.TK2MSFTNGP10.phx.gbl...
> I have a table with a field called PID. This field can have duplicate
> values in it. I have 2 other fields called CAU and SUF. I may have 3
rows
> with the same PID and same CAU, but the SUF field when concatenated with
the
> 2 aforementioned fields make the row unique.
> I want to know how to select 1 of the 3 rows but not the other 2. In
> otherwords I need 1 row for each SUF. Hopes this makes sense and I would
> appreciate any help.
>
values in it. I have 2 other fields called CAU and SUF. I may have 3 rows
with the same PID and same CAU, but the SUF field when concatenated with the
2 aforementioned fields make the row unique.
I want to know how to select 1 of the 3 rows but not the other 2. In
otherwords I need 1 row for each SUF. Hopes this makes sense and I would
appreciate any help.Try the following query. Work from the inner select out.
CREATE TABLE OneSUF
(
PID INT NOT NULL,
CAU INT NOT NULL,
SUF INT NOT NULL,
Description VARCHAR(255) NOT NULL
)
INSERT INTO OneSUF VALUES (1, 2, 3, '1, 2, 3')
INSERT INTO OneSUF VALUES (1, 2, 4, '1, 2, 4')
INSERT INTO OneSUF VALUES (1, 2, 5, '1, 2, 5')
INSERT INTO OneSUF VALUES (1, 3, 1, '1, 3, 1')
INSERT INTO OneSUF VALUES (1, 3, 2, '1, 3, 2')
INSERT INTO OneSUF VALUES (1, 3, 3, '1, 3, 3')
INSERT INTO OneSUF VALUES (1, 4, 3, '1, 4, 3')
INSERT INTO OneSUF VALUES (1, 4, 4, '1, 4, 4')
INSERT INTO OneSUF VALUES (1, 4, 5, '1, 4, 5')
INSERT INTO OneSUF VALUES (1, 4, 6, '1, 4, 6')
SELECT SUF3.SUF, SUF3.CAU, SUF3.PID, SUF3.Description
FROM OneSUF AS SUF3 INNER JOIN
(
SELECT SUF1.SUF, SUF1.CAU, MIN(SUF1.PID) AS PID FROM OneSUF AS SUF1 INNER
JOIN
(SELECT SUF, MIN(CAU) AS CAU FROM OneSUF GROUP BY SUF) AS SUF2
ON SUF1.SUF = SUF2.SUF AND SUF1.CAU = SUF2.CAU
GROUP BY SUF1.SUF, SUF1.CAU) AS SUF4
ON SUF3.SUF = SUF4.SUF AND SUF3.CAU = SUF4.CAU AND SUF3.PID = SUF4.PID
ORDER BY 1,2,3
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Jeff Humphrey" <jeffhumphrey@.cox-internet.com> wrote in message
news:%23OzmAYOmDHA.2416@.TK2MSFTNGP10.phx.gbl...
> I have a table with a field called PID. This field can have duplicate
> values in it. I have 2 other fields called CAU and SUF. I may have 3
rows
> with the same PID and same CAU, but the SUF field when concatenated with
the
> 2 aforementioned fields make the row unique.
> I want to know how to select 1 of the 3 rows but not the other 2. In
> otherwords I need 1 row for each SUF. Hopes this makes sense and I would
> appreciate any help.
>
Eliminating Duplicates
I have a table with 580 rows of duplicate on total of 1101 rows. The
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.
Friday, February 24, 2012
Eliminate Rows with Redundant Columns
I would like my query to return the KeyID from row 4 but from only one of
the first 3 rows where the address data is redundant. Since use of the key
eliminates the DISTINCT operator, is there another method?
DECLARE @.tAddress TABLE (KeyID int, Address varchar(10), City varchar(10),
ST varchar(2))
INSERT @.tAddress
SELECT 1, '100 Main', 'Boston', 'MA'
UNION ALL
SELECT 2, '100 Main', 'Boston', 'MA'
UNION ALL
SELECT 3, '100 Main', 'Boston', 'MA'
UNION ALL
SELECT 4, '200 Main', 'Boston', 'MA'
Thanks!SELECT Address, City, ST, MIN(KeyID)
FROM @.tAddress
GROUP BY Address, City, ST
Or, if you really only wanted the ID column:
SELECT MIN(KeyID)
FROM @.tAddress
GROUP BY Address, City, ST
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 09:47:32 -0700, "Mike Harbinger"
<MikeH@.Cybervillage.net> wrote:
>I would like my query to return the KeyID from row 4 but from only one of
>the first 3 rows where the address data is redundant. Since use of the key
>eliminates the DISTINCT operator, is there another method?
>DECLARE @.tAddress TABLE (KeyID int, Address varchar(10), City varchar(10),
>ST varchar(2))
>INSERT @.tAddress
>SELECT 1, '100 Main', 'Boston', 'MA'
>UNION ALL
>SELECT 2, '100 Main', 'Boston', 'MA'
>UNION ALL
>SELECT 3, '100 Main', 'Boston', 'MA'
>UNION ALL
>SELECT 4, '200 Main', 'Boston', 'MA'
>Thanks!
>|||Simple and elegant; I should have seen that. Many thanks Roy!
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:csf092h8em6sabqjre5jf7jsbmrhroovtc@.
4ax.com...
> SELECT Address, City, ST, MIN(KeyID)
> FROM @.tAddress
> GROUP BY Address, City, ST
> Or, if you really only wanted the ID column:
> SELECT MIN(KeyID)
> FROM @.tAddress
> GROUP BY Address, City, ST
> Roy Harvey
> Beacon Falls, CT
> On Wed, 14 Jun 2006 09:47:32 -0700, "Mike Harbinger"
> <MikeH@.Cybervillage.net> wrote:
>
the first 3 rows where the address data is redundant. Since use of the key
eliminates the DISTINCT operator, is there another method?
DECLARE @.tAddress TABLE (KeyID int, Address varchar(10), City varchar(10),
ST varchar(2))
INSERT @.tAddress
SELECT 1, '100 Main', 'Boston', 'MA'
UNION ALL
SELECT 2, '100 Main', 'Boston', 'MA'
UNION ALL
SELECT 3, '100 Main', 'Boston', 'MA'
UNION ALL
SELECT 4, '200 Main', 'Boston', 'MA'
Thanks!SELECT Address, City, ST, MIN(KeyID)
FROM @.tAddress
GROUP BY Address, City, ST
Or, if you really only wanted the ID column:
SELECT MIN(KeyID)
FROM @.tAddress
GROUP BY Address, City, ST
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 09:47:32 -0700, "Mike Harbinger"
<MikeH@.Cybervillage.net> wrote:
>I would like my query to return the KeyID from row 4 but from only one of
>the first 3 rows where the address data is redundant. Since use of the key
>eliminates the DISTINCT operator, is there another method?
>DECLARE @.tAddress TABLE (KeyID int, Address varchar(10), City varchar(10),
>ST varchar(2))
>INSERT @.tAddress
>SELECT 1, '100 Main', 'Boston', 'MA'
>UNION ALL
>SELECT 2, '100 Main', 'Boston', 'MA'
>UNION ALL
>SELECT 3, '100 Main', 'Boston', 'MA'
>UNION ALL
>SELECT 4, '200 Main', 'Boston', 'MA'
>Thanks!
>|||Simple and elegant; I should have seen that. Many thanks Roy!
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:csf092h8em6sabqjre5jf7jsbmrhroovtc@.
4ax.com...
> SELECT Address, City, ST, MIN(KeyID)
> FROM @.tAddress
> GROUP BY Address, City, ST
> Or, if you really only wanted the ID column:
> SELECT MIN(KeyID)
> FROM @.tAddress
> GROUP BY Address, City, ST
> Roy Harvey
> Beacon Falls, CT
> On Wed, 14 Jun 2006 09:47:32 -0700, "Mike Harbinger"
> <MikeH@.Cybervillage.net> wrote:
>
Sunday, February 19, 2012
efficiently creating random numbers in very large table
Hello,
I need to sample data in a very large table in SQL Server 2000 (a gazillion rows of Performance Monitor statitics).
I'd like to take the top 5%, for instance, based upon a column containing random numbers.
Can anyone suggest a highly efficient method of populating a column with random numbers.
Thanks in advance.
Rodselect TOP 5 PERCENT * from [YourTable] order by newid()|||select TOP 5 PERCENT * from [YourTable] order by newid()
Thank you, I'll give that a go.
Regards,
Rod|||that won't populate your table with any random numbers obviously.
it will give you a random 5% slice of the table. a different slice each time you run it.|||Thanks, Good point; maybe I can have another column to set a bit , so that I can reproduce. I'll have to test performance, perhaps someone has some experience with this or have a different technique to propose. Thank you.
Rod|||If you really want a column of random values, then just create a GUID column with a default of NEWID(). But this won't give you a random sample every time, of course.|||If you really want a column of random values, then just create a GUID column with a default of NEWID(). But this won't give you a random sample every time, of course.
That's ok blindman, I just neede something that's efficient in terms populating random values. Regards, Rod|||just create a GUID column with a default of NEWID()
Ofcourse this works but if your table is really that big beware of the time it takes to alter the table! SQL Server has to expand each record so numerous page splits will occur, indexes will have to be rebuild, etc, etc. This could take a couple of hours.|||Ofcourse this works but if your table is really that big beware of the time it takes to alter the table! SQL Server has to expand each record so numerous page splits will occur, indexes will have to be rebuild, etc, etc. This could take a couple of hours.
...ugh.. Thanks. There does not seem to be a really efficient way of doing this...
Thanks for you input. Rod|||how many rows is the table?
also, you can generate random numbers in sql using rand() if you don't like guids. if a random number from 0-255 is sufficient you could store it in a tinyint and less page splits would result.
this code ran in 31 sec on my dev box. not great, but it is what it is:
set nocount on
declare @.t table (RandomColumn tinyint)
declare @.i int
set @.i=0
while @.i < 1000000
begin
insert into @.t select round(rand() * 255, 0)
set @.i = @.i + 1
end|||how many rows is the table?
also, you can generate random numbers in sql using rand() if you don't like guids. if a random number from 0-255 is sufficient you could store it in a tinyint and less page splits would result.
this code ran in 31 sec on my dev box. not great, but it is what it is:
set nocount on
declare @.t table (RandomColumn tinyint)
declare @.i int
set @.i=0
while @.i < 1000000
begin
insert into @.t select round(rand() * 255, 0)
set @.i = @.i + 1
end
That maybe ok, you're right, not great but maybe we can live that. Thanks for your code.
Regards,
Rod
I need to sample data in a very large table in SQL Server 2000 (a gazillion rows of Performance Monitor statitics).
I'd like to take the top 5%, for instance, based upon a column containing random numbers.
Can anyone suggest a highly efficient method of populating a column with random numbers.
Thanks in advance.
Rodselect TOP 5 PERCENT * from [YourTable] order by newid()|||select TOP 5 PERCENT * from [YourTable] order by newid()
Thank you, I'll give that a go.
Regards,
Rod|||that won't populate your table with any random numbers obviously.
it will give you a random 5% slice of the table. a different slice each time you run it.|||Thanks, Good point; maybe I can have another column to set a bit , so that I can reproduce. I'll have to test performance, perhaps someone has some experience with this or have a different technique to propose. Thank you.
Rod|||If you really want a column of random values, then just create a GUID column with a default of NEWID(). But this won't give you a random sample every time, of course.|||If you really want a column of random values, then just create a GUID column with a default of NEWID(). But this won't give you a random sample every time, of course.
That's ok blindman, I just neede something that's efficient in terms populating random values. Regards, Rod|||just create a GUID column with a default of NEWID()
Ofcourse this works but if your table is really that big beware of the time it takes to alter the table! SQL Server has to expand each record so numerous page splits will occur, indexes will have to be rebuild, etc, etc. This could take a couple of hours.|||Ofcourse this works but if your table is really that big beware of the time it takes to alter the table! SQL Server has to expand each record so numerous page splits will occur, indexes will have to be rebuild, etc, etc. This could take a couple of hours.
...ugh.. Thanks. There does not seem to be a really efficient way of doing this...
Thanks for you input. Rod|||how many rows is the table?
also, you can generate random numbers in sql using rand() if you don't like guids. if a random number from 0-255 is sufficient you could store it in a tinyint and less page splits would result.
this code ran in 31 sec on my dev box. not great, but it is what it is:
set nocount on
declare @.t table (RandomColumn tinyint)
declare @.i int
set @.i=0
while @.i < 1000000
begin
insert into @.t select round(rand() * 255, 0)
set @.i = @.i + 1
end|||how many rows is the table?
also, you can generate random numbers in sql using rand() if you don't like guids. if a random number from 0-255 is sufficient you could store it in a tinyint and less page splits would result.
this code ran in 31 sec on my dev box. not great, but it is what it is:
set nocount on
declare @.t table (RandomColumn tinyint)
declare @.i int
set @.i=0
while @.i < 1000000
begin
insert into @.t select round(rand() * 255, 0)
set @.i = @.i + 1
end
That maybe ok, you're right, not great but maybe we can live that. Thanks for your code.
Regards,
Rod
Friday, February 17, 2012
Efficiency of INSERT with multiple rows
Let's say that I am inserting rows into a table via a statement like this
INSERT INTO A SELECT * FROM B
And let's say that table B has 1,000,000 rows
I was just wondering if SQL Server rebuilds the indexes on table A after
each row is inserted or if it waits until all 1,000,000 rows are inserted
and then rebuilds the indexes at the end? If it rebuilds the indexes upon
each insert then I would probably drop the indexes first and then just add
them at the end.
Does anybody know?
Thanks
Richard Speiss"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
THe index is not 'rebuild' upon each insert. It is updated on each insert.
Since 'table splits' on indexes can be costly, it is sometimes, a good idea
to remove the index.
But that depends, if you have a very large table with bilions of rows and
remove and reapply the index :<<
ps: For batch inserting and bulking rows, always initiate an explicit
transation!
> Does anybody know?
> Thanks
> Richard Speiss
>|||Oops, I did mean does the index get updated (bad wording on my part. I
didn't expect the entire thing to be rebuilt).
Thanks for the reply
Richard
> THe index is not 'rebuild' upon each insert. It is updated on each insert.
> Since 'table splits' on indexes can be costly, it is sometimes, a good
idea
> to remove the index.
> But that depends, if you have a very large table with bilions of rows and
> remove and reapply the index :<<
> ps: For batch inserting and bulking rows, always initiate an explicit
> transation!
>|||Richard,
SQL Server does not rebuild the indexes as data is inserted, rather the
indexes are "maintained". i.e. if you insert a new row into the table, then
all indexes on the table will need to have the index pages maintained at the
same time.
Sometimes you will find that dropping all indexes, doing a large insert, and
then creating the index after the load is quicker than doing the load with
the indexes defined. Sometimes you won't. Only testing will reveal which way
will be quick for your environment.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
> Does anybody know?
> Thanks
> Richard Speiss
>
INSERT INTO A SELECT * FROM B
And let's say that table B has 1,000,000 rows
I was just wondering if SQL Server rebuilds the indexes on table A after
each row is inserted or if it waits until all 1,000,000 rows are inserted
and then rebuilds the indexes at the end? If it rebuilds the indexes upon
each insert then I would probably drop the indexes first and then just add
them at the end.
Does anybody know?
Thanks
Richard Speiss"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
THe index is not 'rebuild' upon each insert. It is updated on each insert.
Since 'table splits' on indexes can be costly, it is sometimes, a good idea
to remove the index.
But that depends, if you have a very large table with bilions of rows and
remove and reapply the index :<<
ps: For batch inserting and bulking rows, always initiate an explicit
transation!
> Does anybody know?
> Thanks
> Richard Speiss
>|||Oops, I did mean does the index get updated (bad wording on my part. I
didn't expect the entire thing to be rebuilt).
Thanks for the reply
Richard
> THe index is not 'rebuild' upon each insert. It is updated on each insert.
> Since 'table splits' on indexes can be costly, it is sometimes, a good
idea
> to remove the index.
> But that depends, if you have a very large table with bilions of rows and
> remove and reapply the index :<<
> ps: For batch inserting and bulking rows, always initiate an explicit
> transation!
>|||Richard,
SQL Server does not rebuild the indexes as data is inserted, rather the
indexes are "maintained". i.e. if you insert a new row into the table, then
all indexes on the table will need to have the index pages maintained at the
same time.
Sometimes you will find that dropping all indexes, doing a large insert, and
then creating the index after the load is quicker than doing the load with
the indexes defined. Sometimes you won't. Only testing will reveal which way
will be quick for your environment.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
> Does anybody know?
> Thanks
> Richard Speiss
>
Efficiency of INSERT with multiple rows
Let's say that I am inserting rows into a table via a statement like this
INSERT INTO A SELECT * FROM B
And let's say that table B has 1,000,000 rows
I was just wondering if SQL Server rebuilds the indexes on table A after
each row is inserted or if it waits until all 1,000,000 rows are inserted
and then rebuilds the indexes at the end? If it rebuilds the indexes upon
each insert then I would probably drop the indexes first and then just add
them at the end.
Does anybody know?
Thanks
Richard Speiss
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
THe index is not 'rebuild' upon each insert. It is updated on each insert.
Since 'table splits' on indexes can be costly, it is sometimes, a good idea
to remove the index.
But that depends, if you have a very large table with bilions of rows and
remove and reapply the index :<<
ps: For batch inserting and bulking rows, always initiate an explicit
transation!
> Does anybody know?
> Thanks
> Richard Speiss
>
|||Oops, I did mean does the index get updated (bad wording on my part. I
didn't expect the entire thing to be rebuilt).
Thanks for the reply
Richard
> THe index is not 'rebuild' upon each insert. It is updated on each insert.
> Since 'table splits' on indexes can be costly, it is sometimes, a good
idea
> to remove the index.
> But that depends, if you have a very large table with bilions of rows and
> remove and reapply the index :<<
> ps: For batch inserting and bulking rows, always initiate an explicit
> transation!
>
|||Richard,
SQL Server does not rebuild the indexes as data is inserted, rather the
indexes are "maintained". i.e. if you insert a new row into the table, then
all indexes on the table will need to have the index pages maintained at the
same time.
Sometimes you will find that dropping all indexes, doing a large insert, and
then creating the index after the load is quicker than doing the load with
the indexes defined. Sometimes you won't. Only testing will reveal which way
will be quick for your environment.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
> Does anybody know?
> Thanks
> Richard Speiss
>
INSERT INTO A SELECT * FROM B
And let's say that table B has 1,000,000 rows
I was just wondering if SQL Server rebuilds the indexes on table A after
each row is inserted or if it waits until all 1,000,000 rows are inserted
and then rebuilds the indexes at the end? If it rebuilds the indexes upon
each insert then I would probably drop the indexes first and then just add
them at the end.
Does anybody know?
Thanks
Richard Speiss
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
THe index is not 'rebuild' upon each insert. It is updated on each insert.
Since 'table splits' on indexes can be costly, it is sometimes, a good idea
to remove the index.
But that depends, if you have a very large table with bilions of rows and
remove and reapply the index :<<
ps: For batch inserting and bulking rows, always initiate an explicit
transation!
> Does anybody know?
> Thanks
> Richard Speiss
>
|||Oops, I did mean does the index get updated (bad wording on my part. I
didn't expect the entire thing to be rebuilt).
Thanks for the reply
Richard
> THe index is not 'rebuild' upon each insert. It is updated on each insert.
> Since 'table splits' on indexes can be costly, it is sometimes, a good
idea
> to remove the index.
> But that depends, if you have a very large table with bilions of rows and
> remove and reapply the index :<<
> ps: For batch inserting and bulking rows, always initiate an explicit
> transation!
>
|||Richard,
SQL Server does not rebuild the indexes as data is inserted, rather the
indexes are "maintained". i.e. if you insert a new row into the table, then
all indexes on the table will need to have the index pages maintained at the
same time.
Sometimes you will find that dropping all indexes, doing a large insert, and
then creating the index after the load is quicker than doing the load with
the indexes defined. Sometimes you won't. Only testing will reveal which way
will be quick for your environment.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
> Does anybody know?
> Thanks
> Richard Speiss
>
Efficiency of INSERT with multiple rows
Let's say that I am inserting rows into a table via a statement like this
INSERT INTO A SELECT * FROM B
And let's say that table B has 1,000,000 rows
I was just wondering if SQL Server rebuilds the indexes on table A after
each row is inserted or if it waits until all 1,000,000 rows are inserted
and then rebuilds the indexes at the end? If it rebuilds the indexes upon
each insert then I would probably drop the indexes first and then just add
them at the end.
Does anybody know?
Thanks
Richard Speiss"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
THe index is not 'rebuild' upon each insert. It is updated on each insert.
Since 'table splits' on indexes can be costly, it is sometimes, a good idea
to remove the index.
But that depends, if you have a very large table with bilions of rows and
remove and reapply the index :<<
ps: For batch inserting and bulking rows, always initiate an explicit
transation!
> Does anybody know?
> Thanks
> Richard Speiss
>|||Oops, I did mean does the index get updated (bad wording on my part. I
didn't expect the entire thing to be rebuilt).
Thanks for the reply
Richard
> THe index is not 'rebuild' upon each insert. It is updated on each insert.
> Since 'table splits' on indexes can be costly, it is sometimes, a good
idea
> to remove the index.
> But that depends, if you have a very large table with bilions of rows and
> remove and reapply the index :<<
> ps: For batch inserting and bulking rows, always initiate an explicit
> transation!
>|||Richard,
SQL Server does not rebuild the indexes as data is inserted, rather the
indexes are "maintained". i.e. if you insert a new row into the table, then
all indexes on the table will need to have the index pages maintained at the
same time.
Sometimes you will find that dropping all indexes, doing a large insert, and
then creating the index after the load is quicker than doing the load with
the indexes defined. Sometimes you won't. Only testing will reveal which way
will be quick for your environment.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
> Does anybody know?
> Thanks
> Richard Speiss
>
INSERT INTO A SELECT * FROM B
And let's say that table B has 1,000,000 rows
I was just wondering if SQL Server rebuilds the indexes on table A after
each row is inserted or if it waits until all 1,000,000 rows are inserted
and then rebuilds the indexes at the end? If it rebuilds the indexes upon
each insert then I would probably drop the indexes first and then just add
them at the end.
Does anybody know?
Thanks
Richard Speiss"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
THe index is not 'rebuild' upon each insert. It is updated on each insert.
Since 'table splits' on indexes can be costly, it is sometimes, a good idea
to remove the index.
But that depends, if you have a very large table with bilions of rows and
remove and reapply the index :<<
ps: For batch inserting and bulking rows, always initiate an explicit
transation!
> Does anybody know?
> Thanks
> Richard Speiss
>|||Oops, I did mean does the index get updated (bad wording on my part. I
didn't expect the entire thing to be rebuilt).
Thanks for the reply
Richard
> THe index is not 'rebuild' upon each insert. It is updated on each insert.
> Since 'table splits' on indexes can be costly, it is sometimes, a good
idea
> to remove the index.
> But that depends, if you have a very large table with bilions of rows and
> remove and reapply the index :<<
> ps: For batch inserting and bulking rows, always initiate an explicit
> transation!
>|||Richard,
SQL Server does not rebuild the indexes as data is inserted, rather the
indexes are "maintained". i.e. if you insert a new row into the table, then
all indexes on the table will need to have the index pages maintained at the
same time.
Sometimes you will find that dropping all indexes, doing a large insert, and
then creating the index after the load is quicker than doing the load with
the indexes defined. Sometimes you won't. Only testing will reveal which way
will be quick for your environment.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
> Does anybody know?
> Thanks
> Richard Speiss
>
Subscribe to:
Posts (Atom)