In order to test graph structures, I needed a large set of random data. In some ways, this data will resemble the IMDB database I will include later in this chapter, but to make it one, controllable in size and two, random, I created this random dataset. I loaded a set of values for an … Read more
Transact SQL (TSQL) is the languaged used to query and update data stored in a SQL Server. This book, written by Simple Talk author Greg Larsen, will give developers an understanding of the basics of the TSQL language. Programmers will have the building blocks necessary to quickly and easily build applications that use SQL Server. … Read more
I am in the middle of a project to enable our corporate databases to work with continuous integration using RedGate SQL Automation (and hopefully get a few blogs/articles out of it too.) One of the things that the tool does is create your database in multiple ways, with multiple names, in order to compare to … Read more
Note: post revised to take into consideration tips from comments The other day, I was working with a query where someone had put together an expression that had something along the lines of: And I thought to myself… “Self, what will the datatype of this be? Will it fit what the programmer wanted it to … Read more
Note: This is an update of a blog I posted in 2006 with a lot of additional information (I was less wordy in my 30s apparently). It is just as relevant today. I have a presentation I do occasionally on concurrency entitled “Let Me Finish” that covers many of the different types of concurrency behaviors … Read more
Having spent a lot of my working life trying to preserve the integrity of data, there was a certain intriguing novelty in the idea of pseudonymizing data. One of the standard techniques of pseudonymization is that of shuffling data columns as though you are shuffling cards. The original values are kept but placed in the … Read more
I haven’t seen a SQL Server table with real unencrypted credit card numbers for several years, and I don’t know of any good reasons to have them stored that way. However, I’ve needed them in the past for testing a web application that had to take credit card details. Generating credit cards in a way … Read more
Today’s blog post is in response to Bert Wagner’s invitation here: https://bertwagner.com/2018/07/03/code-youd-hate-to-live-without-t-sql-tuesday-104-invitation/. For me, the answer was pretty simple. There is one piece of code that I have written that I find very useful, and even more… it was my (as far as I know, my own idea!) In my life, I have obviously learned a … Read more
Assumptions are generally terrible things for programmers (and likely my next “What Counts” blog goes there), because what looks to be true when trying things out on your machine, may not be so at a larger scale. There are lots of examples out there, sorting when there’s a clustered index, not checking errors because you … Read more
As the saying goes: do to others as you would have them do to you. When writing code, I would suggest a complimentary saying of “do to yourself as you would to do others”. Write your software as if it will be used by a very wide audience, even if you aren’t planning on being the … Read more
Maintaining database security can be a complex task. In this post, Louis Davidson describes a method that can be used to maintain separate security for multiple environments (e.g., production and development) without changes to the database itself. … Read more
The other day, a person I work with was creating some fairly complex CHECK constraints to protect data in a new table that was being created (a practice I am 100% for: https://www.red-gate.com/simple-talk/blogs/eliminating-annoying-space-characters-via-check-constraints/. The multiple constraints were combined into just one, with several conditions condensed into a single Boolean expression, using several ANDs, ORs, and … Read more
This week, I had a problem where I needed to find and eliminate from the results of my query, data with overlapping ranges. I have written about this topic before, in my database design book book, in regards to building a trigger to avoid overlapping ranges. But even though I have written on the topic … Read more
This week’s blog is a just about a few simple SQLCMD mode tricks for SSMS, but these tricks have saved me a lot of heartache over the years. If you have not used SQLCMD mode, you probably should be. I don’t intend to introduce SQLCMD too deeply, because there are tons of articles out there … Read more
I am working through my OneNote folder of blog ideas, and this one, while being one of the least consequential of them, was the most interesting tonight (and just may help you one day when converting data some day when you are wondering what the heck just happened in a query). You probably know that … Read more
In this blog, I want to explore what you can do to block the owner of a database from doing stuff in the database they “own”. Own is a strange term, because really there is just one user that is listed as owner, but there are there are three users who essentially are owner level, … Read more
The versatility of T-SQL allows you to create complex scripts to accomplish just about anything you need to do. In this blog post, Louis Davidson demonstrates how to output the status so that you can know how far along a script has progressed.… Read more
Sometimes, in the quest for raw SQL performance, you are forced to sacrifice legibility and maintainability of your code, unless you then document your code lavishly. Phil Factor's SQL Speed Phreak challenge produced some memorable code, but can SQL features introduced since then help to produce code that performs as well and is also easy to understand? Aunty Kathi investigates.… Read more
What are the scripts that a working DBA uses so often that they become part of the 'muscle memory'? Grant Fritchey asked the question on the forums of SQL Server Central. From the large response, Grant was able to pick out the ten most popular T-SQL commands, scripts, or fragments. It seems as if, despite the range of tasks we do, there are some common tools we use.… Read more
