In the last post (Demystifying T-SQL Subqueries – Part I) we looked at how to use scalar subqueries. Let’s continue our subquery adventure.

As with Part I, the following T-SQL query samples are using the Chinook database.

Subqueries which return a single list of values

Read the rest of this entry »

When I teach SQL Server Development/T-SQL courses, subqueries is usually one of those topics that students find confusing. Usually it’s a head scratcher, although there are some who just get it.

So let’s try to demystify subqueries.

Very simply put, a subquery is just a query within a query. It’s a SELECT inside another SELECT.

Now let’s break it down. Let’s start with understanding what a query gives you first.

Understanding results of regular queries

Before we can understand subqueries, we need to understand first the different results we get from regular queries. A regular query always results in a dataset. It gives you the following variations of results:

Read the rest of this entry »

Just another handy code snippet for generating dates for date dimensions in your data warehouse.

I am providing just the select statement here, but to generate (massive) date records, just create and set your start and end date variables, and enclose your insert and the code below in your WHILE loop.

Read the rest of this entry »

Need to check if your tables in 2 different servers are out-of-sync? SQL Server comes with a command line tool that does the job. It’s called tablediff.exe (read up BOL entry for this nifty tool).

By default, you can find this in the COM directory of your SQL Server install folder.

In SQL Server 2005 by default it will be in:
C:Program FilesMicrosoft SQL Server90

In SQL Server 2008 / SQL Server 2008 R2 by default it will be in:
C:Program FilesMicrosoft SQL Server100

These are the switches you can specify for the tool:

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
C:UsersAdministrator>cd C:Program FilesMicrosoft SQL Server100COM
C:Program FilesMicrosoft SQL Server100COM>tablediff.exe /?
Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft Corporation
User-specified agent parameter values:
/?
 Replication Diff Tool Command Line Options
        usage: tablediff
          -- Source Options --
 -sourceserver          Source Host
 -sourcedatabase        Source Database
 -sourceschema          Source Schema Name
 -sourcetable           Source Table or View
 -sourceuser            Source Login
 -sourcepassword        Source Password
 -sourcelocked          Lock the source table/view durring tablediff
          -- Destination Options --
 -destinationserver     Destination Host
 -destinationdatabase   Destination Database
 -destinationschema     Destination Schema Name
 -destinationtable      Destination Table or View
 -destinationuser       Destination Login
 -destinationpassword   Destination Password
 -destinationlocked     Lock the destination table/view durring tablediff
          -- Misc Options --
 -t             Timeout
 -c             Column Level Diff
 -f             Generate Fix SQL (You may also specify a file name and path)
 -q             Quick Row Count
 -et            Specify a table to put the errors into
 -dt            Drop the error table if it exists
 -o             Output file
 -b             Number of bytes to read for blob data types
 -strict        Strict compare of source and destination schema
 -rc            Number of retries
 -ri            Retry interval

Here is an example usage. Note this has to be all in one line at the Command Prompt (I just separated them out into different lines for clarity):
Read the rest of this entry »

Excellent Free Ebooks from RedGate

SQL Server eBook Selection
1. Protecting SQL Server Data by John Magnabosco
2. The Art of High Performance SQL by Grant Fritchey
3. The Art of XSD by Jacob Sebastian
4. Two Minute SQL Server Stumpers Vol. 5

DBA Ebooks Bundle
1. Mastering SQL Server Profiler
2. Brad’s Sure Guide to SQL Server 2008
3. Brad McGehee’s DBA Best Practices
4. How to Become an Exceptional DBA (2nd edition)
5. SQL Server Tacklebox

The Best of Simple Talk .NET
1. The Best of Simple Talk .NET – Jit N’ Run vol.2

You can download them from this link

Jacob Sebastian is a SQL Server XML Guru!

Check out his collection for XQUery Labs. This is the first 12 of his series, and right now he has 43 and counting:

XQuery Sample Scripts

* XQuery Lab 1 – Transforming rows to columns
* XQuery Lab 2 – An example using OUTER APPLY
* XQuery Lab 3 – Filtering specific nodes
* XQuery Lab 4 – Joining XML Nodes with a Relational Table
* XQuery Lab 5 – Working with Namespaces
* XQuery Lab 6 – Processing Header-Detail information
* XQuery Lab 7 – Extracting a comma separated list of values
* XQuery Lab 8 – How to update the attribute value of an XML variable?
* XQuery Lab 9 – How to delete an attribute from an XML variable?
* XQuery Lab 10 – How to insert an attribute to an XML variable
* XQuery Lab 11 – How to insert an element to an XML variable
* XQuery Lab 12 – Different ways of reading values from an XML variable

Jacob Sebastian has also released a free ebook via RedGate – The Art of XSD – SQL Server XML Schema Collections

Check it out, all 483 pages!

I have posted previously 3 different ways of searching for SQL Server Objects (How to Search for Columns in SQL Server ).

Here’s a fourth one!

Check out how powerful and flexible PowerShell is when you need to look for a database object. In the script below, I only search databases, tables, columns, and indexes. But in reality, really, sky is the limit!
Read the rest of this entry »

Here is a simple script to audit your SQL Server Instance Properties.

Note that the property names are not hardcoded. We query each of these properties, and use those to display the property values.

If you prefer, you can also query directly each of the properties of an instance. If this is the case, just specify your server object and then the property name. For example:

$serverObject.BackupDirectory

The list of properties can be found at the end of the post.
Read the rest of this entry »