As a kid, I found Magic 8 Balls alluring. There is something appealing about a who-knows-how-many-sides die emerging from the depths of a mysterious inky blue fluid to help answers life’s most difficult questions.
I never ended up buying a magic eight ball of my own though, so today I’m going to build and animate one in SQL Server Management Studio.
Now you can finally answer those important life questions without leaving Management Studio.
I was troubleshooting a deadlock the other day and it got me thinking…. I know the number 1205 by heart and know it is associated to a deadlock. What other numbers are there that you can associate to an event or object or limitation. For example 32767 will be known by a lot of people as the database id of the ResourceDb, master is 1, msdb is 4 etc etc.
So below is a list of numbers I thought of
Leave me a comment with any numbers that you know by heart
BTW I didn’t do the limits for int, smallint etc etc, those are the same in all programming languages…so not unique to SQL Server
Read on for Denis’s list.
I used to go to the Emoticons (Emoji) 1F600—1F64F page of unicode-table.com to copy and paste characters, code points, or check the encoding chart at the bottom of each character page (the “hex” column of both “UTF-16BE” and “UTF-16LE” rows have proven most useful).
But not anymore. Now, I just hit: Ctrl + 0.
When I do that, I get a list of 188,657 code points. Each row contains the official code point value (“U+HHHH”), the integer value, the hex value (“0xHHHH”), the character itself, the UTF-16 Little Endian byte sequence (how it is actually stored, and what you get if you convert an
VARBINARY), the surrogate pair values, the T-SQL notation (which does not require using an
_140_collation), the HTML notation (“&#xHHHH;”), and finally the C-style notation (“\xHHHH” ; used for C / C++ / C# / Java / etc). I can copy and paste any of those values and use them in queries, emails, blog posts, .NET code, and so on.
Click through to see how Solomon does this.
So a few things here. I’m using a global temp table instead of a local one because it makes it easier to reference. Local temp tables aren’t listed in tempdb under their name while global ones are.
The first part of this message (the bit in black) is a warning basically telling us that if there is a temp table (a # at the front of the name) it’s going to ignore the multi part reference. In other words, you’re going to get this message any time you try to use a multi part name and a temp table. Linked server or not. The second part of the message (the bit in red) just tells us that there isn’t a temp table named ##DBList.
Kenneth finds a way, but I can’t think of a scenario where accessing a temp table on a different instance turned out to be a good idea.
What’s achievable? I want to identify tables to extract from the database that won’t take years. Large monolithic systems can have a lot of dependencies to unravel.
So what tables in the database have the least dependencies? How do I tell without a trustworthy data model? Is it the ones with the fewest foreign keys (in or out)? Maybe, but foreign keys aren’t always defined properly or they can be missing all together.
My thought is that if two tables are joined together in some query, then they’re related or connected in some fashion. So that’s my idea. I can look at the procedure cache of a database in production to see where the connections are. And when I know that, I can figure out what tables are not connected.
Click through for the script to help you do it.
In the event shown directly above, towards the bottom, in the final “<Data>” element that starts with “
\\?\C:\ProgramData...“, that entry does point to a folder containing a Report.wer file. It is a plain text containing a bunch of error dump info, but nothing that would seem to indicate where to even start looking to fix this. And, nothing useful for searching on, at least not as far as my searching around revealed.
There you have it: a nearly untraceable way to prevent SQL Server from starting.
Read on to see how what Solomon did.
Or if you are Rob Volk (@sql_r on Twitter), and you want to create an annoying database on your best frenemy’s SQL Server that includes brackets in the name, like:
This [database] Is Awesome
You will need to do:
CREATE DATABASE [This [database]] Is Awesome];
I’m not saying you should do that, but I’m also not saying you shouldn’t.
This is a contrived example but I was given a script that got the “Discipline”, “DocumentVersion”, “DocumentNumber”, “SectionNumber”, and “SectionName” out of the above.
And while it works, I hate that formatting. Everything is all squashed and shoved together.
No, thanks. Let’s see if we can make this more presentable.
Shane has a regular expression. Now Shane has two problems.
In all seriousness, regular expressions are extremely powerful in the right scenario. Shane mentions being okay with it not in the database engine and I’m usually alright with that, but there are cases when it’s really helpful like figuring out if a particular input is valid. One example I have on a project is finding legitimate codes (like ISBN) where you can solve the problem easily with a regex but my source data is abysmal. I can use the SQL# regular expression functions to drop into CLR and figure out whether that value is any good, something I would have a lot more trouble with in T-SQL alone.
One of the reasons I love DevOps so much is because I’ve done it successfully. I’ve worked on teams that built fully automated deployment mechanisms to get code from Dev to Production. Further, we automated the creation of dev & test servers. We automated the creation of production servers too. We automated the heck out of everything.
And then they fired me…
When we started building our DevOps processes, I was supporting two development teams. As we got better at automating our work, I was supporting three teams. By the time we had fully automated all the various processes, I was supporting between five and seven teams at different levels.
To support Grant’s point, I’ve had a draft in my personal blog entitled “The Cloud is not Stealing Our Jobs” from May of 2017 that I never got around to finishing. Back in 2017, that was what was going to kill the DBA role.
The role has certainly changed over the years. I suppose if your definition of a DBA is someone who lays out indexes starting on certain drive sectors to take advantage of rotation speed on that single 5400 RPM spinning disk drive AND NOTHING ELSE, then your job might not be there. But that describes exactly zero people I have ever known in the industry.
This is NOT a production ready solution, in fact I would not even recommend that you try it.
I definitely wouldn’t recommend it on any machine with anything useful on it that you want to use again.
We will be using a re-compiled dockerd.exe created by someone else and you know the rules about downloading things from the internet don’t you? and trusting unknown unverified people?
Maybe you can try this in an Azure VM or somewhere else safe.
Anyway, with that in mind, lets go.
That’s the kind of intro that makes me want to try it out.