Fibonacci’s numbers are a sequence of numbers calculated using a recursion pattern that typically lends itself more to procedural programming. This makes it trickier to implement in a well-performing solution in T-SQL, as T-SQL is set-based.
Category: Basics
Migrating a database from Enterprise to Standard Edition?
You can move or copy a database from Enterprise Edition (or Developer Edition, which supports more or less the same feature set) to Standard Edition. The simplest way is to take a backup of the database and restore that on the new server. However, if there are any Enterprise Edition features left in the database, the restored database won’t start up, and you’ll get this error, or something similar:
TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore of database ‘databaseName’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks) ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.SmoExtended) ------------------------------ Database 'databaseName' cannot be started in this edition of SQL Server because part or all of object 'myTableName' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition. Database 'databaseName' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)
Viewing the size of your database objects
Here’s a practical script that I built to view how much space each database object takes up in the database. The script also show you information on how many rows a table (or indexed view) contains, as well as if it’s partitioned and/or compressed.
User options and connection flags
Every user session in SQL Server comes with a number of “flags”, or settings, that you can alter to modify the way SQL Server behaves in different aspects. Some are fairly straight-forward, which others are old legacy options or even completely deprecated and have no actual use.
Identifiers in T-SQL
From time to time, you’re going to be confronted with a SQL Server solution where you need manage column or table names that contain non-alphanumeric characters, like for instance space or percent, or even reserved keywords like “table” or “select”.
Calculating 30/360 day count convention
Calculating the number of days between two dates is a trivial matter in T-SQL if you use the DATEDIFF function. However, how many years (or rather, fractions of years) there are between two given dates is a matter of which method (day count convention) you apply. In financial mathematics, a lot of calculations use a 30/360 convention, where you apply certain rules in order to modify each month to contain exactly 30 days.
The SQL Server security model, part 2: securables
Continuing on last week’s post on security principals, this week’s installment in the series on SQL Server security takes a look at securables.
The SQL Server security model, part 1: principals
There are a number of layers in the SQL Server security model, giving you a nearly infinite number of ways to set up access control on your server and databases. Security is a huge topic, and there are literally entire books on it, so this series of articles is designed to give you just a quick overview of the SQL Server security model to get you started.
In this first installment, I’ll go through the different types of security principals that are available, as well as how they connect to each other.
Building a calendar dimension with public holidays
Whenever you’re building a data warehouse or similar solution, you’ll probably want to have a “calendar dimension”, a table that contains all days in a range of years. A challenge with this type of table is getting all the public holidays right, which could be particularly important if your business depends on this, like financial markets or logistics.
Datawarehouse modelling: Inmon vs Kimball
If you’re into business intelligence, data warehousing and analytics, you will have heard an endless number of references to Bill Inmon and Ralph Kimball. These two figureheads in datawarehousing architecture have produced an immense number of books, articles, training seminars, etc. While many of their strategies and modelling approaches are similar, they have near-opposite views on other aspects.