Azure-Sql-Db

Having up to date statistics is critical for SQL Server choosing appropriate query plans. Poor statistics can lead to poor query plan outcomes.

Generally, SQL Server manages this well by itself. As a rough rule, most versions auto-update statistics when the number of changes is about twenty percent of a count of the number of rows in the table. (Some recent changes have slightly altered how this works but the ball park is pretty good).

2018-04-25

For anyone who’s worked with SQL Server for a long time, you’ll know that one of the common requests is that a table of numbers would be useful. Yes, just a table with 1, 2, 3, 4 and so on. Often in a query, you need just a certain set of values.

In our free SDU Tools, we’ve added a function to do that in a reasonably efficient way.

2018-04-18

When I first start working with any SQL Server system, one of the first things I want to look at is how the server has been configured.

More particularly, what I’m interested in is:

What has been changed from the default value?

None of the standard SQL Server system views, however, provides me with this info. We do have the sys.configurations view. It shows you the configurations and what’s been chosen, but it doesn’t show you what the default value was:

2018-04-11

There are a few places in T-SQL where instead of a time value, you need to work with a duration.

An example is if I want to sleep for a short period. In T-SQL, we can do that with the WAITFOR statement, used like this:

This command would cause SQL Server to wait for 10 minutes. One of the challenges though, is how do I create this duration string in a programmatic way? For example, how do I create the string ‘00:02:00’ if I’m starting with a value of 120 seconds?

2018-04-04

Working with dates and times in database systems and programming languages has always been “interesting”, at least interesting in the sense that the old Chinese curse meant when it said “may you live in interesting times”.

One of the curious variations though is the use of Julian day numbers. It’s a count of the number of days since the beginning of the Julian period: https://en.wikipedia.org/wiki/Julian_day

SQL Server doesn’t currently have a built-in function for converting to or from these so we added functions to our free SDU Tools for developers and DBAs. You can use the functions directly from our tool kit, or use them as examples of how to write these functions.

2018-03-28

Some of the simpler tools that we’ve added to our free SDU Tools collection over the last year or so, have turned out to be quite useful.

A good example of this is CountWords.

This function takes a T-SQL string, removes the punctuation, excess whitespace, etc. and then counts the number of words contained. There are a few things that can still trick it (avoiding that would need very detailed langauge parsing) but it’s quite good.

2018-03-21

When you create script out a table in SQL Server using SQL Server Management Studio, you’ll notice that it scripts more than just the table itself. Before the table, it scripts the values for ANSI_NULLS and QUOTED_IDENTIFIER.

A common problem that I see is that someone highlights the CREATE TABLE statement and runs it, without highlighting the SET options above it. That has the chance of leading to the wrong values. I don’t run into big issues with QUOTED_IDENTIFIER but I certainly run into issues with ANSI_NULLS.

2018-03-14

One of the first things that I often do when familiarizing myself with a database, is to get a list of all the tables, columns, and data types. This immediately tells me a lot about how the database was designed.

For example, have they made newbie mistakes like using float for storing amounts of money.

One of our free SDU Tools makes this easy.

In the image above you can see the ListAllColumnsAndDataTypes procedure in use.

2018-03-07

I’m really happy with the additions we made to our free SDU Tools for developers and DBAs that are going out tonight:

DatesBetween - table-valued function to return dates between two dates DateDimensionColumns - table-valued function to return typical date dimension columns for a date (useful for data warehouses)

JulianDayNumberToDate - converts a Julian day number to a date DateToJulianDayNumber - converts a date to a Julian day number

2018-02-28

If you’ve ever used SQL Server Reporting Services, you’ll notice that when you drag a database column into a table, it auto-magically converts the name of the column into a more English-readable name.

Image from MSDN

Notice how when the LineTotal column has been dragged into the table, the heading has been set to Line Total with a space. What it is doing is taking a Pascal-cased or camel-cased name and separating the words with spaces, based upon where the capital letters are.

2018-02-28