How difficult can it be to produce a simple hierarchical list in JSON, YAML, XML and HTML from a SQL Server table that represents a simple hierarchy within an organisation. Well once you know, it is easy and William Brewer is on a mission to tell you how … Read more
There are very few real breakthroughs in IT. Most progress in the industry comes from pure effort and attention to detail. When breakthroughs happen, they are like those famous moments in the construction of Alpine tunnels where two teams, start in separate foreign lands working determinedly for years in opposite directions, apparently digging a bigger … Read more
I faced this problem some years ago. The company I was working for had a lot of clients with very low-quality communications, so each client needed its own local server and BI solutions. A very small DBA team had the task to deal remotely with hundreds of SQL Servers across low-quality communication links and sometimes, … Read more
My new article about Graph Database Objects was just published, it’s really a very good new feature. Another new feature we have since SQL Server 2016 is R language. We can use R language inside the database to analyze our data. The possibility to use both technologies together is very interesting. Using graph objects we … Read more
Microservices can certainly be made to work well for particular types of applications, but is it relevant to the mainstream? Can it replace the traditional architectures of database-driven applications? Microservice architecture is a type of service-oriented architecture that was developed from the concept of Domain-Driven design (DDD) and consists of loosely-coupled services that are network-based. … Read more
Imagine you have some data. Maybe you’ve done a web redesign and you want to do some A/B testing to see if by redesigning the page your visitors spend more time reading your content. You set up an experiment in which visitors are randomly sent to either the old or new design, and you measure … Read more
A few weeks ago I published an interesting article about how to use query store to identify parameterization problems. At that point I hadn’t played with the new SQL Server 2017. While later, when I did, I got surprised by the new ‘Queries with High Variation’ graph included in query store. This new graph can … Read more
SQL Server 2017 brings us some new T-SQL functions. They are very simple to use, and can also help us to simplify our T-SQL code. I’ll be talking about them in this article. String_AGG This new function solves an old and very interesting problem: How can we concatenate the contents of a column from several records in … Read more
The old system function IsNumeric() often causes exasperation to a developer who is unfamiliar with the quirks of Transact SQL. It seems to think a comma or a number with a 'D' in the midde of it is a number. Phil Factor explains that though IsNumeric has its bugs, it real vice is that it doesn't tell you which of the numeric datatypes the string parameter can be coerced into, and because it doesn't check for overflow. Phil comes to the rescue with a couple of useful alternatives, one of which works whatever version of SQL Server you have, and which tell you what datatype the string can be converted to.… Read more
The idea that one can do nothing useful as a developer unless you are in an open plan office is rather retro. Although we all get that knowing wink when we announce that we are “Working from Home”, the truth is more complicated, I think. Some people are more productive when they work from home, … Read more
SQL Server 2017 brings to us some new interesting DMV’s and DMF’s, one of them is sys.dm_db_log_info. This new DMF allows us to manage VLOGs in our databases. The log files are divided in smaller slices called virtual log files, or vlog. Operations such as log truncations happens over an entire vlog. For example, when … Read more
I just faced this problem when trying to analyze some information from google analytics in PowerBI. In my specific situation, I was trying to extract a number from some URL querystrings and filter the rows without the number. When I set the data type of the column to decimal, the rows without a number receive … Read more
So, the story goes that an Ops engineer walked into a data center with the necessary pass, a cheery wave and a ‘good morning’. Shortly afterwards, he made history. At around 8.30AM, British Airway’s entire communications systems went down at the height of the May holiday, forcing them to cancel flights from the UK’s two … Read more
SQL Server 2017 brings several improvements to us. Sometimes a simple new field in a DMV can turn possible interesting new functionalities. That’s what happens with differential backup in SQL Server 2017. The DMV sys.dm_db_file_space_usage has a new field: modified_extent_page_count . This new field tell us how many pages were changed since the last full backup. … Read more
Before you report your conclusions about your data, have you checked whether your 'actionable' figures occurred by chance? The Kruskal-Wallis test is a safe way of determining whether samples come from the same population, because it is simple and doesn't rely on a normal distribution in the population. This allows you a measure of confidence that your results are 'significant'. Phil Factor explains how to do it.… Read more
SQL Server 2016 has a new configuration to control the auto-growth of multiple files in the same filegroup. When we create several files in the same filegroup SQL Server does a round robin across the files, writing a piece of data in each of them until all the data is finally on the files. However, … Read more
The WannaCry ransomware attack has highlighted a serious problem. If there is negligence in your IT strategy, you are increasingly risking the functioning of your organisation, and the privacy of your customers. If you are being careless with data you don’t own, and of which you are legally only the custodian, if you are storing … Read more
The first and overriding thought of the experienced developer given any new development task is, or should be, ‘How the heck am I going to test this?’. I wonder, though, how often developers neglect to ask this question. Nowadays, with Visual Studio, the debugging facilities are so good you can muddle along happily for a … Read more
There are many reasons for wanting to know how data is distributed. Sometimes you just want a rough idea of the way that data is distributed in a column. You may think, wouldn't it be nice to have a SQL function that just showed you roughly what the distribution was, graphically, in the results pane. Phil Factor thought that was well and turned the vague wish into reality.… Read more
Anyone using R in SQL Server employs the procedure sp_execute_external_script, the first parameter of this being the language to use. The documentation rather obliquely says that “the script must be written in a supported and registered language“. Until recently, the only language was R, but now a second supported language, Python, has appeared. Yes, at … Read more
Subscribe for more articles
Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.
Subscribe to our fortnightly newsletter
How you log in to Simple Talk has changed
We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.
