In Praise of User-Defined Datatypes

User-defined datatype aliases assist in allowing clear readable code. Without them, you can still be consistent in your naming of a column, parameter or variable as 'price', but if you use, for example, a datatype called 'price', then your naming can be used to explain more about the way the column, parameter or variable is being used.

When used consistently, they help you to follow the lineage and flow of data through that rats-nest of procedures, functions and tables. You can categorise data. You can, if so requested, provide information about where sensitive information is being processed or stored and whether it is being properly curated. You can attach extended properties to them to further document their use.

They can also provide the means to discourage bad data, because you can use them to create data 'domains' that tie down specific application data, such as a postal code, as closely as possible to a definition. A date, for example, can be a wide range of values, whereas a date of birth has certain restrictions on it unless you are Methuselah or Count Dracula. An account code, an order code or a part number may all be stored as varchars but will have their own divergent rules and defaults.

We used to create data domains in Transact-SQL by binding types to Rules and Defaults (the similarly named 'default constraint' is restricted to columns). From 2005 onwards, both rules and defaults have been threatened with removal from SQL Server. Many of us still look at them longingly, as they languish with rather severe notices warning you not to use them. They are unloved because they are not in the SQL Standard, but part of Transact SQL as devised by Sybase before the ANSI SQL Standard came into being. However, if you're a SQL Purist, there are several other useful features that you'd have to jettison too.

You can argue that no domain datatype, such as 'product cost', will be in more than one table, so that it is enough to use table based NOT NULL constraints, CHECK constraints and DEFAULT constraints. The table, you could argue, defines the data domain. Well, no, not just because these are attributes, not entities, but because we also use temporary tables and table variables that need to be consistent. We have functions, batches and procedures that require parameters and that produce results that must be consistent too.

Of course, Rules have limitations. Since they aren't table-scoped, you can't use them to compare columns on a table, like you can with a CHECK constraint. Also, a rule is only checked when data is inserted or updated and can't check on existing data in a table. You can't bind a rule to a text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, CLR user-defined type, or timestamp column. However, you still have constraints for doing this sort of task.

User-defined datatypes are still smiled-upon by Microsoft and while they deliver a great deal of value even without the use of Rules and Defaults, perhaps a reprieve for these two bad-boys wouldn't hurt, after 15 years on deprecation-row.

Phil Factor

Join the debate, and respond to the editorial on the forums

Stephanie Herr describes the new features in the Deployment Suite for Oracle that will help you automate your database releases safely, on Linux or Windows.

Floating point datatypes accommodate very big numbers but sacrifice precision. They are handy for some types of scientific calculations, but are dangerous when used more widely, because they can introduce big rounding errors.

Phil Factor delves into SQL Prompt's performance rule, PE012, which will advise you if it detects the use of the SET statements within a stored procedure or trigger, which might cause unnecessary recompilations, though the issue extends to other types of batches.

In this post, we will discuss the strengths and capabilities of each service and why you might choose one over the other for all or part of your machine learning workflow.

SQL Server Machine Learning allows you to run R and Python scripts from SQL Server. When SQL Server 2019 was released, Microsoft enhanced the security for this functionality, but it caused some existing code writing to the file system to to break. In this article, Dennes Torres explains the security enhancement and describes three ways to work with it.

Having used Azure CLI to query Azure DevOps to obtain a resultset containing useful information on builds, releases, and so on, it is likely that you will want to consume these results in PowerShell so that you can build upon them. I thought this would be an easy thing to do, but..no.

Join Kendra Little to discuss the key findings from Redgate’s 2020 State of Database DevOps Report. The survey received input from more than two thousand database professionals worldwide, providing insights from changing trends and shifting perceptions to deployment patterns.

I had the need to pull specific revisions of files out of a git repository, save those files, and then execute the contents. This all worked fine until it didn't. I received some complaints that unicode characters in the files we getting mangled, and sure enough they were. But why? In this post I'll explain what happened to me, and ways you can avoid it yourself.

Whether we’re talking nHibernate, Linq, or Entity Framework, the degree of loathing for these tools by most DBAs is really hard to measure. Yet, I’ve come to believe that code generation tools are a very important part of what we do. Further, that they are not evil, or wrong, or bad.

One of the worst things we can do as data analysts is to interpret a regression line as the most important thing on a visual. The important thing here is the per-state set of data points, but our eyes are drawn to the line. The line mentally replaces the data, but in doing so, we lose the noise. And boy, is there a lot of noise.

A thorough examination of the bar chart with tips on how to design nice ones.

It's not only for DevOps-related best practices.

With over 2000 participants from every industry sector across the globe, the State of Database DevOps report will help shape the business case of any team looking to implement or improve their approach to database DevOps, no matter the platform they choose to work with. Including a foreword from Kellyn Pot’Vin-Gorman, Customer Success Engineer at Microsoft, discover the landscape of database DevOps for 2020.

In SQL Server 2019 there have been some big licensing changes about what you can and can’t do on a passive fail-over instance. Especially if you have Software Assurance.

The various ways to do it, using either AlwaysOn Monitoring dashboard in SQL Server Management Studio or Performance Monitor counters.

In this article in the series, Robert Sheldon provides a deep dive into HDDs.

Max degree of parallelism (MAXDOP) is a setting in SQL Server that controls how many processors may be used for parallel plan execution. Parallel plan execution is good—it lets SQL Server make the best use of all those processors in modern servers. However, MAXDOP can be configured incorrectly. Too much or too little parallelism can negatively impact server performance.

Jeff Iannucci shares a little something for those of you who don’t want Query Store drowning in all your ad hoc nonsense.

In some cases locks are serializable, and if you don’t maintain your indexes you may find run-of-the-mill modifications taking quite a long time.

Erik Darling presents a series of videos tackling each of the signs that your execution plan isn't revealing the whole truth.

This article describes the types of relationships available in Power BI and Analysis Services, clarifying the differences in cardinality and filter propagation of physical relationships.

This post demonstrates how the order of steps added to a query can make a big performance difference and drastically effect the number of steps generated by the designer. I’ll demonstrate how to use the new query Diagnostics tools to compare and understand query performance.

Do you have a Power BI report that has a row of card visuals on it? It’s a very common thing to do, but if your report is too slow to render you may find that a design like this is part of the problem.

As someone who enjoys creating unique content in Power BI that’s always “one step away from useful” and not what Power BI was intended for, I now feel like I’ve missed out on a few years’ worth of opportunities. I should have started learning Power Apps long ago.

Management has requested an easy way to view a subset of strategic reports located in different Power BI workspaces. You can ask the users to mark reports and dashboards as favorites so they can access pertinent content in the Favorites menu, but you’re looking for an easier configuration, such as to create a book of reports with a built-in navigation that organizes reports in groups.

The Power Apps visual is a powerful tool for adding writeback and other functionality from Power Apps into Power BI reports. It allows users to take action directly from Power BI reports and update the data in real time.

Tomaz Kastrun reports on a worrying trends in the IT community, including a decline in awareness of how much community is worth, and a decline in respect for community contributions.

Wayne Sheffield explains a couple of potential issues when using Google Chrome with SSRS reports: constant log in prompts to the SSRS server, and the infamous Kerberos Double-Hop.

You are asked to provide information about logins and database users, but the login name does not match the name of the database user account.

Extended Events can do things that simply are not possible with Profiler and another example comes from the stack of audit events that exist only in Extended Events. One...

YAGNI, KISS, DDD and unit testing. Wait, that's four!

When and why to choose monolithic architecture is usually a matter of what works best for each business. Whatever the reason for using monolithic services, you still have to support them. They do, however, bring their own reliability and scaling challenges, and that’s what we’ll tackle in this post.

Kenneth Fisher discovers a weird option for the UPDATE command in a stack overflow answer

A procedure populates a temp table with the key values, which are then used to delete from the related tables, then delete from the primary table. However, the query populating our temp table was taking far too long, 84 seconds.

Jess Pomfret rounds up all the suggested SSMS hacks, time management skills, useful tools and more.

SQL Server 2016 gave us the STRING_SPLIT() function, but what about the reverse – compiling a set of values into one delimited string? We only had to wait 15 months for the release of SQL Server 2017, and the STRING_AGG() function.

If you’re the kind of person who likes to read books, Brent suggests a few that he thinks would expand your SQL horizons.

If you frequently need to pull all of the memberships that were active on a specific date, that’s where performance tuning gets hard. You don’t know the UserId, and even worse, you can’t predict the date/time you’re looking up, or if it’s always Right Now.

Occasionally you might need to know the physical location of a row stored in SQL Server. The code in this post uses the undocumented feature, %%PHYSLOC%%, which returns a binary representation in hexadecimal of the location of each row returned in a SELECT statement.

Sorting data requires memory, and Sort operators particularly may ask for quite a bit of memory. Why? Because all the columns you’re selecting, need to to be sorted by the column in the ORDER BY.

In SQL Server 2017, there is still a value for password in sys.syslogins, but in SQL Server 2019 it is now null. Taryn Pratt explains how this 'gotcha' caused problems during a SQL Server 2019 upgrade.

The sys.dm_exec_describe_first_result_set_for_object() DMF takes as its parameter the object_id of a procedure or trigger and describes the first result metadata for the module with that ID. Phil Factor explains why a developer might find this handy.

sp_describe_undeclared_parameters() promises a great deal, but delivers less. It was added mostly for executing application queries via sp_prepare using a TDS link, but has several quirks and restrictions that rather spoils its more general use.

Phil Factor explains why an ORDER BY clause should always specify the sort columns using their names, or aliases, rather than using an integer to specify the position of a column in the SELECT list.

Azure Kubernetes Services makes Deploying a SQL Server Big Data Cluster is a very simple process but you need some understanding of the the various Kubernetes Pods and Docker Containers created as a result.