Notes on SQL 3

Previous related posts:

• Notes on SQL 1


• Notes on SQL 2

INSERT Statement

To cause DEFAULT constraints to "execute" on a table while inserting, it is possible to do this: INSERT INTO Table VALUES ('value 1', 'value 2', DEFAULT). Additionally, specifying all the columns in the table removes the need to explicitly state the column name for each value.

For batch insertion into an existing table: INSERT SELECT; for temporary/permanent table creation: SELECT INTO.

Advanced DML - OUTPUT Clause

Allows things like these (for INSERT, UPDATE and DELETE statements):

DELETE FROM [Table]
OUTPUT deleted.* INTO [output_table]
WHERE Condition

However, there are limitations: output_table cannot

• Have enabled triggers defined on it.


• Participate on either side of a foreign key constraint.


• Have CHECK constraints or enabled rules.

Advanced DML - MERGE Statement

The syntax should be clear enough as to what this does. I couldn't find much worth mentioning without going into too much detail:

[ WITH <common_table_expression> [,...n] ]
MERGE
        [ TOP ( expression ) [ PERCENT ] ]
        [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
        USING <table_source>
        ON <merge_search_condition>
        [ WHEN MATCHED [ AND <clause_search_condition> ]
            THEN <merge_matched> ] [...n ]
        [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
            THEN <merge_not_matched> ]
        [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
            THEN <merge_matched> ] [...n ]
        [ <output_clause> ]
        [ OPTION ( <query_hint> [ ,...n ] ) ]

Transactions

When implicit transactions is set to ON (SET IMPLICIT_TRANSACTIONS {ON|OFF}), every one of the following statements starts a transaction: ALTER TABLE, CREATE, DELETE, DENY, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE, UPDATE.

Note on Rollbacks: "Although a ROLLBACK statement returns the data to its prior state, some functionalities, such as seed values for identity columns, are not reset."

With nested transactions, a ROLLBACK goes all the way to the outermost transaction (even if COMMITS were done for any of the inner ones).

The SAVE TRANSACTION savepoint_name statement allows partial rollbacks, if a savepoint_name is specified during rollback. However, it cannot be used with distributed transactions (damn it, CDR!).

When transactions deadlock, SQL Server chooses a victim based on the estimated cost of rolling back each one, and a 1205 error is issued.

Locking

"Locks can be assigned to resources such as rows, pages, indexes, tables and databases".

Locking modes:

Shared (S): placed on resources for read (SELECT) operations. They are compatible among them, but not with exclusive locks. If the isolation level is REPEATABLE READ or higher, the lock is kept throughout the transaction instead of just during the read.

Update (U): "placed on resources where a shared (S) lock is required, but the need to upgrade to an exclusive (X) lock is anticipated." ONLY ONE TRANSACTION CAN OBTAIN A "U" LOCK ON A RESOURCE AT A GIVEN TIME. This is to prevent locking deadlocks when two or more transactions have a "S" lock on a resource, that they want to upgrade to an "X" lock. But since each one has to wait for the other to release the "S" lock first, deadlock occurs.

Exclusive (X): for data modification. Not compatible with any other kind of lock, and can only be "overridden" by a NOLOCK hint or the READ UNCOMMITED isolation level.

Intent (IS, IX, SIX): from TechNet:

"The Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.

Intent locks serve two purposes:

• To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.


• To improve the efficiency of the Database Engine in detecting lock conflicts at the higher level of granularity."

Schema (Sch-M, Sch-S): they stand for Schema Modification, and Schema Stability. Obvious meaning for the first (e.g. adding a new column to a table), while the second ones "are placed on objects while queries are being compiled or executed". Need more info on this one...

Bulk Update (BU): used for bulk insert.

Key-range: again from TechNet:

"Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level. Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction."

???

Transaction Isolation Levels

Set with the SET TRANSACTION ISOLATION LEVEL statement.

READ UNCOMITTED: allows reading what other transaction has inserted but not committed.

READ COMMITED: prevents dirty reads (the previous case). This is the default.

REPEATABLE READ: no dirty reads, and ensures that shared locks are maintained until the transaction completes.

SNAPSHOT: (requires the ALLOW_SNAPSHOT_ISOLATION option). Literally takes a snapshot of the data that will be read, and consequently does not issue any locks. It's still not clear to me if data modification is done only "locally" on the snapshot or how is it handled...

SERIALIZABLE: total blocking. No one can read what the transaction has modified, and no one can modify what the transaction has read.

Other notes

Tables without a clustered index are stored in a heap.

"Bound sessions allow two or more sessions to share the same transaction and locks, and can work on the same data without lock conflicts." Cool!

Click here for the next part in this series of posts.

"On thinking for oneself"

Ensayo de Arthur Schopenhauer con el que me sentí parcialmente identificado (and I must do something about that), y que —aunque tal vez un poco arrogante— me gustó bastante.

Here.

Bitten apple + rainbow = Alan Turing ?

Encontré sin querer este comentario mientras veía este video de YouTube (parte 1 acá):

@Bobbiethejean
The Apple logo for Apple computers was inspired by Turing's death. Apple = Poisoned apple + Rainbow colours = his homosexuality.

Hace poco me mandaron un mail que decía que los grafos que usamos actualmente para los números eran así por la cantidad de ángulos que tienen, pero como los ejemplos se me hicieron medio far-fetched y forzados no me la creí. Wikipedia luego confirmó mis sospechas. Pero la teoría del logo de Apple no tiene nada forzado y hasta tiene cierto sentido, con el papel de Turing en la historia de la computación. Pero again, para aclararlo recurrí a Wikipedia, que dice esto:

Apple’s first logo, designed by Jobs and Wayne, depicts Sir Isaac Newton sitting under an apple tree. Almost immediately, though, this was replaced by Rob Janoff’s “rainbow Apple”, the now-familiar rainbow-colored silhouette of an apple with a bite taken out of it. Janoff presented Jobs with several different monochromatic themes for the "bitten" logo, and Jobs immediately took a liking to it. While Jobs liked the logo, he insisted it be in color, as a way to humanize the company.^[122][123]

The original hand drawn logo features Sir Isaac Newton, and one theory states that the symbol refers to his discoveries of gravity (the apple) and the separation of light by prisms (the colors). Another explanation exists that the bitten apple pays homage to the mathematician Alan Turing, who committed suicide by eating an apple he had laced with cyanide.^[124] Turing is regarded as one of the fathers of the computer. The rainbow colors of the logo were rumored to be a reference to the rainbow flag, as a homage to Turing's homosexuality.^[125]

However, Rob Janoff stated in an interview that the alternate theories are all wonderful urban legends, but, unfortunately, "B.S." The Apple logo was designed with a bite for scale, so that people would recognise that it was an apple, not a cherry, and the rainbow color was not a coded reference to homosexuality or prism light, but was conceived to make the logo more accessible and represent the fact the monitor could reproduce images in color ^[126].

Ni modo, si el diseñador dice que no es por eso, qué se le hace jeje. Cool coincidence though. E interesante fact el del primer diseño del logo, que se ve bastante old-school.

Notes on SQL 2

Previous related posts:

• Notes on SQL 1

Aggregate functions I didn't know about

CHECKSUM_AGG: checksum of all values in the dataset.

COUNT_BIG: same as count but return bigint

GROUPING: returns 0 or 1 to specify detail or aggregate row in certain uses of the GROUP BY clause

STDEV/STDEVP: standard deviation and standard deviation for population (still not very clear what this does...)

VAR/VARP: variance and variance for population (still not very clear what this does...)

Additions to the GROUP BY clause

WITH ROLLUP: adds result rows that specify the "summary" of an aggregate function like if the GROUP BY hadn't been applied. E.g., if I group products by CategoryId and request the average cost (SELECT AVG(Cost) FROM Products GROUP BY CategoryId) , I will get a column with each Category identifier and another with the corresponding average cost. If I add WITH ROLLUP at the end, an extra row will appear with the average cost for ALL products across all categories. This extends to several columns in the GROUP BY clause, and in that case each grouping level gets its own summary rows.

WITH CUBE: similar to WITH ROLLUP, but creates summaries as if the GROUP BY clause had been applied to each column individually (maybe each pair of columns too?). These two seem like a minor touch of Business Intelligence over basic SQL Server queries.

Grouping Sets (added in SQL Server 2008)

They provide clearer syntax when requesting aggregate data over several different groupings. This is useful if you have a table which you can group independently by several different columns, and want to see the summaries for each possible grouping in a single query.

SELECT CategoryID, SubCategoryID, AVG(Price) FROM Products GROUP BY GROUPING SETS ( (Products.CategoryID), (Products.SubCategoryID) )

UNION, INTERSECT, EXCEPT

They exist! I thought they only did in Oracle. Minor (expected) nuances like having to match the number and type of columns in both sides of each operator. Regarding efficiency against other operators that provide equivalent functionality (e.g., EXCEPT VS NOT IN), some sources claim that the execution plans are exactly the same, so the clearer syntax should be used.

APPLY Operator

You select some rows from some table, and then use one (or more? probably) of the columns in each row as parameters for a table-valued function, from which you extract additional information. Comes in 2 versions: CROSS APPLY, and OUTER APPLY. The second one returns all rows from the original table, with NULL in the rest of the columns if the table-valued function did not return any data. If the table-valued function returns several rows, the behavior expected from a regular JOIN still applies.

Scalar-valued functions

DATEDIFF(datepart, startdate, enddate) -> startdate gets substracted from enddate. Minor reminder.

DATALENGTH(expression) -> returns the number of bytes required to represent expression.

CHARINDEX(expression1, expression2, [start_location]) -> returns the index of the first instance of expression2 inside expression1, starting to look at start_location.

PATINDEX('pattern', expression) -> similar to CHARINDEX but allows for wildcards.

Click here for the next part in this series of posts.

Interesting passages from "This is your brain on music"

Just some extracts from the book that I liked and happened to take note of:

Regarding sound production:

The introduction of energy to an instrument - the attack phase - usually creates energy at many different frequencies that are not related to one another by simply integer multiples. In other words, for the brief period after we strike, blow into, pluck, or otherwise cause an instrument to start making sound, the impact itself has a rather noisy quality that is not especially musical - more like the sound of a hammer hitting a piece of wood, say, than like a hammer hitting a bell or a piano string, or like the sound of wind rushing through a tube. Following the attack is a more stable phase in which the musical tone takes on the orderly pattern of overtone frequencies as the metal or wood (or other material) that the instrument is made of starts to resonate. This middle part of a musical tone is referred to as the steady state - in most instances the overtone profile is relatively stable while the sound emanates from the instrument during this time.
After Schaeffer edited out the attack of orchestral instrument recordings, he played back the tape and found that it was nearly impossible for most people to identify the instrument that was playing. Without the attack, pianos and bells sounded remarkably unlike pianos and bells, and remarkably similar to one another. If you splice the attack of one instrument onto the steady state, or body, from another, you get varied results. In some cases, you hear an ambiguous hybrid instrument that sounds more like the instrument that the attack came from than the one the steady state came from. Michelle Castellengo and others have discovered that you can create entirely new instruments this way; for example, splicing a violin bow sound onto a flute tone creates a sound that strongly resembles a hurdy-gurdy street organ. These experiments showed the importance of the attack.

Regarding sound identification/discernment:

One case of auditory grouping is the way that the many different sounds emanating from a single musical instrument cohere into a percept of a single instrument. We don't hear the individual harmonics of an oboe or of a trumpet, we hear an oboe or we hear a trumpet. This is all the more remarkable if you imagine an oboe and a trumpet playing at the same time. Our brains are capable of analyzing the dozens of different frequencies reaching our ears, and putting them together in just the right way. We don't have the impression of dozens of disembodied harmonics, nor do we hear just a single hybrid instrument. Rather, our brains construct for us separate mental images of the two of them playing together - the basis for our appreciation of timbral combinations in music. This is what Pierce was talking about when he marveled at the timbres of rock music - the sounds that an electric bass and an electric guitar made when the were playing together - two instruments perfectly distinguishable from one another, and yet simultaneously creating a new sonic combination that can be heard, discussed, and remembered.

Regarding the function of the auditory system:

Imagine that you stretch a pillowcase tightly across the opening of a bucket, and different people throw Ping-Pong balls at it from different distances. Each person can throw as many Ping-Pong balls as he likes, and as often as he likes. Your job is to figure out, just by looking at how the pillowcase moves up and down, how many people there are, who they are, and whether they are walking toward you, away from you, or are standing still. This is analogous to what the auditory system has to contend with in making identifications of auditory objects in the world, using only the movement of the eardrum as a guide.

Regarding emotions in music:

Composers imbue music with emotien by knowing what our expectations are ande then very deliberately controlling when those expectations will be met, and when they won't. The thrills, chills, and tears we experience from music are the result of having our expectations artfully manipulated by a skilled composer and the musicians who interpret that music.

"Jesus" action figure

Jesus action figure! Cures leprosy and restores sight to the blind. Requires 2 AA bateries.¹

¹ Not included

Best. Toast. Ever.

- Person #1: ... por el poder de...!

- Everybody: ... [wtf?]

- Person #2: Thunder! ... Thunder! ... Thundercats!

[Everybody raises their glasses]

- Everybody [while clinking their glasses] HOOOOOOOOOO!

I love my group of friends =D.

Tool fail

Por esto no hay que usar una botella de plástico como martillo para golpear una aguja:



Y menos si tiene agua mineral adentro.

Analyzing the world through their search queries

Prioridades (Brasil):



Sin qué hacer (Canada):



Sospechoso (República Dominicana):



Otros sin qué hacer (Hong Kong):



Consistencia (Suecia):

(btw según Google Translate, systembolaget significa "systems company"... who knows)

One-hit-wonder hysteria:



And finally, media hype (global):

Donald Knuth on his writing style

Yes, I always try to keep a potential reader in mind - basically somebody who is reading my book because they want to, not because they must, and somebody who has a natural ability to do computer programming. Only about 2 per cent of the population really "resonates" with programming the way I do; but somebody ought to write books for that 2%, and I try to be one of the authors who does so. Instead of trying to impress the reader with what I know, I try to explain why the things I've learned impress me. [My emphasis]

Full interview here.