<jco-blog/>

Software Development Project Notes

Category Archives: SQL Server

Home / SQL Server

48 Posts

originally posted 2013-4-1

1	originally posted 2013-4-1

select [Table] = '[' +[Name] + ']'
,[Columns] = '''' + left([Columns],len(columns)-1) + ''''
from
(
 select top 10000 
 t.name
 ,[Columns] = substring(
 (
  select
  '[' + c.name + '], '
  from sys.columns c 
  where t.object_id = c.object_id
  order by c.[Name]
  for xml path('')
 )
 ,1,8000)
 from sys.tables t
 
 where t.type = 'U'
 order by t.name
) as f1
group by [Name],[Columns]

select [Table] = '[' +[Name] + ']'

,[Columns] = '''' + left([Columns],len(columns)-1) + ''''

from

(

select top 10000

t.name

,[Columns] = substring(

(

select

'[' + c.name + '], '

from sys.columns c

where t.object_id = c.object_id

order by c.[Name]

for xml path('')

)

,1,8000)

from sys.tables t

where t.type = 'U'

order by t.name

) as f1

group by [Name],[Columns]

originally posted 2013-4-10

1	originally posted 2013-4-10

Thanks to Jacob Sebastian’s post, here’s some good info to assist in SQL 2012 script conversion…

http://beyondrelational.com/modules/2/blogs/28/Posts/15429/replacement-for-system-stored-procedure-spdboption-in-sql-server-2012-denali.aspx

originally posted 2013-4-19

1	originally posted 2013-4-19

Using setvar in T-SQL
Set the values of the two SQLCMD variables.

In this example, the script will only run the shrink if the ‘foo’ database has at least 100 MB to potentially free.

The script runs a shrink on each file of the database until the reduction value drops to zero.

Note: We use the “Simple” recovery mode for non-production databases, and ‘Full’ for those in production. This may affect how much the transaction log size can be reduced, specifically, the time since the last SQL backup may affect the trans-log’s shrinkability when the database is set to use ‘Full’ recovery mode.

--shrinks *ALL* files for a single database  
--that has a minimum MB of freeable space
--------------------------------------------
:setvar DbName "foo"
:setvar FreeAtLeastMB "100"

use [$(DbName)];   

declare @logname nvarchar(256)  
,@name nvarchar(256) 
,@sizeBefore int 
,@sizeAfter int  
,@reduction int  
,@totalReduction int  
,@rowcount int
,@iteration int      
,@msg nvarchar(1000)
,@originalSpaceFreeMB int
,@originalSizeMB int
,@newSpaceFreeMB int
,@newSizeMB int

set @msg = 'Working on $(DbName)';
raiserror (@msg, 0, 1) with nowait --print immediately

select 
@originalSpaceFreeMB = sum([SpaceFreeMB])
,@originalSizeMB = sum([SizeMB])
from 
(
 select 
 sf.[DbName]
 ,sf.[LogicalName]
 ,sf.[FileName]
 ,sf.[SizeMB]
 ,sf.[SpaceUsedMB]
 ,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]
 from
 (
  select 
  [DbName] = db_name()
  ,[LogicalName] = [Name]
  ,[Filename]
  ,[SpaceUsedMB] = convert(int,round(fileproperty([Name],'SpaceUsed')/128.000,0))
  ,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))
  from sysfiles
 ) as sf
) as total

set @msg = '$(DbName) size is ' + convert(nvarchar(100),@originalSizeMB) + ' MB and has a potential of ' + convert(nvarchar(100),@originalSpaceFreeMB) + ' MB to free. Minimum freeable required is $(FreeAtLeastMB) MB.';
raiserror (@msg, 0, 1) with nowait

if(@originalSpaceFreeMB > $(FreeAtLeastMB)) 
begin
 declare @tab table ([name] nvarchar(256), [filename] nvarchar(256),[size] int, [reduction] int)   

 insert @tab  select [name], [physical_name], convert(int,null),convert(int,null)
 from sys.master_files 
 where [database_id] = db_id() 
 and [type] in (0,1)   

  --iterate files for the database  
 while(1=1)  
 begin   
  set @sizeBefore = 0;   
  set @sizeAfter = 0;   
  set @totalReduction = 0;  
  set @reduction = null;   
  set @iteration = 1; 
  set @name = null; 

  select top 1 @name = [name] 
  from @tab 
  where [reduction] is null;
  
  set @msg = 'Shrinking logical file ' + @name + ' of database ' + db_name();
  raiserror (@msg, 0, 1) with nowait
  
  if(@name is not null and @name <> '')   
  begin    
   --shrink the current file until the reduction is zero
   while(@reduction is null or @reduction > 0)    
   begin
    set @msg = 'shrink pass #' + convert(nvarchar(20),@iteration) + ' ...'; 
    raiserror (@msg, 0, 1) with nowait
       
    select top 1 @sizeBefore = [size] 
    from sysfiles 
    where fileid = file_id(@name);     
    
    dbcc shrinkfile (@name,20) with no_infomsgs;
    
    select top 1 @sizeAfter = [size] 
    from sysfiles 
    where fileid = file_id(@name); 
    
    set @reduction = @sizeBefore-@sizeAfter   ;  
    set @totalReduction = @totalReduction + @reduction;   
    set @iteration = @iteration + 1;
   end    
  
   update @tab 
   set [size] = ceiling((@sizeAfter * 8.) / 1024.)
   ,[reduction] =  ceiling((@totalReduction * 8.) / 1024.)
   where [name] = @name; 
  end 
  else break; 
 end   

 select 
 [Logical Name] = [name] 
 ,[File] = [filename] 
 ,[Reduction MB] = reduction 
 from @tab   
 order by [name];  
 
 select 
 @newSpaceFreeMB = sum([SpaceFreeMB])
 ,@newSizeMB = sum([SizeMB])
 from 
 (
  select 
  sf.[DbName]
  ,sf.[LogicalName]
  ,sf.[FileName]
  ,sf.[SizeMB]
  ,sf.[SpaceUsedMB]
  ,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]
  from
  (
   select 
   [DbName] = db_name()
   ,[LogicalName] = [Name]
   ,[Filename]
   ,[SpaceUsedMB] = convert(int,round(fileproperty([Name],'SpaceUsed')/128.000,0))
   ,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))
   from sysfiles
  ) as sf
 ) as total
 
 select 
 [OriginalSizeMB] =  @originalSizeMB
 ,[OriginalSpaceFreeMB] = @originalSpaceFreeMB
 ,[NewSizeMB] = @newSizeMB
 ,[NewSpaceFreeMB] = @newSpaceFreeMB
 ,[ReductionMB] = @originalSizeMB-@newSizeMB
end
else
begin
 set @msg = 'Skipping shrink b/c $(DbName) does not have the requisite $(FreeAtLeastMB) MB free.';
 raiserror (@msg, 0, 1) with nowait
end

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

--shrinks *ALL* files for a single database

--that has a minimum MB of freeable space

--------------------------------------------

:setvar DbName "foo"

:setvar FreeAtLeastMB "100"

use [$(DbName)];

declare @logname nvarchar(256)

,@name nvarchar(256)

,@sizeBefore int

,@sizeAfter int

,@reduction int

,@totalReduction int

,@rowcount int

,@iteration int

,@msg nvarchar(1000)

,@originalSpaceFreeMB int

,@originalSizeMB int

,@newSpaceFreeMB int

,@newSizeMB int

set @msg = 'Working on $(DbName)';

raiserror (@msg, 0, 1) with nowait --print immediately

select

@originalSpaceFreeMB = sum([SpaceFreeMB])

,@originalSizeMB = sum([SizeMB])

from

(

select

sf.[DbName]

,sf.[LogicalName]

,sf.[FileName]

,sf.[SizeMB]

,sf.[SpaceUsedMB]

,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]

from

(

select

[DbName] = db_name()

,[LogicalName] = [Name]

,[Filename]

,[SpaceUsedMB] = convert(int,round(fileproperty([Name],'SpaceUsed')/128.000,0))

,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))

from sysfiles

) as sf

) as total

set @msg = '$(DbName) size is ' + convert(nvarchar(100),@originalSizeMB) + ' MB and has a potential of ' + convert(nvarchar(100),@originalSpaceFreeMB) + ' MB to free. Minimum freeable required is $(FreeAtLeastMB) MB.';

raiserror (@msg, 0, 1) with nowait

if(@originalSpaceFreeMB > $(FreeAtLeastMB))

begin

declare @tab table ([name] nvarchar(256), [filename] nvarchar(256),[size] int, [reduction] int)

insert @tab select [name], [physical_name], convert(int,null),convert(int,null)

from sys.master_files

where [database_id] = db_id()

and [type] in (0,1)

--iterate files for the database

while(1=1)

begin

set @sizeBefore = 0;

set @sizeAfter = 0;

set @totalReduction = 0;

set @reduction = null;

set @iteration = 1;

set @name = null;

select top 1 @name = [name]

from @tab

where [reduction] is null;

set @msg = 'Shrinking logical file ' + @name + ' of database ' + db_name();

raiserror (@msg, 0, 1) with nowait

if(@name is not null and @name <> '')

begin

--shrink the current file until the reduction is zero

while(@reduction is null or @reduction > 0)

begin

set @msg = 'shrink pass #' + convert(nvarchar(20),@iteration) + ' ...';

raiserror (@msg, 0, 1) with nowait

select top 1 @sizeBefore = [size]

from sysfiles

where fileid = file_id(@name);

dbcc shrinkfile (@name,20) with no_infomsgs;

select top 1 @sizeAfter = [size]

from sysfiles

where fileid = file_id(@name);

set @reduction = @sizeBefore-@sizeAfter ;

set @totalReduction = @totalReduction + @reduction;

set @iteration = @iteration + 1;

end

update @tab

set [size] = ceiling((@sizeAfter * 8.) / 1024.)

,[reduction] = ceiling((@totalReduction * 8.) / 1024.)

where [name] = @name;

end

else break;

end

select

[Logical Name] = [name]

,[File] = [filename]

,[Reduction MB] = reduction

from @tab

order by [name];

select

@newSpaceFreeMB = sum([SpaceFreeMB])

,@newSizeMB = sum([SizeMB])

from

(

select

sf.[DbName]

,sf.[LogicalName]

,sf.[FileName]

,sf.[SizeMB]

,sf.[SpaceUsedMB]

,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]

from

(

select

[DbName] = db_name()

,[LogicalName] = [Name]

,[Filename]

,[SpaceUsedMB] = convert(int,round(fileproperty([Name],'SpaceUsed')/128.000,0))

,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))

from sysfiles

) as sf

) as total

select

[OriginalSizeMB] = @originalSizeMB

,[OriginalSpaceFreeMB] = @originalSpaceFreeMB

,[NewSizeMB] = @newSizeMB

,[NewSpaceFreeMB] = @newSpaceFreeMB

,[ReductionMB] = @originalSizeMB-@newSizeMB

end

else

begin

set @msg = 'Skipping shrink b/c $(DbName) does not have the requisite $(FreeAtLeastMB) MB free.';

raiserror (@msg, 0, 1) with nowait

end

Here’s the same thing as a stored procedure.

use [master];
create procedure spShrinkDatabase(@dbname nvarchar(255), @freeAtLeastMB int)
as
begin
	--------------------------------------------
	--shrinks *ALL* files for a single database  
	--that has a minimum MB of freeable space
	--------------------------------------------
	if(@dbname is null or len(@dbname)=0) 
	begin
		raiserror ('No database name specified.', 0, 1) with nowait --print immediately
		return;
	end

	if(@freeAtLeastMB is null or @freeAtLeastMB <= 0) set @freeAtLeastMB = 1;
	declare @freeAtLeastMBStr nvarchar(20);
        set = @freeAtLeastMBStr = convert(nvarchar(20),@freeAtLeastMB);

	declare @sql nvarchar(max);
	set @sql = 'use [' +  @dbname + '];   
	declare @logname nvarchar(256)  
	,@name nvarchar(256) 
	,@sizeBefore int 
	,@sizeAfter int  
	,@reduction int  
	,@totalReduction int  
	,@rowcount int
	,@iteration int      
	,@msg nvarchar(1000)
	,@originalSpaceFreeMB int
	,@originalSizeMB int
	,@newSpaceFreeMB int
	,@newSizeMB int

	set @msg = ''Working on ' +  @dbname + ''';
	raiserror (@msg, 0, 1) with nowait --print immediately

	select 
	@originalSpaceFreeMB = sum([SpaceFreeMB])
	,@originalSizeMB = sum([SizeMB])
	from 
	(
	 select 
	 sf.[DbName]
	 ,sf.[LogicalName]
	 ,sf.[FileName]
	 ,sf.[SizeMB]
	 ,sf.[SpaceUsedMB]
	 ,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]
	 from
	 (
	  select 
	  [DbName] = db_name()
	  ,[LogicalName] = [Name]
	  ,[Filename]
	  ,[SpaceUsedMB] = convert(int,round(fileproperty([Name],''SpaceUsed'')/128.000,0))
	  ,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))
	  from sysfiles
	 ) as sf
	) as total

	set @msg = ''' +  @dbname + ' size is '' + convert(nvarchar(100),@originalSizeMB) + '' MB and has a potential of '' + convert(nvarchar(100),@originalSpaceFreeMB) + '' MB to free. Minimum freeable required is ' +  @freeAtLeastMBStr + ' MB.'';
	raiserror (@msg, 0, 1) with nowait

	if(@originalSpaceFreeMB >= ' + @freeAtLeastMBStr + ') 
	begin
	 declare @tab table ([name] nvarchar(256), [filename] nvarchar(256),[size] int, [reduction] int)   

	 insert @tab  select [name], [physical_name], convert(int,null),convert(int,null)
	 from sys.master_files 
	 where [database_id] = db_id() 
	 and [type] in (0,1)   

	  --iterate files for the database  
	 while(1=1)  
	 begin   
	  set @sizeBefore = 0;   
	  set @sizeAfter = 0;   
	  set @totalReduction = 0;  
	  set @reduction = null;   
	  set @iteration = 1; 
	  set @name = null; 

	  select top 1 @name = [name] 
	  from @tab 
	  where [reduction] is null;
  
	  set @msg = ''Shrinking logical file '' + @name + '' of database '' + db_name();
	  raiserror (@msg, 0, 1) with nowait
  
	  if(@name is not null and @name <> '''')   
	  begin    
	   --shrink the current file until the reduction is zero
	   while(@reduction is null or @reduction > 0)    
	   begin
		set @msg = ''shrink pass #'' + convert(nvarchar(20),@iteration) + '' ...''; 
		raiserror (@msg, 0, 1) with nowait
       
		select top 1 @sizeBefore = [size] 
		from sysfiles 
		where fileid = file_id(@name);     
    
		dbcc shrinkfile (@name,20) with no_infomsgs;
    
		select top 1 @sizeAfter = [size] 
		from sysfiles 
		where fileid = file_id(@name); 
    
		set @reduction = @sizeBefore-@sizeAfter   ;  
		set @totalReduction = @totalReduction + @reduction;   
		set @iteration = @iteration + 1;
	   end    
  
	   update @tab 
	   set [size] = ceiling((@sizeAfter * 8.) / 1024.)
	   ,[reduction] =  ceiling((@totalReduction * 8.) / 1024.)
	   where [name] = @name; 
	  end 
	  else break; 
	 end   

	 select 
	 [Logical Name] = [name] 
	 ,[File] = [filename] 
	 ,[Reduction MB] = reduction 
	 from @tab   
	 order by [name];  
 
	 select 
	 @newSpaceFreeMB = sum([SpaceFreeMB])
	 ,@newSizeMB = sum([SizeMB])
	 from 
	 (
	  select 
	  sf.[DbName]
	  ,sf.[LogicalName]
	  ,sf.[FileName]
	  ,sf.[SizeMB]
	  ,sf.[SpaceUsedMB]
	  ,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]
	  from
	  (
	   select 
	   [DbName] = db_name()
	   ,[LogicalName] = [Name]
	   ,[Filename]
	   ,[SpaceUsedMB] = convert(int,round(fileproperty([Name],''SpaceUsed'')/128.000,0))
	   ,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))
	   from sysfiles
	  ) as sf
	 ) as total
 
	 select 
	 [OriginalSizeMB] =  @originalSizeMB
	 ,[OriginalSpaceFreeMB] = @originalSpaceFreeMB
	 ,[NewSizeMB] = @newSizeMB
	 ,[NewSpaceFreeMB] = @newSpaceFreeMB
	 ,[ReductionMB] = @originalSizeMB-@newSizeMB
	end
	else
	begin
	 set @msg = ''Skipping shrink b/c ' +  @dbname + ' does not have the requisite ' + @freeAtLeastMBStr + ' MB free.'';
	 raiserror (@msg, 0, 1) with nowait
	end';

	--select @sql
	exec(@sql);
end

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

use [master];

create procedure spShrinkDatabase(@dbname nvarchar(255), @freeAtLeastMB int)

begin

--------------------------------------------

--shrinks *ALL* files for a single database

--that has a minimum MB of freeable space

--------------------------------------------

if(@dbname is null or len(@dbname)=0)

begin

raiserror ('No database name specified.', 0, 1) with nowait --print immediately

return;

end

if(@freeAtLeastMB is null or @freeAtLeastMB <= 0) set @freeAtLeastMB = 1;

declare @freeAtLeastMBStr nvarchar(20);

set = @freeAtLeastMBStr = convert(nvarchar(20),@freeAtLeastMB);

declare @sql nvarchar(max);

set @sql = 'use [' + @dbname + '];

declare @logname nvarchar(256)

,@name nvarchar(256)

,@sizeBefore int

,@sizeAfter int

,@reduction int

,@totalReduction int

,@rowcount int

,@iteration int

,@msg nvarchar(1000)

,@originalSpaceFreeMB int

,@originalSizeMB int

,@newSpaceFreeMB int

,@newSizeMB int

set @msg = ''Working on ' + @dbname + ''';

raiserror (@msg, 0, 1) with nowait --print immediately

select

@originalSpaceFreeMB = sum([SpaceFreeMB])

,@originalSizeMB = sum([SizeMB])

from

(

select

sf.[DbName]

,sf.[LogicalName]

,sf.[FileName]

,sf.[SizeMB]

,sf.[SpaceUsedMB]

,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]

from

(

select

[DbName] = db_name()

,[LogicalName] = [Name]

,[Filename]

,[SpaceUsedMB] = convert(int,round(fileproperty([Name],''SpaceUsed'')/128.000,0))

,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))

from sysfiles

) as sf

) as total

set @msg = ''' + @dbname + ' size is '' + convert(nvarchar(100),@originalSizeMB) + '' MB and has a potential of '' + convert(nvarchar(100),@originalSpaceFreeMB) + '' MB to free. Minimum freeable required is ' + @freeAtLeastMBStr + ' MB.'';

raiserror (@msg, 0, 1) with nowait

if(@originalSpaceFreeMB >= ' + @freeAtLeastMBStr + ')

begin

declare @tab table ([name] nvarchar(256), [filename] nvarchar(256),[size] int, [reduction] int)

insert @tab select [name], [physical_name], convert(int,null),convert(int,null)

from sys.master_files

where [database_id] = db_id()

and [type] in (0,1)

--iterate files for the database

while(1=1)

begin

set @sizeBefore = 0;

set @sizeAfter = 0;

set @totalReduction = 0;

set @reduction = null;

set @iteration = 1;

set @name = null;

select top 1 @name = [name]

from @tab

where [reduction] is null;

set @msg = ''Shrinking logical file '' + @name + '' of database '' + db_name();

raiserror (@msg, 0, 1) with nowait

if(@name is not null and @name <> '''')

begin

--shrink the current file until the reduction is zero

while(@reduction is null or @reduction > 0)

begin

set @msg = ''shrink pass #'' + convert(nvarchar(20),@iteration) + '' ...'';

raiserror (@msg, 0, 1) with nowait

select top 1 @sizeBefore = [size]

from sysfiles

where fileid = file_id(@name);

dbcc shrinkfile (@name,20) with no_infomsgs;

select top 1 @sizeAfter = [size]

from sysfiles

where fileid = file_id(@name);

set @reduction = @sizeBefore-@sizeAfter ;

set @totalReduction = @totalReduction + @reduction;

set @iteration = @iteration + 1;

end

update @tab

set [size] = ceiling((@sizeAfter * 8.) / 1024.)

,[reduction] = ceiling((@totalReduction * 8.) / 1024.)

where [name] = @name;

end

else break;

end

select

[Logical Name] = [name]

,[File] = [filename]

,[Reduction MB] = reduction

from @tab

order by [name];

select

@newSpaceFreeMB = sum([SpaceFreeMB])

,@newSizeMB = sum([SizeMB])

from

(

select

sf.[DbName]

,sf.[LogicalName]

,sf.[FileName]

,sf.[SizeMB]

,sf.[SpaceUsedMB]

,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]

from

(

select

[DbName] = db_name()

,[LogicalName] = [Name]

,[Filename]

,[SpaceUsedMB] = convert(int,round(fileproperty([Name],''SpaceUsed'')/128.000,0))

,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))

from sysfiles

) as sf

) as total

select

[OriginalSizeMB] = @originalSizeMB

,[OriginalSpaceFreeMB] = @originalSpaceFreeMB

,[NewSizeMB] = @newSizeMB

,[NewSpaceFreeMB] = @newSpaceFreeMB

,[ReductionMB] = @originalSizeMB-@newSizeMB

end

else

begin

set @msg = ''Skipping shrink b/c ' + @dbname + ' does not have the requisite ' + @freeAtLeastMBStr + ' MB free.'';

raiserror (@msg, 0, 1) with nowait

end';

--select @sql

exec(@sql);

end

originally posted 2013-4-3

1	originally posted 2013-4-3

Store hierachical organization data
All tables in a multi-tenant database would have a [OrganizationName] field.
The Organization table stores the hierarchical relationships for each organization.
SQL Server 2008+ supports the hierarchyID data type (not used here), which would make some of the hierarchical manipulations easier. I needed to support SQL Server 2005 as well, so I didn’t have the pleasure.

CREATE TABLE [dbo].[Organization](
 [Name] [nvarchar](100) NOT NULL,
 [DisplayName] [nvarchar](100) NULL,
 [ParentName] [nvarchar](100) NULL,
 [Enabled] [bit] NOT NULL,
 CONSTRAINT [PK_Name] PRIMARY KEY CLUSTERED 
(
 [Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--get the hierarchical path for an organization
create function [dbo].[fGetOrganizationPath] (@organizationName nvarchar(100))
returns nvarchar(max)
as
begin
 declare @retVal nvarchar(max);
 set @retVal = N''

 select
 @retVal = 
 (
  select
   case when [OrganizationName] &lt;&gt; 'root' then N'/' + [OrganizationName] 
   else '' 
   end
  from [dbo].tfGetOrganizationNames_Ascend(@organizationName)
  for xml path(''), type, root).value('root[1]','nvarchar(max)'
 )
 return @retVal;
end
GO

--get parent organization names
create function [dbo].[tfGetOrganizationNames_Ascend] 
(
 @organizationName nvarchar(100)
)
returns @tab table ([OrganizationName] nvarchar(100), ParentNamespace nvarchar(100), Level int)
as
begin
 with c as (
 select
  n1.[OrganizationName]
  ,n1.[ParentOrganizationName]
  ,[Level] = 0
 from
  [dbo].[OrganizationName] n1
  left outer join [dbo].[OrganizationName] n2 on n1.[ParentOrganizationName] = n2.[OrganizationName] 
 where
  n1.[OrganizationName] = @organizationName
 union all
 select
  n1.[OrganizationName]
  ,n1.[ParentOrganizationName]
  ,[Level] = c.[Level] + 1
 from
  [dbo].[OrganizationName] n1
  inner join c on c.[ParentOrganizationName] = n1.[OrganizationName])
  
 insert @tab 
 select top 1000
  [OrganizationName]
  ,[ParentOrganizationName]
  ,[Level]
 from
  c
 order by
  [Level] desc
 option(maxrecursion 1000)
 
 return;
end
GO

--generic error handler
create procedure [dbo].[spHandleError] as
begin
 declare @errmsg nvarchar(2048)
 declare @severity tinyint
 declare @state tinyint
 declare @errno int
 declare @proc sysname
 declare @lineno int
            
 select @errmsg = error_message(), @severity = error_severity()
 ,@state  = error_state(), @errno = error_number()
 ,@proc   = error_procedure(), @lineno = error_line()
        
 IF @errmsg NOT LIKE '[[%]]'
 begin 
  select @errmsg = @errmsg + ' [' + isnull(quotename(@proc),'') + '] ' 
  raiserror(@errmsg, @severity, @state)
 end
 else raiserror(@errmsg, @severity, @state)
end
GO

--trigger to make sure organizations added to the Organization table meet the requirements
create trigger [dbo].[tCheckOrganizationName]
on [dbo].[Organization]
after insert,update
as
 begin try
  set nocount on
  set xact_abort on 
  declare @root nvarchar(10);
  set @root = 'root'; 
  
  --verifies new organization names against requirements prior to insert/update

  declare @tab table ([Name] nvarchar(100), [ParentName] nvarchar(100))
  declare @dummyTab table ([Name] nvarchar(100), [ParentName] nvarchar(100))
  
  --combine the existing and pending data. pending data for a organization name is used if the organization name is also found in existing data
  insert @tab
  select [Name],[ParentName] from inserted
  union 
  select [Name],[ParentName] from [dbo].[Name] where [Name] not in (select [Name] from inserted)
  
  --a non-root organization name must have a non-null parent organization name
  if exists (select [Name] from inserted where [Name] &lt;&gt; @root and [ParentName] is null)
  begin
   --error: non-root organization name requires a parent
   raiserror ('A non-root organization name must specify a parent organization name.',11,1)
  end 
  
  --the ROOT organization name must have a null parent organization name
  if exists (select [Name] from inserted where [Name] = @root and [ParentName] is not null)
  begin
   --error: root organization name cannot have a parent
   raiserror ('The root organization name must specify a null parent organization name.',11,1)
  end 
  
     --a organization name's parent organization name must exist
     if exists
     ( 
   select t2.[Name] 
   from @tab t 
   left outer join @tab t2 on t.[ParentName] = t2.[Name]
   where t.[ParentName] is not null and t2.[Name] is null
  )
  begin
   --error: non-existent parent specified
   raiserror ('A organization name specifies a parent organization name that does not exist.',11,1)
  end      
  
  --look for circular references in the pending hierarchy
  begin try
   declare @dummy bit; --trick the sql compiler. it wants a statement that ends with a ';' prior to the 'with' statement.

   with c as 
   (
    select distinct
     n1.[Name]
     ,n1.[ParentName]
    from
     @tab n1
     left outer join @tab n2 on n1.[ParentName] = n2.[Name] 
     
    union all
    
    select 
     n1.[Name]
     ,n1.[ParentName]
    from
     @tab n1
     inner join c on c.[ParentName] = n1.[Name]
   )
   
   insert @dummyTab
   select distinct *
   from c
   option(maxrecursion 1000) --this will throw an exception if circular references exist
   
  end try
  begin catch
   --error: cicular reference(s) found
   IF error_number() = 530 raiserror ('Operation would cause an illegal circular reference in organization name',11,1)
   else exec spHandleError
  end catch

 end try
 begin catch
  exec spHandleError
 end catch
GO
 
--called by the application when it creates a new SqlConnection
--the context travels with the connection for its lifetime.
--The context can be inspected by using T-SQL or C#.
create procedure [dbo].[spSetContext](@context nvarchar(100)) as
begin
 set nocount on
 declare @ctx varbinary(128)

 if([dbo].fIsNullOrWhitespace(@context) = 0) 
 begin
  
  declare @c varchar(127)
  set @c = @context
  declare @length tinyint 
  set @length = len(@c)
  select @ctx = convert(binary(1), @length) + convert(varbinary(127), @c)
 end
 else set @ctx = convert(binary(1), 0)
 set context_info @ctx
end
GO

--retrieve the context set on the current database connection
create function [dbo].[fGetContext]()
returns nvarchar(100) as 
begin
 declare @context nvarchar(100)
 set @context = null
 declare @ctx varbinary(128) 
 set @ctx = CONTEXT_INFO();
 declare @length tinyint 
 set @length = convert(tinyint, substring(@ctx, 1, 1))
 if(@length &gt; 0) select @context = convert(varchar(127), substring(@ctx, 2, 1 + @length))
 return @context
end
GO

--example:
--if the connection was created with the proper context (an organization name)
--a view can take advantage of the framework to select data for the current organization, 
--including data for its child organizations.
--This effectively creates a consolidated view of the data for a mult-tenant schema (one that stores a OrganizationName in each table)

create view [dbo].[v_SomeTable] as
select * 
from [npt_system32].[system].[tfGetOrganizationNamesForContext]() o
join [dbo].[SomeTable] t on o.[Organization] = t.[Organization]

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

CREATE TABLE [dbo].[Organization](

[Name] [nvarchar](100) NOT NULL,

[DisplayName] [nvarchar](100) NULL,

[ParentName] [nvarchar](100) NULL,

[Enabled] [bit] NOT NULL,

CONSTRAINT [PK_Name] PRIMARY KEY CLUSTERED

(

[Name] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

--get the hierarchical path for an organization

create function [dbo].[fGetOrganizationPath] (@organizationName nvarchar(100))

returns nvarchar(max)

begin

declare @retVal nvarchar(max);

set @retVal = N''

select

@retVal =

(

select

case when [OrganizationName] <> 'root' then N'/' + [OrganizationName]

else ''

end

from [dbo].tfGetOrganizationNames_Ascend(@organizationName)

for xml path(''), type, root).value('root[1]','nvarchar(max)'

)

return @retVal;

end

--get parent organization names

create function [dbo].[tfGetOrganizationNames_Ascend]

(

@organizationName nvarchar(100)

)

returns @tab table ([OrganizationName] nvarchar(100), ParentNamespace nvarchar(100), Level int)

begin

with c as (

select

n1.[OrganizationName]

,n1.[ParentOrganizationName]

,[Level] = 0

from

[dbo].[OrganizationName] n1

left outer join [dbo].[OrganizationName] n2 on n1.[ParentOrganizationName] = n2.[OrganizationName]

where

n1.[OrganizationName] = @organizationName

union all

select

n1.[OrganizationName]

,n1.[ParentOrganizationName]

,[Level] = c.[Level] + 1

from

[dbo].[OrganizationName] n1

inner join c on c.[ParentOrganizationName] = n1.[OrganizationName])

insert @tab

select top 1000

[OrganizationName]

,[ParentOrganizationName]

,[Level]

from

order by

[Level] desc

option(maxrecursion 1000)

return;

end

--generic error handler

create procedure [dbo].[spHandleError] as

begin

declare @errmsg nvarchar(2048)

declare @severity tinyint

declare @state tinyint

declare @errno int

declare @proc sysname

declare @lineno int

select @errmsg = error_message(), @severity = error_severity()

,@state = error_state(), @errno = error_number()

,@proc = error_procedure(), @lineno = error_line()

IF @errmsg NOT LIKE '[[%]]'

begin

select @errmsg = @errmsg + ' [' + isnull(quotename(@proc),'') + '] '

raiserror(@errmsg, @severity, @state)

end

else raiserror(@errmsg, @severity, @state)

end

--trigger to make sure organizations added to the Organization table meet the requirements

create trigger [dbo].[tCheckOrganizationName]

on [dbo].[Organization]

after insert,update

begin try

set nocount on

set xact_abort on

declare @root nvarchar(10);

set @root = 'root';

--verifies new organization names against requirements prior to insert/update

declare @tab table ([Name] nvarchar(100), [ParentName] nvarchar(100))

declare @dummyTab table ([Name] nvarchar(100), [ParentName] nvarchar(100))

--combine the existing and pending data. pending data for a organization name is used if the organization name is also found in existing data

insert @tab

select [Name],[ParentName] from inserted

union

select [Name],[ParentName] from [dbo].[Name] where [Name] not in (select [Name] from inserted)

--a non-root organization name must have a non-null parent organization name

if exists (select [Name] from inserted where [Name] <> @root and [ParentName] is null)

begin

--error: non-root organization name requires a parent

raiserror ('A non-root organization name must specify a parent organization name.',11,1)

end

--the ROOT organization name must have a null parent organization name

if exists (select [Name] from inserted where [Name] = @root and [ParentName] is not null)

begin

--error: root organization name cannot have a parent

raiserror ('The root organization name must specify a null parent organization name.',11,1)

end

--a organization name's parent organization name must exist

if exists

(

select t2.[Name]

from @tab t

left outer join @tab t2 on t.[ParentName] = t2.[Name]

where t.[ParentName] is not null and t2.[Name] is null

)

begin

--error: non-existent parent specified

raiserror ('A organization name specifies a parent organization name that does not exist.',11,1)

end

--look for circular references in the pending hierarchy

begin try

declare @dummy bit; --trick the sql compiler. it wants a statement that ends with a ';' prior to the 'with' statement.

with c as

(

select distinct

n1.[Name]

,n1.[ParentName]

from

@tab n1

left outer join @tab n2 on n1.[ParentName] = n2.[Name]

union all

select

n1.[Name]

,n1.[ParentName]

from

@tab n1

inner join c on c.[ParentName] = n1.[Name]

)

insert @dummyTab

select distinct *

from c

option(maxrecursion 1000) --this will throw an exception if circular references exist

end try

begin catch

--error: cicular reference(s) found

IF error_number() = 530 raiserror ('Operation would cause an illegal circular reference in organization name',11,1)

else exec spHandleError

end catch

end try

begin catch

exec spHandleError

end catch

--called by the application when it creates a new SqlConnection

--the context travels with the connection for its lifetime.

--The context can be inspected by using T-SQL or C#.

create procedure [dbo].[spSetContext](@context nvarchar(100)) as

begin

set nocount on

declare @ctx varbinary(128)

if([dbo].fIsNullOrWhitespace(@context) = 0)

begin

declare @c varchar(127)

set @c = @context

declare @length tinyint

set @length = len(@c)

select @ctx = convert(binary(1), @length) + convert(varbinary(127), @c)

end

else set @ctx = convert(binary(1), 0)

set context_info @ctx

end

--retrieve the context set on the current database connection

create function [dbo].[fGetContext]()

returns nvarchar(100) as

begin

declare @context nvarchar(100)

set @context = null

declare @ctx varbinary(128)

set @ctx = CONTEXT_INFO();

declare @length tinyint

set @length = convert(tinyint, substring(@ctx, 1, 1))

if(@length > 0) select @context = convert(varchar(127), substring(@ctx, 2, 1 + @length))

return @context

end

--example:

--if the connection was created with the proper context (an organization name)

--a view can take advantage of the framework to select data for the current organization,

--including data for its child organizations.

--This effectively creates a consolidated view of the data for a mult-tenant schema (one that stores a OrganizationName in each table)

create view [dbo].[v_SomeTable] as

select *

from [npt_system32].[system].[tfGetOrganizationNamesForContext]() o

join [dbo].[SomeTable] t on o.[Organization] = t.[Organization]

[C#] version of [dbo].[spSetContext]

protected void SetSqlContext(SqlConnection connection)
{
    const string sql = @"set context_info @ctx;"; //if not exists(select [Name] from (select [name] = dbo.fgetcontext()) as f1 where [Name] = '" + ns + "') raiserror('context not set!',11,1)";
    string ns = Namespace.Id;
    byte[] bytes = null;
    if (!string.IsNullOrWhiteSpace(ns))
    {
        //set the context
        //encode the namespace into a byte array
        var encoding = new System.Text.ASCIIEncoding();
        byte[] tempbytes = encoding.GetBytes(ns);
        bytes = new byte[tempbytes.Length + 1];
        tempbytes.CopyTo(bytes, 1);
        bytes[0] = Convert.ToByte(ns.Length);
    }
    else bytes = new byte[0]; //clear the context
            
    if (connection == null) return;
    SqlCommand command = GetNewSqlCommand(connection);
    command.CommandType = System.Data.CommandType.Text;
    command.CommandText = sql;
    var p1 = command.Parameters.Add("@ctx", System.Data.SqlDbType.VarBinary, 128);
    p1.Value = bytes;
    command.ExecuteNonQuery();
}

protected void SetSqlContext(SqlConnection connection)

{

const string sql = @"set context_info @ctx;"; //if not exists(select [Name] from (select [name] = dbo.fgetcontext()) as f1 where [Name] = '" + ns + "') raiserror('context not set!',11,1)";

string ns = Namespace.Id;

byte[] bytes = null;

if (!string.IsNullOrWhiteSpace(ns))

{

//set the context

//encode the namespace into a byte array

var encoding = new System.Text.ASCIIEncoding();

byte[] tempbytes = encoding.GetBytes(ns);

bytes = new byte[tempbytes.Length + 1];

tempbytes.CopyTo(bytes, 1);

bytes[0] = Convert.ToByte(ns.Length);

}

else bytes = new byte[0]; //clear the context

if (connection == null) return;

SqlCommand command = GetNewSqlCommand(connection);

command.CommandType = System.Data.CommandType.Text;

command.CommandText = sql;

var p1 = command.Parameters.Add("@ctx", System.Data.SqlDbType.VarBinary, 128);

p1.Value = bytes;

command.ExecuteNonQuery();

}

originally posted 2013-4-4

1	originally posted 2013-4-4

Using uniqueidentifiers (Guids) as primary/foreign keys in SQL Server has its advantages and disadvantages. There are a lot of good discussions of uniqueidentifiers vs. integers as keys out there. We use uniqueidentifiers and the purpose of this example is to address a problem that some encounter using them with large and active databases.

One problem is that primary keys are typically clustered indexes (they are stored in sort order), so if the key for a new record isn’t sort-wise at the end of the index, then the server must move some number of records down to free a slot for the new key. In an extreme case, the server performance can degrade because it’s spending too much time churning the indexes.

Uniqueidentifiers are longer keys (than integers), requiring a bit more work from the server to sort, but the real issue is that uniqueidentifiers generated with newid() are random and therefore not guaranteed to fall at the end of the index. This can be a problem for large tables that experience frequent inserts.

SQL Server exposes the newsequentialid() function that guarantees that 1) a uniqueidentifier is sort-wise larger than the previously generated uniqueidentifier for that SQL instance, so the server can append to the end of the index and avoid the churn, and 2) it retains the uniqueness characteristics that make uniqueidentifiers desirable. The problem with newsequentialid() is that it’s not entirely convenient to use. SQL Server 2005 thru 2008 (and likely 2012) require that the function only be used as a default value for a column.

So while you can do this (SQL 2008 example)…

[OK]
declare @id uniqueidentifier = newid();

This won’t work…
[NO!]
declare @id uniqueidentifier = newsequentialid();

So, if newsequentialid() can only be used as a default for a column and therefore you can only set it when inserting a new record and *not* including that field in the insert statement, then how can you get the value for that column in the newly inserted record?! Sometimes you need it and sometimes you don’t. The example shows how to get the value for those times when you need it. Here’s the table definition, look at the use of newsequentialguid() in the default constraint in the last statement.

CREATE TABLE [dbo].[MyTable](
 [Id] [uniqueidentifier] NOT NULL,
 [Namespace] [nvarchar](100) NOT NULL,
 [CreateDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
 [Id] ASC,
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_CreateDateTime]  DEFAULT (getdate()) FOR [CreateDateTime]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_Id]  DEFAULT (newsequentialid()) FOR [Id]
GO

CREATE TABLE [dbo].[MyTable](

[Id] [uniqueidentifier] NOT NULL,

[Namespace] [nvarchar](100) NOT NULL,

[CreateDateTime] [datetime] NOT NULL,

CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED

(

[Id] ASC,

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_MyTable_CreateDateTime] DEFAULT (getdate()) FOR [CreateDateTime]

ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_MyTable_Id] DEFAULT (newsequentialid()) FOR [Id]

Insert a record like the statement below, and SQL Server sets the default values for the [Id] and [CreateDateTime] fields. The [CreateDateTime] receives a default value of the current SQL Server time. The [Id] receives a default value of a new sequential uniqueidentifier. This works!

insert [MyTable]([Namespace]) values ('MyNamespace');

Insert a record like the statement below, and SQL Server sets the default value for the [CreateDateTime] field and you’re supplying the value for the [Id] field.
This solves the first problem (you know the value of [Id]), but creates another problem… the value for [Id] isn’t a sequential uniqueidentifier.

declare @id uniqueidentifier;
set @id = newid();
insert [MyTable]([Namespace],[Id]) values ('MyNamespace', @id);

declare @id uniqueidentifier;

set @id = newid();

insert [MyTable]([Namespace],[Id]) values ('MyNamespace', @id);

Insert a record like the statement below, and SQL Server sets the default values for the [Id] and [CreateDateTime] fields (good so far) and the new record’s [Id] can be retrieved by using a table variable

--declare the table variable
declare @tab table (id uniqueidentifier)

--insert the new record
insert [MyTable]([Namespace])
output inserted.id 
into @tab
select N'MyNamespace'

--declare the variable to hold the new [Id] value
declare @id uniqueidentifier;

--fetch the value of [Id] for the newly inserted record into the @id variable
select @id = id from @tab

--declare the table variable

declare @tab table (id uniqueidentifier)

--insert the new record

insert [MyTable]([Namespace])

output inserted.id

into @tab

select N'MyNamespace'

--declare the variable to hold the new [Id] value

declare @id uniqueidentifier;

--fetch the value of [Id] for the newly inserted record into the @id variable

select @id = id from @tab

Now you’ve got the best of both worlds! Here’s a stored procedure that sort of side-steps the issue with newsequentialid().
Even though a stored procedure isn’t always convenient, it works.

create procedure [dbo].[spNewSequentialId](@id uniqueidentifier out) 
as
begin
 declare @tab table(dummy bit, id uniqueidentifier default (newsequentialid()));
 insert @tab (dummy) values (0);
 select @id=id from @tab
end

create procedure [dbo].[spNewSequentialId](@id uniqueidentifier out)

begin

declare @tab table(dummy bit, id uniqueidentifier default (newsequentialid()));

insert @tab (dummy) values (0);

select @id=id from @tab

end

Wouldn’t it be great to turn this stored procedure into a function?! Yes it would, but it won’t work because of SQL’s side-effecting rules.
If you try something like this…
[NO!]

--[Won't work!]
create function [dbo].[spNewSequentialId]() 
returns uniqueidentifier
as
begin
 declare @id uniqueidentifier;
 declare @tab table(dummy bit, id uniqueidentifier default (newsequentialid()));
 insert @tab (dummy) values (0);
 select @id=id from @tab;
 return @id;
end

--[Won't work!]

create function [dbo].[spNewSequentialId]()

returns uniqueidentifier

begin

declare @id uniqueidentifier;

declare @tab table(dummy bit, id uniqueidentifier default (newsequentialid()));

insert @tab (dummy) values (0);

select @id=id from @tab;

return @id;

end

… you’re rewarded for you efforts with the following message

<span style="color: #ff0000;">Msg 443, Level 16, State 1, Procedure spNewSequentialId, Line 6
Invalid use of a side-effecting operator 'newsequentialid' within a function.</span>

1 2	<span style="color: #ff0000;">Msg 443, Level 16, State 1, Procedure spNewSequentialId, Line 6 Invalid use of a side-effecting operator 'newsequentialid' within a function.</span>

A C# SQL CLR Solution
Here’s an alternate method, using a C# CLR assembly. This is a “Visual C# SQL CLR Database Project For SQL Server 2005-2008″, these project types have some limitations in terms of what libraries they can reference, they’re restricted to using .Net Framework 3.5 SP1, and they induce registration and versioning headaches outside of those with regular T-SQL schema statements.

I put some info at the end regarding registration of the DLL with SQL Server. It’s a little different with 2005 and 2008, and depending on what you’re doing, you may have to deal with assembly trust issues. If you have the energy and patience to get over the initial hump, then a CLR assembly solution just might be the thing for you.

The C#
The SeqGuid class implements the behavior and is called by the Common class, which is called by a SQL database object. The Common class is the one to which SQL objects are pointed (more on that below).

[assembly: AllowPartiallyTrustedCallers]
namespace MyStuff
{
    public static class Common
    {
        public static Guid NewSequentialGuid()
        {
            return SeqGuid.NewSequentialGuid();
        }
   }
} 

...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;

namespace MyStuff
{
    public class SeqGuid
    {
        [DllImport("rpcrt4.dll", SetLastError = true)]
        static extern int UuidCreateSequential(out Guid guid);

        public static Guid NewSequentialGuid()  
        {
            Guid g;
            if (UuidCreateSequential(out g) != 0) throw new System.ComponentModel.Win32Exception(System.Runtime.InteropServices.Marshal.GetLastWin32Error());
            else
            {
                byte[] bytes = g.ToByteArray();
                Array.Reverse(bytes, 0, 4);
                Array.Reverse(bytes, 4, 2);
                Array.Reverse(bytes, 6, 2);
                return new Guid(bytes);
            }
        }
    }
}

[assembly: AllowPartiallyTrustedCallers]

namespace MyStuff

{

public static class Common

{

public static Guid NewSequentialGuid()

{

return SeqGuid.NewSequentialGuid();

}

...

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Runtime.InteropServices;

namespace MyStuff

{

public class SeqGuid

{

[DllImport("rpcrt4.dll", SetLastError = true)]

static extern int UuidCreateSequential(out Guid guid);

public static Guid NewSequentialGuid()

{

Guid g;

if (UuidCreateSequential(out g) != 0) throw new System.ComponentModel.Win32Exception(System.Runtime.InteropServices.Marshal.GetLastWin32Error());

else

{

byte[] bytes = g.ToByteArray();

Array.Reverse(bytes, 0, 4);

Array.Reverse(bytes, 4, 2);

Array.Reverse(bytes, 6, 2);

return new Guid(bytes);

}

T-SQL CLR Assembly Registration (arcane incantations)

--Note: To use ":setvar" command variables from the SQL Server Management Studio, 
--you'll have to check the "By default, open new queries in SQLCMD mode" box from 
--Tools-&gt;Options-&gt;Query Execution and you may have to open a new query window.

:setvar User "database user name goes here"
:setvar Db "database name goes here"
:setvar AssemblyName "assembly name goes here (the full logical name, not the file name)"
:setvar AssemblyPath "path to the assembly file, e.g. c:\projects\mystuff.dll"

use [master]
go

alter authorization on database::$(Db) TO $(User);
go

GRANT UNSAFE ASSEMBLY TO $(User)
go

use root_properties
go

exec sp_changedbowner '$(User)'
go

sp_configure 'show advanced options', 1;
go

reconfigure;
go

sp_configure 'clr enabled', 1;
go

reconfigure;
go

alter database $(Db) set trustworthy on;
go

create assembly [$(Assembly)] from '$(AssemblyPath)' with permission_set = unsafe;
go

--Note: To use ":setvar" command variables from the SQL Server Management Studio,

--you'll have to check the "By default, open new queries in SQLCMD mode" box from

--Tools->Options->Query Execution and you may have to open a new query window.

:setvar User "database user name goes here"

:setvar Db "database name goes here"

:setvar AssemblyName "assembly name goes here (the full logical name, not the file name)"

:setvar AssemblyPath "path to the assembly file, e.g. c:\projects\mystuff.dll"

use [master]

alter authorization on database::$(Db) TO $(User);

GRANT UNSAFE ASSEMBLY TO $(User)

use root_properties

exec sp_changedbowner '$(User)'

sp_configure 'show advanced options', 1;

reconfigure;

sp_configure 'clr enabled', 1;

reconfigure;

alter database $(Db) set trustworthy on;

create assembly [$(Assembly)] from '$(AssemblyPath)' with permission_set = unsafe;

If that worked, then create database objects that call the CLR function. You should see the assembly in the Programmability->Assemblies under your database in SQL Server Management Studio. Keep in mind that your case may require more or less work for registration. There’s usually at least one wrench in the gears to remove

--a scalar function to get a new sequential guid. This calls the registered C# assembly
create function [dbo].[fNewSequentialGuid]()
returns uniqueidentifier
as external name [&lt;AssemblyName&gt;].[MySqlStuff.Common].[NewSequentialGuid]
GO

--a table-valued function to get a new sequential guid
create function [dbo].[tfGetNewSequentialGuid]()
returns @tab table([Id] uniqueidentifier)
as
begin
 insert @tab
 select [dbo].[fNewSequentialGuid]();
 return;
end
GO

--a scalar function to get a new sequential guid. This calls the registered C# assembly

create function [dbo].[fNewSequentialGuid]()

returns uniqueidentifier

as external name [<AssemblyName>].[MySqlStuff.Common].[NewSequentialGuid]

--a table-valued function to get a new sequential guid

create function [dbo].[tfGetNewSequentialGuid]()

returns @tab table([Id] uniqueidentifier)

begin

insert @tab

select [dbo].[fNewSequentialGuid]();

return;

end

If all went to plan, the new C# function should be callable from T-SQL
select [dbo].[fNewSequentialGuid]() --as a regular function that returns a scalar
or
select [Id] from [dbo].[tfGetNewSequentialGuid]() --as a table-valued function that returns a single-column, single-row table

Note: In cases where a CLR function is to be called between databases, or even instances (requires a linked server to be defined), you may have to wrap the SQL object
for it to be usable. I don’t know why, and there may be another really easy configuration solution for this, but I never got down to the brass tacks of the issue.

--a scalar function that wraps the [dbo].[fNewSequentialGuid](). It *should* be visible to other databases.
create function [dbo].[fNewSequentialGuidX]()
returns uniqueidentifier
begin
 return [dbo].[fNewSequentialGuid]();
end
GO

--a scalar function that wraps the [dbo].[fNewSequentialGuid](). It *should* be visible to other databases.

create function [dbo].[fNewSequentialGuidX]()

returns uniqueidentifier

begin

return [dbo].[fNewSequentialGuid]();

end

originally posted 2013-6-27

1	originally posted 2013-6-27

--Delete SQL object using its schema-qualified name. For tables, the constraints are dropped first.
create procedure [dbo].[spDropObject] (@fullname nvarchar(520))
as
begin
 begin try
  declare @type nvarchar(5)
  declare @resolvedFullname nvarchar(520) 
  declare @resolvedName nvarchar(255) 
  set @type = null
  set @resolvedFullname = null
  set @resolvedName = null
  
  --find the object
  select 
  @type = o.[type] 
  ,@resolvedFullname = '[' + object_schema_name(o.id) +  '].[' + o.[name] + ']'
  ,@resolvedName = '[' + o.[name] + ']'
  from dbo.sysobjects o
  where id = object_id(@fullname)
  
  --PROCEDURE
  if(@type = 'P') 
  begin
   exec('drop procedure ' + @resolvedFullname);
   return;
  end
    
  --VIEW
  if(@type = 'V') 
  begin
   exec('drop view ' + @resolvedFullname);
   return;
  end

  --FUNCTION
  if(@type = 'FN' or @type = 'TF') 
  begin
   exec('drop function ' + @resolvedFullname);
   return;
  end

  --TRIGGER
  if(@type = 'TR') 
  begin
   exec('drop trigger ' + @resolvedFullname);
   return;
  end

  --CONSTRAINT
  if(@type = 'C' or @type = 'UQ' or @type = 'D' or @type = 'F' or @type = 'PK' or @type = 'K') 
  begin
   declare @fullTablename nvarchar(520);
   set @fullTablename = null

   --find the contraint's table
   select @fullTablename ='[' + object_schema_name(t.[object_id]) + '].[' + t.[Name] + ']'
   from sys.tables t
   join sys.schemas s on t.schema_id = s.schema_id
   where t.object_id = (select parent_obj from dbo.sysobjects where id = object_id(@resolvedFullname))

   exec('alter table ' + @fullTablename + ' drop constraint ' + @resolvedName);
   return; 
  end
  
  --TABLE (drop all constraints then drop the table)
  if(@type = 'U') 
  begin
   --find FK references to the table
   declare @fktab table([Name] nvarchar(255))
   insert @fktab 
   select 
   [Name] = '[' + object_name(fkc.[constraint_object_id]) + ']'
   /*
   ,[Parent] = '[' + object_schema_name(fkc.[parent_object_id]) +  '].[' + object_name(fkc.[parent_object_id]) + ']'
   ,[Ref] = '[' + object_schema_name(fkc.[referenced_object_id]) +  '].[' + object_name(fkc.[referenced_object_id]) + ']'
   */
   from sys.foreign_key_columns as fkc
   where referenced_object_id = object_id(@resolvedFullname)
   order by [Name]

   --iterate FKs
   while(1=1)
   begin
    declare @constraint nvarchar(255)
    set @constraint = null
    select top 1
    @constraint = [Name]
    from @fktab

    if(@constraint is not null)
    begin
     --drop FK constraint
     exec [dbo].[spDropObject] @constraint;
     delete from @fktab where [Name] = @constraint --remove current record from working table
    end
    else break;
   end   

   --find constraints for table
   declare @constraintTab table ([Name] nvarchar(255));

   insert @constraintTab
   select [name] 
   from sys.objects 
   where parent_object_id = object_id(@resolvedFullname)
   order by [name]

   --iterate constraints
   while(1=1)
   begin
    set @constraint = null;
    select top 1 @constraint = [Name] from @constraintTab
   
    if(@constraint is not null)
    begin
     --drop constraint
     exec [dbo].[spDropObject] @constraint;
     delete from @constraintTab where [Name] = @constraint --remove current record from working table
    end
    else break;
   end

   --drop table
   exec('drop table ' + @resolvedFullname);
   return;
  end
 end try
 begin catch
  declare @message nvarchar(max)
  set @message = error_message( ) ;
  print @message
 end catch
end

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

--Delete SQL object using its schema-qualified name. For tables, the constraints are dropped first.

create procedure [dbo].[spDropObject] (@fullname nvarchar(520))

begin

begin try

declare @type nvarchar(5)

declare @resolvedFullname nvarchar(520)

declare @resolvedName nvarchar(255)

set @type = null

set @resolvedFullname = null

set @resolvedName = null

--find the object

select

@type = o.[type]

,@resolvedFullname = '[' + object_schema_name(o.id) + '].[' + o.[name] + ']'

,@resolvedName = '[' + o.[name] + ']'

from dbo.sysobjects o

where id = object_id(@fullname)

--PROCEDURE

if(@type = 'P')

begin

exec('drop procedure ' + @resolvedFullname);

return;

end

--VIEW

if(@type = 'V')

begin

exec('drop view ' + @resolvedFullname);

return;

end

--FUNCTION

if(@type = 'FN' or @type = 'TF')

begin

exec('drop function ' + @resolvedFullname);

return;

end

--TRIGGER

if(@type = 'TR')

begin

exec('drop trigger ' + @resolvedFullname);

return;

end

--CONSTRAINT

if(@type = 'C' or @type = 'UQ' or @type = 'D' or @type = 'F' or @type = 'PK' or @type = 'K')

begin

declare @fullTablename nvarchar(520);

set @fullTablename = null

--find the contraint's table

select @fullTablename ='[' + object_schema_name(t.[object_id]) + '].[' + t.[Name] + ']'

from sys.tables t

join sys.schemas s on t.schema_id = s.schema_id

where t.object_id = (select parent_obj from dbo.sysobjects where id = object_id(@resolvedFullname))

exec('alter table ' + @fullTablename + ' drop constraint ' + @resolvedName);

return;

end

--TABLE (drop all constraints then drop the table)

if(@type = 'U')

begin

--find FK references to the table

declare @fktab table([Name] nvarchar(255))

insert @fktab

select

[Name] = '[' + object_name(fkc.[constraint_object_id]) + ']'

,[Parent] = '[' + object_schema_name(fkc.[parent_object_id]) + '].[' + object_name(fkc.[parent_object_id]) + ']'

,[Ref] = '[' + object_schema_name(fkc.[referenced_object_id]) + '].[' + object_name(fkc.[referenced_object_id]) + ']'

from sys.foreign_key_columns as fkc

where referenced_object_id = object_id(@resolvedFullname)

order by [Name]

--iterate FKs

while(1=1)

begin

declare @constraint nvarchar(255)

set @constraint = null

select top 1

@constraint = [Name]

from @fktab

if(@constraint is not null)

begin

--drop FK constraint

exec [dbo].[spDropObject] @constraint;

delete from @fktab where [Name] = @constraint --remove current record from working table

end

else break;

end

--find constraints for table

declare @constraintTab table ([Name] nvarchar(255));

insert @constraintTab

select [name]

from sys.objects

where parent_object_id = object_id(@resolvedFullname)

order by [name]

--iterate constraints

while(1=1)

begin

set @constraint = null;

select top 1 @constraint = [Name] from @constraintTab

if(@constraint is not null)

begin

--drop constraint

exec [dbo].[spDropObject] @constraint;

delete from @constraintTab where [Name] = @constraint --remove current record from working table

end

else break;

end

--drop table

exec('drop table ' + @resolvedFullname);

return;

end

end try

begin catch

declare @message nvarchar(max)

set @message = error_message( ) ;

print @message

end catch

end

originally posted 2013-7-25

1	originally posted 2013-7-25

A Unix timestamp is defined as the number of seconds elapsed between January 1, 1970 00:00:00 and a given date/time expressed in Universal Coordinated Time.
A timestamp can be generated up to the date 2038-1-19 3:14:7 before overflowing a 32-bit integer.
http://en.wikipedia.org/wiki/Unix_time

--create a Unix timestamp from current server UTC time
create function [dbo].[fGetUnixTimestamp]()
returns int
as 
begin
   return datediff(s,'1970-1-1',sysutcdatetime());
end
go

--convert a Unix timestamp to a datetime in UTC
create function [dbo].[fConvertUnixTimestampToDateTime] (@timestamp int)
returns datetime
as
begin
   return dateadd(s,@timestamp,'1970-1-1')
end
go

--convert a Unix timestamp to a datetime adjusted to the server's time zone
create function [dbo].[fConvertUnixTimestampToLocalDateTime] (@timestamp int)
returns datetime
as
begin
   return dateadd(hh,datediff(hh,sysutcdatetime(),getdate()),dateadd(s,@timestamp,'1970-1-1'))
end
go

--[TEST]

declare @ts int
select @ts = [dbo].[fGetUnixTimestamp]()

select [Timestamp] = @ts
,[UTC Time] = [dbo].[fConvertUnixTimestampToDateTime](@ts)
,[Local Time] = [dbo].[fConvertUnixTimestampToLocalDateTime](@ts)
,[Local Time Zone Offset from UTC] = datediff(hh,sysutcdatetime(),getdate())

/*
[EXAMPLE RESULTS]
Timestamp Local Time = 1374776005   
UTC Time  = Jul 25 2013  6:13PM
Local Time = Jul 25 2013 11:13AM
Local Time Zone Offset from UTC = -7
*/

--create a Unix timestamp from current server UTC time

create function [dbo].[fGetUnixTimestamp]()

returns int

begin

return datediff(s,'1970-1-1',sysutcdatetime());

end

--convert a Unix timestamp to a datetime in UTC

create function [dbo].[fConvertUnixTimestampToDateTime] (@timestamp int)

returns datetime

begin

return dateadd(s,@timestamp,'1970-1-1')

end

--convert a Unix timestamp to a datetime adjusted to the server's time zone

create function [dbo].[fConvertUnixTimestampToLocalDateTime] (@timestamp int)

returns datetime

begin

return dateadd(hh,datediff(hh,sysutcdatetime(),getdate()),dateadd(s,@timestamp,'1970-1-1'))

end

--[TEST]

declare @ts int

select @ts = [dbo].[fGetUnixTimestamp]()

select [Timestamp] = @ts

,[UTC Time] = [dbo].[fConvertUnixTimestampToDateTime](@ts)

,[Local Time] = [dbo].[fConvertUnixTimestampToLocalDateTime](@ts)

,[Local Time Zone Offset from UTC] = datediff(hh,sysutcdatetime(),getdate())

[EXAMPLE RESULTS]

Timestamp Local Time = 1374776005

UTC Time = Jul 25 2013 6:13PM

Local Time = Jul 25 2013 11:13AM

Local Time Zone Offset from UTC = -7

An alternative, encoding a DateTime into a BigInt and back again.

create function [dbo].[fEncodeDateTimeToBigInt](@dt datetime)
returns bigint
as
begin
 --note: resolution is to the second
 if(@dt is not null)
  return convert(bigint,
   DATEPART(second, @dt) +
   DATEPART(minute, @dt) * 100 +
   DATEPART(hour, @dt) * 10000 +
   DATEPART(day, @dt) * 1000000 +
   DATEPART(month, @dt) * 100000000 +
   DATEPART(year, @dt) * 10000000000)
 return null;
end
go

create function [dbo].[fDecodeBigIntToDateTime](@bi bigint)
returns datetime
as
begin
 if(@bi is not null)
 begin
  declare @str nvarchar(30)
  set @str = convert(nvarchar(30),@bi)
     if(len(@str)=14) return convert(datetime,
  substring(@str,1,4) + '-' 
  + substring(@str,5,2) + '-' 
  + substring(@str,7,2) + ' ' 
  + substring(@str,9,2) + ':' 
  + substring(@str,11,2) + ':' 
  + substring(@str,13,2));
 end
 return null;
end

--TEST
declare @nowUtc datetime
set @nowUtc = sysutcdatetime();

declare @bi bigint
set @bi = [dbo].[fEncodeDateTimeToBigInt](@nowUtc);

declare @dt datetime 
set @dt = [dbo].[fDecodeBigIntToDateTime](@bi);

select [Input DateTime] = @nowUtc, [Encoded BigInt] = @bi, [Decoded DateTime] = @dt

/*
[Example Results]
Input DateTime = 2013-07-25 20:55:30.293
Encoded BigInt = 20130725205530 
Decoded DateTime = 2013-07-25 20:55:30.000
*/

create function [dbo].[fEncodeDateTimeToBigInt](@dt datetime)

returns bigint

begin

--note: resolution is to the second

if(@dt is not null)

return convert(bigint,

DATEPART(second, @dt) +

DATEPART(minute, @dt) * 100 +

DATEPART(hour, @dt) * 10000 +

DATEPART(day, @dt) * 1000000 +

DATEPART(month, @dt) * 100000000 +

DATEPART(year, @dt) * 10000000000)

return null;

end

create function [dbo].[fDecodeBigIntToDateTime](@bi bigint)

returns datetime

begin

if(@bi is not null)

begin

declare @str nvarchar(30)

set @str = convert(nvarchar(30),@bi)

if(len(@str)=14) return convert(datetime,

substring(@str,1,4) + '-'

+ substring(@str,5,2) + '-'

+ substring(@str,7,2) + ' '

+ substring(@str,9,2) + ':'

+ substring(@str,11,2) + ':'

+ substring(@str,13,2));

end

return null;

end

--TEST

declare @nowUtc datetime

set @nowUtc = sysutcdatetime();

declare @bi bigint

set @bi = [dbo].[fEncodeDateTimeToBigInt](@nowUtc);

declare @dt datetime

set @dt = [dbo].[fDecodeBigIntToDateTime](@bi);

select [Input DateTime] = @nowUtc, [Encoded BigInt] = @bi, [Decoded DateTime] = @dt

[Example Results]

Input DateTime = 2013-07-25 20:55:30.293

Encoded BigInt = 20130725205530

Decoded DateTime = 2013-07-25 20:55:30.000

originally posted 2013-9-10

1	originally posted 2013-9-10

...commit tran
END TRY
BEGIN CATCH
  IF @@trancount > 0 ROLLBACK TRANSACTION
  print @@error
  print 'oh crap.'
END CATCH

...commit tran

END TRY

BEGIN CATCH

IF @@trancount > 0 ROLLBACK TRANSACTION

print @@error

print 'oh crap.'

END CATCH

originally posted 2013-9-11

1	originally posted 2013-9-11

Using setvar in T-SQL

:setvar db "mydb"
alter database $(db)
set restricted_user with rollback after 60 seconds
alter database $(db)
set single_user with rollback immediate
alter database $(db) set multi_user
drop database $(db)

:setvar db "mydb"

alter database $(db)

set restricted_user with rollback after 60 seconds

alter database $(db)

set single_user with rollback immediate

alter database $(db) set multi_user

drop database $(db)

originally posted 2013-9-7

1	originally posted 2013-9-7

There are many more female names in the census data from which this dataset was generated, so statistically the resultset is likely to be lop-sided. If this matters, then extra steps should be taken to balance the M to F ratio.

“Random” SQL database backup (random.zip)

/*
 Random generation of person records inserted into a [GeneratedPeople] table when [dbo].[spGeneratePeople] is executed.
 The source [Address], [Domain], [First], and [Last] tables must first be populated with seed values.
*/


/*
 Seed with address data
 [Id] field must be a unique sequential integer
*/
CREATE TABLE [dbo].[Address](
 [Id] [int] NOT NULL,
 [City] [nvarchar](64) NOT NULL,
 [State] [nvarchar](2) NOT NULL,
 [Zip] [nvarchar](5) NOT NULL,
 [County] [nvarchar](255) NOT NULL,
 [AreaCode] [nvarchar](16) NOT NULL,
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/*
 Seed with domain names, e.g. google.com
 [Id] field must be a unique sequential integer
*/
CREATE TABLE [dbo].[Domain](
 [Id] [int] NOT NULL,
 [Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Domain] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/*
 Seed with person first names and gender
 [Id] field must be a unique sequential integer
 (a good source is the U.S. census)
*/
CREATE TABLE [dbo].[First](
 [Id] [int] NOT NULL,
 [Name] [nvarchar](255) NOT NULL,
 [Gender] [nvarchar](1) NOT NULL,
 CONSTRAINT [PK_First] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/*
 Seed with person surnames
 [Id] field must be a unique sequential integer
 (a good source is the U.S. census)
*/
CREATE TABLE [dbo].[Last](
 [Id] [int] NOT NULL,
 [Name] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_Last] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


/*
 This table is created and populated with person records when the [dbo].[spGeneratePeople] procedure is executed
*/
CREATE TABLE [dbo].[GeneratedPeople](
 [Guid] [uniqueidentifier] NULL,
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [FirstName] [nvarchar](100) NULL,
 [LastName] [nvarchar](100) NULL,
 [Gender] [nvarchar](2) NULL,
 [DateOfBirth] [date] NULL,
 [Email] [nvarchar](100) NULL,
 [Phone] [nvarchar](12) NULL,
 [City] [nvarchar](64) NULL,
 [State] [nvarchar](2) NULL,
 [Zip] [nvarchar](5) NULL,
 [County] [nvarchar](255) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GeneratedPeople] ADD  DEFAULT (newsequentialid()) FOR [Guid]
GO


/*
 Called by [dbo].[spGeneratePeople] to generate a birth date
*/
create procedure [dbo].[spGenerateDateOfBirth](@maxPastYears int = 100, @date date out)
as
begin
 set @date = null;
 if(@maxPastYears is null or @maxPastYears &amp;lt; 0) set @maxPastYears = 100
 declare @year int = 0;
 declare @value nvarchar(10) = null;

 while(isdate(@value)=0)
 begin
  while( @year &amp;lt;  year(getdate())-@maxPastYears or @year &amp;gt; year(getdate()))
   select @year = case when ceiling(rand(convert(varbinary,newid())) * 2) = 1 then '19' else '20' end +  right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(99))),(2))

  declare @month int = right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(12))),(2))
  declare @day int = right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(31))),(2))
  set @value = convert(nvarchar(4),@year) + '-' + convert(nvarchar(2),@month) + '-' + convert(nvarchar(2),@day) 
 end
 set @date = convert(date,@value);
 return;
end
GO

/*
 Called by [dbo].[spGeneratePeople] to create a subset of the records in the Address table
*/
create function [dbo].[tfGetAddresses](@state nvarchar(2) = null, @county nvarchar(255) = null,  @city nvarchar(255) = null)
returns @addrs table
(
[Id] int
,[City] nvarchar(64)
,[State] nvarchar(2)
,[Zip] nvarchar(5)
,[County] nvarchar(255)
,[AreaCode] nvarchar(3)
)
as
begin
 insert @addrs
 select [Id] = ROW_NUMBER() OVER( ORDER BY [Zip]),[City],[State],[Zip],[County],[AreaCode]
 from [Address] 
 where ((@state is not null and [State]= @state) or (@state is null))
 and ((@county is not null and [County]= @county) or (@county is null))
 and ((@city is not null and [City]= @city) or (@city is null))

 return;
end
GO


/*
 Generates people records into a [GeneratedPeople] table (which is dropped and re-created each time)
 @number - the number of records to generate
 @state - if not null, filters addresses to the two char state code
 @county - if not null, filters addresses to the county
 @city - if not null, filters addresses to the city
*/
create procedure [dbo].[spGeneratePeople](@number int, @state nvarchar(2), @county nvarchar(255),@city nvarchar(255))
as 
begin
 declare @count int = 0;
 if(@number is null or @number&amp;lt;=0) set @number = 1

 if exists (select name from sys.tables where name = 'GeneratedPeople') drop table [GeneratedPeople]
 create table [GeneratedPeople]
 (
  [Guid] uniqueidentifier default newsequentialid()
  ,[Id] int identity(1,1)
  ,[FirstName] nvarchar(100)
  ,[LastName] nvarchar(100)
  ,[Gender] nvarchar(2)
  ,[DateOfBirth] date
  ,[Email] nvarchar(100)
  ,[Phone] nvarchar(12)
  ,[City] nvarchar(64)
  ,[State] nvarchar(2)
  ,[Zip] nvarchar(5)
  ,[County] nvarchar(255)
 )

 declare @addrs table
 (
 [Id] int
 ,[City] nvarchar(64)
 ,[State] nvarchar(2)
 ,[Zip] nvarchar(5)
 ,[County] nvarchar(255)
 ,[AreaCode] nvarchar(3)
 )

 insert @addrs
 select * 
 from  tfGetAddresses(@state,@county,@city)

 declare @maxAddrs int = (select max([Id]) from @addrs)
 declare @maxFirsts int = (select max([Id]) from [First])
 declare @maxLasts int = (select max([Id]) from [Last])
 declare @maxDomains int = (select max([Id]) from [Domain])

 while(@count&amp;lt;@number)
 begin
  declare @date date = null
  exec dbo.[spGenerateDateOfBirth] default,@date out
  
  declare @rnd float =rand(convert(varbinary,newid()));

  insert [GeneratedPeople] ([FirstName],[LastName],[Gender],[DateOfBirth],[Email],[Phone],[City],[State],[Zip],[County])
  select 
  [FirstName] = [First].[Name]
  ,[LastName] = [Last].[Name]
  ,[Gender] = [First].[Gender]
  ,[DateOfBirth] = @date
  ,[Email] = lower([First].[Name] + '_' + [Last].[Name] + '@' + [Domain].[Name])
  ,[Phone] = ((((a.[AreaCode] +'-')+right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(999))),(3)))+'-')+right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(9999))),(4)))
  ,a.[City]
  ,a.[State]
  ,a.[Zip]
  ,a.[County]
  from [First],[Last],[Domain],@addrs a
  where [First].[Id] = (select ceiling(@rnd * @maxFirsts))
  and [Last].[Id] = (select ceiling(@rnd * @maxLasts))
  and [Domain].[Id] = (select ceiling(@rnd * @maxDomains))
  and a.[Id] = (select ceiling(@rnd * @maxAddrs))

  set @count=@count+1
 end
end
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

Random generation of person records inserted into a [GeneratedPeople] table when [dbo].[spGeneratePeople] is executed.

The source [Address], [Domain], [First], and [Last] tables must first be populated with seed values.

Seed with address data

[Id] field must be a unique sequential integer

CREATE TABLE [dbo].[Address](

[Id] [int] NOT NULL,

[City] [nvarchar](64) NOT NULL,

[State] [nvarchar](2) NOT NULL,

[Zip] [nvarchar](5) NOT NULL,

[County] [nvarchar](255) NOT NULL,

[AreaCode] [nvarchar](16) NOT NULL,

CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Seed with domain names, e.g. google.com

[Id] field must be a unique sequential integer

CREATE TABLE [dbo].[Domain](

[Id] [int] NOT NULL,

[Name] [nvarchar](100) NOT NULL,

CONSTRAINT [PK_Domain] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Seed with person first names and gender

[Id] field must be a unique sequential integer

(a good source is the U.S. census)

CREATE TABLE [dbo].[First](

[Id] [int] NOT NULL,

[Name] [nvarchar](255) NOT NULL,

[Gender] [nvarchar](1) NOT NULL,

CONSTRAINT [PK_First] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Seed with person surnames

[Id] field must be a unique sequential integer

(a good source is the U.S. census)

CREATE TABLE [dbo].[Last](

[Id] [int] NOT NULL,

[Name] [nvarchar](255) NOT NULL,

CONSTRAINT [PK_Last] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

This table is created and populated with person records when the [dbo].[spGeneratePeople] procedure is executed

CREATE TABLE [dbo].[GeneratedPeople](

[Guid] [uniqueidentifier] NULL,

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

[FirstName] [nvarchar](100) NULL,

[LastName] [nvarchar](100) NULL,

[Gender] [nvarchar](2) NULL,

[DateOfBirth] [date] NULL,

[Email] [nvarchar](100) NULL,

[Phone] [nvarchar](12) NULL,

[City] [nvarchar](64) NULL,

[State] [nvarchar](2) NULL,

[Zip] [nvarchar](5) NULL,

[County] [nvarchar](255) NULL

) ON [PRIMARY]

ALTER TABLE [dbo].[GeneratedPeople] ADD DEFAULT (newsequentialid()) FOR [Guid]

Called by [dbo].[spGeneratePeople] to generate a birth date

create procedure [dbo].[spGenerateDateOfBirth](@maxPastYears int = 100, @date date out)

begin

set @date = null;

if(@maxPastYears is null or @maxPastYears &lt; 0) set @maxPastYears = 100

declare @year int = 0;

declare @value nvarchar(10) = null;

while(isdate(@value)=0)

begin

while( @year &lt; year(getdate())-@maxPastYears or @year &gt; year(getdate()))

select @year = case when ceiling(rand(convert(varbinary,newid())) * 2) = 1 then '19' else '20' end + right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(99))),(2))

declare @month int = right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(12))),(2))

declare @day int = right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(31))),(2))

set @value = convert(nvarchar(4),@year) + '-' + convert(nvarchar(2),@month) + '-' + convert(nvarchar(2),@day)

end

set @date = convert(date,@value);

return;

end

Called by [dbo].[spGeneratePeople] to create a subset of the records in the Address table

create function [dbo].[tfGetAddresses](@state nvarchar(2) = null, @county nvarchar(255) = null, @city nvarchar(255) = null)

returns @addrs table

(

[Id] int

,[City] nvarchar(64)

,[State] nvarchar(2)

,[Zip] nvarchar(5)

,[County] nvarchar(255)

,[AreaCode] nvarchar(3)

)

begin

insert @addrs

select [Id] = ROW_NUMBER() OVER( ORDER BY [Zip]),[City],[State],[Zip],[County],[AreaCode]

from [Address]

where ((@state is not null and [State]= @state) or (@state is null))

and ((@county is not null and [County]= @county) or (@county is null))

and ((@city is not null and [City]= @city) or (@city is null))

return;

end

Generates people records into a [GeneratedPeople] table (which is dropped and re-created each time)

@number - the number of records to generate

@state - if not null, filters addresses to the two char state code

@county - if not null, filters addresses to the county

@city - if not null, filters addresses to the city

create procedure [dbo].[spGeneratePeople](@number int, @state nvarchar(2), @county nvarchar(255),@city nvarchar(255))

begin

declare @count int = 0;

if(@number is null or @number&lt;=0) set @number = 1

if exists (select name from sys.tables where name = 'GeneratedPeople') drop table [GeneratedPeople]

create table [GeneratedPeople]

(

[Guid] uniqueidentifier default newsequentialid()

,[Id] int identity(1,1)

,[FirstName] nvarchar(100)

,[LastName] nvarchar(100)

,[Gender] nvarchar(2)

,[DateOfBirth] date

,[Email] nvarchar(100)

,[Phone] nvarchar(12)

,[City] nvarchar(64)

,[State] nvarchar(2)

,[Zip] nvarchar(5)

,[County] nvarchar(255)

)

declare @addrs table

(

[Id] int

,[City] nvarchar(64)

,[State] nvarchar(2)

,[Zip] nvarchar(5)

,[County] nvarchar(255)

,[AreaCode] nvarchar(3)

)

insert @addrs

select *

from tfGetAddresses(@state,@county,@city)

declare @maxAddrs int = (select max([Id]) from @addrs)

declare @maxFirsts int = (select max([Id]) from [First])

declare @maxLasts int = (select max([Id]) from [Last])

declare @maxDomains int = (select max([Id]) from [Domain])

while(@count&lt;@number)

begin

declare @date date = null

exec dbo.[spGenerateDateOfBirth] default,@date out

declare @rnd float =rand(convert(varbinary,newid()));

insert [GeneratedPeople] ([FirstName],[LastName],[Gender],[DateOfBirth],[Email],[Phone],[City],[State],[Zip],[County])

select

[FirstName] = [First].[Name]

,[LastName] = [Last].[Name]

,[Gender] = [First].[Gender]

,[DateOfBirth] = @date

,[Email] = lower([First].[Name] + '_' + [Last].[Name] + '@' + [Domain].[Name])

,[Phone] = ((((a.[AreaCode] +'-')+right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(999))),(3)))+'-')+right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(9999))),(4)))

,a.[City]

,a.[State]

,a.[Zip]

,a.[County]

from [First],[Last],[Domain],@addrs a

where [First].[Id] = (select ceiling(@rnd * @maxFirsts))

and [Last].[Id] = (select ceiling(@rnd * @maxLasts))

and [Domain].[Id] = (select ceiling(@rnd * @maxDomains))

and a.[Id] = (select ceiling(@rnd * @maxAddrs))

set @count=@count+1

end

<jco-blog/>

Get delimited list of column names for each table in a database

SQL 2012 Replacement of sp_dboption with ALTER DATABASE

Shrink All Physical Files of a Database

Multi-Tenant Hierachical Framework w/ SQL Server

SQL and Uniqueidentifiers as Keys

Drop database objects and dependencies using object full name

Unix Timestamp in SQL Server

The Best SQL Script Ending

Close All Connections and Drop Database

Random generation of person records for demo and testing purposes