ROWNUM function

Does SQL Server 2005 or SQL Express have the capability of the ROWNUM function found in Oracle (LIMIT in MySQL)?
please advice!
To select records from row #10 to row #20
Oracle:SELECT *FROM MyTableWHEREROWNUM>9ANDROWNUM<21
MySQL:SELECT *FROM MyTableLIMIT10,20
SQL Server:?
SELECT * FROM MyTable WHERE Row_Number() BETWEEN 10 and 20|||It is not working in SQL Express....why??|||

This one works:

SELECT

OrderID, OrderDate, RowNumberFROM(SELECT OrderID, OrderDate, ROW_NUMBER()OVER(orderby OrderID)as RowNumber

FROM

ORDERS)as tWHERE RowNumberBETWEEN 10 AND 15

Syntax in SQL Server 2005:

ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

sql

Row will not update or delete using Database Explorer (VWD Express)

Hi

I'm trying to update a database table through Database explorer and occassionally when I try to change an entry it will not update or delete. I therefore have a corrupt record which I can't get rid off and therefore can't use the table. The error report is as follows:

"...A problem occurred attempting to delete row 29

Error Source: Microsoft.VisualStudio.DataTools.

Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows)

Correct the errors and attempt to delete the row again or press ESC to cancel the change(s)..."

All columns allow Nulls and there is no primary key or foreign key relationships with other tables.

Can anyone suggest what I am doing wrong or will I just have to resort to data entry using an ASP.Net control such as Detailsview.

Cheers

Chris

Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows)

In SQL Server, there MUST be a way to uniquely identify a row for a DELETE or UPDATE action. IF the row(s) you are attempting to DELETE are duplicate to other rows, you will not be allowed to delete them.

You options are to create either an IDENTITY field in the table, or a PRIMARY KEY -both of which will uniquely identify the row -allowing deletes to occur.

|||

Arnie

Thank you for your quick response and for your clear explanation as to the problem. I can now delete the rows.

Many thanks

Chris

row level security

I am looking for a way to implement row level security on my SQL Server 2005 Express database. Thanks in advance for any input.

Row Level Security Defined

Why do I need row level security?

As an architect, developer, or operator of a computer based information system, you mustprotect the sensitive data that you manage. To protect the data, you must ensure that the users of your system have the authority toperform thefunctions they attempt in your system to view, update, delete, insert, and grant privileges onyour system's data.

Depending on the approach you take, this requirementfor data access securitycan dramatically increase the time and expense associated with building and operating your information system.

Row level security (for purposes of this discussion) is a scheme that enforcesdata access security for the rows in a relational database table via a set ofpermission grants(both read and write)assigned to auser or to agroup of users.

The identity ofa user is established(user authentication)when the user makes a connection to the database. The databasealreadynatively supports access control at the object level (table, view, etc.) and even the table column level, but not at the table row level, making row level security an additional scheme implemented on top of the base database product.

When the row level security scheme is in place and functioning, the userwillonly see the rows to which they have at least read level access. They are onlyable to updateand deletedata ina row if they have write level access to the row. They are only able to insert data in a table if they have been granted insert privileges on the table.

From a development cost perspective, selection of atransparent row level security schemeis extremely important – i.e. theSQLSELECT, INSERT, UPDATE and DELETE statements that run against the tables prior to implementing the row level security scheme continue towork without being modified when the row level security scheme is in place and functioning.

A significant reduction in administrative overhead can be achieved if the row level security scheme supports apermission inheritance model where the rows in a child table automatically inherit the permission grants to the parent row in the parent table. This pattern can be repeated for grand children, great grandchildren, etc. This feature makes it possible to create a grant to a single row in a parent table and automatically grant access to all of the appropriate rows in child tables, grandchild tables, etc.

This type of permission control is referred to asjoin-based security because the permissions for a particular row in a table are determined using relational join criteria to locate and inherit permissions from related rows in another table.

Maintainingand operating the information system is greatly simplified ifdata access securityis enforced by definingpermissiongrants to agroups of users (instead of directly assigning the grants to individual users). This separatespermissions configuration andmembership configuration into two distinct tasks.

You can look athttp://www.datanomad.com orhttp://www.technicalmedia.com for more information on row level security in SQL Server 2005.