Integer Ranges in SQL Server 2012

I was looking at the numeric data types in SQL Server 2012. I wanted to learn the ranges of the integer data types, but didn't want to memorize them. Noticing that each larger data type is twice the size of the smaller one was the key:

tinyint	1 byte
smallint	2 bytes
int	4 bytes
bigint	8 bytes

Since a byte is 8 bits, an integer data type can hold 2^(8*bytes).

Let's run the numbers:

A tinyint can hold a value as large as 2⁸. 2⁸ is 256, and a tinyint doesn't use negative numbers,so it can hold values from 0 to 255. 

A smallint is 2 bytes, or 16 bits. 2¹⁶=64K, so a smallint can range from a minimum of -32K to a maximum of 32K-1. The calculation of the exact values is left as an exercise for the reader.

An int is 4 bytes, so we're talking about 2³². I know this one by remembering a 32-bit CPU can address 4GB of RAM, which makes the range not 0-4G, but -2G to 2G-1.

Finally, a bigint is 8 bytes. 2³² is...a really big range. According to Books Online, the minimum value is -9,223,372,036,854,775,807. The bigint tops out at 9,223,372,036,854,775,808.

As always, refer to Books Online for more detail.

Exam 70-432: Week 1

Week 1 is done. Here's what I accomplished:

• Prepped for the class by installing Windows Server 2008 R2 in a VM.
• Installed the practice tests from Mike Hotek's book.
• Started reading.
• Decided on my Class Project. I'm going to create a database using Sean Lahman's Baseball Database.
• Answered a Question of the Day from SQLServerCentral.com

The hardest part is going to be posting something substantial in an online forum each week. I don't have enough technical knowledge to contribute anything meaningful -- at least not yet.

On to Week 2.

Exam 70-432: Week 1 - QotD

This week's Question of the Day from SQLServerCentral.com is about ghost process cleanup.

I knew nothing about ghost records or the ghost process (in fact, I had never heard of them), so Bing to the rescue.

I found an article where Paul Randal that explains the ghost record cleanup process. Using the information from the article, I came up with the correct answer of '2'.

Exam 70-432: If At First You Don't Succeed...

You may know that I designed a self-directed class for passing exam 70-432. Well so far, it's been a flop. I didn't allow myself enough time to cover each topic, and I spent way too much time producing output from the lab exercises.

But I'm not giving up. To quote Curly: "If at first you don't succeed, keep on sucking till you do succeed."¹

So, here's 70-432, version 3.0. This time, we'll focus on explaining concepts, instead of turning in homework. Lab exercises and practice tests are still part of the curriculum, but they're no longer the main emphasis.

Take a look at the syllabus, and let me know what you think. Check back for updates, and maybe take the class yourself.

---

1. http://quotationsbook.com/quote/29987/

Exam 70-432: Week 3 - QotD

You may have noticed that I like to finish the Question of the Day part of each assignment early, so it's not hanging over my head all week. This week's submission is the QotD from June 1, 2012, and can be found at SQLServerCentral.com:

SQL Server troubleshooting information can be found in Windows Event Logs. Information about SQL Server information can be viewed in all the logs below except which one?

• Setup Event Logs
• System Event log
• Application Event logs
• Security Event log

I fired up Books24x7 and looked in Microsoft SQL Server 2008: Administrator's Pocket Consultant, Second Edition. Chapter 14 has a section Working With the Event Logs that had this bullet point:

Windows has additional logs that can be helpful when tracking issues. If you are tracking security issues, start with the SQL Server event logs and also examine the Windows security log. If you are having trouble finding the source of a problem that is preventing proper operation of SQL Server, start with the SQL Server logs and also examine the Windows application and system logs. [emphasis mine]

So, by process of elimination, the correct answer is: the Setup Event Logs.

---

Reference:  Stanek, William R. (© 2009). Microsoft SQL Server 2008: administrator's pocket consultant, second edition. [Books24x7 version] Available from http://common.books24x7.com.libproxy.mit.edu/toc.aspx?bookid=34174.

Exam 70-432: Week 2 - Some Lab Exercises

Whenever I took a class in grad school, I always had one week where nothing went right, and I wound up not doing a good job.

This was my one and only bad week for this class. I was a bit under the weather, so I didn't finish the assignments. In hindsight, I should have focused on Chapter 9 in the Lab Manual (Backing Up and Restoring a Database) instead of starting with Chapter 8 (Designing Policy Based Management). Oh, well, here's what I do have:

Chapter 8: Designing Policy Based Management

Lesson 1: Designing Policies

Practice 1: Create a Condition

Steps 1-2:





Step 3:
  

Step 4:

 

Step 5:

 
 
 Step 6:

 

 Step 7:



Practice 2: Create a Condition For a Target Set

Step 1:



Step 2:

Practice 3: Create a Policy

Step 1:






 


Step 2:




Step 3:



Step 4:



Practice 4: Create a Policy Category

Step 1:










Chapter 9: Backing Up and Restoring a Database

Lesson 1: Backing Up Databases

Practice 1: Create a Compressed, Mirrored, Full Backup

Step 1:

 



Practice 2: Create a Transaction Log Backup

Step 1:






Step 2:



Practice 3: Create a Differential Backup

Steps 1-2:




I'm definitely going to come back and re-visit backups and restores.