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
Database ( Technical )
SQL Beautifier
Background As a Database Administrator get so see a bit of SQL Statements. Avenues The SQL gets to the DBA through various avenues, inclusive :- Developers Submitted to review and execute against Production Database Monitoring Tools In quite a few cases, the first thing one has to do is space things out a bit. SQL … Continue reading SQL Beautifier
“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
DBeaver
Background Here I am having used SQL Server Management Studio ( SSMS ) for so long, I am satisfied. It's GUI is versely superior to SQL 2000 Query Analyzer and it exposes just about every SQL Functionality that I use on daily basis. Stumped Spent hours trying to capture a query's grid display unto … Continue reading DBeaver
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
