Time for another episode of the SQL Friday, The Best SQL Server Links Of The Past Week show. Here is what I found interesting this past week in SQL Land: SQL Server 2008 Auditing Resources Lara Rubbelke points us to some good SQL Server 2008 Auditing Resources Spatial Indexes in SP1, almost no hints required Bob Beauchemin is letting us know that with SQL Server 2008 Service pack 1 Spatial Indexes require almost no hints at all

I was listening to show number 426 on dotnetrocks: Rob Boucher on Application Architecture Guidance! They mentioned the Patterns And Practices Application Architecture Guide 2.0, this guide is available for free on codeplex. Although it is a Microsoft technology centric guide, there should be chapters for every developer in your group. Here is one example from the book Key Design Principles When getting started with your design, bear in mind the key principles that will help you to create architecture that meets “best practices,” minimizes costs and maintenance requirements, and promotes usability and extendibility. The key principles are:

Scott Guthrie (AKA The man!) has posted on his blog that you can download a free ASP.NET MVC eBook Tutorial in pdf format. The book is licensed under a “Creative Commons Attribution No Derivatives” license – this means you can share, distribute, print, or hand it out to anyone. This is what is covered in the book _Create a database Build a model with validation and business rules Implement data listing/details UI on a site using Controllers and Views

For quite a while, now my signature and IM status message has stated, “Backups are for sissies!!!”. Recently I had a coworker laugh about this when they saw it on Google chat. They thought it was hilarious. I couldn’t help but stop them and explain how the statement really wasn’t a joke and in reality was probably the most serious statement I’ve ever branded to my online handle. The true meaning goes deep into disaster and recovery of the systems I manage and put my reputation on the line daily by reaching uptime levels that surpass expectations.

Securing data services is part of a DBA’s critical tasks that should be performed. This goes beyond users, roles and schemas though. A DBA must take into account the concept of software installations in a growing and dynamic environment that often pose serious security threats. It’s very common for software to ship with the desktop version of SQL Server (MSDE2000 or SQL Express). This can be prepackaged and installed, configured and user created on the fly during installation. Most of us with developer experience know exactly how to do this and how easy it has made our jobs as developers at times. You can package up your very own free database server with your application and with a simple command and a small script, configure and have it rolling in no time.

You have an identity column in your database that was created as int identity(1,1). But after a good few years you are approaching the limit for a signed four-byte long integer, 2147483647. What do you do? Switch the column to bigint? Not yet! You do something like this: --What's the biggest signed four-byte long: --select power(convert(bigint, 2), 31) - 1 --2147483647 --what your table looks like CREATE TABLE tester ( testerid INT IDENTITY(1, 1) not null CONSTRAINT pk_tester PRIMARY KEY CLUSTERED, descr VARCHAR(100) ) -- simulate the identity column being near the end of its life (only 32 more rows will fit) DBCC checkident(tester, reseed, 2147483616) -- push the identity column to its absolute limit INSERT tester SELECT CONVERT(VARCHAR(100), newid()) WHILE @@ROWCOUNT < 16 INSERT tester SELECT CONVERT(VARCHAR(100), newid()) FROM tester SELECT * FROM tester -- you can see that you can't insert another one like so: -- insert tester select convert(varchar(100), newid()) --Redefine the identity column by moving tables in and out. -- * would have to drop referential constraints temporarily -- * but you can generate script to drop and recreate referential constraints -- move the table out of the way of the new table EXEC SP_RENAME 'tester', 'oldtester' ALTER TABLE oldtester DROP CONSTRAINT pk_tester -- create the new table with identity set to crawl downward from 0 CREATE TABLE tester ( testerid INT IDENTITY(1, -1) not null CONSTRAINT pk_tester PRIMARY KEY CLUSTERED, -- I have no idea why it's not identity(0, -1), but it's not. descr VARCHAR(100) ) -- insert the data from the old table SET IDENTITY_INSERT tester ON INSERT tester (testerid, descr) SELECT testerid, descr FROM oldtester SET IDENTITY_INSERT tester OFF -- insert some rows so we can see if it works INSERT tester SELECT CONVERT(VARCHAR(100), newid()) FROM tester -- and it works! New rows will start at 0 and count downward to -1, -2, and so on. SELECT * FROM tester -- clean up DROP TABLE tester DROP TABLE oldtester With this solution, you can keep your database going for another few years, or for about as long as it took you to fill up the positive integers. Then you’ll have to switch to bigints or a decimal data type, though I have to express my disgust for using a packed data type as a clustered index key, especially considering that compared to int or bigint, decimal always uses more bytes of storage for the same precision:

Time for another episode of the SQL Friday, The Best SQL Server Links Of The Past Week show. Here is what I found interesting this past week in SQL Land: How To Get High Quality Information About Query Performance Elisabeth Redei shows us how you can use the profiler, dynamic management views and SQLNexus to get high quality information about your query performance How It Works: Almost Everything You Wanted To Know About The SQL Server (2005, 2008) Performance Counter Collection Components

I read lots of blogs, More then 40 in total. And sometimes you find one that has an interesting post and then I add that to my google reader list. The next post I found via Jason bock’s blog. It’s about exception handling and the filter posibilities VB.Net has, well actualy the abality is there in the CLR it’s just no implemented in C# as of yet. And not any time soon as it seems.

Last week I got a new computer at work. A real beast I might add. I already wrote about what I wanted a while ago. But after a nice email conversation with the hardware supplier it actually became a bit better than that. Let’s start with the case. A Coolermaster Cosmos 1000. This is a wonderfull case, nice and big and nice and quiet and nice and cool. Even with 5 fans in it you hardly hear it.

So you want to spit out some XML from SQL Server into a file, how can you do that? There are a couple of ways, I will show you how you can do it with SSIS. In the SSIS package you need an Execute SQL Task and a Script Task. Let’s get started First create and populate these two tables in your database create table Artist (ArtistID int primary key not null, ArtistName varchar(38)) go create table Album(AlbumID int primary key not null, ArtistID int not null, AlbumName varchar(100) not null, YearReleased smallint not null) go insert into Artist values(1,'Pink Floyd') insert into Artist values(2,'Incubus') insert into Artist values(3,'Prince') insert into Album values(1,1,'Wish You Were Here',1975) insert into Album values(2,1,'The Wall',1979) insert into Album values(3,3,'Purple Rain',1984) insert into Album values(4,3,'Lotusflow3r',2009) insert into Album values(5,3,'1999',1982) insert into Album values(6,2,'Morning View',2001) insert into Album values(7,2,'Light Grenades',2006) Now create this proc