Oracle TO_CHAR to SQL Server CONVERT Equivalents to change Date to String

When it comes to SQL I tend to lean on the SQL I have used the most over the years, which is Oracle.  Today was no exception, I found myself trying to use the TO_CHAR command in SQL Server to format a date, which of course does not work. So, after a little thought, here … More Oracle TO_CHAR to SQL Server CONVERT Equivalents to change Date to String

SQL server table Describe (DESC) equivalent

Microsoft SQL Server doesn’t seem have a describe command and usually, folks seem to want to build a stored procedure to get the describe behaviors.  However, this is not always practical based on your permissions. So, the simple SQL below will provide describe like information in a pinch.  You may want to dress it up … More SQL server table Describe (DESC) equivalent

Database – What is a foreign key?

Definition of a Foreign Key A foreign Key (FK) is a constraint that references the unique primary key (PK) of another table. Facts About Foreign Keys Foreign Keys act as a cross-reference between tables linking the foreign key (Child record) to the Primary key (parent record) of another table, which establishing a link/relationship between the … More Database – What is a foreign key?

Database – What is a Composite Primary Key?

What is a Composite Primary Key? A Composite Primary key is Primary key What a primary key, which is defined by having multiple fields (columns) in it.  Like a Primary Key what a composite Primary Key is depends on the database.  Essentially a Composite Primary Key: Is a combination of Fields (columns) which uniquely identifies … More Database – What is a Composite Primary Key?

Database – What is DDL?

What is DDL (Data Definition Language)? DDL (Data Definition Language), are the statements used to manage tables, schemas, domains, indexes, views, and privileges.  The the major actions performed by DDL commands are: create, alter, drop, grant, and revoke. Related References What is DCL? What is DML? Database Table Field Ordering Effective Practices Structured Query Language (SQL) … More Database – What is DDL?

Data Modeling – Fact Table Effective Practices

Here are a few guidelines for modeling and designing fact tables. Fact Table Effective Practices The table naming convention should identify it as a fact table. For example: Suffix Pattern: <<TableName>>_Fact <<TableName>>_F Prefix Pattern: FACT_<TableName>> F_<TableName>> Must contain a temporal dimension surrogate key (e.g. date dimension) Measures should be nullable – this has an impact on … More Data Modeling – Fact Table Effective Practices

What is DCL?

What is DCL (Data Control Language)? Data control language (DCL) is anything that is used for administrating access (permissions/security) to database content.  The main DCL commands are: Grant Revoke Related References Database Table Field Ordering Effective Practices Structured Query Language (SQL) Tuning What is DDL? What is DML?

[HY000] ERROR: Base table/view has changed (datatype); rebuild view

Error Message ERROR [HY000] ERROR: Base table/view ‘BLOG_LIST_TBL’ attr ‘BLOG_ID’ has changed (datatype); rebuild view ‘BLOG_LIST_VW’ What does the Error Mean? This error means that an underlying table used by the view has changed in a way, which made the view invalid, as a result, the view must be rebuilt to reflect the new table … More [HY000] ERROR: Base table/view has changed (datatype); rebuild view

Oracle SQL – Removing Non-Numeric Characters

Sometimes, when converting fields from text to numeric fields in SQL, there is a need to remove one or more non-numeric values.  These approaches have been useful for handling multiple non-numeric values. Removing non-numeric values – Approach 1: I have found this approach useful when working with character (Char, Varchar, NVARCHAR) fields being converted to non-integers outputs … More Oracle SQL – Removing Non-Numeric Characters