{"id":2765,"date":"2012-10-16T17:04:03","date_gmt":"2012-10-16T17:04:03","guid":{"rendered":"http:\/\/craftydba.com\/?p=2765"},"modified":"2012-10-17T19:13:56","modified_gmt":"2012-10-17T19:13:56","slug":"database-files-objects-part-1","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=2765","title":{"rendered":"Database Files & Objects – Part 1"},"content":{"rendered":"

\"\"<\/a><\/p>\n

I am currently reading Kalen Delany<\/a> 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.<\/p>\n

To demonstrate key ideas from the book, I am going to expand on the [MATH] database that contains PRIME numbers. The trial division<\/a> algorithm inside this database uses a brute force method for calculating prime numbers<\/a>. This article will focus on files and objects that can be viewed through the system views, system stored procedures, or dynamic management views.<\/p>\n

The first snippet of code creates a database<\/a> name [MATH] with location, size, and file growth options.<\/p>\n

\/*  \r\n\tCreate a database to hold the prime numbers\r\n*\/\r\n\r\n\r\n-- Which database to use.\r\nUSE [master]\r\nGO\r\n\r\n-- Delete existing database\r\nIF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MATH')\r\nDROP DATABASE MATH\r\nGO\r\n\r\n-- Add new database\r\nCREATE DATABASE MATH ON  \r\n PRIMARY \r\n  ( NAME = N'MATH_PRI_DAT', FILENAME = N'C:\\MSSQL\\DATA\\MATH.MDF' , SIZE = 64MB, FILEGROWTH = 64MB) \r\n LOG ON \r\n  ( NAME = N'MATH_ALL_LOG', FILENAME = N'C:\\MSSQL\\LOG\\MATH.LDF' , SIZE = 32MB, FILEGROWTH = 32MB)\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
The second snippet of code creates a file partitioning<\/a> 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.<\/p>\n
\/*  \r\n\tCreate a partition scheme for the numbers\r\n*\/\r\n\r\n-- Which database to use.\r\nUSE [MATH]\r\nGO\r\n\r\n-- Define 3 file groups for a simple partition scheme\r\nALTER DATABASE MATH ADD FILEGROUP FG_MATH_P1;\r\nALTER DATABASE MATH ADD FILEGROUP FG_MATH_P2;\r\nALTER DATABASE MATH ADD FILEGROUP FG_MATH_P3;\r\ngo\r\n  \r\n-- Define 3 files and link to groups\r\nALTER DATABASE MATH ADD FILE \r\n( NAME = N'FN_MATH_P1', FILENAME = N'C:\\MSSQL\\DATA\\FN_MATH_P1.NDF' , SIZE = 64MB, FILEGROWTH = 64MB) \r\nTO FILEGROUP FG_MATH_P1;\r\n\r\nALTER DATABASE MATH ADD FILE \r\n( NAME = N'FN_MATH_P2', FILENAME = N'C:\\MSSQL\\DATA\\FN_MATH_P2.NDF' , SIZE = 64MB, FILEGROWTH = 64MB) \r\nTO FILEGROUP FG_MATH_P2;\r\n\r\nALTER DATABASE MATH ADD FILE \r\n( NAME = N'FN_MATH_P3', FILENAME = N'C:\\MSSQL\\DATA\\FN_MATH_P3.NDF' , SIZE = 64MB, FILEGROWTH = 64MB) \r\nTO FILEGROUP FG_MATH_P3;\r\ngo\r\n\r\n-- Create the partition function\r\nCREATE PARTITION FUNCTION PF_HASH_BY_VALUE (BIGINT) AS RANGE LEFT FOR VALUES (2000000, 4000000)\r\ngo\r\n\r\n-- Create the partition scheme\r\nCREATE PARTITION SCHEME PS_HASH_BY_VALUE \r\nAS PARTITION PF_HASH_BY_VALUE\r\nTO (FG_MATH_P1, FG_MATH_P2, FG_MATH_P3);\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
The third snippet of code creates a table<\/a> [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.<\/p>\n
\/*  \r\n\tCreate a table to hold the prime search information\r\n*\/\r\n\r\n-- Which database to use.\r\nUSE [MATH]\r\nGO\r\n\r\n-- Delete existing table\r\nIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DBO].[TBL_PRIMES]') AND type in (N'U'))\r\nDROP TABLE [DBO].[TBL_PRIMES]\r\nGO\r\n\r\n-- Add new table\r\nCREATE TABLE [DBO].[TBL_PRIMES] \r\n(\r\n\t[MY_VALUE] [bigint] NOT NULL,\r\n\t[MY_DIVISION] [bigint] NOT NULL CONSTRAINT [CHK_TBL_PRIMES] CHECK ([MY_DIVISION] > 0),\r\n\t[MY_TIME] [datetime] NOT NULL CONSTRAINT [DF_TBL_PRIMES] DEFAULT (GETDATE())\r\n    CONSTRAINT [PK_TBL_PRIMES] PRIMARY KEY CLUSTERED ([MY_VALUE] ASC)\r\n) ON PS_HASH_BY_VALUE ([MY_VALUE])\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
The fourth snippet of code creates a trigger<\/a> [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.<\/p>\n
\/*  \r\n\tCreate a trigger to make sure division < value\r\n*\/\r\n\r\n-- Which database to use.\r\nUSE [MATH]\r\nGO\r\n\r\n-- Delete existing trigger\r\nIF  OBJECT_ID('[DBO].[TRG_TBL_PRIMES]', 'TR') IS NOT NULL\r\nDROP TRIGGER [DBO].[TRG_TBL_PRIMES]\r\nGO\r\n\r\n-- Add new trigger\r\nCREATE TRIGGER [DBO].[TRG_TBL_PRIMES] ON [DBO].[TBL_PRIMES]\r\nFOR INSERT, UPDATE \r\nAS\r\n    -- Nothing to do\r\n    if( @@rowcount = 0) return;\r\n\r\n    -- Declare variables\r\n    declare @Var_Change varchar(10);\r\n    declare @Var_Message varchar(128);\r\n\r\n    -- What type of change?\r\n    if exists(select * from inserted) \r\n    begin\r\n\r\n        -- Categorize change\r\n\tif not exists (select * from deleted)\r\n \t    set @Var_Change = 'inserting';\r\n\telse\r\n\t    set @Var_Change = 'updating';\r\n\r\n\t-- Make sure it meets criteria\r\n\tif exists (select * from [DBO].[TBL_PRIMES] where [MY_DIVISION] >= [MY_VALUE])\r\n\tbegin\r\n\t    rollback;\r\n \t    set @Var_Message = 'Division value exceeded prime number when ' + @Var_Change + ' the row.';\r\n            raiserror(@Var_Message, 16, 10);\r\n\tend;\r\n    end;\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
The fifth snippet of code creates a stored procedure<\/a>.  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.<\/p>\n
\/*  \r\n\tCreate a procedure to determine if number is prime\r\n*\/\r\n\r\n\r\n-- Which database to use.\r\nUSE [MATH]\r\nGO\r\n\r\n-- Delete existing procedure\r\nIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_IS_PRIME]') AND type in (N'P', N'PC'))\r\nDROP PROCEDURE [dbo].[SP_IS_PRIME]\r\nGO\r\n\r\n-- Create the stored procedure from scratch\r\nCREATE PROCEDURE [dbo].[SP_IS_PRIME]\r\n    @VAR_NUM2 BIGINT\r\nAS\r\nBEGIN\r\n    -- LOCAL VARIABLES\r\n    DECLARE @VAR_CNT2 BIGINT;\r\n    DECLARE @VAR_MAX2 BIGINT;\r\n\r\n    -- NOT A PRIME NUMBER\r\n    IF (@VAR_NUM2 = 1)\r\n        RETURN 0;            \r\n\r\n    -- A PRIME NUMBER\r\n    IF (@VAR_NUM2 = 2)\r\n        RETURN 1;            \r\n\r\n    -- SET UP COUNTERS    \r\n    SELECT @VAR_CNT2 = 2;\r\n    SELECT @VAR_MAX2 = SQRT(@VAR_NUM2) + 1;\r\n\r\n    -- TRIAL DIVISION 2 TO SQRT(X)\r\n    WHILE (@VAR_CNT2 <= @VAR_MAX2)\r\n    BEGIN\r\n        -- NOT A PRIME NUMBER\r\n        IF (@VAR_NUM2 % @VAR_CNT2) = 0\r\n            RETURN 0;            \r\n\r\n        -- INCREMENT COUNTER\r\n        SELECT @VAR_CNT2 = @VAR_CNT2 + 1;\r\n        \r\n    END;\r\n\r\n    -- A PRIME NUMBER\r\n    RETURN 1;\r\n    \r\nEND\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
The sixth snippet of code creates a stored procedure<\/a>.  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.<\/p>\n
\/*    \r\n\tCreate a procedure to store primes from x to y.\r\n*\/\r\n\r\n-- Which database to use.\r\nUSE [MATH]\r\nGO\r\n\r\n-- Delete existing procedure\r\nIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_STORE_PRIMES]') AND type in (N'P', N'PC'))\r\nDROP PROCEDURE [dbo].[SP_STORE_PRIMES]\r\nGO\r\n\r\n-- Create the stored procedure from scratch\r\nCREATE PROCEDURE SP_STORE_PRIMES\r\n    @VAR_ALPHA BIGINT,\r\n    @VAR_OMEGA BIGINT\r\nAS\r\nBEGIN\r\n    -- DECLARE VARIABLES\r\n    DECLARE @VAR_CNT1 BIGINT;\r\n    DECLARE @VAR_RET1 INT;\r\n    \r\n    -- SET VARIABLES\r\n    SELECT @VAR_RET1 = 0;\r\n    SELECT @VAR_CNT1 = @VAR_ALPHA;\r\n\r\n    -- CHECK EACH NUMBER FOR PRIMENESS\r\n    WHILE (@VAR_CNT1 <= @VAR_OMEGA)\r\n    BEGIN\r\n        -- ARE WE PRIME?\r\n        EXEC @VAR_RET1 = DBO.SP_IS_PRIME @VAR_CNT1;\r\n        \r\n        -- FOUND A PRIME\r\n        IF (@VAR_RET1 = 1)\r\n          BEGIN\r\n            INSERT INTO [DBO].[TBL_PRIMES] (MY_VALUE, MY_DIVISION) VALUES (@VAR_CNT1, SQRT(@VAR_CNT1));\r\n            PRINT @VAR_CNT1;\r\n          END        \r\n        ELSE        \r\n          BEGIN\r\n            PRINT '...';\r\n          END;\r\n        \r\n        -- INCREMENT COUNTER\r\n        SELECT @VAR_CNT1 = @VAR_CNT1 + 1        \r\n    END;\r\n    \r\nEND\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
The seventh snippet of code creates view<\/a> [VW_ONE_HUNDRED_K_PRIMES] to return all primes in the 100 K range.<\/p>\n
\/*  \r\n\tCreate a view see primes from 100K to 199K\r\n*\/\r\n\r\n-- Which database to use.\r\nUSE [MATH]\r\nGO\r\n\r\n-- Delete existing procedure\r\nIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VW_ONE_HUNDRED_K_PRIMES]') AND type = 'V')\r\nDROP VIEW [dbo].[VW_ONE_HUNDRED_K_PRIMES]\r\nGO\r\n\r\n-- Create the stored procedure from scratch\r\nCREATE VIEW [dbo].[VW_ONE_HUNDRED_K_PRIMES]\r\nAS\r\nSELECT * FROM [dbo].[TBL_PRIMES] WHERE [MY_VALUE] > 100000 AND [MY_VALUE] < 200000\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
The eight snippet of code creates function<\/a> [FN_GET_PRIMES_BY_RANGE] to return a table containing prime numbers from @VAR_START to @VAR_END.<\/p>\n
\/*  \r\n\tCreate a function to return a table of primes from x to y\r\n*\/\r\n\r\n-- Which database to use.\r\nUSE [MATH]\r\nGO\r\n\r\n-- Delete existing procedure\r\nIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FN_GET_PRIMES_BY_RANGE]') AND type = 'IF')\r\nDROP FUNCTION [dbo].[FN_GET_PRIMES_BY_RANGE]\r\nGO\r\n\r\n-- Create the function from scratch\r\nCREATE FUNCTION [dbo].[FN_GET_PRIMES_BY_RANGE] (@VAR_START bigint, @VAR_END bigint)\r\nRETURNS TABLE\r\nAS\r\nRETURN \r\n(\r\n    SELECT [MY_VALUE]\r\n    FROM [dbo].[TBL_PRIMES]\r\n    WHERE [MY_VALUE] > @VAR_START AND [MY_VALUE] < @VAR_START \r\n);\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
The ninth snippet of code executes <\/a>the stored procedure [SP_STORE_PRIMES] to discover prime numbers from zero to six million.<\/p>\n
\/*  \r\n\tCalculate primes between 1 & 6 Million\r\n*\/\r\n\r\nEXEC SP_STORE_PRIMES 1, 6000000\r\n<\/span><\/pre>\n<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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,…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[559,558,51,50,565,560,561,315,317,316,31,52,564,562,80,563,332,12,15,566,18,28,29],"class_list":["post-2765","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-add-file","tag-add-filegroup","tag-alter-database","tag-create-database","tag-create-funtion","tag-create-partition-function","tag-create-partition-scheme","tag-create-procedure","tag-create-trigger","tag-create-view","tag-database-developer","tag-drop-database","tag-drop-function","tag-drop-procedure","tag-drop-trigger","tag-drop-view","tag-execute","tag-free-code","tag-john-f-miner-iii","tag-math-sample-database","tag-prime-numbers","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2765","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2765"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2765\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2765"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2765"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2765"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}