Archives for April 2013

Use TPC Database Benchmarks to Save Money

Last month, I began a series of articles describing database application benchmarking. In the first article, I told you about different ways that you can construct your own database application benchmark. However, you don’t have to reinvent the wheel. The Transaction Processing Council (www.tpc.org) has already created a large number of database benchmarks that are extremely useful and informative. I also described last month how the TPC provides several different types of benchmark tests. For example the TPC-C and TPC-E benchmarks are extremely useful for measuring transaction throughput. … [Read more...]

Read the New TPC Database Benchmarking Series

Introduction to TPC Database Benchmarks Let's talk about database application benchmarking. This is a skill set which, in my opinion, is one of the major differentiators between a journeyman-level DBA and a true master of the trade. In this article published in my monthly column at Database Trends & Applications magazine, I'll give you a brief introduction to TPC benchmarks and, in future articles, I'll be telling you how to extract specific pieces of valuable information from the published benchmark results. But let's get started with an overview … read … [Read more...]

PASS Business Analytics Conference (BAC) Recap

The PASS Business Analytics Conference (PASS BAC) is PASS' first foray into an event that is dedicated to business intelligence, big data, data visualization, and business analytics.  And it totally makes sense for PASS to move in this direction, over and above the flagship community work centered on database management and application development.  Why?  Because business analytics is all about how to apply the data being collected and managed by all of those developers and DBAs.  And, at the end of the day, how we use and apply our data is really the nexus of its value.  That's what matters … [Read more...]

The All-New ‘Database Lifecycle Management” is available on MSDN

The initial release of Database Lifecycle Management is now available on MSDN. The site is something called "curated content". This means it's a single consolidated location to look up lots of disparate articles and content, all in one easy to search location. This “curated content view” contains the best content, video, and community-centric information from Microsoft, including topics like: SQL Server Data Tools · Get started with sample projects, code samples · Video demos by Gert Drapers (blog) · Script common data portability tasks using Sqlpackage.exe · Link to the SSDT … [Read more...]

SQL Dialects

Many database vendors provide their own dialect of SQL to supplement the standard.  For example, conditional processing, error handling, and flow-of-control statements are often provided by database vendors as an element of their own dialect of the SQL standard. The ANSI SQL standard SQL/PSM (Persistent Stored Module) package describes many features associated with programming stored procedures and incorporates many of the extensions offered by these dialects.  However, the implementation of SQL/PSM is not consistent or reliable across all database vendors. Some popular dialects of SQL … [Read more...]

SQL History and Implementation

In the early 1970's, the seminal work of IBM research fellow Dr. E. F. Codd led to the development of a relational database product called SEQUEL, or Structured English Query Language. SEQUEL due to some legal hurdles was changed to ultimately SQL, or the Structured Query Language. IBM, along with other database vendors, wanted a standardized method for accessing and manipulating data in a relational database. Although IBM was the first to develop relational database theory, Oracle is widely credited with being first to market and popularize the technology. Over time, SQL proved popular … [Read more...]

Introduction to the Relational Model and ANSI SQL

Relational database management systems (RDBMS) such as those covered on this website are the principal engines of structured data systems worldwide. They enable a multitude of users to quickly and simultaneously access, create, edit, and manipulate data without impacting other users. And they enable developers to write useful applications to access their data while providing administrators with the consistent and reliable capabilities they need to maintain, secure, and optimize organizational data resources. An RDBMS may be defined as a system whose users view data as a collection of tables … [Read more...]

ALL Operator

The ALL operator returns True when all of the records in query result set pass the Boolean condition. A bit more explanation.  The ALL statement gives a TRUE value when: 1) the subquery returns no records, or 2) all records meet the comparison.  ALL will return false if even one record fails the Boolean condition.  If any of the values are NULL, then the operation value will be NULL, not TRUE. For example, we want to see which authors currently have no titles: SELECT au_id FROM authors WHERE au_id <> ALL(SELECT au_id FROM titleauthor) Syntax SELECT … WHERE expression comparison … [Read more...]

BETWEEN operator

BETWEEN performs a Boolean True/False test to determine whether or not a value falls within a particular range, including the values mentioned in the BETWEEN clause.  BETWEEN returns TRUE when in the range, FALSE when not in the range, and NULL if any range values are unknown. It can't be used with data type like BLOB, CLOB, NCLOB, REF, and ARRAY. Syntax SELECT … WHERE expression [NOT] BETWEEN bottom_of_range AND top_of_range Explained WHERE expression Compares an expression, that is, a single value like a column value or a literal expression, to the values … [Read more...]

Codd’s Rules for Relational Database Systems

E.F. Codd applied rigorous mathematical theories (primarily set theory and relational algebra) to the management of data, and he compiled a list of criteria a database must meet to be considered relational. At its core, the relational database concept centers around storing data in tables. This concept is now so common as to seem trivial; however, not long ago the goal of designing a system capable of sustaining the relational model was considered a long shot with limited usefulness. Following are Codd’s Twelve Principles of Relational Databases: 1.     Information is represented logically … [Read more...]