One of the advantages of SQL Server Management Studio (SSMS) is that it can be used to analyze queries, not just to execute them.
There are two basic types of query plan: estimated execution plans, and actual execution plans.
For a typical query, I can obtain the estimated execution plan, by hitting Ctrl-L, choosing the option in the Query menu, or clicking on the toolbar icon:
Let’s do this for the following query:
2018-12-06
I’m often reviewing existing databases and one of the first things I go looking for is the way they’ve used data types. In particular, I’m keen to know if they’ve used any deprecated data types (ie: ones that will/might be removed at some point). In our free SDU Tools for developers and DBAs, we added a procedure ListUseOfDeprecatedDataTypes to do just that, and to provide their details in a form that’s easy to consume programmatically if you need that.
2018-12-05
I’ve been in the IT industry a long, long time. One thing that I’ve never liked is case sensitivity in application development tools or in database languages. And it’s creeping into more and more places.
I know that will offend some people but hear me out.
I think we’re stuck with case sensitivity in languages like C, C#, C++, Java, etc. because that was the easiest way to implement those languages in the first place. As soon as you decide that a language is case insensitive, you also have to decide the internal collation rules. For example, is the letter A the same as the letter a ? But then what about the letter á ?
2018-12-04
If I create a primary key (let’s say a CustomerID in a Customers table) in SQL Server, an index is automatically created to support that key. Primary keys must be unique and not null. SQL Server uses that index to ensure that the key is unique. Keep in mind that when I say “key”, I’m referring to one or more columns, not necessarily just one.
The same happens for unique keys. Again, it makes it easy for SQL Server to ensure the uniqueness.
2018-12-03
When I was growing up, we were endlessly shown Mother Teresa (now known as Saint Teresa of Calcutta by Catholics) as an example of a person who had devoted their life to the service of others, and did so in appalling situations. I knew that the myth surrounding this woman was very different to the reality and I’m surprised that I hadn’t previously read Christopher Hitchen’s book: Book Review: The Missionary Position: Mother Teresa in Theory and Practice.
2018-11-30
Another simple tip for today. In SQL Server Management Studio (SSMS), query results are normally shown at the bottom of the query window.
This can greatly reduce the screen real estate both for the query, and for viewing the results.
In Tools, Options, Query Results, SQL Server, Results to Grid, there is an option to Display results in a separate tab. This can be very useful and generally you will also want to choose the extra option to Switch to results tab after the query executes.
2018-11-29
Ever since we’ve been shipping our free SDU Tools for developers and DBAs, we’ve been getting interesting requests from users for additions to the tools. Today’s tool is one of the stranger requests but for the odd situation where you need it, I’m sure we will have saved you a lot of work. That tool is NumberToRomanNumerals.
The person who requested it was outputting values that need to go into media trailers, pretty much as you see Roman numerals in the tail end of many movies.
2018-11-28
One of the real beauties of the Internet is its global nature. But ever since we’ve had it, people keep trying to ring-fence certain locations, and make applications location-aware. While geolocation can be useful, over-dependence upon it is a real pain in the neck.
The first situation where this is painful is in media restrictions. Companies are still trying to enforce country and region boundaries for media licensing.
We need to get past this.
2018-11-27
I often deal with quite large databases. There are only two things about big databases that trouble me. One is queries that need to read the whole database to find anything. The other is big databases that are still designed like small databases.
Every week, I still see massive SQL Server databases where all of the data is in a single PRIMARY filegroup. That’s a problem but often the people looking after the databases can’t do much to improve that situation.
2018-11-26
Damon Edwards had a session recently where he said claimed that Without Self-Service Operations, the Cloud is Just Expensive Hosting 2.0. There is much in his session that I completely agree with, and have been concerned about for quite a while. I see it more in terms of the adoption of Platform as a Service (PaaS) offerings.
I spend most of my consulting/mentoring time in larger organizations, many are large financial organizations. In every one now, there is a person heading up a “Cloud Transformation” project, but none of these companies mean the same thing when they talk about these types of projects.
2018-11-23
