Press "Enter" to skip to content

Category: T-SQL Tuesday

A Review of Numerical Data Types

Published 2021-03-09 by Kevin Feasel

Lina Kovacheva reviews SQL Server’s numerical data types:

SQL Server provides two dedicated data types for storing monetary values. You can think of MONEY and SMALLMONEY as DECIMAL – Money is effectively the same as DECIMAL(19,4) while SMALLMONEY is effectively the same as DECIMAL(10,4). If you are planning to use MONEY you should have in mind that performing division and multiplication can cause rounding errors that result in the unintentional loss of precision. The cause of the problem is that MONEY only saves information up to the 4th decimal place and if your multiplication or division results in an integer that goes to the 5th decimal place or more, MONEY will round it off, causing an accuracy error.

Click through for an analysis of these data types.

Comments closed

The Unique Properties of DateTimeOffset

Published 2021-03-09 by Kevin Feasel

Rob Farley analyzes a special data type:

And as I have a unique index on this, it won’t let me insert 00:30 in UTC+11, because 00:00 in UTC+10:30 is already there. It tells me “Msg 2627, Level 14, State 1, Line 19. Violation of PRIMARY KEY constraint ‘pkTimesOffset’. Cannot insert duplicate key in object ‘dbo.TimesOffsets’. The duplicate key value is (2021-01-01 00:30:00.0000000 +11:00).”

My general rule is to store everything in SQL Server as UTC. If I did not do this, I would very strongly advocate for using DateTimeOffsets regardless of the extra data length. I’ve experienced the pain of mismatched date and time details one too many times for that.

Fun bonus fact: the same applies to .NET as well. If I control the system, I’m using DateTime.UtcNow for everything. If not, I’m leaning heavily toward DateTimeOffset by default. Again, too many times have I experienced that source system X has times marked in Pacific Standard Time pushing data to a server in Eastern Standard Time, and then mixing in a server based in Central Standard Time and having people confused because “the times are wrong.”

Comments closed

SQL Server Data Types: Bit vs the World

Published 2021-03-09 by Kevin Feasel

Kevin Chant is a fan of the bit type:

I decided to tweak it a bit for this post, to provide a humorous comparison between the bit data type and others that are available in SQL Server. In reality, this won’t cover every single one.

By the end of this post, you will some see pitfalls to using certain data types and some tips on how to avoid them. Plus, you will find out which data type is my least favourite to use.

Click through for a “haha-just-serious” take on an underappreciated datatype which nonetheless can’t decide if it’s a boolean or not.

Comments closed

5 Useful Tools for DBAs

Published 2021-02-11 by Kevin Feasel

David Fowler recommends five helpful tools and products:

Backups are easily the most critical part of any DBA’s job so having a reliable way of managing them is essential. There are various tools and scripts out there but easily top of the tree for me is Minion Backup from Minionware.

Controlled by a number of configuration tables, it makes sceduling backups and configuring them down to a really granular level dead easy. If you’re like me and you’ve got a large number of databases on a server that each need to run on different days, go to different locations and need to run with different settings, Minion is about the only tool that I’ve found that lets me control things how I want without needing 101 different agent jobs. All your backups are controlled from a single agent job and that’s what I really love about it.

I’ve always liked Sean & Jen’s products, and would also recommend their indexing and CHECKDB solutions. David’s other suggestions are great as well.

Comments closed

Powershell Tools and Excel Tips

Published 2021-02-11 by Kevin Feasel

Jess Pomfret shares a few useful Powershell modules and follows up with tips for maximizing your Excel game:

Since I’ve written a lot about PowerShell previously, I wanted to highlight some other tools that I depend on. I’ve always been a fan of Excel, my personal life is full of spreadsheets – most decisions end with a spreadsheet (lucky for me, my wife is also a big fan of Excel!).  I often find myself copying data into Excel to keep track of work, or to quickly analyse data.  It’s also a great way of sharing data with a clear structure.  I’m also a big fan of shortcuts – so here’s a few I use often.

Jess also reminds me that it’s about time to tune up the bicycle…

Comments closed

Tooling Outside of SQL Server

Published 2021-02-11 by Kevin Feasel

Dave Mason shares a few useful tools::

I’m a proponent of “Show me, don’t tell me”. Screen captures go a long way toward that. Sure, Windows has Paint, but Paint.NET is a bit more advanced. I almost always have an instance of it open in the background. Hit the Print Screen keyboard button and paste (CTRL + V) it into Paint.NET (you can also use the ALT + Print Screen key combination to screen capture just the active window). From there you can do anything: trim down to just a specific part of the image, add some red arrows or circles for emphasis, blur/obscur any sensitive data that’s in the image, etc. I take tweaked screen shots and paste them into just about anything…Word documents, email, even Twitter.

As far as it goes, I think I use different tools than Dave across the board, save for 7Zip. I like Notepad++, SnagIt, WinDirStat, LastPass, 7-Zip (yay for agreement), and mRemoteNG, and despites headphones respectively. But that goes to show that there are plenty of good alternatives for products and it’s worth trying a few out.

Comments closed

Comparing SSMS and Azure Data Studio

Published 2021-02-10 by Kevin Feasel

Deborah Melkin contrasts SQL Server Management Studio with Azure Data Studio:

Honestly, the vast majority of my time is split between Management Studio (SSMS) or Azure Data Studio. I’m pretty simple\straightforward this way. I started playing a lot more with Azure Data Studio over the past year, but I find I’m not able to make the switch to using it full time. It really depends on the task that I need to do.

So what tasks do I do often and which tool do I use?

The plus side for Azure Data Studio is that it’s far enough along that some of these choices are difficult to make. The minus side is that it’s still often on the losing end. I’d expect that shift to continue over the next couple of years as the product matures and becomes a good product for database developers.

Comments closed

More Tools of the Trade

Published 2021-02-10 by Kevin Feasel

Deepthi Goguri shares a list of useful tools for SQL Server work, presentations, and recordings:

1. OBS Studio: This is a free and open source software for video recording and live streaming. I mostly prerecord my sessions using OBS. I personally love this tool as we have pretty much good content on YouTube that teach us how to use this tool.

2. SentryOne Plan Explorer: Plan explorer is an amazing tool to analyze your execution plan and tune your queries very quickly. Its completely free.

Click through for the full list of 10.

Comments closed

Power BI Tools

Published 2021-02-10 by Kevin Feasel

Benni De Jagere shares a list of useful tools around Power BI:

The External Tools (and the Enhanced Metadata format enabling it) allow end users of Power BI Desktop to call on custom built applications, scripts, .. to augment their developer/designer experience. These days, there’s over 40 (I stopped counting) external tools available, each with their own use case and focal area. When showing off some of the capabilities to my clients, it amazes me to see how quickly they pick up these things, and start building out their own ways of working.

Depending on the client, their IT Compliancy rules, the business and technical requirements, my actual tool belt tends to vary. Not every IT organisation allows user to freely install an application, digitally signed or not, so this is definitely an important one to take into your conversations early on.

Read on for Benni’s choices.

Comments closed