Date and time tables are commonly found in data models. This includes data warehouse models and models created in BI visualisation tools such as Power BI (PBI). A typical business requirement is to analyse data over a period of time. These tables provide the foundation to do this. In this article I will show how […]
Tag: SQL Server
Table Expressions
Table expressions are queries that represent a relational table in SQL Server. They can be interacted with in the same way you would do a normal table. Table expressions are not persistent objects in the database. When they get used within SQL statements the underlying objects making up the table expression can be inlined within […]
It’s slow, it’s fast, it’s Parameter Sniffing
A classic problem in relational databases. I see this come up again and again. SQL report writers in my department will be stumped when the query they setup for a reporting services report using parameters will run really fast in testing and then be very slow when a specific set of parameters are selected. SQL […]
Dodgy data in a declarative language
Working within the health sector in the UK we have lots of very old systems; some built 20 years + ago. Many have questionable database forms and data validation built into their front end application layers. This can lead to interesting data transformations needed when creating data warehouse data marts. One of these systems stores […]
SQL Window Functions
Window functions allow you to perform calculations against a subset of rows. The result of this calculation is shown against each row. This subset of rows in which you perform the calculation can be thought of as a window. This is achieved by using the SQL OVER clause. Within the OVER clause we can specify […]
Introduction to Transactions in SQL Server
Relational databases are governed by the A.C.I.D principle; these are a set of properties of database transactions to guarantee the integrity of the data. The name ACID is an acronym for the four components that make it up: Atomic. All grouped statements perform as a single unit of work. Either all of them succeed or […]