How to determine ROWCOUNT without executing the select statement

I'm selecting data from a large table in a paged manner by using the
ROW_NUMBER ranking function in a function like the one shown below... I
want the function to also return the total number of rows in the dataset
AFTER the primary select is issued but before the paged data is selected
out. In other words, if there are 10,000 rows, and 3500 of them get
selected by the where clause, but the paging only returns 101 .. 200, I
want the total rows output to be set to 3500.
The way this is written currently, I use @.@.ROWCOUNT, but only get the
page size (eg 100). Is there an easy way to get the primary data set
size without resorting to memory or temporary tables, and without
executing the main query twice'
-mdb
#############################
PROCEDURE [dbo].[GetTablePagedAndSorted]
(
@.tableName nvarchar(100),
@.columnList varchar(2000),
@.sortExpression nvarchar(100),
@.whereClause varchar(2000),
@.startRowIndex int,
@.maximumRows int,
@.totalRows int OUTPUT
) AS
IF (LEN(@.whereClause) = 0) SET @.whereClause = '1=1'
-- Issue query
DECLARE @.sql nvarchar(4000)
SET @.sql = 'SELECT ' + @.columnList + ',RowRank '
SET @.sql = @.sql + ' FROM (
SELECT ' + @.columnList + ', ROW_NUMBER() OVER (ORDER BY ' +
@.sortExpression + ') AS RowRank
FROM ' + @.tableName + '
WHERE (' + @.whereClause + ')
) AS TableWithRowNumbers '
IF (@.maximumRows > 0)
BEGIN
SET @.sql = @.sql + '
WHERE RowRank >= ' + CONVERT(nvarchar(10), @.startRowIndex) +
'
AND RowRank < (' + CONVERT(nvarchar(10), @.startRowIndex) +
' + ' + CONVERT(nvarchar(10), @.maximumRows) + ')'
END
-- Execute the SQL query
PRINT @.sql
EXEC sp_executesql @.sql
SET @.totalRows = @.@.ROWCOUNT
######################################You could put the result into a temp. table. Or you could create a SELECT
COUNT(*) to get the count before you do the actual SELECT. I guess the
question is how important is it to know the intermediate result set size -
is it worth the extra inefficiency?
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
> I'm selecting data from a large table in a paged manner by using the
> ROW_NUMBER ranking function in a function like the one shown below... I
> want the function to also return the total number of rows in the dataset
> AFTER the primary select is issued but before the paged data is selected
> out. In other words, if there are 10,000 rows, and 3500 of them get
> selected by the where clause, but the paging only returns 101 .. 200, I
> want the total rows output to be set to 3500.
> The way this is written currently, I use @.@.ROWCOUNT, but only get the
> page size (eg 100). Is there an easy way to get the primary data set
> size without resorting to memory or temporary tables, and without
> executing the main query twice'
> -mdb
> #############################
> PROCEDURE [dbo].[GetTablePagedAndSorted]
> (
> @.tableName nvarchar(100),
> @.columnList varchar(2000),
> @.sortExpression nvarchar(100),
> @.whereClause varchar(2000),
> @.startRowIndex int,
> @.maximumRows int,
> @.totalRows int OUTPUT
> ) AS
> IF (LEN(@.whereClause) = 0) SET @.whereClause = '1=1'
> -- Issue query
> DECLARE @.sql nvarchar(4000)
> SET @.sql = 'SELECT ' + @.columnList + ',RowRank '
> SET @.sql = @.sql + ' FROM (
> SELECT ' + @.columnList + ', ROW_NUMBER() OVER (ORDER BY ' +
> @.sortExpression + ') AS RowRank
> FROM ' + @.tableName + '
> WHERE (' + @.whereClause + ')
> ) AS TableWithRowNumbers '
> IF (@.maximumRows > 0)
> BEGIN
> SET @.sql = @.sql + '
> WHERE RowRank >= ' + CONVERT(nvarchar(10), @.startRowIndex) +
> '
> AND RowRank < (' + CONVERT(nvarchar(10), @.startRowIndex) +
> ' + ' + CONVERT(nvarchar(10), @.maximumRows) + ')'
> END
> -- Execute the SQL query
> PRINT @.sql
> EXEC sp_executesql @.sql
> SET @.totalRows = @.@.ROWCOUNT
> ######################################
>|||"Mike C#" <xyz@.xyz.com> wrote in
news:OG9RqcZIIHA.4584@.TK2MSFTNGP03.phx.gbl:
> You could put the result into a temp. table. Or you could create a
> SELECT COUNT(*) to get the count before you do the actual SELECT. I
> guess the question is how important is it to know the intermediate
> result set size - is it worth the extra inefficiency?
> "Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
> message news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
>> I'm selecting data from a large table in a paged manner by using the
>> ROW_NUMBER ranking function in a function like the one shown below...
>> I want the function to also return the total number of rows in the
>> dataset AFTER the primary select is issued but before the paged data
>> is selected out. In other words, if there are 10,000 rows, and 3500
>> of them get selected by the where clause, but the paging only returns
>> 101 .. 200, I want the total rows output to be set to 3500.
Thanks, but as I said I do not want to use temporary tables, as this will
absolutely swamp my tempdb due to the potential size of the query results.
I considered your other suggestion previously, but then the question is how
do I get the results of an EXEC into a variable? Keep in mind that I have
to build the sql dynamically, and thus require sp_executesql. The
following syntax doesn't work:
SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
If I can find the solution to how to set a variable based on a dynamic sql
statement then I will be set.
-mdb|||Michael Bray <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16:
> Thanks, but as I said I do not want to use temporary tables, as this
> will absolutely swamp my tempdb due to the potential size of the query
> results. I considered your other suggestion previously, but then the
> question is how do I get the results of an EXEC into a variable? Keep
> in mind that I have to build the sql dynamically, and thus require
> sp_executesql. The following syntax doesn't work:
> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
> If I can find the solution to how to set a variable based on a dynamic
> sql statement then I will be set.
>
OK I found the solution - sp_executesql can actually accept input and
output parameters!!
See the wonderful article at:
http://www.sommarskog.se/dynamic_sql.html
-mdb|||Oops, I see you already found that
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E26438875B7mbrayctiusacom@.207.46.248.16...
> Michael Bray <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
> news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16:
>> Thanks, but as I said I do not want to use temporary tables, as this
>> will absolutely swamp my tempdb due to the potential size of the query
>> results. I considered your other suggestion previously, but then the
>> question is how do I get the results of an EXEC into a variable? Keep
>> in mind that I have to build the sql dynamically, and thus require
>> sp_executesql. The following syntax doesn't work:
>> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
>> If I can find the solution to how to set a variable based on a dynamic
>> sql statement then I will be set.
> OK I found the solution - sp_executesql can actually accept input and
> output parameters!!
> See the wonderful article at:
> http://www.sommarskog.se/dynamic_sql.html
> -mdb|||sp_executesql does take output params
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16...
> "Mike C#" <xyz@.xyz.com> wrote in
> news:OG9RqcZIIHA.4584@.TK2MSFTNGP03.phx.gbl:
>> You could put the result into a temp. table. Or you could create a
>> SELECT COUNT(*) to get the count before you do the actual SELECT. I
>> guess the question is how important is it to know the intermediate
>> result set size - is it worth the extra inefficiency?
>> "Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
>> message news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
>> I'm selecting data from a large table in a paged manner by using the
>> ROW_NUMBER ranking function in a function like the one shown below...
>> I want the function to also return the total number of rows in the
>> dataset AFTER the primary select is issued but before the paged data
>> is selected out. In other words, if there are 10,000 rows, and 3500
>> of them get selected by the where clause, but the paging only returns
>> 101 .. 200, I want the total rows output to be set to 3500.
> Thanks, but as I said I do not want to use temporary tables, as this will
> absolutely swamp my tempdb due to the potential size of the query results.
> I considered your other suggestion previously, but then the question is
> how
> do I get the results of an EXEC into a variable? Keep in mind that I have
> to build the sql dynamically, and thus require sp_executesql. The
> following syntax doesn't work:
> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
> If I can find the solution to how to set a variable based on a dynamic sql
> statement then I will be set.
> -mdb

How to determine default values of a field during runtime

Hi, i am using the function below (visual basic 6) to determine several
properties of
each field in a recordset (SQL server) and all is working well. My
problem is that I
dont know how to find the default value of a field from the table. Does
anyone have
any code suggestions that would get me the default value
For Each F In rstProgressData.Fields
If F.Type <> adChapter Then
If F.Name <> "upsize_ts" Then
rstDest.AddNew
rstDest!Progress = F.Name
Select Case F.Type
Case adChar, adVarWChar, adVarChar
rstDest!ProgressFieldType = "String"
rstDest!ProgressFieldSize = F.DefinedSize
Case adBoolean
rstDest!ProgressFieldType = "Boolean"
Case adSmallInt, adUnsignedTinyInt, adInteger
rstDest!ProgressFieldType = "Integer"
Case adDecimal, adNumeric
rstDest!ProgressFieldType = "Decimal"
Case adDBTimeStamp
rstDest!ProgressFieldType = "DateTime"
Case 203
rstDest!ProgressFieldType = "Memo"
End Select
If (F.Attributes And adFldIsNullable) = adFldIsNullable Then
rstDest!ProgressFieldNullable = True
End If
rstDest.Update
End If
End If
Next FThats hard to see where your recordset is based on if you don=B4t send
the query with you, but otherwise the information can be queried
through the INFORMATION_SCHEMA Views:
Select Column_default from INFORMATION_SCHEMA.COLUMNS
Where table_name =3D '<SomeTable>'
HTH, Jens Suessmeyer.|||The code has to be generic so that it can deal with any sql statement.
As above for any query I can get the field size, type, name & if its
nullable, I just need to find out what syntax to use to get the default
value e.g F.Type gives me type. F has been defined as an ADODB.Field.sql

how to delete the day before bak file created by sql

the below script in sql backup runs fine, however i need it to delete the 1
day prior bak first, before it starts the next day. its like this for
redundicy. but i dont have the space to perform the next day job because the
day before is on the drive. we bakup to tape every night. so we have a copy
of the bak file. i just need to buy some time before i replace the drive
sizes.
EXECUTE master..xp_sqlmaint '-D
PracticeManager -WriteHistory -BkUpOnlyIfClean -CkDB -BkUpMedia
DISK -BkUpDB -UseDefDir -BkExt "BAK" -VrfyBackup -DelBkUps
1DAYS -UpdOptiStats 15'
could i just create a new job to just delbkups 1 days ... and arange it to
go first (1) and on success goto (2)
please help
Am sure this can be done in a round about way. Can use xp_cmdshell to delete
the file before initiating the backup call. this way your old backup is
deleted and the backup process can succeed.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"AA" <jgrace@.digitelusa.net> wrote in message
news:%23haziuX1EHA.2716@.TK2MSFTNGP14.phx.gbl...
> the below script in sql backup runs fine, however i need it to delete the
1
> day prior bak first, before it starts the next day. its like this for
> redundicy. but i dont have the space to perform the next day job because
the
> day before is on the drive. we bakup to tape every night. so we have a
copy
> of the bak file. i just need to buy some time before i replace the drive
> sizes.
> EXECUTE master..xp_sqlmaint '-D
> PracticeManager -WriteHistory -BkUpOnlyIfClean -CkDB -BkUpMedia
> DISK -BkUpDB -UseDefDir -BkExt "BAK" -VrfyBackup -DelBkUps
> 1DAYS -UpdOptiStats 15'
>
> could i just create a new job to just delbkups 1 days ... and arange it to
> go first (1) and on success goto (2)
>
> please help
>
|||In this situation we run a job that uses xp_cmdshell to delete the backup
file before our xp_sqlmaint is run. Might be a nice feature if the backup
job could offer to delete old backups before running a new backup.
Chris Wood
Alberta Department of Energy
CANADA
"AA" <jgrace@.digitelusa.net> wrote in message
news:%23haziuX1EHA.2716@.TK2MSFTNGP14.phx.gbl...
> the below script in sql backup runs fine, however i need it to delete the
1
> day prior bak first, before it starts the next day. its like this for
> redundicy. but i dont have the space to perform the next day job because
the
> day before is on the drive. we bakup to tape every night. so we have a
copy
> of the bak file. i just need to buy some time before i replace the drive
> sizes.
> EXECUTE master..xp_sqlmaint '-D
> PracticeManager -WriteHistory -BkUpOnlyIfClean -CkDB -BkUpMedia
> DISK -BkUpDB -UseDefDir -BkExt "BAK" -VrfyBackup -DelBkUps
> 1DAYS -UpdOptiStats 15'
>
> could i just create a new job to just delbkups 1 days ... and arange it to
> go first (1) and on success goto (2)
>
> please help
>
|||Chris Wood wrote:
> In this situation we run a job that uses xp_cmdshell to delete the backup
> file before our xp_sqlmaint is run. Might be a nice feature if the backup
> job could offer to delete old backups before running a new backup.
Hi,
It is not a good idea to remove the previous backup before you know the
new backup is ok. If the new backup fails you don't have a spare one.
Jo.
|||Very true. But in the case of not enough disk space for more than 1 backup
and the backup file backed up to some other media it could still be a nice
option of the backup plan.
Chris Wood
"Jo Segers" <jo.segers@.alro.be> wrote in message
news:e4fS8hu1EHA.2572@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
>
> Chris Wood wrote:
backup[vbcol=seagreen]
backup
> Hi,
> It is not a good idea to remove the previous backup before you know the
> new backup is ok. If the new backup fails you don't have a spare one.
> Jo.

How to delete rows in a table when no primary key is defined

Hello,

I want to delete duplicate rows in a table when no primary key is
defined.
For eg: If we have table1 with data as below,

Suma 23 100
Suma 23 100

I want to delete a row from this table and retain only one row.

I tried deleting self joins and exists operator. But it is deleting
both the rows. I want to retain one row.

Can anybody help me out.

Thanks in advance,
Suma

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict221110.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=760520Add a identity column to the table and delete the row with de min value.|||Patarroxa wrote:
> Add a identity column to the table and delete the row with de min
> value.

Or copy the data with a SELECT DISTINCT into another table, drop the
original and rename the new table.

robert|||Create a new table (with a key), then use SELECT DISTINCT or GROUP BY to
populate it from the old one.

--
David Portas
SQL Server MVP
--|||"suma" wrote:
> Hello,
> I want to delete duplicate rows in a table when no primary key
> is defined.
> For eg: If we have table1 with data as below,
> Suma 23 100
> Suma 23 100
> I want to delete a row from this table and retain only one
> row.
> I tried deleting self joins and exists operator. But it is
> deleting both the rows. I want to retain one row.
> Can anybody help me out.
> Thanks in advance,
> Suma

Thanks for the response.
But it has to be done using a single sql statement.
Using multiple we can do it...is there any way to do using a single
sql statement.
Thanks,
Suma|||First of all this is not a table by definition. A table must have a
key. And the answer is No, it will take more than one statement to
clean up the base table -- either a cursor, an IDENTITY or a SELECT
DISTINCT. You can put the SELECT DISTINCT into a VIEW as a kludge.

You did fire the guy that did this, didn't you?|||Using a single DELETE statement it can't be done if there is no way to
differentiate between the rows. That's why a primary key is supposed to
be mandatory. Why should you have a table without a key?

--
David Portas
SQL Server MVP
--|||You're not really saving anything doing it this way over the alternatives,
and it's *very* slow for large numbers of duplicates.

SET ROWCOUNT=1

DELETE table1
WHERE EXISTS (SELECT *
FROM table1 AS t2
WHERE table1.col1 = t2.col1 and table1.col2 = t2.col2 and table1.col3 =
t2.col3
GROUP BY t2.col1, t2.col2, t2.col3
HAVING COUNT(*) > 1)

WHILE @.@.ROWCOUNT>0
DELETE ... --same statement all over

Disclaimer: This is not tested. I am not responsible for any loss of data
incurred by use of this technique. SELECT INTO with GROUP BY is probably
safest and fastest, as recommended by others.

Also see books online, Index, DELETE (described), and the description of
DELETE FROM table WHERE CURRENT OF cursor_name

"suma" <DoNotEmail@.dbForumz.com> wrote in message
news:4_761872_6b47fee83970ff4272fca067cae7180d@.dbf orumz.com...
> "suma" wrote:
> > Hello,
> > I want to delete duplicate rows in a table when no primary key
> > is defined.
> > For eg: If we have table1 with data as below,
> > Suma 23 100
> > Suma 23 100
> > I want to delete a row from this table and retain only one
> > row.
> > I tried deleting self joins and exists operator. But it is
> > deleting both the rows. I want to retain one row.
> > Can anybody help me out.
> > Thanks in advance,
> > Suma
> Thanks for the response.
> But it has to be done using a single sql statement.
> Using multiple we can do it...is there any way to do using a single
> sql statement.
> Thanks,
> Suma|||And thats why oracle we can delete the duplicate rows using rowid or rownum
and not in sql server. Some unique identity has to be there !!|||True, but with correct design you'll never need to. The problem IS
soluble in SQL Server too, it's just that SQL Server requires that you
fix things rather than allow you to live with such a kludgy solution.

--
David Portas
SQL Server MVP
--|||>> Oracle we can delete the duplicate rows using rowid or rownum and
not in sql server <<

Yes, Oracle is a sequential file system and a piss-poor RDBMS under the
covers. Parallelism, set processing, and all the other things that
allow a good SQL implmentation to run 4 to 5 orders of magnitude faster
and 80-90% smaller are not available in Oracle and cannot be because of
a horrible architecture. Look up the performance for Nucleus (Sand
Technology) and other VLDB products.|||There is a way to do this, but it will take massive amounts of time to
delete many rows, because the duplicate rows are deleted one row at a
time. It goes like this:

SET ROWCOUNT 1
-- Generate a rowcount > 0
SELECT COUNT(*) FROM MyTable
While @.@.rowcount > 0
Begin
DELETE MyTable
WHERE (
SELECT COUNT(*)
FROM MyTable T1
WHERE T1.Col1 = MyTable.Col1
AND T2.Col2 = MyTable.Col2
) > 1
End
-- don't forget this line!
SET ROWCOUNT 0

Hope this helps,
Gert-Jan

suma wrote:
> Hello,
> I want to delete duplicate rows in a table when no primary key is
> defined.
> For eg: If we have table1 with data as below,
> Suma 23 100
> Suma 23 100
> I want to delete a row from this table and retain only one row.
> I tried deleting self joins and exists operator. But it is deleting
> both the rows. I want to retain one row.
> Can anybody help me out.
> Thanks in advance,
> Suma
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL: http://www.dbforumz.com/General-Dis...pict221110.html
> Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=760520

How to delete data older than X days, without considering time

When running the following SQL statements, I get the same results.
Though I need to count only -30 days. Both statements below also
consider the time of the day as well, which is not desired

DELETE FROM MNT_R
WHERE MNT_R.TIMESTAMP < GETDATE()- 30

DELETE FROM MNT_R
WHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())

Here is the format of the values in column
MNT_R.TIMESTAMP
2005-08-09 06:06:44.577
2005-08-09 06:06:46.810
2005-08-09 06:06:49.060

So, since data are inserted into the MNT_R table every few seconds, my
delete statement will delete different number of rows, according to the
time of the day it runs.

Can you please post a SQL query that will not give me this headache?

thanx a lot allHi there,

You have to convert the source column to a non-using time format like
ISO:

DELETE FROM MNT_R
WHERE VARCHAR(10),MNT_R.TIMESTAMP < CONVERT(VARCHAR(10),GETDATE()-
30,112)

HTH, Jens Suessmeyer.|||nai (nioannides@.laiki.com) writes:
> When running the following SQL statements, I get the same results.
> Though I need to count only -30 days. Both statements below also
> consider the time of the day as well, which is not desired
>
> DELETE FROM MNT_R
> WHERE MNT_R.TIMESTAMP < GETDATE()- 30
> DELETE FROM MNT_R
> WHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())
>
> Here is the format of the values in column
> MNT_R.TIMESTAMP
> 2005-08-09 06:06:44.577
> 2005-08-09 06:06:46.810
> 2005-08-09 06:06:49.060
> So, since data are inserted into the MNT_R table every few seconds, my
> delete statement will delete different number of rows, according to the
> time of the day it runs.
> Can you please post a SQL query that will not give me this headache?

Instead of getdate() used convert(char(8), getdate(), 112) to strip
of the time portion.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx