SQL: Try to avoid unnecessary abbreviations when naming objects

There's an old joke in computing about how you can spend 90% of the time on a project working out what to name things, and end up without time for doing the work.

Phil Karlton is credited with having said: There are only two hard problems in Computer Science: cache invalidation and naming things.

I really liked Jeff Atwood's or Leon Bambrick's update though: There are two hard things in computer science: cache invalidation, naming things, and off-by-one errors. (Can't work out who said it first).

Today's post is just a simple plea to ask you that when you're naming things, to avoid abbreviations that aren't necessary.

I'll give you a few examples of what I mean.

EOMONTH

SQL Server 2012 introduced a new function EOMONTH. It's End of Month. I recently wrote about how it does more than just take a date and give you the end of the month for that date. If you missed the discussion, that's here.

But what I want to talk about today is why on earth it's not called ENDOFMONTH, or perhaps better, END_OF_MONTH.

What's the real value in saving three characters from ENDOFMONTH to make it EOMONTH anyway? Keep in mind that the same version of SQL Server introduced a function DATETIMEOFFSETFROMPARTS, so it wasn't just about saving keystrokes.

When I asked the product group, I was told that they copied the name of the function that's in Excel. I really don't like the idea that the name of new SQL Server functions would be based on what someone came up with in Excel a long time ago.

Database Objects

Anyone got any idea what aptrx is as a table name? If you've been around a while, you might guess that it's Accounts Payable Transactions. But is there really any need to intentionally obscure the database like this, and not use a name like AccountsPayable.Transactions?

I'm sure this originated on older systems where short names were all you could have. I've worked on ancient systems where a table name needed to fit in 6 characters. Even today, I think Oracle is limited to 30. But most sensible database systems allow for longer names if you need them. Object names in SQL Server are of datatype sysname which is currently mapped to nvarchar(128). Length really isn't a justification anymore, and most modern UI's write the names out for you anyway, so it's not even a typing issue most of the time.

Shortened names provide no real benefit and add to maintenance and support costs.

 

 

 

SDU Tools: Execute a T-SQL command in each SQL Server database

I regularly run into situations where I need to execute a T-SQL command in each database on a server. The built-in Microsoft method is to call the unsupported sp_MSforeachdb, and there have been other methods over the years. None of them really worked the way that I wanted them to, so in our free SDU Tools for developers and DBAs, we added a tool that does just that. It's called ExecuteCommandInEachDB.

It takes the following parameters:

@DatabasesToInclude nvarchar(max) – this is a comma-delimited list of databases or the value 'ALL'
@IncludeSystemDatabases bit – Should system databases be included? (Most of the time, for me, they aren't)
@CommandToExecute nvarchar(max) – the T-SQL command to execute (default is SELECT DB_NAME(), @@VERSION;)

Find out more

You can see it in action in the main image above, and in the video here:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

 

T-SQL 101: #33 Adding comments to your T-SQL scripts

It's really important when you're writing SQL Server code (or T-SQL in particular) that you add comments to the code where something isn't obvious to someone who's reading it.

Here's an example of comments being used:

There are two ways that comments can be added:

If you put a double-dash on a line, anything after it is a comment.

If you need to comment a block of code, you can put a /* to start the comment and */ to end it. I'm not a fan of block comments in T-SQL because you can't nest one comment inside another comment. With the double-dash method, that's not a problem.

You might also want to use inline comments to just temporarily remove a line of code from a script. For example, if you have a SELECT query that isn't returning what you expect, you might just comment out the WHERE clause to see if that's what the problem is.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

Snowflake for SQL Server users – Part 4 – T-Shirt Sizing

I mentioned in my last post in this series, that the Compute layer of Snowflake is basically made up of a series of Virtual Warehouses (VWs).  Each VW is an MPP (massively parallel processing) compute cluster that can comprise one or more compute nodes.

The number of nodes in the compute cluster is called its "size" and the sizing options are made to resemble T-Shirt sizing, as you can see in the main image above.

Note that XS (extra small) is the smallest VW size. Using an XS for one hour consumes one Snowflake credit. In Australia, in Azure, right now, that's a little over $2 USD.

The number of credits used per hour for a VW is directly related to the number of compute nodes that it contains. So a 2XL VW consumes 32 credits per hour.

Threads

The other important aspect of this sizing is the number of threads. Currently, there are 8 threads per compute node.

So the number of concurrent threads for a VW goes from 8 at the XS level, up to 1024 at the 4XL level.

I'll talk more about threads later but they become important when you're trying to get good parallel loading of files happening, and when you want significant concurrency in query execution.

 

SQL: Think that T-SQL EOMONTH() just returns the end of month? Think again…

In a recent post, I wrote how the T-SQL TRIM() function was more than just an LTRIM(RTRIM()) and that it took me a while to realize that. Well today, I found another one that I hadn't noticed before: EOMONTH.

I was writing a SELECT clause for a query, when the Intellisense popped up:

And suddenly I noticed "Param2" and wondered what on earth the second parameter was.

Now you might notice that the Intellisense isn't very helpful on this. It gives you no hint what the parameters are. In fact, for many of the features added to T-SQL in SQL Server 2012, the Intellisense is really poor. Here's another example:

Good luck making sense of that helpful popup.

But a quick check of the T-SQL documentation showed it as an integer that changes which month it's returning values for. It's a number of months to add. That's a nice additional option. I'm surprised I hadn't noticed it before.

So in the main image above, you can see that as well as end of month for the current month when I'm writing this, I've also asked for end of month, three months ago. Nice !

 

 

SDU Tools: Calculate number of days in a month using T-SQL

When I'm working with dates, I often need to calculate how many days there are in a specific date.

So, in our free SDU Tools for developers and DBAs, we added a simple tool that does just that. It's called DaysInMonth.

It takes one parameter:

@Date date – the date to check

Nothing complex. It takes a date, and returns the number of days in the month that contains that date.

Find out more

You can see it in action in the main image above, and in the video here:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

 

Opinion: Make sure monitoring isn't causing your SQL Server performance issue

There's a well-known effect that's often described in physics as the Observer effect. The argument is that whenever you measure things, you invariably alter them. The simple example given in Wikipedia is that it's pretty hard to check the pressure of a tire (tyre) on a car, without letting at least some air out.

The same effect also happens in IT systems. A simple example is that to observe something, you might add logging or auditing, and the work to output those slows down the primary work that you are doing.

It's really important to make sure that your monitoring isn't having an adverse affect on your SQL Server workloads.

Example Issues

A few years back, I was at a client site. At the end of a room full of developers, they had a huge LCD screen that was showing how busy the system was. When I finished my tracing work, I could easily demonstrate that the heaviest workload that the system was executing, was the query that updated that LCD screen.

Yes, the system that was monitoring the workload was the largest source of the workload that it was monitoring.

At another site, there were hundreds of information kiosks. They were endlessly getting behind in communicating with the main system. Why? Because of endless monitoring queries being sent to them from the main system.

I now regularly come across really intrusive queries being performed by the very monitoring tools that customers purchased to help them avoid performance problems.

Query Quality

One aspect of this that isn't often discussed is that in many cases, the queries that are part of the core system are well-written and tuned, yet the queries that are part of the monitoring are quite poorly written, and often haven't been tuned at all. Many are added in quite an ad hoc way.

Systems Center Operations Manager (SCOM) monitoring queries are a common source of problems. I've lost count of how many times I've been confronted by nasty performance problems, caused entirely by queries that someone has added into SCOM, to keep an eye on the SQL Server system. Those types of queries don't usually get the oversight that the application queries might.

Check the Effect

I often see people checking for performance issues in their systems, and the first thing they do is to filter out any monitoring-related queries. That might not be the best idea.

 

 

 

T-SQL 101: #32 Repeating T-SQL batches with GO n

In my last T-SQL 101 post, I mentioned that GO was just a word that's used to separate batches. In fact, it's not a SQL word at all. If you actually send the word GO to the server, it wouldn't know what you're talking about.

I mentioned that the word is only understood by the program that you type the script into. SSMS also allows us to make use of another additional trick in relation to GO.

I can add a number after the word GO, and SSMS will execute the batch that number of times.

Here's an example:

First up, notice that the 10 is underlined with a red squiggle. The T-SQL intellisense has no idea what's going on with the number.

But when we execute it, SSMS outputs Beginning execution loop, then it sends the batch containing PRINT 'HELLO'; to the server 10 times (one at a time), then prints Batch execution completed 10 times.

SSMS is doing all the work. SQL Server just sees 10 separate batches sent to it.

This can be useful if you need to run a command many times.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

Snowflake for SQL Server users – Part 3 – Core Architecture

The first thing to understand about Snowflake is that it has a very layered approach. And the layers are quite independent, including how they scale.

Cloud Provider Services

The lowest level isn't part of Snowflake; it's the services that are provided by the underlying cloud provider. As a cloud native application, Snowflake is designed to use services from the cloud provider that they are deployed to, rather than providing all the services themselves. At present, that means AWS or Microsoft Azure. Deployment on Google's cloud platform is in preview at this time.

Each deployment of Snowflake includes the upper three layers that I've shown in the main image above.

Storage Layer

This layer is exactly what it says. It uses storage from the cloud provider to provide a way to store anything that needs to be persisted. That includes, the obvious things like databases, schemas, tables, etc. but it also includes less obvious things like caches for the results of queries that have been executed.

Storage is priced separately from compute. While the prices differ across cloud providers and locations, the Snowflake people said they aren't aiming to make a margin much on the storage costs. They're pretty much passing on the cloud provider's cost.

In addition, when you're staging data (that I'll discuss later), you can choose to use Snowflake managed storage or your own storage accounts with a cloud provider to hold that staged data.

Compute Layer

This is the heart of where Snowflake make their income. The compute layer is made up of a series of virtual clusters that provide the compute power to work on the data. Importantly, each of the virtual clusters (called Virtual Warehouses) can independently access the shared underlying storage.

Compute is charged by consuming what are called "credits". I'll write more about those soon. What is interesting is that you only pay for compute while a virtual warehouse is running.

While there are some blocking commands, the idea is that you should do most of those in staging areas, to keep your shared storage accessible in a highly concurrent way. The aim is to have a virtual warehouse happily querying the data, while another virtual warehouse is in the middle of bulk loading other data. And the virtual warehouses can each be different sizes.

Global Services Layer

This is the layer where all the metadata and control lives. It's also where transactions are managed, and where security and data sharing details live. I'll describe each of its functions in future posts.

 

SDU Tools: Create SQL Server Login with SID from a database

In SQL Server, both logins (access to the server) and users (access to a database) have a name and a security ID (SID). This leads to problem situations where names might match but SIDs don't match.

Common Problem

I've lost count of the number of times I've seen a user restore a database from another server, and then realize that the SQL Server login they need wasn't present. Then, they create a new login and end up in a lousy situation, because the new login's SID doesn't match the SID of the user in the database.  I call this a mismatched SID issue.

The error message will tell you that the user doesn't exist in the database, but when you try to add the user, it will tell you it already exists. That's the sort of thing that makes people want to throw their mouse through their screen.

Microsoft Solution and why it's not enough

There is a Microsoft solution for fixing this problem.  For a long time, the command that people used was:

sp_change_users_login

Lot's of people still use that command. If you're one of the cooler kids, you'll know that the current way to fix a user is to instead do this:

ALTER USER Blah WITH LOGIN Blah;

It's described here.

What these commands do though, is change the database SID to match the one for the login. I think they're fixing the wrong problem.

The problem with this solution though, is that most of these restores aren't one-offs. And when the next restore is done, you'll be back in the same problem.

Avoiding the Issue

Now there is of course a way to have avoided this issue in the first place. Instead of just creating a new login and getting a new SID, you could have specified the SID when creating the login. Then it would match and there wouldn't be an issue.

You could have retrieved the SID by scripting it out when you scripted the login (if you scripted it – most people with this problem haven't).

Another solution

Another solution would be to retrieve the SID from the database that you can't access and use it for creating the new SQL login.

In our free SDU Tools for developers and DBAs, we have a tool called CreateSQLLoginWithSIDFromDB that does just that. It's a long name but it does precisely what it says.

It is quite configurable and has a few parameters (many of them optional):

@SQLLoginName sysname -> Name of the login to create
@Password nvarchar(128) -> Password to assign
@SIDDatabaseName sysname -> Database to retrieve the SID from
@DefaultDatabase sysname -> (Optional) default database for the login
@DefaultLanguage sysname -> (Optional) default language for the login
@IsCheckExpiration bit -> (Optional default 1) is expiration to be checked?
@IsCheckPolicy bit -> (Optional default 1) is policy checked?
@SIDDatabaseUserName sysname -> (Optional default @SQLLoginName)

Find out more

You can see an example of calling it in the main image above, and see it in action in the video here:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com