Originally published at Ramblings from the Flip Side (Site under construction). You can comment here or there.
Today’s SQL Saturdays post discusses a cute little trend we have going on SQLServerCentral.com, SQL Spackle articles.
SQL Spackle articles are short and simple articles meant to spackle, or patch, existing knowledge gaps. While Microsoft’s Books Online covers a lot of information, it also leaves out information that DBAs and Developers have to discover on their own. Plenty of articles have been written by very knowledgeable people on the big stuff such as backups, restores, major code How-Tos. But several members of the SSC community noticed that the small stuff, which is needed just as badly, is rarely covered.
Hence the birth of the SQL Spackle article. At the head of each little article is the following disclaimer:
“SQL Spackle” is a collection of short articles written based on multiple requests for similar code. These short articles are NOT meant to be complete solutions. Rather, they are meant to “fill in the cracks”.
SQL Spackle fulfills a need rarely recognized by the technical community, those useful little code bits that everyone can share, use, and save in their own personal libraries to port from job to job. The intent is to reach everyone regardless of their experience and knowledge level. It seems to be working out fairly well, too.
So if you or someone you know is hitting a wall or looking for that little bit of extra info not included in the usual sources, check out SSC’s SQL Spackle library. There’s some interesting stuff, including one article of mine that just got published two days ago, Schema-Owned Tables and Generated DROP Scripts. (Because I keep running into this problem myself, and remembering the solution is easier for me if I actually write about and share it.)
Do you have ideas for more SQL Spackle articles? SSC pays for both these and regular SQL Server articles. Check out their Write For Us page. Join the fun and spackle a few holes in the process. You’ll be glad you did!
Originally published at Ramblings from the Flip Side (Site under construction). You can comment here or there.
Welcome back to SQL Saturdays. It may seem odd that I’m devoting a SQL Server post to a science fiction / fantasy (sff) controversy, but I find my writing and technology careers often converge and overlap. Especially in an era where all the rules of inter-gender conduct are changing. Women aren’t just accomplished authors and editors, they are database administrators, messaging admin, architecture analysts. When the Old Mens Club of sff refer to a woman first by her pleasing appearance and barely touch upon her many professional accomplishments, it reflects beliefs that exist across the spectrum of career fields.
The New York Times proved this when its obituary for scientist Yvonne Brill mentioned her cooking skills in a long sentence before briefly addressing her many achievements in rocket science. And then there’s an interesting study about women’s and men’s math skills (thanks to fellow RPG writer David Hill Jr. for the link) where women did just as well as men on the tests when they were allowed to change their name.
People still believe women are bad at maths and sciences, that it’s a fluke when the fairer sex manage to be good at technology. Some of those people are themselves women, perpetuating the stereotype by their silent acceptance of their limitations. I had a female boss who didn’t believe I could be a good server administrator. Ten years later, I am working as a SQL Server DBA.
While I don’t agree we should be burning bras, chaining ourselves to buildings, or screaming at everyone who dares say “women can’t do that,” I do believe that this is the time where we need to stand up for ourselves. Yes, it sucks that we have to be ten times better at something than a man before we get the jobs or the raises or the commendations. Yes, it sucks that we are paid less (mostly) for doing the same job with the same job description as our better-paid male counterparts. But the balance is shifting. Companies are recognizing that women have superior communications skills and are more likely to seek consensus and work on team building, making for a more productive, more peaceful workplace environment.
And as the CIA learned, women are just that much better at absorbing details and less likely to jump to conclusions about the big picture without those details.
Holding women to the Barbie stereotype or the “good looker, good cooker” model doesn’t do any business or government agency any good. Then again, playing the blame game doesn’t either. We do need to stand up, make our voices heard, let people know that we are confident, capable, and just as ambitious as the boys are. And if calm dialogue doesn’t work, then yes, we do need to raise our voices. We need to stand in solidarity with our sisters in other industries and let them know we have their back. Because then they will also have ours.
The speculative fiction genres have always influenced our hearts, minds, and technological progress. Cell phones are based on Star Trek communicators. Holographic tech, robots, even 3-D printers were all inspired by science fiction. Not just that, but Star Trek also gave us Uhura, the first female POC television series regular who was a competent professional in her chosen field. Okay, yes, she was basically the receptionist, but she held her own in the series and in the followup movies. And she was better at her job than any of the men!
If we want to encourage young women and girls to study their maths and sciences, if we want more women in technology, the first place to start is getting more women in our fiction. Women as authors and editors, who are referenced first by their professional accomplishments, with looks and appearance as an after-thought (if even that). Women as well-rounded characters, strong in their own right but still realistic. Not the weepy willow romance heroine who falls into the arms of men. Not the erotica lustful babes just waiting to have hot sex every chapter. I’m not even referring to the sexless women-acting-like-men characters. But real honest-to-goodness women who are protagonists first and female simply by virtue of being female.
Gender equity in the publishing field will ripple outwards. It will teach our peers, our children, and even some of our elders, what we expect in our every day lives. It can be done. We can break the glass ceiling. But weapons of noise aren’t working as well as we want, so I propose changing over to weapons of subtly. Ambush the masses with good fiction depicting women professionals in positions of power, from an industry where real life women do hold positions of fame and responsibility, and soon the masses will accept us into other high-level or tech jobs without realizing the Old Boys’ Network has been corrupted from within.
This is why the SFWA debate matters to women in technology. And this is why women in technology should support the change.
Do you agree? Disagree? Tell me why.
Originally published at Ramblings from the Flip Side (Site under construction). You can comment here or there.
Designing a Data Recovery Solution[/caption]
Today’s SQL Saturdays post is aimed at the teachers, librarians, and tutors of our community. A few weeks ago, my employer held a “bring your kids to work” day themed “Work in Progress.” The day was broken up into intervals that included exercise and snack breaks, even a walk through our beautiful little mini-park. The thrust of the exercise, though, was to walk the 8-12 year old children through mock interviews and mock-job training. Volunteers were picked to read through the children’s resumes and interview them. A co-worker and I were picked to do on-the-job training for a group of 5 kids. The goal, I was told, would be to explain my day job and teach the kids how to do it.
I admit to being a little panicked. I’ve rarely been able to explain database administration to an adult in a way that makes sense to them. Trying to teach kids in 30 minutes how to do what I do seemed impossible. Then fellow SSCer Stefan Krzywicki suggested I do a bit of flowcharting, because lots of kids enjoy drawing. With that idea, I was off and running. After a quick kid-friendly explanation of database administration, we “programmed” burgers, pizza, and even one girl’s gymnastics class routine.
Several large sheets of paper
Lots of colored markers or pencils
Dollup of Patience
There are 5 basic shapes I used : The terminator oval, the process rectangle, the decision diamond, the preparation hexagon, and the arrow-shaped precedence constraint. (All documents are attached to this post). Rather than confuse the kids with the more complicated terminology, I called them “Start or End,” “Prep Work,” “Job,” “What’s Next,” and “The Question.”I initially started off with a PBJ (peanut butter and jelly sandwich), but the kids didn’t like PBJs. When I asked them for suggestions everyone agreed they liked burgers. So I used that to illustrate the flowchart process. We start in the kitchen. Our prep work consists of laying out the ingredients and heating the pan. Burger assembly is broken down into multiple jobs. Questions are asked about what we want on the burger, with each question leading to a yes or no decision. If the answer is yes, we do the task, then we move on to the next question. If the answer is no, we move on to the next question. We end our process by eating the burger. Here’s what the final workflow looks like.
I could have made this a lot more complicated with the number of condiments and the additional process of adding seasonings and asking how the burger should be cooked, but that’s not the point of this exercise. The point is getting them to think of something they do every day as a program that can be broken down into multiple steps and multiple decision-making points. After the burger example, the kids got their own paper and choice of markers so they could program pizzas and ice cream sundaes (I gave them the option of programming whatever food they wanted and this is what they chose). I stood over their shoulders, answered their questions, and made suggestions when they hesitated. What kind of toppings? Do you want the cheese on the pizza first or the pepperoni before the cheese? Do you have to make the ice cream first or did you get it from the store?
It’s important to let the kids make the choices about what they want to do with their programming. Telling them “it doesn’t work that way” or “you’re doing it in the wrong order” doesn’t help them. Asking them a question about the order, though, lets them think about the ramifications of their choices. “Do you want to add the ice cream to the bowl before the chocolate syrup?” is a better way of guiding them, and if they want syrup under the ice cream, let them have it in that order. I like syrup on all sides of my ice cream, so I’m not one to judge. @=)
Reminder: Kids will not always stick to the actual flowchart shapes. That is absolutely OK. The shapes aren’t important. What is important is teaching kids how to break things down into individual logical units and learn how to order those units. So encourage the creativity. And when they’re finished, let them decorate their flowchart with pictures. My group drew pictures of their completed pizzas and sundaes and one kid even drew himself eating said pizza.
After we finished that project, one girl was so excited that she wanted to do it again, but didn’t know what to draw. When I asked her what activities she enjoyed the most, she said gymnastics class. So I walked her through her routine. We started at home, the prep work was gathering her gymnastics supplies, then she drew her tasks (getting dressed, getting in the car, walking into class, warming up). We had a decision point where she decide which of three gymnastics practices she would do next (tumbling, flips, or ribbon work), and another decision point that depending on whether or not she was on a team and had to do a team practice. This chart was more complicated than the food chart because it had multiple branches, each with their own terminators. Every terminator was “home,” but her new chart looked more like a real programming workflow because home didn’t happen at the same point every time.
After we were done, the kids signed and rolled up their flowcharts to take them home. They had a great time programming their favorite things. Who knows, this experience may have influence a new generation of database administrators and programmers.
Oh, and as far as the kid-friendly explanation of database administration goes, here’s how I spun it. “You know all those treasures, coins, and equipment you can earn in a video game? Each game has a limited number. A database stores that information. My job is keeping track of what is in your inventory and how many of each item is still available in the game.”
To which the response was “What games do you work on?” ah-hem. Apparently I explained my job a little too well. They were very excited to meet a “game designer.” For the record, though, I have actually programmed games. Text-based MUSHes, though. Not video games. It’s the same difference, right?
As I prepare to speak at SQL Saturday 197 (Omaha Nebraska), I’m listening to Matthew Brimer speak about Database Security & Shrinking Your Attack Surface. It’s an interesting lecture. But since I’m speaking about data recovery plans, I thought I’d post about something along those lines: Recovery Models.
SQL Server has 3 recovery models that are an essential part of any recovery and backup plan: Full, Bulk-Logged, and Simple.
The Full recovery model tracks pretty much every transaction (committed and uncommitted) in the database. If you have Full recovery set on your database, then your Transaction Log needs to be backed up or it will balloon in size as it tracks all inserts, deletes, and updates (which is both an insert and a delete).
The Bulk-Logged recovery model doesn’t track every transaction like the Full model does. Bulk-Logged marks and records the modified database extents (or pages–index or data, depending on the circumstances). This is called Minimal Logging. There is, however, a caveat. Just because the database is set Bulk-Logged, doesn’t mean the whole database is logging the extents / pages. Minimal Logging has prerequisites and if those prereqs aren’t met, then the certain objects are still fully logged.
A table used in Replication cannot be bulk-logged. A table that isn’t table locked (TABLOCK) cannot be bulk-logged. Table “status” also changes how bulk-logging is done. Whether a table is indexed, non-indexed, empty, or loaded can affect what is logged as part of this recovery model. More information can be found by clicking the SQL Server Books Online link. Again, backing up the Transaction Log is essential for preventing run-away log growth.
The last recovery model, Simple, is … well, simple.
The Transaction Log isn’t used… Ah, and here we run into a nice little bit of SQL Server myth. Transaction Log backups cannot be run on databases using the Simple Recovery Model. This has led to the belief that SQL Server doesn’t use the Transaction Log for these databases, but that belief is mistaken. Simple recovery does use the Transaction Log, and the Simple recovery databases do use Minimal Logging, just like Bulk-Logged. The major difference between the two is that Simple recovery auto-truncates the Transaction Log, eliminating the need for Transaction Log backups, and that it is only as good as the last data backup. Whereas Bulk-Logged databases can be recovered to the last Transaction Log backup.
Microsoft has a good overview of the Recovery Models online. Understanding these models are an important part of creating a data recovery plan for any server. With these, and a few other business-related tidbits, any DBA can create the best recovery plan (and backup plan) for their business.
If you’re in the Omaha area, stop on by SQL Saturday to hear my presentation. And if you missed it, check out the attachment for my power point presentation.Designing a Data Recovery Solution
Depending on what type of DBA you are, you might find yourself using different database tools, but the most important tool (in my opinion) is SSMS – also known as SQL Server Management Studio.
SSMS is part of the SQL Server client toolset. SQL Server does work without the client tools being installed and the client tools can be installed on a computer that doesn’t have SQL Server installed on it. The client tools are typically installed as part of the SQL Server installation. If you don’t see them, don’t panic. Just grab the DVD (or .msi install file) and alter your SQL Server install to include them.
NOTE: Before installing the client tools on multiple computers, make sure you have the proper licensing. Microsoft is really picky about these things and you don’t want to get fined for overusing the software.
Once SSMS is installed, it can be found under the Start -> Programs -> Microsoft SQL Server menu as “SQL Server Management Studio.” The icon is a little wrench-crossing-a-hammer with a little yellow cylinder in the upper left hand corner (a database symbol). When you open it up, the default settings will ask you to connect to a database. The connect box looks like this:
SSMS is the GUI (Graphical User Interface) most DBAs use for managing all the working parts of SQL Server. It does not manage everything, but it covers the essentials: Instances, Databases, Security, Server Objects, Resources, object properties, SQL Agent objects, Database Mail, Jobs, Integration Services (management only), Analysis Services (management only), and a lot more. SSMS has an Object Explorer which can be used to monitor and check all of the above, a summary window (also called the Object Explorer Details) which focuses on all items within a specific instance, and query windows (as many as you can deal with) which can be used to write and run T-SQL and some CLR code.
In my experience, SSMS is THE power tool of SQL Server database administration. I’m a “jack of all trades” DBA and SSMS is what I use 90% of the time. I always have at least 2 copies open on my desktop, one in each monitor. Knowing how to get around in SSMS is essential for any type of DBA, and learning it is the first step in becoming a DBA. It’s also a lot easier than writing T-SQL in BIDS or Visual Studio (in my opinion).
If you’re interested in more information, Microsoft has a SSMS tutorial here. Stay tuned to SQL Saturdays for more information on the SQL Server database tools.
Welcome back to SQL Saturday’s. Today I wanted to discuss a T-SQL trick I frequently use to search for the existence of records.
SQL Server has a fun function calls EXISTS() that I actually don’t use that often. Why? Because most of my existence checking involves multiple joined tables and verifying if records in one exist in another. Whether I’m doing an INSERT, DELETE, or just a simple SELECT, I need to compare these tables and verify the existence (or non-existence) of a record. Using the AdventureWorks database (2008 R2 version), here’s how I do it.
In this scenario, I have received an audit request. The auditing team is reviewing all our person records, but their files only show 20 records. They know this isn’t right because AdventureWorks has over 200 employees, not to mention the customer records the database should contain. So, as the DBA, I am tasked with finding all the records in our Person table that do not exist in the audit team’s file.
I created the audit team file with the below code, but you can import it into your database by using the attached CSV file. Audit Team Person File
SELECT TOP 20 BusinessEntityID, PersonType, NameStyle, Title, FirstName, MiddleName, LastName, Suffix
WHERE PersonType = ‘IN’
ORDER BY Title;
–Get the first 20 people who don’t have a title
Before I start coding, I create a dirty backup in the form of a database snapshot to protect the integrity of my AdventureWorks database. That way, if I accidentally update or delete records, I can restore back to the original db.
The long, difficult way of finding the missing audit team records would be to compare BusinessEntityIDs or names. So I could do a SELECT on the temp table, copy over the IDs (if they exist in the file) or the names and then just do a WHERE clause that says “WHERE FirstName NOT IN (
) AND LastName NOT IN (
). But not only is that tedious, it’s dangerous too. Records that I should receive back will disappear.
The audit team records I pulled out contain the first names of Mandar, Alma, Warren, Kelvin, Crystal, Francis, and Isabella with Laura and Crystal repeated, so I deleted the repeats. The last names are Samant, Son, Tang, Pal, Xie, Cai, Zeng, Zhang, Wang, Chen, Li, Liu, Huang, Wu, Hernandez, Lin, Zhou, Ye, Zhao, and Cox. BusinessEntityID 10261 has a name of Isabella Adams. Adams is not one of the last names in the above list, so there’s a reasonable expectation that I should see it in the comparison results. (I always locate one or two records that I can use to verify all my queries).
Here’s my query, ordered by LastName and FirstName to make locating Isabella Adams easier:
WHERE FirstName NOT IN (‘Mandar’,'Alma’,'Warren’,'Kelvin’,'Crys
AND LastName NOT IN (‘Samant’,'Son’,'Tang’,'Pal’,'Xie’,'Cai’
ORDER BY LastName, FirstName;
And the results are:
She’s nowhere in my results list. In fact, if I add the line “AND BusinessEntityID = 10261″ to the WHERE clause, I get a zero record resultset, which means my existence query is poorly written.
My life is made a lot easier since the audit team file contains my table’s primary key, BusinessEntityID. So I upload the file to my SQL Server database. Now that I have that information as a table, I have one of two solutions to find the true non-matching records. I can do a subquery or an OUTER JOIN. The performance of one over the other really depends on individual database design. My personal preference is for JOINs over subqueries, all other things being equal. But here’s how I do both.
The SubQuery Version uses a subquery in the WHERE clause that pulls only the BusinessEntityID from #MyTemp and compares it to the BusinessEntityID in Person.Person. In effect, I am saying “pull the records from my Person table where that key doesn’t exist in the temp table.”
WHERE BusinessEntityID NOT IN
(SELECT BusinessEntityID FROM #MyTemp)
ORDER BY LastName, FirstName;
The OUTER JOIN version does basically the same thing as the subquery, but I feel it is a lot cleaner. With this code, I am saying, “Join these two tables on the BusinessEntityID column, but only give me the results from the Person table that don’t match anything in the temp table.
FROM Person.Person p1
LEFT OUTER JOIN #MyTemp p2
ON p1.BusinessEntityID = p2.BusinessEntityID
WHERE p2.BusinessEntityID IS NULL
ORDER BY p1.LastName, p1.FirstName;
In both instances, I get back Isabella Adams, which means that these queries are more accurate than doing the search the hard way. I love OUTER JOINs and subqueries for these types of checks because it makes my life so much easier. They’re faster to type too.
What type of code do you use for existence checks?
Here’s an argument for taking math classes, whether you are male or female. It really will help you with your future jobs. And yes, I laughed at that one too when I was a kid. Now I know better.
The intro to television series Numb3rs includes the lines “We all use math every day… We use math to [...] reveal patters and predict behavior.” It’s actually a good point. Logic, math, and numbers are essential to doing many things in life, especially in regards to being a database administrator. You don’t have to be a math expert to be a DBA, but it helps if you understand arithmetic functions and precedence. The ability to work through story problems is very similar to working through work issues. And understanding granularity is essential.
Let’s start with precedence. 2 + 3 = 5. And 2 + 3 – 4 will always equal 1. Precedence in math and SQL is identified by the use of parenthesis. Everything is operated on from the inside heading to the outside. So we have two possible formulas: (2 + 3) – 4 or 2 + (3 – 4).
Here’s the difference
We start with (2 + 3) – 4 and work the parens first. The formula becomes (5) – 4. Then 5 – 4 = 1.
We start with 2 + (3 – 4) and work the parens first. The formula becomes 2 + (-1) where the addition gets changed to a minus due to the presence of a negative number. So now we have 2 – 1 (which is the same as 2 + -1) = 1.
Precedence becomes really important in cases of the division and multiplication operands: * or /. So when we have a formula like 2 + 3 * 4, our two possibilities are either (2 + 3) * 4 or 2 + (3 * 4). Now we have 2 possible answers, that of 20 or that of 14.
(2 + 3) * 4 becomes (5) * 4 becomes 20.
2 + (3 * 4) becomes 2 + (12) becomes 14.
But precedence in SQL Server is about more than mathematical formulas in reports. It makes a difference in how one treats JOIN or WHERE clauses. If I’m looking for order invoices for a customer called XYZ Soup Company, I’m might start looking in the customer table or the order table or the invoice table. If I can do an INNER JOIN on all of these tables (each table has a key that leads logically to the next table) it doesn’t matter where I start. But if I need to find order invoices for this customer that do not match up to the orders actually placed by the customer, I might start at the customer table and join up to the invoice table before doing an OUTER JOIN to the orders table in order to check for a state of non-existence.
Such a query sounds complicated and, depending on the database’s design, it might be. This is where understanding story problems come in handy, and maybe some geometry skills as well. The ability to picture the tables and their connections in your head, or to draw it out on paper, is important to resolving the problems DBAs experience in the everyday workplace. It’s not just about creating financial reports or adding quarterly totals. DBAs also use math to analyze trends, budget hard disk space, troubleshoot performance issues, and verify data flows. Again, though, you don’t need to be a math major to do this job. The ability to internalize math principals in the job are key.
Let me explain. Math is intuitive (and yes, I’ve heard the arguments for why it’s not, but I disagree). Personally, I can’t do math if I concentrate on each and every step of the problem. But I can generally get from the problem to the correct answer if I don’t have to show my work. Math is so “internalized” in what I do, that until I actually follow a math formula to discover a problem, I don’t think about my job as a “math job.” It’s just a job that occasionally requires me to think in math terms. So the idea that I need math and mathematical concepts to do my job amuses me. I only took advanced math courses in high school because I heard a rumor that if I took calculus in 12th grade I wouldn’t need to take math in college. It was just an old wives’ tale, I found out later. I’m glad, however, that I fell for it. As average a math student as I was, these courses have helped me greatly with every job I’ve ever held and are crucial to the job I have now.
So don’t neglect your math education. You’d be amazed at what it can do for you, even when you aren’t thinking about it.