Press "Enter" to skip to content

Month: July 2021

Against sp_hexadecimal and sp_help_revlogin

Published 2021-07-07 by Kevin Feasel

Andy Mallon says it’s time to give up a couple of procedures:

We recently ran into some performance problems with our login sync, which is based on sp_hexadecimal and sp_help_revlogin, the documented & recommended approach by Microsoft.

I’ve been installing & using these two procedures since I started working with SQL Server, back at the turn of the century. In the nearly two decades since, I’ve blindly installed & used these procedures, first on SQL Server 2000, and then on every version since… just because that’s the way I’ve always done it. But our recent performance problems made me rethink that, and dive in to take a look at the two procedures to see if I could do better, which made me realize, OHBOY! WE CAN DO BETTER!!

Read on to understand how.

Comments closed

Goodbye, SQL Server on Windows Containers

Published 2021-07-06 by Kevin Feasel

Amit Khandelwal shuts the doors:

As you may be aware, the SQL Server on Windows Containers Beta program began in 2017. It has remained in Beta mode meant for only test and development environment until now. Due to the existing ecosystem challenges and usage patterns we have decided to suspend the SQL Server on Windows Containers beta program for foreseeable future. Should the circumstances change, we will revisit the decision at appropriate time and make relevant announcement.

I never heard of many people using Widows containers, but with the differences in available products and features between Windows and Linux versions of SQL Server, I can see why some people would want to use them.

Comments closed

Handling Tombstones in Cassandra

Published 2021-07-06 by Kevin Feasel

Payal Kumari takes us through tombstone management in Apache Cassandra:

Got too many tombstones? This blog post will talk about how to deal with tombstones once you already have them. For more information about tombstones, check out this post: Examining the Lifecycle of Tombstones in Apache Cassandra.

Click through for several techniques for handling tombstoned records in Cassandra. In SQL Server, with columnstore indexes, the prevention advice is similar (avoid deletion or updating of data) but the treatment options are quite different.

Comments closed

The Importance of SQL for Data-Related Jobs

Published 2021-07-06 by Kevin Feasel

Camila Henrique explains the importance of knowing SQL:

I wanted to talk about this because I see a lot of doubts and lack of direction from people who are either beginning now in IT land or thinking about switching careers. The short answer to “do I need to know SQL for a data job?” , is yes. In the next few paragraphs I explain why I think so.

It’s easy to get caught up in all the fancy programming languages and methodologies for projects that sometimes the basics… are just not there. I believe having a good foundation opens paths to other doors that you could not see before. And I’m certain that SQL is one hell of a foundation to have in the data land.

This is also a good time to slip in a reminder of Feasel’s Law.

Comments closed

Is sysname Case-Insensitive?

Published 2021-07-06 by Kevin Feasel

Solomon Rutzky tries Betteridge’s Law of Headlines:

Over time I’ve used a variety of SQL Server versions with a variety of instance-level collations. This has lead me to conclude that sysname had to be all lower-case in some earlier versions if the instance-level collation was either binary or case-sensitive. However, it was not clear exactly which versions and what scenarios truly affected the behavior of sysname name resolution, so I played it safe and continued to always specify that particular data type in all lower-case, even leaving comments in scripts that it must remain as all lower-case (just in case someone goes through and tries to make it upper-case to be consistent with the other data types).

But then, while researching another topic, I recently found the following in some old documentation ( Breaking Changes to Database Engine Features in SQL Server 2005 ):

Read on for the results of Solomon’s archaeological expedition.

Comments closed

Power BI Report Iconography

Published 2021-07-06 by Kevin Feasel

Joe Billingham brings in the Unicode characters:

There are 143,859 Unicode characters available, everything from emojis, symbols, shapes and braille patterns to dice and playing cards. Whether you want to offer further insight into your data, enhance the user experience or simply create something sublimely ridiculous, with so many icons at your fingertips, the possibilities are only limited by your imagination.

Click through to see how you can include specific Unicode characters to create a visual link in the mind of your viewer to your data.

Comments closed

Alternatives for ISNUMERIC

Published 2021-07-06 by Kevin Feasel

Erik Darling opposes expansive numeral policies:

A while back I blogged about how ISNUMERIC can give you some wacky results.

Working with a client… Well, it’s hard to know based on my publishing schedule. But they were using ISNUMERIC all over the place, and getting the same wacky results, and even errors.

Click through to see what types of things ISNUMERIC() considers to be numbers and for a couple of alternatives.

Comments closed

June 2021 Power BI Updates

Published 2021-07-06 by Kevin Feasel

Chris Webb takes a look at three new features in Power BI:

There were a couple of new features and enhancements to existing features in the June 2021 Power BI Desktop release that don’t seem to have much to do with each other but which I think can be combined to do cool things. They are:

1. The new paginated report visual
2. Native SQL support in the Snowflake connector
3. Improvements to dynamic M parameters

Let me give you an example of what I mean…

Click through to see how all of these might fit together.

Comments closed

Optimizing for Ad Hoc Workloads

Published 2021-07-06 by Kevin Feasel

Chad Callihan explains the importance of a feature:

Anytime you run a query, SQL Server needs to build an execution plan to use as directions for best executing that query. These execution plans can be stored in your plan cache to be reused in the future if that same query is ran. Instead of resources going into rebuilding the plan each time, SQL Server can use the same plan as the previous execution. This is great for queries that run over and over. On the other hand, what if you have a large number of queries that will run once but never again? Plans never to be reused are taking up valuable space in plan cache. If this looks like your workload, consider enabling the Optimize for Ad hoc Workloads feature.

Click through to learn more about the feature. I haven’t seen many (any?) cases where Optimize for Ad Hoc Workloads doesn’t help at least a little on net.

Comments closed