For those that know me I have a terribly short attention span for operating systems.  I'm constantly playing with versions or configurations.  I recently installed Windows Server 2008 (WS 2008 from here) on my Dell Latitude D620 with tremendous results.

It all started innocently enough, I was reading a blog one night and saw rave reviews about performance from WS 2008.  I did a little more searching and found several Microsoft employee blogs saying the same thing.

I knew I wanted to play around with WS 2008 as a SQL Server platform so I thought "why not?".  Fortunately for me I work for a company that lets me change my OS when I choose(but for the record, I ran it by Tom anyway).  I installed in what seemed like 15 minutes and was off to the races. 

I was especially happy with what felt like a reversal in Windows OS paradigm.  After installation I had an extremely small footprint, requiring me to go turn on the services/feature I wanted.  What a concept!

There are a few configuration settings that have to be changed.  They are itemized nicely here.  Once I enabled the "Desktop Experience" I had a Vista looking UI with super fast performance.  I am running the 64 bit version of the OS and haven't had any issues with drivers or applications.  As a side note, I don't do any gaming on this laptop (or at all really) so your mileage may vary on that topic.

After a week or so of solid development on this OS I am a huge fan.  I realize I am probably running the most expensive laptop OS imaginable but if results are the name of the game check out Windows Server 2008.

If you are headed to the MS launch event tomorrow make sure you stop by the Magenic kiosk and say hello.  I will be splitting my time between our kiosk and the Ask the Experts area.

It should be a great day, the presentations are always top notch and the giveaways don't hurt either.  ;)

Check the site for more details about the event here.

After a bit of delay I'm back with the last installment of SQL Basics. I planned to write this blog several times over the last few days but to be honest I had other interests. My daughter said her first words this week, which just happened to be "dada", and has wanted to play all evening. How do you say no to that?

So today I'm talking about identity columns and how to find them in your database. I'm not going to get into the debate over whether or not you should use identity columns, I'm just pointing you to where they are. :)

If you read the last two posts you will recognize that we are headed down similar paths for finding identity columns. We will be focusing on a couple of the catalog views as well as making use of a system function. For the purposes of this blog I'm returning the following detail from each query:

• Table name
• Column name
• Data type
• Current identity value

Option 1 is sys.identity_columns, a catalog view returning a row for each object with an identity column. This view was introduced in SQL 2005 and inherits from the sys.columns catalog view. The syntax is:

Option 2 is sys.columns, a catalog view returning a row for all objects that have columns. The syntax for this view is:

Option 3 is sys.syscolumns, a compatibility view returning a row for each table, view, and stored procedure in your database. The syntax for this view is:

Option 4 makes use of the OBJECTPROPERTY system function. This function returns a wealth of information about objects. Note that the scope of this function is database specific. The one drawback to this option is that the return of the column name and data type are not easily done without a join to one of the objects above, which would defeat the point. But if you are only looking for tables that contain identity columns this is a good option. Here is the syntax:

So there are four ways to get at identity columns in your database. I hope this series of posts has been helpful. If there are other topics you would like to see drop me an email, otherwise we headed back to SQL 2008 in anticipation of next week's launch event here in Atlanta.

-W

We are in part two of our four part series around basic operations in TSQL.  Today we will be finding tables based on certain criteria.  Here are a couple I will show:

• Finding table names matching a string
• Finding tables containing a certain data type
• Excluding system objects from queries for tables

The first item is pretty straightforward and mirrors our prior discussion on columns.  I will show you four ways to query for a list of tables matching a string.

Option 1 uses the sys.tables catalog view.  This view returns a row for each table object, currently user tables only.  The syntax is:

SELECT *
FROM sys.tables
WHERE name LIKE 'Product%';

Option 2 uses the sys.objects catalog view.  This view returns a row for each user defined object in a database.  The syntax is:

SELECT * 
FROM sys.objects
WHERE type = 'U'
    AND name LIKE 'Product%';

Option 3 uses the sys.sysobjects compatibility view.  This view contains one row for each object in a database.  This view is created for backward compatibility, so its future is not guaranteed.  The syntax is:

SELECT *
FROM sys.sysobjects
WHERE type = 'U'
    AND name LIKE 'Product%';

Option 4 uses the INFORMATION_SCHEMA.TABLES view.  This view returns one row for each table in the current database that can be accessed by the current user in the current database.  The syntax is:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'Product%';

To find tables containing certain data types we have two options.  The sys.tables, sys.objects, or sys.sysobjects views could be used in the manner below (swapping out [object_id] for [id] if using sys.sysobjects). 

SELECT t.name AS tableName
    ,c.name AS columnName
    ,ty.name AS dataType
FROM sys.tables AS t
    INNER JOIN sys.columns AS c ON c.[object_id] = t.[object_id]
    INNER JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id
WHERE ty.name IN('varchar', 'nvarchar');

The same thought could be expressed a little differently against INFORMATION_SCHEMA.  The syntax is:

SELECT *
FROM INFORMATION_SCHEMA.TABLES AS T
    INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C ON C.TABLE_NAME = T.TABLE_NAME
WHERE C.DATA_TYPE IN('varchar', 'nvarchar');

For certain searches for tables you might want to exclude the system objects stored in meta data.  One method for this I have already mentioned, indirectly, is the sys.tables compatibility view.  For now it only contains user defined objects.  It does, however, contain a very important column named is_ms_shipped.  This column also exists in sys.objects and allows us to effectively filter from those tables.  Your syntax would simply be:

SELECT *
FROM sys.tables
WHERE is_ms_shipped = 0;

or

SELECT *
FROM sys.objects
WHERE is_ms_shipped = 0;

An additional option is to make use of the OBJECTPROPERTY function.  This would allow us to use sys.sysobjects as well.  The syntax would be:

SELECT *
FROM sys.sysobjects
WHERE type = 'U'
    AND OBJECTPROPERTY(id, 'IsMSShipped') = 0;

Up next, identity columns.  I hope this is helpful for everyone, if not at least it will be over quickly.  :)

I have decided to step away from the SQL 2008 CTP for a bit and post a couple items that keep coming up.  I have been spending more time in the MSDN Transact SQL forums and have noticed a consistent stream of questions around some basic operations with transact SQL.  After a conversation with a client this week that touched on the same subject  I thought "I smell a blog series".  :)

So I have decided to post a blog a day this week on what I'm calling "SQL Basics".  I will be covering the following items:

• Finding a given column by name
• Finding tables based on a certain criteria (i.e. name, containing certain data types, etc.)
• Finding identity columns
• String concatenation tricks

So with those topics in mind, let's get rolling.  Today I will show you four ways to find a given column in your database by name.

Option 1 uses the sys.columns catalog view.  This view returns a row for each column of an object that has columns, these include:

• Table valued assembly functions
• Inline table-valued functions
• Internal tables
• System tables
• Table-valued SQL functions
• User tables
• Views

The syntax is:

--Option 1
SELECT OBJECT_NAME(object_id) AS objectName
    ,name AS columnName
FROM sys.columns
WHERE name = 'ProductID';

Option 2 uses the sys.syscolumns compatibility view.  This view returns a row for every column in every table and view, as well as a row for each parameter in a stored procedure in the database.  This view is created for backward compatibility, so its future is not guaranteed.

The syntax is:

--Option 2
SELECT OBJECT_NAME(id) AS objectName
    ,name AS columnName
FROM sys.syscolumns
WHERE name = 'ProductID';

Option 3 utilizes the sys.all_columns object catalog view.  This view shows the columns of all user-defined and system objects.

The syntax is:

--Option 3
SELECT OBJECT_NAME(object_id) AS objectName
    ,name AS columnName
FROM sys.all_columns AS ac
WHERE name = 'ProductID';

Option 4 uses the the INFORMATION_SCHEMA.COLUMNS view.  This view returns one row for each column that can be accessed by the current user in the current database.

The syntax is:

--Option 4
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'ProductID';

So there are four ways to get around in meta data when you need to do a column search.  Take your time to expand the queries provided and notice the wealth of information provided to you in the various views.  Happy hunting!

Next week marks the end of mainstream support for SQL Server 2000 SP4.  I have been really focused on SQL Server 2008 and it's new features so I kept having the "oh yeah" moment when it came to end of support for this version.

I am still surprised by the number of clients I run across that are still using SQL 2000 in production (some with very little thought to post-support life).  At the same time, it seems most of those customers are skipping SQL 2005 and going straight to SQL 2008.  It will be interesting to see how this affects many business as they will have given themselves quite a learning curve.  Incorporating the changes brought about by SQL 2005 was daunting for many shops.  Trying to roll two revs worth of into an environment will certainly be a challenge.  That said, I still believe there are enough compelling features in SQL 2008 to make that jump.

If you're reading this and the thought of a migration makes you nervous contact us at Magenic.  We've been preparing for this day for a while and can help guide your business to a successful implementation.

-W

Today I'm looking at one of my favorite SQL Server 2008 features, Change Data Capture (CDC from here).  CDC collects insert, update, and delete activity against a SQL Server table and stores the details of the changes in a relational format.  CDC will be available in the Enterprise, Developer, and Evaluation editions.

The steps for configuring CDC on your instance require sysadmin fixed server role permissions.

First, we need to enable CDC on our database.  To do so we issue the following call to sp_cdc_enable_db:

USE ExampleCode;
GO

--Activate change data capture at database level
EXECUTE sys.sp_cdc_enable_db;

To check any database for CDC activation we can query the sys.databases catalog view.

--Check CDC activation for databases
SELECT name, is_cdc_enabled
FROM sys.databases
WHERE [name] NOT IN ('master', 'tempdb', 'model', 'msdb');

Notice that I'm excluding the system databases -- these are not supported for CDC.

In my database I have an existing table named Customer with the following schema:

CREATE TABLE dbo.Customer
(
CustomerID          INT          IDENTITY(1,1) NOT NULL
,CustomerName       VARCHAR(100) NOT NULL
,DoingBusinessAs    VARCHAR(100) SPARSE  NULL
,CustomerTypeID     SMALLINT     NOT NULL
,CreatedOn          DATE         NOT NULL
);

We enable CDC on the Customer table with the following call to sp_cdc_enable_table:

--Activate change data capture at table level
EXECUTE sys.sp_cdc_enable_table 
    @source_schema = 'dbo'
    ,@source_name = 'Customer'
    ,@role_name = 'cdc_examplecode';

To check any table for CDC activation we can query the sys.tables catalog view.

--Check CDC activation for our table
SELECT name, is_tracked_by_cdc
FROM sys.tables 
WHERE name = 'Customer';

If we have a look around our database we will notice several objects have been created.  First, we will find a list of new System Tables with the cdc schema.

Notice that a table has been created matching the schema of our referenced table, Customer.  If you open the Security tab you will also notice a new role under Database Roles, cdc_examplecode (which we referenced in our procedure call).

Now the real fun begins.  We will insert and update some data in our Customer table, as we would do in a normal database application.

--Insert some data
INSERT dbo.Customer (CustomerName, DoingBusinessAs, CustomerTypeID, CreatedOn)
VALUES ('ExampleCode', 'Blog Example Customer', 1, GETDATE());

--Update some data
UPDATE dbo.Customer
SET DoingBusinessAs = 'Updated Blog Example Customer'
WHERE CustomerName = 'ExampleCode';

To view the effects of our DML changes we can run the following query:

--View the effects
SELECT CASE
        WHEN __$operation = 1 THEN 'Delete'
        WHEN __$operation = 2 THEN 'Insert'
        WHEN __$operation = 3 THEN 'Update (before image)'
        WHEN __$operation = 4 THEN 'Update (after image)'
    END AS cdcOperation 
    ,CustomerID
    ,CustomerName
    ,DoingBusinessAs
    ,CustomerTypeID
    ,CreatedOn
FROM cdc.dbo_Customer_CT;

This will return all statements that have been issued against the Customer table with a "before" and "after" image for any updates.  Sweet!

For those of you that might be worried about the potential cost of keeping this data I have good news there as well.  By default, a cleanup process is automatically enabled that retains change data for three days.  This is configurable, allowing you to balance the business need versus storage cost proposition.

I love the implementation of this feature.  No longer am I resorting to triggers for keeping up with changes in my database.  While triggers are certainly easy to use, they have additional consequences on development that just don't exist with CDC.

Another complimentary feature I can see, introduced in SQL Server 2005, would be a DDL trigger to make sure tables are not altered without the proper additions to CDC.  Any columns added to a table after issuing the sp_cdc_enable_table stored procedure call are ignored in the tracking table.  Columns that are dropped will remain in the tracking table with NULL values.

At the risk of sounding like a giddy little kid, SQL Server 2008 has really got me excited.  I think this will be a huge release with some very compelling functionality.

For further BOL reading, check the topic How Change Data Capture Works.