SQL Server – Create Number Table

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.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s