Introduction In this short article I'm going to show you how to run and connect to a SQL Server instance running on docker. If you've never used Docker before then I highly recommend you give it a try for your next development project. I find docker easy to set up and work with. It removes … Continue reading SQL Server and Docker is easy as 1, 2, 3
In this post I'm going to demonstrate how you can connect to SQL Server in python, read data from a table and load it into a data frame. Prerequisites In order to connect to SQL Server you'll need to ensure you have the correct ODBC Driver installed.You'll also want to pip install the Python packages … Continue reading Connect to SQL Server using Python
In a previous article I demonstrated how you can import XML data using the OPENROWSET() function. In this article I'm going to show how you can do the same but with a JSON file and the OPENJSON() function. Example JSON [ { "id": 1, "product_name": "white paint", "status": "in stock", "orders": [ { "order_id": 1, … Continue reading Import JSON data to SQL Server
In this article I'm going to provide an example of how you can read data from an XML file in SQL Server.To achieve this we're going to use OPENROWSET and OPENXML functions. In this example I'm using the XML sample books which you can obtain from here. Open Row Set Using the open row set … Continue reading Import XML data to SQL Server
In this post I'm going to cover the different types of Slowly Changing Dimensions (SCD). What is a Slowly Changing Dimension? In a Kimball structured star schema you use Slowly Changing Dimensions to associate facts with their original value. This is because sometimes associating a fact with it's changed dimensional value will look odd.For example … Continue reading Slowly Changing Dimension Types
For the occasions that you need to check the backup history of your SQL Instance this script is helpful. /****************** * Title: View Latest Backup History * Notes: Provides detail on the last backups and when they took place. *******************/ SELECT bs.[database_name] AS DatabaseName ,bs.backup_start_date AS DateStart ,bs.backup_finish_date AS DateFinished ,DATEDIFF(MI, bs.backup_start_date, bs.backup_finish_date) AS DurationMin … Continue reading View SQL Server Backup History
Creating a slowly changing dimension (SCD) is an essential part of Data Warehousing. There are many tools for the job but I prefer to do the work using T-SQL against the database rather than using something like SSIS. This will generally involve staging the data from source and then merging the source copy against target. … Continue reading Create a Slowly Changing Dimension Type 2 with T-SQL
