On occasions I have to work with binary strings in SQL Server using T-SQL. The literal values are hexadecimal strings. T-SQL doesn’t have a simple function to just convert one of these character-pair strings to a character.
In our free SDU Tools for developers and DBAs, we added a function HexCharStringToChar to do just this.
You can see the outcome in the main image above.
You can see it in action here:
2018-09-19
I often need to do a quick check to see if the schema of two SQL Server databases is the same.
In our free SDU Tools for developers and DBAs, there is a stored procedure called GetDBSchemaCoreComparison to make that easy. It takes the following parameters and returns a rowset that’s easy to consume programmatically (or by just looking at it):
@Database1 sysname -> name of the first database to check @Database2 sysname -> name of the second database to compare @IgnoreColumnID bit -> set to 1 if tables with the same columns but in different order are considered equivalent, otherwise set to 0 @IgnoreFillFactor bit -> set to 1 if index fillfactors are to be ignored, otherwise set to 0
2018-09-05
We’ve been building tools to create scripts for various SQL Server T-SQL objects for a long time. Part of scripting a table is the scripting of the data type. That means its datatype name, precision, scale, and maximum length.
In our free SDU Tools for developers and DBAs, we added a scalar function called FormatDataTypeName ot make that easy. It takes the following parameters:
@DataTypeName sysname - the name of the data type @Precision int - the decimal or numeric precision @Scale int - the scale for the value @MaximumLength - the maximum length of string values
2018-08-29
Fellow MVP Paul White and others have discussed the indexing of small segments of strings, to make fast indexes that I think are somewhat like full-text indexes but potentially more efficient. Three character substrings are common, and are known as trigrams.
I’ve experimented with these at a number of site and seen really interesting (good) results.
In our free SDU Tools for developers and DBAs, we added a table-valued function ExtractTrigrams to help with this. You can pass it a string, and it will pull it apart for you, and return the set of trigrams. This would make it much easier for you to experiment with these types of indexes.
2018-08-22
Occasionally I’ve needed to take a string, and extract all the words out of it. For example a string like ‘hello there greg’ might lead me to want the three words ‘hello’, ’there’, and ‘greg’. Note that I usually want them trimmed, not just extracted.
In our free SDU Tools for developers and DBAs, we added a table-valued function ExtractTrimmedWords to help with this. You can pass it a string, and it will pull it apart for you, assuming that you have whitespace separating the words.
2018-08-15
The PRINT statement in SQL Server’s T-SQL language is useful but one of the biggest restrictions with it is the size of the strings that it can print. Where this becomes a big issue is if you are needing to create dynamic SQL statements (which you obviously need to be careful of in the first place) or scripting database objects, and the statements need to be either executed or printed.
2018-08-08
You can execute SQL Server Agent jobs from within T-SQL code. The procedure sp_start_job can do that.
That’s all well and good but notice that it doesn’t say “execute job”; it just says “start job”. The command starts the execution of a job but has no interest in when it completes.
Sometimes, you need to be able to start a job and wait for it to complete before taking a following action.
2018-08-01
I regularly find myself writing repetitive code in T-SQL. Some things are best done by just creating code snippets but we’ve also added several others to our free SDU Tools for developers and DBAs.
One straightforward one is DropTemporaryTableIfExists.
This just wraps all that’s needed to remove a temporary table if it exists. The nice thing with this procedure is that you can call it before creating a temporary table, and call it again after you finish using the temporary table, as shown in the main image above.
2018-07-25
In our free SDU Tools for developers and DBAs, we have added many tools that help to manipulate dates.
When creating a date dimension (as part of dimensional modeling), you need to be able to get a list of all the dates between a start date and an end date. There are many other reasons why you might need to do this as well.
So we’ve added a table-valued function called DatesBetween to do just this. It takes a start date and an end date as parameters and returns all dates between. As well as the date values, it also numbers each of the dates.
2018-07-18
I spend a lot of time reviewing other people’s databases. Apart from looking at table designs, data types, keys, etc. I’m often interested in what the typical data in the table looks like.
In our free SDU Tools for developers and DBAs, we have added a special tool called AnalyzeTableColumns that makes this type of review easy. You point it at database, schema, and table, and it tells you about the table.
2018-07-11
