Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.
Last week I reviewed how far I’d come and some of what I’d learned so far on this journey. You [hopefully] noticed the titles of the previous episodes were all Star Wars-esque titles, and the episodes were numbered. All of those posts were a generalized recap of what had happened two or three weeks previously. Going forward, my contributions will be more timely—as in what I’ve learned or experienced within the last week or so—and hopefully continue to add value to my fellow apprentices in the #sqlfamily.
Brackets Not Feeling the Love?
There seems to be some generalized hating on the use of square brackets in TSQL code among both new and seasoned DBAs. What are square brackets? I’m sure if you’ve spent almost any time in the SSMS query editor you’ve seen something like this:
[StackOverflow2010].[dbo].[Users]
This commonly happens when you use SSMS to create your scripts for you, or you drag and drop a database or table into the query editor window. The example above is a demonstration of a three-part naming convention for the Users table in the StackOverflow2010 database. What this shows is [NameOfDatabase].[Schema].[TableName]. The periods, or dots, are concatenating the three parts of the name together.
I get why some folks don’t like the brackets as they tend to muddle your script’s readability. To see a great example of this in action, check out Michael J. Swart’s post Remove SQL Junk (Brackets and Other Clutter). In most cases, you don’t need square brackets in your scripts, but there are instances where you will need them. That’s mostly beyond the scope of this post.
Cool side note: One of the tidbits I learned during this investigation into square brackets is that a database can have tables with identical names as long as each table’s schema is different. That’s something for you apprentice and accidental DBAs to be aware of for the future.
An Example of Nifty Bracket Usage
I mentioned last week how I have been working through the Stairway to T-SQL course at SQL Server Central. While playing with the GROUP BY clause scripts, I became annoyed at the inelegance of the table header output. Here’s an example:
USE tempdb;
GO
SELECT StoreName
,SUM(TotalSalesAmount) AS StoreSalesAmount
FROM dbo.SalesTransaction
GROUP BY StoreName;
The script above renders this result:
I decided to test the waters and “fix” this by using square brackets for the column header names:
USE tempdb;
GO
SELECT StoreName AS [Store Name]
,SUM(TotalSalesAmount) AS [Store Sales Amount]
FROM dbo.SalesTransaction
GROUP BY StoreName;
Which rendered this:
Ahh, satisfying.
It’s a super simple addition, and one that does not detract all that much from the script’s readability. What is does do in my opinion is make the output more reader-friendly. Essentially, consider if you might want to use square brackets when you want to use spaces or special characters in your column headers. If you want to go crazy, you can take using square brackets to rename columns a whole lot deeper.
As a side note, you may have to use square brackets if you have a table or column name that uses a word that is reserved in SQL DML. For instance, if someone was wicked enough to name a table or column “Index” then you would have to use square brackets around the name in order to create scripts that function properly.
But no one would ever be that evil, would they?
That’s all for this week. Join me next time for next episode in The Ironic DBA Files.
Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.
The Ironic DBA Files
-
-
- Prequel: The Ironic DBA—Starting a New and Unexpected Career
- Episode 1: You Back That Up?
- Episode 2: Attack of the Corruption
- Episode 3: Revenge of the Index
- Episode 4: A New Primary Key
- Episode 5: The Maintenance Plan Strikes Back
- Episode 6: Return of the TSQL
- Episode 7: The Backup Awakens
- Episode 8: The Last Rebuild
- Episode 9: Rise of the Clients
- Review One: A SQL Story
-
