A Fine Slice Of SQL Server
Patrick Smacchia looks at two new operators in C#:
C#8 added the index
^and range..operators. In this post I am attempting to demystify both in the most comprehensive way.
Read on for the demos. I’m not sure I like how the range operator is exclusive on the right-hand side, but I suppose it’s just a matter of remembering that in this language, it’s exclusive and in others it can be inclusive.
Comments closedErik Darling compares cardinality estimators:
Look, I’m not saying there’s only one thing that the “Default” cardinality estimator does better than the “Legacy” cardinality estimator. All I’m saying is that this is one thing that I think it does better.
What’s that one thing? Ascending keys. In particular, when queries search for values that haven’t quite made it to the histogram yet because a stats update hasn’t occurred since they landed in the mix.
Read the whole thing.
Comments closedKendra Little has some considerations for us:
One question which comes up periodically from our Microsoft Data Platform customers is whether there is anything special that they need to do when implementing version control, continuous integration, and automated deployments for a production database which is a member of a SQL Server Availability Group.
The good news is that deployments are quite straightforward. You’ve most likely already configured a listener for your applications to connect to the Availability Group and automatically find the writeable database. To do a deployment, you simply need to connect to the listener as well.
There are a few other considerations which are helpful to think about when building your approach to database DevOps, however.
Check out the article for the details.
Comments closedImke Feldmann shows how to refresh Power Query results on protected sheets in Excel:
When working with Power Query in Excel you might want to refresh Power Queries on protected sheets. But this will not work by default. Using a macro to temporarily unprotect the sheet and protect it again will do the trick. But this requires the password being displayed in the VBA code. So please have in mind that this technique only works for scenarios where you want to prevent accidental changes with the password protection.
Read on for the process.
Comments closedBrent Ozar takes us through one big problem around scalar user-defined queries:
When your query has a scalar user-defined function in it, SQL Server may not parallelize it and may hide the work that it’s doing in your execution plan.
To show it, I’ll run a simple query against the Users table in the Stack Overflow database.
Read on for the demo, as well as what you can do about it.
Comments closedTomaz Kastrun has a script for us:
So the following function does just that; if goes through the list of functions for all the installed packages in particular environment and finds duplicates. Giving the R programmer the insights where to be cautious and refer to scope and namespace.
This is a cautionary tale to use full object naming, like coin::show() rather than just show().
Kyle Buzzell looks at time series databases:
As the name implies, a time series database (TSDB) makes it possible to efficiently and continuously add, process, and track massive quantities of real-time data with lightning speed and precision. While other database models have been used for these kinds of workloads in the past, TSDBs utilize specific algorithms and architecture to deal with their unique needs.
In this piece, we’ll take a deeper look at time series databases, including the unique needs of the workloads they’re built for, their benefits, common use cases, and the TSDBs out there.
Click through for an overview. Time series databases are definitely a niche product, but they are really good inside that niche.
Comments closedHasan Savran wants information:
Indexes let your queries run faster. When you need to adjust your indexing policies, database engines re-indexes your data respecting to your changes. In Cosmos DB, when you change your indexing policies, database engine truncates all your indexes and starts to reindex all your indexes from scratch. You do not want to change your indexing policies when your application is busy. Because your queries can not use the dropped indexes, queries will take longer, and they will cost more Request Units. Also, your queries might not return all the data they supposed to. You can read me my older post about indexes in Cosmos DB.
You may want to monitor re-indexing progress; you may want to disable your application until indexing is completed or warn your team about the re-indexing progress. You can check the re-indexing progress only from SDK, that means you need to write your own code to accomplish this. I have the following code which checks the progress every second. If progress is at %100 then it quits, otherwise it continues to check progress every second until it receives 100 as result.
Hasan has provided us with a script, so check that out.
Comments closedKenneth Fisher has a script for us:
It’s been a while since I worked with compression and the other day I needed to check which of my indexes were compressed and which weren’t. Now, I knew the information wasn’t going to be in sys.tables and I couldn’t find it in sys.indexes or INDEXPROPERTY(). I’ll be honest it had me stumped for a little bit. Until I remembered something!
Read on for Kenneth’s remembered insight and a script to get the job done.
Comments closedAlways Encrypted protects our data both at rest and in transit. To accomplish that, SQL only stores the encrypted data and cannot decrypt it; all the work is done by the client. In our case the client is SSRS and it is the account running the SSRS service that will need the certificate to decrypt data. Note that it is not the account running the report.
Click through for the solution.
Comments closed