Press "Enter" to skip to content

Day: July 14, 2022

Saving DateOnly and TimeOnly to Databases

Published 2022-07-14 by Kevin Feasel

Hasan Savran gives us some mixed news:

DateOnly and TimeOnly are the new members of .NET family, they are introduced in version 6. Developers have been asking to declare only a date or only a time without using the DateTime object for a long time. These two new functions will make the developer’s life easy for sure. You can declare them easily but saving them to a database can be a challenge. Before we go into those details for saving them, Let’s look at how to declare and use them in the code.

But right now, they aren’t so useful for SQL Server. Which is a shame, as there are obvious mapping candidates : DATE and TIME, respectively. My hope is that the Microsoft.Data.SqlClient library gets an update pretty soon to handle them. But read on to learn how Cosmos DB handles these new types.

Comments closed

The ago() Function in KQL

Published 2022-07-14 by Kevin Feasel

Robert Cain continues a series on learning the Kusto Query Language:

The ago function is very similar to the now function, which was discussed in my previous post Fun With KQL – Now. In this article we’ll take a look at ago, see how it works and how it differs from now.

We’ll be using both the print operator and the now function in this post, so if you aren’t familiar with them please go read my articles Fun With KQL – Print and Fun With KQL – Now.

Click through for proper use of ago().

Comments closed

Giving Managers Access to Servers

Published 2022-07-14 by Kevin Feasel

Tracy Boggiano opens a can of worms:

So, what to rant about?  Should your managers have access to your SQL Server instances? It depends.  You say on what.  Well, just one thing.  What type of manager do you have?

Read on for Tracy’s thoughts on the matter. Speaking as a manager, I tend to agree. If you don’t know what you’re doing, better not to have the ability to mess things up. I can think of oddball scenarios where you might still want the manager to have (at least theoretical) access to a system, primarily as a backstop in case the line staff get locked out or someone gets hit by the lottery bus and you suddenly need to bring in a new employee. That’s more of “break glass in case of emergency” access, though.

Comments closed

Hardware and Finger-Pointing

Published 2022-07-14 by Kevin Feasel

Glenn Berry gives us two rants for the price of one:

This is rant #1. Even though I have an unusual fascination with computer hardware, I am still somewhat taken aback when I encounter DBAs who have absolutely no idea what type of hardware they are using. I’ll sometimes ask a DBA “What processor does your most important database server have?”, and I often get a “deer in headlights” look in response. Then a mumbled response, “I’m not sure, maybe a Xeon?”.

Read on for this rant, as well as the origin story of Glenn’s outstanding SQL Server Diagnostic Information Queries.

Comments closed

The Joys of Replication, Example 48,106

Published 2022-07-14 by Kevin Feasel

Garry Bargsley troubleshoots a transactional replication issue:

Use transactional replication to replicate similar data from four databases to one Azure DB.  This sounds pretty straightforward for anyone who has done any replication work.

However, once I had everything setup and working, things stopped working, and it was a head-scratcher as to why.  I made the proper settings configurations on each article, or so I thought.  Let me show you the scenario in more detail.

Read on for the answer.

Comments closed

Power Query’s Equivalent of IN

Published 2022-07-14 by Kevin Feasel

Gilbert Quevauvilliers is making a list:

In the example I did not want to specify all the country names one row at a time. This not only takes a long time, but if I had to then make updates it could be painful too.

The requirement was for certain countries to have their names and the rest be grouped into “Other Countries”

Read on to see how Gilbert was able to combine the set of “Other Countries” together.

Comments closed

Indexes: Gotta Add ‘Em All

Published 2022-07-14 by Kevin Feasel

Andy Yun tells a tale of woe:

I once worked for an Entity Framework SaaS company that was having horrific T-SQL performance issues. We put out a major release one weekend and spent the subsequent week buried in horrible performance hell. Because all T-SQL code was generated from Entity Framework (stored procedures were banned by a development exec prior to me joining the company), there were no easy fixes that could be implemented (and a Production release typically took 12 – 36 hours, I wish I was joking).

The manager of infrastructure had heard about SQL Server’s missing index recommendations:

Yep, it ends about as poorly as you’d expect.

Comments closed

Ordered Columnstore Indexes

Published 2022-07-14 by Kevin Feasel

Joe Obbish and Erik Darling tag team on this one. First, Joe looks at the details of what the CCI ordering process does:

The sort for inserting into an ordered columnstore is a DML request sort. It appears to use the same internal mechanism as the sort that’s added for inserting into partitioned columnstore tables. The difference is that the data is sorted by the specified columns instead of a calculated partition id. In my testing, the sort appears to be a best effort sort that does not spill to tempdb. This means that if SQL Server thinks there won’t be enough memory then the data will not be fully sorted. Parallel inserts have an additional complication. 

And Erik has a messy work-around:

Anyway, I decided to dig in and see what was going on behind the scenes. Which of course, means query plans, and bothering people who are really good at debuggers.

Most of the problems that you’ll run into in SQL Server will come from sorting data.

Whenever I have to think about Sorts, I head to this post about all the different Sorts you might see in a query plan.

Definitely read both posts.

Comments closed