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 …
Tag Archives: SQL Server
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 …
Continue reading “DROP/CREATE vs ALTER on SQL Server Stored Procedures”
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’.” …
Continue reading “Tip of the day: How to tell why your app couldn’t log on to SQL Server”
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 …
Continue reading “Optimising clustered indexes in SQL Server 2008”
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 …
Continue reading “Rewiring the users and logins in SQL Server”
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 …
VBUG Manchester – Spatial Queries
Just a reminder to anyone that is interested that I’m speaking tomorrow (Thursday July 24th) Evening at VBUG in Manchester on the topic of SQL Server 2008 Spatial Queries, slide decks are available on my website. Technorati Tags: VBUG,Manchester,Spatial Queries,SQL Server 2008,SQL Server,SQL,spatial,slide deck
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 …
Continue reading “Tip of the Day #5 (SQL Server memory usage)”
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 …
Continue reading “Spatial Operations in SQL Server 2008 (Katmai) – Union and Convex Hull”
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 …
