Press "Enter" to skip to content

Day: September 28, 2020

Using Tables for Visualization

Alex Velez takes us through one of the simplest visuals:

Tables are a common way to show data, but in my current work, I don’t create them frequently. Admittedly, when I come across a table, I often choose to visualize the data. I should clarify that I am not proposing that we never build tables. Instead, let’s understand their benefits and shortcomings so we can be thoughtful about when to use them and how to design good data tables.

Click through for some interesting thoughts around a sometimes-forgotten visual.

Comments closed

Visualizing Seinfeld

Andy Kirk walks through a brand new book:

This project is a compilation of visual analysis I’ve been working on for a couple of years to pursue a curiosity I had about the rhythm and texture of Seinfeld, looking into how it was written and how it was produced.

The musicality of language is something defines the signature comedic talent of Jerry Seinfeld and, co-creator, Larry David. Like music, situation comedy has rhythm and it has texture. The rhythm is shaped by its temporal structures and pacing; textures are formed from the arrangements of characters and locations which amalgamate into the comedy. All orchestrated by the storylines and dialogue emerging from the writers.

It is an exploration of the show’s musicality that forms the purpose of this book. It presents a portfolio of visuals produced, intended for people who share my Seinfeld fanaticism. It is a body of work akin to giving the show something of a sports analytics treatment, investigating how Seinfeld and David utilised their resources – the people, places, and the dialogue – to translate their creativity from the page and onto our screens.

It’s an interesting look at the show and there will be a limited run on physical copies of the book.

Comments closed

Finding Unused SQL Server Tables

Nisarg Upadhyay shares a few techniques to look for whether a table is currently in use:

In this article, I am going to demonstrate how we can identify the unused tables of a given SQL Database. As a database administrator, we must maintain the tables and objects of the SQL Database. In my organization, when we add a new column or change the data type of a column, we export the data of the existing table in the backup table. Often, we forgot to review and maintain those backup tables. So as a solution, we decided to create a SQL Job that populates the list of unused tables and email the list to the DBA Team for review.

The best way to decommission any table is to rename it first, and if it does not break the functionality of the application, we can drop the table and its dependencies. We decided that after a review of the unused table completes; we will rename the tables and later drop them.

Click through for the techniques and a script which checks tables. It does look fairly reasonable, with the concern being if you restart the SQL Server instance or look at a table which is only accessed at a particular time of year.

Comments closed

Using Stopwords and Stoplists with Full-Text Search

Haroon Ashraf walks us through stoplists and stopwords in SQL Server Full-Text Search:

First, let’s clarify the essence of Stopwords and Stoplist. Then we’ll proceed to use them to improve Full-Text Search.

A Stoplist

A stoplist, as the name implies, is a list of stopwords. When associated with Full-Text Search, the Stoplist can filter out meaningless words or terms, thus improving search results.

A Stopword

A stopword is a word that has a minor role in Full-Text Search, despite being important grammatically. Therefore, a stopword is not essential from the Full-Text Search perspective.

According to Microsoft documentation, a stopword can be a word with some meaning in a specific language, or it may be some token with no linguistic value. In both cases, it is useless for the Full-Text Search.

Read on to see examples and how to build your own stoplists.

Comments closed

Finding Loops in an Active Directory Hierarchy

Jana Sattainathan builds a recursive solution to an interesting problem:

Here is the example he gave me:

I am looking for a way to detect recursion loops in a specified AD Group.  Take this example of groups and members. I started with Group1, and found Group2, with group member Group2. Group2 has a group member Group3. Enumerating Group3 finds Group1, and loop!

He also said: Some groups are nested many levels down, and a single top level group can have more than one recursive loop. The script should be able to not only identify Group1>Group3>Group1, but also Group1>Group8>Group5>Group15>Group1.

This is a good use case for recursion.

Comments closed

Public Preview of SQL Server on Azure Arc

Sasha Nosov gives us an update on Azure Arc:

The preview includes the following features:

– Use Azure Portal to register and track the global inventory of your SQL instances across different hosting infrastructures. You can register an individual SQL instance or register a set of servers at scale using the same auto-generated script.

– Use Azure Security Center to produce a comprehensive report of vulnerabilities in SQL servers and get advanced, real time security alerts for threats to SQL servers and the OS.

– Investigate threats in SQL Servers using Azure Sentinel 

– Periodically check the health of the SQL Server configurations and provide comprehensive reports and remediation recommendations using the power of Azure Log analytics.

Click through for more information and documentation.

Comments closed

Multi-Tenant Database Designs

Adrian Hills walks us through four database designs for multi-tenant data storage:

In my previous blog post, I talked about some of the key considerations around designing a multi-tenant system using SQL Server. There are several ways to implement multi-tenancy, and, as is often the case, there is no single “best” way but rather a range of options that each offer different trade-offs. The approach that is right for you depends on your objectives and needs for your specific environment. It’s important to consider which of these approaches best suit your requirements and goals based on the 3 core considerations from Multi-Tenancy with SQL Server, Part 1: security, maintainability (manageability), and scalability.

The following are the 4 approaches I will cover in this blog post:
1. Single database, shared schema
2. Single database, separate schema
3. Database per tenant
4. Multiple databases, multiple tenants per database, shared schema

I’ve worked with options 1, 3, and 4. Read on for Adrian’s thoughts. Ceteris paribus, my preference is 3. That said, I’ve worked in a situation where I migrated from 3 to 1 because there were thousands of customers, none of whom had more than hundreds of megabytes worth of data. 4 provides a good balance in that fashion, where you can bunch up smaller clients and give larger clients their own databases (and sometimes even servers). But if you’re going with options 2, 3, and 4, you probably want a central data warehouse which collects data across all four for internal use.

Comments closed