I am currently reading Kalen Delany and others book on SQL Server 2008 System internals. If you have not read this book from cover to cover, you should. It has a wealth of knowledge about database engine details.
To demonstrate key ideas from the book, I am going to expand on the [MATH] database that contains PRIME numbers. The trial division algorithm inside this database uses a brute force method for calculating prime numbers. This article will focus on files and objects that can be viewed through the system views, system stored procedures, or dynamic management views.
The first snippet of code creates a database name [MATH] with location, size, and file growth options.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
<span style="color: #008000; font-size: x-small;">/* Create a database to hold the prime numbers */ -- Which database to use. USE [master] GO -- Delete existing database IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MATH') DROP DATABASE MATH GO -- Add new database CREATE DATABASE MATH ON PRIMARY ( NAME = N'MATH_PRI_DAT', FILENAME = N'C:\MSSQL\DATA\MATH.MDF' , SIZE = 64MB, FILEGROWTH = 64MB) LOG ON ( NAME = N'MATH_ALL_LOG', FILENAME = N'C:\MSSQL\LOG\MATH.LDF' , SIZE = 32MB, FILEGROWTH = 32MB) GO </span> |
The second snippet of code creates a file partitioning scheme to divide the discovered primes from one to six million into three groups. For each group, a file group with one secondary data file is created. Next, a partition function [PF_HASH_BY_VALUE] is created to divide the values into groups. Last but not least, a partition scheme [PS_HASH_BY_VALUE] maps the function onto the file groups.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
<span style="color: #008000; font-size: x-small;">/* Create a partition scheme for the numbers */ -- Which database to use. USE [MATH] GO -- Define 3 file groups for a simple partition scheme ALTER DATABASE MATH ADD FILEGROUP FG_MATH_P1; ALTER DATABASE MATH ADD FILEGROUP FG_MATH_P2; ALTER DATABASE MATH ADD FILEGROUP FG_MATH_P3; go -- Define 3 files and link to groups ALTER DATABASE MATH ADD FILE ( NAME = N'FN_MATH_P1', FILENAME = N'C:\MSSQL\DATA\FN_MATH_P1.NDF' , SIZE = 64MB, FILEGROWTH = 64MB) TO FILEGROUP FG_MATH_P1; ALTER DATABASE MATH ADD FILE ( NAME = N'FN_MATH_P2', FILENAME = N'C:\MSSQL\DATA\FN_MATH_P2.NDF' , SIZE = 64MB, FILEGROWTH = 64MB) TO FILEGROUP FG_MATH_P2; ALTER DATABASE MATH ADD FILE ( NAME = N'FN_MATH_P3', FILENAME = N'C:\MSSQL\DATA\FN_MATH_P3.NDF' , SIZE = 64MB, FILEGROWTH = 64MB) TO FILEGROUP FG_MATH_P3; go -- Create the partition function CREATE PARTITION FUNCTION PF_HASH_BY_VALUE (BIGINT) AS RANGE LEFT FOR VALUES (2000000, 4000000) go -- Create the partition scheme CREATE PARTITION SCHEME PS_HASH_BY_VALUE AS PARTITION PF_HASH_BY_VALUE TO (FG_MATH_P1, FG_MATH_P2, FG_MATH_P3); go </span> |
The third snippet of code creates a table [TBL_PRIMES] with a primary key applied to the partitioning scheme. Please note that check, default and primary key constraints are demonstrated in the example.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<span style="color: #008000; font-size: x-small;">/* Create a table to hold the prime search information */ -- Which database to use. USE [MATH] GO -- Delete existing table IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DBO].[TBL_PRIMES]') AND type in (N'U')) DROP TABLE [DBO].[TBL_PRIMES] GO -- Add new table CREATE TABLE [DBO].[TBL_PRIMES] ( [MY_VALUE] [bigint] NOT NULL, [MY_DIVISION] [bigint] NOT NULL CONSTRAINT [CHK_TBL_PRIMES] CHECK ([MY_DIVISION] > 0), [MY_TIME] [datetime] NOT NULL CONSTRAINT [DF_TBL_PRIMES] DEFAULT (GETDATE()) CONSTRAINT [PK_TBL_PRIMES] PRIMARY KEY CLUSTERED ([MY_VALUE] ASC) ) ON PS_HASH_BY_VALUE ([MY_VALUE]) GO </span> |
The fourth snippet of code creates a trigger [TRG_TBL_PRIMES] that makes sure the divisor is less than the found prime. While this condition will never be meet to generate a roll back and error message, it is an good example of a data manipulation language (DML) trigger.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
<span style="color: #008000; font-size: x-small;">/* Create a trigger to make sure division < value */ -- Which database to use. USE [MATH] GO -- Delete existing trigger IF OBJECT_ID('[DBO].[TRG_TBL_PRIMES]', 'TR') IS NOT NULL DROP TRIGGER [DBO].[TRG_TBL_PRIMES] GO -- Add new trigger CREATE TRIGGER [DBO].[TRG_TBL_PRIMES] ON [DBO].[TBL_PRIMES] FOR INSERT, UPDATE AS -- Nothing to do if( @@rowcount = 0) return; -- Declare variables declare @Var_Change varchar(10); declare @Var_Message varchar(128); -- What type of change? if exists(select * from inserted) begin -- Categorize change if not exists (select * from deleted) set @Var_Change = 'inserting'; else set @Var_Change = 'updating'; -- Make sure it meets criteria if exists (select * from [DBO].[TBL_PRIMES] where [MY_DIVISION] >= [MY_VALUE]) begin rollback; set @Var_Message = 'Division value exceeded prime number when ' + @Var_Change + ' the row.'; raiserror(@Var_Message, 16, 10); end; end; GO </span> |
The fifth snippet of code creates a stored procedure. The [SP_IS_PRIME] stored procedure takes a number as input and returns 1 if the number is prime. Otherwise, it returns an zero for all non-prime numbers.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
<span style="color: #008000; font-size: x-small;">/* Create a procedure to determine if number is prime */ -- Which database to use. USE [MATH] GO -- Delete existing procedure IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_IS_PRIME]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[SP_IS_PRIME] GO -- Create the stored procedure from scratch CREATE PROCEDURE [dbo].[SP_IS_PRIME] @VAR_NUM2 BIGINT AS BEGIN -- LOCAL VARIABLES DECLARE @VAR_CNT2 BIGINT; DECLARE @VAR_MAX2 BIGINT; -- NOT A PRIME NUMBER IF (@VAR_NUM2 = 1) RETURN 0; -- A PRIME NUMBER IF (@VAR_NUM2 = 2) RETURN 1; -- SET UP COUNTERS SELECT @VAR_CNT2 = 2; SELECT @VAR_MAX2 = SQRT(@VAR_NUM2) + 1; -- TRIAL DIVISION 2 TO SQRT(X) WHILE (@VAR_CNT2 <= @VAR_MAX2) BEGIN -- NOT A PRIME NUMBER IF (@VAR_NUM2 % @VAR_CNT2) = 0 RETURN 0; -- INCREMENT COUNTER SELECT @VAR_CNT2 = @VAR_CNT2 + 1; END; -- A PRIME NUMBER RETURN 1; END GO </span> |
The sixth snippet of code creates a stored procedure. The [SP_STORE_PRIMES] stored procedure finds and stores discovered prime numbers from @VAR_ALPHA to @VAR_OMEGA. These discovered primes are stored in the table [TBL_PRIMES] for later analysis.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
<span style="color: #008000; font-size: x-small;">/* Create a procedure to store primes from x to y. */ -- Which database to use. USE [MATH] GO -- Delete existing procedure IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_STORE_PRIMES]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[SP_STORE_PRIMES] GO -- Create the stored procedure from scratch CREATE PROCEDURE SP_STORE_PRIMES @VAR_ALPHA BIGINT, @VAR_OMEGA BIGINT AS BEGIN -- DECLARE VARIABLES DECLARE @VAR_CNT1 BIGINT; DECLARE @VAR_RET1 INT; -- SET VARIABLES SELECT @VAR_RET1 = 0; SELECT @VAR_CNT1 = @VAR_ALPHA; -- CHECK EACH NUMBER FOR PRIMENESS WHILE (@VAR_CNT1 <= @VAR_OMEGA) BEGIN -- ARE WE PRIME? EXEC @VAR_RET1 = DBO.SP_IS_PRIME @VAR_CNT1; -- FOUND A PRIME IF (@VAR_RET1 = 1) BEGIN INSERT INTO [DBO].[TBL_PRIMES] (MY_VALUE, MY_DIVISION) VALUES (@VAR_CNT1, SQRT(@VAR_CNT1)); PRINT @VAR_CNT1; END ELSE BEGIN PRINT '...'; END; -- INCREMENT COUNTER SELECT @VAR_CNT1 = @VAR_CNT1 + 1 END; END GO </span> |
The seventh snippet of code creates view [VW_ONE_HUNDRED_K_PRIMES] to return all primes in the 100 K range.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<span style="color: #008000; font-size: x-small;">/* Create a view see primes from 100K to 199K */ -- Which database to use. USE [MATH] GO -- Delete existing procedure IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VW_ONE_HUNDRED_K_PRIMES]') AND type = 'V') DROP VIEW [dbo].[VW_ONE_HUNDRED_K_PRIMES] GO -- Create the stored procedure from scratch CREATE VIEW [dbo].[VW_ONE_HUNDRED_K_PRIMES] AS SELECT * FROM [dbo].[TBL_PRIMES] WHERE [MY_VALUE] > 100000 AND [MY_VALUE] < 200000 GO </span> |
The eight snippet of code creates function [FN_GET_PRIMES_BY_RANGE] to return a table containing prime numbers from @VAR_START to @VAR_END.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
<span style="color: #008000; font-size: x-small;">/* Create a function to return a table of primes from x to y */ -- Which database to use. USE [MATH] GO -- Delete existing procedure IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FN_GET_PRIMES_BY_RANGE]') AND type = 'IF') DROP FUNCTION [dbo].[FN_GET_PRIMES_BY_RANGE] GO -- Create the function from scratch CREATE FUNCTION [dbo].[FN_GET_PRIMES_BY_RANGE] (@VAR_START bigint, @VAR_END bigint) RETURNS TABLE AS RETURN ( SELECT [MY_VALUE] FROM [dbo].[TBL_PRIMES] WHERE [MY_VALUE] > @VAR_START AND [MY_VALUE] < @VAR_START ); GO </span> |
The ninth snippet of code executes the stored procedure [SP_STORE_PRIMES] to discover prime numbers from zero to six million.
|
1 2 3 4 5 6 |
<span style="color: #008000; font-size: x-small;">/* Calculate primes between 1 & 6 Million */ EXEC SP_STORE_PRIMES 1, 6000000 </span> |
In short, we have a sufficiently complicated enough database to demonstrate files and objects that can be viewed through the system views, system stored procedures, or dynamic management views. Next time, I will be reviewing code to examine the database files.
