Fun with SQL part 1
With great power comes great responsibilities.
When you have access to a database, you can do great things.
Things that can take users hours or even days, could be done within seconds.
The other way around is also true:
What users have put in a database can be lost forever within a second.
Wait? Is this about Fun with SQL? Yes it is, keep reading.
Fun with SQL
Working with the SQL Management Studio is great.
You can do wonderful and very usefull things with it.
Just don't forget to lock your machine when it is open and you need to be away from it for even a few minutes.
Why? Her are some things might happen when I see your Management Studio open and you are not there!
p.s. Never do this on Production!
Truncate a Table
Yes, I could truncate a Table and all the data will be gone forever with no undo.
But that would just be evil, and this post would be about fun?
True, so never truncate your co-workers data!
But making him think someone did?
Hmmm, now that is an option! Try this one:
Just run the command and remove the line. Now go back to your place and wait.
You just gave the instruction to show just the format of a table or view.
In other words, just show the columns.
From then on, any query will produce no results whatsoever.
Just the Column Name, like all data was truncated.
Now that will keep your co-worker panicked for a few moments.
set fmtonly off
What language?
This one is a nice one when your co-worker is working with dates. Let's say we have this query:
Just try it, it works fine. Until you run this one:
British is my favorite to switch the language to.
That is because they will not recognize it straight away.
But do switch to British and try the same Query again.
Now it will fail because with British the date format also changes from 'yyyy-MM-dd' to 'yyyy-dd-MM'
Ah, if only my co-worker would have used DateFromParts like I told him too:
DateFromParts makes the Query imune from this trick. And it also makes sure your Query runs on other servers with a different language setting as well.
Don't use * so often
"select * from" is something everybody uses.
Often without any constraint.
Until you run into a table that has millions of records and hundreds of columns.
Then the performance of the SQL Server has to take a big hit, resulting in angry users.
So always be sure to add a Constraint to it to make sure you don't get all data.
Or even better, use "select top 10 *" instead.
But why always use * when specifying some columns is what I would advice you to do?
For co-workers that do't listen to my advice, I might use this one:
From now on, every "select *" will give just that one Colun with Null values!
But wait, we can make it even more evil. First let's remove that Column:
And instead add a computed Column that is guaranteed to raise an error like dividing by zero. To make it even impossible to debug, let's switch the language to Korean:
GO
set Language Korean
Good luck, my dear co-worker. You know where to find me!
That's it for today!
Although this post is about fun ways to annoy a co-worker, do note that it is important to lock your machine when you have to be away from your machine.
Even when it is just for a few minutes.
Also note that it is important to keep regional settings in mind as your Queries should not depend on your regional settings.
Functions like DateFromParts are there for a reason!
And do remember that running Queries agains a database you don't know that well requires caution.
Do you just need the Column Names? Set fmtonly on.
Do you want to see a sample of data? Use "select top 10 *".
Like I said before, do not use tricks like this on production. Never.
But to tease your co-worker? Be my guest!
You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!