Enabling Transparent Data Encryption on SQL Server 2014

To start with for Transparent Data Encyrption (TDE) we will need to be using Enterprise (or Developer) edition of SQL Server 2014. I am using SQL Server 2014 Enterprise Edition CTP1, which is the early release of SQL Server 2014.

Next we need a database to work with.  I will just use the Sample Database for Common Table Expressions, a simple database for TDE with just a couple tables. After downloading the script and running it, we can see the following in the Object Exporer.

TDE14_1

Encrypting the Database

First lets set up the master key to be used for encryption, then create a certificate for the database we are going to encrypt.


USE master;
GO
CREATE MASTER KEY ENCRYPTION
    BY PASSWORD = 'BeSureToUseABetterPasswordThanThis!!!!!!';
GO
CREATE CERTIFICATE cteDemoCert
  WITH SUBJECT = 'cte_demo Certificate';

The next step is to create the encryption key for the database being encrypted. In this case we are encrypting the cte_demo database.

USE cte_demo;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = TRIPLE_DES_3KEY
ENCRYPTION BY SERVER CERTIFICATE cteDemoCert;

As soon as you run this you will be shown a warning stating that key has not been backed up and that you should back it up.
TDE14_2

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

Now lets backup the certificate as the warning suggests:

USE master;
GO
BACKUP CERTIFICATE cteDemoCert TO FILE = 'c:\Backups\cteDemoCert';

And the final step is to turn on TDE encryption for the database. This may take a while for larger databases.


USE cte_demo;
GO
ALTER DATABASE cte_demo SET ENCRYPTION ON;

And to check to see if everything worked, we can just query the sys.databases table to see if we the cte_demo database is now encrypted.

SELECT NAME, IS_ENCRYPTED
 FROM sys.databases;

TDE14_4From there we can see that the cte_demo database has been encrypted with transparent data encryption. Be sure to make a full database backup at this time and to safely store your backed up encryption key.

The same script will also work on SQL Server 2012.

SQL Server 2014 related articles:

CTE Scope

Day 5 of Common Table Expression Month (June) at SteveStedman.com, today I will cover concepts around the scope of a CTE.

The queries in the video will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

Where is live is not to far from the US/Canadian border. On most days I have no need to visit Canada, but occasionally I may got to Vancouver for a SQLSaturday, or just to go out for some sushi or a comedy club. As long as I have my passport with me, I have the ability to cross into Canada and return to the United States. If I don’t plan ahead and take my passport with me, then visiting Canada is not an option, it is just not allowed. When we talk about scope in any programming language it is similar to  not being able to get to that sushi restaurant inVancouver BC because I don’t have my passport and I can’t cross the border without it. That sushi restaurant would be considered out of scope. In programming languages, including T-SQL, scope is the concept of what you can or can’t access. Some things in T-SQL are accessible from anywhere on the SQL Server (assuming permissions have been granted), for instance a table has what I would call global scope in that it can be referenced from any query accessing the SQL Server (again assuming the correct permissions). If you look at something like a temporary table, it has the scope of the function or query batch that created it, there is also a concept of a global temporary table which has a greater scope.

One of the confusing things around CTE is the scope of a CTE. Given that a CTE is described as being similar to a temporary table, like a temporary named result set, or like a temporary view, all of these imply something that might be around for more than a single query. When using CTE’s, the CTE only exists for the single statement that is using it.

The scope of the CTE is only a single query. That means that the single select, insert or update statement that uses the CTE is the only thing that can access that CTE.

Lets take a look at scope using the following query:


USE [cte_demo];
 GO

SELECT *
FROM Departments
ORDER BY id;

;WITH departmentsCTE(id, department, parent) AS
 ( SELECT id, department, parent
 FROM Departments)
 SELECT * FROM departmentsCTE WHERE id = 1;

SELECT * FROM departmentsCTE WHERE id = 2;

SELECT department
 FROM Departments
 ORDER BY id DESC;

At first glance this batch of SQL may look fine, but when we run it it throws an error.

CTE_Scope1

Even thought we get two result sets.

CTE_Scope2

Lets take a look at the code again, so we can see what is in scope and out of scope. The green rectangle outlines a single query that is using a CTE, and that CTE is available only inside of that single statement.  The red rectangle is attempting to access the CTE from the previous query, and the CTE is not reachable, it is out of scope.

CTE_Scope3

If we take a look again at the original query, what can we do to achieve the results that were intended here, 4 result sets returned, and the 2nd and 3rd queries using the CTE.


USE [cte_demo];
GO

SELECT *
FROM Departments
ORDER BY id;

;WITH departmentsCTE(id, department, parent) AS
( SELECT id, department, parent
FROM Departments)
SELECT * FROM departmentsCTE WHERE id = 1;

SELECT * FROM departmentsCTE WHERE id = 2;

SELECT department
FROM Departments
ORDER BY id DESC;

To make it work we just need to copy the CTE and paste it in to the second query so that it looks like this:


USE [cte_demo];
GO

SELECT *
 FROM Departments
 ORDER BY id;

;WITH departmentsCTE(id, department, parent) AS
( SELECT id, department, parent
 FROM Departments)
SELECT * FROM departmentsCTE WHERE id = 1;

;WITH departmentsCTE(id, department, parent) AS
( SELECT id, department, parent
 FROM Departments)
SELECT * FROM departmentsCTE WHERE id = 2;

SELECT department
 FROM Departments
 ORDER BY id DESC;

Which produces the desired result sets:

CTE_Scope4

Here is how the scope of the CTE’s works. The first CTE query, the CTE is only available int the green rectangle, and the second CTE only has the scope of the blue rectangle.

CTE_Scope6

I hope this helps with the overall understanding of the scope of a common table expression.

Related Links:

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions - Joes 2 Pros® - A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

Common Table Expressions – Terminating the Previous Statement

Day 4 of Common Table Expression Month (June) at SteveStedman.com, today I will cover the topic of terminating the previous T-SQL statement.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

To start with having come from a background years ago of writing C programs, the concept of terminating statements is natural to me. I always feel a bit uncomfortable of a SQL Statement isn’t properly terminated with a semicolon. If I am doing a T-SQL code review for someone else, I usually will make at least one comment about terminating T-SQL statements, unless it has already been done. I know that is not a requirement to terminate every SQL Statement, but it does make it easier to read. However in most cases, other than readability the termination of the previous statement really doesn’t matter. . . In most cases.

For instance, the following two sets of queries work exactly the same with our without the semicolons.

Batch 1:


SELECT * FROM Departments
SELECT * FROM (SELECT id, department, parent FROM Departments) as Dept

Batch 2:


SELECT * FROM Departments;
SELECT * FROM (SELECT id, department, parent FROM Departments) as Dept;

In most cases, when writing multiple T-SQL statements in a batch, the semicolon to terminate the lines really doesn’t matter, but there are a few cases in SQL Server where it does matter, and common table expressions are one of them.

Improper Termination

To start with lets take a look at the sample code from our previous posting titled “Writing Your First Common Table Expression with SQL Server“. If we tried to run this SQL without any semicolons, it would run fine if it was run one line at a time, but if we run it as a batch, it throws an error.

USE cte_demo
GO

SELECT * FROM Departments

WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE

When run produces the following results:

CTE_Termination

Msg 336, Level 15, State 1, Line 5
Incorrect syntax near 'departmentsCTE'. If this is intended to be a 
common table expression, you need to explicitly terminate the previous 
statement with a semi-colon.

Which is a descriptive enough message stating that we need to explicitly terminate the previous statement with a semi-colon.

Proper Termination

When there is another T-SQL Statement in a batch prior to a CTE, that statement must be terminated with a semi-colon like this:

USE cte_demo
GO

SELECT * FROM Departments;

WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE

Note, the only required semi-colon is the one at the end of the SELECT * FROM Departments; query, and we get the desired results.
CTE_Termination2

GO Keyword in SSMS

When working in SQL Server Management Studio, an alternative is to terminate the batch, and the GO keyword is used to break a number of T-SQL statements into different batches, like this:


USE cte_demo;
GO

SELECT * FROM Departments

GO

WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE

GO

CTE_Termination3

Clean Code

Being an advocate for clean code, and clearing specifying your intentions, the way that I would write is with a semi-colon at the end of every T-SQL statement, and an extra semi-colon at the beginning of any CTE just to be safe.


USE cte_demo;
GO

SELECT * FROM Departments;

;WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE;

CTE_Termination4

How you decide to do it is up to your own coding standards, and as long as it is clearly defined and supports clean code it is just fine.

Related Links:

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions - Joes 2 Pros® - A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

DBCC CheckFilegroup

Being day five of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC CHECKFILEGROUP.

Description:

DBCC CHECKFILEGROUP is used for a specific filegroup to check the disk allocation and structural integrity of all tables and indexed views.

DBCC CHECKFILEGROUP Syntax:

dbcc checkfilegroup
(
    [ { 'filegroup_name' | filegroup_id | 0 } ]
    [ , NOINDEX ]
)
    [ WITH
        {
            [ ALL_ERRORMSGS ]
            [ , [ NO_INFOMSGS ] ]
            [ , [ TABLOCK ] ]
            [ , [ ESTIMATEONLY ] ]
        }
    ]
  • filegroup_name – The name of the filegroup to be checked. Ddefault (or if 0 is specified) = the primary filegroup.
  • NOINDEX – Skip intensive checks of nonclustered indexes.
  • ALL_ERRORMSGS – Return all reported errors per object, default = first 200 errors.
  • TABLOCK – Obtain locks instead of using an internal database snapshot.
  • ESTIMATEONLY – Display the estimated amount of tempdb space that would be required.
  • PHYSICAL_ONLY – Limit checking to the integrity of the physical structure of the page, record headers and the physical structure of B-trees.

DBCC CHECKFILEGROUP performs the following commands:

  • DBCC CHECKALLOC of the filegroup.
  • DBCC CHECKTABLE of every table and indexed view in the filegroup.

Example:

Before using DBCC CheckFileGroup, you need to know what filegroups you are using. The following query shows how to list your filegroups for a specific database using T-SQL. The following filegroup check is being done for the northwind database.

USE northwind;
GO

SELECT * FROM  sys.filegroups;

DBCC_CheckFilegroup1

Now the filegroup name, or filegroup id is available it can be used to check the filegroup with DBCC as shown in the following example.

dbcc checkfilegroup ('PRIMARY');

Which shows the following output on success:
DBCC results for ‘Northwind’.
DBCC results for ‘sys.sysrscols’.
There are 1024 rows in 12 pages for object “sys.sysrscols”.
DBCC results for ‘sys.sysrowsets’.
There are 168 rows in 3 pages for object “sys.sysrowsets”.
DBCC results for ‘sys.sysclones’.
There are 0 rows in 0 pages for object “sys.sysclones”.
DBCC results for ‘sys.sysallocunits’.
There are 186 rows in 2 pages for object “sys.sysallocunits”.
DBCC results for ‘sys.sysfiles1′.
There are 2 rows in 1 pages for object “sys.sysfiles1″.
DBCC results for ‘sys.sysseobjvalues’.
.
.
.
.
DBCC results for ‘Customers’.
There are 91 rows in 3 pages for object “Customers”.
DBCC results for ‘Shippers’.
There are 3 rows in 1 pages for object “Shippers”.
DBCC results for ‘Suppliers’.
There are 29 rows in 1 pages for object “Suppliers”.
CHECKFILEGROUP found 0 allocation errors and 0 consistency errors in database ‘Northwind’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If you want to reduce the output and only see errors you can run DBCC CheckFilegroup with the
NO_INFOMSGS option for instance:

dbcc checkfilegroup ('PRIMARY') WITH NO_INFOMSGS ;

Which on success only displays Command(s) completed successfully.
DBCC_CheckFilegroup2

BUG In SQL Server 2008:

There was a bug with DBCC CheckFilegroup in the initial release of SQL Server 2008. This bug was fixed in CU8 or sp2. The bug does not exist in SQL Server 2008 R2 or newer.

Notes:

For more information see TSQL Wiki DBCC checkfilegroup.

DBCC Command month at SteveStedman.com is almost as much fun as a slinky on an escalator.

DBCC CheckAlloc

Being day one of DBBC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC CHECKALLOC.

Description:

DBCC CheckAlloc checks and can repair disk space allocation structures for a database. DBCC CheckAlloc checks the allocation for all pages in the database compared to their internal structures representing those pages.

DBCC CHECKALLOC Syntax:

dbcc checkalloc
(
     [ { 'database_name' | database_id | 0 } ]
     [ , NOINDEX |
     { REPAIR_ALLOW_DATA_LOSS
     | REPAIR_FAST
     | REPAIR_REBUILD
     } ]
)
     [ WITH
         {
             [ ALL_ERRORMSGS ]
           	 [ , [ NO_INFOMSGS ] ]
           	 [ , [ TABLOCK ] ]
             [ , [ ESTIMATEONLY ] ]
         }
     ]

Example:

Running DBCC CheckAlloc without a parameter checks the current database. Alternatively the first parameter can specify a database name or database id.

-- Check the current database.
DBCC CHECKALLOC;

Which gives the following output:

DBCC results for 'cte_demo'.
***************************************************************
Table sys.sysrscols                Object ID 3.
Index ID 1, partition ID 196608, alloc unit ID 196608 (type In-row data). FirstIAM (1:157). Root (1:158). Dpages 12.
Index ID 1, partition ID 196608, alloc unit ID 196608 (type In-row data). 14 pages used in 1 dedicated extents.
Total number of extents is 1.
***************************************************************
Table sys.sysrowsets                Object ID 5.
Index ID 1, partition ID 327680, alloc unit ID 327680 (type In-row data). FirstIAM (1:131). Root (1:17). Dpages 1.
Index ID 1, partition ID 327680, alloc unit ID 327680 (type In-row data). 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sys.sysclones                Object ID 6.
Index ID 1, partition ID 281474977103872, alloc unit ID 281474977103872 (type In-row data). FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1, partition ID 281474977103872, alloc unit ID 281474977103872 (type In-row data). 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sys.sysallocunits                Object ID 7.
Index ID 1, partition ID 458752, alloc unit ID 458752 (type In-row data). FirstIAM (1:21). Root (1:139). Dpages 0.
Index ID 1, partition ID 458752, alloc unit ID 458752 (type In-row data). 4 pages used in 0 dedicated extents.
Index ID 2, partition ID 562949953880064, alloc unit ID 562949953880064 (type In-row data). FirstIAM (1:18). Root (1:16). Dpages 1.
Index ID 2, partition ID 562949953880064, alloc unit ID 562949953880064 (type In-row data). 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
.
.
.
Dozens of rows removed
.
.
.
***************************************************************
Table sys.filetable_updates_2105058535                Object ID 2105058535.
Index ID 1, partition ID 72057594038976512, alloc unit ID 72057594043301888 (type In-row data). FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1, partition ID 72057594038976512, alloc unit ID 72057594043301888 (type In-row data). 0 pages used in 0 dedicated extents.
Total number of extents is 0.
File 1. The number of extents = 35, used pages = 242, and reserved pages = 274.
           File 1 (number of mixed extents = 21, mixed pages = 162).
    Object ID 3, index ID 1, partition ID 196608, alloc unit ID 196608 (type In-row data), data extents 1, pages 14, mixed extent pages 9.
    Object ID 5, index ID 1, partition ID 327680, alloc unit ID 327680 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 7, index ID 1, partition ID 458752, alloc unit ID 458752 (type In-row data), data extents 0, pages 4, mixed extent pages 4.
    Object ID 7, index ID 2, partition ID 562949953880064, alloc unit ID 562949953880064 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 8, index ID 0, partition ID 524288, alloc unit ID 524288 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 20, index ID 1, partition ID 281474978021376, alloc unit ID 281474978021376 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 23, index ID 1, partition ID 281474978217984, alloc unit ID 281474978217984 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 24, index ID 1, partition ID 281474978283520, alloc unit ID 281474978283520 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 27, index ID 1, partition ID 281474978480128, alloc unit ID 281474978480128 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 27, index ID 2, partition ID 562949955190784, alloc unit ID 562949955190784 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 27, index ID 3, partition ID 844424931901440, alloc unit ID 844424931901440 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 281474978611200 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 34, index ID 1, partition ID 281474978938880, alloc unit ID 281474978938880 (type In-row data), data extents 3, pages 31, mixed extent pages 9.
    Object ID 34, index ID 2, partition ID 562949955649536, alloc unit ID 562949955649536 (type In-row data), index extents 3, pages 26, mixed extent pages 9.
    Object ID 34, index ID 3, partition ID 844424932360192, alloc unit ID 844424932360192 (type In-row data), index extents 3, pages 26, mixed extent pages 9.
    Object ID 34, index ID 4, partition ID 1125899909070848, alloc unit ID 1125899909070848 (type In-row data), index extents 0, pages 7, mixed extent pages 7.
    Object ID 41, index ID 1, partition ID 281474979397632, alloc unit ID 281474979397632 (type In-row data), data extents 1, pages 13, mixed extent pages 9.
    Object ID 41, index ID 2, partition ID 562949956108288, alloc unit ID 562949956108288 (type In-row data), index extents 0, pages 7, mixed extent pages 7.
    Object ID 44, index ID 1, partition ID 281474979594240, alloc unit ID 281474979594240 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 44, index ID 2, partition ID 562949956304896, alloc unit ID 562949956304896 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 50, index ID 1, partition ID 281474979987456, alloc unit ID 281474979987456 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 50, index ID 2, partition ID 562949956698112, alloc unit ID 562949956698112 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 50, index ID 3, partition ID 844424933408768, alloc unit ID 844424933408768 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 54, index ID 1, partition ID 281474980249600, alloc unit ID 281474980249600 (type In-row data), data extents 0, pages 4, mixed extent pages 4.
    Object ID 54, index ID 2, partition ID 562949956960256, alloc unit ID 562949956960256 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 55, index ID 1, partition ID 281474980315136, alloc unit ID 281474980315136 (type In-row data), data extents 0, pages 4, mixed extent pages 4.
    Object ID 55, index ID 2, partition ID 562949957025792, alloc unit ID 562949957025792 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 58, index ID 1, partition ID 281474980511744, alloc unit ID 72057594037993472 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 58, index ID 2, partition ID 562949957222400, alloc unit ID 72057594038059008 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data), index extents 0, pages 5, mixed extent pages 5.
    Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 281474980642816 (type In-row data), data extents 2, pages 18, mixed extent pages 9.
    Object ID 64, index ID 1, partition ID 281474980904960, alloc unit ID 281474980904960 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 64, index ID 2, partition ID 562949957615616, alloc unit ID 562949957615616 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 69, index ID 1, partition ID 281474981232640, alloc unit ID 72057594039697408 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 69, index ID 2, partition ID 562949957943296, alloc unit ID 72057594039762944 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 69, index ID 3, partition ID 844424934653952, alloc unit ID 72057594039828480 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 74, index ID 1, partition ID 281474981560320, alloc unit ID 281474981560320 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 74, index ID 2, partition ID 562949958270976, alloc unit ID 562949958270976 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 75, index ID 1, partition ID 281474981625856, alloc unit ID 281474981625856 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 75, index ID 2, partition ID 562949958336512, alloc unit ID 562949958336512 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 90, index ID 1, partition ID 281474982608896, alloc unit ID 72057594038583296 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 90, index ID 2, partition ID 562949959319552, alloc unit ID 72057594038648832 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 91, index ID 1, partition ID 281474982674432, alloc unit ID 72057594038779904 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 91, index ID 2, partition ID 562949959385088, alloc unit ID 72057594038845440 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 92, index ID 1, partition ID 281474982739968, alloc unit ID 72057594038976512 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 93, index ID 1, partition ID 281474982805504, alloc unit ID 72057594039107584 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 93, index ID 2, partition ID 562949959516160, alloc unit ID 72057594039173120 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 97, index ID 1, partition ID 281474983067648, alloc unit ID 72057594038190080 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 97, index ID 2, partition ID 562949959778304, alloc unit ID 72057594038255616 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 99, index ID 0, partition ID 0, alloc unit ID 6488064 (type Unknown), index extents 1, pages 7, mixed extent pages 1.
    Object ID 245575913, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043432960 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
The total number of extents = 35, used pages = 242, and reserved pages = 274 in this database.
       (number of mixed extents = 21, mixed pages = 162) in this database.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'cte_demo'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If errors were encountered on the output, it states to contact your system administrator. But what if you are the system administrator or DBA… At that point your safest option is to restore from backup. The unsafe alternative is to run DBCC CheckAlloc with one of the repair options to attempt to fix your database corruption.

Example with Corruption:

Here we will be using the dbcc_corruption database with a table named Departments. The IAM on the Departments table is corrupt, so DBCC_CheckAlloc produces the following errors:


USE [dbcc_corruption];
DBCC CheckAlloc();

DBCC results for ‘dbcc_corruption’.
Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 99360666784432128 (type

Unknown), page (1:272). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -6.
Msg 8928, Level 16, State 6, Line 2
Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown): Page (1:272) could
not be processed. See other errors for details.
Msg 8906, Level 16, State 1, Line 2
Page (1:272) in database ID 17 is allocated in the SGAM (1:3) and PFS (1:1), but was not

allocated in any IAM. PFS flags ‘IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL’.
CHECKALLOC found 3 allocation errors and 0 consistency errors not associated with any
single object.
***************************************************************
Table sys.sysrscols Object ID 3.
Index ID 1, partition ID 196608, alloc unit ID 196608 (type In-row data). FirstIAM (1:157).
Root (1:158). Dpages 12.
Index ID 1, partition ID 196608, alloc unit ID 196608 (type In-row data). 14 pages used in 1
dedicated extents.
Total number of extents is 1.
***************************************************************

 
Now to go about fixing it.  First I would recommend checking the contents of the table to see if it look correct, if the table contents don’t look right it may require to restore from backup:


SELECT * FROM Departments;

DBCC_CheckAllocCorrupt2

We see 4 rows which tells us that our table data is ok.  It might be a good idea at this point to copy the data from this table into another table. For now we will attempt to repair the table, so we run the DBCC CheckAlloc with REPAIR_REBUILD option.  Keep in mind that this must be run in single user mode.


ALTER DATABASE [dbcc_corruption] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CheckAlloc(dbcc_corruption, REPAIR_REBUILD);
ALTER DATABASE [dbcc_corruption] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

DBCC results for ‘dbcc_corruption’.
Msg 8928, Level 16, State 6, Line 2
Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown): Page (1:272) could

not be processed. See other errors for details.
Repairing this error requires other errors to be corrected first.
Msg 8906, Level 16, State 1, Line 2
Page (1:272) in database ID 17 is allocated in the SGAM (1:3) and PFS (1:1), but was not

allocated in any IAM. PFS flags ‘IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL’.
The repair level on the DBCC statement caused this repair to be bypassed.
Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 99360666784432128 (type

Unknown), page (1:272). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -6.
Repairing this error requires other errors to be corrected first.
CHECKALLOC found 3 allocation errors and 0 consistency errors not associated with any

single object.
***************************************************************

 
Indicating failure, the REPAIR_REBUILD option won’t do the job.  Now for the REPAIR_ALLOW_DATA_LOSS option. You should have copied the contents of this table into another table before trying this.


ALTER DATABASE [dbcc_corruption] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CheckAlloc(dbcc_corruption, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE [dbcc_corruption] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

DBCC results for ‘dbcc_corruption’.
Repair: The page (1:272) has been deallocated from object ID 0, index ID -1,
partition ID 0, alloc unit ID 99360666784432128 (type Unknown).
Msg 8928, Level 16, State 6, Line 2
Object ID 0, index ID -1, partition ID 0, alloc
unit ID 0 (type Unknown): Page (1:272) could not be processed. See other
errors for details.
The error has been repaired.
Msg 8906, Level 16, State 1, Line 2
Page (1:272) in database ID 17 is allocated
in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags
‘IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL’.
The error has been repaired.
Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 99360666784432128
(type Unknown), page (1:272). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -6.
The error has been repaired.
CHECKALLOC found 3 allocation errors and 0 consistency errors not associated with any
single object.
CHECKALLOC fixed 3 allocation errors and 0 consistency errors not associated with any
single object.
***************************************************************

 
This time the error is repaired. But now for the real test, does our table data look alright?

SELECT * FROM Departments;

DBCC_CheckAllocCorrupt3
Which looks good. Now to run DBCC CheckAlloc to see how things look.

USE [dbcc_corruption];
DBCC CheckAlloc();

…. output truncated
The total number of extents = 35, used pages = 244, and reserved pages =
275 in this database. (number of mixed extents = 21, mixed pages = 163)
in this database.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database
‘dbcc_corruption’.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Here we see that the fix worked, and the table is fine. The next step would be to run DBCC CheckDB to see if the rest of the database is fine also. In this case the rest of the database is fine.

Notes:

DBCC CheckAlloc along with DBCC CheckTable for every object in the database are called when DBCC CheckDB is run. Running DBCC CheckAlloc or DBCC CheckTable would be redundant after running DBCC CheckDB.

For more information see TSQL Wiki DBCC checkalloc.

DBCC Command month at SteveStedman.com is almost as much fun as shark week.

Rows and Range, Preceding and Following

SQL Server 2012 adds many new features to Transact SQL (T-SQL).  One of my favorites is the Rows/Range enhancements to the over clause. These enhancements are often times referred to as the windowing functions.

Overview:
ROWS PRECEDING, FOLLOWING, UNBOUNDED, refers to the current row and those before or after based on preceding or following.
RANGE PRECEDING, FOLLOWING, UNBOUNDED, means all values in the current range and those before or after.

An example of a need for rows preceding would be to calculate a 3 year trailing average, which needs to look at the current year and the three previous years in the calculation.

Terminology:
ROWS or RANGE- specifying rows or range.
PRECEDING – get rows before the current one.
FOLLOWING – get rows after the current one.
UNBOUNDED – when used with PRECEDING or FOLLOWING, it returns all before or after.
CURRENT ROW

To start out we need a database to work on, so we will create a tsql2012 database. you can use your own database if you wish.

CREATE DATABASE [tsql2012];
GO
USE [tsql2012];

Next we create a table to use for the over clause enhancments of rows and range preceding and following.

-- Table to be used by Over Clause Rows/Range

CREATE TABLE REVENUE
(
[DepartmentID] int,
[Revenue] int,
[Year] int
);

insert into REVENUE
values (1,10030,1998),(2,20000,1998),(3,40000,1998),
 (1,20000,1999),(2,60000,1999),(3,50000,1999),
 (1,40000,2000),(2,40000,2000),(3,60000,2000),
 (1,30000,2001),(2,30000,2001),(3,70000,2001),
 (1,90000,2002),(2,20000,2002),(3,80000,2002),
 (1,10300,2003),(2,1000,2003), (3,90000,2003),
 (1,10000,2004),(2,10000,2004),(3,10000,2004),
 (1,20000,2005),(2,20000,2005),(3,20000,2005),
 (1,40000,2006),(2,30000,2006),(3,30000,2006),
 (1,70000,2007),(2,40000,2007),(3,40000,2007),
 (1,50000,2008),(2,50000,2008),(3,50000,2008),
 (1,20000,2009),(2,60000,2009),(3,60000,2009),
 (1,30000,2010),(2,70000,2010),(3,70000,2010),
 (1,80000,2011),(2,80000,2011),(3,80000,2011),
 (1,10000,2012),(2,90000,2012),(3,90000,2012);

Take a look at the revenue table to see whats there before we start the demo.


USE [tsql2012];

-- first lets look at the REVENUE table

SELECT *
 FROM Revenue;

rows_range_preceding_following1
Contents of the REVENUE table.

Then a quick review of sum and avg.

-- first simple sum and avg aggregates
SELECT sum(Revenue) as TotalRevenue,
 avg(Revenue) as AverageRevenue,
 count(*) as NumRows
 FROM Revenue;

rows_range_preceding_following2

OVER Clause Pre SQL Server 2012

The OVER clause before SQL Server 2012 is really handy to perform aggregates over a different range than your standard grouping.

--First OVER Clause pre SQL 2012
SELECT *,
 avg(Revenue) OVER (PARTITION by DepartmentID) as AverageDeptRevenue,
 sum(Revenue) OVER (PARTITION by DepartmentID) as TotalDeptRevenue
FROM REVENUE
ORDER BY departmentID, year;

rows_range_preceding_following3

ROWS PRECEDING

Now the new features, ROWS PRECEDING specifies the the aggregate functions in the current partition in the OVER clause will consider the current row, and a specific number of rows before the current row.


--ROWS PRECEDING
-- look at the sum of revenue over a trailing 3 year period
SELECT Year, DepartmentID, Revenue,
 sum(Revenue) OVER (PARTITION by DepartmentID
 ORDER BY [YEAR]
 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as CurrentAndPrev3
FROM REVENUE
ORDER BY departmentID, year;

rows_range_preceding_following4

ROWS FOLLOWING

Now rows following… The ROWS FOLLOWING option specifies a specific number of rows in the current partition to use after the current row.

-- ROWS FOLLOWING
SELECT Year, DepartmentID, Revenue,
 sum(Revenue) OVER (PARTITION by DepartmentID
 ORDER BY [YEAR]
 ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) as CurrentAndNext3
FROM REVENUE
ORDER BY departmentID, year;

rows_range_preceding_following5

Both PRECEDING and FOLLOWING

Using both ROWS PRECEDING and ROWS FOLLOWING allows you to do things like calculate an average including the current year and years both before and after the current year.


--ROWS PRECEDING AND FOLLOWING

SELECT Year, DepartmentID, Revenue,
 sum(Revenue) OVER (PARTITION by DepartmentID
 ORDER BY [YEAR]
 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as BeforeAndAfter
FROM REVENUE
ORDER BY departmentID, year;

rows_range_preceding_following6

UNBOUNDED

UNBOUNDED PRECEDING tells the windowing function and aggregrates to use the current value, and all values in the partition before the current value.

-- ROWS UNBOUNDED PRECEDING
SELECT Year, DepartmentID, Revenue,
 min(Revenue) OVER (PARTITION by DepartmentID
 ORDER BY [YEAR]
 ROWS UNBOUNDED PRECEDING) as MinRevenueToDate
FROM REVENUE
ORDER BY departmentID, year;

rows_range_preceding_following7

When using UNBOUNDED FOLLOWING as follows, this means the use the current row, and consider all rows after the current row in the current partition.

-- ROWS UNBOUNDED FOLLOWING
-- http://stevestedman.com/?p=1485
SELECT Year, DepartmentID, Revenue,
 min(Revenue) OVER (PARTITION by DepartmentID
 ORDER BY [YEAR]
 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as MinRevenueBeyond
FROM REVENUE
ORDER BY departmentID, year;

rows_range_preceding_following8

ROWS vs RANGE

ROWS vs RANGE appears to cause much confusion.

ROWS means the specific row or rows specified, and RANGE refers to those same rows plus any others that have the same matching values.

Here is an example of ROWS and RANGE producing the exact same results because the years and department IDs don’t have any duplicates. In this case the ROWS and RANGE are identical.

-- ROWS vs RANGE UNBOUNDED PRECEDING

SELECT Year, DepartmentID, Revenue,
 sum(Revenue) OVER (PARTITION by DepartmentID
 ORDER BY [YEAR]
 ROWS UNBOUNDED PRECEDING) as RowsCumulative,
 sum(Revenue) OVER (PARTITION by DepartmentID
 ORDER BY [YEAR]
 RANGE UNBOUNDED PRECEDING) as RangeCumulative
FROM REVENUE
WHERE year between 2003 and 2008
ORDER BY departmentID, year;

rows_range_preceding_following9

Now if we consider the same query of ROWS vs RANGE with duplicate values. Here we insert a duplicate set of values for 2005, and the results below show that for the year 2005 there are two rows in the result set, and the RowsCumulative column is different form the RangeCumulative.  The rows counts the specific rows it the aggregate calculation, the range counts the current row, plus the other 2005 rows for that department id.

-- INSERT A DUPLICATE VALUE FOR RANGE UNBOUNDED PRECEEDING

INSERT INTO REVENUE
VALUES (1,10000,2005),(2,20000,2005),(3,30000,2005);

-- same query as above
SELECT Year, DepartmentID, Revenue,
 sum(Revenue) OVER (PARTITION by DepartmentID
 ORDER BY [YEAR]
 ROWS UNBOUNDED PRECEDING) as RowsCumulative,
 sum(Revenue) OVER (PARTITION by DepartmentID
 ORDER BY [YEAR]
 RANGE UNBOUNDED PRECEDING) as RangeCumulative
FROM REVENUE
WHERE year between 2003 and 2008
ORDER BY departmentID, year;

rows_range_preceding_following10

Summary

With all the great new features in SQL Server 2012, the windowing functions, ROWS and RANGE, PRECEDING and FOLLOWING updates to the OVER clause are a great addition.