On occasions, I need to work with XML data in SQL Server T-SQL and one problem is that I need to be able to encode and decode strings, using the XML encoding standards. For example, my strings might include % characters or < or > characters, etc.
So we added a pair of tools (XMLEncodeString and XMLDecodeString) our free SDU Tools for developers and DBAs.
You can see them in action in the main image above, and in this video:
2019-02-27
There was an interesting discussion on a local mailing list about the performance of a table that was just used for logging. One of the things that intrigued me was that the developer had used the text data type to store his log messages.
Now text, along with ntext and image, was deprecated back in 2005. Instead of text, ntext, and image, you should be using varchar(max), nvarchar(max), and varbinary(max).
2019-02-26
You might be wondering what databases are.
A database is just a collection of information. Let’s take a look at an example:
On this system, I have a number of user databases (AdventureWorks, PopkornKraze, and others). There are also some system databases:
These are ones that are provided by SQL server itself, and mostly are used to hold SQL Server’s own configuration.
Now if we look inside any of the databases, we’ll see a list of tables, views, and programmable objects like stored procedures, and functions. Tables are where most of the information lives. In PopkornKraze, you can see the tables that are present:
2019-02-25
Ever since I’ve done development work on Windows, I’ve seen two things happening:
This is a long-term nasty problem, but I thought I’d share today an anecdote I was told by a Microsoft product group member about how easy it is to get security wrong during development, if you always develop as an admin.
2019-02-22
Every now and again, I come back to my laptop and find that it has rebooted for some reason, while I wasn’t expecting it. A prime cause of that is Windows Updates. I really, really wish that wasn’t so, but someone at Microsoft has decided that I must apply these updates. I have very little control over the time when that occurs. For example, if I’m on the road delivering presentations, there’s no “wait till I get home” option for Windows Updates.
2019-02-21
In a previous SDU Tools post, I described how to calculate the columns required for a date dimension. Another related tool that we have in our free SDU Tools for developers and DBAs, is a function that creates the columns needed for a time period dimension. It’s called TimePeriodDimensionColumns.
You supply the starting time, and the number of minutes per time period, and it supplies the output columns.
You can see it in action in the main image above, and in this video:
2019-02-20
In a recent post, I discussed questions that had come up about how we should calculate the number of days between two given dates, but excluding the weekends i.e. people just wanted the number of weekdays.
This relates to one of our SDU Tools called DateDiffNoWeekends. Some customers using it felt that it didn’t calculate the days in the way they thought it should. And on reflection, I agreed.
Much of the discussion centred around how we should treat start and end dates, depending upon whether they were on the weekends or not.
2019-02-19
Before, you can access anything on SQL Server, apart from knowing what to call the server, the server itself needs to know who you are. This process is called authentication.
In SQL Server, a login is person that the server has identified.
Now access to the server by a login doesn’t mean that you can access anything much on the server, it’s just the first step where you get the server to trust who you are.
2019-02-18
Some T-SQL functions like DATEPART work differently based upon your session settings. If you want to write reliable code, you should always check for these issues, and try to write code that’s impervious to these settings.
Let’s take finding the day of the week as an example. It might seem that to find out if a day is a Sunday, you could just do this:
DECLARE @DayToCheck date = ‘20190224’;
2019-02-15
SQL Server Management Studio (SSMS) keeps a list of the server names that you have connected to, and prompts you with those when you drop-down the list while making a connection:
Eventually, that list can either become messy, it can include servers that don’t exist anymore, and so on. You might want to clear up the list.
To do this in early versions of SSMS, you needed to locate the SqlStudio.bin file from the Documents and Settings area in your user profile.
2019-02-14
