Contents tagged with sql
-
Fixing up the archive counts in Orchard
For reasons best known to itself, sometimes Orchard gets a little count happy with the number of posts in a given archive year/month and over exaggerates the number of posts present. Fortunately …
-
Using Common Table Expressions to explode recipes - Part 2 - Getting the ingredients
A Brief Recap
In the last part of this article I showed a table structure for recipes and their ingredients. There are two kinds of ingredients that a recipe can contain, raw ingredients (e.g. …
-
Using Common Table Expressions to explode recipes - Part 1 - The CTE
A lot of the examples that you see for using Common Table Expressions (CTEs) in SQL Server, particularly recursive ones use examples that are around user hierarchies - showing ways to find all the …
-
Using Sequence Numbers in SQL Server
Microsoft introduced the concept of Sequence Numbers in SQL Server 2012 which are:
... a user-defined schema-bound object that generates a sequence of numeric values according to the specification …
-
Using ROW_NUMBER to get the most recent record from a table
Quite a while ago I wrote about using SQL Server CLR to add "earliest" and "latest" aggregate functions that could be used to retrieve the, well, earliest and latest Id values from a table based on …
-
What's an Estimated Subtree Cost?
When you're looking at the execution plan for a query (you do that, right?) one of the data-points that's shown is "Estimated Subtree Cost". Looking at this is one of the things I review when I've …
-
Creating a linked server that has a different name to the "real" server
It's sometimes useful to be able to have a linked server where the name is misleading. One example of this could be where you don't have a perfectly isolated test or staging infrastructure so you …
-
Quick Tip: Randomising a resultset from a SQL query
Sometimes I need a random (random enough for my purposes - I don't know, nor care, if it meets a formal definition of random) sample of N rows of data from a table. There's a very quick way to do …
-
Always make sure you specify the length to CONVERT in SQL
Over the years I've seen this potential blackhole of data loss several times, a developer has written CONVERT(VARCHAR, [ColumnName]) without specifying the length of the VARCHAR to convert the column …
-
SQL Server 2016 syntax for dropping objects
SQL Server 2016 introduced a new syntax for dropping objects, that I've known about for quite a while. I've been surprised by the number of people that aren't familiar with it so thought I'd put …