Within SSMS, when executing a query, we can view the query results in a grid, or tabular, format (Ctrl-D), as plain text (Ctrl-T), or we can write them to a plain-text reporting (.rpt) file using Ctrl-Shift-F. However, over my 20 plus years as a SQL programmer, I’ve often needed to take these results and use Read more
Using SELECT * FROM within IF EXISTS statements are fine, but in other contexts it causes several problems. For example, INSERT INTO…SELECT * FROM statement assumes the columns are in a particular order, and that order may not last. Also, selecting all the columns in a table when you don’t need them all can cause Read more
Mistakes happen, occasionally .You accidentally close an SSMS query tab without saving it, before realizing it contained an essential bit of code. You’re working late, switching between test and development servers, and accidentally execute code against the wrong server. SSMS conspires against you and crashes unexpectedly, and you lose all your currently open query tabs, Read more
You’ve been working on a query, function or procedure and you want to store the results of executing it in a table; maybe a table variable, or a temporary table. These all require column definitions, and such things can be tiresome to do. Consider the following AdventureWorks query. Listing 1 You need to store the Read more
Using TOP in a SELECT statement without a subsequent ORDER BY clause is legal in SQL Server, but meaningless because asking for the TOP 10 rows implies that the data is guaranteed to be in a certain order, and tables have no implicit logical order. You must specify the order. In a SELECT statement, you Read more
People can, and do, argue a great deal about the relative merits of table variables and temporary tables. Sometimes, as when writing functions, you have no choice; but when you do you’ll find that both have their uses, and it’s easy to find examples where either one is quicker. In this article, I’ll explain the Read more
The MONEY data type confuses the storage of data values with their display, though its name clearly suggests the sort of data it holds. It is proprietary to SQL Server and allows you to specify monetary values preceded by a currency symbol, but SQL Server doesn’t store any currency information at all with the actual Read more
We can use SELECT…INTO in SQL Server to create a new table from a table source. SQL Server uses the attributes of the expressions in the SELECT list to define the structure of the new table. Prior to SQL Server 2005, use of SELECT…INTO in production code was a performance ‘code smell’ because it acquired Read more
In this article, I’ll discuss how I use the SQL Prompt actions that you can apply as part of the Format SQL command. These actions are designed to help improve the overall quality of your SQL code, in various subtle but meaningful ways, such as qualifying object names, standardizing the use of aliases, adding semicolons Read more
Most of us in the data management industry will have learned to adapt, in recent years, to ‘agile’ development and deployment practices. Many organizations have invested heavily in the tools and processes they hope will allow them to deliver new functionality to users more frequently and reliably, while also maintaining quality standards. To achieve this, Read more
