rowid is there?

Hi experts,
I'm in need to use the rowid of a column.
is there any concept like rowid?

for example, i need the first row of a table.or 5th row of a table.how can i write the query?

thank you verymuch in advance.

Quote:

Originally Posted by vinod

Hi experts,
I'm in need to use the rowid of a column.
is there any concept like rowid?

for example, i need the first row of a table.or 5th row of a table.how can i write the query?

thank you verymuch in advance.

select top 1 * from tablename|||Add a column of int datatype to the existing table. Define identity on that column. Define Identity seed and identity increment as 1,1.
The following is the syntax.

a int identity(1,1). Here a is the column. SQL Server automatically increments the number. Based on that number you can fetch the desired record.|||Using Identity function is really great friend.
I'm trying for my purpose now, if i need some more assistance, i'l come back to you

rowcount in ''select into''

Is there a way to insert the rowcount in a 'select into' clause? Example:

select @.@.rowcount as id,* into mytable_with_rowcount from mytable

If mytable has 100 records then I want an id column in 'mytable_with_rowcount' and have the id column numbered 1 - 100. I know this can be done in a loop, but can it be done in the select w/out doing a loop?

Thanks,

Phil

if its sql server 2005 you can use Ranking Function to number the row and then insert to table. Read about Ranking Functions in BOL.

If its 2000, then you can create a table with ID as indentity column and then insert the rows to that table.

http://www.sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk

http://www.sql-server-performance.com/ak_ranking_functions.asp

Madhu

|||Very nice. Thanks.

rowcount help

i'm trying to get total rows found by query that uses top clause...
for example:
select top 10 myTable.* from myTable where myTable.number > 200
let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.
is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().
tnx...> is there any way to get total row count, without affecting the TOP
> clause?
One method is with a subquery:
SELECT TOP 10
myTable.*,
(SELECT COUNT(*)
FROM myTable
WHERE myTable.number > 200) AS TotalRows
FROM myTable
WHERE myTable.number > 200
--
Hope this helps.
Dan Guzman
SQL Server MVP
"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128944.554703.296710@.38g2000cwa.googlegroups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...
>|||well, it helped...
but i'm worried about the execution time when using full-text search...
anyway, thanx for your help...|||You could select the entire set into a temp table or table variable.
Then, get the count of the temp table, then select the number you want.
e.g.
declare @.tAllRows as table(...)
insert into @.tAllRows
select ... from myTable where myTable.number>200
declare @.iTotalCount int
set @.iTotalCount = @.@.rowcount
select top 10 myTable.*, @.@.rowcount from @.tAllRows
While I usually try to avoid temp tables and table variables,
_sometimes_ they help a lot with performance. Benchmarking would be a
good idea, though, as my approach could be a disaster.
Also, are you paging based on an identity field? If so, you might not
get the results you expect when the numbers are not continuous.|||err...
select top 10 myTable.*, @.iTotalCount from @.tAllRows
my bad...

Row level Locking

To All:
I like to know if Row-level Locking is set by default. For example ... if
thousands of users regularly update a table, is it gauranteed that Sql
Server does a row level lock or something needs to be defined?
Thanks,
TunjiLocking models are dynamic in SQL Server. No there is no guarantee a row
lock will be chosen. SQL will do what it thinks is best according to a large
body of information that is documented in BOL amonth other places.
Do you have a specific problem? Or just trying to understand the locking
model better?
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Tunji" <Tunj@.hotmail.com> wrote in message
news:efYBulfAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> To All:
> I like to know if Row-level Locking is set by default. For example ... if
> thousands of users regularly update a table, is it gauranteed that Sql
> Server does a row level lock or something needs to be defined?
> Thanks,
> Tunji
>|||Brain,
Yes, i just like to know the effect and result of having thousands of users
updating a particular table before putting it into test and production.
Most importantly how locks are handled in this kind of situation.
Thanks for your contribution to the Swl Server world and great Articles in
Sql Server Magazine.
Tunji
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23b%23wsvfAEHA.3316@.TK2MSFTNGP11.phx.gbl...
> Locking models are dynamic in SQL Server. No there is no guarantee a row
> lock will be chosen. SQL will do what it thinks is best according to a
large
> body of information that is documented in BOL amonth other places.
> Do you have a specific problem? Or just trying to understand the locking
> model better?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Tunji" <Tunj@.hotmail.com> wrote in message
> news:efYBulfAEHA.2212@.TK2MSFTNGP10.phx.gbl...
if
>

Row level Locking

To All:
I like to know if Row-level Locking is set by default. For example ... if
thousands of users regularly update a table, is it gauranteed that Sql
Server does a row level lock or something needs to be defined?
Thanks,
TunjiLocking models are dynamic in SQL Server. No there is no guarantee a row
lock will be chosen. SQL will do what it thinks is best according to a large
body of information that is documented in BOL amonth other places.
Do you have a specific problem? Or just trying to understand the locking
model better?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Tunji" <Tunj@.hotmail.com> wrote in message
news:efYBulfAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> To All:
> I like to know if Row-level Locking is set by default. For example ... if
> thousands of users regularly update a table, is it gauranteed that Sql
> Server does a row level lock or something needs to be defined?
> Thanks,
> Tunji
>|||Brain,
Yes, i just like to know the effect and result of having thousands of users
updating a particular table before putting it into test and production.
Most importantly how locks are handled in this kind of situation.
Thanks for your contribution to the Swl Server world and great Articles in
Sql Server Magazine.
Tunji
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23b%23wsvfAEHA.3316@.TK2MSFTNGP11.phx.gbl...
> Locking models are dynamic in SQL Server. No there is no guarantee a row
> lock will be chosen. SQL will do what it thinks is best according to a
large
> body of information that is documented in BOL amonth other places.
> Do you have a specific problem? Or just trying to understand the locking
> model better?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Tunji" <Tunj@.hotmail.com> wrote in message
> news:efYBulfAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> > To All:
> >
> > I like to know if Row-level Locking is set by default. For example ...
if
> > thousands of users regularly update a table, is it gauranteed that Sql
> > Server does a row level lock or something needs to be defined?
> >
> > Thanks,
> >
> > Tunji
> >
> >
>

Row formatting

Can I have each row have a different background shade or color. For example
data in row 1 white, row 2 grey and so on. A mix of white and grey for easy
reading. I dont want to use lines.
Thanks for your help.http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_layout_v1_5bl3.asp
On Mon, 5 Jun 2006 10:56:02 -0700, Shan
<Shan@.discussions.microsoft.com> wrote:
>Can I have each row have a different background shade or color. For example
>data in row 1 white, row 2 grey and so on. A mix of white and grey for easy
>reading. I dont want to use lines.
>Thanks for your help.|||Thanks a bunch.
"Shan" wrote:
> Can I have each row have a different background shade or color. For example
> data in row 1 white, row 2 grey and so on. A mix of white and grey for easy
> reading. I dont want to use lines.
> Thanks for your help.

Row Correlated calculation Query

Hi,
I need help in writing a sql, where a calculated column depends on previous'
s row column, for example,
Table: test
Time Packet Seen Seq_num
--
10:00 20 25
10:01 15 40
10:02 17 57
10:03 10 60
10:04 12 72
Query Result: The output of the query should be
Time Packet Seen Seq_num drops
---
10:00 20 25 NULL
10:01 15 40 0
10:02 17 57 0
10:03 10 60 7 (=57+10-60)
10:04 12 72 0
Is this kind of calculation possible using SQL. Any help in this regards wil
l be highly appreciated.
Thanks
MonisDECLARE @.table TABLE(ident INT IDENTITY(1,1),Timecol VARCHAR(5), PacketSeen
INT, SeqNum INT)
INSERT @.table(Timecol, PacketSeen, SeqNum)
SELECT '10:00',20,25 UNION ALL
SELECT '10:01',15,40 UNION ALL
SELECT '10:02',17,57 UNION ALL
SELECT '10:03',10,60 UNION ALL
SELECT '10:04',12,72
SELECT Timecol, PacketSeen, SeqNum FROM @.table
SELECT
t1.Timecol,
t1.PacketSeen,
t1.SeqNum,
t2.SeqNum+t1.PacketSeen-t1.SeqNum AS drops
FROM
@.table t1
LEFT OUTER JOIN @.table t2 ON t1.ident = t2.ident+1
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"mmonis" wrote:

> Hi,
> I need help in writing a sql, where a calculated column depends on
> previous's row column, for example,
> Table: test
> Time Packet Seen Seq_num
> --
> 10:00 20 25
> 10:01 15 40
> 10:02 17 57
> 10:03 10 60
> 10:04 12 72
>
> Query Result: The output of the query should be
> Time Packet Seen Seq_num drops
> ---
> 10:00 20 25 NULL
> 10:01 15 40 0
> 10:02 17 57 0
> 10:03 10 60 7 (=57+10-60)
> 10:04 12 72 0
>
> Is this kind of calculation possible using SQL. Any help in this
> regards will be highly appreciated.
> Thanks
> Monis
>
> --
> mmonis
> ---
> Posted via http://www.codecomments.com
> ---
>|||Hi
Yes , it is possible by using T-SQL , however much more easier doing such
reports on the client side
"mmonis" <mmonis.28cehp@.mail.codecomments.com> wrote in message
news:mmonis.28cehp@.mail.codecomments.com...
> Hi,
> I need help in writing a sql, where a calculated column depends on
> previous's row column, for example,
> Table: test
> Time Packet Seen Seq_num
> --
> 10:00 20 25
> 10:01 15 40
> 10:02 17 57
> 10:03 10 60
> 10:04 12 72
>
> Query Result: The output of the query should be
> Time Packet Seen Seq_num drops
> ---
> 10:00 20 25 NULL
> 10:01 15 40 0
> 10:02 17 57 0
> 10:03 10 60 7 (=57+10-60)
> 10:04 12 72 0
>
> Is this kind of calculation possible using SQL. Any help in this
> regards will be highly appreciated.
> Thanks
> Monis
>
> --
> mmonis
> ---
> Posted via http://www.codecomments.com
> ---
>|||This looks like it depends on identity being sequential and matching the
order TimeCol. Failed inserts will cause gaps in identity, and we may not
be able to depend on TimeCol always being inserted sequentially either. In
a production environment, I think this will break more often than not. At a
minimum, I think we need a correlated subquery here, no?
"MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
news:ED9AB916-DD8B-423D-92D2-0AD19932AE91@.microsoft.com...
> DECLARE @.table TABLE(ident INT IDENTITY(1,1),Timecol VARCHAR(5),
PacketSeen
> INT, SeqNum INT)
> INSERT @.table(Timecol, PacketSeen, SeqNum)
> SELECT '10:00',20,25 UNION ALL
> SELECT '10:01',15,40 UNION ALL
> SELECT '10:02',17,57 UNION ALL
> SELECT '10:03',10,60 UNION ALL
> SELECT '10:04',12,72
> SELECT Timecol, PacketSeen, SeqNum FROM @.table
> SELECT
> t1.Timecol,
> t1.PacketSeen,
> t1.SeqNum,
> t2.SeqNum+t1.PacketSeen-t1.SeqNum AS drops
> FROM
> @.table t1
> LEFT OUTER JOIN @.table t2 ON t1.ident = t2.ident+1
>
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "mmonis" wrote:
>

Rounding Error?

I'm passing a value from an ASP.net app to an sql stored procedure which stores it in a table.

Problem is if, for example, the value is 2.81 the value is ending up as 2.08999... in my table, but if i do say 6.3 it's fine.

Ive tried having the variable in asp and the field where its stored in sql as a number of types but all with the same result.

Any ideas?

Geoff.You should check the definition and precision of your database column.
Maybe change it from float to money.

Regards
Fredrik

Rounding a real number

Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
HaraldREAL is an inexact numeric type. If you want more precise results then you
should use a precise type such as NUMERIC. Either change the column or CAST
it. On the other hand if you just want to format the value to a certain
number of decimals that's probably easier to do in your client-side app.
--
David Portas
SQL Server MVP
--|||Hello,
I understand, that REAL is not an exact numeric type.
But I think this should work (depth is REAL type):
Query Analyzer:
update dims set depth = 4.4
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
Harald|||This is a multi-part message in MIME format.
--=_NextPart_000_0053_01C4D3E5.B0DEA7D0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=3DCAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald
--=_NextPart_000_0053_01C4D3E5.B0DEA7D0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hello,
it works in a SELECT statement, but not =in an UPDATE statement.
Update dims Set depth=3DCAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald

---

--=_NextPart_000_0053_01C4D3E5.B0DEA7D0--|||> it works in a SELECT statement, but not in an UPDATE statement.
If you put CAST(4.4 AS NUMERIC(9,2)) in a SELECT statement you are
outputting a NUMERIC column not a REAL. Again, REAL is NOT exact. You CANNOT
expect to control precisely the value of the digits to the right of the point
with a REAL. If this is a problem for you then you shouldn't be using REAL.
--
David Portas
SQL Server MVP
--|||Harald,
The data type REAL cannot store the value 4.4. The closest REAL value to
4.4 is what you are seeing. You wouldn't expect an INTEGER column to hold
the value 1.3, and it's the same story here. REAL can only hold certain
numbers, and 4.4 is not one of them.
Steve Kass
Drew University
"Harald Witt" <hwitt@.novasoftware.de> wrote in message
news:OUlBBh90EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I understand, that REAL is not an exact numeric type.
> But I think this should work (depth is REAL type):
> Query Analyzer:
> update dims set depth = 4.4
> Select depth from dims
> Result: 4.4000001
> Any advise?
> Thanks in advance,
> Harald
>

Round Up to next integer

Is there a way to round up a value to the next integer?

For example 2.1 needs to be 3 and 4.9 need to be 5.

use ceiling

SELECT CEILING(2.1) AS CeilsTo3, CEILING(4.9) AS CeilsTo5|||Thanks.

Rotating information from Recordsets to columns.

Hello,

This problem perplexes me and I hope that someone has done something
efficient.

Take for example the data in the MASTER..SYSPERFINFO:
SELECT
CAST(RTRIM(INSTANCE_NAME) AS VARCHAR(15)),
CAST(RTRIM(COUNTER_NAME) AS VARCHAR(31)),
CAST(RTRIM(CNTR_VALUE) AS VARCHAR(10))
FROM MASTER..SYSPERFINFO
WHERE INSTANCE_NAME = N'TEMPDB'

tempdb Data File(s) Size (KB) 51200
tempdb Log File(s) Size (KB) 1272
tempdb Log File(s) Used Size (KB) 738
tempdb Percent Log Used 58
tempdb Active Transactions 0
tempdb Transactions/sec 186281
tempdb Repl. Pending Xacts 0
tempdb Repl. Trans. Rate 0
tempdb Log Cache Reads/sec 0
tempdb Log Cache Hit Ratio 0
tempdb Log Cache Hit Ratio Base 0
tempdb Bulk Copy Rows/sec 0
tempdb Bulk Copy Throughput/sec 0
tempdb Backup/Restore Throughput/sec 0
tempdb DBCC Logical Scan Bytes/sec 0
tempdb Shrink Data Movement Bytes/sec 0
tempdb Log Flushes/sec 1578
tempdb Log Bytes Flushed/sec 67882496
tempdb Log Flush Waits/sec 226
tempdb Log Flush Wait Time 47
tempdb Log Truncations 248
tempdb Log Growths 3
tempdb Log Shrinks 0

<I did the CAST and LTRIM so that it looks better when displayed in a
browser
I would like to keep statistics in a table with the following columns:
INSTANCE_NAME,
DATA_FILE_SIZE,
LOG_FILE_FIZE,
ACTIVE_TRANS,
TRANS_PER_SEC

So, instead of having a table with three columns and 23 rows(only 4 of
which I want), I would have a single row with 4 columns(plus the
Instance_Name).

Visualy, I want to call this a 90 degree rotation. Here's what the
select statement would then look like:
SELECT *
FROM SYSPERFINFO_ARCHIVE
WHERE INSTANCE_NAME = N'TEMPDB'

Here's the result set:
tempdb 51200 1272 0 185198

Is it possible to 'rotate' a recordset into columns?
How would it be done?

Gracias.Tim (google_mssql2000@.cfapostle.com) writes:
> So, instead of having a table with three columns and 23 rows(only 4 of
> which I want), I would have a single row with 4 columns(plus the
> Instance_Name).
> Visualy, I want to call this a 90 degree rotation. Here's what the
> select statement would then look like:
> SELECT *
> FROM SYSPERFINFO_ARCHIVE
> WHERE INSTANCE_NAME = N'TEMPDB'
> Here's the result set:
> tempdb 51200 1272 0 185198

select a.instance_name,
data_file_size = a.cntr_value,
log_file_size = b.cntr_value,
active_trans = c.cntr_value,
trans_per_sec = d.cntr_value
from master..sysperfinfo a
join master..sysperfinfo b on a.instance_name = b.instance_name
join master..sysperfinfo c on a.instance_name = c.instance_name
join master..sysperfinfo d on a.instance_name = d.instance_name
where a.instance_name = N'tempdb'
and a.counter_name = N'Data File(s) Size (KB)'
and b.counter_name = N'Log File(s) Size (KB)'
and c.counter_name = N'Active Transactions'
and d.counter_name = N'Transactions/sec'

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message
> select a.instance_name,
> data_file_size = a.cntr_value,
> log_file_size = b.cntr_value,
> active_trans = c.cntr_value,
> trans_per_sec = d.cntr_value
> from master..sysperfinfo a
> join master..sysperfinfo b on a.instance_name = b.instance_name
> join master..sysperfinfo c on a.instance_name = c.instance_name
> join master..sysperfinfo d on a.instance_name = d.instance_name
> where a.instance_name = N'tempdb'
> and a.counter_name = N'Data File(s) Size (KB)'
> and b.counter_name = N'Log File(s) Size (KB)'
> and c.counter_name = N'Active Transactions'
> and d.counter_name = N'Transactions/sec'

Wow!. There are times I feel like a complete newbie. Hats off to the
set based thinkers as opposed to us poor old procedural bods.

Root node attributes

Hello,

I've been trying to craft some xml that has attributes on the root, but without any success.

For example :-

SELECT
Test1 "Test1",
Test2 "Test2"
FROM
TestTable
FOR
XML PATH('TestValue'),
ELEMENTS,
ROOT('TestValues test=1') <-- this is not allowed, but this is the kind of thing that i would like

To produce :-

<TestValues test=1>
<TestValue>
<Test1>asdf </Test1>
<Test2>asdf </Test2>
</TestValue>
<TestValue>
<Test1>asdf </Test1>
<Test2>asdf </Test2>
</TestValue>
</TestValues>

Any ideas?

Thanks
Cedric

Don't you hate it when you click on the send button and at the same time an idea pop's into your head?!

Just thought i'd answer my own question incase someone is interested...

SELECT 1 '@.Test',
(
SELECT
Test1 "Test1",
Test2 "Test2"
FROM
TestTable
FOR
XML PATH('TestValue'),
TYPE
)
FOR
XML PATH ('TestValues')

Clearly there was too much blood in my coffee stream...

Thanks

Cedric