Learning Mandarin – Nouns, Verbs, Adjectives, and Adverbs

In Mandarin, the word for word is  (Cí). It's pronounced somewhat like "tser". An example of its use is in the word 词典  (Cídiǎn) which is the word for a dictionary.

Individual characters are called   (Zì) but that can also be used for a word. Mandarin characters would be 汉字  (Hànzì) where 汉  (Hàn) represents the Han people ie: most of the Chinese.

Most of the parts of English speech have fairly direct equivalents. A Noun is called 名词  (Míngcí) which pretty much means "name word".

A verb is called 动词  (Dòngcí) which is close to meaning a "move word" or perhaps a "movement word".

An adjective is called 形容词  (Xíngróngcí) which is a little more complex but basically a "describe word". on its own is a shape.

An adverb is called 副词  (Fùcí) which is close to a "assistant word", a "secondary word", or an "auxiliary word".  on its own is means secondary, auxiliary, deputy, assistant, etc.

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.

Shortcut: Missing index details in SQL Server Management Studio

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.

In SQL Server 2008, the suggestions got better (eg: we weren't endlessly having suggestions to create non-clustered indexes for every column in the table), and SSMS now showed them clearly.

Here's an example query with an issue:

SQL Server thinks it's doing this work the hard way. Note that it suggests an impact of over 97% on running this query, just because an index is missing.

To find out what it wants, we right-click in the white area of the query plan:

The option of interest is Missing Index Details. When we click that to open it, the following appears:

Overall, the suggestions that this system makes aren't bad. They are far from perfect but if you don't know much about SQL Server indexing, these suggestions might make a good start. Just don't blindly follow large numbers of them. Someone with strong SQL Server skills can often come up with better suggestions than those currently offered by the tools.

Also, make sure you rename the index. I have come across indexes at customer sites where the index name is [<Name of Missing Index, sysname,>]. I wish I was joking.

Learn about indexing

Want to learn more about indexes? Take our online on-demand course now:  https://training.sqldownunder.com/p/sql-server-indexing-for-developers

 

SQL: Cursor types in SQL Server

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.

When we first review many systems, a strong code smell is lots of temporary tables and cursors. That tends to indicate that the author hasn't really understood set-based logic.

But if you have a situation where cursors are needed, it's important to know how the different types work. The different types of server cursor are:

Forward-Only Read-Only

This is the most basic type of cursor. When you open it, SQL Server executes the query and starts throwing data in the direction of the client as soon as it can. The client navigates by just asking for each row, one at a time. There's no option to go backwards, or to change the data (at least by using the cursor to change it).

Static

This option tells SQL Server to execute the query, throw a copy of all the data that it found into tempdb, and send a pointer back to the client. The client can then move forwards or backwards in the data, and it doesn't matter what other users do to the data, the client is using its own copy and is unaffected. This means that it doesn't suffer from Halloween-type issues. This is heavy work for the server and doesn't scale well.

Recently I saw a fellow MVP complaining about Halloween-type issues with other cursor types but thought he couldn't use Static cursors because he read that they don't support updates. It's true that you can't use a WHERE CURRENT OF clause to modify the data through the cursor, but there's nothing stopping you using a standard UPDATE based upon the primary key value if that was retrieved as part of the data.

Keyset

This is a lighter form of cursor. SQL Server executes the query, puts a copy of the keys into tempdb, and returns a pointer to the client. Much less data is going into tempdb. The client can then request forward or backward movement along the cursor, but each time they request data, SQL Server has to go to the original data to find the values to return, based upon the stored keys. If another user adds additional data, they won't see it because they already have the list of keys. If another user deletes data though, the client can have a problem. If they ask for the next row, the key might be in the cursor but the data might no longer exist. The client has to deal with that scenario. (This can cause errors on clients that don't cater for errors). Because this cursor is connected to the original data indirectly, it's considered an updatable cursor.

Dynamic

This cursor is the lightest from the point of view of the server. All that's stored at the server is the query and a pointer as to where it's up to. When the client asks to go forward, it looks up the next row and returns it. Same for backwards. The challenge with this type of cursor is that the client is exposed to all the vagaries of impacts from other users. As other users add or delete rows, interesting outcomes can occur. For example, you could be on one row, step forward once, then step backwards once, and not be on the same row that you started with, because someone else added or deleted a row. It's also an updatable cursor type.

 

 

SDU Tools: Show a number as text in SQL Server

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.

You can see how to execute it in the main image above.

We decided not to assume that it's a monetary value, just a number, so you could easily turn it into a monetary value like this:

The text is returned in lower-case. It's easy to then use UPPER to force it to upper-case:

You could easily add a comma in between the dollars and the cents as well:

You can see it in action here:

We hope you find it useful.

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

http://sdutools.sqldownunder.com

Opinion: Get used to reading traces and logs before you need them

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.

When you don't know what normal activity looks like, everything looks odd.

Today, I see the same thing with traces of SQL Server activity, either using SQL Profiler (and/or SQL Trace), and Extended Events Profiler. I also see the same thing with insights data sent to Log Analytics, and the outcomes of many expensive SQL Server monitoring tools.

For example, if you are looking at a SQL Server trace, and you see a large number of sp_reset_connection commands. Is that an issue? When would it be an issue, and when is it just normal?

If I see an sp_reset_connection executed on a connection followed by a number of other commands, I know that the application is using connection pooling. If however, I see a bunch of those on the same connection, without any commands executed in between, I know that the application code is opening connections when it doesn't need to. Perhaps it should be opening the connection closer to where it decides if it needs it.

The key point is that it's really important that you learn to use these tools before you have a problem. You need to be able to recognize what's normal, and what isn't.

 

SQL: Fields and columns, what's in a name?

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.

My take on this is that if I hear someone use "Record" and "Field", I don't lose the plot and want to correct them. I know what they meant, and that's all that matters.

The one exception to this though, is in training.

If you are teaching people to use SQL, please don't use Record and Field and instead use Row and Column. Using standard terms when teaching is important.

Shortcut: Compare query plans in SQL Server Management Studio

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:

We get the following plan output:

Now we might look at this plan and wonder if we exerted control over how the joins were performed, if we'd improve things. Perhaps we'd heard that merge joins were more efficient and thought SQL Server should have used those.

Now we can change the query like this:

Notice I've added the word MERGE between INNER and JOIN in this query. But how do we know what SQL Server thinks? We can get another estimated query plan but what we really want is to compare them.

When we obtain an estimated plan for multiple queries at once, SSMS shows us a comparison of the two, by showing us the proportion of the overall query for each part. (Perhaps we shouldn't force that change 😊)

SDU Tools: List use of Deprecated Data Types in a SQL Server Database

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.

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'
@ColumnsToList nvarchar(max) – a comma-delimited list of columns to include (ie: 'CustomerName,StreetAddress') or the word 'ALL'

One row is returned for each use of each deprecated data type.

The columns returned are SchemaName, TableName, ColumnName, DataType, and SuggestedReplacementType.

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: Case sensitivity is a pox on computing

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 á ?

I get that it's a hassle but humans just don't think in a case sensitive way, and that shouldn't be the basis of designing a language for humans to use. It might be computers that execute it but it's humans that write it, and more importantly, read it.

Now before I have people jump all over me, I'm not talking about case preservation.

It is important to me that if I write CustomerName or customerName, that when the system sends that value back to me, that it shows it the same way that I defined it. That's case preservation, not case sensitivity. I just shouldn't have to request objects or data in a specific case sensitive form. If it's a development tool, just automagically convert it to the defined case. If it's a database, just give me the data.

And I hear the C folk charging along in the background arguing that there's a common standard for backing variables (ie: someProperty) to have the same name as properties, with just a case change (ie: SomeProperty).

Sorry, but that was never a good idea either. There are other ways that we can solve that problem, and I've lost count of the number of times I've seen bugs in code where a property should have been accessed but a variable was accessed instead.

When you break it down to its essence, what case sensitivity does is allow me to have two objects in the same object scope, that differ only by the casing of their names.

You'll have a hard time convincing me that that was ever a good idea.

SQL: Should foreign keys be indexed in SQL Server?

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.

But if I create a foreign key (let's say a CustomerID in an Orders table), SQL Server doesn't create any index to support that. When I have the foreign key in place, and I perform an INSERT or UPDATE, it can use the primary key of the Customers table to check whether or not the customer exists.

Where the problems start

That's all well and good. But ask yourself: what's the chance that I need to come the other way across the relationship ie: find me the orders for a particular customer? At that point, you'd want an index to help your query.

DELETE statements can be even nastier. If I execute a command to just delete one customer, without any index in place, that's going to cause SQL Server to read the entire Orders table first. It has to make sure there are no orders for that customer. So a simple DELETE operation that should take no time at all, suddenly becomes a nasty mess that involves reading millions or billions of rows. I'd also end up with all sorts of locks involved.

An index on the foreign key would have avoided that.

The big question though is if SQL Server auto-created the index for you, would it be useful? For the DELETE operation, it most certainly would be, and for that reason alone, I'd like to see it there.

But what about the query? Well it's likely that if I was looking for all the orders for a customer, I'd want more than the OrderID. I'd probably want an OrderDate, a DueDate, a PurchaseOrderNumber, etc. And this is where the controversy starts. That auto-created index would be OK for this, as long as the query was highly selective. It would end up doing lookups to get the other columns once it found the orders.

But if the outcome wasn't highly selective, the index would be ignored. SQL Server would decide that the lookups would be too expensive.

What's needed

So what is needed? I'd need an index where the CustomerID was the first component of the index, and the other columns were either index key columns, or included columns. So perhaps an index like this:

CREATE INDEX FK_dbo_Orders_dbo_Customers
ON dbo.Orders (CustomerID, OrderDate)
INCLUDE (DueDate, PurchaseOrderNumber);

Importantly, that index would work for the DELETE operation as well.

Wish time

I believe that SQL Server would be overall better if it auto-created indexes to support foreign keys. I just see too many issues happen because they're missing.

Ideally, it would do that only unless it detected that there was another suitable index (best bet). Alternately, it could offer an I_KNOW_WHAT_I_AM_DOING clause when creating the foreign key.

OK, perhaps I'd settle for a clause like WITH NO_INDEX.

What's that smell?

When I'm reviewing databases, I do look for this. I consider a declared foreign key, where there is no index with the foreign key columns as its left-most components, to be a code smell.

If you want to check for these, our ListUnindexedForeignKeys procedure in our free developer and DBA toolkit (SDU Tools) offers a simple way to find out.