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
- Set statements
- set nocount on
- No need for number of records affected
- set xact_abort
- Abort sql batch if error
- set nocount on
- Create Schema, if it does not exist
- if schema_id(<schema_name> is null, exec(“create schema [schema-name]”)
- Create Table, if it does not exist
- If object_id(<table-name>) is null, create table
- Truncate table
- Remove previous records from table
- Insert records
- Insert into <table>, default values
- 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.