-
-
Save SQLKiwi/d7ef9372ca10b0f579edfb9d52dec9cf to your computer and use it in GitHub Desktop.
Sequence Tables scripts
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ================================================================================================================================================ | |
-- TEST RIG SETUP - SQL SERVER 2005 OR 2008 REQUIRED | |
-- | |
-- Creates all the objects required to run the tests | |
-- The loopback server implementation requires SQL Server 2008 | |
-- ================================================================================================================================================ | |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; | |
SET NOCOUNT OFF; | |
GO | |
USE master; | |
GO | |
-- Remove the loopback linked server if it exists | |
-- I use a GUID here as the name of the linked server to prevent a name collision | |
IF EXISTS (SELECT * FROM sys.servers WHERE name = N'47B10603-3D2A-4DED-AD40-3C8598EB8B6A') | |
EXECUTE sp_dropserver N'47B10603-3D2A-4DED-AD40-3C8598EB8B6A', 'droplogins'; | |
GO | |
-- Create the loopback linked server and configure it | |
DECLARE @servername SYSNAME; | |
SET @servername = CONVERT(SYSNAME, SERVERPROPERTY(N'ServerName')); | |
EXECUTE sys.sp_addlinkedserver | |
@server = N'47B10603-3D2A-4DED-AD40-3C8598EB8B6A', | |
@srvproduct = N'', | |
@provider = N'SQLNCLI', | |
@datasrc = @servername; | |
EXECUTE sys.sp_serveroption | |
@server = N'47B10603-3D2A-4DED-AD40-3C8598EB8B6A', | |
@optname = 'RPC OUT', | |
@optvalue = 'ON'; | |
-- Only for 2008 | |
IF CONVERT(SYSNAME, SERVERPROPERTY(N'ProductVersion')) LIKE N'10.%' | |
BEGIN | |
-- Prevent a local transaction esclating to a distributed transaction (SQL Server 2008 only) | |
EXECUTE sys.sp_serveroption | |
@server = N'47B10603-3D2A-4DED-AD40-3C8598EB8B6A', | |
@optname = 'remote proc transaction promotion', | |
@optvalue = 'OFF'; | |
END; | |
GO | |
-- Enable common language runtime integration | |
IF EXISTS (SELECT * FROM sys.configurations WHERE name = N'clr enabled' AND value_in_use = 0) | |
BEGIN | |
-- Create a synonym as a way of remembering that we enabled CLR | |
-- This allows the clean-up script to reset it later | |
CREATE SYNONYM [FB111697-80B1-4B5C-A42C-6B074AC2DAF6] | |
FOR master.dbo.spt_values; | |
-- Enable CLR | |
EXECUTE sys.sp_configure | |
@configname = 'clr enabled', | |
@configvalue = 1; | |
RECONFIGURE; | |
END; | |
GO | |
-- Drop the CLR assembly, login, and asymmetric key if they exist in master | |
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'SqlAllocation') | |
DROP ASSEMBLY [SqlAllocation]; | |
IF EXISTS (SELECT * from sys.server_principals WHERE name = N'SqlAllocation_ExternalAccessLogin' AND type_desc = N'ASYMMETRIC_KEY_MAPPED_LOGIN') | |
DROP LOGIN [SqlAllocation_ExternalAccessLogin]; | |
IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = N'AK_SqlAllocation') | |
DROP ASYMMETRIC KEY [AK_SqlAllocation]; | |
GO | |
-- Create the CLR assemby temporarily, with the SAFE permission set | |
CREATE ASSEMBLY [SqlAllocation] | |
AUTHORIZATION [dbo] | |
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030003EF894B0000000000000000E00002210B010800001000000020000000000000AE2D00000020000000400000000040000020000000100000040000000000000004000000000000000080000000100000E8850000030040850000100000100000000010000010000000000000100000000000000000000000582D000053000000004000005803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B40D0000002000000010000000100000000000000000000000000000200000602E7273726300000058030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C00000000600000001000000030000000000000000000000000000040000042000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000902D000000000000480000000200050054220000040B00000900000000000000000000000000000050200000800000000000000000000000000000000000000000000000000000000000000000000000F1A2F9AB2F844F82F47B11CC5B813EBA3558D48596A908127013543DFF5254D0F62D7F12F523F70F4017F68ED55029E79E74DCE327454F96410AC2E5E84EB1747B044748F4F9BF740EE8F21B0D2F749AED534FA08A4C902B4FAF4BF990C9CF2FC4BA58E10F31927103B0469AFE6178F511DCA0C23ABDB5B94D1809E7C5D571681B3004002F010000010000117201000070731000000A0C086F1100000A723100007008731200000A0D096F1300000A74170000010ADE0A092C06096F1400000ADCDE0A082C06086F1400000ADC731500000A13041104066F1600000A1104176F1700000A1104166F1800000A11046F1900000A731000000A130511056F1100000A731A00000A13061106727B0000706F1B00000A11066F1C00000A72980100701E166F1D00000A038C020000016F1E00000A11066F1C00000A729E0100701F0C1F146F1D00000A028C030000016F1E00000A1106176F1F00000A110611056F2000000A11066F1300000AA51F0000010BDE1726047E2100000A81020000011F63732200000A1307DE2FDE0C11062C0711066F1400000ADCDE0C11052C0711056F1400000ADC0407732200000A810200000116732200000A2A11072A000140000002001D000E2B000A0000000002000B002C37000A000000000000D7000FE600172000000102007C0083FF000C0000000002006E009F0D010C000000001E02282300000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000C8020000237E0000340300004804000023537472696E6773000000007C070000A4010000235553002009000010000000234755494400000030090000D401000023426C6F620000000000000002000001471502000900000000FA0133001600000100000020000000020000000200000004000000230000000F00000001000000010000000200000000000A0001000000000006003D0036000A00650050000A006E0050000600990087000600D100B2000600E50087000600FE00870006001901870006003401870006004D0187000600660187000600850187000600A20187000600D901B9010600F901B9010A00400225020A005602250206008E02B2000A00B1029B020A00D202BF020A00E4029B020A00EF02BF0206000703360006000E0336000A0022039B020A006E03BF020A00AD039B020A00D3039B020A00E00344000A00FB0344000600260436000600310436000000000001000000000001000100010010001C000000050001000100D02000000000960078000A0001004C22000000008618810016000500000000000000000001006802000002007502020003007F02210081001A00290081001F00310081001A00390081001A00410081001A00490081001A00510081001A00590081001A00610081001A00690081001A00710081002400790081001600810081001600890081001600910081001600990081001A00A100DF021600A90081003801B100F9023F01C1001A031600C90081001600C9003D031A00C9004C031F00C90063031F00D10088034301A90081001600B1009D031A00A900C4034701D900EA034C01E100EE035501B10007045A01A9001704600111002C04660111008100240009008100160020006B00CB0024007300E0002E002B0081012E00330099012E003B007B012E006300B5012E0023007B012E0043007B012E005B00AC012E0013007B012E004B007B012E005300990144007300F30064007300E0008400730025016A0104800000010000007F0EE9790100000029001702000002000000000000000000000001002D000000000002000000000000000000000001004400000000000000003C4D6F64756C653E0053716C416C6C6F636174696F6E2E646C6C0053746F72656450726F63656475726573006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C496E7433320053716C537472696E6700416C6C6F63617465002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C416C6C6F636174696F6E004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053716C46616365744174747269627574650053657175656E63654E616D650052616E676553697A65004669727374416C6C6F6361746564004F75744174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E64004462436F6D6D616E6400457865637574655363616C617200537472696E670049446973706F7361626C6500446973706F73650053716C436F6E6E656374696F6E537472696E674275696C646572007365745F44617461536F75726365007365745F496E74656772617465645365637572697479007365745F456E6C697374004462436F6E6E656374696F6E537472696E674275696C646572006765745F436F6E6E656374696F6E537472696E67007365745F436F6D6D616E64546578740053716C506172616D65746572436F6C6C656374696F6E006765745F506172616D65746572730053716C506172616D657465720053716C44625479706500416464007365745F53716C56616C756500436F6D6D616E6454797065007365745F436F6D6D616E6454797065007365745F436F6E6E656374696F6E00496E743332004E756C6C004E756C6C5265666572656E6365457863657074696F6E00002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065000049530045004C004500430054002000530045005200560045005200500052004F005000450052005400590028004E0027005300650072007600650072004E0061006D0065002700290001811B5500500044004100540045002000740065006D007000640062002E00640062006F002E00530065007100750065006E00630065005400610062006C0065002000570049005400480020002800520045004100440043004F004D004D00490054005400450044004C004F0043004B002900200053004500540020006E006500780074005F00760061006C007500650020003D0020006E006500780074005F00760061006C007500650020002B0020004000560020004F00550054005000550054002000640065006C0065007400650064002E006E006500780074005F00760061006C00750065002000570048004500520045002000730065007100750065006E00630065005F006E0061006D00650020003D002000400045003B0000054000560000054000450000ED293D04D789A7418562F35209C755B40008B77A5C561934E0890B00031109110D110910110903200001042001010E0420010102042001010880A00024000004800000940000000602000000240000525341310004000001000100F376ACC72BC853C56C6280B2BB6A1C1363EE66CA57901D21C382209D57C4747645C306AE771354FFE4940D58BD0B67727785E9B5A8E6A06FD8B77B3605906BAEDF1ADC3F2411BD6886D39EB5ED7C629193C524E6DCFFD10E62A578C56A70ABBDEB2E91C0DB42308932AED779D0D727A699206F1074EF07E867C1F77637B3C9951401000100540E044E616D6508416C6C6F63617465120100010054020A49734E756C6C61626C6500310100030054020D497346697865644C656E6774680054020A49734E756C6C61626C65005408074D617853697A6580000000120100010054020A49734E756C6C61626C6501062002010E124D0320001C0320000E042000126D08200312710E117508042001011C05200101117905200101124D030611091007080E08124D12551265124D1255110905010000000017010012436F7079726967687420C2A920203230313000001201000D53716C416C6C6F636174696F6E00000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301802D000000000000000000009E2D0000002000000000000000000000000000000000000000000000902D000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000000300000000000000000000000334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100E9797F0E00000100E9797F0E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00460020000010053007400720069006E006700460069006C00650049006E0066006F0000003C020000010030003000300030003000340062003000000044000E000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C0041006C006C006F0063006100740069006F006E00000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700310031002E00330031003200300039000000000044001200010049006E007400650072006E0061006C004E0061006D0065000000530071006C0041006C006C006F0063006100740069006F006E002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000004C00120001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0041006C006C006F0063006100740069006F006E002E0064006C006C0000003C000E000100500072006F0064007500630074004E0061006D00650000000000530071006C0041006C006C006F0063006100740069006F006E00000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700310031002E00330031003200300039000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700310031002E003300310032003000390000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000B03D000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | |
WITH PERMISSION_SET = SAFE; | |
GO | |
-- Create an asymmetric key, create a login from it, and give the login EXTERNAL_ACCESS permission | |
CREATE ASYMMETRIC KEY AK_SqlAllocation | |
FROM ASSEMBLY SqlAllocation; | |
CREATE LOGIN SqlAllocation_ExternalAccessLogin | |
FROM ASYMMETRIC KEY AK_SqlAllocation; | |
GRANT EXTERNAL ACCESS ASSEMBLY | |
TO SqlAllocation_ExternalAccessLogin; | |
DROP ASSEMBLY [SqlAllocation]; | |
GO | |
-- Switch to the target database (we'll use tempdb for the demo) | |
USE [tempdb]; | |
GO | |
-- Drop the allocation table and T-SQL procedure | |
IF OBJECT_ID(N'dbo.SequenceTable', N'U') | |
IS NOT NULL | |
DROP TABLE dbo.SequenceTable; | |
IF OBJECT_ID(N'dbo.Allocate_TSQL', N'P') | |
IS NOT NULL | |
DROP PROCEDURE dbo.Allocate_TSQL; | |
IF OBJECT_ID(N'dbo.Allocate_CLR', N'PC') | |
IS NOT NULL | |
DROP PROCEDURE dbo.Allocate_CLR; | |
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'SqlAllocation') | |
DROP ASSEMBLY SqlAllocation; | |
GO | |
-- Create the assembly with EXTERNAL_ACCESS | |
-- The assembly key matches the asymmetric key created earlier. The login mapped to the key has EXTERNAL_ACCESS permission. | |
CREATE ASSEMBLY [SqlAllocation] | |
AUTHORIZATION [dbo] | |
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030003EF894B0000000000000000E00002210B010800001000000020000000000000AE2D00000020000000400000000040000020000000100000040000000000000004000000000000000080000000100000E8850000030040850000100000100000000010000010000000000000100000000000000000000000582D000053000000004000005803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B40D0000002000000010000000100000000000000000000000000000200000602E7273726300000058030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C00000000600000001000000030000000000000000000000000000040000042000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000902D000000000000480000000200050054220000040B00000900000000000000000000000000000050200000800000000000000000000000000000000000000000000000000000000000000000000000F1A2F9AB2F844F82F47B11CC5B813EBA3558D48596A908127013543DFF5254D0F62D7F12F523F70F4017F68ED55029E79E74DCE327454F96410AC2E5E84EB1747B044748F4F9BF740EE8F21B0D2F749AED534FA08A4C902B4FAF4BF990C9CF2FC4BA58E10F31927103B0469AFE6178F511DCA0C23ABDB5B94D1809E7C5D571681B3004002F010000010000117201000070731000000A0C086F1100000A723100007008731200000A0D096F1300000A74170000010ADE0A092C06096F1400000ADCDE0A082C06086F1400000ADC731500000A13041104066F1600000A1104176F1700000A1104166F1800000A11046F1900000A731000000A130511056F1100000A731A00000A13061106727B0000706F1B00000A11066F1C00000A72980100701E166F1D00000A038C020000016F1E00000A11066F1C00000A729E0100701F0C1F146F1D00000A028C030000016F1E00000A1106176F1F00000A110611056F2000000A11066F1300000AA51F0000010BDE1726047E2100000A81020000011F63732200000A1307DE2FDE0C11062C0711066F1400000ADCDE0C11052C0711056F1400000ADC0407732200000A810200000116732200000A2A11072A000140000002001D000E2B000A0000000002000B002C37000A000000000000D7000FE600172000000102007C0083FF000C0000000002006E009F0D010C000000001E02282300000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000C8020000237E0000340300004804000023537472696E6773000000007C070000A4010000235553002009000010000000234755494400000030090000D401000023426C6F620000000000000002000001471502000900000000FA0133001600000100000020000000020000000200000004000000230000000F00000001000000010000000200000000000A0001000000000006003D0036000A00650050000A006E0050000600990087000600D100B2000600E50087000600FE00870006001901870006003401870006004D0187000600660187000600850187000600A20187000600D901B9010600F901B9010A00400225020A005602250206008E02B2000A00B1029B020A00D202BF020A00E4029B020A00EF02BF0206000703360006000E0336000A0022039B020A006E03BF020A00AD039B020A00D3039B020A00E00344000A00FB0344000600260436000600310436000000000001000000000001000100010010001C000000050001000100D02000000000960078000A0001004C22000000008618810016000500000000000000000001006802000002007502020003007F02210081001A00290081001F00310081001A00390081001A00410081001A00490081001A00510081001A00590081001A00610081001A00690081001A00710081002400790081001600810081001600890081001600910081001600990081001A00A100DF021600A90081003801B100F9023F01C1001A031600C90081001600C9003D031A00C9004C031F00C90063031F00D10088034301A90081001600B1009D031A00A900C4034701D900EA034C01E100EE035501B10007045A01A9001704600111002C04660111008100240009008100160020006B00CB0024007300E0002E002B0081012E00330099012E003B007B012E006300B5012E0023007B012E0043007B012E005B00AC012E0013007B012E004B007B012E005300990144007300F30064007300E0008400730025016A0104800000010000007F0EE9790100000029001702000002000000000000000000000001002D000000000002000000000000000000000001004400000000000000003C4D6F64756C653E0053716C416C6C6F636174696F6E2E646C6C0053746F72656450726F63656475726573006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C496E7433320053716C537472696E6700416C6C6F63617465002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C416C6C6F636174696F6E004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053716C46616365744174747269627574650053657175656E63654E616D650052616E676553697A65004669727374416C6C6F6361746564004F75744174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E64004462436F6D6D616E6400457865637574655363616C617200537472696E670049446973706F7361626C6500446973706F73650053716C436F6E6E656374696F6E537472696E674275696C646572007365745F44617461536F75726365007365745F496E74656772617465645365637572697479007365745F456E6C697374004462436F6E6E656374696F6E537472696E674275696C646572006765745F436F6E6E656374696F6E537472696E67007365745F436F6D6D616E64546578740053716C506172616D65746572436F6C6C656374696F6E006765745F506172616D65746572730053716C506172616D657465720053716C44625479706500416464007365745F53716C56616C756500436F6D6D616E6454797065007365745F436F6D6D616E6454797065007365745F436F6E6E656374696F6E00496E743332004E756C6C004E756C6C5265666572656E6365457863657074696F6E00002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065000049530045004C004500430054002000530045005200560045005200500052004F005000450052005400590028004E0027005300650072007600650072004E0061006D0065002700290001811B5500500044004100540045002000740065006D007000640062002E00640062006F002E00530065007100750065006E00630065005400610062006C0065002000570049005400480020002800520045004100440043004F004D004D00490054005400450044004C004F0043004B002900200053004500540020006E006500780074005F00760061006C007500650020003D0020006E006500780074005F00760061006C007500650020002B0020004000560020004F00550054005000550054002000640065006C0065007400650064002E006E006500780074005F00760061006C00750065002000570048004500520045002000730065007100750065006E00630065005F006E0061006D00650020003D002000400045003B0000054000560000054000450000ED293D04D789A7418562F35209C755B40008B77A5C561934E0890B00031109110D110910110903200001042001010E0420010102042001010880A00024000004800000940000000602000000240000525341310004000001000100F376ACC72BC853C56C6280B2BB6A1C1363EE66CA57901D21C382209D57C4747645C306AE771354FFE4940D58BD0B67727785E9B5A8E6A06FD8B77B3605906BAEDF1ADC3F2411BD6886D39EB5ED7C629193C524E6DCFFD10E62A578C56A70ABBDEB2E91C0DB42308932AED779D0D727A699206F1074EF07E867C1F77637B3C9951401000100540E044E616D6508416C6C6F63617465120100010054020A49734E756C6C61626C6500310100030054020D497346697865644C656E6774680054020A49734E756C6C61626C65005408074D617853697A6580000000120100010054020A49734E756C6C61626C6501062002010E124D0320001C0320000E042000126D08200312710E117508042001011C05200101117905200101124D030611091007080E08124D12551265124D1255110905010000000017010012436F7079726967687420C2A920203230313000001201000D53716C416C6C6F636174696F6E00000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301802D000000000000000000009E2D0000002000000000000000000000000000000000000000000000902D000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000000300000000000000000000000334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100E9797F0E00000100E9797F0E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00460020000010053007400720069006E006700460069006C00650049006E0066006F0000003C020000010030003000300030003000340062003000000044000E000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C0041006C006C006F0063006100740069006F006E00000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700310031002E00330031003200300039000000000044001200010049006E007400650072006E0061006C004E0061006D0065000000530071006C0041006C006C006F0063006100740069006F006E002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000004C00120001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0041006C006C006F0063006100740069006F006E002E0064006C006C0000003C000E000100500072006F0064007500630074004E0061006D00650000000000530071006C0041006C006C006F0063006100740069006F006E00000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700310031002E00330031003200300039000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700310031002E003300310032003000390000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000B03D000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | |
WITH PERMISSION_SET = EXTERNAL_ACCESS; | |
GO | |
-- Create the CLR procedure from the assembly | |
CREATE PROCEDURE | |
[dbo].[Allocate_CLR] | |
@SequenceName NVARCHAR(20), | |
@RangeSize INTEGER, | |
@FirstAllocated INTEGER OUTPUT | |
WITH EXECUTE AS CALLER | |
AS EXTERNAL NAME | |
[SqlAllocation].[StoredProcedures].[Allocate]; | |
GO | |
-- The table used to hold sequences | |
CREATE TABLE dbo.SequenceTable | |
( | |
sequence_name NVARCHAR(20) PRIMARY KEY CLUSTERED, | |
next_value INTEGER NOT NULL | |
); | |
GO | |
-- Base procedure to allocate ranges (called by all implementations) | |
CREATE PROCEDURE dbo.Allocate_TSQL | |
@SequenceName NVARCHAR(20), -- The name of the sequence to allocate keys from | |
@RangeSize INTEGER = 1, -- The number of keys to allocate | |
@FirstAllocated INTEGER OUTPUT -- The first key allocated (output) | |
AS | |
BEGIN | |
SET XACT_ABORT ON; -- Most errors will abort the batch | |
SET NOCOUNT ON; -- Supress 'x row(s) affected' messages | |
SET ROWCOUNT 0; -- Reset rowcount | |
-- Validate the range size requested | |
IF (@RangeSize IS NULL OR @RangeSize < 1) | |
BEGIN | |
RAISERROR('@RangeSize must be a positive integer (supplied value = %i)', 16, 1, @RangeSize); | |
RETURN 999; | |
END; | |
-- Initialize the output parameter | |
SET @FirstAllocated = NULL; | |
-- Update the row associated with @SequenceName, returning the | |
-- current value, and then incrementing it by @RangeSize | |
UPDATE dbo.SequenceTable WITH (READCOMMITTEDLOCK) | |
SET @FirstAllocated = next_value, | |
next_value = next_value + @RangeSize | |
WHERE sequence_name = @SequenceName; | |
-- If @Allocated has a non-NULL value, we know we successfully updated a row | |
RETURN CASE WHEN (@FirstAllocated IS NOT NULL) THEN 0 ELSE -999 END; | |
END; | |
GO | |
-- Initialise the test sequence | |
INSERT dbo.SequenceTable | |
(sequence_name, next_value) | |
VALUES (N'Test Sequence', 1); | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ================================================================================================================================================ | |
-- TEST RUN SCRIPT | |
-- | |
-- Runs tests to demonstrate locking differences between the implementations | |
-- The loopback server implementation requires SQL Server 2008 | |
-- ================================================================================================================================================ | |
USE tempdb; | |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; | |
SET NOCOUNT OFF; | |
GO | |
SELECT 'TEST ONE - ORIGINAL (BLOCKING)' AS Test; | |
GO | |
DECLARE @RC INTEGER, -- Return code from the procedure call | |
@Value INTEGER; -- The first ID allocated | |
-- Start a local transaction | |
BEGIN TRANSACTION; | |
-- Call the procedure to allocate a range of 100 keys | |
EXECUTE @RC = | |
dbo.Allocate_TSQL | |
@SequenceName = N'Test Sequence', | |
@RangeSize = 100, | |
@FirstAllocated = @Value OUTPUT; | |
-- Show the locks held | |
-- The UPDATE inside the procedure holds an exclusive row lock until the end | |
-- of the outermost transaction. This prevents other users from allocating | |
-- IDs concurrently | |
SELECT request_session_id, resource_type, resource_description, request_mode, request_status | |
FROM sys.dm_tran_locks; | |
-- Rollback the transaction | |
-- The ID allocation is also rolled back | |
ROLLBACK TRANSACTION; | |
-- The next value to allocate is still '1' | |
SELECT A.sequence_name, | |
A.next_value | |
FROM dbo.SequenceTable A; | |
GO | |
SELECT 'TEST TWO - CLR (NON-BLOCKING)' AS Test; | |
GO | |
DECLARE @RC INTEGER, -- Return code from the procedure call | |
@Value INTEGER; -- The first ID allocated | |
-- Start a local transaction | |
BEGIN TRANSACTION; | |
-- Call the procedure to allocate a range of 100 IDs | |
EXECUTE @RC = | |
dbo.Allocate_CLR | |
@SequenceName = N'Test Sequence', | |
@RangeSize = 100, | |
@FirstAllocated = @Value OUTPUT; | |
-- Show the locks held | |
-- There are no locks on the table - the ID allocation took place in a separate transaction, on a separate connection | |
SELECT request_session_id, resource_type, resource_description, request_mode, request_status | |
FROM sys.dm_tran_locks; | |
-- Rollback the transaction | |
-- The ID allocation is NOT rolled back | |
ROLLBACK TRANSACTION; | |
-- The next value to allocate is now '101' | |
SELECT A.sequence_name, | |
A.next_value | |
FROM dbo.SequenceTable A; | |
GO | |
SELECT 'TEST THREE - LOOPBACK SERVER (NON-BLOCKING, 2008 ONLY)' AS Test; | |
GO | |
DECLARE @RC INTEGER, -- Return code from the procedure call | |
@Value INTEGER; -- The first ID allocated | |
-- Start a local transaction | |
BEGIN TRANSACTION; | |
-- Call the procedure to allocate a range of 100 keys | |
-- (The linked server would usually be called some more | |
-- friendly, like 'loopback'. I use a GUID to avoid | |
-- a name collision with an exisiting linked server. | |
EXECUTE @RC = | |
[47B10603-3D2A-4DED-AD40-3C8598EB8B6A].tempdb.dbo.Allocate_TSQL | |
@SequenceName = N'Test Sequence', | |
@RangeSize = 100, | |
@FirstAllocated = @Value OUTPUT; | |
-- Show the locks held | |
-- There are none - the ID allocation took place in a separate transaction, on a separate connection | |
SELECT request_session_id, resource_type, resource_description, request_mode, request_status | |
FROM sys.dm_tran_locks; | |
-- Rollback the transaction | |
-- The ID allocation is NOT rolled back | |
ROLLBACK TRANSACTION; | |
-- The next value to allocate is now '201' | |
SELECT A.sequence_name, | |
A.next_value | |
FROM dbo.SequenceTable A; | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ================================================================================================================================================ | |
-- PERFORMANCE TESTS | |
-- | |
-- Measures the time taken to run a configurable number of allocations using each method | |
-- The loopback server implementation requires SQL Server 2008 | |
-- ================================================================================================================================================ | |
USE tempdb; | |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; | |
SET NOCOUNT OFF; | |
GO | |
DECLARE @StartTime DATETIME, -- Start time of each test run | |
@RunNumber INTEGER, -- Current test number | |
@Runs INTEGER, -- Total number of test runs | |
@KeyCount INTEGER, -- The number of keys to reserve on each call | |
@RC INTEGER, -- Return code from the procedure call | |
@Value INTEGER, -- The first ID allocated | |
@Time1 INTEGER, -- Total execution time for the basic method | |
@Time2 INTEGER, -- Total execution time for the CLR method | |
@Time3 INTEGER; -- Total execution time for the loopback method | |
-- *** START TEST CONFIGURATION OPTIONS *** | |
SELECT @Runs = 10000, | |
@KeyCount = 250; | |
-- *** END TEST CONFIGURATION OPTIONS *** | |
-- Initialize the run | |
SELECT @RunNumber = 1, | |
@StartTime = CURRENT_TIMESTAMP; | |
WHILE (@RunNumber <= @Runs) | |
BEGIN | |
-- Call the procedure to allocate a range of keys | |
EXECUTE @RC = | |
dbo.Allocate_TSQL | |
@SequenceName = N'Test Sequence', | |
@RangeSize = @KeyCount, | |
@FirstAllocated = @Value OUTPUT; | |
SET @RunNumber = @RunNumber + 1; | |
END; | |
SET @Time1 = DATEDIFF(MILLISECOND, @StartTime, CURRENT_TIMESTAMP); | |
SELECT @RunNumber = 1, | |
@StartTime = CURRENT_TIMESTAMP; | |
WHILE (@RunNumber <= @Runs) | |
BEGIN | |
-- Call the procedure to allocate a range of keys | |
EXECUTE @RC = | |
dbo.Allocate_CLR | |
@SequenceName = N'Test Sequence', | |
@RangeSize = @KeyCount, | |
@FirstAllocated = @Value OUTPUT; | |
SET @RunNumber = @RunNumber + 1; | |
END; | |
SET @Time2 = DATEDIFF(MILLISECOND, @StartTime, CURRENT_TIMESTAMP); | |
SELECT @RunNumber = 1, | |
@StartTime = CURRENT_TIMESTAMP; | |
WHILE (@RunNumber <= @Runs) | |
BEGIN | |
-- Call the procedure to allocate a range of keys | |
-- (GUID used for linked server name to avoid a name collision) | |
EXECUTE @RC = | |
[47B10603-3D2A-4DED-AD40-3C8598EB8B6A].tempdb.dbo.Allocate_TSQL | |
@SequenceName = N'Test Sequence', | |
@RangeSize = @KeyCount, | |
@FirstAllocated = @Value OUTPUT; | |
SET @RunNumber = @RunNumber + 1; | |
END; | |
SET @Time3 = DATEDIFF(MILLISECOND, @StartTime, CURRENT_TIMESTAMP); | |
-- Results | |
SELECT 'Performance Test Results (average, ms)'; | |
SELECT run_count = @Runs, | |
allocation_size = @KeyCount, | |
blocking_run_ms = CONVERT(DEC(9,4), @Time1 * 1. / @Runs), | |
clr_method_ms = CONVERT(DEC(9,4), @Time2 * 1. / @Runs), | |
loopback_method_ms = CONVERT(DEC(9,4), @Time3 * 1. / @Runs); | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ================================================================================================================================================ | |
-- POST-TEST CLEANUP | |
-- | |
-- Drops all objects created by the script | |
-- Disables CLR integration if script #1 enabled it | |
-- ================================================================================================================================================ | |
USE master; | |
GO | |
DROP LOGIN [SqlAllocation_ExternalAccessLogin]; | |
DROP ASYMMETRIC KEY [AK_SqlAllocation]; | |
GO | |
IF EXISTS | |
( | |
-- Check for the synonym we created if CLR | |
-- integration was enabled by this script | |
SELECT * | |
FROM master.sys.synonyms | |
WHERE name = N'FB111697-80B1-4B5C-A42C-6B074AC2DAF6' | |
AND type_desc = N'SYNONYM' | |
) | |
BEGIN | |
-- Disable CLR integration | |
EXECUTE sys.sp_configure | |
@configname = 'clr enabled', | |
@configvalue = 0; | |
RECONFIGURE; | |
DROP SYNONYM [FB111697-80B1-4B5C-A42C-6B074AC2DAF6]; | |
END; | |
GO | |
USE tempdb; | |
GO | |
DROP PROCEDURE dbo.Allocate_CLR; | |
DROP PROCEDURE dbo.Allocate_TSQL; | |
DROP ASSEMBLY SqlAllocation; | |
DROP TABLE dbo.SequenceTable; | |
EXECUTE sys.sp_dropserver | |
@server = N'47B10603-3D2A-4DED-AD40-3C8598EB8B6A', | |
@droplogins = 'droplogins'; | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System; | |
using System.Data; | |
using System.Data.SqlClient; | |
using System.Data.SqlTypes; | |
using Microsoft.SqlServer.Server; | |
using System.Text; | |
public partial class StoredProcedures | |
{ | |
[Microsoft.SqlServer.Server.SqlProcedure(Name="Allocate")] | |
[return: SqlFacet(IsNullable = false)] | |
public static SqlInt32 Allocate | |
( | |
[SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 128)] SqlString SequenceName, | |
[SqlFacet(IsNullable = false)] SqlInt32 RangeSize, | |
[SqlFacet(IsNullable = true)]out SqlInt32 FirstAllocated | |
) | |
{ | |
string serverName; // The name of the SQL Server instance this procedure was called from | |
int allocated; // The first range value allocated by this routine | |
// Use the context connection to discover the name of the calling SQL Server | |
// (this is needed for the non-context connection used later) | |
using (SqlConnection conn = new SqlConnection("context connection=true")) | |
{ | |
conn.Open(); | |
using (SqlCommand cmd = new SqlCommand("SELECT SERVERPROPERTY(N'ServerName')", conn)) | |
{ | |
serverName = (string)cmd.ExecuteScalar(); | |
} | |
} | |
// Construct the connection string for the non-context connection | |
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(); | |
csb.DataSource=serverName; | |
csb.IntegratedSecurity=true; | |
csb.Enlist = false; | |
// Use a separate transaction scope to allocate the range | |
using (SqlConnection conn = new SqlConnection(csb.ConnectionString)) | |
{ | |
conn.Open(); | |
using (SqlCommand cmd = new SqlCommand()) | |
{ | |
// Parameterize the query to promote plan re-use | |
cmd.CommandText = "UPDATE tempdb.dbo.SequenceTable WITH (READCOMMITTEDLOCK) SET next_value = next_value + @V OUTPUT deleted.next_value WHERE sequence_name = @E;"; | |
cmd.Parameters.Add("@V", SqlDbType.Int, 0).SqlValue = RangeSize; | |
cmd.Parameters.Add("@E", SqlDbType.NVarChar, 20).SqlValue = SequenceName; | |
cmd.CommandType = CommandType.Text; | |
cmd.Connection = conn; | |
try | |
{ | |
// The first ID allocated is returned by the OUTPUT clause of the UPDATE statement | |
allocated = (int)cmd.ExecuteScalar(); | |
} | |
catch (NullReferenceException) | |
{ | |
// The sequence name did not exist so return NULL in the output parameter | |
FirstAllocated = SqlInt32.Null; | |
// 'Entity not found' return code | |
return new SqlInt32(99); | |
} | |
} | |
} | |
// Set the output parameter | |
FirstAllocated = new SqlInt32(allocated); | |
// Success return code | |
return new SqlInt32(0); | |
} | |
}; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment