Everyone has a script, a hack or a checklist they can’t function without. In this edition of T-SQL Tuesday, Bert Wagner challenged us to write about our favorite scripts. This is my take.
Here are 50 random numbers:
--- 50 random numbers WITH cte AS ( SELECT 0 AS i, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3) AS n UNION ALL SELECT i+1, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3) FROM cte WHERE i<50) SELECT * FROM cte;
And in SSMS, with the variable-width default font, the output looks… slightly-less-than-readable in the grid view:
We could use STR() to format the output, but the indent looks a little off:
--- 50 random numbers WITH cte AS ( SELECT 0 AS i, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3) AS n UNION ALL SELECT i+1, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3) FROM cte WHERE i<50) SELECT *, STR(n, 12, 2) AS with_str FROM cte;
Here’s something I’ve found: the space character is roughly about half the width of a typical number character. So replace every leading space with two spaces, and it will look really neat in the grid:
--- 50 random numbers WITH cte AS ( SELECT 0 AS i, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3) AS n UNION ALL SELECT i+1, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3) FROM cte WHERE i<50) SELECT *, STR(n, 12, 2) AS with_str, REPLACE(STR(n, 12, 2), ' ', ' ') AS with_replace FROM cte;
(and terrible everywhere else, obviously.)
It’s not entirely uncommon to want to group by a computed expression in an aggregation query. The trouble is, whenever you group by a computed expression, SQL Server considers the ordering of the data to be lost, and this will turn your buttery-smooth Stream Aggregate operation into a Hash Match (aggregate) or create a corrective Sort operation, both of which are blocking.
Is there anything we can do about this? Yes, sometimes, like when those computed expressions are YEAR() and MONTH(), there is. But you should probably get your nerd on for this one.
I’m trying a new type of blog post, and if it works out, I would be happy to post more of the same going forward. The format is a real-world troubleshooting mystery, and I’ll clue you in to the details along the way.
How quickly can you crack it?
Download and print this nifty little PDF with all of the INNER, LEFT, RIGHT, FULL and CROSS JOINs visualized! It’ll look great on your office wall or cubicle. Your coworkers and your interior decorator will love you for it.
How it works: For each join example, there are two tables, the left and the right table, shown as two columns. For the sake of simplicity, these tables are called “a” and “b” respectively in the code.
You’ll notice that the sheet uses a kind of pseudo-code when it comes to table names and column names.
Dynamic data masking is a neat new feature in recent SQL Server versions that allows you to protect sensitive information from non-privileged users by masking it. But using a brute-force guessing attack, even a non-privileged user can guess the contents of a masked column. And if you’re on SQL Server 2014 or earlier, you won’t have the option of using data masking at all.
Encrypting your SQL Server’s TDS connections should be high on your list of things to do if you’re concerned with the privacy of your data. This often boils down to one big problem: can you get a valid certificate without paying a ton of money, and will it work with SQL Server?
So follow me down the rabbit hole, as we work out the steps to using Let’s Encrypt to create (and auto-renew!) a certificate for SQL Server. This is going to get technical.
Inspired by an actual customer scenario: what if you have a legacy app that doesn’t schema-prefix its database objects, but you want it to work with a specific assigned schema? There’s a quick and easy solution.
SQL Server Management Studio allows you to view effective permissions on an object, but it’s limited in a few important respects. To work around some of those limitations, I’ve built a stored procedure to display all the defined and effective permissions across an entire SQL Server database.