ANSI Standards – Database – Substring

Background Reviewing some SQL and wanted to point out a tiny dissimilarity between database platforms. Data Our test table is Oracle's SCOTT.EMP Here is what the data looks like :- Substring/Substr The HIREDATE is the only well formatted fixed length string. Its format is DD-MMM-YY. Query Oracle SQL Output Explanation Oracle relies on Substr to … Continue reading ANSI Standards – Database – Substring

“Entity Relationship” meets poetry

Background Researching an issue and wanted to see the best choice for a name.  Googled on it and found good reasoning. Question The Question is :- Image Text Table: Color Columns: Id, ColorName, ColorCode Table: Shape Columns: Id, ShapeName, VertexList What should I call the table that maps color to shape? Table: ??? Columns: ColorId, … Continue reading “Entity Relationship” meets poetry

MySQL :- Secondary Indexes and the Clustering Keys – Day 2

Preface In our last post, we spoke about how one might not need to add the Clustering columns when defining a Secondary Index. At that point, we touched on the fact that just reviewing the Query Plan via "Visual Explain" might not fully reveal whether the Index used is "covering" or whether the Clustered Index is … Continue reading MySQL :- Secondary Indexes and the Clustering Keys – Day 2

MySQL :- Secondary Indexes and the Clustering Keys

Preface RDMS Database tables can either be stored as a Heap or Clustered. When stored as a Heap, data is appended as they come in.  When Clustered, data is sequenced based on the Clustering Columns. Secondary Indexes For Clustered tables, the Clustering data is written as an additional data on each record.  On the other hand … Continue reading MySQL :- Secondary Indexes and the Clustering Keys

Transact SQL – Constraint – Primary Key

There are a couple of approaches one can use to get the primary key for a table. Here are some of those ways: sp_pkeys sp_primarykeys INFORMATION_SCHEMA.TABLE_CONSTRAINTS sp_pkeys Syntax: Sample: Output: sp_primarykeys Though sp_primarykeys was added to gain insight into remote data sources, you can use it it to query your local data source, as well. … Continue reading Transact SQL – Constraint – Primary Key