-
Notifications
You must be signed in to change notification settings - Fork 8
/
sql_database.txt
52 lines (45 loc) · 2.11 KB
/
sql_database.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
HoBT: Heap or B+Tree (generic term for index or lack thereof)
clustered index: each data page organized in a linked list
B-Tree
each node points to n child nodes (unlike binary tree)
B+Tree
SQL Server uses this for indexes
efficient for inserts, deletes
each level is a doubly-linked list
level 0 = leaf (no leaves at any other level)
Heap = table that _lacks_ a [clustered] index
points to row (RID) directly
one HoBT per partition (currently):
sys.partitions.hobt_id == sys.partitions.partition_id
actully aliased from sys.sysrowsets.rowsetid
thehobt.blogspot.com/2009/02/what-heck-is...
Clustered index leaf
stores the actual column data (at leaf level)
only 1 clustered index per table, so all other [non-clustered] index leaves store the clustered key
footnote: you can actually add "included columns" indexes which in effect create another clustered key
on a given table, which effectively duplicates the table data--so this is generally advised against.
sorted, so moves have a cost
Nonclustered index leaf
store RID or clustered key
non-sorted, so moves are cheap
3 ways for SQL server to read data
scan
table scan
used for heaps only
needs to read ALL pages to satisfy WHERE clause
clustered index scan
reads almost all pages
seek
clustered index seek
lookup
================================================================================
sqlite
tradeoffs:
- No foreign keys by default. Every connecting session must toggle it on.
- One writer. Any session that issues BEGIN TRANSACTION and then hangs halts all DML.
- WAL mode confusion. WAL cannot safely be used on network filesystems, and it breaks ACID on ATTACHed databases, among other problems.
- No date/time types.
- Length specifications on a column are ignored. CHAR(2) will allow the insertion of a blob.
Check constraints could be used to enforce this.
- Type affinity => any data type can be inserted into columns declared as any other data type.
Type enforcement can be done, but not the default.