SQL Concepts

SSDT syntax highlighting disappeared. How I fixed it.

2013-04-05T22:53:00.001+13:00

I recently struggled with a problem where I seemed to lose the syntax highlighting in Visual Studio 2012 for SSDT projects. Took me a while to stumble onto a solution after multiple reinstalls and almost giving up to rebuild the PC (I like syntax highlighting)

The tldr solution is:

Open a Visual Studio Command Prompt (as administrator)
Run:
DEVENV /SETUP

The problem (no pretty colours)

I'd been having an issue with my PC build where my SQL 2012 installation seems to lose some WMI settings. I can temporarily fix that with a mofcomp command, but getting frustrated with that I decided to reinstall SQL Server 2012. That was my first mistake. After the reinstall of SQL Server 2012 I noticed I'd lost all the syntax highlighting for SSDT projects as per this Stack Overflow question (not mine by the way): http://stackoverflow.com/questions/14365678/limited-vs2012-sql-syntax-highlighting

That article has the rather unhelpful "Hey it just fixed itself" answer. No such luck for me. After a general failing of my Google foo, I thought it would be just easier to reinstall Visual Studio 2012, SSDT etc. So that's what I did. Alas I still had no syntax highlighting. And no options for the SQL "types" to manually update the colours.

To list all I had tried;

Removed and reinstalled SQL Server 2012
Patched SQL Server 2012 with SP1
Reinstalled SSDT for Visual Studio 2012
Removed and reinstalled Visual Studio 2012
Patched Visual Studio 2012 with Update 1
Tried an earlier version of SSDT
Updated SSDT (again)

All yielded no difference.

Twitter failed me too.

The solution

I had pretty much given up and decided to get the PC rebuilt. But one last Google search yielded a couple of potential fixes that I thought I would try. These were two further Stack Overflow articles about problems that were (at best) vaguely similar to mine:

http://stackoverflow.com/questions/15643145/cannot-find-user-keywords-section-in-visual-studio-2012
http://stackoverflow.com/questions/11988077/plain-c-sharp-editor-in-visual-studio-2012-no-intellisense-no-indentation-no

The second one looked more promising, but infinitely more difficult for a simple DBA. So I thought I'd give the first article a go first.

The first stack overflow article says to perform the following steps:

Open a Visual Studio Command Prompt (as administrator)
Run:
DEVENV /SETUP

After all my reinstallations I really couldn't see this making any difference. I was happy to be wrong! These simple steps brought back the options to update colours for syntax highlighting.

Data warehouse starter for 10

2013-03-04T22:33:00.003+13:00

I've been working on a little project in my spare time, as scarce as that is lately, and as part of that I was putting together some dimensions for a star schema. This isn't the first time I've been around this block so I dug out some old code. I also thought "I can't be the first to do, need or want this".

So I took some of the common dimensions and code to populate them and started a SSDT project. I've added a few more dimensions since that and have since loaded it up to BitBucket.

I've opened up the repository to allow anyone who is interested or would benefit from the beginnings of a data warehouse model. You can find it here: https://bitbucket.org/kchenery/warehouse-model

Schema

Currently you'll find it has dimensions for:

Date
Time
Country
Continent
Currency
HTTP and FTP status codes - thanks to David Curlewis (b | t) for the inspiration on these
Languages
Top Level Domains

If you're in New Zealand, you'll find a couple of specific dimensions:

ANZSIC codes
NZ Regions

Its far from complete. So if you find something that needs fixing or like it but want it expanded feel free to log an issue/request in BitBucket and I'll see what I can do for you. For example; I want to add currency symbols to the currency dimension (but haven't yet found a good source).

Data

Along with the schema to create the basic dimensions, I've included post deploy scripts in the SSDT project to pre-populate the data. Most of these are pretty simple scripts that merge a long SELECT...UNION ALL script into the target dimension table.

The Date dimension has a procedure that is called to create a range of dates. This can be called to customise the range loaded or even extend that range.

You'll also notice each dimension table has a "special" record to record the "unknown" dimension value. The unknown record, and any other special records, will always have a negative value for the dimension key. E.g. -1 is always the unknown record.

Holiday Dates

Now a Date dimension, like any dimension or even database, is only as good as the data in it. You can customise the dates by adding your current regional dates into the dimHolidayDate table.

SSDT SQL Agent Job Deployment Template

2012-09-22T21:50:00.000+12:00

SQL Server Data Tools (SSDT) is a great product for database development. It even includes the ability to code and publish some "server level" objects:

Logins (so we can associate database users)
Extended Events
Linked Servers
Cryptography keys
Service Broker queues
Server level triggers
Server roles and memberships

But one object currently omitted is the ability to define and deploy SQL Agent Jobs. The only practical solution is to manage these through post deployment scripts.

As I've been working on a project that will have a fairly large number of SQL Agent jobs I have come up with a pattern for maintaining these that might be of use to other people. Its a pretty simple approach, it does involve a few manual steps but as you'll see these aren't too bad.

For my projects I like to structure my deployment scripts into a "Scripts" folder. Under that I put a number of sub-folders for various tasks, e.g:

Data population/seeding
Miscellaneous (usually this just has a script to drop the __Refactor table)
Jobs

The "root" Scripts folder just holds a Pre and Post deployment script that will reference other scripts in the various directories.

E.g:

/*
Post-Deployment Script Template       
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.  
 Use SQLCMD syntax to include a file in the post-deployment script.   
 Example:      :r .\myfile.sql        
 Use SQLCMD syntax to reference a variable in the post-deployment script.  
 Example:      :setvar TableName MyTable       
               SELECT * FROM [$(TableName)]     
--------------------------------------------------------------------------------------
*/

/* Data Population */
:r ".\Data\Seed Product Categories.sql"

/* Job Categores */
:r ".\Jobs\Job Categories.sql"

/* Jobs */
:r ".\Jobs\Example Job.sql"

/* Refactor table tidy up */
:r ".\Misc\Drop the __Refactor table.sql"

This allows me to add and remove scripts quickly and easily. I can even just comment one out if I want. Its also a good way to quickly find deployment code (as long as you name your files sensibly).

In the SSDT project, that'd look something like this:

The job template script

As you'll see from the partial screenshot above there is also a file named "_Template.sql" in the Jobs folder that was not referenced in the PostDeployment.sql file. This template file contains a stub I use for creating the job deployment scripts.

The _Template.sql file contains stubs for various steps we want to go through:

Define a SQLCMD variable for the job name we want to use. This parameter is used throughout the script as we reference the name a few times.
Find out if there's an existing job with the same name. If there is we need to determine if we're going to do nothing and leave it alone or if we're going to redeploy the job.
If there is no existing job with the same name we will deploy the "new" job.
If there is an existing job we will check another SQLCMD variable RedeployJobs. If that variable is set to "Yes" then we'll drop the existing job and recreate it.
Depending on results of the above we'll either deploy the job or simply do nothing.

It looks like this:

/*
Job Deployment Script Template                            
--------------------------------------------------------------------------------------

Instructions
------------
    1.  Copy the contents of this script (you do not need to copy these comments/instructions)
        to a script that will contain the job definition.

    2.  Update the JobName parameter with the desired name.

    3.  Script the job from the server using SSMS.

    4.  Copy the job category to the Job Categories.sql post deployment script.

    5.  Copy the relavent sections from the generated script to the various sections below.

    6.  Ensure the database name is correct.  I recommend using the built in $(DatabaseName)
        parameter.

*/

/* Copy from below this line to the end of the script */
--:setvar JobName "Enter the job name within these quotes and uncomment this line"

PRINT 'Deploying job: $(JobName)';
DECLARE @JobId  UNIQUEIDENTIFIER;

SELECT  @JobId = job_id
FROM    msdb.dbo.sysjobs
WHERE   name = '$(JobName)';

/*
 * First we check existence of the specified job name and whether the RedeployJobs parameter
 * has been set to "YES".
 */
IF UPPER('$(RedeployJobs)') = 'YES'   /* Redeploy the job */
OR @JobId IS NULL                   /* It does not exist so deploy it anyway */
BEGIN;
    /*
     * Either this is a new job or the RedeployJobs parameter is set to YES.  If its an existing job
     * we need to remove it so that we can "redeploy" it.
     */
    IF @JobId IS NOT NULL
    BEGIN;
        PRINT ' Deleting existing job';
        EXEC msdb.dbo.sp_delete_job @job_id = @JobId;

        /*
         * Set the @JobId variable to NULL for the sp_add_job command later on.  If it is not null the
         * server things the job is from a MSX server
         */
        SET @JobId = NULL;
    END;
     
    /*
     * Add the job
     */

    /*
     * Add the job step(s)
     */

    /*
     * Add the job schedule
     */

    /*
     * Add the job server
     */
    EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';

    PRINT ' Created the job "$(JobName)"';
END;
ELSE
BEGIN;
    PRINT ' Bypassing job "$(JobName)" deployment as job exists and RedeployJob parameter is "$(RedeployJobs)"';
END;

Hopefully the template is pretty clear as far as determining whether to deploy the job or not.

Deploying a new job

I'm going to assume you've created a job in your development environment that you now want to include in your SSDT project. Here's how we go about that:

First we need to create a new "PostDeployment" script in our Jobs folder
Right click on the Jobs folder --> Add --> Script...
Select "Post-Deployment Script" and call it the same name as your job (simply so you can find it easily). For the purposes of this blog post, it will be referred to as Example Job.sql
Click Add
Open up the _Template.sql file and copy its contents into the new Example Job.sql script.
In the Example Job.sql script, uncomment the ":setvar JobName ...." line and enter the name of the job between the quotes:
```
:setvar JobName "Example Job"
```
Now open up SQL Server Management Studio and script out your job to a new query window or file. From here we'll be copying various sections into our new Example Job.sql file to fill in the missing sections.
First copy the section to add the job:

Into the line just below the comment:
```
/*
 * Add the job
 */
```
Modify the step so that the @job_name parameter uses the SQLCMD variable:
For each job step follow the same approach by copying the sp_add_jobstep commands to the line just below the comment:
```
/*
 * Add the job step(s)
 */ 
```
Note: For any step that references the database this project applies to, it is highly recommended that you update the command to use the SQLCMD variable
```
$(DatabaseName)
```
. E.g.:
I also add the "sp_update_job" command in this section. If you prefer you could create your own section for that.
Finally add the job schedule. I like to name the schedule the same as the job and therefore use the $(JobName)
SQLCMD variable here too:

You should now have something that resembles this:

:setvar JobName "Example Job"

PRINT 'Deploying job: $(JobName)';
DECLARE @JobId  UNIQUEIDENTIFIER;

SELECT  @JobId = job_id
FROM    msdb.dbo.sysjobs
WHERE   name = '$(JobName)';

/*
 * First we check existence of the specified job name and whether the RedeployJobs parameter
 * has been set to "YES".
 */
IF UPPER('$(RedeployJobs)') = 'YES'   /* Redeploy the job */
OR @JobId IS NULL                   /* It does not exist so deploy it anyway */
BEGIN;
    /*
     * Either this is a new job or the RedeployJobs parameter is set to YES.  If its an existing job
     * we need to remove it so that we can "redeploy" it.
     */
    IF @JobId IS NOT NULL
    BEGIN;
        PRINT ' Deleting existing job';
        EXEC msdb.dbo.sp_delete_job @job_id = @JobId;

        /*
         * Set the @JobId variable to NULL for the sp_add_job command later on.  If it is not null the
         * server things the job is from a MSX server
         */
        SET @JobId = NULL;
    END;
     
    /*
     * Add the job
     */
    EXEC msdb.dbo.sp_add_job @job_name=N'$(JobName)', 
  @enabled=1, 
  @notify_level_eventlog=0, 
  @notify_level_email=0, 
  @notify_level_netsend=0, 
  @notify_level_page=0, 
  @delete_level=0, 
  @description=N'This is a SSDT example job deployment', 
  @category_name=N'Database Maintenance', 
  @owner_login_name=N'sa', @job_id = @jobId OUTPUT;

    /*
     * Add the job step(s)
     */
    EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 1', 
  @step_id=1, 
  @cmdexec_success_code=0, 
  @on_success_action=1, 
  @on_success_step_id=0, 
  @on_fail_action=2, 
  @on_fail_step_id=0, 
  @retry_attempts=0, 
  @retry_interval=0, 
  @os_run_priority=0, @subsystem=N'TSQL', 
  @command=N'SELECT * FROM sys.databases', 
  @database_name=N'$(DatabaseName)', 
  @flags=12;

    EXEC msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1;

    /*
     * Add the job schedule
     */
    EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'$(JobName)', 
  @enabled=1, 
  @freq_type=4, 
  @freq_interval=1, 
  @freq_subday_type=4, 
  @freq_subday_interval=10, 
  @freq_relative_interval=0, 
  @freq_recurrence_factor=0, 
  @active_start_date=20120922, 
  @active_end_date=99991231, 
  @active_start_time=0, 
  @active_end_time=235959, 
  @schedule_uid=N'64aed10d-3b89-47ae-8f51-ca6d196b1444';

    /*
     * Add the job server
     */
    EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';

    PRINT ' Created the job "$(JobName)"';
END;
ELSE
BEGIN;
    PRINT ' Bypassing job "$(JobName)" deployment as job exists and RedeployJob parameter is "$(RedeployJobs)"';
END;

Save your file
Update the "master" post deployment script to reference the newly created job script

Before you deploy...

If you were to try and deploy this now, it would fail. We haven't yet added the $(RedeployJobs) variable to the project. This is very simple to do:

Open up the properties for the project
Select the "SQLCMD Variables" tab
Add the RedeployJobs variable with Yes as the value for Default and Local:

Deploying

Now when you deploy you can decide whether to override any existing jobs with the same names or leave them as they are. As it uses a SQLCMD variable you can specify this as part of your continuous integration tools. Even if you specify "No" for RedeployJobs a new job will always be deployed.

Whilst there are quite a few steps to follow, once you've done this once or twice you'll realise that its a very easy approach. The main dangers are forgetting to update the generated job names and database references with the appropriate SQLCMD variables.

Cursors and While loops reply

2012-07-09T21:14:00.002+12:00

Earlier in the year David Curlewis ( t | b ) wrote a blog post titled "cursors and while loops". If you haven't read that article please go read it now as this is basically a reply or follow-up to that post.

In his article, David showed that for the test he was using the cursor was far and away the higher performing query. The WHILE loop took 30+ seconds whereas the cursor query took closer to 0.5 seconds.

I spotted a potential improvement to make the WHILE loop behave more in line with what I expected and I think what David expected.

Lets recap

Since the hardware I am running on is no doubt different to David's and I ran my tests on SQL Server 2012 with cumulative update 2 (11.0.2325) there are going to be some timing differences. I believe I have used the same AdventureWorks version of SQL 2008 (get it from CodePlex) and I have used the scripts David used.

For each test result I have taken the average of 5 tests.

Rather than repeat ALL the cursor tests that David did I've selected the one that performed the fastest: a GLOBAL STATIC CURSOR. As I said: go read his article if haven't yet.

The CURSOR approach

------------------------------------------------
-- CURSOR
------------------------------------------------
--DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
--     [ FORWARD_ONLY | SCROLL ]
--     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
--     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
--     [ TYPE_WARNING ]
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO
SET NOCOUNT ON;
GO
DECLARE @SalesOrderID INT, @LineCount INT;
DECLARE SOID CURSOR GLOBAL STATIC TYPE_WARNING
FOR
    SELECT  SalesOrderID
    FROM    Sales.SalesOrderHeader
    WHERE   OnlineOrderFlag = 1;
 
OPEN SOID;
FETCH NEXT FROM SOID INTO @SalesOrderID;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @LineCount = ISNULL(@LineCount,0) + ISNULL((
            SELECT  COUNT(*)
            FROM    Sales.SalesOrderDetail
            WHERE   SalesOrderID = @SalesOrderID
            ), 0);
 
    FETCH NEXT FROM SOID INTO @SalesOrderID;
END
CLOSE SOID;
DEALLOCATE SOID;
IF @LineCount <> 60398
BEGIN
    RAISERROR('Bad Total',16,1);
    PRINT @LineCount;
END
GO

When I ran this the code achieved the following metrics:

CPU (ms)	Reads	Writes	Duration (ms)
699	195433	52	1216

The WHILE loop

------------------------------------------------
-- WHILE LOOP (using counter)
------------------------------------------------
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID('tempdb..#SOID') IS NOT NULL DROP TABLE #SOID;
GO
CREATE TABLE #SOID (ID INT IDENTITY, SalesOrderID INT NOT NULL);
DECLARE @i INT = 1, @ii INT, @SalesOrderID INT, @LineCount INT;
 
INSERT  #SOID (SalesOrderID)
SELECT  SalesOrderID
FROM    Sales.SalesOrderHeader
WHERE   OnlineOrderFlag = 1;

SET @ii = @@ROWCOUNT;
 
WHILE @i <= @ii
BEGIN
    SET @SalesOrderID = (SELECT SalesOrderID FROM #SOID WHERE ID = @i);
 
    SET @LineCount = ISNULL(@LineCount, 0) + ISNULL((
            SELECT  COUNT(*)
            FROM    Sales.SalesOrderDetail
            WHERE   SalesOrderID = @SalesOrderID
            ),0);
 
    SET @i += 1;
END
IF @LineCount <> 60398
BEGIN
    RAISERROR('Bad Total',16,1);
    PRINT @LineCount;
END
GO

This code achieved the following metrics:

CPU (ms)	Reads	Writes	Duration (ms)
39642	1827358	60	41651

We've gone from 0.7secs to a whopping 39.6secs of CPU and 1.2secs duration to 41.6secs. Clearly theres an issue with the WHILE loop.

The keen eyed amongst you would have spotted where all that resource is being chewed up - the reads has jumped from 195,433 to 1,827,358. The WHILE loop is doing nearly 10 times as many reads.

What I realised when I first saw this with David's article was that the ID column lookup on #SQLIO has no index. Therefore with every iteration of the loop we have to scan that temporary table. We're really only after one row and we know that the ID is unique because it is an IDENTITY column. All we need to do is add an index on that column and being unique its quick and easy to make it a primary key...

The WHILE loop with a PRIMARY KEY

------------------------------------------------
-- WHILE LOOP (using counter)
------------------------------------------------
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID('tempdb..#SOID') IS NOT NULL DROP TABLE #SOID;
GO
CREATE TABLE #SOID (ID INT IDENTITY PRIMARY KEY CLUSTERED, SalesOrderID INT NOT NULL);
DECLARE @i INT = 1, @ii INT, @SalesOrderID INT, @LineCount INT;
 
INSERT  #SOID (SalesOrderID)
SELECT  SalesOrderID
FROM    Sales.SalesOrderHeader
WHERE   OnlineOrderFlag = 1;

SET @ii = @@ROWCOUNT;
 
WHILE @i <= @ii
BEGIN
    SET @SalesOrderID = (SELECT SalesOrderID FROM #SOID WHERE ID = @i);
 
    SET @LineCount = ISNULL(@LineCount, 0) + ISNULL((
            SELECT  COUNT(*)
            FROM    Sales.SalesOrderDetail
            WHERE   SalesOrderID = @SalesOrderID
            ),0);
 
    SET @i += 1;
END
IF @LineCount <> 60398
BEGIN
    RAISERROR('Bad Total',16,1);
    PRINT @LineCount;
END
GO

And....voila:

CPU (ms)	Reads	Writes	Duration (ms)
717	140336	31	1215

There are a few milliseconds here and there, but the performance of the while loop and cursor are now in line with one another.

Whilst I was here I thought I would try the same query but using a table variable instead.

The WHILE loop using a TABLE variable

Here's the code that I used for this test:

-------------------------------------------------
-- WHILE LOOP (using counter) with TABLE VARIABLE
-------------------------------------------------
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO
SET NOCOUNT ON;
GO
DECLARE @SQLIO TABLE(ID INT IDENTITY PRIMARY KEY CLUSTERED, SalesOrderID INT NOT NULL)
DECLARE @i INT = 1, @ii INT, @SalesOrderID INT, @LineCount INT;
 
INSERT  @SQLIO (SalesOrderID)
SELECT  SalesOrderID
FROM    Sales.SalesOrderHeader
WHERE   OnlineOrderFlag = 1;

SET @ii = @@ROWCOUNT;
 
WHILE @i <= @ii
BEGIN
    SET @SalesOrderID = (SELECT SalesOrderID FROM @SQLIO WHERE ID = @i);
 
    SET @LineCount = ISNULL(@LineCount, 0) + ISNULL((
            SELECT  COUNT(*)
            FROM    Sales.SalesOrderDetail
            WHERE   SalesOrderID = @SalesOrderID
            ),0);
 
    SET @i += 1;
END
IF @LineCount <> 60398
BEGIN
    RAISERROR('Bad Total',16,1);
    PRINT @LineCount;
END
GO

I fully expected the results for this to be the same as the temporary table.

CPU (ms)	Reads	Writes	Duration (ms)
699	140074	31	1183

And that is basically what we see.

Full results

CPU

Test	CPU (ms)
Cursor	669
While Loop (original)	39642
While Loop (temp table)	717
While Loop (temp variable)	661

CPU for all tests:

CPU for tests excluding the original while:

Duration

Test	Reads
Cursor	1216
While Loop (original)	41651
While Loop (temp table)	1215
While Loop (temp variable)	1184

Duration for all tests:

Duration for tests excluding the original while:

Reads

Test	Reads
Cursor	195433
While Loop (original)	1827358
While Loop (temp table)	140336
While Loop (temp variable)	140074

Reads for all tests:

Reads for tests excluding the original while:

Writes

Test	Reads
Cursor	52
While Loop (original)	60
While Loop (temp table)	31
While Loop (temp variable)	31

Writes for all tests:

Conclusion

I personally don't draw much of a conclusion from these tests.

The cursor and the while loop perform at (more or less) the same level. It is worth noting that a STATIC CURSOR effectively builds a temporary table in the background. Books Online about cursors even has the following:

STATIC

Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

Its therefore unsurprising that performance of these two approaches is on a par with one another.

The table variable approach seems to offer a slight benefit over the others. However, the figures are very close and this could simply be down to the way the measurements are taken and/or something else running in the background, even if very briefly, at the time of the tests.

Lets be clear about one thing: either of these approaches is a bad solution for the question that it answers. You can write a very simple set based SELECT query in place of these queries.

Using host names? You are doing it wrong!

2012-03-19T21:21:00.001+13:00

This is one of my pet peeves on how many people seem to set up their environment and in particular what they use in their connection strings.

It comes down to this: If you are using the actual host name to connect to your database, you are doing it wrong!

What do I mean? Simple if your database lives on a server named WINSQL01 you should never use that name for your applications. Why? Simple - what if you want to move your database to WINSQL02 through database growth, a fail-over to your disaster recovery server, a hardware refresh or server consolidation? You will now need to reconfigure all your application connections to point the new name (WINSQL02). That is potentially going to require a new application release and that means yet another round of application testing.

From something that could have involved just the DBA (moving the database) we now have to involve the developers or application vendor and your test team. 1 person to potentially dozens. A cheap change to something downright expensive when you add up the time required by all the people.

So how do we avoid this scenario. Its actually quite simple and I'm amazed how few companies are doing this in my experience: You use a DNS CNAME (aka alias).

Examples

Lets say we are using the AdventureWorks database that resides on WINSQL01. You would probably have a connection string something like this:

Data Source=WINSQL01; Initial Catalog=AdventureWorks; Integrated Security=SSPI;

We might then create a CNAME record similar to this:

NAME                  TYPE     VALUE
--------------------- -------- ---------------------
AdventureWorksDB      CNAME    winsql01.contoso.com

We then simply update the connection string to:

Data Source=AdventureWorksDB; Initial Catalog=AdventureWorks; Integrated Security=SSPI;

You'll notice that I have simply added "DB" to the end of the database name. This is a standard you may wish to use, although I prefer another one I will demonstrate shortly.

If we then move our database, for whatever reason, to WINSQL02 we simply update the DNS entry!

NAME                  TYPE     VALUE
--------------------- -------- ---------------------
AdventureWorksDB      CNAME    winsql02.contoso.com

This is almost always a far simpler change to make!

I personally like to set up DNS names for both production and disaster recovery databases. Especially if I'm using database mirroring. For that I'd do something like this:

NAME                  TYPE     VALUE
--------------------- -------- ---------------------
AdventureWorksProd    CNAME    winsql01.contoso.com
AdventureWorksDR      CNAME    winsql02.contoso.com

Then the connection string would be:

Data Source=AdventureWorksProd; Failover Partner=AdventureWorksDR;
Initial Catalog=AdventureWorks; Integrated Security=SSPI;

Note: The connection string wrapping is simply to fit on the screen.

What about named instances?

These are a little trickier I will admit. However; you can still use a DNS CNAME. Following the examples above, if our AdventureWorks database lived on WINSQL01\SALES we would use the connection string:

Data Source=AdventureWorksDB\SALES; Initial Catalog=AdventureWorks; Integrated Security=SSPI;

The problem comes when moving to another server. If the instance name is the same then you are good to go. I would try and do this if you have named instances and you want to have DR equivalents. Everything above still holds true. If you move the database to a server with another instance name then you have update the connection string. I know of no easy way around that and I would love to hear any suggestions you have.

Arguments against this

Personally I think there is every reason to do this and absolutely no reason not to do this. However; here are a few of the "arguments" I've heard against using it:

It takes an extra network lookup to do this
WRONG! You have to query DNS to get the IP address for the host name anyway
Updating the DNS name for a failover takes too long
Does it take longer than updating the connection strings on all your application servers or users workstations? Why not script it?
I can't remember all the CNAMES we have created
You could always query the DNS to find out! In a few places I've worked with the DNS admins to export the DNS tables to a text file that we then load into a table and create a Reporting Services report over that.
I cant (or don't want to) create a CNAME for every database
You don't have to. Indeed I recommend you create one per application rather than database. Typically an application that uses many databases will need those databases on the same server. Take SharePoint as an example; you often have those DBs on the same server so create a single SharePointDB alias.

Conclusion

This really is a no brainer to me, but all too often I see this approach ignored or overlooked. Its such a simple change to the way you work, it costs you nothing but it will save you a huge amount of time and effort when you want to move your databases.

Listing SQL Server Instances

2012-02-09T21:04:00.004+13:00

As a consultant I go into environments where the client simply does not know how many SQL Server instances they have. Perhaps as a DBA in a company you've been asked to do a license audit and need to know about all the SQL Server instances in your company. Or, as is common in New Zealand so probably elsewhere, you've just started at a company as their first DBA since they've grown to a size that requires one. The same company has probably allowed too many people to have too many rights and all kinds of SQL Server instances have sprung up all over the place.

Its a common situation. You can of course physically go to each machine in your company and check what is installed. But I'm a lazy DBA, or I try to be, and I want a solution that can let me have another coffee before lunch time.

If you do a Bingoogle search for something like "list sql server instances" you'll probably find these articles:

Both of which list this PowerShell command:

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

I did. And when I did all I could think was "Sweet! How easy is that!?"

Sadly not as easy as it looks.
I ran it and got this result:

I honestly cannot explain why that is - but in my "test" environment I also get the same and that's a pretty vanilla setup. But like anything in IT there is always another way. I basically thought "if I can get a list of services on each machine in the domain I'll be able to work out what instances are installed where".

The first half of that equation is easily achieved in PowerShell with the

Get-Service -ComputerName HostName

command. I just needed a list of computers in the domain to work over. Back to Bingoogle and once again "PowerShell Guy" to the rescue: http://blogs.technet.com/b/heyscriptingguy/archive/2006/11/09/how-can-i-use-windows-powershell-to-get-a-list-of-all-my-computers.aspx

I had a small tweak of the code to pump it into a list that can be saved to a file (computers.txt):

$strCategory = "computer"

$objDomain = New-Object System.DirectoryServices.DirectoryEntry

$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = ("(objectCategory=$strCategory)")

$colProplist = "name"
foreach ($i in $colPropList){
   $res = $objSearcher.PropertiesToLoad.Add($i)
}

$colResults = $objSearcher.FindAll()

$computers = foreach ($objResult in $colResults){
   $objComputer = $objResult.Properties; 
   $objComputer.name
}

$computers

$computers | Out-File "computers.txt"

Then take that file as the input to the Get-Service cmdlet (you could of course bypass the file...but having a computer list is handy for other tasks...so keep hold of it!):

Get-Content "computers.txt" | %{

    Get-Service -ComputerName $_ | ?{$_.Name -like "MSSQLServer" -or $_.Name -like "MSSQL$*"} | Add-Member -MemberType NoteProperty "HostName" -Value $_ -Passthru

} | select HostName, ServiceName, DisplayName, Status

Which spits out:

And there you have it. A complete list of SQL Server instances.

There are a couple of things to consider here though. Firstly you need the ability to query active directory. Secondly you need the ability to query the services on every computer. In larger environments a DBA typically will not be allowed to do that. You're going to have to go make friends with your domain administrators and either temporarily get some elevated privileges or have them run the code for you. And thirdly: if you've got servers in a DMZ type environment you may not be aware of those and/or be able to query the services list.

Also I haven't done too much formatting of the output, but it is possible to turn that into a HOSTNAME\INSTANCE_NAME format. And it would be handy to know the version of SQL Server being run. Which will make a terrific future blog post ;-)

For now - run the scripts and go have your coffee.

Deleting files by age with PowerShell

2012-02-08T00:07:00.002+13:00

Back in November 2011 I blogged about using FORFILES to delete files by age. I've since been "playing" with PowerShell and intend to blog quite a bit about it over the coming months.

To say PowerShell is powerful is an understatement! There's plenty of material on the Internet to help you too. But I wanted to kick off by replacing that old article with a PowerShell script, basically because I find PowerShell far easier to read!

But rather than just dumping a command with little explanation, I want to work you through it. This also demonstrates how I think through a problem with PowerShell and maybe that'll help you too. So I start with trying to display the items I want to deal with (be it report on, delete, move, action etc):

Get-ChildItem "C:\Example"

That will list all the files in C:\Example directory. A bit like this:

Mode                LastWriteTime     Length Name                                                                       
----                -------------     ------ ----                                                                       
-a---       7/02/2012  11:41 p.m.        598 File A.txt                                                                 
-a---       7/02/2012  11:41 p.m.         95 File B.txt                                                                 
-a---       7/02/2012  11:41 p.m.        311 File C.txt                                                                 
-a---       7/02/2012  11:41 p.m.         12 File D.dat

Note: You can use DIR too as it is aliased to Get-ChildItem.

Ok...but we're interested in files older than an age.
So we might come up with something like this:

Get-ChildItem "C:\Example" | Where-Object {
   $_.CreationTime -le [datetime]"2012-02-01 00:00:00"
}

That shows just one file:

Mode                LastWriteTime     Length Name                                                                       
----                -------------     ------ ----                                                                       
-a---       7/02/2012  11:41 p.m.        598 File A.txt

But we cant really verify thats the only file older than the date specified. If you wanted to show the CreationTime you'd need to adjust the command to include a "Select-Object" such as this:

Get-ChildItem "C:\Example" | Where-Object {
   $_.CreationTime -le [datetime]"2012-02-01 00:00:00"
} | select Mode, LastWriteTime, CreationTime, Length, Name

That would show:

Mode                     LastWriteTime           CreationTime                             Length Name                   
----                     -------------           ------------                             ------ ----                   
-a---                    7/02/2012 11:41:02 p.m. 31/01/2012 7:30:34 a.m.                     598 File A.txt

So to do the delete? Simple, just pipe into the Remove-Item cmdlet:

Get-ChildItem "C:\Example" | Where-Object {
   $_.CreationTime -le [datetime]"2012-02-01 00:00:00"
} | Remove-Item

And that's pretty much it!

But wait, there's more. Lets say we wanted to do something like "Show all files older than one month". We employ the Get-Date cmdlet and one of its methods:

Get-ChildItem "C:\Example" | Where-Object {
   $_.CreationTime -le (Get-Date).AddMonths(-1)
}

Want to know what other methods Get-Date offers? Try this:

Get-Date | Get-Member

T-SQL Tuesday #25 - T-SQL Tricks - Selective aggregates

2011-12-13T21:11:00.000+13:00

Firstly, thanks to Allen White for hosting T-SQL Tuesday #25.

This month's T-SQL Tuesday is about T-SQL Tips and Tricks. I mulled over a couple of ideas I had and eventually settled on showing a way to do selective aggregates. Not because I necessarily think its a trick, though it sort of is, but because I keep seeing this done in other terrible ways (as we'll see).

Selective Aggregates

So what is a selective aggregate?
Well I'm not even sure that's an official name. I just made it up but it does describe the approach. Its best described with an example, and I'm going to use our old friend AdventureWorks (get it from CodePlex)

This is a very trivial example, but imagine we wanted to get an aggregate (we're going to use COUNT) of sales by month and year. Well that's quite easy:

select
    year(OrderDate)
    ,month(OrderDate)
    ,count(*)
from
    Sales.SalesOrderHeader
group by
    year(OrderDate)
    ,month(OrderDate)
order by
    year(OrderDate)
    ,month(OrderDate)

Results

Boom! And there you have it. But what if we wanted to have years across the top...just like your CFO asked so (s)he could compare year on year?
We could use Pivot for this scenario but thats not what I want to show! If you look at the results we have years 2005 through 2008.

What I'll often see is something like this:

select
    month(soh.OrderDate) as [Month]
    ,(    select count(*) 
          from Sales.SalesOrderHeader as soh2
          where year(soh2.OrderDate) = 2005
          and month(soh.OrderDate) = month(soh2.OrderDate)
    ) as [2005]
    ,(    select count(*) 
          from Sales.SalesOrderHeader as soh2
          where year(soh2.OrderDate) = 2006
          and month(soh.OrderDate) = month(soh2.OrderDate)
    ) as [2006]
    ,(    select count(*) 
          from Sales.SalesOrderHeader as soh2
          where year(soh2.OrderDate) = 2007
          and month(soh.OrderDate) = month(soh2.OrderDate)
     ) as [2007]
     ,(   select count(*) 
          from Sales.SalesOrderHeader as soh2
          where year(soh2.OrderDate) = 2008
          and month(soh.OrderDate) = month(soh2.OrderDate)
     ) as [2008]
from
    Sales.SalesOrderHeader as soh
group by
    month(soh.OrderDate)
order by
    month(soh.OrderDate)

Results

And it works. But suddenly we've gone from referencing the Sales.SalesOrderHeader table once to 5 times. Thats not good.

How else could we achieve the same thing? The trick is to change your thinking slightly. We are trying to count occurrences where the year is a given value. And a count is the same as sum(1). And thats the trick, we sum the value 1 each time the year is the one we want. Here's how:

select
    month(OrderDate) as [Month]
    ,[2005] = sum(case when Year(OrderDate) = 2005 then 1 else 0 end)
    ,[2006] = sum(case when Year(OrderDate) = 2006 then 1 else 0 end)
    ,[2007] = sum(case when Year(OrderDate) = 2007 then 1 else 0 end)
    ,[2008] = sum(case when Year(OrderDate) = 2008 then 1 else 0 end)
    ,count(*) as TotalInMonth
from
    Sales.SalesOrderHeader
group by
    month(OrderDate)
order by
    month(OrderDate)

Results

Double boom! As you can see, we wrap a CASE statement inside a SUM. The case statement simply checks the year value and either reports 1 (count this one) or 0 (don't count this one). I don't know about you, but that method is much easier to read and to comprehend what is going on. Not only that its considerably faster. We are back to referencing the table just the once.

Query Plan Comparison

Aside from being easier to read, it has a much improved query plan.

Here is the query plan for the basic select:

As you'd probably expect it does a single scan of the table.

Here's the plan for the query with all the sub-selects (brace yourself):

I had to zoom out a bit to fit it on my screen. But just glancing at it...it looks worse right!? That's because it is. Much worse.

And here's the one for the selective aggregate:

That's back to a single table scan. It actually has 1 less operator than the original select by virtual of 1 less group by.

Conclusion

In these examples I was counting the records by doing a SUM(1 or 0). But hopefully you'll have spotted this isn't the only thing you can SUM. For example, we could have grabbed the "TotalDue" value:

select
    month(OrderDate) as [Month]
    ,[2005] = sum(case when Year(OrderDate) = 2005 then TotalDue else 0 end)
    ,[2006] = sum(case when Year(OrderDate) = 2006 then TotalDue else 0 end)
    ,[2007] = sum(case when Year(OrderDate) = 2007 then TotalDue else 0 end)
    ,[2008] = sum(case when Year(OrderDate) = 2008 then TotalDue else 0 end)
from
    Sales.SalesOrderHeader
group by
    month(OrderDate)
order by
    month(OrderDate)

Which would have yielded these results:

There's always many ways to write a query. And I'm sure many of you will already know this approach which is why I'm not 100% convinced its a T-SQL trick. But I meet people far too often that simply didn't know you could do this - and I guess that makes it valid for a T-SQL trick. Try to always think "can I write this a better way" - your DBA will thank you for it.

I'm now off to ~~steal~~ learn from everyone elses tricks. I suggest you do too!

5 Steps To Effective And Efficient Comments

2011-11-22T13:00:00.000+13:00

Recently I visited the dentist for a regular trip to the hygienist. My previous hygienist had moved away and so I had a new one. As part of the visit, my new hygienist said something that got me thinking: "I read your notes earlier and from that...". Exactly what followed is unimportant other than to take home the point that my new hygienist already had an idea of my dental history before they even met me.

Those few words struck a chord with me about how important your comments can be in your code. And whilst the example I give describes when someone else is tackling something you've coded, can you honestly remember every line of code you've written? I know I cant and I can think of at least one occasion where I've revisited a piece of code I wrote, had insufficient comments (that would be none) and could not understand why I had done something in a particular way.

So how do we go about ensuring we give good details about the code without wasting time, effort and the concentration of the reader?

Here are 5 steps I try to follow when writing code:

1. Comment first. Code later

I'm reminded of a quote from a university lecturer after setting us an assignment:

"The earlier you start coding, the further away you are from the solution."

And he was right.

There's nothing worse than trying to work through someone else's procedure that has no comments. Other than your own code that has no comments.

Rather than commenting your code, take the approach of using your comments to describe the approach you're about to employ and then code around that. I sometimes call this: coding your comments.

This forces you plan out your stored procedure. Collect your thoughts and ensure all the parts of the procedure you require are covered off. This approach gives you a chance to spot errors in your logic before you have even begun coding. These comments don't have to be dozens of lines long, and I'd encourage you to try and keep comments short, but ensure its enough so that you can follow your logic and retain your train of thought.

Think of your comments as pseudo code!

Taking this approach you can achieve the following benefits:

Sufficient comments for you and your colleagues to follow
You plan your procedure before you start trying to write it
Potentially spotting issues with your logic before you've even started writing your procedure

Its not a foolproof approach, but I find it saves me quite a bit of time.

2. Provide a clear description

Whenever I open up a procedure I want to immediately know what it does. If you have a good description at the top (in your comment block) it'll be much easier to follow the actual code and understand what is going on. Remember that comments are here to make your life easier - give the reader of the code that chance.

If you can give a good description in 1 line that's fine, but on the other hand don't be afraid to take several pages if needed. If I have to write a long description there's a couple of things I try to consider:

Is this procedure too complex? Should I break it down further? Can I provide a reference to a better description (e.g. a document, wiki article or something with a picture)

If you do still have to write a large amount of text I'll even approach the description/comment block as a document by providing a quick synopsis of the function (often with bullet points) then dive into each of the points after they're listed. Doing this gives the reviewer the choice as to how much they read.

E.g: Consider a poorly constructed database where customer details are stored in many tables and not necessarily with a single "Customer" table. You want to write a customer search procedure but each table stores customer details slightly differently.

Here's what I would write as a comment block:

Performs a customer search with the name provided. We do this by searching the following tables (in this order):

dbo.EmailAliases

dbo.CustomerOrders

dbo.CustomerCorrespondence

dbo.CustomerLeads

dbo.CustomerArchives

Each table is searched slightly differently, here's how we search each table:

dbo.EmailAliases
We search this table by comparing the email address supplied. We also try a bit of a pattern search against the names

dbo.CustomerOrders
We search this table by comparing the delivery address and the customer names

...etc...

3. Only comment as much as you need to

Does the following code really need a comment?

SELECT FirstName, LastName
FROM dbo.Person
WHERE PersonID = @PersonID

Its pretty clear reading the code what that does. On the other hand, I would argue this code does need a comment:

SELECT FirstName, LastName
FROM dbo.Person
WHERE Type = 'X'

Why is there the "WHERE Type = 'X'" in the SQL? If you don't tell people why its there how will we know if its correct or not?

4. Always comment something ugly, non-standard or just plain weird

If I'm reviewing a procedure and I see lots of index and optimiser hints I want to know why they're in there. If you haven't commented a reason to add a NOLOCK hint and I cant see a good reason for it I'm almost certainly going to remove that hint.

Have you applied some business logic to your code? Why is it in there and what does it do? If your code follows best practices and you have a well defined, well normalised schema you'll find you dont have to provide many comments at all. If you're the other 99% of us that don't have that luxury you'll find comments invaluable when you come to fix something later.

5. Do not comment out blocks of code

If its not needed...take it out completely! Don't comment it out "just in case" you need it later. Use source control rather than commenting out sections. Thats what source control is for! Its just as easy to redeploy an old version of a procedure as it is to uncomment a piece of code. Because its the same function! I have seen procedures that are comment blocks and no runnable code. Worse still - there was no comment about why something was commented out!

If you feel you absolutely must comment out a section of code, at least put a comment alongside it with a date at which point that block can be considered dead and fully removed. And add a reminder to your calendar!

Conclusion

Most technology people hate writing comments, we just want to code and code quickly. Some people even argue your code should be obvious enough to negate the need for any comments. If you can do that then great! But in my experience, and especially with TSQL, its rarely possible to do that. Like my new dental hygienist who hadn't put eyes on my teeth before, and who will almost certainly look at many more sets of teeth before mine again, notes and comments help you get an idea of the task in front of you. Hopefully one or two of the points will help you quickly write clear and concise comments without taking up much of your time.

T-SQL Tuesday #024 - Calculating Easter with a CLR Function

2011-11-08T16:29:00.001+13:00

T-SQL Tuesday for November 2011 is about Prox n Funx (Procedures and Functions) and is hosted by Brad Schulz here: http://bradsruminations.blogspot.com/2011/10/invitation-for-t-sql-tuesday-024-prox-n.html

This is my first T-SQL Tuesday post (be gentle) and I thought I'd share a function I have recently converted from T-SQL into a CLR based function as it fits with this months topic. I wont be going into what the CLR is or how you go about using it in SQL Server as there are plenty of posts and articles on the Internet already that explain it far better than I ever could.

A little while back I created a blog post on calculating the date of Easter when given a particular year, that post is here: http://blog.sqlconcepts.co.nz/2011/08/i-like-chocolate-eggswhen-is-it-easter.html

The function works fine from a logical point of view, however, due to the type of calculations going on it doesn't perform that well. If you use to populate a date dimension (or other static list of dates) its more than adequate for the job but if you are calling it frequently you'll want something with less overhead and more speed.

For that reason I set about turning it into a C# CLR function. Another reason for doing this was to get me back into the CLR and C# coding as I've only ever done a few pieces here and there. I need the practice!

Before I present the C# code and a few instructions to get it into the database a bit of background on calculating the date of Easter. In looking up how to calculate the date from a given year I discovered a few things I hadn't previously considered:

There is more than one way to calculate the date
Different parts of the world recognise the date differently

The first item wasn't too much of a surprise, that just became a case of picking the one I was most comfortable with (i.e. the one I could follow and implement!). The second one did come as a bit of a surprise although probably shouldn't have. In the end I went with what is appropriate for New Zealand but if you're in another part of the world you may have to find an alternate method. A chance for a function upgrade in the future perhaps.

The method I eventually went with is called the "Anonymous Gregorian Method". You'll find info on it, and also the algorithm, on Wikipedia: http://en.wikipedia.org/wiki/Computus#Anonymous_Gregorian_algorithm

The Code:

using System;
using System.Data.SqlTypes;

namespace SQLConceptsLtd
{
    public class SqlClrEaster
    {
        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDateTime CalcEaster(SqlInt32 Year)
        {
            // Function to calculate the date of Easter for a supplied year.
            // If you want to return a wider range of dates to SQL Server you can use
            // the DateTime (.Net) datatype to return to a DateTime2 in SQL Server.  However,
            // that presents other problems notably the inability to set the return
            // value to Null.

            // This function uses the "Anonymous Gregorian algorithm" as described in
            // Wikipedia: http://en.wikipedia.org/wiki/Computus#Anonymous_Gregorian_algorithm


            // Return null if Year is null or out of DateTime range
            if ((Year.IsNull) || (Year < 1753) || (Year > 9999))
                return SqlDateTime.Null;

            // Cast SqlInt32 to int type as we reference it a few times in the calculation
            int year = (int)Year;

            // See the Wikipedia link above to understand the need for all these variables
            int a, b, c, d, e, f, g, h, i, k, l, m, month, day;

            // Here's the calcuation to get the month and day values
            a = year % 19;
            b = year / 100;
            c = year % 100;
            d = b / 4;
            e = b % 4;
            f = ((b + 8) / 25);
            g = ((b - f + 1) / 3);
            h = ((19 * a) + b - d - g + 15) % 30;
            i = c / 4;
            k = c % 4;
            l = (32 + (2 * e) + (2 * i) - h - k) % 7;
            m = (a + (11 * h) + (22 * l)) / 451;

            month = (h + l - (7 * m) + 114) / 31;
            day = ((h + l - (7 * m) + 114) % 31) + 1;

            // return the date!
            return new SqlDateTime(year, month, day);
        }
    }
}

There really isn't all that much to this function and if you've looked at the Wikipedia article with its description and algorithm you'll see I've literally just converted it to C# and wrapped a few .Net datatypes around it. I did discover we don't (yet) have support for the SQL DATETIME2 data-type in .Net. I found you could use the .Net DateTime datatype which would then map to the SQL DATETIME2 datatype, however, you cannot set the .Net DateTime datatype to NULL which could be a bit of a problem. For this function I stuck to the SqlDateTime which can be set to NULL.

As I'm still (re)learning C# if I've done something stupid I'd be keen to get some feedback on that so comment away!

Once you compile the class above you'll get a .dll assembly we can load into SQL Server. That's done with the following code:

create assembly CalcEaster
from 'C:\Users\Kent Chenery\Documents\Visual Studio 2010\Projects\SqlClrEaster\SqlClrEaster\bin\Release\SqlClrEaster.dll'
with permission_set = safe;
go

create function dbo.CLRCalcEaster(@Year int)
returns datetime
as
external name CalcEaster.[SQLConceptsLtd.SqlClrEaster].CalcEaster;
go

I imagine its obvious but you'll have to adjust the assembly source for yourselves and your environment.

You can now call the dbo.CLRCalcEaster function like you would any other scalar function. So to find out the date of Easter in 2012 you simply run:

select dbo.CLRCalcEaster(2012);

The function will tell you its: 8th April 2012

I said at the beginning of this article that the T-SQL function was slow - thats perhaps not that fair, its not so slow as to be unusable but the CLR equivalent function results in a measurable drop in CPU usage and execution duration. Here's some stats for you to consider:

# Calculations	T-SQL Version	CLR Version	% Improvement
10000	0.233	0.159	31.8%
100000	1.598	0.998	37.5%
1000000	15.445	8.601	44.3%

The timings are in seconds and an average of 5 results for each set of tests.

As you can see, with a good number of calculations you get 40% savings in CPU and execution times. Without any further research/testing my guess for the increased efficiency with more calculations comes from the compilation time becoming a smaller and smaller portion of the overall execution time.

Conclusion

This is a trivial example of a CLR function but one that clearly demonstrates the advantages in performance that can be gained with the CLR. I'm planning to explore the CLR further and hopefully post up some more functions and procedures that might be of use to people (other than myself).

Delete files by age using FORFILES

2011-11-07T09:37:00.001+13:00

Ever needed to tidy up some old backups? Perhaps wanted to delete a bunch of files by age?

If you're using standard SQL tools for local backups and old file tidy ups you can get into the situation I just faced when the files are not tidied up in time. Your disk fills up, your backup and cleanup task starts failing and the world comes crashing down.

Sure you can manually delete old files, but when there's lots of files in lots of directories and no PowerShell you'll want something to help you. Enter FORFILES.EXE

I ran the following to tidy up files older than 2 days in the backup directory:

forfiles /m *.* /s /d -2 /c "cmd /c del @path"

That command will delete (/c "cmd /c del @path") all files (/m *.*) older than 2 days (/d -2) in the current folder and all its sub-directories (/s).

Update (2012-02-07)

I recommend you check out PowerShell for all your command line needs. I blogged about a PowerShell way to do this here: http://blog.sqlconcepts.co.nz/2012/02/deleting-files-by-age-with-powershell.html

SQL Server and the Windows Firewall

2011-09-19T20:58:00.001+12:00

Anyone who's been administering SQL Server for more than 10 minutes knows that SQL Server uses port 1433 (by default) for connections. Once you been administering SQL Server for a bit longer you learn that the following default ports and protocols are used by the following services:

Port	Protocol	Used By
1433	TCP	Default port for SQL Server connections
1434	UDP	Port for the SQL Browser service. In essence, the browser service returns a list of instances and the ports they are listening on that reside on the host. It is mostly used to identify where named instances are listening
5022	TCP	The default port for Mirroring

Note: As far as I know; other than port 1434 for the Browser the ports can all be changed. Feel free to correct me!

So what? Well, if you are running in any kind of secure environment (we all keep our databases secure don't we?) then you'll need to be poking pinholes through the odd firewall here and there. For example; you may have a web server out in a DMZ but retain the database server in your "corporate" LAN. That is quite a common set up, and as such you have to open up connections to your SQL Server through a firewall.

Testing if the ports are opened

By far the easiest way to do this is to follow these steps:

Install the TELNET client
Open a COMMAND PROMPT
Run the following: TELNET [HOST IP ADDRESS] [PORT NUMBER]

E.G: TELNET 192.168.1.1 1433

If the port is open you'll generally be presented with a blank screen (helpful huh). However; if it fails you'll see the following:

Opening the ports on your server

You can of course manually edit the firewall rules, but running the script (or portions of the script) below is much quicker and far easier...

This quick little script came out of recently building up a virtual environment to test some database mirroring. I could have been lazy and simply disabled the Windows firewall but thought I'd see if I could whip up a script. Here's what came out:

REM Enable ICMPv4 (aka PING)
netsh advfirewall firewall set rule name="File and Printer Sharing (Echo Request - ICMPv4-IN)" dir=in new enable=yes
netsh advfirewall firewall set rule name="File and Printer Sharing (Echo Request - ICMPv4-Out)" dir=out new enable=yes

REM Add rules for SQL Server inbound
netsh advfirewall firewall add rule name="SQL Server (Engine - 1433)" dir=in action=allow protocol=TCP localport=1433
netsh advfirewall firewall add rule name="SQL Server (Browser - 1434)" dir=in action=allow protocol=UDP localport=1434
netsh advfirewall firewall add rule name="SQL Server (Mirroring - 5022)" dir=in action=allow protocol=TCP localport=5022


REM Add rules for SQL Server outbound
netsh advfirewall firewall add rule name="SQL Server (Engine - 1433)" dir=out action=allow protocol=TCP remoteport=1433
netsh advfirewall firewall add rule name="SQL Server (Browser - 1434)" dir=out action=allow protocol=UDP remoteport=1434
netsh advfirewall firewall add rule name="SQL Server (Mirroring - 5022)" dir=out action=allow protocol=TCP remoteport=5022

Sorry about the spill over...I need to find a better template

As you'll also see, its quite easy to define other rules and/or amend the port numbers in use.

Update (2011-11-30)

The best resource I've found for the list of port numbers required for the SQL Server range of services is this article in Books Online http://technet.microsoft.com/en-us/library/cc646023.aspx

Effective TSQL Templates

2011-09-13T22:33:00.002+12:00

What are TSQL Templates?

If you haven't discovered TSQL templates yet, you've been missing out on an extremely useful feature of SQL Server Management Studio (SSMS). We'll get into why they're so good very shortly but lets start by opening up the Template Explorer so you can see what they are; click View → Template Explorer:

Or if you're like me and you like your keyboard shortcuts: Ctrl + Alt + T

Though this shortcut is strangely missing in SSMS in Denali CTP3. This will open up the Template Explorer that has dozens of TSQL templates to build from. Go ahead and explore them.

Template Replacements

Using the "Alter Stored Procedure Template" as an example (its under the Stored Procedure folder of the Template Explorer), you will see many of the templates have some strange looking code amongst them. It looks tenuously like XML in that the bits of code appear to be tags surrounded by < and >

For example:

What are these exactly?

Well, they're a handy way to allow you specify replacement values in your template. More commonly known as template parameters. With the "Alter Stored Procedure Template" you are always going to need to specify the procedure you want to modify. The fully qualified name takes the form schema.object_name, in the example above I have highlighted the piece of code that allows for a common replacement for a schema name. You might be thinking "that looks awfully complicated for something that could be specified simply with <schema>". There's a reason for this; we have the ability to specify both a data-type and a default value for the replacement.

The full "syntax" for the template parameters is:
<Parameter_Name, Data_Type, Default_Value>

Whilst you have the ability to specify a data type, this actually plays no meaning (as far as I can tell). It simply helps you identify the kind of data you are expecting to replace here. You can even leave the data type and default value sections empty if you like.

Setting Template Parameter Values

How do we go about actually using these template parameters? Click Query → Specify Values For Template Parameters:

Or use the keyboard shortcut Ctrl + Shift + M
Yet another missing shortcut from Denali.

When you initiate the "Specify Values for Template Parameters" you'll receive the following dialog box:

If you enter some values and click ok, you'll find the values replaced in your template:

Modifying the default templates

After you've experimented with the default templates, you'll soon discover that you want to:

Modify existing templates - either to add your own spin to them or adhere to your coding standards
Add templates of your own

If you right click on one of the templates, rather than double clicking, you'll see an "edit" option:

Selecting edit will open the template, as per earlier, and allow you to make modifications to it. Lets add a comment block and save the changes:

Save your changes.

Now reopen your template.

This is all great. Right up until you edit any of the "... (New Menu)" templates. For example; if you edit the "Create Procedure (New Menu)" template, save and reopen you'll find everything looks as if it should. And perhaps you did this following my instructions above. But now open a database, navigate to the Stored Procedures in that database, right click and select "New Stored Procedure":

You'll find the original "...(New Menu)" template is opened. None of your changes have propagated through. Why is this?

Well the answer is quite simple, and arguably a bug but we'll settle for it being labelled as a feature.

When you edit any of the templates you edit the templates stored in: %APPDATA%\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

This seems to be the path used when you open a template through the template explorer too. Unfortunately, when you use the "New Menu" options through SSMS it doesn't use your profile copy, but the original install copy found in: C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql

To me; this a little bit crazy and a hugely counter intuitive. But at least we can "fix" this by updating these versions. T-SQL templates are a useful feature, especially if you want to standardise a few areas such as comment blocks. But as you'll have seen from the array of pre-supplied templates you can use them to build up an array of shortcuts for building your code. These are further extended in Denali, or by 3rd party tools including SSMS Tools (which is free), by the use of code snippets.

An effective "New Menu" template

Now that you know where to update the templates to affect the "New ..." option from SSMS its up to you to go and create your effective templates. But to help you, here's one I use for my coding that I find useful:

--use <DBName, sysname, >;
--go

if not exists(select 1 from sys.objects where object_id = object_id('[<Schema, sysname, dbo>].[<Object Name, sysname,>]')
begin;
	exec sp_executeSQL N'create procedure [<Schema, sysname, dbo>].[<Object Name, sysname,>] as select ''Procedure Stub'';';
end;
go

alter procedure [<Schema, sysname, dbo>].[<Object Name, sysname,>]
/***********************************************
 * Procedure:	[<Schema, sysname, dbo>].[<Object Name, sysname,>]
 * Date:		<Created Date, datetime, >
 * Author:		<Created By, ,Your Name>
 *
 * Description:	
 *
 * ------------ --------------- ----------------------------------------------------
 * Date			Author			Description
 * ------------ --------------- ----------------------------------------------------
 * <Created Date, datetime, >	<Created By, ,Your Name>	
 * ------------ --------------- ----------------------------------------------------
 */
as
/*
 * Procedure Options
 */
set nocount on;

/*
 * Start transaction
 */
begin transaction <Schema, sysname, dbo>_<Object Name, sysname,>;

/*
 * Do the work
 */

/*
 * Commit transaction
 */
commit transaction <Schema, sysname, dbo>_<Object Name, sysname,>;

/*
 * End of procedure
 */
go;

I recommend you amend the "New Menu" templates at the very least as these are the easiest ones to use. And they're likely to be the ones you reference the most if not the only ones you reference. What you define as your template is up to you, but do think about what you want to always include. Its a good opportunity to encourage comments or at the very least comment blocks in stored procedures, functions and views. I'll be writing a blog post about effective commenting in the coming weeks and setting up some templates will help in this area.

Discovering Full Text Search

2011-08-29T22:16:00.001+12:00

And so Code Camp 2011 comes to a close for another year. I can honestly say I had a great time and enjoyed presenting my topic: Discovering Full Text Search

Apologies for not being able to get through all the demos, I'm a bit disappointed about that as there were a couple I think were of great value. One in particular around the thesaurus as there were a couple of questions I think it would have answered quite well.

However, this then becomes a great opportunity to present the topic again at the Auckland SQL Users Group. There were quite a few people from Auckland who admitted to having not attended the users group before - it would be good to see some of you. We have free pizza and beer!

The goodness

Until then, you're welcome to view/download the slides to the presentation.
And grab the code for the demos - even the ones I skipped. You will need the AdventureWorks2008R2 database which you can download from http://msftdbprodsamples.codeplex.com/

If you did attend, thanks for coming and I hope you enjoyed it and managed to get something out of it.

Feedback

I'd love some feedback. I'm still fairly new to presenting so have a long way to go. What did you like, what didn't you like? Could you hear me OK, did I mumble? etc. Feel free to leave a comment or perhaps more ideally drop me a email (see my contact page above).

Shameless Plug

We've got SQL Saturday coming up. There's going to be fireworks! Literally. Its on the 5th of November. Put it in your diaries. More info will be available on the Auckland SQL website (http://www.aucklandsql.com/SQL-Saturday.aspx) when it becomes available.

If you did not hang around for Leo Millers excellent security presentation you missed a couple of things:

a very good presentation with lots to think about
and my little nugget (found the probable original): http://stuff.creativityhurts.net/post/9468362554/best-sql-injection-out-there-translation-for

Until next year...

We dont need no education!

2011-08-13T22:41:00.006+12:00

Way back in 1979 Pink Floyd argued "we don't need no education!". But that was over 30 years ago (sheesh...just how old am I?!) and education is fast becoming a scarce commodity. In just under two weeks TechEd NZ 2011 kicks off.

But I'm not here to tell you about that. In just over two weeks a far more cost accessible set of training is available: Code Camp.

I've been to a couple of code camps and the first one I attended, I think, was 2008 at Whitireia in Porirua. And I'm going to be brutally honest: it was better than TechEd. I kid you not. For a SQL Server professional this was a seriously good event. The quality of presentations and knowledge was amazing I met some great people and best of all it was free!

"You! Yes you laddie!"

On the 28th of August we have Code Camp 2011 in Auckland.

Personally I'm interested in the SQL Server track, but we also have:

Development Track - you know...all things .Net
SharePoint Track - Im guessing...but I think this focuses on SharePoint
Infastructure Track - shared with SQL track to cover AD, SSO, NLB and other awesome TLAs
Business Track - a new one covering topics that don't sit comfortably numb elsewhere.

Check out the full agenda for all the sessions here: http://www.dot.net.nz/codecamp/Pages/AllSessions.aspx

But lets be honest with ourselves...we're only interested in the SQL Server track so what are we covering here? Since the SQL track and the Infrastructure track are shared, the first two sessions are for Infrastructure topics, then we get into it:

11:30 - Full Text Search
12:30 - Lunch. Quite possibly the most important session. Seriously it is - this is your chance to quiz everyone!
13:00 - Monitoring multiple SQL instances for $0.00. I'm personally looking forward to this one
14:00 - Minimal Logging and Data Manoeuvring on Very Large Tables
15:00 - Afternoon Tea - Yummy!
15:30 - SQL Server: How Secure are you?
16:30 - Close and Prizes - Yet more "free stuff" (we do love free at Code Camp)

"If you dont eat your meat, you cant have any pudding. How can you have any pudding if you dont eat your meat?"

Going to TechEd NZ 2011? Then have your pudding too and come to Code Camp!
Not going to TechEd NZ 2011? I'll let you in on a secret: you can still have your pudding. It is free after all!

Come along to put another brick in your (education) wall. With a price like that, just why wouldn't you go?

Want some more info? Your two best sources are the Code Camp website (http://www.codecamp.co.nz/) and the #ccnz hash tag on twitter.

Teacher. Leave them kids alone.

And yes, I'm speaking! Please come say "Hello".

Heading towards an identity crisis?

2011-08-09T07:00:00.000+12:00

My client recently struck an issue with an identity column value. Basically they ran out! This brought a very visible (read customer facing) interruption to one of their systems.

I'm sure many of you are in the situation where you have defined an identity column for your primary key. There's nothing particularly wrong with that but do you keep an eye on whether you're near to running out of values or not?

Simply run this script to find out:

with cteIdentity as (
    -- Basic identity info
    select 
    o.object_id                                  as TableID
    ,c.column_id                                 as ColumnID
    ,s.name + '.' + o.name                       as TableName
    ,ident_current(s.name + '.' + o.name)        as CurrentIdentity
    ,power(2.0, ((8 * c.max_length) - 1)) - 1    as MaxIdentity
    from
        sys.objects as o
        inner join sys.columns as c
            on o.object_id = c.object_id
        inner join sys.schemas as s
            on o.schema_id = s.schema_id
        where
            c.is_identity = 1    -- Table has an identity column
        and o.type = 'U'         -- User tables only
)
-- Calc the percentage used
select
    TableName
    ,CurrentIdentity
    ,(CurrentIdentity / MaxIdentity) * 100       as PercentageOfMax
from
    cteIdentity;

It would be very easy to wrap some alerts around the results if the "PercentageOfMax" value is above a predefined threshold level (e.g: 95%)

Naturally this does not fix the issue you might be screaming towards, but it gives you that important "heads up" that you need to start planning for a change soon.

Consider that to increase the data type to bigint from an int (which is what you would typically have to do) could involve:

An outage to perform the table change
The need to re-factor several (many?) procedures that reference the table
Since its likely the identity is a primary key you're going to have to amend any tables that reference this one
You'll need to release an application update to cater for the increase in data-type size

Trust me; its far easier to address this before it happens than during an identity crisis.

I like chocolate eggs...when is it Easter again?

2011-08-04T21:22:00.001+12:00

A little while back I was working on a new data warehouse and was building the date dimension. The date dimension needed to cater for some seasonality adjustments and as such needed to know when specific holidays occurred. Most holidays are pretty easy to work out:

Christmas is usually on the 25th of Dec
New Year is round about the 1st of Jan

Some holidays are not so clear; Easter moves all over the place! That makes populating a date dimension a little awkward, but at least once you've done it you don't have to worry about it again.

I found a couple of sites that listed when Easter had or would occur (date dimensions need to cater for dates in the past and potentially in the future). If you Google for Easter dates you'll get them too. But I really wanted a programmatic way to find the date for a given year.

With a little bit of hunting I found this: http://www.bbc.co.uk/dna/h2g2/A653267

Good on ya BBC!

A bit of jiggery-pokery later I give you (I hope they don't mind me regurgitating it):

create function [dbo].[CalcEaster](@Year int)
returns datetime2
with schemabinding
as
begin
 /*
  * This function originally came from http://www.bbc.co.uk/dna/h2g2/A653267 which read:
  *
  * Given the Year as a four digit number in the range 1700 - 2299
  * Calculate Day and Month of Easter Sunday
  *  Note 1: the algorithm has not been tested outside this range.
  *  Note 2: the \ operator performs integer division without remainder.
  *  Note 3: the date returned is the Gregorian Calendar date
  *          (the one we use now), even for dates in the 18th Century.
  * 
  * a = Year mod 19;
  * b = Year \ 100;
  * c = Year mod 100;
  * d = b \ 4;
  * e = b mod 4;
  * f = c \ 4;
  * g = c mod 4;
  * 
  * h = (b + 8)\25;
  * i = (b - h + 1)\3;
  * j = (19*a + b - d - i + 15) mod 30;
  * k = (32 + 2*e + 2*f - j - g) mod 7;
  * m = (a + 11*j + 22*k) \ 451;
  * n = j + k - 7*m + 114;
  * 
  * Month = n\31;
  * Day = (n mod 31) + 1;
  */
 declare @a  int
 declare @b  int
 declare @c  int
 declare @d  int
 declare @e  int
 declare @f  int
 declare @g  int
 declare @h  int
 declare @i  int
 declare @j  int
 declare @k  int
 declare @l  int
 declare @m  int
 declare @n  int

 select @a = (@Year % 19)
 select @b = (@Year / 100)
 select @c = (@Year % 100)
 select @d = ((@Year / 100) / 4)
 select @e = ((@Year / 100) % 4)
 select @f = ((@Year % 100) / 4)
 select @g = ((@Year % 100) % 4)

 select @h = (@b + 8) / 25
 select @i = (@b - @h +1 ) / 3
 select @j = ((19 * @a) + @b - @d - @i + 15) % 30
 select @k = (32 + (2 * @e) + (2 * @f) - @j - @g) % 7
 select @m = (@a + (11 * @j) + (22 * @k)) / 451
 select @n = (@j + @k) - (7 * @m) + 114

 /*
  * Return the values as a datetime
  */
 return convert(datetime2, convert(char(4), @Year) + '-' + right('0' + convert(varchar(2), (@n / 31)), 2) + '-' + right('0' + convert(varchar(2), (@n % 31) + 1), 2))
end

So when is Easter 2012?

Enjoy.

Sequences for the pre-Denali generation

2011-07-27T22:00:00.000+12:00

One of the raved about new features of SQL Server Denali is Sequences. And why not? They will be a fantastic, if long overdue, feature in my opinion.

There has been the odd time in my career I could have done with them. Indeed I recently had cause to make use of them, however, they're simply not available yet. Sometimes an identity will do, but sometimes you need a pseudo sequence!

So what are sequences?

Before we dive into how to generate my so called "pseudo sequences", it is best to cover what they are. If you've used identity columns in the past you're about 80% of the way to understanding them. Essentially they are a counter much like an identity column. Unlike the identity property of a column, a sequence is not tied to a particular column - indeed it is not necessarily tied to a column at all. And unlike an identity column, you must explicitly increment the sequence.

Is the identity property dead?

Most definitely not! Like any tool, sequences have their place but they certainly do not solve every scenario. You are more likely to use an identity column than a sequence value, but as I have already mentioned there are times when an identity column simply wont do.

When would you use a sequence?

A couple of scenarios immediately spring to mind:

Scenario 1 : Manual table partitioning

You might have two (or more) tables that have the same auto generated key that you do not want to overlap. A scenario for this might be where you have manually partitioned tables based on a function. I recently had such a scenario: records could be generated from two sources; a customer and a system generated record. The customer records would not need validating, but the system ones would. Normally you'd create something like an IsValidated flag column - but modifying the existing schema was not possible (though I was allowed to create new tables). So I created a holding table where records could be checked before being pushed into the real table. Overlaps of keys wasn't allowed and it was required that in either scenario the key value was returned to the customer/system. A sequence would be great...but we're not working in SQL Server 2011 yet.

Scenario 2 : Incident management systems

Another scenario I've encountered is with ticketing or problem management systems. The "ticket" table would have an identity column for the primary key and this value would be used to store the ticket/incident number the customer would reference. Unfortunately you cannot give the customer their reference number until the ticket is saved. The work around most systems take is to create a blank record to display the ticket number and then update once the details are collected - this ends up with lots of orphan entries in your DB. Again a sequence is ideal: take the next sequence number and do an insert once all the data is collected.

I'm convinced...I want them NOW!

Firstly, we need a table to store our sequence values:

/*
 * Sequence Table
 */
if object_id('dbo.SequenceStore') is not null
begin;
 drop table dbo.SequenceStore;
end;
go

create table dbo.SequenceStore(
 SequenceStoreID   int identity(1, 1) not null
 ,SequenceName   varchar(50)   not null
 ,Value     bigint    null
 ,StartValue    bigint    not null
 ,MinValue    bigint    not null
 ,MaxValue    bigint    not null
 ,Increment    bigint    not null
 ,DateCreated   datetime2   not null
 ,DateLastAccessed  datetime2   null
 ,DateLastIncremented datetime2   null
);
go
/*
 * Primary key
 */
alter table dbo.SequenceStore
add constraint PK_SequenceStore
primary key clustered(SequenceStoreID)
with fillfactor = 100;
go

/*
 * Unique constraint on the name
 */
alter table dbo.SequenceStore
add constraint UC_SequenceStore_SequenceName
unique(SequenceName)
with fillfactor = 90;
go

/*
 * Defaults for the SequenceStore
 */
alter table dbo.SequenceStore
add constraint DF_SequenceStore_MinValue
default(0)
for MinValue;
go

alter table dbo.SequenceStore
add constraint DF_SequenceStore_MaxValue
default(9223372036854775807)
for MaxValue;
go

alter table dbo.SequenceStore
add constraint DF_SequenceStore_Increment
default(1)
for Increment;
go

alter table dbo.SequenceStore
add constraint DF_SequenceStore_DateCreated
default(sysdatetime())
for DateCreated;
go

A way to create sequences within our environment:

/*
 * dbo.SequenceCreate
 */
if object_id('dbo.SequenceCreate') is not null
begin;
 drop procedure dbo.SequenceCreate;
end;
go

create procedure dbo.SequenceCreate
 @SequenceName   varchar(50)     /* Name to assign to the sequence */
 ,@MinValue    bigint  = null   /* Min value of the sequence */
 ,@MaxValue    bigint  = null   /* Max value of the sequence */
 ,@Increment    bigint  = null   /* Increment value for the sequence */
 ,@StartValue   bigint  = null    /* A starting value */
 ,@NewSequenceID   int   = null output /* The new ID created */
as
/*
 * Procedure Options
 */
set nocount on;  /* Provide a slight performance improvement */

/*
 * Verify optional input paramenter values
 */
select
 @MinValue  = isnull(@MinValue, 0)
 ,@MaxValue  = isnull(@MaxValue, 9223372036854775807)
 ,@Increment  = isnull(@Increment, 1)
 ,@StartValue = isnull(@StartValue, @MinValue)
 ;

/*
 * Check the name doesn't already exist
 */
if exists(select 1 from dbo.SequenceStore where SequenceName = @SequenceName)
begin;
 raiserror('Unable to create new sequence as the name "%s" already exists', 16, 1, @SequenceName);
 return -1;
end;

/*
 * Check the StartValue, if supplied, is between the min and max
 */
if @StartValue is not null and not(@StartValue between @MinValue and @MaxValue)
begin;
 /*
  * Cant create the sequence as the start value is outside the bounds of the min/max values
  */
 raiserror('Unable to create the new sequence as the Start Value (%I64d) is outside of the Min (%I64d) and Max (%I64d) values', 16, 1, @StartValue, @MinValue, @MaxValue);
 return -2;
end;

/*
 * Add the record
 */
begin tran txSequenceCreate;

insert into dbo.SequenceStore(
 SequenceName
 ,Value
 ,StartValue
 ,MinValue
 ,MaxValue
 ,Increment
 )
values(
 @SequenceName
 ,null
 ,@StartValue
 ,@MinValue
 ,@MaxValue
 ,@Increment
 );

/*
 * Get the sequence ID value
 */
select @NewSequenceID = scope_identity();

commit tran txSequenceCreate;
go

A way to increment our sequence (safely):

/*
 * dbo.SequenceIncrement
 */
if object_id('dbo.SequenceIncrement') is not null
begin;
 drop procedure dbo.SequenceIncrement;
end;
go

create procedure dbo.SequenceIncrement
 @SequenceName   varchar(50)     /* Name of the sequence */
 ,@NumToGen    int  = 1     /* Number of sequences to generate */
 ,@Value     bigint = null output  /* The value after the increment */
as
/*
 * Procedure Options
 */
set nocount on;  /* Provide a slight performance improvement */

/*
 * Variables
 */
declare @NewValue bigint;
declare @MinValue bigint;
declare @MaxValue bigint;

/*
 * Verify the sequence exists
 */
if not exists(select 1 from dbo.SequenceStore where SequenceName = @SequenceName)
begin;
 raiserror('No sequence named "%s" exists', 16, 1, @SequenceName);
 return -1;
end;

/*
 * Increment the sequence
 */
begin transaction txSequenceIncrement;

/*
 * Get the next value with an update lock to ensure no other processes can
 * increment at the same time.
 */
select
 @NewValue = case
     when Increment > 0 then isnull(Value + (Increment * @NumToGen), (Increment * (@NumToGen - 1)) + MinValue)
     /* Less than zero, so need to use MaxValue if we starting the sequence from scratch (null) */
     else isnull(Value + (Increment * @NumToGen), (Increment * (@NumToGen - 1)) + MaxValue)
    end
 ,@MinValue = MinValue
 ,@MaxValue = MaxValue
from
 dbo.SequenceStore
 with(updlock, rowlock) /* row and update lock hints */
where
 SequenceName = @SequenceName;

/*
 * Verify the value is between the Min and Max - that is we haven't
 * exceeded the allowed range
 */
if @NewValue not between @MinValue and @MaxValue
begin;
 /*
  * Cannot do the increment as we'll exceed the range allowed
  */
 raiserror('Unable to increment the sequence as the allowable range will be exceeded', 16, 1);
 rollback transaction txSequenceIncrement;

 /*
  * Exit
  */
 return -2;
end;
else
begin;
 /*
  * Update the current row
  */
 update dbo.SequenceStore
 set
  Value     = @NewValue
  ,DateLastAccessed  = sysdatetime()
  ,DateLastIncremented = sysdatetime()
 where
  SequenceName = @SequenceName;

 /*
  * Set the output variable
  */
 set @Value = @NewValue;
end;

commit transaction txSequenceIncrement;
go

And for good measure; a way to reset our sequence:

/*
 * dbo.SequenceReset
 */
if object_id('dbo.SequenceReset') is not null
begin;
 drop procedure dbo.SequenceReset;
end;
go

create procedure dbo.SequenceReset
 @SequenceName   varchar(50)     /* Name of the sequence */
as
/*
 * Procedure Options
 */
set nocount on;  /* Provide a slight performance improvement */

/*
 * Variables
 */

/*
 * Verify the sequence exists
 */
if not exists(select 1 from dbo.SequenceStore where SequenceName = @SequenceName)
begin;
 raiserror('No sequence named "%s" exists', 16, 1, @SequenceName);
 return -1;
end;

begin transaction txSequenceReset;

update dbo.SequenceStore
set
 Value = null
where
 SequenceName = @SequenceName;

commit transaction txSequenceReset;
/*
 * End of procedure
 */
go

There are other procedures or functions you could create, for example a "peek" procedure that returned the next value but did not increment might be useful.

But how do I use that stuff?

Run the code to create the objects.
Use the procedure dbo.SequenceCreate to add sequences to your environment. You must give them a name, but all the other parameters are optional. You can specify min and max range values and increment amounts.
Every time you need a new sequence value call dbo.SequenceIncrement. Specify the name and capture the new value in the output parameter

The eagle eyed amongst you will notice a couple of optimiser hints in the dbo.SequenceIncrement procedure. These are to ensure only one increment "thread" can happen at a time on a given sequence. Without them we could hit some serious data integrity problems as two threads could increment to the same value!

"Hold on...

...You've used SQL Server 2008 features like DATETIME2 and I'm stuck on SQL 2005 (or even SQL 2000)"
This is true, but the code would be extremely easy to adjust for earlier versions. I can't do your whole job for you. You can, however, hire me for that!

Drawbacks

As it uses procedures you cannot auto apply a value to a column. And alas, as we need to update a table we cannot use a function to do this - unless someone can come up with a clever hack.

Its awkward to load into a table with this approach in a set based way. You need to prefetch all your sequences, therefore you need to know how many records are in play. You can then use a variable and/or nifty function to set the value. Something like:

MinOfRangeGathered + (SequenceIncrement * (ROW_NUMBER - 1))

The table can become a "hotspot" in your database if you're incrementing your sequence frequently. There is also the potential for a heavy amount of contention on the records in the table. Having said that; testing on my laptop yielded more than adequate results. I was able to increment 20,000 times across two threads in a couple of seconds - YMMV (and it almost certainly will).

Disclaimer

I provide absolutely no warranty on this code. You are free to use, modify and disseminate as you wish. Please give credit where its due.

Who Am I?

2011-07-18T21:46:00.001+12:00

SQL Server has no less than 13 functions to capture the current "person" running a piece of code. (And I may have missed some too!)

USER
CURRENT_USER
SESSION_USER
SYSTEM_USER
USER_NAME()
USER_ID()
USER_SID()
SUSER_NAME()
SUSER_SNAME()
SUSER_ID()
SUSER_SID()
ORIGINAL_LOGIN()
DATABASE_PRINCIPAL_ID()

Why so many? What do they all do? Why do some end in parentheses and some do not? And, more importantly, which one do I really need?

For something as simple as finding out who is calling a piece of T-SQL, there sure do seem to be a lot of functions! Lets take a look at what each ones does; paraphrasing from Books OnLine...

USER

The USER function returns the name of the currently executing context. If the context is switched with an EXECUTE AS the impersonated context is used.

This function returns the current database user.

Return type: CHAR

CURRENT_USER

CURRENT_USER returns the name of the current security context. If the context is switched with an EXECUTE AS the impersonated context is used. This function returns the current database user.

Return type: SYSNAME

SESSION_USER

SESSION_USER returns the name of the current security context. If the context is switched with an EXECUTE AS the impersonated context is used. This function returns the current database user.

Return type: NVARCHAR(128)

SYSTEM_USER

SYSTEM_USER returns the name of the current security context. If the context is switched with an EXECUTE AS the impersonated context is used. This function returns the current login, not the database user.

Return type: NCHAR

USER_NAME()

The USER_NAME() function is principally the same as the USER function. It can optionally take a database_principal_id as an input value and will return the database user of that ID if found.

Return type: NVARCHAR(256)

USER_ID()

This returns the identification number of the database user. What this means is that it returns the database_principal_id from sys.database_principals. Or if you're stuck on SQL 2000 uid from sysusers. The function can optionally take a database user as input and return the ID for that user if found. If the function is called with the EXECUTE AS statement and no input parameters it returns the ID of the impersonated account.

Return type: INT

Note: This function is to be deprecated. It is replaced by the DATABASE_PRINCIPAL_ID() function.

USER_SID()

This function seems to be strangely absent from Books Online. It returns the security identifier (SID) of current security context. It can optionally take a user id (database_principal_id) value as input. If the context is switched with an EXECUTE AS the impersonated context is used.

Return type: VARBINARY(85)

SUSER_NAME()

Returns the login name associated with a database user which can optionally be passed into the function. When called without parameters it takes the current security context. If the context is switched with an EXECUTE AS the impersonated context is used.

Return type: NVARCHAR(128)

SUSER_SNAME()

Returns the login name associated with a security identification number (SID) which can optionally be passed into the function. When called without parameters it takes the current security context. If the context is switched with an EXECUTE AS the impersonated context is used.

Return type: NVARCHAR(128)

SUSER_ID()

Returns the login identification number of the user. In SQL 2000 this always returned null. Starting with SQL 2005 this returned the principal_id in sys.server_principals. This function can optionally take the name of a login as input. If the security context is switched with an EXECUTE AS the impersonated context is used. Only if a login is explicitly defined is a valid value returned. Therefore any login that gains access through group inheritance returns the "public" server role sid.

Return type: INT

SUSER_SID()

Returns the security identification number (SID) for the specified login. When the login is not explicitly passed in the current security context is used. If the security context is switched with an EXECUTE AS the impersonated context is used.

Return type: VARBINARY(85)

ORIGINAL_LOGIN()

Returns the name of the login that connected to the instance of SQL Server. If the security context is switched with an EXECUTE AS statement the original context is used.

Return type: SYSNAME

DATABASE_PRINCIPAL_ID()

Returns the ID number of a principal of the current database. It can optionally take the name of a database principal as input. If the principal name is omitted the current database principal is used. If the security context is switched with an EXECUTE AS the impersonated context is used.

Return type: INT

Examples

Thats a lot of information to try and digest. Many of the functions would appear to be identical from their descriptions. Its therefore best to show the subtleties of each function with a few examples.

Throughout these examples, I'll be running the following T-SQL to demonstrate each of the functions in various scenarios:

select [Function] = 'user' ,Result = convert(sql_variant, user) union all
select [Function] = 'current_user' ,Result = convert(sql_variant, current_user) union all
select [Function] = 'sesion_user' ,Result = convert(sql_variant, session_user) union all
select [Function] = 'user_name()' ,Result = convert(sql_variant, user_name()) union all
select [Function] = 'user_id()' ,Result = convert(sql_variant, user_id()) union all 
select [Function] = 'user_sid()' ,Result = convert(sql_variant, user_sid()) union all 
select [Function] = 'system_user' ,Result = convert(sql_variant, system_user) union all 
select [Function] = 'suser_name()' ,Result = convert(sql_variant, suser_name()) union all 
select [Function] = 'suser_sname()' ,Result = convert(sql_variant, suser_sname()) union all 
select [Function] = 'suser_id()' ,Result = convert(sql_variant, suser_id()) union all 
select [Function] = 'suser_sid()' ,Result = convert(sql_variant, suser_sid()) union all 
select [Function] = 'original_login()' ,Result = convert(sql_variant, original_login()) union all
select [Function] = 'database_principal_id()', Result = convert(sql_variant, database_principal_id())

Note: I wouldn't normally advocate the use of the SQL_VARIANT datatype, but in this case its quite useful to concatenate the results into a single output.

To help understand the screen shots here are some notes about the setup/configuration of the SQL Instance.

Login Type	Login Name	Database User
Windows	BARGHEST\WhoAmI	WhoAmI_win
SQL	WhoAmI_std	WhoAmI_db

Changing login impersonation	EXECUTE AS LOGIN = 'WhoAmI_std';
Changing DB impersonation	EXECUTE AS USER = 'WhoAmI_db';

Windows login no impersonation

In this scenario we connect to the instance of SQL Server as a windows authenticated account and run the script.

As you would expect, we see the Windows login used (BARGHEST\WhoAmI) and the associated database user (WhoAmI_win)

Standard login no impersonation

In this scenario we connect to the instance of SQL Server as a SQL authenticated account and run the script.

As you would expect, we see the SQL login used (WhoAmI_std) and the associated database user (WhoAmI_db)

Windows login impersonating the WhoAmI_std login

In this scenario we connect to the instance of SQL Server as a windows authenticated account, change the context to the WhoAmI_std login and run the script.

Of note here is the fact that all functions return the impersonated login and user with the exception of the ORIGINAL_LOGIN() function.

Windows login impersonating the WhoAmI_db user

In this scenario we connect to the instance of SQL Server as a windows authenticated account, change the context to the WhoAmI_db user and run the script.

The expected database user (WhoAmI_db) is returned by the functions, what may be of surprise is that the login (WhoAmI_std) associated with the impersonated user is also returned.

Standard login impersonating a Windows login

In this scenario we connect to the instance of SQL Server as a SQL authenticated account, change the context to the BARGHEST\WhoAmI login and run the script.

Standard login impersonating the db user WhoAmI_db

In this scenario we connect to the instance of SQL Server as a SQL authenticated account, change the context to the WhoAmI_db user and run the script.

Summary

Each function has its own unique facet that make them each useful in their own right. There's a lot to take in with all these functions and I'm danger of leaving you more confused than when we started.

I haven't even explored using the EXECUTE AS functionality in procedures and will save that for another article as I've presented more than enough here!

The general rule of thumb I follow for remembering the function, and I'm aware its not perfect, is this:

If the function ends ID it returns the database principal
It the function ends SID it returns the server principal
If the function ends USER it returns the database user name
If the function ends NAME it returns the server login name

And like learning a foreign language you just have to remember the exceptions (CURRENT_USER and SESSION_USER anyone?)

I recommend you try out each of the functions yourself, though I think you'll find you settle on one or two you use regularly. For example; traditionally I had used SUSER_SNAME() but now use ORIGINAL_LOGIN() to identify who is making the database call. I'm usually only interested in who the person sat at the keyboard is and therefore ORIGINAL_LOGIN() is the best choice for this.

Compare database schema (Part 2)

2011-07-11T23:40:00.001+12:00

In Part 1 I showed how you could quickly, and easily, identify objects (specifically procedures) that had changed. However, whilst you could see which objects had changed, it wasn't clear what had changed.

From the first article we saw that the dbo.uspGetBillOfMaterials procedure had changed. Today I'm going to show you how we can identify which line has changed in that particular procedure. To do this, we'll be using a slightly modified version of the "Tally Oh CSV splitter" by Jeff Moden. If you haven't read that article I strongly suggest you do - especially if you haven't come across Tally Tables or Number Tables before. They are a very useful SQL trick for "set based iterations".

But back to identifying what has changed in our procedure...

I had made a small modification to the "production" version of the procedure. All I have done is add a line that is a comment and changed the MAXRECURSION option at the bottom of the procedure. Here's the last few lines of each procedure to demonstrate:

Original Version:

-- Outer select from the CTE
    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
    FROM [BOM_cte] b
    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
    OPTION (MAXRECURSION 25)

Modified Version:

-- Outer select from the CTE
    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
    FROM [BOM_cte] b
    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
 /* Change the MAXRECURSION from 25 to 30 */
    OPTION (MAXRECURSION 30)

How can we go about finding those "different" lines? Enter the afore mentioned "Tally Oh" article.

I'm going to cheat a little and create the dbo.DelimitedSplit8K function in each of the databases concerned. Once created we can run the following on each of the databases:

select
 ItemNumber  as LineNumber
 ,Item   as LineText
 ,checksum(Item) as LineChecksum
from
 dbo.DelimitedSplit8K(object_definition(object_id('dbo.uspGetBillOfMaterials')), char(13));

What we are doing here is splitting the code by the carriage return character: char(13).

This gives us output resembling:

On a long procedure its going to be hard to find the changed lines. So take the output and paste it into Excel (or your spreadsheet software of choice) and with the simplest of formulas

=IF(C2<>F2,"Different!","")

We get:

Not the prettiest of tools by a long stretch...but we have found the different lines.

Hopefully if you're stuck in the field without access to some handy schema compare tools you'll save some time finding differences in code by applying the checksum across your db!

This doesn't mark the end of this series, I'm exploring some other options and ideas and intend to blog about those in due course - unfortunately my job keeps getting in the way. And we haven't even covered table differences - which can't be had at with the object_definition function.

Compare database schema (Part 1)

2011-07-06T23:00:00.003+12:00

The idea for this series of blog posts stems from the fact that not all database administrators and developers get to have access to great tools such as:

Even though many of these tools are superb value for money and improve your productivity immensely it can be hard to convince your boss to pay for them.

So what can we do when our purchase order is rejected? And lets face it; in today's climate its harder to get purchase sign off.

Well there are a couple of Codeplex projects that are worth considering:

The price of those is hard to beat!

But then perhaps you're working in an environment where installing anything on your work PC is a big "no no!" This is the situation I am currently in and as I'm working as a contractor I really have to honour the policies the company has in place. Believe me the company has them in place for good reason.

So you're stuck with the SQL Server tools and not much else.

For part 1 of this series I have a quick little piece of code we can use to see whether an object's definition is the same between two versions of the database. I have taken a copy of AdventureWorks2008R2 and created two versions on the same server, but they could just as easily have been on different servers. I have amended one of the procedures by adding a comment.

How can I quickly tell if they're the same or not:

select
    object_schema_name(object_id)     as schema_name
    ,object_name(object_id)       as object_name
    ,checksum(object_definition(object_id))   as code_checksum
from
    sys.procedures
order by
    object_schema_name(object_id)
    ,object_name(object_id)

If I run this on both copies of the database I get:

and

If you look closely you'll see that dbo.uspGetBillOfMaterials have different checksums. Therefore there's something different about their definition. Sure we don't yet know what is different, but at least we only have to compare one procedure and not all of them!

In future entries for this series I plan to show how we can make the comparison of many objects easier and I hope to come up with a way to automate this (but I'm still working on that!).

In the meantime, if you know of some good free or very nearly free comparison tools post them in the comments. I'd like to do bit of an evaluation and do a "round up" post on them as I'm always looking for ways to make my job easier.

And have a think about how else you might use the checksum functions - they seem to get unfairly neglected in my experience and yet they're so handy.

Thanks for reading.

Count of occurences in a string

2011-07-04T22:26:00.002+12:00

Recently a colleague was importing a pipe delimited file into a table. The file was supposed to be "clean" but they were getting data appearing in the wrong columns and all kinds of data type mismatch errors being thrown all over the place. They suspected there were too many fields in some rows being sent through the file (e.g. they were expecting 10 pipe characters, but were getting 11...or more!). They imported the file into a single column table and then wanted to count the pipe characters.

My initial thought was "strip out the characters you don't want then grab the length of the resulting string". Something akin to this:

declare @String varchar(max);

set @String = 'The quick |brown fox |jumped over |the lazy |dog';

select len(@String) as OriginalLength,  len(replace(replace(replace(@String, 'a', ''), 'b', ''), 'c', '')) as Characters;
go

That approach was going to get "gnarly" way too quickly. So I flipped the problem on its head...

Strip out the characters you want to count then find the difference in the original and new string lengths. Here's the code I came up with for that approach:

declare @String varchar(max);

set @String = 'The quick |brown fox |jumped over |the lazy |dog';

select len(@String) as OriginalLength, len(@String) - len(replace(@String, '|', '')) as Characters;
go

First impressions were great! This was too easy. It was actually enough to solve the original problem. However after some further testing I found it contains one tiny, but critical floor. After removing the search character, if the string ends with whitespace characters (space, tab, carriage return etc) it returns incorrect results. Here is an examples to illustrate this:

declare @SampleData table(
 StringData   varchar(max)
 ,PipeCount int
 );

insert into @SampleData(StringData, PipeCount)
values
 ('The quick |brown fox |jumped over |the lazy |dog', 4) -- good count
 ,('The quick |brown fox |jumped over |the lazy |dog ', 4) -- good count
 ,('The quick |brown fox |jumped over |the lazy |dog|', 5) -- good count
 ,('|The quick |brown fox |jumped over |the lazy |dog', 5) -- good count
 ,('The quick |brown fox |jumped over |the lazy |dog |', 5) -- bad count
 ,('| The quick |brown fox |jumped over |the lazy |dog', 5) -- good count
 ;

select PipeCount, len(StringData) - len(replace(StringData, '|', '')) as CalcCount
from
 @SampleData;

go

Results

As you'll see Row 5 counts an extra character and further whitespace characters at the end of the data would increase this discrepancy. Note that a leading space in Row 6 is not truncated due to trailing spaces only truncated in a varchar column or variable.

I then remembered that the datalength function counts the whitespace characters so with a slight tweak we get:

declare @SampleData table(
 StringData   varchar(max)
 ,PipeCount int
 );

insert into @SampleData(StringData, PipeCount)
values
 ('The quick |brown fox |jumped over |the lazy |dog', 4) -- good count
 ,('The quick |brown fox |jumped over |the lazy |dog ', 4) -- good count
 ,('The quick |brown fox |jumped over |the lazy |dog|', 5) -- good count
 ,('|The quick |brown fox |jumped over |the lazy |dog', 5) -- good count
 ,('The quick |brown fox |jumped over |the lazy |dog |', 5) -- bad count
 ,('| The quick |brown fox |jumped over |the lazy |dog', 5) -- good count
 ;

select PipeCount, datalength(StringData) - datalength(replace(StringData, '|', '')) as CalcCount
from
 @SampleData;

go

Results:

Success! And a nifty little string occurrences counting routine.

What I particularly like about this approach (aside from the lack of looping) is that it can count any length string within another string - not just individual characters. You can apply it to set based queries without worrying too much about performance. I did do some experiments with performance...I might save that for a part 2 post though.

I could turn this into a UDF for you but I'm sure you're more than capable of that. Hands up who's got the while loop UDF solution to this problem? C'mon...own up! We've all done it :-) However, for your sake and your users sake please replace it with the code above!

And we're off...

2011-06-30T21:27:00.002+12:00

This blog has to start somewhere, so what better way than to describe myself and what I hope to achieve through this blog.

A tiny bit about me...

I'm a SQL Server consultant/contractor/dba (take your pick for today). I first experienced SQL Server some time in the mid to late 90's writing a web based front end for a components database during a summer job whilst at university

That summer job landed me my first "real job" after university back at that company where I started out as a NT administrator. We had a SQL Server 6.5 box that seemed to need a lot of attention from time to time and that piqued my interest in becoming a DBA. I guess that makes me another accidental DBA originally.

Over the years I've worked in energy, financial, telecommunication and consulting companies. Sometime amongst all those roles I moved to New Zealand (Auckland). Most recently I've set up my own consulting company to strive out on my own. We'll see how that goes!

I recently had the opportunity to take up the "2IC" post for the Auckland SQL Users Group - thanks Dave! So you can catch me there (I look like that dude on the right) or feel free to drop me a line via twitter @sqlconcepts.

What I hope to achieve with this blog

In my "more than a decade" in working with SQL Server I've leveraged a lot off the community. And in short I want to put something back there. This blog is part of that process.

Maybe it will spark some debate from time to time, it may serve as a reference for some of my pieces of code (watch out for a "what's in my toolbox" blog article I'm pondering over)...who knows. But if I manage to help a few people make their jobs a little easier, look at a solution another way or teach them something they simply didn't know before I'll feel I've accomplished what I want from this blog.

So what things will we be covering? Essentially anything around SQL Server. It'll mostly be technical covering many aspects of SQL Server such as SSAS, SSRS, SSIS etc and not just the DB engine.

I'm new to this. Be gentle with me.