Press "Enter" to skip to content

Day: March 4, 2024

Setting Data Frame Columns as Indexes in R

Published 2024-03-04 by Kevin Feasel

Steven Sanderson explains and does:

Before we dive into the how, let’s briefly discuss why you might want to set a column as the index in your data frame. By doing so, you essentially designate that column as the unique identifier for each row in your data. This can be particularly useful when dealing with time-series data, categorical variables, or any other column that serves as a natural identifier.

Setting a column as the index offers several advantages:

Read on to see those advantages.

Comments closed

Reducing Power BI Dataset Sizes with Semantic Link

Published 2024-03-04 by Kevin Feasel

Sandeep Pawar builds some really cool diagnostics:

Semantic Link v0.6 is out and it has many new exciting additions to its growing list of list_* methods. Highlighted are some of the new methods. Install the latest version and check it out.

Some of the existing methods such as list_columns() have an additional parameter extended which returns more column information such as column cardinality, size, encoding and many more column properties. This allows users to get detailed information about the dataset and the columns.

Click through to see how you can get this information not just for a single semantic model, but for all semantic models in a tenant.

Comments closed

Extended Events Tracing on Read Scale-Out Azure SQL MI

Published 2024-03-04 by Kevin Feasel

Kendra Little goes on a journey:

It took me more than half hour to figure out how to start an XEvents trace on a read-scale out instance of Azure SQL Managed Instance. It’s hard to monitor read scale-out instances, so tracing is desirable! I started with a simple trace of sql_statement_completed. Hopefully this saves other folks some time.

Click through for that process. The process seems a bit painful, to put it kindly.

Comments closed

Strict Encryption in SSMS 20

Published 2024-03-04 by Kevin Feasel

Erin Stellato shares an update:

SSMS 20 is the first major version of SSMS that supports Strict encryption and TLS 1.3, thanks to the migration to Microsoft.Data.SqlClient (MDS) 5.1.4.  MDS is the data access library used by SSMS 19 and higher, as well as other SQL Server tools.

Read on for a quick primer on terminology, as well as what it means to force strict encryption. I’m not sure how quickly companies will jump on this, especially given the features that don’t support strict encryption yet, such as availability groups, replication, SQL Server Agent, database mail, linked servers, and PolyBase’s connector to SQL Server.

Comments closed

Symbolic Links and Powershell Modules

Published 2024-03-04 by Kevin Feasel

Jeff Hicks makes a connection:

I have a short tip today that you may find useful, especially if you write modules for your private use. I have a number of such modules that I have written to fill my needs. These are private modules that I don’t publish to the PowerShell Gallery. I develop and maintain these modules in C:\Scripts. This means that when I need to import the module, I have to type the full path.

Read on to see how you can use symbolic links to make this a bit smoother.

Comments closed

Reducing the Cost of Delete Operations in SQL Server

Published 2024-03-04 by Kevin Feasel

Ben Johnston eats the elephant:

One of the first things you learn when working with SQL Server, and other transactional based SQL systems, is that set based operations perform best. If you are querying data, a cursor pulling individual rows doesn’t perform as well as a single query. It doesn’t matter if that cursor is on the client side or the server side. A set-based operation is more efficient, runs faster, locks less, and is generally better than submitting multiple queries.

This is also generally true with delete statements. This post covers the exceptions to that rule. Large delete statements impacting many rows and large amounts of data (millions of rows and many gigs of data) can actually have decreased performance. With transactional systems, such as SQL Server, each transaction follows the ACID standard. Part of that standard ensures that transactional statements either complete or roll back fully – partial transactions are not allowed. For a delete statement, that means that all of the rows specified by the delete are removed from the table, or none are removed and the data rolls back to the original state. The delete and rollback behavior must be predictable and consistent or the data could be left in a contaminated, unreliable state. Performing very large deletes can present some challenges and needs to be treated differently in production systems.

Read on for the reasoning behind this, as well as several techniques you can use and how they compare.

Comments closed