15

You're going to have to make your application not put the ORDER BY inside the subquery (maybe it has an option to not use a needless subquery in the first place). As you've already discovered, this syntax is not supported in SQL Server without TOP. And with TOP, unless you want to leave some rows out, using TOP 100 PERCENT is going to render the ORDER BY ...


9

SQL Server uses checkpoint, log sequence numbers, and the transaction log to guarantee that a full backup is restored to a transactionally-consistent state, as of a particular point in time. The exact point in time for a full database backup is the end of the data-reading portion of the backup process. To recover to an arbitrary point in time requires ...


8

Ensure you have a valid backup; hopefully it will be from prior to the corruption, but not so long ago that the data isn't useful. You should set this aside in case direct repair isn't possible and you need to recover data from the backup. The documentation explains what to do to correct the problem - you can try the REPAIR_REBUILD option, and if that doesn'...


7

You are right, the design allows inconsistencies, exactly what you notice. A ProjectRealm may be referring through Project to a company and through CompanyRealm to another company. This is not uncommon, it appears when there is a triangular or a "diamond" shape in the relationships: Realm Company \ / \ \ / \ ...


7

Which "same result" do we have in the example? Which reordering can we have to produce the same result? The opening quote is an incorrect statement of serializable isolation. The definition of the serializable isolation level in the SQL-92 standard contains the following text (emphasis mine): A serializable execution is defined to be an execution of the ...


6

I think the difference is that the two terms refer to different things: Consistency refers to Transactions (it's the C in ACID). Integrity refers to Databases. A transaction is said to be consistent if it keeps the integrity of the database (i.e. if it transforms the database from a valid state to another valid state).


5

CAP and ACID are discussing different things, though both use the C for Consistency. Read John Huggs's post: https://www.voltdb.com/blog/disambiguating-acid-and-cap ACID is defining what it takes to make a what is normally described as making a "true transaction" fulfilling the ACID goals. In actuality, it seems that the C for Consistency is more for the ...


2

It looks like your namespace file is still corrupt, you get similar errors when you hit the max number of collections for a database in MMAPv1. To try to fix this up and get a database that is in a more consistent state you can try recreating your collections in a new database. If you have auth enabled, you will need to do this in the admin database - note ...


2

As per the definitions you've quoted in your question description, assertions are logical tests that trigger exceptions on failure. These are used within most automated testing suites as well as at runtime. In test suites assertions confirm that the results of a function call or data update are as expected and are often set up as macros indicating the ...


2

I have zero to none experience in the world of NoSQL so I'll add some perspective from a RDBM:s side of view. I believe assertions where introduced in SQL92, the following is an example from: https://mariadb.com/kb/en/sql-99/create-assertion-statement/ CREATE ASSERTION constraint_1 CHECK ((SELECT AVG(column_1) FROM Table_1 >40) NOT DEFERRABLE; ...


2

With having QUORUM for both read & write requrests you achieve strong consistency per following equation R + W > N, where R - read replica count, W - write replica count, and N - replication factor. If you write with QUORUM, then 2 nodes will get fresh data, and when you read with QUORUM, then one node may have the old data, and one will have fresh ...


2

Run the following command to track the corrupt pages: SELECT * FROM msdb.dbo.suspect_pages Review the suspect pages. Run DBCC CHECKDB command to check the inconsistencies of the database. Please check the PAGE_VERIFY CHECKSUM option and turn it on. Note: Also check the hardware of your machine.


2

C stands for eventual consistent. Correct ? Consistency in the CAP theorem is referring to strong consistency where every read receives the most recent write or an error. By default MongoDB drivers direct all reads & writes to the primary of a replica set, which is strongly consistent. The CAP theorem asserts that a distributed system must choose ...


2

The effects of a trigger 'belong' to the statement that triggered it, so everything will be rolled back together (with or without explicit transactions): > create table a(x int); > create table b(y int check(y > 0)); > insert into b values(1); > create function t() returns trigger as $$ begin update b set y = -1; return null; end; ...


2

Your question seems to be logically inconsistent, as tolerating a latency in persistence and "Actually I can afford to lose transactions that are in memory only" by definition violates the "D" in "ACID". If you really want to be just "ACI" compliant, you can turn off "synchronous_commit". Note that this means you could report to the traders that their ...


1

For SQL Server: But what's about single statements? In SQL Server, there is 4 transaction isolation levels (in pessimistic locking model). Default transaction isolation level is Read Committed, and locks are placed on a statement level. If you have a transaction that has 2 statements inside it that retrieve same data, and in the middle of that ...


1

I think you are correct. We need at least two distinct subnet to talk about network partitioning. But if your 10 nodes are in the same subnet, what is the point ? If your network is down, you loose your cluster ! Best practice would be to have 5 nodes in one datacenter, 5 nodes in another data center.


1

Mostly yes, but with small correction. You can get the stale data if they aren't replicated yet to another node (replication happens almost at the same time, but still could be delayed because of garbage collection, etc.). You can get strong consistency by tuning consistency levels... The comment is about point 3c - Cassandra's partitions are replicated to ...


1

After reading this answer I would suggest you to read Paul Randal's blog who is expert in corruption issues. You are most likely to find better answer on his blogs. Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:24694884) contains an incorrect page ID in its page header....


1

I've never had a corrupt page before. Can you provide any information from the dbo.suspect_pages table? Please see the below resources: Brent Ozar on Corruption SQL Server Central Corruption


1

There can be several reasons that cause Database Logical Consistency based I/O error: Unexpected system shutdown/crash or forced shutdown SQL administrator tries to query or modify SQL data This article on SQL Server Central about logical consistency based error in SQL is amazingly written by SQL DBA.


1

Here's a great article on recovering a faulty db: www.sqlservercentral.com/articles/Corruption/65804/ Firstly it advises things like: don't panic, don't restart the server, don't run a DBCC CHECKDB(DB_NAME, REPAIR_ALLOW_DATA_LOSS) Then takes you through common fixes to common errors in the database, such as: - Inaccurate space metadata - Corruption only ...


1

Transaction atomicity implies consistency This is not true. A transaction that adds an order for a nonexistent customer is atomic, but makes data inconsistent. To follow your example, a consistent bank transaction must guarantee that the sum of all debits and credits is exactly 0. An atomic transaction must only guarantee that either all necessary tables, ...


1

What you're seeing is completely unacceptable, and developers should not be expected to work around it. You've suppressed it in your answer, and I don't know if you didn't notice it, or didn't consider it significant, or something is broken in your setup. Here's what you should have seen: mysql> insert into t1 values (uuid_short()); Query OK, 1 row ...


Only top voted, non community-wiki answers of a minimum length are eligible