A Fine Slice Of SQL Server
Ron the Polymath notes a change slipped into Windows:
The October 2020 Preview releases of Windows builds 1809 (KB4580390), 1903/1909 (KB4580386), and 2004/20H2 (KB4580364) include the following change:
– Adds support for the Transport Layer Security (TLS) 1.1 and 1.2 protocols when connecting to SQL Server using the data providers in Microsoft Data Access Components (MDAC).
Click through for Ron’s experience and another update of potential interest.
Comments closedWilliam Assaf shows how you can use sp_help to learn about temp tables:
For example, I was trying to convert a query from using a #temp table to a CTE instead, and wanted to see the column list and resulting data types of the #temp table.
Sp_help is a helpful SQL Server system sproc to return schema of objects. It’s that magic that happens when you press Alt+F1 in SSMS. (Side note: showing someone the Alt+F1 shortcut in SSMS for the first time and seeing their life change for the better is really rewarding.)
But Alt+F1 doesn’t work on #temp tables[…]
Read on for an alternative which does work.
Comments closedGilbert Quevauvilliers knows that time is a flat circle:
I have seen in the past Week-on-Week comparisons but one of the challenges is what happens when it overlaps years. Especially at the start of a year like it is now Feb 2021, the users want to compare week-on-week for the past 3 months.
My challenge was to find a way to have a continuous week number over multiple years.
Click through to learn how.
Comments closedThomas Rushton has a warning for us:
Let’s test that script:
Create Table Trigger FiredCompletion time: 2021-02-27T14:17:09.4783931+00:00Great. Refresh the tables list in the object explorer to go poking around, and:
Hmm. We have a problem.
Click through to understand what happened.
Comments closedDavid Eldersveld gives us ways to go beyond simplistic tables in Power BI:
Conditional formatting for a table or matrix is an easy way to keep the grid and display raw numbers while providing visual cues that our brains process faster than reading numbers. Background color, font color, icons, and data bars help jazz up bland tables. It’s an easy compromise that brings some data visualization to table detail.
Click through for more examples of this.
Comments closedBrent Powell has a new series for us:
Between the Power BI PowerShell modules and the Power BI REST APIs administrators have a rich set of tools to efficiently administer Power BI environments. Custom administrative and monitoring solutions based on these technologies have been featured on this blog before but today we will start the first of a two-part series highlighting simple script examples that Power BI administrators can use to address common scenarios.
The six PowerShell script files (.ps1) for today’s examples are available in my GitHub repo.
Check out those scripts, as well as Brent’s walkthrough of each.
Comments closedErik Darling shows why you might not want to set ANSI_DEFAULTS ON:
Rather than just setting the required options, which was apparently a lot of typing, someone had just set all the ANSI defaults on.
Read on to learn what the problem is and an alternative to using ANSI_DEFAULTS.
Nisarg Upadhyay wants to move files around in SQL Server on Linux:
In this article, I am going to explain how we can move the SQL database files to another location in Ubuntu 18.04. For the demonstration, I have installed Ubuntu 18.04, SQL Server 2019 on Linux on my workstation. You can read SQL Server 2019 on Linux with Ubuntu to understand the step-by-step installation process of the SQL Server 2019 on Linux. We will move database files of AdventureWorks2019 and Wideworldimportors database.
Click through for the process. It’s really similar to Windows in this respect. And, well, in most respects.
Comments closedDeepthi Goguri takes us through a fairly new feature in SQL Server:
With SQL Server 2017 and below versions, SQL Server always estimates one row for table variable as the table variable data gets inserted during the run time, so optimizer doesn’t know how many values it can expect coming out of the table variable. Due to this bad estimation, performance of the queries is effected.
Click through to see how this has changed in SQL Server 2019.
Comments closedMatthew McGiffen does the math:
I mentioned in my previous post about manually updating statistics that you can specify whether they’re updated using a full scan, or you can specify an amount of data to sample, either a percentage of the table size, or a fixed number of rows. You can also choose not to specify this, and SQL Server will decide for you whether to do a full scan, or to sample a certain amount of data.
I thought it would be interesting to look at what the sample sizes are that SQL will choose to use, depending on the amount of data in your table.
Click through for the result of Matthew’s analysis.
Comments closed