A Fine Slice Of SQL Server
This post is for you in case you decide to use Uniqueidentifier column in your table and then you think about including it into a Persisted calculated column.
You also might see it useful if you like weird or funny SQL Server behavior.
Slava has an Azure feedback item and it looks like someone tested the behavior in SQL Server 2019 and it works as you’d expect, so this must have been fixed sometime between then and now.
Comments closedBob Pusateri has a two-parter on Powershell notebooks. First up is the problem:
PowerShell Notebooks are a great new feature in Azure Data Studio, first becoming available in the November 2019 release. Like SQL notebooks, PowerShell notebooks are based on Jupyter Notebooks format, which are interactive documents containing text and executable code blocks.
Having some working PowerShell code that I wanted to share along with explanations and examples, I created a PowerShell Notebook. The only problem was my functions would never initialize. Actually they would never stop initializing – I would run the cell they were defined in, and it would just keep running forever.
And then Bob has the solution:
It turns out I did not have the latest version of the PowerShell Kernel running on my machine. The latest version is currently 0.1.3, and I had 0.1.2. Upgrading appears to have solved this issue for me – yay!
This solution also raises the issue that there is no notification from Azure Data Studio that a PowerShell Kernel exists or is in need of updating. I (and probably others) will just believe that as long as Azure Data Studio is up to date, we’re good to go. So how does one update their PowerShell kernel? Well, it’s simple, but not intuitive.
Read on to see how.
Comments closedGreg Low has a workaround for an annoying problem:
Once again, I’m seeing lots of customers reporting screen repainting issues in SQL Server Management Studio (SSMS). It mostly seems to affect version 18 but I’ve also seen it in version 17. And it’s most prevalent on Windows 10.
The typical issue is that you click on another open tab, and the contents of the tab doesn’t repaint. You are still seeing the previous tab. If you click into the tab, you start to see bits from both tabs.
Click through to see the fix. I’ve seen this issue pop up though I don’t remember seeing it on the latest version of SSMS 18…though now that I say that, I’m guaranteed to have the problem hit me today.
Comments closedTaiob Ali points out a bug in SQL Server Management Studio:
SQL Server Management Studio (SSMS) showplan root node properties have two attributes to indicate when a filtered index can not be used during a parameterized query. One is ‘UnmatchedIndexes’, and the other one is under ‘Warnings’ attribute. Due to a bug in SSMS, the attributes are also showing positive results with filtered index and non-parameterized queries.
Let’s look at an example. I am using the AdventureWorks database, which you download from here.
If you want this fixed, vote up this Feedback item.
Comments closedMaxim Stefanov ran a static analysis of several Hadoop libraries and here are the findings:
After the analysis was completed, I chose the most interesting warnings and noticed that I had the same number of warnings in production code and in tests. Normally, I don’t consider analyzer warnings from tests. But when I divided them, I couldn’t leave ‘tests’ warnings unattended. “Why not take a look at them,” I thought, “because bugs in tests might also have adverse consequences.” They can lead to incorrect or partial testing, or even to mishmash.
After I selected the most intriguing warnings, I divided them by the following groups: production, test and the four main Hadoop modules. And now I’m glad to offer the review of analyzer warnings.
Read on for the list. Hopefully Maxim submitted a few pull requests or at least Jira tickets for the projects.
Comments closedSlava Murygin shows how you can recover from a nasty TRY_PARSE error:
In my previous post I outlined very dangerous SQL Server problem, caused by usually not very harmful commands “PARSE” and “TRY_PARSE“: Having “NaN” value for REAL and FLOAT producing severe error.
This post will be about a major issue it causes and on how to fight it.
Will start from generating the problem.
Attention: Do not run that in production!!!
Slava’s serious about not running the code in a real environment.
Comments closedBert Wagner points out that some numeric types handle overflow in a weird way:
Why does SQL Server sometimes error when converting a number into a string, but other times succeeds and returns an asterisk?
I don’t know.
The best (and logical) answer I could find online is from Robert Sheldon, who attributes it to poor error handling practices, “…before error handling got a more reputable foothold.”
This makes it important to check your results. I imagine that there’s somebody who relies upon this exact functionality, but it’s pretty weird.
Comments closedSlava Murygin finds a nasty bug in SQL Server:
Database in trouble has a table with FLOAT column. It’s Front-End application verifies user’s input and inserts the data into that column using TRY_PARSE function.
The developer’s intention was that any “Not-a-Numeric” or “Out-of-Range” values will be automatically converted to NULL and it will be for user’s discretion to verify and fix these values.However, one of the application users was very educated and instead of empty space, NULL or any other bad not numeric value the user supplied data with value of “NaN” for empty cells, which simply stands for “Not a Numeric”.
That action caused a database corruption!
Click through for a demo which you should not repeat on a work server.
Comments closedNiels Berglund hits an annoying error after installing PolyBase on SQL Server 2019:
At MS Ignite in Orlando November 4 – 8, 2019, Microsoft announced the general availability of SQL Server 2019. At the same time, the SQL Server 2019 Developers Edition appeared as an MSDN download, and of course, I downloaded it and installed it on my dev box.
After the installation, I noticed that PolyBase did not start up correctly, and I saw dump files all over the place. After some investigation, I figured out what the issue was, and this blog post describes the fix.
This only affects Developer and Express editions, not Standard or Enterprise.
Comments closedMatthias Nistler walks through troubleshooting rBokeh missing argument errors:
This approach is my go-to solution to change a rBokeh plot for which there is an argument missing in rBokeh that is available in python.
– Create the plot.
– Inspect the structure (str(plot)) of the rBokeh object.
– Search for the python’s argument name.
– Overwrite the value with the desired option as derived from python’s bokeh.
Given how nice the bokeh package looks, I really want rBokeh to work well. Hopefully this experience improves over time.
Comments closed