Ten Common SQL Programming Mistakes

Full article here.

It is not always easy to spot “antipatterns” in your SQL, especially in more complex queries. In this article, Plamen demonstrates some of the most common SQL coding errors that he encounters, explains their root cause, and illustrates potential solutions

  1. NULLs and the NOT IN predicate
  2. Functions on indexed columns in predicates
  3. Incorrect subquery column
  4. Data type mismatch in predicates
  5. Predicate evaluation order
  6. Outer joins and placement of predicates
  7. Subqueries that return more than one value
  8. Use of SELECT *
  9. Scalar user-defined functions
  10. Overuse of cursors
Advertisements
Posted in SQL. Tags: . Leave a Comment »

SQL Server 2008 and PowerShell

SQL Server 2008 now has support for PowerShell.

SQL Server provides two PowerShell snap-ins that implement:

* A SQL Server provider, which enables a simple navigation mechanism similar to file system paths. You can build paths similar to file system paths, where the drive is associated with a SQL Server management object model, and the nodes are based on the object model classes. You can then use familiar commands such as cd and dir to navigate the paths similar to the way you navigate folders in a command prompt window. You can use other commands, such as ren or del, to perform actions on the nodes in the path.

* A set of cmdlets, which are commands used in PowerShell scripts to specify a SQL Server action. The SQL Server cmdlets support actions such as running a sqlcmd script containing Transact-SQL or XQuery statements.

Resources:
SQL Server PowerShell Overview

Pre-written Powershell scripts for SQL Server Management

SQL Server Standard Edition 2005 upgrade to SQL Server Standard 2008 upgrade rules fail

I had an issue with SQL Server 2008 Upgrade Rules. I was running SQL Server 2005 Standard. I could not get past the Upgrade Rules. I told me I was trying to upgrade to an incompatible version and to see the version matrix. It made no sense to me. I ran “Select @@version” and it all came back correct. I did some digging into the Microsoft SQL Server100Setup BootstrapLogSummary.txt. I found out that my Reporting Services and tools version were installed with SQL Server 2005 Developer Edition.

What! How could that be? Yes, that is what I had installed, so not matter what version I tried to upgrade to it failed. The installer could not figure out what to do.

The Solution:
None really, I could not upgrade.
So, I had to re-create my environment with a new clean install of SQL Server 2005 Standard w/ Reporting Services then go through my upgrade tests. All was fine after that.

Upgrade to SQL Server 2008 fixes Reporting Services nested data region export to Excel

I recently upgraded to SQL Server 2008. The primary reason was to fix an issue with SQL Server Reporting Services – Excel Export issues with nested data regions.

“Any data region nested inside of a table or matrix data region is not supported. An error is displayed in Excel if this layout is encountered.”

They reportedly fixed this issue in SQL Server 2008. I my testing supports the claim and we no longer have the “nested data region errors”.

I must say that I was impressed with the new installer. Microsoft goes to great lengths to validate your SQL Server configuration before trying to upgrade. I had one upgrade issue that I talk about in this post, that was caused by me.

Logging in .NET

Options:
Enterprise Library 3.1

*Populating and raising events from code
*Populating a log message with additional context information
*Tracing activities and propagating context information
*Checking filter status before constructing log messages.
*Configuring options for trace listeners

*Directing different event types to different trace listeners
*Configuring log filters
*Configure logging to be performed from a central location
*Creating a custom trace listener

    log4net 1.2.10
    log4net is a tool to help the programmer output log statements to a variety of output targets. In case of problems with an application, it is helpful to enable logging so that the problem can be located. With log4net it is possible to enable logging at runtime without modifying the application binary. The log4net package is designed so that log statements can remain in shipped code without incurring a high performance cost. It follows that the speed of logging (or rather not logging) is crucial.

    At the same time, log output can be so voluminous that it quickly becomes overwhelming. One of the distinctive features of log4net is the notion of hierarchical loggers. Using these loggers it is possible to selectively control which log statements are output at arbitrary granularity.

    log4net is designed with two distinct goals in mind: speed and flexibility