TAILIEUCHUNG - SQL Antipatterns- P6

Tham khảo tài liệu 'sql antipatterns- p6', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | Objective Tidy Up the Data 251 Objective Tidy Up the Data There s a certain type of person who is unnerved by a gap in a series of numbers. bug_id status product_name 1 OPEN Open RoundFile 2 FIXED ReConsider 4 OPEN ReConsider On one hand it s understandable to be concerned because it s unclear what happened to the row with bug_id 3. Why didn t the query return that bug Did the database lose it What was in that bug Was the bug reported by one of our important customers Am I going to be held responsible for the lost data The objective of one who practices the Pseudokey Neat-Freak antipattern is to resolve these troubling questions. This person is accountable for data integrity issues but typically they don t have enough understanding of or confidence in the database technology to feel confident of the generated report results. Antipattern Filling in the Corners Most people s first reaction to a perceived gap is naturally to want to seal the gap. There are two ways you might do this. Assigning Numbers Out of Sequence Instead of allocating a new primary key value using the automatic pseudokey mechanism you might want to make any new row use the first unused primary key value. This way as you insert data you naturally make gaps fill in. bug_id status product_name 1 OPEN Open RoundFile 2 FIXED ReConsider 4 OPEN ReConsider 3 NEW Visual TurboBuilder Repor erratum this copy is printing May 2010 Antipattern Filling in the Corners 252 However you have to run an unnecessary self-join query to find the lowest unused value Download Neat-Freak anti SELECT 1 FROM Bugs bl LEFT OUTER JOIN Bugs AS b2 ON 1 WHERE IS NULL ORDER BY LIMIT 1 Earlier in the book we looked at a concurrency issue when you try to allocate a unique primary key value by running a query such as SELECT MAX bug_id 1 FROM This has the same flaw when two applications may try to find the lowest unused value at the same time. As .

TỪ KHÓA LIÊN QUAN
Đã phát hiện trình chặn quảng cáo AdBlock
Trang web này phụ thuộc vào doanh thu từ số lần hiển thị quảng cáo để tồn tại. Vui lòng tắt trình chặn quảng cáo của bạn hoặc tạm dừng tính năng chặn quảng cáo cho trang web này.