Contents tagged with SQL

  • Microsoft Cloud Services

    One of the quietly announced (at MIX - WMV here) new things coming from Microsoft "soon" is SQL Server Data Services (SSDS).  The SSDS team has a blog on MSDN.  Ryan Dunn discussed it with me recently and also has been blogging about it.  Last week he announced the release of PhluffyFotos, a sample site built on top of SSDS.  You can sign up for the beta of SSDS here.  Roger Jennings has some comments here. So, what is SSDS (read the FAQ)?  Essentially, it's a way for you to access data from "the cloud" providing a highly scalable and globally available data access story.  One scenario that benefits greatly from this approach is the application that suffers … more

  • Use Unit Test Framework to Test Production DB Consistency

    For Lake Quincy Media's AdSignia Ad Server, I wanted to be able to ensure that the database had some internal logic rules checked periodically.  What kinds of rules?  Well, probably an example would be best.  Suffice to say up front, though, that we're talking about more than NOT NULL or enforcing referential integrity.  For example, part of the ad engine's job is to redirect requests to ads to their destination URL on the advertiser's page.  These URLs are stored in a field in the database.  Occasionally, through cut-and-paste, there would be newlines in the URL field, which would be difficult to detect visually, but which would cause the redirect to … more

  • Stored Procedure Performance Varies Between ADO.NET and Management Studio

    I ran into this very annoying issue earlier this week, that my buddy and SQL guru Gregg Stark was able to track down for me.  I have a fairly intense XtraReport report that gets its data from a stored procedure.  When I run that stored procedure in SQL Management Studio, it returns in less than a second.  When I run it on my web site, it takes over 30 seconds and times out.  WTF?  I confirmed, ad nauseum, that it really was running the same sproc with the same parameters. So I hit up Stark and he suggests I try messing with ARITHABORT settings in Management Studio.  Lo and behold, when it's ON it's fast - less than one second.  When it's OFF it takes 90 … more

  • Creating a New SQL Database

    Today I need to set up a new database for a new web site I'm working on.  Last week, I saw Rick's post about the "mousercise" that defines the typical table set up in SQL Server 2000 and 2005's table designer.  I have to admit that I completely relate to his feelings, and what made his post better still was that in addition to raising a complaint (that I share), he offered a solution!  This is one reason why Rick Strahl's blog is worth reading (and probably has something to do with why he has several thousand more subscribers than my blog).  So, in the course of setting up this database (which I'm doing as I write this, and which currently … more

  • Case Sensitive or Insensitive SQL Query

    Suppose you need to perform a SQL query and you need for it to be case sensitive or case insensitive, and either your database is set up the opposite way or you're smart and you're trying to write your query so that it will work regardless of how the database may or may not be configured.  For instance, consider this query:SELECT UserId, emailFROM aspnet_membership WHERE email = '' If your database contains an email for Bill like this one: then whether or not your query will return any rows will depend on the COLLATION for the database.  If you want to ensure that you DO get results, you can force it to use a CASE INSENSITIVE … more

  • Installing SQL 2005 Management Studio

    I've installed SQL Server on a few dev boxes in my time, and one thing I've noted on several occasion is that even if you check the box during the install to say you want to install all of the client tools, usually they don't install.  You think they installed, the setup dialog said it would install them, but when everything is said and done you don't see anything in the Start-Programs menu except a SQL Configuration option.  What gives? So you install again, only this time the setup dialog tells you nothing's going to change because the client tools were already installed last time.  But they weren't.  Eventually, I've managed to get them installed, but it was usually a … more

  • Using PowerShell to Automate a SQL Task

    I have a very large table in a SQL database that I need to clean up some old data on.  I've already copied all of the data to another table in another database with a different schema.  I have a legacy application that still uses the old data, but the data goes back for years, and now that it's in the new system, I'm willing to remove at least everything up until the new system started running alongside the old one. Deleting thousands of rows in SQL Server is an annoyingly difficult task on a disk-deficient server because inevitably the transaction log fills up and the delete statement fails.  If I had plenty of disk space, I could get round this issue through cleverness like … more

  • COPY One Table Row in SQL

    I have a table with a bunch of columns in it that I wanted to be able to duplicate so that I could just change a couple of columns and not have to re-enter all of the columns and their values.  I did some searching and the closest I came to what I wanted was this.  So, working with Gregg on IM we came up with a more flexible solution that doesn’t require typing in the 20–some column names one might have (twice): ALTER PROCEDURE [dbo].[aa_widget_Copy] ( @widget_id int ) AS BEGIN declare @columns varchar(5000) select @columns = case when @columns is null then column_name else @columns + ',' + column_name end from information_schema.columns where table_name = 'aa_widget' … more