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
In a recent blog post, I described converting strings to Proper Case and Title Case. And more recently, I described how to convert them to Pascal Case and Camel Case.
The final option (for now) in this set is conversion to Snake Case and Kebab Case.
In Snake Case, all words are lower-cased, then all spaces between words are replaced by underscores.
You can see it in this example:
2017-12-29
When you first start working with SQL Server, you’re often working with smaller databases and every backup seems to happen quickly. SQL Server is fast at creating backups.
When you get to larger databases, backups start to take a while. Fortunately, backup is one of the commands that sets a percentage complete value that can be seen in the sys.dm_exec_requests system view.
And when the databases get even larger, the question quickly becomes:
2017-12-27
