A Fine Slice Of SQL Server
Sometimes to make our lives easier, we, as database engineers, can create a table that automatically tells us the answer as we need it – or at least how we tell it we want it. In SQL Server, we create what is called “Computed Columns.”
Read on to see how to create one of these in Snowflake.
Comments closedDeepthi Goguri gives us an overview of Azure SQL Database’s built-in auditing functionality:
As you all know how crucial it is to Audit activity on the Server for both prod and non-prod environments, turning on the auditing in Azure SQL is pretty simple and the results we see in the audit log are similar to the logs we see on-prem. The difference is where we save the audit data in Azure.
You can enable the auditing at the Server level and at the database level just like the way we can audit SQL Server on-prem. If you would like to enable audit at the Server level in Azure, it will automatically audit all the databases under that Server. If we allow the auditing at the server level (logical Server for Azure SQL Databases) and also at the database level, we might get double the amount of collected audit data as it contains the same data twice. Always chose the Storage account if you wanted to audit the data at the Server level. If you just want to collect the audit data on one or some databases only, you can disable the logical Server level audit and enable the Auditing at the database level.
Read on for more information and to see a bit of it in action.
Comments closedJason Cockington share some advice:
From my experience, most people who have reports built on a DirectQuery connection into their data source did so because of a lack of understanding of what the DirectQuery connection was designed to achieve. For the vast majority of reports, Import mode is the best solution for working with data in Power BI. DirectQuery should really only ever be applied when you are trying to solve one of the following challenges.
- Real-time Data – you need to see the latest available data from the source
- Huge Datasets – you have many billions of rows of data (more than 10Gb) so you just can’t import it into Power BI
- Regulatory Compliance – the data must stay in the source for data security/privacy reasons
Click through for more information.
Comments closedKedhar Natekar continues a series on Snowflake performance:
Clustering does not guarantee improved performance on non-clustered columns.
If you have frequent queries on non-clustered columns and performance is the key irrespective of cost, then opt for a search optimization service over the entire table of specific columns.
It’s similar to enabling indexing on RDBMS databases like Oracle on specific columns.
Read on to see how these work and get a few tips along the way.
Comments closedThe Big Data in Real World team creates a Spark user-defined function in Scala:
In this post we are going to create a Spark UDF which converts temperature from Fahrenheit to Celsius.
Here is our data. We have day and temperature in Fahrenheit.
And, of course, it’s roughly the same in PySpark. Also, note that user-defined function performance will take a hit, and that answers are fairly consistent through the years, so save these for when you need them.
Comments closedIn reality, there are a few different ways to join the Microsoft Fabric (Preview) trial.
For example, you might be lucky enough to have it enabled in the workplace already. However, there are ways that you can create your own Microsoft Fabric environment as well.
Click through for the process, and note that the trial is 60 days, though Microsoft will let you renew the trial until the product goes GA.
Comments closedLeila Etaati smooths the curve:
The calculation will be done for all rows, till we don’t have the anymore data ahead ( 3 rows ahead) as you can see it will finished on row 2018 as 2018 is the average of 2018, 2019 and 2020.
so in the moving average we will miss some data point but replacing with average of data.
Less data point but help us to see a clear trend.
Click through to see how to do this. Moving averages is really good for the reports in which you want to get a rough magnitude but don’t care about exact values and don’t want oscillations to throw you off. For example, if I need to know how many seats to reserve for an event, I might use a moving average of the last several events as my baseline, rather than the prior event’s number.
Comments closedForrest McDaniel abuses that poor server:
The prod version of this issue manifested as 1-3 minutes of SCH-M blocking for index ops, yet only on a single server; I couldn’t replicate it anywhere else. Of course, bad blocking meant nasty timeouts, and that in turn meant I was reluctant to run live diagnostics. Better to just stop index rebuilds. One does not take down production in order to test weird SQL behavior.
An important clue came from a tiny table with a rebuild, and is visible in the whoisactive screenshot above. Why would such a small rebuild need so much CPU? That worker was definitely doing something – the question was what.
Read on to find out the answer, a repro script (that you should not run in your own production environment!), and what you can do about it.
Comments closedErin Stellato has a two-parter around SSMS 19.1. The first includes a small but important change:
In SSMS 19.1, the default value for Use system default web browser was changed to True. This only applies to new installations; anyone upgrading from an earlier version of SSMS 19 will need to change the option to True manually.
This means that, for new installations, clicking on a link in SSMS will no longer open in the built-in SSMS browser, which doesn’t work for a lot of newer sites.
Part 2 picks up with some additional changes:
We have heard complaints, for months, about the startup time for SSMS. Between 18.12.1 and 19.0 we made a few changes, and in 19.1 we delayed initializing the output window to also reduce startup time. Some of you noticed! Glenn Berry completed a round of testing and documented findings in his SSMS 19.1 Performance Improvements blog post. We are hoping to also gain improvement when we get to SSMS 20, so know that our work is not yet done.
Click through for the full set of chnages.
2 CommentsSteven Sanderson shows off a built-in function:
In R, the
file.info()function is a useful tool for retrieving file information, such as file attributes and metadata. It allows programmers to gather details about files, including their size, permissions, and timestamps. In this post, we will explore thefile.info()function and demonstrate how it can be used to list files by date.
Read on for more information. This function is a lot more powerful than simply running ls or dir (without any flags) in a directory.