I’ve been to nearly all the SQL Saturday locations in the Midwest at one point or another, but Indy has been one of the very few that’s evaded me. (Minnesota, you’re next!) I’m very happy to be presenting in The Circle City this year at SQL Saturday Indianapolis on August 8 2015! I’m really looking forward to being part of the tremendous group of presenters put together by the organizing committee.

My presentation is all about the wonderful new features in SQL Server 2014 other than In-Memory OLTP, more commonly known as “Hekaton”, which was it’s feature codename. In-Memory OLTP is arguably the most significant feature in 2014 and tends to get the most love, but SQL Server 2014 offers plenty of other awesomeness that far more environments can benefit from. This session gives those other features some much-deserved attention, and shows attendees how they can take advantage of all the new improvements.

If you haven’t already signed up for SQL Saturday Indianapolis, register now! I hope to see you there!

My love of collecting data isn’t limited to work, I’m just as happy doing it at home. From the typical digital keepsakes such as documents and photos to geekier things like detailed utility records and 10 years of fuel consumption info for my car, I’ve got quite the little hoard.

The usefulness of some of the data I save can be debated, but one dataset that’s helped me tremendously over the past few years is a spreadsheet of all my SQL community activity. Any time I speak or volunteer in any capacity, I record the following:

• Name
• Date
• Location
• Total number of attendees/participants (I’ll ask an organizer for a ballpark guess)
• If I volunteered in any way, a brief description of what I helped with

If I presented, I also add:

• Title of presentation
• Number of attendees in the session*
• Link to session description page
• Constructive feedback from comment cards, face-to-face, etc

*This can be tricky. Some people come in after the session starts, and others will leave before it ends. What I do is take a headcount right before I start. If I get comment cards following the session, I’ll take the greater of my initial headcount or the number of cards I receive.

#### Why do this?

First of all, you’ll have actual numbers for how many presentations you’ve given and how many people you’ve reached. If you submit abstracts for PASS Summit, they ask for this information as part of your speaking experience. It’s also my understanding that the Microsoft MVP program wants similar numbers if you are nominated.

Aside from all that, this data helps influence what topics I submit for SQL Saturdays and user groups. If I spoke about indexing in a particular city last year, I’ll be sure to submit other topics this time around. On a more personal level, whenever I’m feeling incompetent or irrelevant, looking back on a list of all the things I’ve done can be an excellent morale booster.

So if you’re the least bit involved in the SQL Server community (or volunteering anywhere else, for that matter), I’d recommend keeping track of it. The effort to create and maintain a spreadsheet like this is minimal, and it will never be any easier than it is right now.

It’s getting to be about that time of year again. Bees are buzzing, Summer’s nearly here (at least in the northern hemisphere), and PASS Summit 2015 session selections will probably be announced soon. I don’t know this for sure; I’m not part of the speaker selection process this year, but based on the dates the notification emails went out the past few years:

• 2014: June 24
• 2013: May 17
• 2012: June 6
• 2011: June 15

I’m guessing it will be in the next few weeks.

As always, questions come up when notifications are sent out. While I can’t even come close to having all the answers, there are a few questions I’ve always wanted to share my thoughts on. In previous years I didn’t feel it was appropriate to add my two cents in because I was part of the process, but this year I’m not. The following thoughts are my own opinion and nothing more.

#### Why didn’t I get picked?

In the past, the “reasons” sent out in speaker selection notification emails were extremely vague. They’ve gotten better recently, but before that you would see such extremely descriptive statements as: “poor quality abstract” and nothing more.  Whatever the explanation, I tend to lump reasons for not getting picked into one of two groups:

Your abstract wasn’t good. There’s no way to sugarcoat the fact that some submissions are lacking in quality. I’ve seen plenty of abstracts that were incomplete when submitted, or full of factual or grammatical errors. I detailed a lot of these issues in my previous post. Even if you have great content, errors like these are hard to survive. Fortunately, the quality of your abstract is something you have complete control over. Proofread. Over and over. And when you get tired of proofreading it, have someone else look at it – maybe 2 or 3 people. PASS even offered confidential abstract evaluation this year, and hopefully they will continue to do so in the future. An abstract, much like a résumé, can never be proofread too many times or by too many people.

Now that I’ve gotten that out of the way, please realize that most abstracts are good. Of all the abstracts I read over the past few years, maybe 10% fell into the category I just mentioned. The vast majority of rejections occur not because an abstract isn’t good, but because someone else’s abstract was better. Maybe they had a little something extra in their wording or explanation that resonated with the abstract review team. Perhaps their session included an additional topic that will make it appeal to a wider audience. Whatever the reason, it’s something that’s out of your hands. I liken it to getting dumped by someone and having them say “it’s not you, it’s me.” You can’t force someone to like your abstract (or you, for that matter.)

This is a tough group to be in; you don’t have any control over making sure your abstract is liked more than someone else’s that you can’t see. Actually – scratch that – you CAN see them. While the call for speakers is open, you can read all the abstracts that have been submitted. You’d probably be doing yourself a favor by checking submissions on similar topics and seeing if there’s any changes you’d like to make to your abstract based on what else is out there. You’ll still have zero control over whether or not the abstract reviewers like it, but at least you can say you tried everything there was to try. I think this is completely fair; the list of submissions is out there for everyone to see, and others are free to read your topics and adjust theirs accordingly as well. Unless you don’t submit your abstract until the last possible second so others can’t see it ahead of time, which seems to be occurring more and more frequently the past few years. It can be argued that many who do this are clearly gaming the system, but it’s their right to do so. They are still abiding by the rules (the deadline), and if they want to sneak submissions in right under the wire, there’s nothing that says they can’t. While I think it’s unfortunate that things have gotten to be like this, it’s still fair in my mind.

#### Why don’t I have comments?

While working as an abstract reviewer in previous years, the instructions were to give as many comments as possible. Comments are a wonderful thing. In theory, they can help everyone improve their submissions for the next time around, except when they can’t. If an abstract has obvious errors, those are easy to comment on. If I think you could have worded something a little better, I’m more than happy to comment on that as well. If I have any constructive criticism at all, you’ll see it in a comment.

But what if I read your abstract and I think it’s perfect? It’s well-written, easy to understand, and the topic is so compelling that I’m looking forward to attending your session. I write all these things down as comments. It turns out all the other abstract reviewers feel the same way and write similar comments. But then that abstract doesn’t end up getting selected, perhaps because someone else’s was better. Now you have a rejection with a side of comments saying how awesome your abstract was, and that doesn’t help you out at all.

From what I’ve heard, some reviewers will leave no comments if they liked an abstract and found nothing wrong with it. But that doesn’t help you improve, which is why we’re submitting sessions to begin with, right? I’ve always tried really hard to find something to comment on in every abstract I review. No abstract is perfect – there’s always room for improvement.

#### How can I improve for next year?

This one’s simple. Don’t give up! Keep trying. Submit for local user groups and SQL Saturdays while you continue to hone your skills. Many speakers try for years before they get an abstract accepted for PASS Summit. I tried unsuccessfully 3 or 4 times before I was accepted. The only way to ensure you’ll never speak at Summit is to give up and stop submitting abstracts.

#### What if I have more questions?

This one’s easy too. Don’t ask me – ask the people who really know what’s going on! It just so happens that PASS is hosting a Town Hall meeting tomorrow, 27 May, at 8:00 PM (UTC) and the topic is the PASS Summit selection process. You can register to attend by clicking here and be sure to bring your questions. I will definitely be there and am looking forward to it. Hope to see you there!

SQL Server’s fixed server roles control incredibly broad permissions across your entire instance. For this reason, membership in these roles should be granted carefully and reviewed often. Fortunately, reviewing role membership is extremely easy, so you can always answer the above question better than Homer.

#### The Easy Way: SSMS GUI

In the SSMS Object Explorer, open the “Security” folder for an instance, and then open “Server Roles”.

You’ll see it displays the 9 fixed server roles. Double-clicking on any role brings up a dialog that shows all its members:

If you hate typing, then this method is for you. Otherwise, you’ll probably like

#### The Easier, Faster Way: T-SQL

In SQL Server, where there’s a will, there’s a way to do something in T-SQL. And it will probably be better/faster/cheaper too. Here’s the query I use to check server role memberships:

```SELECT
rp.name AS Server_Role,
mp.name AS Role_Member,
mp.type_desc AS Member_Type,
FROM sys.server_role_members srm
INNER JOIN sys.server_principals rp ON srm.role_principal_id = rp.principal_id
INNER JOIN sys.server_principals mp ON srm.member_principal_id = mp.principal_id;
```

On my test server here at home, it returns the following results:

You can see the 4 columns it gives us: the name of the server-level role, the name of the login, what type of login it is, and whether or not the login is disabled. The SSMS role member dialog shown above gives no indication if a login is disabled or not, so I find that column especially handy in the query.

It’s important to note that this query returns members of all server-level roles at once. This example shows only the members of the sysadmin role because it’s the only one with members on this instance.

#### What to look for

• First and foremost, do you recognize all the members of all roles, and should they have that level of permissions?
• You’ll most likely see windows groups included in this list as well. Do all members of the group belong there?
• You cannot remove the sa login from the sysadmin role, so I recommend either disabling it or changing its name.

#### Securityadmin: the most dangerous role

Ask anyone familiar with SQL Server what the most dangerous role is, and they’ll probably say “sysadmin.” While sysadmin has the keys to the kingdom in the sense that its members can do absolutely anything on the server, there is something securityadmin has that sysadmin doesn’t: secrecy.

Members of the securityadmin role can manage any login on the server, including membership in server level roles. This means someone with securityadmin rights can:

• do whatever dastardly deeds they want
• and then remove themselves from from sysadmin

If you’re only looking at members of the sysadmin role, you will be none the wiser. When checking server-level roles, remember that securityadmin is every bit as powerful and dangerous as sysadmin. It just might save your bacon!

*It’s always 10PM somewhere

Whether you are working in T-SQL, Oracle, MySQL, C#, or Java, the range of possible values for a signed (positive or negative) 32-bit integer is from $-2^{31}$ or (-2,147,483,648) to $(2^{31})-1$ or (2,147,483,647). The fact that it’s so consistent across so many different platforms (and also against plenty of others I didn’t list) means there has to be more to it than just the preference of some developers somewhere, right? Exactly right.

#### Down to the bits

Any data your computer deals with, be it numbers, text, music or videos, all end up in binary at one point or another. Binary means values are in “base 2″, where each digit represents a power of 2, and the possible values for that digit are 0 or 1. A digit capable of storing only the values 0 and 1 is typically referred to as a bit, which is short for “binary digit”. This is in stark contrast to the decimal or “base 10″ number system commonly used, where each digit represents a power of 10 and the possible values for that digit are 0 to 9. To show how values are calculated in different number systems, here is the value 37 in both binary and decimal:

(click to enlarge)

I realize that’s an extremely brief example, but if you want to learn more about binary numbers, there are tons of great resources online.

But what about negative values? Negative numbers can be represented a bunch of different ways. Sometimes it’s done with the minus sign

$-150$

or with parentheses

$(150)$

but those are all formats, or different ways of expressing a value. A format can be changed without altering the value itself.

How do you really represent that a value is negative? Since binary consists only of bits storing the values 0 and 1, what if the first bit of a binary number served not as a value, but to show whether it was positive or negative? A “1” in the left-most bit would effectively mean the value is negative, and a “0” would mean it is positive. Losing a bit to storing positive or negative would effectively cut the number of values you could store in half (since you are losing a power of two), but it would be awfully handy. There’s a little bit more to this method than that, but it’s known as…

#### Two’s Complement

Two’s Complement is a great way to express signed numbers in binary, for reasons we will see shortly.

Expressing a negative value via two’s complement is a rather simple process. Start with the binary expression of the positive value you want to negate. Next, flip all the bits so all the 0’s become 1’s, and all the 1’s become 0’s. If we were to stop here, this would be the one’s complement of the value. To get the two’s complement, we add 1 to the one’s complement. Here’s an example for the number 19:

```010011  (19, unsigned, with leading 0 added)
101100  (bits flipped)
101101  (add 1. This is -19 in two's complement)```

We now have the two’s complement of 19, which has the value -19.

Why did we have to add one? Let’s say we’re in a five-bit environment and want to find the two’s complement of zero (which would be negative zero). The number zero is represented as 00000 in this case. If we flip each bit, we now have 11111, and by adding one to that we now need a sixth bit so we can arrive at 100000. Since we’re still working in a five-bit environment, we ignore the leftmost bit, bringing us back to 00000, which represents both zero and negative zero. Having a single value for positive and negative zero is a key advantage that two’s complement has over ones’ complement.

An even larger advantage of two’s complement is that addition, subtraction, and multiplication work exactly the same as if all values were positive. You can even use the carry method to do this (like you were probably taught in school, but your kids most likely won’t learn thanks to common core in the US.) As a demonstration, let’s add 24 to the -19 value we just computed. The result should be 5.

(click to enlarge)

And indeed it is! If you’re paying close attention you will notice that I truncated the result to 6 bits and ignored the left-most carry. Restricting the result to the same number of significant bits you are adding is a requirement to arrive at a correct answer.

This is really neat, but it still doesn’t answer our original question about the possible range of values. Let’s look at the values you can create with 3 bits.

Three bits allow 8 or $2^3$ values to be stored, and in the case of unsigned numbers, those values are from 0 to 7.  The two’s complement values of those same bit arrangements gives a range of -4 to 3.

Thinking of it in powers of 2, three bits in two’s complement allows us to store values from $-(2^2)$ to $(2^2)-1$. Both the exponents are 2 instead of 3 because a bit is being used to determine whether the value is positive or negative. If we showed 2 as one less than three, the value range would look like:

$-(2^{(3-1)})$ to $2^{(3-1)}-1$

So for $n$ bits, two’s complement lets you express values ranging from $-(2^{(n-1)})$ to $2^{(n-1)}-1$. This is exactly why for a 32-bit integer, the range is $-(2^{31})$ to $2^{31}-1$.

And there you have it. Two’s complement is the reason why basically any signed data types have the range that they do.