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

This post shows how to delete SSAS Tabular partitions with C#. This is an improvement over a PowerShell script I previously wrote about, Delete SSAS Partitions with TMSL and PowerShell, because it is more dynamic and doesn’t require a manually-authored TMSL script.
Specifically, the code below deletes all partitions that contain a specified suffix, which is useful for quickly deleting all partitions used for development purposes. I’ve worked with SSAS models that required several development partitions in order to prevent the workspace database from growing too large. Deleting them manually after a deployment to a server would be tedious, and it would be easy to forget one and end up processing duplicate data.

Click through for the code.

Comments closed

mssql-cli Update

Published 2018-05-15 by Kevin Feasel

Alan Yu announces an update to mssql-cli:

GDPR compliance
As many of us are familiar with, GDPR is approaching and we made some updates. In the past, file history stored entire T-SQL queries. However, if the query contained any secrets or passwords, it wasn’t smart enough to scrub those out. This is no longer the case, and now file history has been updated to no longer store secrets or passwords.
In addition, we have added 24-hour rotation of UserID when we collect telemetry.

Read on for other improvements.

Comments closed

Useful Azure SQL Database T-SQL Statements

Published 2018-05-15 by Kevin Feasel

Arun Sirpal has a few T-SQL statements to help manage an Azure SQL Database database:

Creating a database

Very easy to create something like the below – a S2 database.

CREATE DATABASE [MeeTwoDB]
( MAXSIZE = 5GB, EDITION = 'standard', SERVICE_OBJECTIVE = 'S2' ) ;
--checking details
SELECT Edition = DATABASEPROPERTYEX('MeeTwoDB', 'Edition'), ServiceObjective = DATABASEPROPERTYEX('MeeTwoDB', 'ServiceObjective')

Read on for several more examples.

Comments closed

Troubleshooting SQL Server Error 18456 State 73

Published 2018-05-15 by Kevin Feasel

Thomas Rushton reproduces an error state in SQL Server:

A question asked on one of the forums today wasn’t easily answerable by Googling. Summary of the question “I have error 18456 State 73 – why?”
Google seemed remarkably quiet on the subject of that particular state code. Even Aaron Bertrand’s list of causes of state codes for SQL Server error 18456 missed this one.
However, some searching did find a link to what appears to be some in-depth VMWare VSAN training documentation that includes that error in some logging information, which made me wonder if it was related to the error 18456 state 38 that followed.

Read on for more details.

Comments closed

Power BI: Parameterized Loading With Web.Contents()

Published 2018-05-15 by Kevin Feasel

Stacia Varga shows off Power BI automated refresh when hitting dynamically generated endpoint URLs:

There’s just one problem. I’m using the Web.Contents() function in several queries to get the JSON data from the NHL API.
Actually, the function is not the problem. It’s the way I’m using it to dynamically construct URLs so that I can iterate through lists of players or teams or whatever to retrieve the data I want.
Clearly that technique works just fine because I have data. However, when I try to schedule the refresh in the Power BI service, I get the dreaded error: “You can’t schedule refresh for this dataset because one or more sources currently don’t support refresh.”
I explained how to solve this particular problem once upon a time. For the hockey data queries, I had to use variations on that theme to get the dynamic URLs to work. To do this, I had to fix up my queries (which are actually functions) in the Query Editor by opening up the Advanced Editor and fixing the code as noted below.

Read on for her examples. Given some of the problems she ran into, it seems like it might be a good idea to pull that data into SQL Server (or somewhere) and thereby separate data retrieval from data processing.

Comments closed

Views Don’t Improve Performance

Published 2018-05-15 by Kevin Feasel

Grant Fritchey lays down the law on views:

One day, it’s going to happen. I’m going to hear some crazy theory about how SQL Server works and I’m going to literally explode. Instead of some long silly rant with oddball literary & pop culture references you’ll get a screed the size of Ulysses (and about as much fun to read). However, for the moment, like Robin Williams describing a dance move, I’m going to keep it all inside. Here’s our query:
1
2
3
4
5
6
7
8
9
SELECT soh.SalesOrderNumber,
       sod.OrderQty,
       sod.UnitPrice,
       p.Name
FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
    JOIN Production.Product AS p
        ON p.ProductID = sod.ProductID;
No, no where clause because we have to compare this to this, our view:

1
2
3
4
5
6
7
8
9
10
11

CREATE OR ALTER VIEW dbo.SalesInfo
AS
SELECT soh.SalesOrderNumber,
       sod.OrderQty,
       sod.UnitPrice,
       p.Name
FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
    JOIN Production.Product AS p
        ON p.ProductID = sod.ProductID;

Grant used up much of his strategic reserve of GIFs in that post, so check it out.

Comments closed

Power BI Custom Visuals In Excel

Published 2018-05-14 by Kevin Feasel

David Smith notes that Excel is getting a bit of an upgrade:

This week at the BUILD conference, Microsoft announced that Power BI custom visuals will soon be available as charts with Excel. You’ll be able to choose a range of data within an Excel workbook, and pass those data to one of the built-in Power BI custom visuals, or one you’ve created yourself using the API.

David’s point is that you can bring in R charts, but it extends to more than that.

Comments closed

Using The Spark Connector To Speed Up Data Loads

Published 2018-05-14 by Kevin Feasel

Denzil Riberio explains how you can use the Spark connector for Azure SQL DB and SQL Server to speed up inserting data from Spark into SQL Server 15x over the native JDBC client:

Since the load was taking longer than expected, we examined the sys.dm_exec_requests DMV while load was running, and saw that there was a fair amount of latch contention on various pages, which wouldn’t not be expected if data was being loaded via a bulk API.
Examining the statements being executed, we saw that the JDBC driver uses sp_prepare followed by sp_execute for each inserted row; therefore, the operation is not a bulk insert. One can further example the Spark JDBC connector source code, it builds a batch consisting of singleton insert statements, and then executes the batch via the prep/exec model.

It’s the power of bulk insertion.

Comments closed

Reducing Backup Sizes

Published 2018-05-14 by Kevin Feasel

Ned Otter gives us some options for reducing the size of full backups:

SPARSE columns
IF a column contains mostly NULLs, then depending on the data type, you can achieve space savings by using the SPARSE property (documentation here). SPARSE columns can be used with filtered indexes to theoretically reduce storage space and increase query performance. But there are a boatload of gotchas, such as issues with query plan caching (filtered indexes), and the fact that if you use SPARSE columns, neither the table or indexes can have any form of compression (the documentation is clear about not supporting table compression, but does not mention index compression being an issue – but it is).
As the documentation clearly states, when converting a column from non-sparse to sparse, the following steps are taken:
Adds a new column to the table in the new storage size and format
For each row in the table, updates and copies the value stored in the old column to the new column
Removes the old column from the table schema
Rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column
For large tables with even a few columns that you wanted to convert to SPARSE, this process would take forever, because you must do this for each column you want to convert.

I don’t like sparse columns at all, but I do like the rest of Ned’s options.

Comments closed

Updated DIML Catalog Browser

Published 2018-05-14 by Kevin Feasel

Andy Leonard announces a new version of his Catalog Browser utility:

Catalog Browser first displays the reference mapping in the context of the environment named DEV_Person. DEV_Person is a Catalog Environment that contains a collection of Catalog Environment Variables.
Catalog Browser next displays the reference mapping in the context of the SSS Connection Manager named AdventureWorks2014.OLEDB that consumes the Reference between the DEV_Person environment and the Load_Person.dtsx SSIS package. Note that this Reference Mapping is displayed as <Property Name> –> <Environment Variable Name>, or “ConnectionString –> SourceConnectionString”. Why? Catalog Browser is displaying the Reference Mapping from the perspective of the Connection Manager property.
The third instance of Values Everywhere is shown in the Package Connection References node. Remember, a reference “connects” a package or project to an SSIS Environment Variable (learn more at SSIS Catalog Environments– Step 20 of the Stairway to Integration Services). From the perspective of the reference, the reference mapping is displayed as <Environment Variable Name> –> <Property Name>, or “SourceConnectionString –> ConnectionString”. Why? Catalog Browser is displaying the Reference Mapping from the perspective of the Reference.

Check it out.

Comments closed

M	T	W	T	F	S	S
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

Curated SQL Posts

GDPR compliance