Sunday, April 30, 2006

Return types of IDENTITY-functions

I ran into some strange inconsistency a couple of days ago when I was reading about identity-columns, -functions and -properties in the SQL Server Books OnLine (BOL).
I was actually only looking for the correct syntax of DBCC CHECKIDENT for reseeding an identity-column of a table, as I was wondering what the datatype of the new_reseed_value has to be.

DBCC CHECKIDENT ('table_name' , RESEED , new_reseed_value)

No big secret though, the datatype of an identity column can be int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0. That explains why the return type for the functions @@IDENTITY and SCOPE_IDENTITY() is numeric, actually numeric(38,0), which is the datatype with the biggest precision of the whole list.
So far so good, but why has the similar function IDENT_CURRENT('table_name') a return type of sql_variant according to the BOL? It just doesn't make sense, certainly because I have no indication that the function actually returns a sql_variant. The way I see it now, IDENT_CURRENT simply returns numeric-data like the other functions, not sql_variant.

USE TempDB
GO
CREATE TABLE tmpTable
(
i NUMERIC(12,0) IDENTITY
)
GO
INSERT tmpTable DEFAULT VALUES
GO
DECLARE @sql_variant SQL_VARIANT

--Determining the datatype, precision and scale
SELECT @sql_variant = SCOPE_IDENTITY()
SELECT
SQL_VARIANT_PROPERTY(@sql_variant, 'BaseType') AS [BaseType],
SQL_VARIANT_PROPERTY(@sql_variant, 'Precision') AS [Precision],
SQL_VARIANT_PROPERTY(@sql_variant, 'Scale') AS [Scale]

--Determining the return type for the identity-functions
SELECT
@@IDENTITY AS [DT_@@IDENTITY],
SCOPE_IDENTITY() AS [DT_SCOPE_IDENTITY],
IDENT_CURRENT('tmpTable') AS [DT_IDENT_CURRENT]
INTO tmpReturnType

EXEC sp_help 'tmpReturnType'
GO
DROP TABLE tmpReturnType
DROP TABLE tmpTable
GO

Friday, April 28, 2006

We went to Devconnections Europe

Just back from Nice where we attended Devconnections Europe! At first I was very enthusiastic about it and in fact the speakers and sessions didn't let me down, but the organizers of the event seemed much less pleased after noticing that the attendance was rather low. But the beautiful city of Nice easily made up for the mess-up with the sessions and the poor service during the breaks.

The session of Itzik Ben-Gan about row numbers (Discover the Power of Row Numbers) was very inspiring. It really made the attendees think about T-SQL and the reason why new additions in 2005 can help us to overcome everyday problems in a more efficient fashion.

Grant Dickinson had a talk about programming against SSIS (Integrating Integration Services into Your Custom Applications) which gave me some ideas. Very hands-on stuff that I tend to use as soon as I see the opportunity.

And then there was the SQL-guru Gert Drapers of whom we attended most sessions (SQL Server 2005 Memory Internals / SQL Server 2005 Bulk Loading / SQL-CLR: A Performance Evaluation / Database Mirroring Inside Out / Exploring and Optimizing SQL Server 2005 Using Dynamic Management Views). Really too much interesting stuff to sum it up, just follow the link http://www.sqldev.net/events.htm ;-)

Last but not least, Matt Nunn gave us some interesting news about the future development on SQL Server and Visual Studio. His session about offline (Occasionally Connected Systems) gave us some input for our future development.

Sunday, April 23, 2006

We are going to Devconnections Europe

I had never thought they would allow us to do so, but we are in fact going to Devconnections Europe! The SQL leads for development and operations and I are leaving for Nice tomorrow for hopefully a very enriching experience.
The subjects for the sessions of the SQL Server Magazine Connections look very interesting to say the least. And the abstracts promise us to treat some in-depth information on all the cool stuff in SQL Server 2005. I’m really looking forward to learn more about database mirroring, performance tuning, service broker, dynamic management views, memory internals, etc.
Experienced speakers, great sessions and a nice setting: Nice, here we come ;-)

Read more about the event and the sessions at http://www.devconnectionseurope.com/

Wednesday, April 05, 2006

Everyone else is blogging, so why can't I?

Hey, if all my colleagues at work can start a technical blog, why can't I? Okay, I for myself can imagine a million of reasons why I shouldn't, but it's already too late, you're reading my first post :-).
All this blog-stuff looks very funky to me now, but I will have a hard time producing some meaningful content. Like my 'favorite colleague' I'm considering this blog primarily as my long term memory of my professional experiences at work and at home.

Quick introduction to conclude my first post: I'm a software engineer, more specifically a SQL Server database developer on a long-running, Microsoft-minded project at a large Belgian company. It's still my first job, already for more than four years now, and still loving it because of the dynamism we try to maintain here.