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
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
TCL (Transaction Control Language) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. The main TCL commands are: COMMIT SAVEPOINT ROLLBACK SET TRANSACTION Related References Database Table Field Ordering Effective Practices Structured Query Language (SQL) Tuning What is DDL? What is DML? What … More Database – What is TCL?
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?
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?
What is a primary Key? What a primary key is depends, somewhat, on the database. However, in its simplest form a primary key: Is a field (Column) or combination of Fields (columns) which uniquely identifies every row. Is an index in database systems which use indexes for optimization Is a type of table constraint Is … More Database – What is a Primary Key?
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?
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 DML (Data Manipulation Language)? As the name indicates, Data manipulation for working with information inside a database structure. There are four main DML commands: Select – reading data rows Insert – adding data rows update – changing values within data rows Delete – removing data row Related References 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?
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
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