TSQL

Compare SQL Server Instance Properties

A few years ago, I created a couple of T-SQL scripts that can be used for comparing instance-level and database-level properties between two HA/DR replicas. Originally, this supported comparing only two servers. But recently, following a fan request, I upgraded the script to support an unlimited number of servers that you can compare to each other.

So, I figured, if one person found this useful, there must be more out there that would need this, right?

The Best Solution for SQL Server Partitioning Sliding Window

2022-05-302022-12-11
T-SQL, Tools and Scripts

Turns out there’s a shortage of solutions online for easily managing partitioned tables in SQL Server. Well… I guess I’ll have to do it myself!

Detect Weak Passwords in SQL Server

2022-02-012022-12-25
T-SQL, Tools and Scripts
2 Comments

This is a T-SQL script that I’m cross-publishing with the official Madeira Data Solutions blog.

This script generates various permutations and variations of common and weak passwords and uses the PWDCOMPARE function to check whether there are any enabled SQL logins that have these passwords.

The Simplest Alternative to sp_MSforeachdb

2021-08-05
T-SQL
1 Comment

Which drawbacks are there to using the built-in sp_MSforeachdb stored procedure? What other alternatives are out there? Is there a simpler solution? Find out all this and more in this short post.

Finding a use for Extended Properties in SQL Server

2021-07-27
SQL Server Database Development, T-SQL, Tools and Scripts
2 Comments

“Extended properties allow you to add custom properties to database objects”, so says the official Microsoft documentation. However, very few DBAs make use of them, if at all. This is actually a good thing, because indeed the scenarios in which this feature could be useful are relatively few and rare.

On the other hand, I also see DBAs essentially jumping through hoops to implement something, which could in fact very easily be implemented using Extended Properties.

What are these peculiar use cases? Let’s look at a few examples.

The Asynchronous Ledger Trick for Fast SQL Server Insert, Update and Delete Processes

2021-07-142022-12-31
SQL Server Database Development, SQL Server Database Engine, SQL Server Performance
4 Comments

Do you find yourself facing performance problems and long lock chains caused by very frequent INSERT, UPDATE, or DELETE statements being executed on a table? Check out this neat trick that could help you out and make all the difference in the world.

Even more fun with DATETIME arithmetics!

2021-06-172023-07-09
SQL Server Database Development, T-SQL, Tools and Scripts
1 Comment

In one of my previous posts, Fun with DATETIME Arithmetics, I introduced a way to use “math” to manipulate datetime values for effectively generating, calculating, and displaying intervals (i.e. difference between two datetime values). These mostly work with the addition and subtraction operators (+, -).

In one of the paragraphs, I mentioned multiplication and division, and posed the question about why anyone would ever need to do this.

Fun with DATETIME Arithmetics

Did you know that we can have basic datetime arithmetics in SQL server? We can use subtraction to find an accurate difference between two dates, and use addition to add an accurate interval to a datetime column or variable.

Read this post to learn more.

T-SQL Tuesday #136 – Most and Least Favorite Data Type

2021-03-09
SQL Server Database Development, SQL Server Database Engine, T-SQL
2 Comments

This month’s T-SQL Tuesday is hosted by Brent Ozar, who asks us to Blog about our Favorite Data Type (or Least Favorite).

I find it actually quite easy to decide both on a most-favorite, as well as a least-favorite data type in SQL Server. So, why not blog about both?

T-SQL Tuesday #131 – Star Trek Candy and SWITCH TO

2020-10-13
SQL Server Database Development, SQL Server Database Engine, T-SQL
1 Comment

This month’s #tsql2sday came to us from Rob Volk (b|t), who asks us to explain databases using an analogy, as if explaining to a 5 year old. I’m actually a big fan of The Feynman Technique (aka ELI5), so I really wanted to participate. But to be honest, I nearly missed out this time simply because I couldn’t think of an idea this whole week.

On the very last day, when the posts already started rolling out by all the bloggers, I’ve read a few, and only then the muse finally hit me. I kid you not, the time is literally 23:59 here in Israel as I hit the publish button!

So anyways, the idea I had was for the ALTER TABLE..SWITCH TO command in SQL Server.