You may think that speaking at usergroups and conferences is reserved for a select group of elite professionals. Let me tell you right away that this is not the case, and that you should seriously consider a rewarding side career as a speaker at usergroups and conferences!
Here’s the why and the how.
Typically, the advice on fill factor is “if it ain’t broke, don’t fix it”. But occasionally, you’ll find a database or even a server with a crazy default setting that just fills your disk and buffer pool without any real benefit. Here’s a nifty script to rebuild any tables and indexes to a fill factor of 100%.
We need to talk about the nullable columns in your database. Specifically, because of how NULL values are compared, they can dramatically affect how some lookup operations perform.
Some operations in SQL Server will turn your entire query plan serial (single-threaded), others will just reserve a so-called “serial zone”. I read up on this stuff a number of years ago (including a great post by Paul White), and thinking that some things must have changed since, I decided to go see for myself.
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.
Read on to see how you can bypass dynamic data masking, and for an alternative approach that uses SQL Server column-level security instead.