Claypole's World - The SQL Server Side

SQLBits III - Registration is open!!!

James.Rowland-Jones — Fri, 18 Jul 2008 13:33:13 GMT

This morning the guys opened up registration for SQLBits III to be held in Hatfield on 13th September.

In an incredible morning there has been over 100 successful registrations on the site - that's great going over 25% full on day 1.

Just to remind you all this is a free to attend day by the SQL Server community for the community.

I have cut and pasted the instructions below for those of you who haven't received the email from the UK SQL Server User Group.

Also open today is the session voting and the world famous logo competition. So head on over to SQLBits and register!

Cheers, James

Dear Claypole,

Registration is now open for SQLBits III.

We have a staggering 63 sessions submitted for this conference far more than the previous two. Because of this and to ensure you get to see the sessions you want to see we have setup the registration process as follows

Vote on your top 10 sessions
Register for the conference

So to register for SQLBits III get voting on your top 10 sessions

Training Day

We have also introduced this time a training day on the Friday before. We have 4 full day seminars available for more details go to the training day page on the site

Logo Competition

And finally but not least, you may have noticed the plain logo at the top of this email. We have decided to get a new logo and asked the community to get drawing. The logos have now been submitted and can be voted on. The logo with the most 5 star votes will win an xBox 360.

So get voting on your favourite logo

That's all for now and we hope to see you soon in Hatfield.

Regards
SQL Bits Organising Committee

del.icio.us Tags: Community,SQLBits,UK SQL Server User Group

ISNULL() <> COALESCE(). Discuss.

James.Rowland-Jones — Fri, 18 Jul 2008 09:30:58 GMT

Crikey. That title sounds like an essay I had to write when I was at school and that was a long time ago...

Jamie Thomson raised an important point in a comment on a blog I posted yesterday entitled "Getting rid of getdate()". In it I stated that ISNULL() and COALESCE() are in the same boat as getdate() and CURRENT_TIMESTAMP. Both COALESCE() and CURRENT_TIMESTAMP are ANSI standard functions whereas their corollary T-SQL specific functions, ISNULL() and GETDATE() are not. However, Jamie pointed out that ISNULL() does have a behaviour difference to COALESCE(). That set me thinking as to what if any other differences there were between these two functions. Guess what I found one!

In Books On-line it states a key difference between the two functions. When you COALESCE() an expression the value coalesced is Nullable. Whereas if you used ISNULL then the value is NOT NULL. This shows up when using these functions as computed columns. I have to say this isn't something I do a lot but ignorance is no excuse in the eyes of the law and so I am not going to start making excuses now (although...I think I just have)...

So to take the Books On-line example:

CREATE TABLE #CheckSumTest 
(
        ID int identity ,
        Num int DEFAULT ( RAND() * 100 ) ,
        RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 )  PRIMARY KEY
)

Produces an error:

Msg 1711, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on column 'RowCheckSum' in table '#CheckSumTest'. The computed column has to be persisted and not nullable.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Whereas this (note now using ISNULL()) does not:

CREATE TABLE #CheckSumTest 
(
        ID int identity ,
        Num int DEFAULT ( RAND() * 100 ) ,
        RowCheckSum AS ISNULL( CHECKSUM( id , num ) , 0 )  PRIMARY KEY
)

So there we have it a difference. This is not the end of the story though. It is still possible to use COALESCE() in this scenario. You need to use the PERSISTED key word. Like this:

CREATE TABLE #CheckSumTest
(
        ID int identity ,
        Num int DEFAULT ( RAND() * 100 ) ,
        RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
)

So functionally it can be the same. Note the clue in the error message about persistence. Functionally the same result can be achieved with either function and we can still safely dispose of ISNULL(). Hoorah.

This however brings me nicely onto Jamie's comment. He pointed me at an article that shows an example of ISNULL() and COALESCE() producing different results.

The example was:

DECLARE @value VARCHAR(2)
DECLARE @result1 VARCHAR(4)
DECLARE @result2 VARCHAR(4)

SELECT @result1 = ISNULL(@value,'test')
SELECT @result2 = COALESCE(@value,'test')

SELECT @result1 AS Result
UNION ALL 
SELECT @result2 AS Result

With the result being:

Yuck. That to me has all the hallmarks of being a bug! Cards on the table time. I can't see how the definition of one variable should influence the output of the ISNULL()/COALESCE(). To me ISNULL is broken - each value separated by the comma is isolated and the test being performed is whether the value contained within either is or isn't null. The data type shouldn't have anything to do with it and certainly shouldn't have a bearing on the overall outcome of the function. This gives me greater reason to exclude the usage of ISNULL from a standards perspective. If I saw a developer actually try and 'leverage' this feature I'd ask for the code to be re-written as I expect there'd have been a better way to write the same thing. In my own mind therefore I will continue to exclude the usage of ISNULL().

However, many thanks to JT for pointing this out - it's certainly a gotcha that I hadn't previously appreciated.

I am off to connect now to file some "feedback" on this feature... I'll update you in due course depending on what the powers say.

Cheers, James

del.icio.us Tags: Database Development,SQL Server,T-SQL

Here and Now()

James.Rowland-Jones — Thu, 17 Jul 2008 09:39:00 GMT

AAAaaaaah...Yesterday was just one of those days. I was trying to do something very simple in a script task - assign the current datetime value to one of my variables - and was pulling my stubbly hair out trying to do it.

I am happy to confess to not being the strongest .netter in the world but we do also in SSIS get a pretty pony script editor in which to do our work. Anyways, I digress. Why was I finding this so hard?

Well I had written this.

Dts.Variables("myvariable") = Now.Date()

Which is fine if you just want the date. I didn't I wanted the time component as well. So I dutifully edited my code deleting the Date() bit to get me back to here bring up the intellisense.

Dts.Variables("myvariable") = Now.

I scanned the drop down looking for the date time option I needed and I couldn't find it anywhere! How rubbish is that! I could see every other imaginable component of the date but not the full datetime. My brain then did an odd thing.

I thought perhaps I need to build up a string using all these properties to get to my date time. That would be RUBBISH I thought but would probably get me to where I needed to be. However, fortunately for me, Simon Sabin was online and so I asked him about my problem.

"How about just using Now()" he said. "Does that work" I replied. "I thought I needed to use a property!". Simon proceeded to wet himself laughing at my little misfortune.

Sure enough I tried it and

Dts.Variables("myvariable") = Now()

sets the variable to the current date time. Magic. I had completely forgotten that the rest of this stuff is an inheritance of the base object. At least I assume that is what is going on. At the end of the day I am very glad I have a C# fundamentals course booked in as part of my training plan. Roll on SQL 2008, C# in SSIS and a dream of better editors to make up for my stunning ignorance.

Cheers, James

del.icio.us Tags: .net,database development,sql server 2005,sqlbits,ssis

Getting rid of Getdate()...

James.Rowland-Jones — Thu, 17 Jul 2008 09:32:08 GMT

Ok I admit - I use it. Pretty much everyone I know uses it and yet I feel dirty when I do.

Why? Well it's not portable SQL and most certainly not ANSI standard. I need to get the datetime I hear you say. How do I do this without getdate()?

There is a perfectly good substitute in the form of CURRENT_TIMESTAMP that is ANSI standard and therefore portable. It's in SQL Server and works exactly the same as getdate(). So why don't we all use it?

Based on my personal experience, my guess is that it is longer to write and more cumbersome to say. However, I don't feel I can excuse myself when, as a result of my actions, the SQL I have written has been tightly bound to SQL Server and it needn't have been the case.

I find I am much more militant about the use of ISNULL() rather than COALESCE(). It's the same deal. COALESCE() is portable and functionally the same as ISNULL(). So why do so many people use ISNULL()? I have no idea. I always use COALESCE() but then I also prefer the word :o).

I don't seriously expect the SQL Server product team to drop these functions; mainly because of the body of legacy code out there. I'd love it if they did - once I had fixed all of my code...

However, I do think we should all make more effort to keep our code portable. What do you think?

Cheers, James

del.icio.us Tags: SQL,T-SQL,SQL Server,Database Development

SQL Server Database Recovery Model: How do you set yours?

James.Rowland-Jones — Tue, 15 Jul 2008 09:07:27 GMT

We had the classic situation come up on a project the other day. The recovery models on the databases were set to full and yet the backup strategy didn't reflect this. Needless to say, we ran out of disk space on our build server. In this case the answer was to change the recovery models over to simple. However, I wanted to do this quickly and painlessly. This is how I did it.

First of all I confirmed that this was indeed the case by checking all the recovery models on the server.

Select   [name]
        ,state_desc 
        ,recovery_model_desc 
From    sys.databases

(Stunning graphical editing powers to protect the innocent I know. Perhaps I should be having a word with Matt and our amazing creative team... )

This told me that as I suspected every database save tempdb was in the full recovery model. N.B. tempdb as we all know is "special". It can only be in the simple recovery model and if you try to change it then you will get the following error.

Msg 5058, Level 16, State 1, Line 1
Option 'RECOVERY' cannot be set in database 'tempdb'.

So how did I change this. I needed to accomplish two things.

Change a lot of databases to the simple recovery model
Leave tempdb alone to avoid raising the error message

I opted to use my old friend sp_MSforeachdb. sp_MSforeachdb is not documented in Books Online so I guess it's not officially supported. However, it is extremely useful at times like this. Sp_MSforeachdb opens up an OLEDB connection to the sql server and loops through the DB's so that you can execute a statement against each one. Yes that does mean a cursor - but at least I haven't had to write all that logic. However, because it does it this way you can also parameterise your script using the ? as a parameter. If this proc has escaped your attention up till now I am sure you will also be delighted to know that it has a little sister called sp_MSforeachtable. I'll let you work out what that does. However, there is a pretty good write up here on Database Journal.

So anyways back to the story. I was left with a script that looked like this:

exec sp_msforeachdb 'IF ''?'' <> ''tempdb'' Begin EXEC('' alter database [?] set recovery simple '') print ''?'' + '' recovery model altered '' End'

For the keenly observant you will notice that I have put my alter database command inside an exec statement. This was due to the fact that SQL Server continued to raise the error mentioned above even though I had specifically coded around the problem. I will go into more detail on why this happens in another post.

A quick look at sys.databases confirms that all the recovery models are now set to simple. Now all that needs to be done is for the log files to be re-sized...

(hey where did the blue highlighting go... oh that's not very consistent...)

Cheers, James

del.icio.us Tags: Database Administration,SQLServer

Synchronous and Asynchronous Shopping

James.Rowland-Jones — Fri, 11 Jul 2008 09:06:28 GMT

A couple of days ago I was asked to explain the difference between synchronous and asynchronous I/O. This was something that I covered last year at SQLBits II in my presentation "I/O! I/O! It's off to work we go...". However, when I was asked again the other day I thought of a rather good analogy.

Synchronous Shopping

It's Saturday and I need to go to Waitrose. I get in the car and head off to my nearest store in sunny Dartford. I pick up my trolley and look at the shopping list. Pasta, Sauce, and mince for the bolognese. No prizes for guessing what we are having for dinner (unlike the SQLBits logo competition). First of all I make my way to the pasta aisle. I look at my options and select the spaghetti. Secondly, I glide over to the sauces and select something wholesome and organic that still comes out of a jar (I am no Gordon Ramsay). Finally, over to the meat counter to pick up some nice mince. With all my products in the basket I proceed to the checkout and make my way back home. This is "Synchronous Shopping": simple, structured, sequential & calm.

Asynchronous Shopping

It's Saturday again and Spag bol is back on the menu and I need to get to Waitrose - with the kids. We have an argument who is going to sit in the front and who is in the back, what music will be played from which ipod. With the "Robots in Disguise" blaring out of my largely defunct stereo we head off to Waitrose. We get the trolley and armed with my little helpers we enter the store. "Lucy go and get the Pasta" and off she goes as I make my way over to the sweets aisle (asynchronous help isn't free). "Kate can you get the sauce?". Finally, "Oliver can you please pick me some mince". Oliver is six years old so this is more of a hope based request. As I make my way round the store my helpers start to return. Kate has picked something tomato-ey but with mushrooms in that is sure to delight her siblings. Lucy returns next with some tagliatelle. Interesting choice. Finally, Oliver returns and as if by magic this coincides with my arrival in the sweetie aisle. However, he has mince so all is good with the world. Welcome to "Asynchronous Shopping": complex, unstructured, non-sequential & hectic. It can however be a lot quicker than when I do it by myself.

I/O

So how does this relate back to I/O? Well your asynchronous reads and writes are to your data files whilst your synchronous activity is to the transaction log. Your synchronous I/O is structured and sequential. It uses the standard readfile and writefile api's. This is why we put this file onto mirrored drives where the disk head can simply move in one direction. Asynchronous I/O is different. It uses different API's (ReadfileEx and WritefileEx). It fires off requests but doesn't wait for a response before moving on. When an asynchronous I/O is required the task that requested it is put into a pending state and taken off the scheduler whilst it waits for the I/O to complete. Something else is moved onto the scheduler in the meantime. Once the task is complete SQL Server is notified the operation is now runnable and can get put back onto the cpu by the scheduler when the yield for the task that has been running occurs.

Service Broker

Service broker is an asynchronous shopper. However, I won't go into that now. I hope that this will be one of the sessions selected for SQLBits III and I'll get to talk about it then. The guys are getting ready to open the voting for sessions and abstracts as well as the voting for the world famous SQLBits Logo competition. I will update you all when the voting goes live but expect an announcement soon.

Cheers, James

del.icio.us Tags: SQLBits,SQL Server,SQLServer I/O,Community

SQL Security Patch Build is CU6+ but not quite CU7

James.Rowland-Jones — Thu, 10 Jul 2008 10:40:44 GMT

So as suspected when I installed the new Security patch released by Microsoft for SQL 2005 sp2 that I mentioned this morning my build number has indeed shot up to 3233.

This places me somewhere between CU6 and CU7. Build 9.00.3228 was CU6 and 9.00.3239 is CU7.

So just bear in mind that when you plop on your patch you are actually taking a number of other fixes through with you. Test...Test... Test...

If you are interested in seeing what published builds are out there I know of no better resource than sqlsecurity.com's version database which you can find here.

Cheers, JRJ

del.icio.us Tags: Security,SQL Server 2005,SQL Server,Sp2

SQL Server and Windows Security patch posted

James.Rowland-Jones — Thu, 10 Jul 2008 08:33:40 GMT

And so the great run comes to an end...

For the full details of this incident please click here. N.B. This also affects Windows. However this is released under a different KB article.

For the SQL 2005 sp2 KB article look at this link http://support.microsoft.com/kb/948108

For SQL 2000 GDR and MSDE 2000 KB article look at this one http://support.microsoft.com/?kbid=948110

If the Windows article is what you are after click here http://support.microsoft.com/?kbid=948109

The post-sp2 security patch popped up on my radar via my Automatic Updates (I knew there was a reason I looked at the Advanced Custom Settings first)...

It applies to pretty much all versions of SQL 2005 with sp2 installed. What a dog's breakfast sp2 was. It looks like it takes the build number up to 3233. However, as I have mentioned this also affects

The situation with windows is also interesting. The component affected in Windows is MSDE for windows 2000 and 2003 on 32 bit. However, for those running the WYUKON Windows Internal Database component in Windows 2003 or if you are on the bleeding edge on Windows 2008 then you are also impacted.

Windows 2008 is particularly interesting. The article states that the "Server Core Installation (...is...) affected. ... This applies...whether or not Windows Server 2008 was installed using the Server Core installation option."

If you'd like to know more about Server Core then here is a link.

There are also some known issues with the installs but these are covered in the link above.

Those links again:

http://support.microsoft.com/kb/948108

http://support.microsoft.com/?kbid=948110

http://support.microsoft.com/?kbid=948109

It has been a great run for SQL Server and Microsoft on the security side of the equation. Slammer taught Microsoft an invaluable lesson and it is a testament to the team that ever since that horrific week of callout when we all went patch-tastic there has been a dearth of security based issues especially in the server products. However, I fear the counter now has to be reset pretty much across the board for Windows and SQL Server. Let's hope we have to wait as long for the next one.

Cheers, JRJ

del.icio.us Tags: Windows 2008,Windows 2003,Windows 2000,SQL Server 2000,SQL Server 2005,SQL Server 2005 sp2,Security

Resources for Spatial Data in SQL Server 2008

James.Rowland-Jones — Wed, 09 Jul 2008 08:14:19 GMT

As you may be aware we recently hosted a meeting of the UK SQL Server User Group featuring Michael Rys.

For those of you who weren't able to jot down the resources he mentioned in his presentation I have copied and pasted them in below. Thanks to Michael for posting them through - I didn't have time either! :o)

Spatial Site: .
http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx

Forum:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1629&SiteID=1

Weblogs:

http://blogs.msdn.com/isaac

http://blogs.msdn.com/edkatibah

Cheers, James

del.icio.us Tags: Community,SQL Server,SQL Server 2008,Spatial Data,UK SQL Server User Group

VISIO 2007 add-in for SQL Server Infrastructure Diagrams

James.Rowland-Jones — Tue, 08 Jul 2008 23:33:06 GMT

Here's a little freebie from Microsoft for those of you who have the job of drawing out infrastructure diagrams of SQL Server farms. Ok that already sounds like a pretty niche market. How about here's a little freebie from Microsoft for those of you that should have diagrams of your SQL Server Infrastructure but don't have the time... Ah yes. That old chestnut. Well help is at hand in the form of a (relatively) new add-in to Visio 2007 that is free download from the mighty M$ here.

To cut a fairly dull story short basically there is a read-me in the download that will tell you all your pre-requisites (.net 2.0 and some dlls that I just hope are installed when I installed visio) and also give a pretty thorough overview of the tool and how to use it.

However, to show you how easy it is to use here is my not so thorough version.

Step 1: Fire up Visio Start/All Programs/...

<No Picture for this>

Step 2: Look to the top!

Step 3: Go Configure

<No Picture for this either>

Step 4: A Wizard! Shock ... Horror

Clicking Next about now would be a good thing

Step 5: Your Servers .. Count em

Think of a number...

Again Next is recommended at this point

Step 6: Things start to get interesting

Pick your Server (Slightly ropey ordering I have to say)
Identify your SQL Server Version (Oh it's already got SQL Server 2008... nice touch)
OS is next... Windows 2008 I think (and now things don't look so clever) Beta 3? We've RTM'ed here luv.
Finally SQL Edition. Oh dear Developer Edition only. I have had a play about here with these settings and I can't get it off developer edition in this drop down for SQL 2008.

Then you Click Save and do it for the next server. You'll get asked if you really meant that in a Vista stylee but hey ho that's the way of the world nowadays.

N.B. A few little pecularities emerge.

If you want to select a cluster be very sure to select at least two servers before progressing to select sql server versions. If you fail to do this you will not be able to select the "Enable Clusters for SQL Servers" check box that I have cunningly hidden with my drop down for Step 6.

Step 6a the clustering checkbox revealed....

Ooo look at that! Nodes a plenty and storage options too..

Step 7 Repeat until complete

Then hit Next >

Step 8 Time for the Summary

A screen that performs next to no real purpose other than showing off my affinity for Enterprise Edition. I guess you could delete records but after spending that time putting in all that info why ever would you want to do that...

Click Next > (Please let it end)

Step 9 Sweet Relief

Visio is finally doing some work and we are about to see the fruits of our labour...

Step 10 We must be there now!

Sadly not. Unfortunately, I require a screen to tell me that Visio hasn't crashed.

Out of morbid curiosity I press close

Step 11 The Reveal

And there you have it. In only 11 easy steps...

Notice that by clicking on a shape and looking at the shape data out pops all that metadata we spent ages adding in. The good news is that this data is totally amendable so I can RTM my windows 2008 server which my clients will be pleased about.

Anyways if you fancy a play you can always download a copy yourself. It is better than nothing I wouldn't say by much but it is an albeit small step in making Visio useful to a much wider audience. I find only Microsoft Project to be more unwieldy than Visio. Perhaps that's just me...

Visio needs shapes and addin's like this in my view that anyone can download and install. The frustrating thing I find is that it could be such a useful tool - but often it just winds me up.

It would also be a lot more useful if it was updated to have all the latest and greatest Server OS and SQL Server combinations. Given how new this download is I'd say it's a shame that it's content is already out of date.

Cheers, JRJ

Link:

The software : http://www.microsoft.com/DOWNLOADS/details.aspx?familyid=779561DE-C704-4584-80AC-7E4348C927C0&displaylang=en

del.icio.us Tags: Visio 2007,SQL Server,SQL Server 2008,Database Administration

Using a Bitmask - a practical example

James.Rowland-Jones — Fri, 04 Jul 2008 22:28:51 GMT

A colleague of mine asked for a simple example to get him up and running with the basics of bitmasking. Since I had gone to the effort to create the example I thought I might as well blog it :o).

This example uses the logical AND operator &.

First of all you need to define your table..

create table BitmaskDemo
(ID int Primary key
,Supermarket varchar(10)
,bitmask varbinary(1000)
)

and then secondly assign a numeric value to your data values. I have used numbers in the power of 2 in this example. This ensures that every value gets assigned a unique number and that the sum of these numbers will always generate a unique combination. So bananas and oranges = 5.

/*
values
1 = Banana              00000001
2 = Strawberries        00000010
4 = Oranges             00000100
8 = Apples              00001000
16 = plums              00010000
32 = pineapple          00100000
*/

We are now ready to populate the table with some data

Insert into Bitmaskdemo
values (1,'Tesco',7) -- Bananas,strawberries & oranges 00000111
Insert into Bitmaskdemo
values (2,'Sainsburys',31) -- Bananas, strawberries, oranges, apples & plums 00011111
Insert into Bitmaskdemo
values (3,'Morrissons',8) -- Apples 00001000
Insert into Bitmaskdemo
values (4,'Waitrose',24) -- Apples and plums 00011000
Insert into BitmaskDemo
values (5,'Aldi',25) -- Bananas, Apples and Plums 00011001
Insert into BitmaskDemo
values (6,'Somerfield',9) -- Bananas & Apples 00001001

Finally we can now ask some questions

/* Who sells strawberries */
Select *
from BitmaskDemo
where Bitmask & 2 = 2

/* Who sells Bananas & Plums */
Select *
from BitmaskDemo
where Bitmask & 17 = 17

/* Who sells Bananas & Plums */
Select *
from BitmaskDemo
where Bitmask & 17 = 17

/* Who doesn't sell bananas  */ 
Select * 
from BitmaskDemo 
where Bitmask & 1 <> 1

/* Who Sells Bananas or Pineapples */ 
select * 
from BitmaskDemo 
where bitmask & 33 = 1 
OR bitmask & 33 = 32

/* Who Sells Apples but does not sell plums */ 
select * 
from BitmaskDemo 
where bitmask & 24 = 8 
AND bitmask & 24 <> 24

Hope this helps anyone else trying to get their head round bitmasking! If there are any other amendments or suggestions that people want to make.

Cheers, JRJ

del.icio.us Tags: Database Development,SQL,SQL Server,T-SQL,Bitmask

SQLBits III Session Submissions Deadline Update

James.Rowland-Jones — Fri, 04 Jul 2008 20:40:18 GMT

In an earlier post I mentioned that the 4th July had been set as the deadline for submission of session abstracts. This deadline has now been extended till July 11th as there are still a number of people who we are waiting to hear from. For those of you still mulling over your session submissions you now have exactly a week to get your thoughts together, take the plunge and put your hat in the ring (can't think of any more cliche's right now) for SQLBits III.

I presented at SQLBits II and I have to say I thoroughly enjoyed the experience. If you are interested but are nervous / unsure or would like to talk to me about it first then please feel free to drop me a note. I'd be more than happy to share my experiences with you and help get you started if you are feeling a bit nervous about taking your maiden voyage (found one last one to end with :o)).

Cheers, JRJ

del.icio.us Tags: Community,SQL Server,SQL Server 2008,SQLBits,UK SQL Server User Group

Spatial Data Eye Candy

James.Rowland-Jones — Fri, 04 Jul 2008 08:40:51 GMT

In an earlier post I mentioned the new visualisation functionality for spatial data. Michael Rys kindly pointed me in the direction of Isaac's blog that has some pictures of the new "Spatial Data" tab and resulting visualisation. As confirmed in the comments below the blog this is using some of the Dundas controls that Microsoft recently procured.

So if you haven't seen them yet - check out this post from Isaac.

Nice work guys.

Cheers, James

del.icio.us Tags: Michael Rys,Spatial Data,SQL Server 2008,Database Development

Want an XBOX 360 for free? Then enter the SQLBits Logo Competition...

James.Rowland-Jones — Thu, 03 Jul 2008 17:35:32 GMT

That's right! The prize for coming up with the winning logo for SQLBits is an XBOX 360! Not only do you get the console but also the satisfaction of seeing your logo emblazoned across the internet and also (hopefully) sunny Hertfordshire on 13th September when SQLBits III rolls into town...

It get's better. At the moment there are less than 10 logos in the competition. So that's a 1 in 10 chance of walking away with the juice. Not even Betfair has those odds...

What are you waiting for?

http://www.sqlbits.com/LogoCompetition.aspx

Cheers, James

del.icio.us Tags: competition,SQLBits,Community,UK SQL Server User Group

T-SQL IDENTITY : Fun and Games with DBCC CHECKIDENT

James.Rowland-Jones — Thu, 03 Jul 2008 17:26:59 GMT

This issue caught me out today whilst putting together some build scripts for reference data. So I thought I’d share it.

USE [tempdb]

CREATE TABLE [dbo].[Table_1](

[Col1] [int] IDENTITY(1,1) NOT NULL,

[Col2] [char](1) NOT NULL

) ON [PRIMARY]

delete from dbo.table_1

dbcc checkident(Table_1,Reseed,0)

insert into table_1

values('1')

--Checking identity information: current identity value 'NULL', current column value '0'.

--DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Select * from table_1

Basically when you first create a table with an identity column the value of the identity prior to inserting any data is NULL and not 0.

Therefore the first DBCC CHECKIDENT does not behave as expected.

The first row you write has an identity value of 0 and not 1.

Now Run it again and the problem magically disappears…(the helpful messages in green didn’t come out in the build scripts – at least I didn’t see them.)

delete from dbo.table_1

dbcc checkident(Table_1,Reseed,0)

insert into table_1

values('1')

--Checking identity information: current identity value '0', current column value '0'.

--DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Select * from table_1

drop table dbo.table_1

I should also point out that I was using Database Edition and this code was in my post-deployment script. I therefore didn't see the messages as it was all executed in msbuild. What was even more interesting was that the build scripts carried on regardless even though as a result of my bug a foreign key violation occurred. More on that in another post methinks...

Cheers, James

del.icio.us Tags: T-SQL,SQL Server,SQL Server2005,DBCC CHECKIDENT,Database Development,Database Edition,Data Dude