I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.
I want to get 100 rows onward to one particular data ... how can i ?This doesn't sound like a good idea... (using rownum)
Please enlighten me with the SQL you used in mySQL?
Also, what version of SQL Server are you using?|||SELECT TOP n
(Being deprecated for modification statements in SS 2008+)
SET ROWCOUNT = n
(SS 2005)
OVER () clause|||I want to get 100 rows from particular record and onward.
You need more than just TOP, don't you?
I don't like the use of rownum - I'm sure there's a better way to get what the OP wants!|||You need more than just TOP, don't you?yes, a WHERE condition, too
oh, and an ORDER BY clause, without which TOP is meaningless
:)|||I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.
I want to get 100 rows onward to one particular data ... how can i ?
ummmmmmmmmmmmmm
what particular row|||what particular rowthe one specified by the WHERE condition|||ummmmmmmmmmmmmm
what particular rowThat row, right there near the middle of my screen.
-PatP|||I'm feeling better now|||"Standard" for SQL 2005:
with cte
as
(select row_number () over (order by name) as num, object_id, name
from master.sys.tables)
select *
from cte
where num >= 4
Showing posts with label particular. Show all posts
Showing posts with label particular. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
RowLock
I have to lock the row while insertion or at updation when many users
accessthe particular row.i f the first user access the row after at that tim
e
the row should be locked after his updation the row will unlock since many
users not have to updation at the same time.so ihave lock and release the ro
w
in the stored procedureIf I understood your post correctly, you need some sort of check-out
management. This is not provided natively by SQL Server 2000, and can be
achieved in several ways.
If you specify your needs and provide the DDL and some sample data, maybe we
can help you find a solution.
ML|||hi ML,
I will explain in clearly.
By Using frontend(PHP forms)my clients can update a particular row in
particular table at that same time.while updating i have to update th versio
n
number in the table.so i took the maxvalue of that column at that particular
row and update version mumber.while at that time of updating max count two o
r
more clients gets the same value of that the particular column at that same
time.so it will crasing the number.so i have to lock that paricular while on
e
updating if another tries it displays a message some one is updating,so
please wait a moment and try after his updation is over the row lockis
released and another client able to sccess that row and update.
so how can lock that row and release the lock
it is long query,the query depends many so i can't able to send the query
directly
regards,
balakarthik
"ML" wrote:
> If I understood your post correctly, you need some sort of check-out
> management. This is not provided natively by SQL Server 2000, and can be
> achieved in several ways.
> If you specify your needs and provide the DDL and some sample data, maybe
we
> can help you find a solution.
>
> ML|||balakarthik
You have already asked this in .server and already have been given some
references there. Please do not multipost, it make is very confusing for
those people who are trying to help, who don't know what has already been
said.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:E98C44C0-69AC-489A-9B52-D43785C4D684@.microsoft.com...
> hi ML,
> I will explain in clearly.
> By Using frontend(PHP forms)my clients can update a particular row in
> particular table at that same time.while updating i have to update th
> version
> number in the table.so i took the maxvalue of that column at that
> particular
> row and update version mumber.while at that time of updating max count two
> or
> more clients gets the same value of that the particular column at that
> same
> time.so it will crasing the number.so i have to lock that paricular while
> one
> updating if another tries it displays a message some one is updating,so
> please wait a moment and try after his updation is over the row lockis
> released and another client able to sccess that row and update.
>
> so how can lock that row and release the lock
> it is long query,the query depends many so i can't able to send the query
> directly
>
> regards,
> balakarthik
> "ML" wrote:
>|||You need to use SELECT MAX...FROM...WITH(UPDLOCK, HOLDLOCK) to get the
maximum value, and the locks must be held until the INSERT/UPDATE completes.
If you issue INSERT...SELECT MAX...FROM ...WITH(UPDLOCK, HOLDLOCK) then the
INSERT statement provides an implicit transaction. If you issue SELECT
@.localVariable = MAX...FROM...WITH(UPDLOCK, HOLDLOCK) INSERT...VALUES, then
you need to enclose both statements within a transaction. WITH(UPDLOCK,
HOLDLOCK) prevents other transactions from issuing an insert or update that
has a value for the column that is between MAX and infinity until the lock
is released. Both UPDLOCK and HOLDLOCK are required because you need an
update range-lock, not an individual update lock, or a shared range-lock.
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:E98C44C0-69AC-489A-9B52-D43785C4D684@.microsoft.com...
> hi ML,
> I will explain in clearly.
> By Using frontend(PHP forms)my clients can update a particular row in
> particular table at that same time.while updating i have to update th
version
> number in the table.so i took the maxvalue of that column at that
particular
> row and update version mumber.while at that time of updating max count two
or
> more clients gets the same value of that the particular column at that
same
> time.so it will crasing the number.so i have to lock that paricular while
one
> updating if another tries it displays a message some one is updating,so
> please wait a moment and try after his updation is over the row lockis
> released and another client able to sccess that row and update.
>
> so how can lock that row and release the lock
> it is long query,the query depends many so i can't able to send the query
> directly
>
> regards,
> balakarthik
> "ML" wrote:
>
maybe we|||I see.
Let us forget for a minute about rows, columns, tables and databases. Let's
discuss entities.
Let's say your entity is a single document in a drawer. But this is not an
ordinary drawer, since many users can read the same document at any given
moment. However, only one of them should be allowed to change the contents o
f
that document at any given time.
What you need to do is to let other users know that changes to a document
cannot be made if that document is "checked-out".
In a database you need to do something like this:
1) make sure all data access is done through procedures; and
2) make sure procedures cannot make changes to documents that are
checked-out; and
3) make sure the appropriate procedures report check-outs to the database.
One way is to add a column (or two) to the master table (BTW: since I
haven't seen your DDL this is more a guess than a fully valid suggestion):
CheckedOutBy - it holds the username of the person who checked out the
document. When null, the document is not checked out.
(Maybe even CheckedOutSince - it holds the time of the check-out.)
So, in a use case you'd do something like:
when a user gets a document that hasn't been checked-out (CheckOutBy is
null), the reading procedure sets CheckedOutBy to store the name of the
current user; and when a user finishes his changes the procedure then sets
CheckedOutBy back to null;
any user that tries to check out a document that is already checked out by
another user is warned that his changes will not succeed, and the applicatio
n
should prevent any changes to be made.
But, this is just *one* way of doing it.
ML|||Thanks sir i wil try
"ML" wrote:
> I see.
> Let us forget for a minute about rows, columns, tables and databases. Let'
s
> discuss entities.
> Let's say your entity is a single document in a drawer. But this is not an
> ordinary drawer, since many users can read the same document at any given
> moment. However, only one of them should be allowed to change the contents
of
> that document at any given time.
> What you need to do is to let other users know that changes to a document
> cannot be made if that document is "checked-out".
> In a database you need to do something like this:
> 1) make sure all data access is done through procedures; and
> 2) make sure procedures cannot make changes to documents that are
> checked-out; and
> 3) make sure the appropriate procedures report check-outs to the database.
> One way is to add a column (or two) to the master table (BTW: since I
> haven't seen your DDL this is more a guess than a fully valid suggestion):
> CheckedOutBy - it holds the username of the person who checked out the
> document. When null, the document is not checked out.
> (Maybe even CheckedOutSince - it holds the time of the check-out.)
> So, in a use case you'd do something like:
> when a user gets a document that hasn't been checked-out (CheckOutBy is
> null), the reading procedure sets CheckedOutBy to store the name of the
> current user; and when a user finishes his changes the procedure then sets
> CheckedOutBy back to null;
> any user that tries to check out a document that is already checked out by
> another user is warned that his changes will not succeed, and the applicat
ion
> should prevent any changes to be made.
> But, this is just *one* way of doing it.
>
> ML
accessthe particular row.i f the first user access the row after at that tim
e
the row should be locked after his updation the row will unlock since many
users not have to updation at the same time.so ihave lock and release the ro
w
in the stored procedureIf I understood your post correctly, you need some sort of check-out
management. This is not provided natively by SQL Server 2000, and can be
achieved in several ways.
If you specify your needs and provide the DDL and some sample data, maybe we
can help you find a solution.
ML|||hi ML,
I will explain in clearly.
By Using frontend(PHP forms)my clients can update a particular row in
particular table at that same time.while updating i have to update th versio
n
number in the table.so i took the maxvalue of that column at that particular
row and update version mumber.while at that time of updating max count two o
r
more clients gets the same value of that the particular column at that same
time.so it will crasing the number.so i have to lock that paricular while on
e
updating if another tries it displays a message some one is updating,so
please wait a moment and try after his updation is over the row lockis
released and another client able to sccess that row and update.
so how can lock that row and release the lock
it is long query,the query depends many so i can't able to send the query
directly
regards,
balakarthik
"ML" wrote:
> If I understood your post correctly, you need some sort of check-out
> management. This is not provided natively by SQL Server 2000, and can be
> achieved in several ways.
> If you specify your needs and provide the DDL and some sample data, maybe
we
> can help you find a solution.
>
> ML|||balakarthik
You have already asked this in .server and already have been given some
references there. Please do not multipost, it make is very confusing for
those people who are trying to help, who don't know what has already been
said.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:E98C44C0-69AC-489A-9B52-D43785C4D684@.microsoft.com...
> hi ML,
> I will explain in clearly.
> By Using frontend(PHP forms)my clients can update a particular row in
> particular table at that same time.while updating i have to update th
> version
> number in the table.so i took the maxvalue of that column at that
> particular
> row and update version mumber.while at that time of updating max count two
> or
> more clients gets the same value of that the particular column at that
> same
> time.so it will crasing the number.so i have to lock that paricular while
> one
> updating if another tries it displays a message some one is updating,so
> please wait a moment and try after his updation is over the row lockis
> released and another client able to sccess that row and update.
>
> so how can lock that row and release the lock
> it is long query,the query depends many so i can't able to send the query
> directly
>
> regards,
> balakarthik
> "ML" wrote:
>|||You need to use SELECT MAX...FROM...WITH(UPDLOCK, HOLDLOCK) to get the
maximum value, and the locks must be held until the INSERT/UPDATE completes.
If you issue INSERT...SELECT MAX...FROM ...WITH(UPDLOCK, HOLDLOCK) then the
INSERT statement provides an implicit transaction. If you issue SELECT
@.localVariable = MAX...FROM...WITH(UPDLOCK, HOLDLOCK) INSERT...VALUES, then
you need to enclose both statements within a transaction. WITH(UPDLOCK,
HOLDLOCK) prevents other transactions from issuing an insert or update that
has a value for the column that is between MAX and infinity until the lock
is released. Both UPDLOCK and HOLDLOCK are required because you need an
update range-lock, not an individual update lock, or a shared range-lock.
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:E98C44C0-69AC-489A-9B52-D43785C4D684@.microsoft.com...
> hi ML,
> I will explain in clearly.
> By Using frontend(PHP forms)my clients can update a particular row in
> particular table at that same time.while updating i have to update th
version
> number in the table.so i took the maxvalue of that column at that
particular
> row and update version mumber.while at that time of updating max count two
or
> more clients gets the same value of that the particular column at that
same
> time.so it will crasing the number.so i have to lock that paricular while
one
> updating if another tries it displays a message some one is updating,so
> please wait a moment and try after his updation is over the row lockis
> released and another client able to sccess that row and update.
>
> so how can lock that row and release the lock
> it is long query,the query depends many so i can't able to send the query
> directly
>
> regards,
> balakarthik
> "ML" wrote:
>
maybe we|||I see.
Let us forget for a minute about rows, columns, tables and databases. Let's
discuss entities.
Let's say your entity is a single document in a drawer. But this is not an
ordinary drawer, since many users can read the same document at any given
moment. However, only one of them should be allowed to change the contents o
f
that document at any given time.
What you need to do is to let other users know that changes to a document
cannot be made if that document is "checked-out".
In a database you need to do something like this:
1) make sure all data access is done through procedures; and
2) make sure procedures cannot make changes to documents that are
checked-out; and
3) make sure the appropriate procedures report check-outs to the database.
One way is to add a column (or two) to the master table (BTW: since I
haven't seen your DDL this is more a guess than a fully valid suggestion):
CheckedOutBy - it holds the username of the person who checked out the
document. When null, the document is not checked out.
(Maybe even CheckedOutSince - it holds the time of the check-out.)
So, in a use case you'd do something like:
when a user gets a document that hasn't been checked-out (CheckOutBy is
null), the reading procedure sets CheckedOutBy to store the name of the
current user; and when a user finishes his changes the procedure then sets
CheckedOutBy back to null;
any user that tries to check out a document that is already checked out by
another user is warned that his changes will not succeed, and the applicatio
n
should prevent any changes to be made.
But, this is just *one* way of doing it.
ML|||Thanks sir i wil try
"ML" wrote:
> I see.
> Let us forget for a minute about rows, columns, tables and databases. Let'
s
> discuss entities.
> Let's say your entity is a single document in a drawer. But this is not an
> ordinary drawer, since many users can read the same document at any given
> moment. However, only one of them should be allowed to change the contents
of
> that document at any given time.
> What you need to do is to let other users know that changes to a document
> cannot be made if that document is "checked-out".
> In a database you need to do something like this:
> 1) make sure all data access is done through procedures; and
> 2) make sure procedures cannot make changes to documents that are
> checked-out; and
> 3) make sure the appropriate procedures report check-outs to the database.
> One way is to add a column (or two) to the master table (BTW: since I
> haven't seen your DDL this is more a guess than a fully valid suggestion):
> CheckedOutBy - it holds the username of the person who checked out the
> document. When null, the document is not checked out.
> (Maybe even CheckedOutSince - it holds the time of the check-out.)
> So, in a use case you'd do something like:
> when a user gets a document that hasn't been checked-out (CheckOutBy is
> null), the reading procedure sets CheckedOutBy to store the name of the
> current user; and when a user finishes his changes the procedure then sets
> CheckedOutBy back to null;
> any user that tries to check out a document that is already checked out by
> another user is warned that his changes will not succeed, and the applicat
ion
> should prevent any changes to be made.
> But, this is just *one* way of doing it.
>
> ML
Rowcounts on all tables in a DB
I need to create a query to get the row counts of all tables in a
particular database. Can somebody point me to a method of doing this?
Regards,
RandyThis will probably work for what you need:
--2005
select object_name(object_id), rows
from sys.partitions
order by 2 desc
--2000
select object_name(id), rowcnt
from sysindexes
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I need to create a query to get the row counts of all tables in a
>particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy|||Hi
--SQL Server 2005
SELECT
t.name,
[RowCount] = SUM
(
CASE
WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
ELSE 0
END
)
FROM
sys.tables t
INNER JOIN sys.partitions p
ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY
t.name;
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I need to create a query to get the row counts of all tables in a
>particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy|||Just an FYI that for 2000, the rowcount can very well be off (not reflect reality). In 2005 it is
likely to reflect reality.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:B2937C70-6DD2-4E49-8028-1148FF13E8FF@.microsoft.com...
> This will probably work for what you need:
> --2005
> select object_name(object_id), rows
> from sys.partitions
> order by 2 desc
> --2000
> select object_name(id), rowcnt
> from sysindexes
> order by 2 desc
>
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "Randy Galliano" <r_galliano@.yahoo.com> wrote in message
> news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>>I need to create a query to get the row counts of all tables in a particular database. Can
>>somebody point me to a method of doing this?
>> Regards,
>> Randy
>|||Thank you for all the help. The queries are just what I needed.
Regards,
Randy.
Randy Galliano wrote:
> I need to create a query to get the row counts of all tables in a
> particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy|||On Wed, 20 Feb 2008 16:44:12 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Just an FYI that for 2000, the rowcount can very well be off (not reflect reality). In 2005 it is
>likely to reflect reality.
But if you run:
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS
just before you should have accurate numbers for all tables. Or, to
be more precise, each table's count will have been accurate very
recently.
Roy Harvey
Beacon Falls, CT|||On Feb 20, 5:23=A0am, Randy Galliano <r_galli...@.yahoo.com> wrote:
> I need to create a query to get the row counts of all tables in a
> particular database. =A0Can somebody point me to a method of doing this?
> Regards,
> Randy
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=
o-count-rows-from-a-table.aspx
particular database. Can somebody point me to a method of doing this?
Regards,
RandyThis will probably work for what you need:
--2005
select object_name(object_id), rows
from sys.partitions
order by 2 desc
--2000
select object_name(id), rowcnt
from sysindexes
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I need to create a query to get the row counts of all tables in a
>particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy|||Hi
--SQL Server 2005
SELECT
t.name,
[RowCount] = SUM
(
CASE
WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
ELSE 0
END
)
FROM
sys.tables t
INNER JOIN sys.partitions p
ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY
t.name;
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I need to create a query to get the row counts of all tables in a
>particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy|||Just an FYI that for 2000, the rowcount can very well be off (not reflect reality). In 2005 it is
likely to reflect reality.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:B2937C70-6DD2-4E49-8028-1148FF13E8FF@.microsoft.com...
> This will probably work for what you need:
> --2005
> select object_name(object_id), rows
> from sys.partitions
> order by 2 desc
> --2000
> select object_name(id), rowcnt
> from sysindexes
> order by 2 desc
>
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "Randy Galliano" <r_galliano@.yahoo.com> wrote in message
> news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>>I need to create a query to get the row counts of all tables in a particular database. Can
>>somebody point me to a method of doing this?
>> Regards,
>> Randy
>|||Thank you for all the help. The queries are just what I needed.
Regards,
Randy.
Randy Galliano wrote:
> I need to create a query to get the row counts of all tables in a
> particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy|||On Wed, 20 Feb 2008 16:44:12 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Just an FYI that for 2000, the rowcount can very well be off (not reflect reality). In 2005 it is
>likely to reflect reality.
But if you run:
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS
just before you should have accurate numbers for all tables. Or, to
be more precise, each table's count will have been accurate very
recently.
Roy Harvey
Beacon Falls, CT|||On Feb 20, 5:23=A0am, Randy Galliano <r_galli...@.yahoo.com> wrote:
> I need to create a query to get the row counts of all tables in a
> particular database. =A0Can somebody point me to a method of doing this?
> Regards,
> Randy
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=
o-count-rows-from-a-table.aspx
Friday, March 23, 2012
Row size limitations of SQL Server 2005
I've created a database design and I'm trying to figure out if it's workable or not. I have 2 tables in particular that are sparse (20 columns, but only a few have data - about 100 bytes/row) but will grow very large - to the tune of 700 million rows.
My question is whether or not there is a problem with SQL Server 2005 having 1.5+ billion rows of data even though it will likely only take up 100 gigs or so on disk. Anyone have experience in large numbers of rows like this? We're still doing testing as far determining how slow queries will get.
Thanks,
Craig
That's fine. SQL Server can deal with that many rows of data. I've worked with tables with billions of rows before. Don't forget to plan for your index space as well and any necessary index maintenance.
-Sue
|||Try the link below for the SQL Server Max CAP.
http://msdn2.microsoft.com/en-us/library/ms143432.aspx
Monday, March 12, 2012
ROW ID
Is there a hidden column in a table that contains a
unique value for a particular row.
In Oracle there is a ROWID
In DB2 there is RRN
Is there an equivalent value in SQL 2000No. Not unless you create one. In SQL Server you can use the Identity
property to create a rowid...
Now if you're looking for the internal row locator - which non-clustered
indexes use to point to the associated data row - then there are two types
within SQL Server - the clustering key (if the table is clustered) and a
fixed rowid (if the table does not have a clustered index - therefore it's a
heap). The clustering key is easily accessibly. The fixed rowid as part of a
heap consists of an 8 byte key. 2 bytes for the file, 4 bytes for the page
id and 2 bytes for the row offset slot number.
OK - that might be too much information... :)
hth,
kt
--
Please reply only on the newsgroups! Include dml/ddl, when possible.Thanks
Kimberly L. Tripp
President, SYSolutions, Inc. www.SQLSkills.com
Principal Mentor, Solid Quality Learning www.SolidQualityLearning.com
"Roy Wennerod" <rwennerod@.hotmail.com> wrote in message
news:032801c37eda$70fec160$a401280a@.phx.gbl...
> Is there a hidden column in a table that contains a
> unique value for a particular row.
> In Oracle there is a ROWID
> In DB2 there is RRN
> Is there an equivalent value in SQL 2000|||"Roy Wennerod" <rwennerod@.hotmail.com> wrote in message
news:032801c37eda$70fec160$a401280a@.phx.gbl...
> Is there a hidden column in a table that contains a
> unique value for a particular row.
> In Oracle there is a ROWID
> In DB2 there is RRN
> Is there an equivalent value in SQL 2000
No there is not.
David|||"Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
news:OeQq6h9fDHA.2172@.TK2MSFTNGP09.phx.gbl...
> The problem with exposing the internal pointers (whatever a product may
call
> them) is that it makes it impractical to move rows around in the database
> without breaking applications. As long as applications don't have access
to
> this physical pointer, you can do things like split a node in a clustered
> index, or perform an on-line reorganization, and never break an
application.
> That's why SQL Server, like other products that had their origins in the
> so-called second generation of rdbms products, does not expose them. The
> products that have their origins in the first generation, DB2, Rdb, and
> Oracle do expose these pointers.
> It is highly unlikely that SQL Server will ever expose the internal
> pointers. There were extensive discussions on this topic during
development
> of SQL Server 7.0, and the team (including those from Rdb who were fond of
> DBKEYs) concluded that exposing the internal pointers would be a mistake.
> To expose them and retain the online reorganization capabilities would
> require that the pointers be virtualized. And if you virtualize them then
> the implementation is no different then the user defining a new column
(with
> an index), or using an existing key column. So SQL Server's architecture
> relies on not exposing the physical pointers to the user.
Can't you have your cake and eat it too with the sql99 row_number()
function? :)
There's an army of users who are begging for this!:)|||Hal,
Thank you for the quick response...perhaps I should ask for what I am
trying to accomplish.
I have a table that has one nvarchar column. I need to display the
results in the order in which the data was entered into the database.
I was thinking I could use rowid or an equivalent. Any ideas.
Thanks in advance.
"Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message news:<OeQq6h9fDHA.2172@.TK2MSFTNGP09.phx.gbl>...
> The problem with exposing the internal pointers (whatever a product may call
> them) is that it makes it impractical to move rows around in the database
> without breaking applications. As long as applications don't have access to
> this physical pointer, you can do things like split a node in a clustered
> index, or perform an on-line reorganization, and never break an application.
> That's why SQL Server, like other products that had their origins in the
> so-called second generation of rdbms products, does not expose them. The
> products that have their origins in the first generation, DB2, Rdb, and
> Oracle do expose these pointers.
> It is highly unlikely that SQL Server will ever expose the internal
> pointers. There were extensive discussions on this topic during development
> of SQL Server 7.0, and the team (including those from Rdb who were fond of
> DBKEYs) concluded that exposing the internal pointers would be a mistake.
> To expose them and retain the online reorganization capabilities would
> require that the pointers be virtualized. And if you virtualize them then
> the implementation is no different then the user defining a new column (with
> an index), or using an existing key column. So SQL Server's architecture
> relies on not exposing the physical pointers to the user.
> --
> Hal Berenson, SQL Server MVP
> True Mountain Group LLC
>
> "bob" <bob@.hotmail.com> wrote in message
> news:03a701c37edc$800c23d0$a401280a@.phx.gbl...
> > there is a ROWID for each row in SQL Server too, just like
> > in Oracle but it is for only internal use, not exposed to
> > the users for querying. You can't see it or query it as an
> > implicit column. I don't know if MS is planning to expose
> > ROWID to the user querying in future versions of sql
> > server.
> >
> > You have to define a business key as a unique key or use
> > IDENTITY column to uniquely identify each row in a table.
> >
> > >--Original Message--
> > >Is there a hidden column in a table that contains a
> > >unique value for a particular row.
> > >
> > >In Oracle there is a ROWID
> > >
> > >In DB2 there is RRN
> > >
> > >Is there an equivalent value in SQL 2000
> > >.
> > >
unique value for a particular row.
In Oracle there is a ROWID
In DB2 there is RRN
Is there an equivalent value in SQL 2000No. Not unless you create one. In SQL Server you can use the Identity
property to create a rowid...
Now if you're looking for the internal row locator - which non-clustered
indexes use to point to the associated data row - then there are two types
within SQL Server - the clustering key (if the table is clustered) and a
fixed rowid (if the table does not have a clustered index - therefore it's a
heap). The clustering key is easily accessibly. The fixed rowid as part of a
heap consists of an 8 byte key. 2 bytes for the file, 4 bytes for the page
id and 2 bytes for the row offset slot number.
OK - that might be too much information... :)
hth,
kt
--
Please reply only on the newsgroups! Include dml/ddl, when possible.Thanks
Kimberly L. Tripp
President, SYSolutions, Inc. www.SQLSkills.com
Principal Mentor, Solid Quality Learning www.SolidQualityLearning.com
"Roy Wennerod" <rwennerod@.hotmail.com> wrote in message
news:032801c37eda$70fec160$a401280a@.phx.gbl...
> Is there a hidden column in a table that contains a
> unique value for a particular row.
> In Oracle there is a ROWID
> In DB2 there is RRN
> Is there an equivalent value in SQL 2000|||"Roy Wennerod" <rwennerod@.hotmail.com> wrote in message
news:032801c37eda$70fec160$a401280a@.phx.gbl...
> Is there a hidden column in a table that contains a
> unique value for a particular row.
> In Oracle there is a ROWID
> In DB2 there is RRN
> Is there an equivalent value in SQL 2000
No there is not.
David|||"Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
news:OeQq6h9fDHA.2172@.TK2MSFTNGP09.phx.gbl...
> The problem with exposing the internal pointers (whatever a product may
call
> them) is that it makes it impractical to move rows around in the database
> without breaking applications. As long as applications don't have access
to
> this physical pointer, you can do things like split a node in a clustered
> index, or perform an on-line reorganization, and never break an
application.
> That's why SQL Server, like other products that had their origins in the
> so-called second generation of rdbms products, does not expose them. The
> products that have their origins in the first generation, DB2, Rdb, and
> Oracle do expose these pointers.
> It is highly unlikely that SQL Server will ever expose the internal
> pointers. There were extensive discussions on this topic during
development
> of SQL Server 7.0, and the team (including those from Rdb who were fond of
> DBKEYs) concluded that exposing the internal pointers would be a mistake.
> To expose them and retain the online reorganization capabilities would
> require that the pointers be virtualized. And if you virtualize them then
> the implementation is no different then the user defining a new column
(with
> an index), or using an existing key column. So SQL Server's architecture
> relies on not exposing the physical pointers to the user.
Can't you have your cake and eat it too with the sql99 row_number()
function? :)
There's an army of users who are begging for this!:)|||Hal,
Thank you for the quick response...perhaps I should ask for what I am
trying to accomplish.
I have a table that has one nvarchar column. I need to display the
results in the order in which the data was entered into the database.
I was thinking I could use rowid or an equivalent. Any ideas.
Thanks in advance.
"Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message news:<OeQq6h9fDHA.2172@.TK2MSFTNGP09.phx.gbl>...
> The problem with exposing the internal pointers (whatever a product may call
> them) is that it makes it impractical to move rows around in the database
> without breaking applications. As long as applications don't have access to
> this physical pointer, you can do things like split a node in a clustered
> index, or perform an on-line reorganization, and never break an application.
> That's why SQL Server, like other products that had their origins in the
> so-called second generation of rdbms products, does not expose them. The
> products that have their origins in the first generation, DB2, Rdb, and
> Oracle do expose these pointers.
> It is highly unlikely that SQL Server will ever expose the internal
> pointers. There were extensive discussions on this topic during development
> of SQL Server 7.0, and the team (including those from Rdb who were fond of
> DBKEYs) concluded that exposing the internal pointers would be a mistake.
> To expose them and retain the online reorganization capabilities would
> require that the pointers be virtualized. And if you virtualize them then
> the implementation is no different then the user defining a new column (with
> an index), or using an existing key column. So SQL Server's architecture
> relies on not exposing the physical pointers to the user.
> --
> Hal Berenson, SQL Server MVP
> True Mountain Group LLC
>
> "bob" <bob@.hotmail.com> wrote in message
> news:03a701c37edc$800c23d0$a401280a@.phx.gbl...
> > there is a ROWID for each row in SQL Server too, just like
> > in Oracle but it is for only internal use, not exposed to
> > the users for querying. You can't see it or query it as an
> > implicit column. I don't know if MS is planning to expose
> > ROWID to the user querying in future versions of sql
> > server.
> >
> > You have to define a business key as a unique key or use
> > IDENTITY column to uniquely identify each row in a table.
> >
> > >--Original Message--
> > >Is there a hidden column in a table that contains a
> > >unique value for a particular row.
> > >
> > >In Oracle there is a ROWID
> > >
> > >In DB2 there is RRN
> > >
> > >Is there an equivalent value in SQL 2000
> > >.
> > >
Friday, March 9, 2012
Row based security
Hi,
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:
> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:
> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>
Row based security
Hi,
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:
> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:
> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>
Subscribe to:
Posts (Atom)