In most data warehouse implementations, a date dimension is created to standardize dates and make time bucketing easier. In this quick example I will discuss how to create a simple query that can generate a table for a Date Dimension. Creating the Ranges & Session Variables ALTER SESSION SET WEEK_START = 0; SET (START_DATE,END_DATE) = … Continue reading A quick Date Dimension with Snowflake
Character Delimited Lists without using XML Path
Usually when a delimited lists is need in T-SQL, XML Path has been the solution for generating a this delimited list in a single column. With the generation of XML, comes the concatenation needed to add the delimiter and the trimming function to remove the leading or trailing delimiter. Using AdventureWorks2017 database, the following snippet … Continue reading Character Delimited Lists without using XML Path
Dynamic SQL, now what?
Many times as DBA or Developers inherit a database that is using a lot of Dynamic SQL, that nifty tool that developers love and most DBA hate. Dynamic SQL scripts are a way of composing a query by concatenating values and logic into a dynamically generated query. During my career every time I have found … Continue reading Dynamic SQL, now what?
Generate Backup/Restore to URL Azure Data Studio Notebook using PowerShell
In the last few weeks I've found myself using Azure Data Studio more, specially the notebooks to organize my scripts in a more explanatory and aesthetically way. About a year ago I wrote a SQL script that I use to backup and restore databases from IaaS to PaaS (Azure VM to Azure Managed Instance) using … Continue reading Generate Backup/Restore to URL Azure Data Studio Notebook using PowerShell
UTC to Timezone using At Time Zone option.
Let's face it, most of us store dates in UTC and rely on the front end application to handle local time. Now you've been asked to produce a report across customers in different time zones. Starting SQL 2016, Microsoft introduced the option AT TIME ZONE, which produces a Date Time Offset return. A Date Time … Continue reading UTC to Timezone using At Time Zone option.
Using JSON for Lookup Tables
When creating lookup or reference tables during database design we often run into the problem that the data contained by these tables varies within environments. This can cause problems in the application layer, especially if ENUMS are created based on this table. One of the methods of keeping these tables aligned in a Database Project … Continue reading Using JSON for Lookup Tables
SQL Insights 