This is the 158th edition of Log Buffer, the weekly review of database blogs.
SQL Server
Simon Sabin has a TSQL Challenge – counting non zero columns. He says, “I’m working on a project where I need to cycle a flag amongst a set of columns. To achieve this I am storing a position value in each column which allows me to cycle them . . . So the challenge is to find out the how many non zero columns there are, the twist is to use as little code as possible.”
On a cue from Simon, Aaron Bertrand shares a quick experiment in Unicode Compression on SQL Server 2008 R2. ” . . . what is going to happen.” Aaron writes, “is that NCHAR / NVARCHAR . . . columns, in objects that are row- or page-compressed, can benefit from additional compression, where realistically you can cut your storage requirements in half, depending on the language / character sets in use. . . . The difference is astounding: a space savings of roughly 60%, FOR FREE.”
Kimberly L. Tripp is here to tell us, Column order doesn’t matter… generally, but – IT DEPENDS! “SQL Server doesn’t care about the order in which you define the columns of your table because internally SQL Server will re-arrange your columns to store all of the fixed width columns first and the variable columns last. . . . It’s all in the cost of the variable array’s offset values.”
Joe Webb of WebbTech Solutions exposes some some not-so-obvious side-effects UNION in a SQL query.
Aaron Alton, The HOBT, informs us that the Transact-SQL OVER clause is not just for ranking functions. “Prior to the OVER clause, we would have needed to create a derived table which GROUPed the query by our partition columns, then joined said table back to our parent query. This method is much cleaner, and much more efficient . . . ”
Jason “Hutch” Massie blows his cover by revealing his secrets of SQL Server consultant. (Well, not really, but the badge is kind of a giveaway.) Jason writes, “Well, I hope you are not looking for them from me. All of my secrets are common knowledge now. I was hoping you would share yours with us. Just leave them in the comments. I promise not to tell anyone. They can be our little secret, dawg.”
MySQL
On the MySQL Performance Blog, Morgan Tocker explains why you don’t want to shard. (It has nothing to do with The Dark Crystal, I already checked.) A good post—lots of debate and discussion.
You don’t want to shard, but you might want a slice of SQL pie—Shlomi Noach’s SQL-generated pie chart, that is.
Jayant Kumar provides a nice backgrounder on document oriented data stores, and compares some examples—TokyoTyrant, MongoDb, and CouchDb—to MySQL 5.4.
Let’s go back to Morgan Tocker for a moment, and his post helping everyone in understanding the MySQL forks, complete with a handy family tree. Thank you, Morgan.
Mark Callaghan of High Availability MySQL has found a reason to use 5.1: “I can reduce the size of the patch I need to maintain extreme performance with MySQL.” Mark also has some remarks on four new features from InnoDB, Percona, and Google.
Oracle
Jonathan Lewis writes, “Here’s a thought for the weekend . . . ” When people talk about ‘index fragmentation’, what do they mean, and why do they care? . . . “would you let me know what you mean, and how you measure [it]. (I can think of three or four interpretations for the term – but I’m interested to hear from people who actually use it.)”
On So Many Oracle Manuals, So Little Time, Iggy Fernandez shares Great Expectations: An Interview with Tanel Poder, covering, among other subjects, Tanel’s background, Oracle ACE and certification, and OSs.
Let’s stay with Iggy for The Tenth Solution—that is, his own solution to the NoCOUG Challenge.
Here’s Alex Fatkulin demonstrating how to install Oracle Grid Control agents on a Windows failover cluster with no downtime.
On Striving for Optimal Performance, Christian Antognini discusses 11g’s improvements to system managed extent size.
DB2
From his DB2 News and Personal Views, Conor O’Mahony is calling all DB2 users in India. The message? “The third annual IDUG India Conference will take place at the Chancery Pavillion Hotel in Bangalore on 24-26 September.” Click through for more detail.
Other
To close, here is The Rambling DBA, Jonathan Kehayias, with his book review of SQL in a Nutshell. In a nutshell: “I’d recommend this book hands down to anyone who works in one or more of the RDBMS’s covered in the book; SQL Server, Oracle, MySQL, and PostgreSQL.”
That’s all for now. Till next time!
No comments