Curated SQL – Page 710 – A Fine Slice Of SQL Server

You’ve created a beautiful, wide-open Tabular model. You use USERELATIONSHIP() to switch relationships on and off. Everything works and everyone is pleased. Then RLS sneaks in, such as when external users need access, and you must secure on some dimension table. You create a role, specify a row filter, test the role, and get greeted with:
The UseRelationship() and CrossFilter() functions may not be used when querying ‘<dimension table>’ because it is constrained by row-level security defined on ‘<dimension table>’ or related tables.

Read on to learn what the issue is and one potential workaround.

Comments closed

The Geography Data Type

Published 2021-03-10 by Kevin Feasel

Greg Dodd talks about the GEOGRAPHY data type:

If you read through the docs on data types, you hit the “Other data types” section, and you start scratching your head: cursor, hierarchyid, rowversion. I’m sure XML will have a special place for some (love or hate).
The datatype that I think is the coolest has to be Spatial Geography. Under the covers, it’s probably the same as Geometry, but no where near as much fun. What is Geography? It’s what it sounds like – a way to store Latitude and Longitude data that will let you query it back again and plot it on a map, or measure distances from it.

Also read Rob Farley’s note on the topic, which mirrors my thoughts: spatial data types are quite relevant for comparison work. And a bit of data manipulation in the database can save a lot of network traffic.

Comments closed

In Praise of the XML Data Type

Published 2021-03-10 by Kevin Feasel

Eitan Blumin likes an underappreciated data type:

The xml data type in SQL Server exists since about SQL Server 2005, and it introduced a lot of very powerful and useful capabilities that were never before seen in SQL Server. It was the first-ever data type with built-in CLR methods (i.e. where you can write a dot after a column name and execute some kind of method. For example: mycolumn.nodes(…)).
The xml data type and the functionality around it made it relatively easy to “refactor” a resultset from a relational structure into a scalar structure (i.e. a single XML document) using the FOR XML directive, and vice versa (single XML document into a relational structure) using the nodes(), value(), and query() methods.

Show me a thousand posts and I don’t expect to see XML show up as a favorite type more than three or four times. Eitan also shares a least favorite type, and I do expect that one to show up on the most-hated list quite frequently.

Comments closed

Things to Like about Integers

Published 2021-03-10 by Kevin Feasel

Brent Ozar is a fan of integers:

I love integers.
Furthermore, I think we’ve all been taking them for granted.
Integers are clean, elegant datatypes that do exactly what they’re supposed to. They’re the Golden Retrievers of the database world: they have a few shortcomings, but so, so few. And so for this month’s T-SQL Tuesday, I wanna share my love for integers with you.

Click through for four things to like.

Comments closed

Executing Parameterized Notebooks via Azure Data Studio

Published 2021-03-09 by Kevin Feasel

Julie Koesmarno takes us through three methods for executing parameterized notebooks in Azure Data Studio:

In Feb 2021 release, Azure Data Studio (v1.26.1) has added parameterized URI execution. See the “Preview of passing parameters through URI” section and the Parameterization of Notebooks in Azure Data Studio on Microsoft Docs.
So, in total there are three ways of executing parameterized notebook (from another notebook). Check out the demo files here:

Click through for the notebooks.

Comments closed

Getting Started with data.table

Published 2021-03-09 by Kevin Feasel

Gary Hutson has a primer on data.table:

This example uses the copy data frame we made and uses the organisation code by the type of attendances. I want to then summarise the mean admissions by type and organisation code.
Pivots can be implemented in data.table in the following way:

I’ve never been the biggest fan of the syntax for data.table but the performance is unquestionably there and that makes it worth learning. H/T R-bloggers.

Comments closed

Execution Plan Operator Timings

Published 2021-03-09 by Kevin Feasel

Paul White dives into a murky problem:

SQL Server 2014 SP2 and later produce runtime (“actual”) execution plans that can include elapsed time and CPU usage for each execution plan operator (see KB3170113 and this blog post by Pedro Lopes).
Interpreting these numbers is not always as straightforward as one might expect. There are important differences between row mode and batch mode execution, as well as tricky issues with row mode parallelism. SQL Server makes some timing adjustments in parallel plans to promote consistency, but they are not perfectly implemented. This can make it difficult to draw sound performance-tuning conclusions.
This article aims to help you understand where the timings come from in each case, and how they can be best interpreted in context.

My default advice is to read what Paul White writes, and this is no exception.

Comments closed

Getting the Stack Overflow Database for Demos

Published 2021-03-09 by Kevin Feasel

Brent Ozar has an update on getting the latest version of the Stack Overflow data set:

If you only have a limited amount of bandwidth, you don’t have to keep seeding the database after you get it – I’ve got it hosted on a handful of seedboxes around the world.
As with the original data dump, these are provided under cc-by-sa 4.0 license. That means you are free to share it and adapt it for any purpose, even commercially, but you must attribute it to the original authors (not me):

Click through for those attribution details as well as links to get the demo database in various sizes.

Comments closed

The Unique Properties of DateTimeOffset

Published 2021-03-09 by Kevin Feasel

Rob Farley analyzes a special data type:

And as I have a unique index on this, it won’t let me insert 00:30 in UTC+11, because 00:00 in UTC+10:30 is already there. It tells me “Msg 2627, Level 14, State 1, Line 19. Violation of PRIMARY KEY constraint ‘pkTimesOffset’. Cannot insert duplicate key in object ‘dbo.TimesOffsets’. The duplicate key value is (2021-01-01 00:30:00.0000000 +11:00).”

My general rule is to store everything in SQL Server as UTC. If I did not do this, I would very strongly advocate for using DateTimeOffsets regardless of the extra data length. I’ve experienced the pain of mismatched date and time details one too many times for that.

Fun bonus fact: the same applies to .NET as well. If I control the system, I’m using DateTime.UtcNow for everything. If not, I’m leaning heavily toward DateTimeOffset by default. Again, too many times have I experienced that source system X has times marked in Pacific Standard Time pushing data to a server in Eastern Standard Time, and then mixing in a server based in Central Standard Time and having people confused because “the times are wrong.”

Comments closed

A Review of Numerical Data Types

Published 2021-03-09 by Kevin Feasel

Lina Kovacheva reviews SQL Server’s numerical data types:

SQL Server provides two dedicated data types for storing monetary values. You can think of MONEY and SMALLMONEY as DECIMAL – Money is effectively the same as DECIMAL(19,4) while SMALLMONEY is effectively the same as DECIMAL(10,4). If you are planning to use MONEY you should have in mind that performing division and multiplication can cause rounding errors that result in the unintentional loss of precision. The cause of the problem is that MONEY only saves information up to the 4th decimal place and if your multiplication or division results in an integer that goes to the 5th decimal place or more, MONEY will round it off, causing an accuracy error.

Click through for an analysis of these data types.

Comments closed

Curated SQL Posts