SQL Server databases are often littered with indexes that aren’t needed. In many cases, people won’t even remember why they were created in the first place. So I’m often checking out indexes that might be candidates for removal. One of our free SDU Tools for developers and DBAs does just that. It’s ListUnusedIndexes.
You can see how to execute it in the main image above. The procedure takes just one parameter:
2018-12-19
I’ve mentioned before that SQL Server Management Studio (SSMS) is a good tool for analyzing queries, as much as for executing them.
In SQL Server 2005, query plans had missing index details added. When a query plan was created, SQL Server recorded that it thought it could have executed the query better, if only you’d provided it with appropriate indexes. But at that point, the suggestions weren’t very good, and the tools didn’t show them.
2018-12-13
Years ago, it was common to need to print checks (cheques) from computer systems. Fortunately checks have now pretty much disappeared in most countries. One of the challenges with printing a check, was that you needed to convert an amount of money into words. Even though checks are on the wane, it’s still important to do that on some contracts, etc. In our free SDU Tools for developers and DBAs, we added a function NumberAsText to do just that.
2018-12-12
When I’m running SQL Server Advanced T-SQL classes, we spend time discussing cursors. The general message is that most of the time, but not always, cursors are the wrong answer. The problem with cursors is that instead of telling SQL Server what you want it to work out (ie: declarative query), you are telling it how to work that out (ie: procedural query).
You’ll hear people say to never use cursors. This is also incorrect. There are occasions where I do use cursors. Mostly this is when I’m building utilities that do things like scripting objects in the database. I’d almost never use a cursor though, in general data processing running against SQL Server. You don’t want to be doing row by row processing against the server.
2018-12-12
I used to do a lot of work at the operating system and network level. I was always fascinated watching people use network trace tools when they were trying to debug a problem. The challenge was that they had no idea what was normal activity on the network, and what wasn’t.
The end result of this is that they’d then spend huge amounts of time chasing down what were really just red herrings.
2018-12-11
Old PC style databases and tools (like Access and DBase) and most 4GL databases tended to use the words Record and Field to describe the layout of tables. Relational SQL databases use the terms Row and Column. I endlessly hear discussions about if there’s a difference, and where that lies.
Some will argue, for example, that a field is a data value within a single record or row. That would make a field more like a cell in Excel, than a column in Excel.
2018-12-10
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
