Shortcut: Configuring registered servers in SQL Server Management Studio

When working with SQL Server systems, it can be hard to remember the names of all the servers, to remember connection details for the ones that need SQL logins (instead of Windows authentication), and to remember other details of those servers, such as which environments they are part of (eg: production, UAT, test)

SQL Server Management Studio (SSMS) has a facility to help you to do this. It allows you to register server details in a single place.

By default, the window isn't shown, but from the View menu, you can choose Registered Servers.

When the window opens, you can see this:

Note the toolbar near the top of the window. It is showing that we're configuring database servers but the other icons let you know that you can also work with Analysis Services, Integration Services, and Reporting Services servers.

The first decision that you need to take is to decide where the details will be stored. Local Server Groups are stored on your local system ie: the system that is running SSMS. If you move to a different system to work, you won't have those connection details. Alternately, a Central Management Server can be configured. This is a server that agrees to hold connection details. While this seems a great idea (because the details would be held in a single place), one of the down-sides of this arrangement is that only Windows authentication can then be used. Local Server Groups can also work with SQL logins.

Let's create a server group as an example. If I right-click Local Server Groups, here are the available options:

Note that there is an option to Import (and Export) these details. This at least allows you to move details between systems.

Let's create a new Server Group:

It just needs a name and an optional description, then OK. When it's created, right-click it, and choose New Server Registration:

I've connected to the server SDUPROD and I've given the registered server the same name. Note that you don't need to do that. I could have called it PayrollServer or some other more meaningful name. You'll also notice that there are tabs for configuring other connection properties.

I've then created a second server called HRServer and under the covers, I've pointed it to another server.

Now I have all my servers in groups, in an appropriate location. I can right-click them to open new queries to them, and to do much more.

SDU Tools: List Foreign Key Columns in a SQL Server Database

In a previous post, I talked about the ListForeignKeys procedure as part of our free SDU Tools for developers and DBAs. That procedure returned one row per foreign key. Sometimes though, you need to process each column of a foreign key separately. So we've provided the ListForeignKeyColumns tool to do that.

The tool also detects any keys that are using system-generated names. (We don't recommend that).

You can see how to execute it in the main image above. The procedure takes these parameters:

@DatabaseName sysname – This is the database to process
@SchemasToList nvarchar(max) – a comma-delimited list of schemas to include (ie: 'Sales,Purchasing') or the word 'ALL'
@TablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: 'Customers,Orders') or the word 'ALL'

One row is returned for each foreign key column, rather than for each foreign key.

The columns returned are ForeignKeyName, IsDisabled, IsNotTrusted, IsSystemNamed, SchemaName, TableName, ColumnID, ColumnName, ReferencedSchemaName, ReferencedTableName, ReferencedColumnName.

You can see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

Opinion: Just how cheap should applications be?

In a recent post, I talked about my use of SnagIt and how I think people should be prepared to pay a little for applications. I'm endlessly puzzled by people I see stumbling around using free alternatives that don't do the job, when there are good options available.

I had some interesting feedback from that post and it got me thinking further though, about how much we should be prepared to pay for applications? Why is there an expectation that most apps that we use will be free?

The smartphone market is the one that seems most distorted on this. I've seen sophisticated applications that would have sold for hundreds of dollars years ago, being sold for $9. And what do the reviews say?

Great application but so expensive.

The perception is that that application should have been $3 instead. How dare they charge $9 when most apps are $2 or $3.

How did we get to this point? Worse still, the current app stores are making this even worse.

I was talking to a friend in Brisbane recently. He mentioned that he had built an app and put it into an app store. It was being sold for $1.99. When it was being used, it connected back to his servers that he was paying for. After the first month, he'd sold 300 copies and things were looking up. At the end of the second month, there were over 10,000 users connected to his servers but here's the rub:

He'd still only sold 500 copies.

So what on earth had happened? Turns out that someone had reverse engineered his $1.99 app, added advertising into it, and put it back in the app store as a different app, offered for free.

That's just beyond ridiculous, at least if we want there to be apps for us to buy.

I'd love to hear your thoughts.

 

SQL: SET NOCOUNT ON in SQL Server Triggers

I've done a lot of Microsoft certification exams over the years. Most of those have been SQL Server exams. The quality of the questions in those exams though, varies enormously.

One of the exams that really drove me crazy a while back was one of the intro-level ones. The problem was with the number of errors in the exam. When the question-writer gives you five substantial blocks of T-SQL, and asks which one is correct, yet they are all actually wrong in some way, that makes it a very, very tough question to answer. I find these types of exams very tiring because I'm endlessly trying to second guess what the question writer actually meant to ask. Some exams have a large number of these.

A more subtle problem though, is when there are questions that aren't really wrong, but it's immediately obvious that the question-writer just didn't "get" something. Here's an example:

One of the very first things that we teach people about writing DML triggers (ie: INSERT, DELETE, UPDATE triggers) is about the use of SET NOCOUNT ON. Yet on T-SQL exams that involve these triggers, I've lost count of how many triggers I've seen that don't include this.

What that tells me is that the question-writer has read about triggers and has a theoretical understanding of them, but hasn't really spent time implementing them.

If the question-writer was used to writing triggers, they'd put this in almost every time, without even thinking. Let me explain why.

We'll start with a simple table:

I'll then update one row:

Note that it shows 1 row affected as expected. If I execute that UPDATE statement from an application, and I expect it to update one row, the code will often check that one row was affected.

But now let's add an auditing table and a trigger:

After doing this, note what happens with our previous UPDATE:

This will not be what the application is expecting. The two sets of affected row counts might well be a problem. Worse, imagine if the second one had two rows affected in the background. The app might total the rows affected, or it might use the last value. Either way it'll be wrong.

Now let's alter the trigger to add SET NOCOUNT ON:

When we then do the update, we're back to what we expected:

So, while there are exceptions to any rule, in general when writing DML triggers, you should have SET NOCOUNT ON in the code.

Learning Mandarin: Common Characters used as Radicals

In an earlier post, I discussed how many Chinese characters are in fact made up of several other simpler characters, called radicals. In Mandarin, these radicals are called bù shǒu (部首).

For example, in the word Hǎo (good):

The character on the left is the radical form of the character Nǚ (woman or female):

And the character on the right is the radical form of the character Zi and means "child":

In both these cases though, it's pretty obvious when you look at the first character what the characters that are contained as radicals are. That's not always the case as in the character below:

It's Mù and means to "wash" or "bathe" or even to "wash your hair". The radical character on the left though is Shuǐ (water) and notice that when the character is written on its own, it's quite different:

Another common radical is Shǒu (hand). This is its normal form:

Here is an example of it used (on the left hand side) as a radical in the word Tí (lift up or raise):

The radical part of that character is normally referred to as "shou zi pang" (hand character beside).

There are 214 standard Chinese radicals used to build most characters according to the HSK Academy.

Here are some other very common words and their radical forms on the left-hand side of other characters that use them:

Here are two used below:

And one used on the lower right-hand side:

Learning Mandarin

I'll write more soon on the best methods for learning. If you want to get a taste for it in the meantime though, my current favorite is Tutor Ming. If you decide to try it, click here and it's a bit cheaper for you, and for me.

 

 

 

AI: Machine Learning and AI – What's in a name?

I regularly hear the terms AI and Machine Learning used almost interchangeably, along with a variety of other related terms. I thought it would be useful to add a post that defines some of the common terms and how they differ:

Artificial intelligence (AI) is a fairly generic term. It relates to all intelligent agents that are able to be aware of their environments (in some way), and to take actions where the aim is to achieve a specified goal. Sometimes these goals are terminal ie: they reach a final desired state. Other times, these goals are continuous ie: keep speed at a desired value. It is considered "artificial" intelligence as to an observer, it mimics cognitive functions that humans would imagine other humans performing.

Machine Learning (ML) describes a form of "learning" where a system  improves its model of a specific behavior (ie: "learns"). It can then use the model to predict future outcomes. Machine Learning is considered a field of Artificial Intelligence. There are many types of Machine Learning.

The most common form of Machine Learning today is Data Mining where the model is trained by analyzing existing outcomes, and then used to predict future outcomes. (This is usually called Predictive Analytics).

The learning can be supervised (ie: here are pictures of dogs, is this other picture a dog?), unsupervised (ie: what are the common types of an object?), or combinations of the two (often called semi-supervised).

Deep Learning is a form of Machine Learning where the models comprise many layers. "Deep" refers to the number of layers, not to any specific ability or insight. These models often do an amazing job, and in some cases are already performing better than humans at specific tasks such as speech to text translation.

Reinforcement Learning is another form of Machine Learning that typically involves working out optimal ways for software agents to operate within defined software environments. Game theory, simulation experiments, etc. often form part of Reinforcement Learning. One common way to represent the environments is as what is known as a Markov Decision Process (a mathematical framework that defines the rules for decision making and the goals and rewards involved).

 

 

 

 

Shortcut: Finding error locations within queries in SQL Server Management Studio

This is probably one of the simplest tips that I've published, yet I'm endlessly surprised how many people do not realize that it's available.

When you have a script loaded in SQL Server Management Studio (SSMS), and you execute the script, you might run into an error like this:

To find where the error is, just double-click the error down in the Messages tab. I double-clicked it, and it took me directly to the error and highlighted it:

SDU Tools: List Foreign Keys in a SQL Server Database

In SQL Server Management Studio, there's no great way to list all the foreign keys that are defined in a database.

In our free SDU Tools for developers and DBAs,  we added a function ListForeignKeys to do just that, and to provide their details in a form that's easy to consume programmatically if you need that.

You can see how to execute it in the main image above. The procedure takes these parameters:

@DatabaseName sysname – This is the database to process
@SchemasToList nvarchar(max) – a comma-delimited list of schemas to include (ie: 'Sales,Purchasing') or the word 'ALL'
@TablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: 'Customers,Orders') or the word 'ALL'

One row is returned for each foreign key.

The columns returned are SourceSchemaName, SourceTableName, ForeignKeyName, SourceColumnList, ReferencedSchemaName, ReferencedTableName, ReferencedColumnList, IsNotTrusted, IsDisabled.

You can see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

Opinion: Shout out to TechSmith for Snagit

I don't normally do blog posts to just promote products from companies, but two weeks ago I was asked about tools that I use on a daily basis and that I really wouldn't want to do without. Normally with a question like that, I have to think for a while. But this one's easy: It's SnagIt from TechSmith.

It's the one tool that I use in nearly every part of my work. I use it all day long. If I didn't have it, it would harm my productivity in a significant way.

I was using it at a client site the other day, and the client commented on how easily I could do things using it, compared to the way that he did screen captures, etc.

I'm always amazed at how people at various companies will go to extraordinary lengths to avoid paying small amounts for tools that change their productivity. It see people trying to use free snipping tools, etc. and it's all so clumsy.

Don't be that person.

Be prepared to pay a small amount for tools that can change your personal productivity.

Note: TechSmith now give me a free license for this tool but I was buying it long, long before they did so, and I would continue to do so if they stopped providing me with one.

Like with anything you've used over a long time, you can have a love-hate relationship at times. And there have been some very recent updates to the 2018 version where I've had performance issues, but I've just installed the 2019 version and everything seems to be sunshine and unicorns again performance-wise.

Is there anything I wish was better? Yes. A few versions back, they changed how the capture part works, and it's now slower for me to use, in terms of the UI. I takes an extra screen click to achieve what I want. It's hard to describe how little things like that make it feel like you're working slower. I really wish I could just hit PrintScreen, drag and click to capture, and have it already in my clipboard, without needing to go into the editing screen. I don't think there's any way to do that now. When I'm taking a lot of screen captures for recording a step by step process, that would speed things up. But we're talking about pretty minor stuff.

If you haven't tried this tool, just download it and try it. I suspect you won't want to ever then give it up.

 

 

SQL: Version 12 of SDU Tools has shipped

SDU Tools is a toolkit that's simple to use, has many useful functions, procedures, and views, and is updated regularly. Even if you aren't using the tools as a set, they provide wonderful examples of how to do things in T-SQL. We have released version 12.0 of these popular tools.

The tools are free tools for DBAS and Developers. Tools for comparing databases, tables, finding unused indexes, manipulating strings, performance tuning converting data, and so much more.

This release includes the following new tools (click on any one of them to see a video about it):

ScriptTableAsUnpivot : T-SQL has an UNPIVOT operator that can convert columns in table to rows in the output. The syntax is, however, a little hard to learn. (The same applies to PIVOT). Many people though, want to access standard relational tables as lists of attributes. This tool helps to write code for that, either as queries based on the table's primary key, or as a view that can be later queried. This can also help if you want to automate the creation of unpivoted views for your tables. If you need to access a standard table like it's an entity-attribute-value table (list of attributes and values), this tool can help.

NumberToRomanNumerals : We often get odd requests for new tools. For v12, it's this one. It takes a number (including large numbers), and returns a string representing the number in Roman Numerals.

ListDisabledIndexes : Does what the name says. It lists any indexes that are currently disabled. While these might be intentional, we often find they are accidentally left behind after data load processes, and cause performance problems.

ListUserHeapTables : In SQL Server, a heap is a table that doesn't have a clustered index. While there are tables that are best structured as heaps, most tables work best with a clustered index, particularly if there are other nonclustered indexes associated with them. What we often find though, is that this happens by accident, not by design. This tool helps to find any tables that are heaps.

ListUserTablesWithNoPrimaryKey : In SQL Server, a primary key is used to uniquely identify a row in a table. It is one or more columns that are both unique and not null. Generally, all tables should have primary keys. This tool helps to find any tables that don't have primary keys.

SetAnsiNullsQuotedIdentifierForStoredProcedures : This T-SQL stored procedure cleans up ANSI_NULLS and QUOTED_IDENTIFIER settings for one or more stored procedures. It can be used to fix procedures that have been inconsistently defined.

IsWeekday and IsWeekend : These T-SQL functions can be used to determine if a given date is a week day (Monday to Friday) or a weekend (Saturday or Sunday).

We hope you find these tools useful!