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.
In the image above you can see the ListAllColumnsAndDataTypes procedure in use.
You tell it which database to list, and you can optionally limit it to particular schemas, tables, and/or columns. For each of these, you can specify the value ALL or you can supply a comma-delimited list of values.
You can see the tool in action here:
SDU Tools are free developer and DBA tools for SDU Insiders. To find out more about SDU Tools or to add yourself to the list, visit here:
Brent defined consultants as giving advice on what the client should do; whereas contractors turn up and do what they're told. This distinction is pretty clear and I agree with him.
I always thought that was a great post but I also always thought it misses one additional category: mentor.
I'm often a consultant but one of the challenges for clients is that once you give them advice, they often don't know how to implement the advice.
Conversely, a contractor tends to come in, do the work, and share little knowledge with the client. Unfortunately, many contractors specialize in holding back information.
A mentor, however, does whatever he or she needs to do, to get the staff at the client organization into a position where they can do the work themselves. This will involve:
Guiding them through decision processes
Helping them to understand the pros and cons of the options
Working with them on proofs of concept
Training them
And more.
The key difference is that a very high degree of knowledge transfer is happening.
99% of the time, I'd rather be a mentor because when I leave again (and I will), they are the ones that need to look after whatever we created, longer-term.
Side-note: I found it really hard to find an image for this post, where a woman was mentoring a man. It was always the other way around. We need to keep trying to change that.
There is an old saying about not reinventing the wheel yet this is something that I see happening at client sites every day. I see two main reasons why this happens:
Discoverability
Image by Nathan Dumlao
There are so many tools and frameworks in this industry, that you can't be expected to know them all. I remember when I worked a lot with the .NET framework. I'd go into client sites and see them designing and building classes that were already in the framework. Worse, the framework classes were usually very well designed and tested.
The challenge was that with thousands and thousands of classes (at release I think .NET was over 6,000 classes), it's hard to know what's in there.
Image by Rafael de Nadai
I see developers working with SQL Server and creating tables to hold queues. A table seems the natural way to store queue data but the problem is that it doesn't have many of the semantics that are needed for a great queue.
When I describe Service Broker, the developers are often very surprised to learn that SQL Server already contains a fully transactional queue, already there, right in the database. Often it has all the capabilities that they are trying to implement themselves.
More importantly, Service Broker includes support for things they hadn't even considered. Building a queue sounds simple, but it really isn't.
For example, if you take an entry off the queue, try to apply it, and the transaction rolls back, what happens next? Does your application just stop? Does it try to take the entry off the queue and apply it, only to go bang again?
The biggest problem I've seen with Service Broker is that Microsoft promoted it to database people, as it was part of the database. That's completely the wrong target market. It should have been promoted to developer leads and technical architects. I think the marketing for this was completely misdirected.
Not Invented Here
Image by Roman Mager
The other common reason that I come across is the "Not Invented Here" syndrome. There are clients who simply won't ever use code that someone else has created. They are usually concerned about one of these:
Takes too long to learn
Won't do exactly what I want
Don't want a dependency on it
I can't always dismiss this but I do note that the same people won't write their own database management systems or operating systems. (Although I think many of them would prefer to if they had time)
The problem with this is that the outcome for their own clients is usually substandard, and worse, their products are likely to become noncompetitive.
For example, you can write your own reporting system and/or dashboard system, but you'll probably get a better outcome if you use Reporting Services and/or Power BI. I've seen some of my own clients write their own reporting systems, but they are extremely low-functioning, and are generally unable to be integrated with any other tools that their customers use.
When you are starting to create new functionality, at least please consider that what you're after might already exist, and in a better form than you would ever have time or experience to create.
I spend quite a bit of time tracing queries sent from applications to SQL Server.
Having now done this for a long time, I can recognize many of the data frameworks that are used by various developers. I think that if any of the developers ever spent much time looking at traces, they’d be amazed at how the requests they have made to the framework are translated to commands that are sent to SQL Server.
I’ve also been posting lately about how the quickest way to scale SQL Server is to just stop talking to it unnecessarily.
One variant of the JDBC drivers that I’ve seen, has a command that, while it’s really a “nothing” for SQL Server to execute, breaks this rule.
What I see is that the framework regularly executes this command:
SELECT 1
Yep, that’s it. And why does it do this? It appears that they are doing it to check if they have connectivity to the server.
I’m sure some framework developer thought that was a good idea. These are the people who don’t get things like race conditions, and timing, and are the sort of people who create applications that nearly always work.
This command is actually completely and utterly pointless, and worse, incurs the latency of a regular round trip to the server while doing so.
Even if the server responds to your SELECT statement, that tells you NOTHING about the state of your connection immediately after the command is completed.
Image by Markus Spiske
The fact that the server responded to you, doesn’t in any way at all, guarantee that the server will still be connected for your next command batch.
Please don’t do this.
It wastes your time, your end-user’s time, and SQL Server’s time. Worse, every little extra command that SQL Server has to process takes it away from the real processing that you want it to do.
In my last DevOps-related post, I discussed some of the issues that arise when using migration-based deployments.
Migration-based deployments are based on a series of T-SQL scripts that apply changes to your database from its current state to a desired final state.
One of the questions that arises though, is whether or not these T-SQL scripts should be idempotent ie:
Should the script be written so that you get the same outcome if you run the scripts multiple times?
Creating T-SQL scripts that you can reliably run more than once isn't easy. It's not required for migration-based deployments if all goes well, it might make them more reliable, and/or make them more useful when errors occur during deployment. However, achieving that isn't easy.
In essence, creating scripts like this means that instead of writing code like this:
you write code like this:
But what would happen if the table is there but different?
Sometimes it's also messier than you'd hope. For example, CREATE SCHEMA has to be the first statement in a batch. You can't write:
Instead, you need to write something like:
And apart from now losing help from Intellisense, etc. within the script items, this sort of thing just gets harder and harder.
My experience is that apart from situations that are fairly trivial, creating seriously idempotent scripts is much, much harder than it looks, and it's far too easy to create scripts that can still end up with different end states.
I think you are far better off validating the schema before running the migration, and if failure occurs, recover and re-run whenever possible.
Whenever I need to work on a new laptop or server, or whenever I change versions of SQL Server Management Studio, I kick myself for not remembering to export my settings, so I can import them again.
I spend quite a bit of effort getting SSMS configured the way I want, so it only makes sense to save the settings. Saving them isn't perfect but it's far better than not having done it.
From the Tools menu, choose Import and Export Settings:
As an example, let's export the settings. I'll choose Next:
Notice that it's not an all or nothing export. I can choose details of which settings or groups of settings to export.
In this case, I wanted all of them, so I just need to pick a name and a location:
And next time I change to a different machine or new version, I can just import them and pat myself on the back for remembering.
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
SystemConfigurationOptionDefaults – view that shows system configuration options and their default values (and many other values) NonDefaultSystemConfigurationOptions – view that shows system configuration options that are not at their default values
SQLServerVersionForCompatibilityLevel – function that converts a database (or server) compatibility level to a SQL Server version
SystemWords – view that shows reserved words, future reserved words, ODBC reserved words, and system data type names and their display color in SSMS ReservedWords – view that shows SQL Server reserved words (and their display color in SSMS) FutureReservedWords – view that shows SQL Server future reserved words (and their display color in SSMS) ODBCReservedWords – view that shows ODBC reserved words (and their display color in SSMS) SystemDataTypeNames – view that shows all system data type names (and their display color in SSMS)
CreateLinkedServerToAzureSQLDatabase – as the name says – makes it easy to create a linked server to an Azure SQL DB
ListPrimaryKeyColumns – comma-delimited list of primary key columns for all primary keys
You'll find more on joining SDU Insiders and getting our free resources here:
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.
In our free SDU Tools, we have a tool that does that as well.
In the example above, you can see both a Pascal-cased phrase and a camel-cased phrase that has been separated.
You can see it in action here:
For more information on our SDU Tools, and to join our SDU Insiders to get them and our other free resources, please just look here:
Many of my customers are software houses (ISVs). In almost all of them, I hear people complaining that they can't find enough good staff. I think they are trying to tackle the wrong problem.
Most of the staff members they are trying to find are developers, and there are so many great developers out there, but you have to accept that they might not be in the location that you hope they're in.
I've seen companies struggling to hire the last remaining developers for various technologies, from those available in that city. This often even leads to crazy situations like hiring staff that they have previously rejected. They should not be drilling further and further down the talent pool in their location.
Worse, the more that companies hire poorly qualified staff, the more their experienced staff will be spending their times fixing issues caused by staff that should never have been hired in the first place. This is a formula for losing your best staff.
Some temporary respite might come from convincing people to move to the same city, but that's a very limited group of people who can do that or will be willing to do that.
So many jobs are now able to be performed from distributed locations, and development jobs are prime examples of these.
Why hire poorly qualified locals at ever-increasing costs when there are many outstanding people in other locations?
Instead of trying to fight for the last remaining staff in a city, and paying ever increasing salaries to ever less capable staff, companies should make a serious commitment to enabling remote work, and investing in really high quality communications infrastructure and probably some software. It's important to get out of the mindset of needing to have all your staff in your building.
Image by Richard Jaimes
It's time to look outside. It's a big world out there, full of amazing people who could be doing great work for you.
I've done a lot of Microsoft exams over the years, mostly SQL Server ones but plenty of others too. And one thing that I really don't like is when the questions are:
Purely academic (ie: would never happen)
Memory based (ie: who cares what the DTU limit is for a P3 today?)
Clearly not written by someone who actually uses the product
Today, I want to mention an item in the last category.
In many exams, I've seen T-SQL code for triggers and one of the instant tell-tale signs of whether the author of the question really uses triggers or has just read the documentation and thinks they know about triggers, is whether or not they've used SET NOCOUNT ON.
If an application updates two rows in a table, SQL Server tells it that two rows were updated.
If you then add a trigger that, for example, updates a row in another table, SQL Server will now tell the application that three rows were updated.
This is enough to break many applications.
The application that sent the update won't want to know about other rows inserted, updated, deleted, etc. by your trigger. That will only confuse or break things.
While there are odd exceptions where you do want the default behavior, the answer to this is to use SET NOCOUNT ON. When we are teaching students to write triggers, we tell them that nearly every DML trigger should start with SET NOCOUNT ON. Check out the documentation here:
SET NOCOUNT ON stops the count of affected rows being returned for an operation. The beauty of using this in a trigger is that it stops the count of rows affected by the trigger from affecting the count that the application sees.
So, when I see a whole lot of exam questions on triggers and none of them include this, that's really disappointing in so many ways. Learn to use this in your trigger code.
(Note that we try to avoid using triggers in the first place most of the time).
