Tag Archives: SQL Server

Aggregate of columns, not rows

In SQL Server it is very easy to aggregate a value across many rows. You simply just write something like this: SELECT MAX(SomeColumn) FROM SomeTable This will return one row with the aggregate value (in this case MAXimum) of the column. However, what if you want to do this across columns. What if you have […]

DROP/CREATE vs ALTER on SQL Server Stored Procedures

We have a number of migration scripts that modify the database as our applications progress. As a result we have occasion to alter stored procedures, but during development the migrations may be run out of sequence on various test and development databases. We sometimes don’t know if a stored procedure will exist on a particular […]

Tip of the day: How to tell why your app couldn’t log on to SQL Server

When you get a log in failure on SQL Server the message you get back from SQL Server Management Studio, or in a .NET Exception is vague for security. They don’t want to give away too much information just in case. For example, the exception message will be something like “Login failed for user ‘someUser’.” […]

Optimising clustered indexes in SQL Server 2008

I’ve just found a script on another blog to go through all the clustered indexes in a SQL Server database and rebuild them in order to reduce fragmentation and improve the disk IO needed to get the data. The original script didn’t take into account tables in different schemas so I updated it. I also […]

Rewiring the users and logins in SQL Server

As a developer I find that I’m frequently backing up and restoring SQL Server databases between servers for development and testing purposes. However, each time I do the link between the login (a server concept) and the user (a per database concept) gets broken. There is a stored procedure in SQL Server to wire it […]

If you really must do dynamic SQL…

I may have mentioned in previous posts and articles about SQL Injection Attacks that dynamic SQL (building SQL commands by concatenating strings together) is a source of failure in the security of a data driven application. It becomes easy to inject malicious text in there to cause the system to return incorrect responses. Generally the […]

Tip of the Day #5 (SQL Server memory usage)

You can limit the amount of memory that SQL Server uses by using the sp_configure stored procedure. By limiting the amount of memory that SQL Server is permitted to use it means that more memory is available to other applications or other instances of SQL Server. In fact books on-line recommends setting the minimum and […]

Spatial Operations in SQL Server 2008 (Katmai) – Union and Convex Hull

CODE EXAMPLES IN THIS POST WORK WITH THE NOVEMBER 2007 CTP (CTP 5) OF SQL SERVER 2008. Say you would like to create a polygon out of a group of points. One way of doing this is to union the points together then create a convex hull from those points. A convex hull is a […]

Contradictory messages

While attempting to create a database project in Visual Studio 2008 against a SQL Server 2008 database I got a rather odd error message. The dialog used to create the project requests information about the SQL Server database. It clearly states “The server version must be 2005 or later”. No problem, I thought. So I […]