Sql-Server

If you used computers much in the 1980’s or 1990’s you’ll remember jokes that circulated all the time, but encoded in ROT13. It was a type of Caesar Cypher or Shift Cypher where the letters in the original words were shifted by a fixed number of positions in the alphabet to create the cyphertext.

ROT13 was a specific type of shift cypher where, for our alphabet of 26 characters, a 13 character shift is applied. That means that the same function can be used to encode text and then to decode the same text.

2019-10-02

Today, I’m continuing the discussion on the variety of data types supported by SQL Server. Last time I mentioned exact whole numbers, but there are also exact decimal numbers. Here are the available types:

When I need to work with numbers with decimal places in them, the data type that I normally use is decimal.

It has a fixed precision and scale so for example, if I say:

decimal(18,3)

2019-09-30

There are many things I like about Snowflake. How they handle case and collations is not one of them.

There are currently no rich options for handling case like you have in SQL Server, with detailed options around both collations, and case sensitivity.

I’ve previously written about how I think that case-sensitivity is a pox on computing. I see absolutely no value in case-sensitivity in business applications, and a significant downside.

2019-09-27

I had a number of responses to my blog post about maintaining an online website’s data during full data refreshes by using partitioning. Lots of people asked for a code sample, so I’ve provided it here.

It’s really common to want to keep a website online as much as possible, but need to rebuild the data that it’s displaying periodically. It might be a website for members of a superannuation fund, and you need to let them see balances as of last night. The problem is that you can’t have the data missing for long or the website isn’t going to keep working. You can’t just truncate the tables, and start repopulating them again. You need to make the changeover as quick as possible.

2019-09-26

There are a few things in IT that seem to rarely change. One is the use of CSV (comma-separated value) files. It’s a pretty basic file format and sometimes we see other delimiters like tabs (aka TSV files) or pipe symbols but these types of files are still everywhere.

So it’s hardly surprising that people want to work with them in T-SQL as well. In our free SDU Tools for developers and DBAs, we added two functions to help: NumberOfTokens and ExtractToken.

2019-09-25

At one of my customer sites, I recently started having an issue logging onto an Azure SQL Database. The customer had configured Azure Active Directory (AAD) and were using Multi-factor Authentication (MFA).

I had previously been using it OK.

I would connect to the server using SSMS, enter my username and password, and then be prompted for the MFA authorization. After I authorized the logon, I would then receive the error shown in the main image: Login failed for user ‘NT AUTHORITY\ANONYMOUS’ and Error 18456.

2019-09-25

I previously mentioned that SQL Server supports a wide variety of data types. The first of these are what we would consider exact numbers. Here are the available types:

So there’s a tinyint that’s basically one byte in size. One byte (8 bits) gives us 2^8 possible values, so we have 256 possible values. The first one is zero, so we have values up to 255.

Note: tinyint doesn’t allow for negative values.

2019-09-23

Authentication in Snowflake is based around user identities. That’s very similar to what we have today with SQL Server authentication (i.e. SQL Server logs you on), as opposed to Windows Active Directory authentication (i.e. Windows AD logs you on), or  Azure Active Directory authentication (i.e. AAD logs you on).

I can create users if I’m a member of either the SECURITYADMIN or ACCOUNTADMIN roles.

Snowflake has a single specific password policy:

2019-09-20

Every so often I hear a discussion start about whether ID should be used with both letters capitalized, or whether it should be Id.

The argument from the Id proponents is pretty simple. Id is short for identifier and when we use other acronyms, we normally just capitalize the first letter from each word.

I understand that argument and I see some merit in it. However, that’s not the way I use it.

2019-09-19

When I’ve reviewing customer databases, I often come across tables that have been created by users, yet they are completely empty. Now that might be perfectly OK but sometimes, it’s a mistake that should be cleaned up.

So, in our free SDU Tools for developers and DBAs, we added a tool that can look for user tables that are empty. No surprise, it’s called ListEmptyTables.

It takes three parameters:

@DatabaseName sysname - the database to look into @SchemasToList nvarchar(max) - a comma-delimited list of schemas to check (or ‘ALL’) @TablesToList nvarchar(max) - a comma-delimited list of tables to check (or ‘ALL’)

2019-09-18