Dan Guzman Blog

Beware Job Recruiting SCAM

I’ve had multiple reports of a scam where persons have used my identity to entice IT professionals into applying for positions they have no business recruiting for. The scammers later ask for a PayPal payment for their services. Read more →

Secrets of Foreign Key Index Binding

You might be surprised to learn that foreign keys bind to physical indexes when they are created. Furthermore, a foreign key does not necessarily bind to the primary key index of the referenced table; SQL Server allows a foreign key to refer to any column(s) that are guaranteed to be unique as enforced by a primary key constraint, unique constraint or unique index. Read more →

RIP OLE DB

I was very surprised when Microsoft announced deprecation of OLE DB provider for SQL Server data access last week on the Data Access Blog and MSDN Forums Announcement. The next release of SQL Server, code-named “Denali”, will be the last to ship a new SQL Server Native Client OLE DB provider. Read more →

Denali CTP3: THROW Statement

Not to mince words, T-SQL error handling has historically sucked. I’m excited that SQL Server “Denali” CTP3 (a.k.a. SQL11) includes a long-awaited THROW statement that I hope to see in the final release. Read more →

Internal SQL Server Database Version Numbers

A database created by a more recent version of SQL Server cannot be attached or restored to an earlier version. This restriction is simply because an older version cannot know about file format changes that were introduced in the newer release. Read more →

SQL Server Connection Strings

This is the first of a series of posts on SQL Server connection strings. I don’t think connection strings are all that complicated but I often see developers have problems because they simply cloned an existing connection string (or found one on the internet) and tweaked it for the task at hand without really understanding what the keywords and values mean. Read more →

Move a Partition to a Different File Group Efficiently

SQL Server table partitioning can reduce storage costs associated with large tables while maintaining performance SLAs.Table partitioning, available in Enterprise and above SKUs, allows you to keep frequently used current data on fast storage while storing infrequently accessed older data on slower, less expensive storage. Read more →

Stairway Series on SQLServerCentral.com

SQLServerCentral.com launched a new Stairway content seriestoday, targeting specific areas of SQL Server.Each Stairway includes a series of up to 12 levels focused on a specific SQL Server topic. The goal is to guide DBAs and developers with little or no understanding of a subject through a sequence of tutorials in order to quickly gain the knowledge one needs to use a SQL Server feature confidently in a production environment. Read more →

Calendar Table and Date/Time Functions

I frequently see questions in the forums and newsgroups about how to best query date/time data and perform date manipulation.Let me first say that a permanent calendar table that materializes commonly used DATEPART values along with time periods you frequently use is invaluable. Read more →

Secret of SQL Trace Duration Column

Why would a trace of long-running queries not show all queries that exceeded the specified duration filter?We have a server-side SQL Trace that includes RPC:Completed and SQL:BatchCompleted events with a filter on Duration >= 100000. Read more →

Ad-Hoc Rollup by date/time Interval

I often use aggregate queries to rollup data by an arbitrary date/time interval.I'll share some techniques that I use to accomplish the task in case you find these useful, using the same table below: Read more →

Collation Hell (Part 3)

In this final post of my Collation Hell series, I'll discuss techniques to change a SQL Server instance collation along with the collation of all databases and columns.The objective is to ensure the standard collation is used throughout the entire SQL Server instance. Read more →

Collation Hell (Part 2)

In my last post, I discussed why one should avoid a mixed collation environment and how to choose the right collation for your environment.This post focuses on planning a collation change. Read more →

Collation Hell (Part 1)

I inherited a mixed collation environment with more collations than I can count on one hand.The different collations require workarounds to avoid "cannot resolve collation conflict" errors and those workarounds kill performance due to non-sargableexpressions. Read more →

Forced Parameterization: A Turbo Button?

I never had the need to turn on the PARAMETERIZATION FORCED database option until this week.We pretty much use only stored procedures for our internal applications so the execution plans are almost always in cache and reused. Read more →

Restore Database Stored Procedure

A user in the SQL Server public newsgroups asked about how to restore a database with many files and rename during the process: I am restoring a database onto another server with different drive sizes and mappings. Read more →

Database Mail Configuration

I recently had to setup Database Mail on dozens of SQL Server instances.Rather than perform this tedious task using the SSMS GUI, I developed a script that saved me a lot of time which I'm sharing here. Read more →

Not Before Service Pack 1

In case you haven't yet heard, Microsoft SQL Server 2008 service pack 1 was released on April 7.This milestone is especially significant for those of you who could not previously deploy the latest SQL Server release because your organization has a "not before the first service pack" policy. Read more →

Low-Hanging Fruit of Sargable Expressions

Can you spot the performance problem with the two queries below? These queries both illustrate a common and nasty performance problem that robs CPU cycles and consumes I/O bandwidth. CREATETABLE dbo. Read more →

QUOTED_IDENTIFIERS and ANSI_NULLS ON

I suggest that one always turn on both the QUOTED_IDENTIFIERS and ANSI_NULLS session settings.Not only do these settings provide ANSI-standard behavior, these must be turned on in order to use features like indexed views, indexes on computed columns and query notifications. Read more →