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
Ever since I started working with SQL Server back in 1992, the #1 requested feature that I kept hearing about was a separate date data type. SQL Server had a smalldatetime and a datetime but these included both date and time within the same type.
Having date and time often led to odd bugs or performance issues, where people didn’t realize that times were included and tried to work with the values as dates.
2018-01-17
When I first start to work with a database that I haven’t seen before, one of the things that I check immediately is the type of data being stored. The list of existing data types often gives me interesting insights into the design of the database, and how it interacts with application code.
The list of data types also shows me details of the consistency (or lack of) in the design.
2018-01-10
Is 2018 be a leap year?
Was 2000 a leap year?
Will 2100 be a leap year?
When I was a young student at school, we learned that leap years were every four years. Then as I got older, I learned that there was more to it than that.
It’s important to understand that any calendar is just an approximation, and there is a lot more to calendars than most people realize.
2018-01-03
