Background

A family member and I were discussing the impact of different settings on database performance.

He kept trying to repeat the same point.

After a while, I pressed him to show me.

But, he wouldn’t.

Code

Outline

1. Set statements
   • set nocount on
     • No need for number of records affected
   • set xact_abort
     • Abort sql batch if error
2. Create Schema, if it does not exist
   • if schema_id(<schema_name> is null, exec(“create schema [schema-name]”)
3. Create Table, if it does not exist
   • If object_id(<table-name>) is null, create table
4. Truncate table
   • Remove previous records from table
5. Insert records
   • Insert into <table>, default values
6. Go < N>
   • Execute previous batch this many times

SQL

use [tempdb]
go

set nocount on;
go

set xact_abort on
go

if schema_id('lab') is null
begin

    exec('create schema [lab] authorization [dbo]')

end
go

if object_id('[lab].[number]') is null
begin


    create table [lab].[number]
    (
        [id] bigint not null
            identity(1,1)

        , constraint [PK_Lab_Number]
            primary key
            (
                [id]
            )
    )

end
go

truncate table [lab].[number]
go

insert into [lab].[number]
default values;
go 5000

declare @lNumberMax bigint

set @lNumberMax = SCOPE_IDENTITY()

print 'Last Assigned Identity is '
        + cast(@lNumberMax as varchar(30))

Summary

I wish my family member would have taken the time to demonstrate his point.

He chose not to.

But, thankfully I have a lab environment, and was able to use transact SQL.

I took the point further and proved him right after more work on my part.