Jon Galloway : SQL

The real reason SELECT * queries are bad: index coverage

Jon Galloway — Thu, 19 Jul 2007 06:56:00 GMT

Are SELECT * queries bad? Sure, everyone know that. But, why?

It's returning too much data, right?

That's the common answer, but I don't think it's the right one. If you're working with a reasonably normalized database, the actual network traffic difference is pretty small.

Let's take a look at a sample. The following two queries select 326 rows from the TransactionHistoryArchive table in the AdventureWorks database (which has a total of 89K rows). The first uses a SELECT * query, the second selects a specific column:

SELECT * FROM Production.TransactionHistoryArchive 
WHERE ReferenceOrderID < 100

SELECT ReferenceOrderLineID FROM Production.TransactionHistoryArchive 
WHERE ReferenceOrderID < 100

In this case, the difference in network traffic is only 15K, roughly a 10% difference (180K vs. 165K). It's worth fixing, but not a huge difference.

SELECT * makes the Table / Index Scan Monster come

Often, the bigger problem with SELECT * is the effect it will have on the execution plan. While SQL Server primarily uses indexes to look up your data, if the index contains all the columns you’re requesting it doesn’t even need to look in the table. That concept is known as index coverage. In the above example, the first query results in a Clustered Index Scan, whereas the second query uses a much more efficient Index Seek. In this case, the Index seek is one hundred times more efficient than the Clustered Index Scan.

Unless you've indexed every single column in a table (which is almost never a good idea), a SELECT * query can't take advantage of index coverage, and you're likely to get (extremely inefficient) scan operations.

If you just query the rows you'll actually be using, it's more likely they'll be covered by indexes. And I think that's the biggest performance advantage of ignoring SELECT * queries.

The Stability Aspect

SELECT * queries are also bad from an application maintenance point of view as well, since it introduces another outside variable to your code. If a column is added to a table, the results returned to your application will change in structure. Well programmed applications should be referring to columns by name and shouldn't be affected, but well programmed applications should also minimize the ways in which they are vulnerable to external changes.

Shameless Plug: I go into this (and a lot other important performance tips) in more detail in a soon-to-be-released book for SitePoint.

[T-SQL] Getting distinct, current entries from tables with timestamp / datetime fields

Jon Galloway — Fri, 13 Jul 2007 05:50:00 GMT

It's relatively easy to store data with a time dimension, but querying it is another matter. If you select from a temporal tables (one which includes historical information indicated by a timestamp or datetime column) based on your ID, you'll get a lot of duplicate records; only one or a few of those records will be applicable to a given time or timespan.

For example, the following query (from the AdventureWorks sample database) returns 746 records:

SELECT ActualCost 
FROM Production.TransactionHistory 
WHERE ProductID = 784

A table with History in the name is expected to work that way, but I've run into plenty of tables which allow for duplicates of every column but the primary key, differentiated by a datetime column. I'm starting to use these more now, partly due to project requirements, and partly because SubSonic has built-in support for CreatedOn and ModifiedOn columns.

In the past, I've used stacked views or nested subqueries to handle this madness. It's a pain in the neck, and often leads to frustrations with the GROUP BY clause since queries with an aggregate term can't include a column in a resultset that's not in the GROUP BY clause (but adding those columns to the GROUP BY clause isn't the right solution, as it alters the rows you're returning).

The general idea is to write a query which gets the latest update date for a unique ID combination, then join against it. It works, but it's a pain in the neck, and it's error prone. Fortunately, SQL Server 2005's RANK / PARTITION features make this a lot easier.

Let's talk specifics - a query against the AdventureWorks Production.TransactionHistory table which returns the latest record Product Name and Number by Transaction Date. There are 113K rows in that table, but if we only want the latest entry for each Product we're down to 490 rows.

Unlike the traditional GROUP BY based clause, the RANK / POSITION based queries are a bit more intuitive once you've got the hang of them. The important thing is to use PARTITION in the same way you'd think of a GROUP BY - the column(s) you want unique should show up in the PARTITION clause.

One limitation on RANK queries is that you can't use the RANK value directly in a where clause. That's easy to work around by placing the RANK value in a subquery and the WHERE clause filtering on the RANK in the outer query.

UPDATE: Based on comments, my original example was a little too simple. I've added a few joins and return columns; the point is that we can continue to add other columns to the result set without having to worry about how the grouping is handled. Yes, you can do this with subqueries, but in many cases those will continue to grow more complicated as you add tables and columns; this syntax doesn't.

SELECT
    Product.Name,
    Product.ProductNumber,
    TransactionDate,
    TransactionID,
    ProductCategory.Name,
    ProductSubcategory.Name,
    ProductSubcategory.ModifiedDate
FROM
(
    SELECT DISTINCT
    Production.TransactionHistory.ProductID,
    Production.TransactionHistory.TransactionDate,
    Production.TransactionHistory.TransactionID,
    RANK() OVER (
        PARTITION BY Production.TransactionHistory.ProductID 
        ORDER BY Production.TransactionHistory.TransactionDate DESC) 
    AS DateOrder
    FROM Production.TransactionHistory
) CurrentTransactionHistory
INNER JOIN Production.Product 
    ON Production.Product.ProductID = CurrentTransactionHistory.ProductID
INNER JOIN Production.ProductSubcategory ON
    Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID
INNER JOIN Production.ProductCategory ON
    Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID
WHERE DateOrder = 1 
ORDER BY ProductSubcategory.ModifiedDate

[SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string

Jon Galloway — Sun, 25 Feb 2007 03:09:42 GMT

Barry Dorrans recently mentioned that you can force the database connection protocol by specifying np: or tcp: before the server name in your connection string. I've jumped through some hoops before using localhost to target tcp and (local) to target named pipes, but it looks like there's a much better way to do this (since MDAC 2.6).

There's more info in MS KB Article 313295:

TCP/IP:
server=tcp:hostname
You can optionally specify a specific port number. By default, the port is 1433.
server=tcp:hostname, portNumber
Named Pipes:
server=np:hostname
You can optionally specify a specific named pipe.
server=np:\\hostname\pipe\pipeName

They recommend that you always use (local) to specify the local machine, and then specify a protocol. Sounds like the right way to connect to the local machine over TCP is to specify server=tcp:(local), and to use named pipes on the local machine you'd use np:(local). To specify a protocol when pointing to a server, you'd use server=tcp:DBSERVERNAME.

To quote my previous post:

There are many differences between TCP and Named Pipe connection, but if you're on localhost you're mostly concerned with simple access.

The default ASPNET account generally has an easier time with TCP, since the ASPNET user doesn't have access to named pipes by default (http://support.microsoft.com/Default.aspx?id=315159).
If you're using impersonation, Named Pipes is usually simpler. If you're using impersonation with no username specified, you're running under the IIS Authenticating user. This defaults to IUSR_MACHINENAME if you're allowing annonymous access, which generally has access to the IPC$ share required for named pipe communications.

You can target other supported protocols, too:

Multiprotocol = rpc
Shared Memory = lpc
NWlink IPX / SPX = spx
Banyan VINES = vines
Apple Talk = adsp

Passing lists to SQL Server 2005 with XML Parameters

Jon Galloway — Fri, 16 Feb 2007 09:01:00 GMT

Overview

SQL Server 2005's XML capabilities make it a easier to pass lists to SQL Server procedures.

Background

I recently needed to write a stored procedure which took a list of ID's as a parameter. That's one of those things that seems like it would be really simple, but isn't. You'd think you could just pass in a comma delimited string of id's: @ids = '3,5,7,8' and use something like 'SELECT * FROM Products WHERE ID IN (@ids)'. Nope, it doesn't work. I still remember my surprise when I ran into that six or seven years ago.

There are a huge variety of workarounds for this issue - see Erland's comprehensive list ranging form SQL Server 6.5 to 2000. I've used several of these, and while they worked I never liked them. Probably the best method is to just use a SPLIT table valued function which splits your string and returns a table. It's clean, but all of your procedures depend on the existence of that function.

It was also possible to use OPENXML in SQL Server 2000. The syntax was obviously put together by C++ programmers (you have to prepare a document and work with an integer handle, which feels a lot like a pointer), and there were some limitations to be aware of, but it pretty much worked.

This time around, I decided to try this with SQL Server 2005's XML capabilities and see if it was any easier. It is.

Getting started with SQL Server 2005's XML Syntax

XML variables in SQL Server 2005 make it easy to "shred" XML strings into relational data. The main new methods you'll need to use are value() and nodes() which allow us to select values from XML documents.

DECLARE @productIds xml
SET @productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>' 

SELECT
ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)

Which gives us the following three rows:

3
6
15

Alright, just show me how to pass a list in a procedure parameter already!

Here's a proc which takes a single XML parameter. We first declare a table variable (@Products) and load the XML values into it. Once that's done, we can join against the @Products table as if it were any other table in the database.

CREATE PROCEDURE SelectByIdList(@productIds xml) AS

DECLARE @Products TABLE (ID int) 

INSERT INTO @Products (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID) 

SELECT * FROM 
    Products
INNER JOIN 
    @Products p
ON    Products.ProductID = p.ID

Now we can call it as follows:

EXEC SelectByIdList @productIds='<Products><id>3</id><id>6</id><id>15</id></Products>'

Which gives us the following:

ProductID	ProductName	SupplierID	CategoryID	QuantityPerUnit	UnitPrice	UnitsInStock	UnitsOnOrder	ReorderLevel	Discontinued	ID
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10	13	100	25	0	3
6	Grandma's Boysenberry Spread	3	2	12 - 8 oz jars	25	120	0	25	0	6
15	Genen Shouyu	6	2	24 - 250 ml bottles	15.5	39	0	5	0	15

In order to use this, you'll need to an XML string with your ID's. In our application, Steve was handling the application code, and I talked him into doing this via quick and dirty string concatenation. His method worked great:

public static string BuildXmlString(string xmlRootName, string[] values)
{
    StringBuilder xmlString = new StringBuilder();

    xmlString.AppendFormat("<{0}>", xmlRootName);
    for (int i = 0; i < values.Length; i++)
    {
    xmlString.AppendFormat("<value>{0}</value>", values[i]);
    }
    xmlString.AppendFormat("</{0}>", xmlRootName);

    return xmlString.ToString();
}

What's next?

This is a very simple use of XML in SQL Server. You can pass complex XML documents containing business objects to insert and update in your relational tables, for instance. If you're going to do that with a large amount of data, have a look at Ayende's clever use of SqlBulkCopy to handle that more efficiently.

sysobjects, sys.objects... what about INFORMATION_SCHEMA?

Jon Galloway — Sun, 31 Dec 2006 23:53:00 GMT

SQL Server Management Objects (SMO) is one of the coolest parts of SQL Server 2005, in my opinion. I've written about SMO before, and used it extensively in writing Data Dictionary Creator. In a nutshell, SMO is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

One thing about SMO puzzles me, though - it uses the system tables (sysobjects and sys.objects) instead of INFORMATION_SCHEMA when it checks if tables, views, or procedures exist. There are at least three ways that's evident:

Scripting database objects from SSMS
Scripting database object using SMO in CaptureSQL mode
The new SQL Server Database Publishing Wizard

All the above check for object existence using system table checks rather than INFORMATION_SCHEMA checks. I'm not just guessing how they work, though - take a look at Microsoft.SqlServer.Management.Smo.Scripts..ctor() in Reflector:

Scripts.INCLUDE_EXISTS_TABLE80 = "IF {0} EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{1}') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)"
Scripts.INCLUDE_EXISTS_TABLE90 = "IF {0} EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{1}') AND type in (N'U'))"
Scripts.INCLUDE_EXISTS_VIEW90 = "IF {0} EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'{1}'))"
Scripts.INCLUDE_EXISTS_VIEW80 = "IF {0} EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{1}') AND OBJECTPROPERTY(id, N'IsView') = 1)"
Scripts.INCLUDE_EXISTS_PROCEDURE80 = "IF {0} EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{1}') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)"
Scripts.INCLUDE_EXISTS_PROCEDURE90 = "IF {0} EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{1}') AND type in (N'P', N'PC'))"

INFORMATION_SCHEMA views good, System Tables bad

The biggest problem is that internal system tables change between versions, so scripts are generated for one specific SQL Server version. The INFORMATION_SCHEMA views, on the other hand, are part of the SQL92 standard, so they don't change between versions.[1] Here's a pretty good article on INFORMATION_SCHEMA if you'd like to read further.

For example, the following works on both SQL 2000 and SQL 2005:

use northwind
go

if exists (select * from information_schema.tables where table_name = 'Products' and table_type = 'BASE TABLE') print 'Products table exists'
if exists (select * from information_schema.tables where table_name = 'Invoices' and table_type = 'VIEW') print 'Invoices view exists'
if exists (select * from information_schema.routines where specific_name = 'SalesByCategory') print 'SalesByCategory stored procedure exists'

Disclaimer

I know there are some cases where the INFORMATION_SCHEMA won't cut it. INFO SCHEMA is a cross database standard, so it's got no concept of some advanced and proprietary features. In Data Dictionary Creator, I had to support different syntax for extended property lookup, since SQL Server 2000 uses ::fn_listextendedproperty and SQL Server 2005 uses sys.fn_listextendedproperty.

Fine. That's no reason to drop INFORMATION_SCHEMA - use it for the 90% of the time it will do the job, and drop down to the system tables when you have to.

Workaround: Use Regular Expression replacements on the scripts

You can use regular expression replacements to "fix" SMO generated scripts to run against INFORMATION_SCHEMA.

This regular expression matches a table existence check in SQL 2000 specific syntax:

^IF\ EXISTS\ $SELECT\ \*\ FROM\ dbo\.sysobjects\ WHERE\ id\ =\ OBJECT_ID\(N'(?<tablename>.+?)'$\ AND\ OBJECTPROPERTY$id,\ N'IsUserTable'$\ =\ 1\)$

Here's the same thing, in SQL 2005 syntax:

^IF\ NOT\ EXISTS\ $SELECT\ \*\ FROM\ sys\.objects\ WHERE\ object_id\ =\ OBJECT_ID\(N'\[dbo]\.\[(?<tablename>.+?)]'$\ AND\ type\ in\ $N'U'$\)$

We're using a named capture group so we can use it in the replacement. Then we replace either with the following:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'${TABLENAME}' AND TABLE_TYPE = 'BASE TABLE')

I'm resisting the urge to write a utility to handle the script conversion since I need to be working on my book. Here's a rough outline for a console application that would read the console input, convert table existence checks to use INFORMATION_SCHEMA, and write the result to the console output. The reason for using console input and output is to allow piping SqlPubWiz output through it. It's not tested, and it doesn't include checks for views, procedures, and indices; they'd follow the exact same pattern. This isn't the optimal way to do regular expression replacements - given time, I'd do these with MatchEvaluators.

using System;
using System.Text;
using System.Text.RegularExpressions;

class SqlScriptCleaner
{
    static void Main(string[] args)
    {
        string script = System.Console.In.ToString();
        Regex.Replace(script,
            @"^IF\ EXISTS\ \(SELECT\ \*\ FROM\ dbo\.sysobjects\ WHERE\ id\ =\ OBJECT_ID\(N'(?<tablename>.+?)'\)\ AND\ OBJECTPROPERTY\(id,\ N'IsUserTable'\)\ =\ 1\)$",
            @"IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'${TABLENAME}' AND TABLE_TYPE = 'BASE TABLE')");
        Regex.Replace(script,
            @"^IF\ NOT\ EXISTS\ \(SELECT\ \*\ FROM\ sys\.objects\ WHERE\ object_id\ =\ OBJECT_ID\(N'\[dbo]\.\[(?<tablename>.+?)]'\)\ AND\ type\ in\ \(N'U'\)\)$",
            @"IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'${TABLENAME}' AND TABLE_TYPE = 'BASE TABLE')");
        System.Console.Out.Write(script);
    }
}

Come to think of it, this (theoretical) utility could also take care of my other gripe with SqlPubWiz, which is that it doesn't allow you to select which objects to script. It's inelegant, but it would work - let SqlPubWiz script everything, then clean the output to remove scripts for tables we don't want included.

A more practical workaround: Regex Replacements in SSMS

For now, though, I'm fine with just handling this manually in an editor that supports Regular Expression replacement. Visual Studio does it, as do most of the top Notepad replacement tools (Notepad++, Notepad2, etc.). Now that SQL Server Management Studio uses the Visual Studio IDE, you can do Regex replacements in SSMS as well.

Generate your script and open it in a new query window - if you're just scripting schema, you can right click on the database, select Tasks, and pick the Generate Scripts task (selecting a new query window as the output method). If you're scripting schema and data with SqlPubWiz, output to a SQL file and open it in SSMS.
Bring up the Quick Replace dialog (Edit -> Find / Replace -> Quick Replace, or just hit ctrl-h).
Expand the "Find Options" section and check the "Use" checkbox, then ensure "Regular expressions" is selected.
Enter the correct Regex find and replacements as shown below. Note that Visual Studio has its own bastardized Regex syntax, so you'll need to tweak the regexes.
Revised Find expression: ^IF\ NOT\ EXISTS\ $SELECT\ \*\ FROM\ sys\.objects\ WHERE\ object_id\ =\ OBJECT_ID\(N'\[dbo\]\.\[{.+}\]'$\ AND\ type\ in\ $N'U'$\)
Revised Replace expression: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'\1' AND TABLE_TYPE = 'BASE TABLE')
Hit the Replace All button.
Make sure the script still compiles by hitting CTRL-F5.

[1] Jamie Thomson reported one issue with INFORMATION_SCHEMA behavior changing between 2000 and 2005 due to an implementation bug in INFORMATION_SCHEMA.COLUMNS.NUMERIC.PRECISION for smalldatetime and datetime.

[tip] Use the Database Publishing Wizard to script your table data

Jon Galloway — Sat, 30 Dec 2006 07:53:06 GMT

Jeff Atwood recently asked "Is your database under version control?" Well, is it? It's not as easy as it sounds. Until now, there wasn't a good, scriptable solution to the problem. I'll run through a few of the options I've looked at in the past, but let's jump to to the punchline:

The Database Publishing Wizard can script data as schema, and it's got a command line mode.

The Problem

Most of the applications I work on run against SQL Server databases. Jeff hit the nail on the head when he said that your database is an integral part of your application. I'll take it a step further and say that your data is often an integral part of your application. Many databases include signifcant amounts of reference data which are as much a part of your software release as your config files - for instance, a DotNetNuke site without the correct version of the database is just plain shot.

I'm not talking about database backups, which are an important part of your production environment. Database backups are a more appropriate solution to safeguarding your transactional data, but they don't integrate at all with the rest of your application's version control.

I previously posted a script to check a database (MDF) file into Subversion. It works, but it's far from ideal. It's just a binary file, so while you've got history, you can't track differences. No good.

I've used SQL-Scripter, GenDbScripts (had to fix a few bugs), and Enisey Data Scripter. They all work, but none of them was scriptable.

I set out to see how easy it would be to write something in PowerSMO that would script out the table schema and data. It should have been easy, since SMO has a special "execution mode" that records all commands and outputs the resulting script. Heck, David Hayden has one post showing C# sample code to script a database schema, and another using SMO's Transfer.TransferData() to copy all data from one database to another. No problem, I'll just mash them together to enable CaptureSql and TransferData, and I'm set...

Yeah, not really. I converted it to a PowerShell script. It worked great... but it only scripted the schema, not the data. It looks like TransferData doesn't go through script, so it's not picked up by that cool CaptureSql thing. (In case you care, that PowerShell script is in the footnotes.)

The Solution (well, pretty much...)

Then Scott Guthrie mentioned the new Database Publishing Wizard on an extensive post on deploying a SQL database to a remote hosting environment. Scott's post was pretty beefy and covered the SQL Server Hosting Toolkit, so you might have missed the beautiful screenshots below the fold. The Database Publishing Wizard makes it easy to script your schema and data from within Visual Studio:

The more exciting thing to me is that the Database Publishing Wizard can be run from the commandline:

sqlpubwiz script ( -C connection_string | -d local_database_name ) (output_file) [switches]

switches:

 Output options:

  [-f] :                       Overwrite existing files
  [-noschemaqualify] :         Output script does not qualify object names with
                             schema name.
  [-schemaonly] :              Only script schema
  [-dataonly] :                Only script data
  [-targetserver] :            Specifies what version of SQL Server the script
                             should target.  Valid versions are "2000", "2005".
                             Default Value: 2005
  [-q] :                       Suppress output
  [-nodropexisting] :           Default Value: False

 Local connection related:

  [-C connection_string] :     Connection string
  [-d local_database_name] :   Database name to script
  [-U local_user_name] :       Username
  [-P local_password] :        Password
  [-S local_server_name] :     Server name

Perfect!!!

Oops, we're missing two things before my quest is ended:

We need an option to specify which tables to script. I know that complicates things a bit, but we gotta have it. Maybe a parameter that points to a text file containing a list of tables to be scripted. That's important because we want to script and version our reference data, but we don't want to script our transactional data for a lot of reasons (most importantly we don't want to overwrite transactional data, but also the transactional data will usually be the most subject to large growth).
Why do the drop scripts need to check sys.objects for SQL 2005 databases and sysobjects for SQL 2000 databases? Why not use the INFORMATION_SCHEMA views so the same script works on both? That's the subject of another post, I guess...

Oh, and that PowerSMO I mentioned. It'll script the schema, but not the data:

$databaseToScript = "AdventureWorks"
$serverName = "(local)\SQL2005"
#USES POWERSMO - http://www.pluralsight.com/dan/samples/PowerSMO.zip

#SETUP VOODOO
$executionPolicy = get-ExecutionPolicy 
set-ExecutionPolicy "Unrestricted"
.\InitPowerSMO.ps1
cls

#CONNECT TO DATABASE
$server = SMO_Server $serverName
$server.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]"CaptureSql" #ExecuteAndCaptureSql
$database = $server.Databases[$databaseToScript]

$t = SMO_Transfer($database)
$t.CopyAllObjects = $true
$t.DropDestinationObjectsFirst = $true
$t.CopySchema = $true
$t.CopyData = $true #Doesn't get scripted
$t.DestinationServer = $serverName
$t.DestinationDatabase = "just_a_dummy_value"
$t.Options.IncludeIfNotExists = $true
$t.CreateTargetDatabase = $true

#Doesn't help
#$t.TransferData()

"Generating script"
$commands = $t.ScriptTransfer()
"Writing to file"
$scriptFile = "{0}_Script.sql" -f $databaseToScript
$commands | out-File $scriptFile
"Done"

#CLEANUP
$server = ''
set-ExecutionPolicy -executionPolicy $executionPolicy

SQL Server recovery model selection (or, what's this 3GB LDF file?!)

Jon Galloway — Sun, 17 Dec 2006 09:22:00 GMT

SQL Server recovery models can be a bit tricky. By default, SQL Server databases are set to Full Recovery mode, which means they maintain sufficient logs to restore to any specific point in time. These logs can grow to several GB for a small, lightly used database over time. For a development workstation, especially if your database is under version control, I like to change the recovery mode to Simple (most cases) or Bulk-Logged at most. Then you can shrink the database, which will clear out the log file in Simple mode - you can do this in SQL Server Management Studio by right clicking on the database, selecting Shrink, then Database. Keep in mind that shrinking databases can be a little more complex that I've pointed out here, but that's not normally important on a developer workstation.

Now, that solves the problem for this one database, but it will crop again the next time you create a database. A good way to deal with this once and for all is to change the recovery mode of your Model database to Simple or Bulk Logged, since that setting will apply to all future databases you create. Just make sure to update the recovery mode when you database is deployed to a production environment.

This is just a simplistic solution for SQL Server running on a developer machine - see some more in depth information on the MS Support site.

Notes for 11/28 Talk - SMO and INFORMATION_SCHEMA

Jon Galloway — Sun, 03 Dec 2006 08:26:00 GMT

I spoke at the San Diego .NET User Group meetin on 11/28 on SQL Server Metadata (SMO and INFORMATION_SCHEMA). The complete notes should be available on the user group downloads page, but here are the rough notes:

Extract of the PowerPoint outline:

What is Metadata?
SMO

What is it?
Simple sample - C#
Data Dictionary Creator
PowerSMO

INFORMATION_SCHEMA views

What are they?
Sample SQL utility scripts
Unpivot example
SubSonic demo

References

SMO – David Hayden http://davidhayden.com/blog/dave/category/51.aspx
INFORMATION_SCHEMA http://haacked.com/archive/2006/07/05/BulletproofSqlChangeScriptsUsingINFORMATION_SCHEMAViews.aspx
PowerSMO – Dan Sullivan http://pluralsight.com/blogs/dan/
DataDictionaryCreator http://codeplex.com/datadictionary
SubSonic http://codeplex.com/actionpack

Here's my PowerSMO (SMO in PowerShell) script:

#POWERSMO DEMO
#USES POWERSMO - http://www.pluralsight.com/dan/samples/PowerSMO.zip

#SETUP VOODOO
$executionPolicy = get-ExecutionPolicy
set-ExecutionPolicy "Unrestricted"
.\InitPowerSMO.ps1
cls

#CONNECT TO DATABASE
$server = SMO_Server "(local)\SQL2005"
$server.ConnectionContext.SqlExecutionModes = 3 #ExecuteAndCaptureSql
$database = $server.Databases["AdventureWorks"]
$database.FileGroups[0].Files[0].FileName

#LIST TABLES
$database.Tables | %{$_.name}

#CREATE A NEW TABLE
$orders = SMO_Table $database "Orders"
$order_number = SMO_Column $orders "Order Number" (SMO_DataType "Int")
$orders.Columns.Add($order_number)
$customer_name = SMO_Column $orders "Customer Name" (SMO_DataType "Varchar")
$orders.Columns.Add($customer_name)
$value = SMO_Column $orders "Value" (SMO_DataType "Int")
$orders.Columns.Add($value)
$orders.Create()

#SHOW SCHEMA
$orders.Columns | format-Table Name, DataType -auto
$orders.Script()

#CHANGE SOME COLUMNS
$orders.Columns["Value"].DataType = (SMO_DataType "Money")
$orders.Alter()
$orders.Script()

#DROP A COLUMN, THEN THE TABLE
$orders.Columns["Customer Name"].Drop()
$orders.Drop()

cls
$server.ConnectionContext.CapturedSql.Text
#CLEANUP
$server = ''
set-ExecutionPolicy -executionPolicy $executionPolicy

Here's the code from my winform sample

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace SMO_Overview
{
    public partial class Main : Form
    {
        Database db;

        public Main()
        {
            InitializeComponent();
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            GetTables(connectionTextbox1.SqlConnectionString);
            Cursor.Current = Cursors.Default;
        }

        private void GetTables(string connectionString)
        {
            ddlTables.Items.Clear();
            SqlConnection connection = new SqlConnection(connectionString);
            Server server = new Server(new ServerConnection(connection));
            db = server.Databases[connection.Database];

            foreach (Table table in db.Tables)
            {
                if (!table.IsSystemObject)
                {
                    if(!string.IsNullOrEmpty(table.Schema))
                        ddlTables.Items.Add(table.Schema + "." + table.Name);
                    else
                        ddlTables.Items.Add(table.Name);
                }
            }
            if (ddlTables.Items.Count > 0)
                ddlTables.SelectedIndex = 0;
        }

        private void ddlTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                if (ddlTables.SelectedIndex >= 0)
                {
                    string[] tableSelection = ddlTables.Text.Split('.');
                    Table table = db.Tables[tableSelection[1],tableSelection[0]];
                    DataTable columnList = new DataTable();
                    columnList.Columns.Add("Column");
                    columnList.Columns.Add("Datatype");

                    foreach (Column column in table.Columns)
                    {
                        DataRow row = columnList.NewRow();
                        row["Column"] = column.Name;
                        row["Datatype"] = SmoUtil.GetDatatypeString(column);
                        columnList.Rows.Add(row);
                    }
                    dgvColumns.DataSource = columnList;
                }
            }
            catch { }
            Cursor.Current = Cursors.Default;
        }
    }
}

I previously posted most of the INFORMATION_SCHEMA SQL Scripts: Some of my favorite INFORMATION_SCHEMA utility queries

Thanks to Justin Angel, who sent me hundreds of slides about SQL Server 2005 for his upcoming talk on December 4th at the Israeli SQL Server Usergroup. I'm sorry I won't be able to see his talk, it looks like it will be incredible.

Speaking: 11/28 - San Diego .NET User Group

Jon Galloway — Sat, 25 Nov 2006 00:29:54 GMT

I'll be speaking at the San Diego .NET User Group on 11/28:

Jon has been putting some new and lesser used features in SQL Server to work to solve some tough business problems lately. He will review SQL Server Management Objects (SMO), INFORMATION_SCHEMA queries, and some of the T-SQL language enhancements; and then will show how he used them in several recent projects. We'll look at SMO code in a freeware tool Jon recently released, Data Dictionary Creator, as well as INFORMATION_SCHEMA queries used to drive code generation in the SubSonic Data Access Layer system.

I'm splitting the evening with Zoiner Tejada, who will be speaking on SSIS.

Data Dictionary Creator 1.2 is out on CodePlex

Jon Galloway — Wed, 15 Nov 2006 08:59:59 GMT

We just released Data Dictionary Creator version 1.2 to CodePlex. Version 1.2 adds a lot of great features:

Added documentation of tables as well as columns
Changed Excel export from HTML based to XMLSpreadsheet to support separate worksheets for Table and Column documentation
Improved error handling - detection of non-DBO logins, etc.
(UI) - tab reorganization to fit workflow a little better
(UI) - moved feedback and progress bars to statusbar for consistency
Support for SQL 2000 and 2005 export scripts (there were several breaking changes from 2000 to 2005)
Limited import functionality (SQL and XML)
Added an Installer

DDC is a nice utility to help you document your databases. Even if that kind of thing doesn't appeal to you, you might want to check out the code if you're at all interested in using SQL Server Managment Objects (SMO) or saving data to multiple formats (Word via WordML, Excel SpreadsheetML, HTML) using XSLT.

Thanks to Ben Griswold (a.k.a. JohnnyCoder) who made this release possible, or at least kept it from being really crappy. Ben's been cleaning up my coding messes at three different jobs over the past nine years, and I was very happy to have his help for this release. Thanks, Ben!

Let me know if you'd like to contribute, or if you've got any ideas for making DDC better (I think the next big feature to be added is an import from Excel, but what do I know?).

I set up a walkthrough on the DDC Codeplex site, check it out!

You didn't check it out, did you? I thought you might not. No matter, I'm copying it below:

Documenting databases is fun again!

No, not really. But a least it's not a complete waste of time. Unlike those data documents you've written in the past, this one lives with your database (in SQL Server Extended Properties) so it stays up to date. Follow along and we'll show you how easy it is to create useful documentation for that good old Northwind database...

Make a connection

The first step is to connect to a database. You can enter a connection string or click on the "..." button to view the standard data connection dialog.

Hit the ground running - get advanced!

You can breeze right by the next tab (Advanced Settings) if you'd like, but it's got some neat gizmos that could save you some time. The two big features:

Auto-Fill - this looks at you schema and adds descriptions for primary and foreign keys. You can customize the text these use.
Additional Properties - By default, you're only filling in a simple "Description" tab. If you'd like add other columns to your documentation, you're going to want to look at Additional Properties.

Document that DB

Did you think we were going to do it for you? Nope! But we'll help:

The user interface shows you column names and datatypes to help you remember what all those columns are used for.
The datagrid lets you move around pretty quickly
Your work is saved immediately, so you can work on it as time permits. Your edits are saved with the database.

Show off!

Now that you've documented your database, you can export it to share with others. There are two ways to share it:

Documentation formats (Word, Excel, HTML)
Importable formats (T-SQL, XML) - these let other users import your documentation into their copy of the database

Here's what the export looks like in Excel. Note that the Table and Column documentation are on separate worksheets. Feel free to pick your favorite AutoFormat to pretty this up.

Here's the HTML export:

The next two formats are importable. The T-SQL export can be executed via Query Analyzer / SSMS, or it can be loaded by DDC.

Here's the XML format. The only reasons you'd want to use this are to import into another copy of the database via DDC, or to export to another format. All file exports actually start with XML and go through an XSL transform. You can check out the XSL folder and modify any of these, or create your own.

Copy that documentation with Import

You can use the Import tab to load previously exported documentation. Browser for a T-SQL or XML export from DDC. We do a few checks to make sure the file was created by DDC, then load the documentation into the currently connected database.

Handling "GO" Separators in SQL Scripts - the easy way

Jon Galloway — Tue, 07 Nov 2006 09:32:00 GMT

If you've ever had to execute one or more SQL scripts from ADO.NET, you've likely run into the GO batch terminator issue. Any SQL script that does anything worthwhile has more than one batch, separated by a GO terminator. The problem is that "GO" isn't valid T-SQL, it's just a command used by the SQLCMD, OSQL and ISQL utilities that can also be used within Query Analyzer and the Query Editor window. If you try to execute T-SQL scripts with GO commands in them via ADO.NET SqlCommand.ExecuteNonQuery, you'll get an error that says something like:

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch

Until recently, there have been two ways to handle this problem - execute SQL scripts by shelling to OSQL, or splitting the script on GO separators and running them in sequence. Both solutions kind of worked, but SQL Server Management Objects (SMO) has a better solution for us: Server.ConnectionContext.ExecuteNonQuery(), which parses T-SQL statements and "gets" the GO statement as a batch separator. And the crowd goes wild!!!

I'm telling you, if you're doing anything with SQL Server from .NET code, you really have to look at SMO.

Here's a simple sample app that iterates SQL scripts in a directory and executes them with that fancy ConnectionContext.ExecuteNonQuery - the one that rocks a house party at the drop of a hat, while retaining the ability to beat a biter down with an aluminum bat:

using System;
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;

//Microsoft.SqlServer.Smo.dll
using Microsoft.SqlServer.Management.Smo;
//Microsoft.SqlServer.ConnectionInfo.dll
using Microsoft.SqlServer.Management.Common;

public class RunAllSqlSriptsInDirectory
{
    public static void Main()
    {
        string scriptDirectory = "c:\\temp\\sqltest\\";
        string sqlConnectionString = "Integrated Security=SSPI;" + 
            "Persist Security Info=True;Initial Catalog=Northwind;Data Source=(local)";
        DirectoryInfo di = new DirectoryInfo(scriptDirectory);
        FileInfo[] rgFiles = di.GetFiles("*.sql");
        foreach (FileInfo fi in rgFiles)
        {
            FileInfo fileInfo = new FileInfo(fi.FullName);
            string script = fileInfo.OpenText().ReadToEnd();
            SqlConnection connection = new SqlConnection(sqlConnectionString);
            Server server = new Server(new ServerConnection(connection));
            server.ConnectionContext.ExecuteNonQuery(script);
        }
    }
}

SQL Puzzle #1 - Answer

Jon Galloway — Thu, 02 Nov 2006 07:22:00 GMT

Here's my solution to the puzzle in my previous post. If you haven't seen the challenge, go back and read that first.

Are you reading this before trying to solve the puzzle on your own? Unacceptable! Your IP is being logged! C'mon, give it a try!

My Solution

I used 29 lines and 3 temp tables. It's reasonably straightforward - I set up a rank table (#temp), cross join it with the Student table to get ten rows for each student (a cartesian product), then join it to the grades table in order and select out the result.

The one little trick is the ranking in the selct for #temp3 - I used the GradeID as the tie-breaker if the student gets the same grade more than once. Without a tie-breaker, a duplicate score would result duplicating one Row number and skipping the next (e.g. 1,2,3,4,5,5,7,8,9,10).

How would you solve this?

use [test]
go

IF OBJECT_ID('tempdb..#temp') IS NOT NULL drop table #temp
create table #temp (Row int)
declare @counter int
set @counter = 0
while @counter < 10
begin
  set @counter = @counter + 1
  insert into #temp (Row) values (@counter)
end

IF OBJECT_ID('tempdb..#temp2') IS NOT NULL drop table #temp2
create table #temp2 (Row int, StudentID int)
insert into #temp2 select Row, StudentID from Student, #temp order by StudentID, Row

IF OBJECT_ID('tempdb..#temp3') IS NOT NULL drop table  #temp3
select *, (SELECT COUNT(*) FROM Grade pt2 WHERE pt2.Grade + (.001*pt2.GradeID) >= pt.Grade + (.001*pt.GradeID) AND pt2.StudentID = pt.StudentID) AS Row
into #temp3
from Grade pt order by StudentID, Grade desc

select a.Row, a.StudentID, s.FirstName, s.LastName, b.Grade from 
#temp2 a left join #temp3 b on a.StudentID = b.StudentID and a.Row = b.Row
inner join Student s on a.StudentID = s.StudentID

drop table #temp
drop table #temp2
drop table #temp3

SQL Puzzle #1

Jon Galloway — Thu, 02 Nov 2006 07:05:00 GMT

Background

I had to write a semi-interesting SQL query this past week and thought it might make for a fun SQL puzzle (for very small values of "fun").

I'm working on a bio-tech business intelligence application, but I simplified things way down to two tables in a SQL Server 2000 database: Student and Grade. The student may have any number of grades, and may of course may get the same grade several times.

StudentID	FirstName	LastName
1	Bill	Smith
2	Bobby	Brown
3	Derek	Zoolander

GradeID	StudentID	Grade
1	2	61
2	3	98
3	1	87
...	...	...
18	1	58
19	2	82
20	3	68

Task

Prepare a report that shows the top ten grades for each student. You must show exactly ten rows for each student, so if they have less than ten grades you should show a null. Remember that you need to handle the case where the same student gets the same score more than once (e.g.- in my results below, check out Bobby Brown's top two grades are both 82).

Row	StudentID	FirstName	LastName	Grade
1	1	Bill	Smith	94
2	1	Bill	Smith	87
3	1	Bill	Smith	82
4	1	Bill	Smith	67
5	1	Bill	Smith	62
6	1	Bill	Smith	58
7	1	Bill	Smith	NULL
8	1	Bill	Smith	NULL
9	1	Bill	Smith	NULL
10	1	Bill	Smith	NULL
1	2	Bobby	Brown	82
2	2	Bobby	Brown	82
3	2	Bobby	Brown	72
…	…	…	…	…
7	3	Derek	Zoolander	53
8	3	Derek	Zoolander	NULL
9	3	Derek	Zoolander	NULL
10	3	Derek	Zoolander	NULL

My answer will not use cursors and will run on standard SQL 2000 T-SQL, but if you'd like to submit answers using the SQL 2005 ranking functions, go for it. Post your answers as comments; my answer will be in the next post.

Here's a SQL script with some sample data. You'll need to create a "test" database to run this.

use [test]
go

if exists (select * from information_schema.tables where table_name = 'Grade') drop table Grade
if exists (select * from information_schema.tables where table_name = 'Student') drop table student

create table Student(
StudentID int identity(1,1) primary key,
FirstName varchar(50),
LastName varchar(50)
)

insert into student(firstname, lastname) values ('Bill','Smith')
insert into student(firstname, lastname) values ('Bobby','Brown')
insert into student(firstname, lastname) values ('Derek','Zoolander')

create table Grade(
GradeID int identity(1,1),
StudentID int,
Grade float
)
ALTER TABLE Grade WITH CHECK ADD CONSTRAINT FK_Grade_Student FOREIGN KEY([StudentID]) REFERENCES Student(StudentID)

declare @counter int
declare @grade int
set @counter = 0
while @counter < 20
begin
  set @counter = @counter + 1
  set @grade = cast(50*rand(cast(cast(newid() as binary(8)) as int)) as int) + 50
  insert into Grade (StudentID,Grade) values (@counter % 3 + 1,@grade)
end
select * from Grade
go

Showing a Connection String prompt in a WinForm application

Jon Galloway — Mon, 30 Oct 2006 05:45:03 GMT

When I was putting together the Data Dictionary Creator program, I needed to allow users to input a connection string. A lot of winform applications that require data connections give you a textbox and tell you to figure it out yourself, but I really wanted to show a prompt that let you test your connections, select databases on a server, etc.

I was surprised that I couldn't find a nice, free, winform control to build a connection string. I found something on CodeProject with a smart solution - it pops up the standard Data Link Properties dialog. The code mostly worked, but had some problems (passwords were lost if user didn't check "Allow Saving Password", problems with integrated security). I found some general suggestions in the comments and newsgroup postings and threw in some exception handling, and came up with something that worked pretty well for me. It's a little trickier than it looks, since setting certain properties in just the right order shifts the dialog into modes which hide tabs and stuff.

This requires references to a few COM objects:

%PROGRAMFILES%\Microsoft.NET\Primary Interop Assemblies\adodb.dll
%PROGRAMFILES%\Common Files\System\Ole DB\OLEDB32.DLL

I found an article about using CreateObject or GetTypeByProgID to avoid the interop references, I think I'd rather ship the interop stubs than worry about dynamic object calls failing when I'm shipping an application, but it's interesting.

The next step for this would be bundle this up in a user control (textbox with an ellipses button to launch the dialog, maybe a balloon tip to show connection errors), but I'm not sure I'll get to that any time soon. Here's the code as a simple function that pops the dialog and returns the selected connection string:

/// <summary>
/// Displays a Connection String Builder (DataLinks) dialog.
/// 
/// Credits:
/// http://www.codeproject.com/cs/database/DataLinks.asp
/// http://www.codeproject.com/cs/database/DataLinks.asp?df=100&forumid=33457&select=1560237#xx1560237xx
/// 
/// Required COM references:
/// %PROGRAMFILES%\Microsoft.NET\Primary Interop Assemblies\adodb.dll
/// %PROGRAMFILES%\Common Files\System\Ole DB\OLEDB32.DLL
/// </summary>
/// <param name="currentConnectionString">Previous database connection string</param>
/// <returns>Selected connection string</returns>
private string PromptForConnectionString(string currentConnectionString)
{
    MSDASC.DataLinks dataLinks = new MSDASC.DataLinksClass();
    ADODB.Connection dialogConnection;
    string generatedConnectionString = string.Empty;

    if (currentConnectionString == String.Empty)
    {
        dialogConnection = (ADODB.Connection)dataLinks.PromptNew();
        generatedConnectionString = dialogConnection.ConnectionString.ToString();
    }
    else
    {
        dialogConnection = new ADODB.Connection();
        dialogConnection.Provider = "SQLOLEDB.1";
        ADODB.Property persistProperty = dialogConnection.Properties["Persist Security Info"];
        persistProperty.Value = true;

        dialogConnection.ConnectionString = currentConnectionString;
        dataLinks = new MSDASC.DataLinks();

        object objConn = dialogConnection;
        if (dataLinks.PromptEdit(ref objConn))
        {
            generatedConnectionString = dialogConnection.ConnectionString.ToString();
        }
    }
    generatedConnectionString = generatedConnectionString.Replace("Provider=SQLOLEDB.1;", string.Empty);
    if (
            !generatedConnectionString.Contains("Integrated Security=SSPI")
            && !generatedConnectionString.Contains("Trusted_Connection=True")
            && !generatedConnectionString.Contains("Password=")
            && !generatedConnectionString.Contains("Pwd=")
        )
        if(dialogConnection.Properties["Password"] != null)
            generatedConnectionString += ";Password=" + dialogConnection.Properties["Password"].Value.ToString();

    return generatedConnectionString;
}

Reading the SQL 2005 instance and installation directory from a DOS batch file

Jon Galloway — Sun, 29 Oct 2006 05:19:00 GMT

SQL 2005 installs each instance to a numbered directory based on the order installed: MSSQL.1, MSSQL.2, etc. That makes scripting a little tricky. Fortunately, you can find them in the registry.

The install path for the SQL 2005 database server is here:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2005\Setup\SQLPath

You can look up specific instances in the the Instance Names registry node. For instance, to find the SQL2005 instance, you'd look at this key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\SQL2005

Looking up registry values in a DOS batch file is a little tricky. Fortunately, Robert VanderWoude has a great tip for reading registry values via creative use of the FOR /F command.

So, with all that out of the way, we can read the SQL Server instance with this line:

FOR /F "tokens=2* delims=     " %%A IN ('REG QUERY "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL" /v SQL2005') DO SET SQLINSTANCE=%%B

and the SQL Server base installation directory with this:

FOR /F "tokens=2* delims=     " %%A IN ('REG QUERY "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2005\Setup" /v SQLPath') DO SET DATADIRECTORY=%%B\Data

Why would you want to read that kind of stuff in a batch file? Well, to write a Batch files to check SQL 2005 (MDF) files in and out of Subversion source control, or course.