Some of the simpler tools that we’ve added to our free SDU Tools collection over the last year or so, have turned out to be quite useful.
A good example of this is CountWords.
This function takes a T-SQL string, removes the punctuation, excess whitespace, etc. and then counts the number of words contained. There are a few things that can still trick it (avoiding that would need very detailed langauge parsing) but it’s quite good.
2018-03-21
When you create script out a table in SQL Server using SQL Server Management Studio, you’ll notice that it scripts more than just the table itself. Before the table, it scripts the values for ANSI_NULLS and QUOTED_IDENTIFIER.
A common problem that I see is that someone highlights the CREATE TABLE statement and runs it, without highlighting the SET options above it. That has the chance of leading to the wrong values. I don’t run into big issues with QUOTED_IDENTIFIER but I certainly run into issues with ANSI_NULLS.
2018-03-14
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.
For example, have they made newbie mistakes like using float for storing amounts of money.
One of our free SDU Tools makes this easy.
In the image above you can see the ListAllColumnsAndDataTypes procedure in use.
2018-03-07
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
2018-02-28
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.
2018-02-28
I’m not a great fan of dynamic SQL (where you create a T-SQL command in code, then execute the command) but there are times when you just need to do it.
When creating dynamic SQL, it can get very painful trying to work out which strings are quoted and how many quotes you need.
To make this a bit easier, in our free SDU Tools, we added a function for QuoteString.
2018-02-21
Prior to SQL Server 2012: IDENTITY Columns
When we needed to automatically number rows in SQL Server prior to SQL Server 2012, one option was to use IDENTITY columns.
(Apologies to purists who think these were the spawn of the devil but real people did use them).
One of the challenges with IDENTITY columns was when you wanted to move data from table to table but needed to retain the same value from the IDENTITY column. To do that, we used SET IDENTITY_INSERT tablename ON; That was fine but an issue that arises, is that if the next IDENTITY value was going to be 245001 and you just inserted a row with 245001, what would happen with you turned IDENTITY_INSERT back off and tried to insert another row.
2018-02-14
One of the endless challenges when working with databases (and not just SQL Server databases) is processing dates and times.
While there are some good date-handling functions in the T-SQL language, there are many more gaps in what’s offered. This month, we’ve filled another one of those gaps.
In Australia, whether staff are religious or not, the Christian Easter is associated with a four-day weekend for most workers except those in retail ie: Friday (Good Friday), Saturday (Holy Saturday), Sunday (Easter Sunday), and Monday (Easter Monday). For many, it looks like this:
2018-02-07
Percent Encoding (also known as URL Encoding) is a technique for encoding the values in strings that are not able to be directly represented, according to the rules for the target location. For example, not all characters are permitted in URLs.
Percent encoding a T-SQL string is also useful when working with XML. For example, one common method for splitting strings is based on the use of XML PATH but if you try to use those types of string splitters with strings containing, say, percent signs, you’ll find they just don’t work.
2018-01-31
Today’s post is about one of our free SDU Tools that helps you with what you’d think would be a simple task in T-SQL but isn’t.
TrimWhitespace is used to remove leading and trailing whitespace characters in T-SQL strings. As well as spaces, it will remove carriage returns, linefeeds, and tabs.
You can see its action in the image above.
You can also see it in action here:
2018-01-24
