Create Database Diagrams of Your Azure SQL Databases

Do you miss Database Diagrams?

I find them to be indispensable when I join new projects, because they provide insight that makes my on-boarding much smoother.

As my databases are hosted on the Azure SQL Database Service, I am not able to create Database Diagrams using tools like Microsoft SQL Server Management Studio or Visual Studio 2013. Finding this to be a pain, I decided to look around for tools that were able to create Database Diagrams.

Of course my first reflex at that time, was to generate an EDMX using Entity Framework and Visual Studio. This was an ok solution for short glimpse of the database. The EDMX is great at helping me grasp the data model, but it hides a few important details.

 

Building Clustered Indexes on Non-Primary Key Columns in Azure SQL Database

Azure SQL Database doesn’t support Heap Tables. Thus creating tables in Azure SQL Database requires Clustered Indexes. By default Azure SQL Database creates the Clustered Index on the table’s Primary Key.

CREATE TABLE [dbo].[TestTable] (
    [Id]        UNIQUEIDENTIFIER NOT NULL,
    [FirstName] NVARCHAR (10)    NOT NULL,
    [LastName]  NVARCHAR (10)    NOT NULL,
    [Type]      INT              NOT NULL,
    [Timestamp] ROWVERSION       NOT NULL,
    PRIMARY KEY (Id));

Primary Keys often make good Clustered Indexes because data is regularly accessed through them. But there are times when the Primary Key just doesn’t cut it. Fortunately, you can define a Clustered Index on other columns.

CREATE TABLE [dbo].[TestTable] (
    [Id]        UNIQUEIDENTIFIER NOT NULL,
    [FirstName] NVARCHAR (10)    NOT NULL,
    [LastName]  NVARCHAR (10)    NOT NULL,
    [Type]      INT              NOT NULL,
    [Timestamp] ROWVERSION       NOT NULL,
    PRIMARY KEY NONCLUSTERED (Id));
CREATE CLUSTERED INDEX IX_TestTable on TestTable (FirstName, LastName);

Changing a clustered index in Azure SQL Database is possible but it comes with some constraints. Failing to satisfy the existing  constraints enforce by the index will result in “The new index definition does not match the constraint being enforced by the existing index”.

CREATE CLUSTERED INDEX IX_TestTable
ON TestTable ([Type],[FirstName],[LastName])
WITH (DROP_EXISTING=ON)
ON [Primary]

Book – Building Interactive Queries with LINQPad

A few months ago Sébastien Finot was approached to write a book about working with LINQ using LINQPad and I had the honor of being one of the technical reviewers.

If you need to interact with databases, XML, in-memory collections, or remote services, LINQ can make your life simpler. The best way to discover LINQ is with the help of LINQPad, a free IDE whose first goal is to make sure that writing and interacting with your LINQ query is fun and easy. More generally, LINQPad is a C#/VB/F# scratchpad that instantly executes any expression, statement block, or program with rich output formatting and a wealth of features.

With Building Interactive Queries with LINQPad, you will quickly learn everything you need to know to start using LINQ. To accelerate your learning curve, you will discover how to use LINQPad and its features to test your queries interactively and experiment with all the options offered by LINQ.

In all probability, you already know C#, but have you had a chance to try out LINQ? Building Interactive Queries with LINQPad will introduce you to everything LINQ can offer and will let you interact with every example in LINQPad, LINQ’s best companion.

You will learn how to build and experiment with interactive queries with this practical guide illustrated with short and detailed code samples. You will also get acquainted with other cool applications of LINQpad such as testing, code snippet generation, and so on, along with a broad approach to LINQ (to object, to SQL, to XML, and so on).

Don’t Forget About Index Maintenance on Windows Azure SQL Database

Granted that Azure SQL Database takes care of a lot of maintenance tasks usually done by DBAs, you should definitely keep an eye on those Indexes. Indexes get fragmented and eat up lots of precious space. Furthermore, fragmented indexes make for slower queries.

I’m currently using the following technique to keep my Indexes healthy so that I may offer a constant end user experience. Furthermore, giving my Indexes regular doses of love prevents my database from growing too fast, which results in drastic cost savings. Throughout my test period, I noticed that fragmented indexes for large amounts of records would eat up close to 4gb of extra data. Once the Indexes are rebuilt, that extra space is recuperated and I am able to push back the dreaded moment where I have to pay for more SQL Database storage.