XPGUID.DLL - Sequential GUID generation and GUID helper functions XP

version: 1.4.0.0

last updated: 27 May 2005

platform: MSDE 2000 or SQL Server 2000 running Windows 2000 and higher

Content:

Description

Usage

Parameters

Return codes

Installation

Uninstall

Version and platform support

Known limitations

FAQ

Troubleshooting

License

Planned functionality enhancements

Realized functionality enhancements

Change log

Description:

XPGUID provides a couple of GUID (uniqueidentifier) related procedures to improve the usage of GUIDs inside SQL Server 2000; most importantly it provides a procedure for generating sequential GUID's, besides that it provides a procedure for testing a string if it represents a valid GUID, without generating an error that will abort your T-SQL batch.

 

The DLL XPGUID hosts three extended stored procedures:

1. xp_guid_new_guid - generates a new GUID using the Win32 call UuidCreate()

2. xp_guid_new_sequential_guid - generates a new sequential GUID using the Win32 call UuidCreateSequential()

3. xp_guid_is_guid - validates if a string contains a valid GUID and optionally returns the GUID as an output parameter

Additionally there are four T-SQL user defined functions which wrap the extended stored procedures:

1. fn_is_guid - validates if a strings contains a valid GUID

2. fn_string2guid - converts a string in to a GUID, returns NULL if the string does not represent a valid GUID

3. fn_new_guid - generates a new GUID like SQL Server 2000 function NEWID()

4. fn_new_sequential_guid - generated a new sequential GUID, like the SQL Server 2005 function NEWSEQUENTIALID()

xp_guid_new_guid and fn_new_guid()

Creates a random GUID, like NEWID() does today. This procedure is mainly implemented so we can compare the cost of the build in function NEWID() with the extended stored procedure implementation. See test 1 and 2 in the xpguid-perf.sql script file which is contained in the xpguid.zip.

 

xp_guid_new_sequential_guid and fn_new-sequential_guid()

Creates a sequential GUID that is greater than any GUID previously generated by this function on a specified computer. Using sequential GUIDs can reduce page contention at the leaf level of indexes which are based on GUIDs, it can also reduce fragmentation (see test 3 in the xpguid-perf.sql script file).

 

Note: GUIDs generated by xp_guid_new_sequential_guid or fn_new-sequential_guid() are unique only within a particular computer if the computer does not have a network card.

 

Note: If privacy is a concern, do not use this function. It is possible to guess the value of the next generated GUID, and therefore access data associated with that GUID.

 

When using sequential GUIDs the order in which rows are inserted remains increasing, while when using normal GUID generation is is completely random. The following example demonstrates this:

 

create table #t1(c1 int not null identity, c2 uniqueidentifier not null)
create table #t2(c1 int not null identity, c2 uniqueidentifier not null)

declare @i int
select  @i = 0

begin tran

while (@i < 10)
begin
    insert into #t1(c2) values(newid())
    insert into #t2(c2) values(fn_new_sequential_guid())
    select @i = @i + 1
end

commit tran

select * from #t1 order by c2
select * from #t2 order by c2

drop table #t1
drop table #t2

 

Results using newid() or fn_new_guid(); the order of c2 is not identical to the order in which rows are inserted (represented by column c1).

c1	c2
1	7376C585-01D9-4EC4-B402-09285864D673
6	8BDECA78-2F96-4AC7-8F53-1C1034EEDA1C
4	D95DAD5C-915E-4096-B0F2-3A9558795950
9	E8542C4F-F514-471B-9034-6657E01259BC
10	A0D3DD97-B07E-48D2-A428-7624FFD83050
8	3AC76ADF-3A41-4348-A207-84FADED174C1
3	F19FB856-62B8-497E-8EF7-B21DB9810A29
5	CFD121A2-69E2-43BF-A9ED-C4D97F8CB5F2
2	DE44F198-9E93-498B-A73D-C7EE8951FA0F
7	2A640830-C497-4B66-88F1-FF8264597DA4

 

Results using fn_new_sequential_guid(), the order of c2 is identical to the order in which the rows were inserted (represented by column c1).

c1	c2
1	304FC21B-A7CE-D911-8506-00096B936E11
2	314FC21B-A7CE-D911-8506-00096B936E11
3	324FC21B-A7CE-D911-8506-00096B936E11
4	334FC21B-A7CE-D911-8506-00096B936E11
5	344FC21B-A7CE-D911-8506-00096B936E11
6	354FC21B-A7CE-D911-8506-00096B936E11
7	364FC21B-A7CE-D911-8506-00096B936E11
8	374FC21B-A7CE-D911-8506-00096B936E11
9	384FC21B-A7CE-D911-8506-00096B936E11
10	394FC21B-A7CE-D911-8506-00096B936E11

 

xp_guid_is_guid, fn_is_guid() and fn_string2guid()

These procedure and functions allow you to check the validity of a GUID represented in the form of a string and optionally convert the string in to a GUID, without raising an error which will abort the batch, what will happen when you use CAST or CONVERT instead.

 

For example:

select convert(uniqueidentifier, N'a94e2f79 c2ad d911 84d3 00096b936e11')

 

Will result in a severity 16 error, which abort the batch

Server: Msg 8169, Level 16, State 2, Line 1
Syntax error converting from a character string to uniqueidentifier.
 

While fn_is_guid will return 0 (zero) in this example:

select fn_is_guid(N'a94e2f79 c2ad d911 84d3 00096b936e11')

 

The usage of the procedures and functions will be discussed in the paragraphs below.

Usage:

XPGUID only ordinal parameters.

 

-- ************************************************************************
-- xp_guid_new_guid

-- ************************************************************************
declare @rc int,                    -- 0 = SUCCESS, 1 = ERROR
        @guid uniqueidentifier

exec    @rc = master.dbo.xp_guid_new_guid @guid output
select  @rc as rc, @guid as guid

go

 

-- ************************************************************************
-- xp_guid_new_sequential_guid
-- ************************************************************************
declare @rc int,                    -- 0 = SUCCESS, 1 = ERROR
        @guid uniqueidentifier

exec @rc = master.dbo.xp_guid_new_sequential_guid @guid output
select @rc as rc, @guid as guid
go
 

-- ************************************************************************
-- xp_guid_is_guid - validation only
-- ************************************************************************
declare @rc int,                   -- 0 = SUCCESS, 1 = ERROR

        @strguid nvarchar(4000),

        @isguid int                -- 0 = FALSE, 1 = TRUE
 

-- set initial values
select @strguid = N'A94E2F79-C2AD-D911-84D3-00096B936E11', @isguid = null

exec   @rc = master.dbo.xp_guid_is_guid @strguid, @isguid output
select @rc as rc, @isguid as isguid,

go

-- ************************************************************************
-- xp_guid_is_guid - validation and convert to GUID
-- ************************************************************************
declare @rc int,                   -- 0 = SUCCESS, 1 = ERROR

        @strguid nvarchar(4000),

        @guid uniqueidentifier,
        @isguid int                -- 0 = FALSE, 1 = TRUE
 

-- set initial values

select @strguid = N'A94E2F79-C2AD-D911-84D3-00096B936E11', @isguid = null, @guid = null

exec @rc = master.dbo.xp_guid_is_guid @strguid, @isguid output, @guid output
select @rc as rc, @isguid as isguid, @guid as guid
go

 

-- ************************************************************************
-- fn_new_guid()
-- ************************************************************************
declare @guid uniqueidentifier

 

select  @guid = fn_new_guid()

 

select  fn_new_guid() as fn_new_guid
go

 

-- ************************************************************************
-- fn_new_sequential_guid()
-- ************************************************************************
declare @guid uniqueidentifier

 

select  @guid = fn_new_sequential_guid()

 

select  fn_new_sequential_guid() as fn_new_sequential_guid

go

 

-- ************************************************************************
-- fn_is_guid()
-- ************************************************************************
declare @strguid nvarchar(4000),

        @isguid int                -- 0 = FALSE, 1 = TRUE
 

-- set initial values

select @strguid = N'A94E2F79-C2AD-D911-84D3-00096B936E11', @isguid = null
 

select @isguid = fn_is_guid(@strguid)

 

select @isguid = fn_is_guid(N'A94E2F79-C2AD-D911-84D3-00096B936E11')
 

select fn_is_guid(N'A94E2F79-C2AD-D911-84D3-00096B936E11') as fn_is_guid

go

 

-- ************************************************************************
-- fn_string2guid()

-- ************************************************************************
declare @strguid nvarchar(4000),

        @guid uniqueidentifier
 

select @strguid = N'A94E2F79-C2AD-D911-84D3-00096B936E11', @guid = null

select @guid = fn_string2guid(@strguid)

 

select fn_string2guid(@strguid) as fn_string2guid
go

 

-- ************************************************************************
-- End of sample section

-- ************************************************************************
 

Parameters:

This is the complete list off parameters per extended stored procedure in XPGUID, all parameters are ordinal.

@rc = xp_guid_new_guid @guid output

@rc = xp_guid_new_sequential_guid @guid output

@rc = xp_guid_is_guid @strguid, @isguid output, @guid output

Return codes:

The extended stored procedures return only two return codes, 0 (zero) indicating successful execution, 1 indicating failure always accompanied with an error message.

 

This is how to retrieve the return code:

 

declare @rc int, @guid uniqueidentifier

exec    @rc = master.dbo.xp_guid_new_guid @guid output

select  @rc

Installation:

To install XPGUID follow these instructions:

1. For SQL Server 2000, download xpguid.zip and unzip the files
    

2. Copy xpguid80.dll into the SQL Server BINN directory.
   
   For SQL Server 2000 the default location is "C:\Program Files\Microsoft SQL Server\MSSQL\Binn"
    

3. Register the extended stored procedures using OSQL or SQL Query Analyzer by executing the xpguid-install.sql script which is contained in the xpguid.zip file
   -- OSQL using standard SQL Server security (will be prompted for the password)
   osql -S (local) -Usa -i xpguid-install.sql
   
   -- SQL Query Analyzer using Windows Authentication
   isqlw -S (local) -E -i xpguid-install.sql
    

4. Optionally grant rights to the correct set of users using OSQL or SQL Query Analyzer by executing:
   grant execute on xp_guid_new_guid to <role>
   
   By default only the members of the sysadmin role have execution rights on the XP after it is being registered

Uninstall:

To remove XPGUID from a system follow these instructions:

1. To uninstall simple run the xpguid-uninstall.sql script uysing OSQL or Query Analyzer.
   -- OSQL using standard SQL Server security (will be prompted for the password)
   osql -S (local) -Usa -i xpguid-uninstall.sql
   
   -- SQL Query Analyzer using Windows Authentication
   isqlw -S (local) -E -i xpguid-uninstall.sql
    

2. Delete the XPGUID80.DLL from the SQL Server BINN directory

Version and platform support:

XPGUID is tested and supported on:

• SQL Server 2000 (including all available service packs), running on Windows XP and Windows Server 2003

• MSDE 2000 (including all available service packs), running on Windows XP and Windows Server 2003

XPGUID is supported on:

• SQL Server 2000 (including all available service packs), running on Windows 2000, Windows XP and Windows Server 2003

• MSDE 2000 (including all available service packs), running on Windows 2000, Windows XP and Windows Server 2003

XPGUID is NOT supported or tested in any way or form on:

• SQL Server 7.0, none of the versions are supported

• MSDE 1.0, none of the versions are supported

• SQL Server 2000 running on Windows 95, Windows 98, Windows 98 Second Edition, Windows Me or Windows NT 4.0

• MSDE 2000 running on Windows 95, Windows 98, Windows 98 Second Edition, Windows Me or Windows NT 4.0

Known limitations:

• All error messages are in English and do not reflect the client side locale ID

FAQ:

• Q: Where can I get the source code of XPGUID?
  A: The source code of XPGUID is not made publicly available.
   
• Q: Is XPGUID supported on a cluster?
  A: Yes, you only need to make sure that the binary (xpguidXX.dll) is available in the BINN directory of every instance for each node.
   
• Q: Should I be using xp_guid_new_guid of fn_newguid() instead of newid()?
  A: No, the extended procedure implementation provides exactly the same functionality as the build-in function newid() and is only available to compare the performance overhead between a build-in function and an extended stored procedure call, see xpguid_perf.sql for a test script.
   
• Q: When will there be a version for Itanium (ia64)?
  A: Based on demand we will decide to generate one
   
• Q: ...
   

Troubleshooting:

• ...
   

License

XPGUID - Copyright © SQLDev.Net 1991-2005 (http://SQLDev.Net)

All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

3. Neither the name of SQLDev.Net nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

4. Binaries, source code and any other parts of this distribution may not be incorporated into any software licensed under the terms of the GNU General Public License (GPL) or the GNU Lesser Public License (LGPL). Binaries, source code and any other parts of this distribution
   may not be incorporated into any software licensed under any license requiring source code disclosure of derivative works.

5. Modified redistributions of source code, binaries and/or documentation must carry the above copyright as required by clauses (1) and (2) and may retain the name "SQLDev.Net" in source code, documentation and metadata.

6. The name "SQLDev.Net" is a trademark of SQLDev.Net B.V. the Netherlands.
   
   THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
    

Planned functionality enhancements:

The next release will contain the following feature (subject to change):

• None

For future release:

• None

Realized functionality enhancements:

• None

Change log:

***

Questions or problems regarding this web site should be directed to Web Master.
Copyright © 1991-2005 SQLDev.Net. All rights reserved.
Last modified: 04/06/05.