Voice of the DBA

Category: Blog

Starting ReadyRoll with an Existing DB

I’ve been working with ReadyRoll quite a bit the last few months, and wanted to start putting out some information. I’m looking to do a longer series, but here’s a quick post on getting started.

When you install ReadyRoll, you get a new project type under SQL Server.

This is a database project that will track all changes to your database as a series of migration scripts. Let’s get started. I’ll choose this and give it a name.

Once I do this, I get the ReadyRoll window with some information. What I really want to do here is point this to my existing database. Right now it’s looking at a LocalDB instance.

I’ll click the configure link and get the Connection String dialog. This is the database that mirrors one of the SSC databases, with lots of objects in it.

Once I’ve done this, the project will use this database. The Shadow database will also be on this instance.

I need to make one other change and set the correct version in the Project Settings.

Now, back to the ReadyRoll window. I’ve configured the DB connection, so I can ignore that. What I want to do is import my database, getting a baseline script.

I click that and ReadyRoll creates a Shadow database (since one doesn’t exist) and begins to import objects.

Once that’s done, I get a list.

And a migration script.

This is my baseline, starting script. This contains all the objects that exist in the database at this point. These are also added to the Schema-Model, but I can ignore those. I’m going to work on the database itself.

The ReadyRoll widget notes this was an import, and there’s no need to deploy anything since these aren’t changes, but just the state of the db.

I can see this if I do a Visual Studio build. Note the message in the middle: No migrations pending deployment. The changes in script 1 (001_20160614-1249_sjones.sql) are already in the db.

Now I can make changes to my database and get new scripts. Add a table in the designer?

When I click Update (and Generate Script), I get a new migration script.

Note that I just generated this script. I’ll write more about this process later, but for now, I’ll click Deploy Project to execute this against my database. When I do that, VS does a build, and one migration is executed.

Add a procedure?

Generate a new migration script.

And so it goes. I can work with my database in VS and get new scripts. I can also do an import if someone else makes changes to the database from their own machine with VS, SSMS, SQLCMD, isql, etc. The import will generate another migration script that gets added.

This is a really basic look at ReadyRoll, but it will get you started. I’ll tackle more topics and different ways of working with database development in another series of posts.

June 22, 2016
LAST_VALUE–The Basics of Framing

I did some work a 3-4 years ago, learning about the Windowing functions and enjoying them so much I built a few presentations on them. In learning about them, and trying to understand them, I found some challenges, and it took some experimentation to actually understand how the functions work in small data sets.

I noticed last week that SQLServerCentral had re-run a great piece from Kathi Kellenberger on LAST_VALUE, which is worth the read. There’s a lot in there to understand, so I thought I’d break things down a bit.

Framing

The important thing to understand with window functions is that there is a frame at any point in time when the data is being scanned or processed. I’m not sure what the best term to use is.

Let’s look at the same data set Kathi used. For simplicity, I’ll use a few images of her dataset, but I’ll examine the SalesOrderID. I think that can be easier than looking at the amounts.

Here’s the base dataset for two customers, separated by CustomerID and ordered by the OrderDate. I’ve included amount, but it’s really not important.

Now, if I do something like query for LAST_VALUE with a partition of CustomerID and ordered by OrderDate, I get this set. The partition divides the set up into the two customer sets. Without an ORDER BY, these sets would exist as the red set and blue set, but in no particular order. The ORDER BY functions as it does in any query, guaranteeing the same order every time.

Now, let’s look at the framing of the partition. I have a few choices, but at any point, I have the current row. So my processing looks like this, with the arrow representing the current row.

The next row is this one:

Then this one (the last one for this customer)

Then we move to the next customer.

When I look at any row, if I use “current row” in my framing, then I’m looking at, and including, the current row. The rest of my frame depends on what else I have. I could have UNBOUNDED PRECEEDING and UNBOUNDED FOLLOWING in there.

If I used UNBOUNDED PRECEEDING and CURRENT ROW, I’d have this frame, in green, for the first row. It’s slightly offset to show the difference.

However, if I had CURRENT ROW and UNBOUNDED FOLLOWING, I’d have this frame (in green).

In this last case, the frame is the entire partition.

What’s the last value? In the first case, the last part of that frame is the current SalesOrderID (43793). That’s the only row in the frame. In the second frame, the last one is 57418, the last row in the frame, and partition.

What if we move to the next row? Let’s look at both frames. First, UNBOUNDED PRECEEDING and CURRENT ROW.

Now the frame is the first two rows. In this case, the last value is again the current row (51522). Below, we switch to CURRENT ROW and UNBOUNDED FOLLOWING.

Now the frame is just the last two rows of the partition and the last value is the same (57418).

There’s a lot more to the window functions, and I certainly would recommend either Kathi’s book (Expert T-SQL Window Functions in SQL Server) or Itzik’s book (Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions). Either one will help. We’ve also got some good articles at SQLServerCentral on windowing functions.

June 20, 2016
The RGE

I first heard this little acronym from Grant Fritchey (b | t). He used it when talking about backups and restores, and I like it. However I realize that I’ve never actually noted what it is, so a short blog to do so today.

An RGE is a Resume Generating Event. This is usually when you make a mistake so egregious that you’ll be packing up your personal effects and exiting the building. If it’s really bad, such as releasing financial or other confidential information, you might be escorted out and someone else packs up your things. I’ve seen it happen, and it will shake you. Don’t do this.

We talk about forgetting about backups, or writing bad code or some important task we often perform as causing an RGE. In my experience, that doesn’t happen too often. Companies usually have a fairly high tolerance for mistakes.

However, that tolerance is usually extended only once. Don’t make the same mistake again. I’d also note that some managers can be very short tempered, and a single, large issue might be an RGE in their eyes.

I don’t usually worry about causing an RGE, but I keep the acronym in mind. Especially when I do something that could affect the core parts of my organization’s business.

June 17, 2016
When were statistics updated?–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I ran across the STATS_DATE function recently, and it’s one that I hadn’t used in production code. I’m not sure how this escaped me, as it was added in SQL Server 2008, but I rarely see it written about, so it’s not just me.

This function takes an object_id and a stats_id, and returns the date the statistics were last updated. The statistics id is the id from sys.stats and doesn’t necessarily correspond to the index ID.

As a quick example, if you look at the Sales.SalesOrderHeader table in AdventureWorks2012, you can run this:

SELECT STATS_DATE ( 1266103551 , 2)

This should return a simple date. I don’t know if you’ll have the same date in your database, but I assume this is the default date for the sample database.

Obviously these stats are out of date.

Or are they? I don’t use this database a lot and haven’t changed the data in this table that I’m aware of. In that case, they may be up to date.

This can be a handy function, but remember, the age of stats only matters if you’ve had data changes. However with having an understanding of both pieces of information, you might use this to accelerate statistics rebuilds ahead of what AUTO STATISTICS might do.

SQLNewBlogger

This was a good chance to dig into and look at how a function works in SQL and how I might use it. You could write this easily.

June 16, 2016