T-SQL Tuesday #111: Why I Do Things with Databases

Somehow these monthly posts continue motivating me to write abstract thoughts which allow for a modicum of reflection.

This month’s topic comes from Andy Leonard (b|t), who I’ve had the pleasure of meeting a few times whether in NC or VA. Why do we do the stuff we do? Why have we focused so much on our career development? Well, it may seem pretty standard, but there are multiple “why” questions that can be asked in a string.

Why did becoming a data professional become appealing?

In college, when I had a class introducing me to database concepts, I had the chance to think about more advanced features of Access from my high school days and how to query this data. The consumption of information and its interpretation goes a long way. There is a phrase I heard at one time: In God we trust; all others must bring data.

I do this because I showed an aptitude for this ability, and knew there could be a challenge learning how to move data from one place to another. I ended up working with Microsoft SQL Server by default at my places of employment, which obviously led to me building a resume on how to swing the hammer, but I then was able to see how it was more than just “SQL development” but actually analyzing and managing the data to solve problems.

The biggest aspect today which keeps me loving what I do is the evolution of analysis and automation to get this abundance of data managed more efficiently. With predictive analytics taking having taken off, I really get to see how consumers of the data can make informed decisions based on facts. I get to see at the end how the use of data can contribute to society.

Okay, why is this a good way to contribute to society?

For the majority of the last five years, the companies I have worked for are all in the healthcare industry. It’s an industry where the concern is the betterment of other humans’ lives. That alone is reason to like where I ended up when interviewing for different jobs, as I know the companies I am part of are about reaching a person in need.

On an individual level, I provide for the household, which is of utmost importance. However, treating this as a legit career is more motivation to really stretch the difference being made to allow for the level of security to stay in the industry. It doesn’t feel like “living to work” either, as taking lessons back makes the “job” aspect easier to manage.

Then why involve yourself in a professional association like PASS?

Naturally, this also comes back to this question and why I’m writing this post. Well, I first joined looking to get better at this job. I didn’t know much then, and there’s still more I don’t know versus what I do know with both changes in the platform and my struggles to apply lessons taking a while.

So why did you increase your involvement, from volunteering and periodic blog entries to throwing your hat in the tech speaker circuit?

The reason is two-fold. The more selfish reason: it helps me raise my profile on my career track while motivating me to learn more. The less selfish reason: because I’m totally an expert in some areas who can talk down to others on how to do stuff better addressing how I have solved a problem will help someone looking for a solution. I soaked up knowledge for a while, but then gave my first talks on a professional level in late 2016-early 2017. I gained enough confidence to slowly become an okay public speaker who could then teach the next set of beginners a few things. As for the facilitation and assistance at events, volunteer work is a way to keep this outlet going to keep it alive. Another great thing about having a professional community is that it forces me to network with others. As a person on the spectrum, networking is a weaker suit but the ability to meet people who have all kinds of interests to bond over is key.

Autism? Why do you involve yourself there?

The spectrum is part of why I showed an aptitude for this subject area, with my compulsive abilities to sort. To get off data for a moment (somewhat), I volunteer with the Autism Society of North Carolina as someone hoping others sufficiently have their living standards improved. The chance to encourage kids on the spectrum to do programming, for instance, can tap into abilities that are often overlooked due to the social quotient.

With all that being said, WHAT is the message?

(What? This is asking why! I’ll let it go.)

Do what you love, and love what you do, and leave a positive impact.

That was 2018, So Here are Goals for 2019

Usually we here some degree of “the year was fun” or “screw this year, and next will be better.” On the surface, the engagement and the house made for a pretty outstanding year. Also, I got a volunteer promotion in the late fall as the Vice Chair of the ASNC Human Rights Committee, adding a little more responsibility on site visits and ensuring plan reviews are completed by the right members. However, this also took enough time that a lot of intended professional and running goals fell by the wayside.

Last time I wrote a blog for a T-SQL Tuesday concerning 2018 goals, it was a public goal accountability type of post in terms of learning. So what was accomplished (struck from the record)?

  • MBA Program and ITIL: finally graduated in December, and got a certification. Both check.
  • DBA fundamentals focus: well, this is subjective, but I think I reached this goal through some simple blog learning and shadowing some DBAs. I think I gained a better understanding on keeping backups and even temporal tables. I’ll call it a win for now.
  • Mid-level Python and mid-level R development: my focus was more on architecture, so this got scrapped.
  • Two new talks: I had one, though it will continue to mature. Got to discuss normalization and took it on the road (yet never published the blog, which I was terrible at this year). However, I didn’t devote time to consider a second to add to my rotation of…three. Still figuring out the presenting benchmarks.
  • Set up a true home lab and my own database: never got the data I wanted in full, and didn’t try. Or maybe I was afraid of all the XML files I created without trying JSON instead. Big miss anyway.
  • Get reacquainted with MySQL: I’ll call this one a win because of a server I dealt with that used MySQL. Very generalized, but I got to check the nuances between PL/SQL and T-SQL again.

So last year was unsurprisingly too ambitious on the professional front. However, it’s time to try 2019 again, even if I did not publish this post until February.

To start, my first goal is the most important: do well in year one as a first time husband. My start date is April 20. I hear the manager was also expecting a lifelong commitment to the craft. With that in mind, this means I want to be cautious as I continue in my career to not try for too much. I write this because data is more than a job to me, but there’s a matter of still enjoying it while making time for other fun things in life.

Now this year’s professional goals.

  • Actually make that custom database happen: I want to make my home lab actually look like something worth demoing, which means I should consider the test database I use needs to be a heck of a lot more robust, and I don’t want to be using World Wide Importers a ton. So that racing database which I want to more fully design is where I will start, which will require some creative thinking when I talk to the timers…or to the company behind the prediction algorithms.
  • One new talk to speak on: after not meeting last year’s goal of two, I would rather focus on tweaking the current ones when I have time in addition to adding a new topic for both my own growth and because I was told that my topic idea was rather good as many others have asked this same question in the early days of their jobs/assignments. May start that one with a blog post this time, so no more details…yet.
  • Speak to a new audience, whether it’s a specialized user group locally or even outside the Triangle: I’m hoping that any of those talks can be presented outside of my work environment or my SQL Server community, as I’d like to change up the audience for my own growth. It likely depends on the subject on if I can speak at a group with another language as the focus, whether it is a bunch of analysts who are beyond my pay grade when I was an analyst, or the .NET developers I yell at when they develop bad SQL code and haven’t learned from my own mistakes (I don’t yell; I just quietly facepalm).
  • Learn SQL to JSON integration techniques: so this is something I will need in my next adventure; I will get to that in a separate post. Considering that the only character that likes XML file imports is No One Ever, I think transitioning to JSON could be smoother, but someone will try to fight me over it.
  • (Re)start the MCSA certification exam (stretch – get the certification): time to get back to something I started and then let expire. The database development exam structure has changed since, and I feel more prepared for both exams. This does not mean I finish the exams and get my cert, but I officially train and possibly complete one exam with the intent of the next one not long after.

Hopefully this is a bit more reasonable than the previous year, as now we’ve found a groove as homeowners. So now it’s time for my other major extracurricular activity outside of my career industry…the one where I use my feet a lot.

I did run more miles than the previous year, though it’s not because the last eight months were something outstanding. I had a goal of 1500 miles and missed even hitting 90% of that. My excuse is that I was burnt out after the Shamrock Marathon training cycle and subsequent race ended with a thud. The miles dropped because the desire was lacking, especially as a house and the final stretch of my MBA pursuit took priority. On the good side, I did have half marathon, 10k, and mile personal bests.

For 2019, I want to get back to a spot I was in when it came to endurance versus speed.

  • 1500 miles on the year: I’m going to start an actual training plan which uses some algorithms to create a plan. It’s less than $100 annually, so I can squeeze it in to personal expenses outside the household ones. I believe many of us hobbyists also have a similar mentality to pros and semi-pros that when it comes to miles, we wish to ensure that they count for something.
  • Run my third marathon and break 4:00 (stretch – 3:35): I had this goal for Shamrock and then mile 18 is where everything fell apart. I’m not at a Boston level as some of my friends are, but I think if I put in the work at something I love, this can work. Also not hoping to get a stomach bug in the days heading into the race; that would help.
  • Run a 5k at a consistent sub-6:40 pace (stretch – break 20 minutes): I am a sadist who likes to run fast (for me), though I am very mediocre at my fastest. After I smoked the Rocket Mile last year, however, I’ve found algorithms which predict 19:30 when running on an even course (a rarity). Again, the plan combined with the algorithms (and badgering their data scientists if I’m crazy) would tie into learning more data science techniques if I give myself free time.
  • Racing at least once every 8 weeks at a minimum, and four times in the same span as a maximum: this will be interesting, as that means I need to watch what summer races I select. Charity type is a priority, especially with my likely return as Triangle RunWalk for Autism competitive ambassador.

Data growth is important to me, as is my running stress therapy. So let’s see what 2019 brings after this stream of consciousness entry. Any tips on the realism of the goals or where to begin?

The Spectrum and #MeToo

Seeing all the #MeToo statuses through my social media feeds, which took off after those revelations about Harvey Weinstein, has me in the midst of a two-fold thought. As a male, and as an individual on the spectrum, what would I know and what actions should I take?

A concern I have for those on the spectrum is the influences and the culture giving us impressions that are not correct, or can be rooted in our own issues with relationships (there are many autistics who haven’t had a girlfriend by my age, and not by choice). Some behaviors are expected to be figured out and not literally explained as proper etiquette, including not encroaching on the space of someone else. However, it is no secret that we struggle with what behaviors should be explained.

A man on the spectrum may get some of their knowledge from sex education classes and discussions with their guardians, but they also get a bulk of knowledge of behavior from the media (television/internet) or peers. The former groups may not be sure how to explain the topic, so then outside sources take over. A man may see a behavior and not know at first that there’s a problem with what he has done.

Here is a theoretical. A man on the spectrum sees a girl he wants to get to know; he probably wants to flirt. he goes up and makes some conversation. She finds him to be friendly, and the two hit it off pretty well. Knowing what he’s seen in the past around acquaintances and media, he pulls the leg touch maneuver. Conversation starts to break down slightly, but he’s unaware as she’s still acting friendly even if she pulled back slightly. She says she has to meet with friends, and will come back. However, his first instinct is that she actually does want to come back, and that he has no idea that he was violating her space and that she’s not actually coming back because he was a creep.

You likely figured out this is the part where that man…was me. It took going into unnecessary detail with another friend many months later to get an explanation. Even then I wasn’t sure I understood until, well, hearing more about behaviors via the internet. Oh, I felt terrible about it, as I always believed myself to be better than a creep, and then realized I had these moments for years. We never pay attention to staring and stalking as we’re trying to get around social awkwardness (especially in high school or college). However, I know I’ve done these, and I know others like me who have as well without understanding that it’s not an innocent behavior.

There are instances where I didn’t help when a friend was trying to do his thing and refrained to stop him from backing off, not considering the context of his actions. Let’s not forget instances of “locker room talk” where all I did was act silent and didn’t speak up to say “dude, that’s too far” to the people around. At some moments, I had no idea how bad it truly was based on what I was told. At other moments, I did know and was a coward.

All of this can be independent of the spectrum, but what if an impressionable Aspie believes this is acceptable behavior and does not see the red flags? Or, that the Aspie does see it as bad but thinks they should stay quiet since unrelated behaviors have been ridiculed? Someone worse off than myself may not catch on and will only be enabled, even if it comes to the point that you have to explicitly ask the yes/no question, and ensuring a partner that you’re okay stopping once they say so to make up for the inability to recognize cues (if she says no, then it’s no…if she even says maybe, then it’s not yes). I’m not trying to extend this analogy to say I would believe someone claiming to be autistic and not knowing gang rape is wrong (I call bull). However, even the more seemingly innocent flirtatious behaviors are actually wrong, and those of us on the high end can still learn with the right influences.

Let’s not forget that it goes the other way as well. People on the autism spectrum are 66% more likely to be victims of sexual assault. I recall speaking with two women I know from the spectrum who alluded to these experiences. Their descriptions of sexual harassment had graphic moments, and thinking back to the conversations now is pretty harrowing even as someone who believed them from the start. Some autistic girls claim the #MeToo hashtag for the same reasons an autistic man may let bad behaviors slide.

I can’t, and shouldn’t, claim the whole “as someone with a niece/sister/daughter” thing, as I grew up around mostly males. My concern is the gap when an impressionable person on the spectrum may be a victim or the perpetrator. When we hear “see something, say something” are we going to know what the “something” is in our literal minds? Will we be able to call our male friends out when in action? I don’t see it much, but I worry about being in my own world and not catching creepers in action, or being said creeper. Unwittingly and not, I have been both. I’m thankful for times I’ve been called out, even if I got overly sensitive at the start. That is something we need, and it’s why I am participating in and creating a few forums within autistic groups to talk about what behaviors are permissible, what mistakes we’ve all made, and how to feel comfortable with our behaviors and to ensure others are comfortable too. We can’t use the spectrum as an excuse for improper behavior, and hopefully the internet can be a positive form of sex education with this movement.

Sexual assault is not an easy topic, but it’s on me to talk with others, to teach, and to learn. Feel free to shoot me a message if there is a desire to talk further. Let’s make sure those of us with autistic peers or children set good examples, and that we on the high functioning end of the spectrum can be better men who put an end to this stuff.

Time to Task

Sometimes I need to schedule time for dedicated scheduling time. It may sound odd, but it relates to my issues with time management, which have been inherent for years. In terms of what I do during the day (employment) it is very apparent.

With any job, one has to juggle multiple tasks and deadlines. Earlier this week I found out that a recurring task (which unfortunately calls on me patching a bug manually) did not succeed. I had a calendar event set up but wasn’t in front of the task in time because I miscalculated the reminder. So the vendor caught it first and I was stuck in reactive mode. I don’t like being reactive with the work I do, yet it is difficult for me to figure out proactive ways to get around this without someone else’s advisement.

When you’re surrounded by workaholics, when the engineers you team with are workaholics, when your boss is a workaholic, then I figure you might want to own all your work to show them you’re as dedicated to the craft. Or at least that is my perception. So naturally I don’t want to hand tasks off to others as I see it as a sign of weakness that the Aspergian who can’t handle it. There’s a few tasks that have become actual deliverables, but others I could have delivered about three months ago, leaving stakeholders frustrated. My problem is knowing that I can do it, but I’m not always quick to the task because of a combination of 1) methodical approach to details and 2) various distractions in the open. How does the sensory overload spectrum person deal? Often with headphones. However, what I will also be trying is setting aside time at the start of the week and midweek to figure out what is on my plate, and then talking with my manager to ensure I have priorities right. For career purposes, I like to figure out the tasks myself as a way of understanding the pulse of the business. So my hope is that this will help my time management, which will in turn help me improve my technical chops and value as a senior member of my team.

The list? Has tasks from Team Services and random email requests, then categories of importance rank and completion rank, on a level of low-medium-high. This way I also have perspective on how long I think something will take. That last part will take time to figure out, but that’s also why as a unit we’re tracking the time to get tasks and large projects done.

I have often noticed that this sort of “soft skill” work, if you call it that, is tougher for the spectrum person. However, taking the time for the tasks will help, and I hope I stick with it for a long time.

2017 Public Goal Setting Post for Personal Accountability

I know what some of you are thinking: good riddance to 2016. Looking back on a personal level, it wasn’t so bad. A new half marathon PR, my first hackathon, a return to PASS Summit after a three-year absence, my first speaking engagement for a professional association, starting a new job more suited to my interests, closing the year by getting a promotion of sorts at said job, surviving cohabitation all year long, and actually buying this domain and starting a website! So in light of world turmoil, I think life was okay.

My motivations for creating a website are still related to my own attempt at branding…Which makes it all the more sad that I went long stretches without posts. However, the wrap up at the end of the year is as good a time as any. I have some goals related to my profession and passions and now I post them for my own record, to see if I keep growing and how it happens.

  • Restart on that MCSA certification (Database Development)
    I finished 70-461 back in 2014, near the end of the year. Then I didn’t touch the next two exams out of concern that everything would change with the upcoming SQL Server 2016 release. However, There are new exams which have started, with more specified tracks that only take two exams. While I can’t carry 70-461 to 70-761 (someone keep me honest on that factoid), that exam and 70-762 should be in reach this year, particularly the summer when I won’t be in grad school classes two nights a week.
  • Grasp more data science concepts and strengthen my R abilities
    At my gig in 2014 with a health analytics firm, I started to dabble in Python as part of  new initiative for an in-house statistical tool. Seeing the power of Python as a data extract tool had me intrigued and I began to use it more, especially after I started taking a class on Udemy to enhance my abilities in that realm. Naturally, the concurrent step is the R language. I’m still quite novice there, but started focusing some of my professional workshops on the integration of SQL Server with R, especially with Microsoft’s recent acquisition of Revolution leading to Microsoft R Server. R is a language which no longer represents the future, but the present, and it’s something I look to improve upon as a senior member of our segment of the Business Intelligence department at my work.
  • A SQL Saturday speaking engagement
    There has been some hints and persuasions for me to speak on two fronts. Those from the PASS community know of SQL Saturday quite well. Our chapter leader had noted after the completion of another successful event in the Triangle that he hoped for some of the younger pros in the organization to submit abstracts. Okay, it was multiple senior members of our chapter that I hold in high esteem. Well, this November I gave my first microsession to our local user group, and now I am working to expand on that with a professional development talk. I need to submit for Raleigh in the next ten days, and if I’m feeling confident and time permits, I may try for Baltimore next. As long as it doesn’t get in the way of my day job. Speaking of…
  • Stay at my current company all year
    I have changed jobs during the spring in 2014, 2015, and 2016 alike. I think my current gig is a keeper. I’m leaning back towards analyst versus developer in this gig, but with much more seniority as I’ve improved my technical chops this year as much as any other. The big difference now is that I’m doing a better job applying everything I have learned. The learning curve for the pharma industry has been a bit steep, but now everything is making more sense and I strive to make the lives of my colleagues easier.
  • Participate in one of the RDU civic hackathons
    I saw this on a partial level last year and continue to be fascinated by the potential with open data, but now I want to really immerse myself over the weekend at CityCampNC, Datapalooza, or another of the related events. I expect that developed R skills will come in handy once these events take place. Also, allow me to shout out PASS yet again for getting into this movement at last.
  • At least one technical blog post and one autism spectrum post per month
    Simple: there needs to be more blog entries. I’m underdelivering.
  • Do some further organization for the Run/Walk for Autism
    I didn’t get as involved this year as I hoped, and then we had a rainout. I’m hoping to do more for this, especially involving my tie-in with the Human Rights Committee. That will be a work in progress, as I hope to carve out more of a place in the HRC after I fell behind on cases somewhat in the second half of 2016.
  • Break the 1000 mile barrier and run my first marathon
    Naturally a running goal would be included. I had about 650 this year, which was a tad shorter than I expected due to various injuries. What hurt was that I still trained as if temperatures were normal in the summer (in NC it was quite hot) and pushed myself too much. I’m still learning how to recover, which will be crucial this year. I am eyeing Richmond in November for my marathon debut, so bring on the summer training regimen.

That’s a lot of expectation for 2017 that will be paired with motivation. Now let’s get on to a new year.

The TRY Microsession: A T-SQL Tuesday Follow Up

Warning: contains expletives

Last month, I gave the talk first referenced in last month’s Tuesday blog festival, concerning conversion functions with TRY. I can’t say I wasn’t nervous about giving my first presentation to a professional organization, even as a 101-level lightning talk. I was concerned about my own abilities on that stage, being around a mix of developers who may not have been privy to TRY_CAST, TRY_CONVERT, or TRY_PARSE (oof), and people who know this already and were curious as to how I would fare on stage. For the latter group, it was like presenting a class project to the professors in my department. So how do I think it turned out?

It was a success, and not only based on feedback from others. It was a good chance to understand what is required of speaking and how I could break the ice with others who knew generally what I did, but wasn’t sure what I knew about.

With this in mind, I learned some lessons from the presentation.

  • Always bring cables, and backup cables. I did bring my work laptop as a backup computer when I had update issues with my affectionately-nicknamed “Shitty Toshiba”, but the HDMI connection is not the most modern…and I never thought to bring the cables for it. So we had a misadventure where I had to open my script on another computer. Originally, I had UPDATE and INSERT statements included, but I couldn’t risk that WideWorldImporters was in its original condition. Maybe the cables would have allowed me to continue with more than SELECT.
  • Even when carrying a note card, don’t go too off topic just to show you aren’t reading from the card. I’m not the most concise person, so it was easy for me to elaborate more than needed.
  • Know your room setup. This was my first chapter meeting since summer, and the meetings moved to a new office building of which I was unfamiliar.

There were also a few positives to take away.

  • Since I had to use a backup computer, one which I did not have WideWorldImporters as it was my work databases only, I was able to rely on agnostic SELECT statements to show the functions in action. It was the biggest key to recovery.
  • I kept a sense of calm despite the cable issue, and despite deep down thinking “they don’t think I’m prepared…fuck” while taking longer than necessary to set up. To take something in stride would not rattle my audience or allow me to come off nervous; I can be very expressive, so it did take some work to remain confident that a backup solution was in the works.
  • I was confident that I knew the basics of the subject, while not trying to act like I knew everything on the subject. There was a moment early on where I said I was a developer and immediately ducked, explaining next that I “didn’t want the DBAs to throw things at me. Wait until after.” Got a good laugh.
  • You know how I said I elaborated more than needed? I didn’t elaborate too much, as the two rehearsals of my talk seemed to keep me relatively constrained, even with my audibles.

The next phase? The submission for a Saturday event…a full-length presentation. Now here’s my chance to take advantage of the help that came from this forum! Thanks to everyone, especially Andy, for giving a platform for us noobs to join the speaker chapter.

T-SQL Tuesday #84: Becoming a Speaker

I’m going to take part in T-SQL Tuesday this month. Why? Well, because I’m now starting to venture into speaking on beginner topics and lessons learned as I perfect my technical chops and learn new skills. This month, the subject is on new speakers, and the veterans who have advice for those of us starting out.


Talk #1 is already happening

Funny thing is…I’m already going to make my speaking debut on November 15, giving the microsession at our Raleigh-Durham PASS User Group. I’ll be discussing the TRY conversion functions (TRY_CAST, TRY_CONVERT, TRY_PARSE) over a 10-15 time frame. The talk was inspired by a case at my job involving error reporting, and some manually entered date values that led to exceptions in the original procedures. For this talk, I’ll be using some basic examples to explain each one. How CAST and CONVERT throw exceptions, while TRY_CAST and TRY_CONVERT avoid it with NULL instead. How PARSE is something most people don’t touch for good reason. It should be interesting to start out, and I’ve received some good advice from a few professionals before I rehearse. It does help that I’ll be working on rehearsal with partners as well for critiques.

Talk #2 is a longer talk – a Saturday submission

This March is SQL Saturday Raleigh, and it’s not a secret to some of the members that I’m strongly considering throwing my hat in the ring for a slot this year. I’ve been considering two topics to begin. One is about merging Python and T-SQL together. Of course, I’m picking up R and the Revolution Analytics acquisition has led to SQL and R beginning a beautiful relationship. However, Python is still a popular enough language that for the longest time I struggled to consolidate with SQL. I want to investigate this further to see if this is possible as a data tool.

Another topic that has gotten some traction is about moving from analyst to developer. I started my career as an analyst, then became a Database & BI Developer, and now I’m in a bit of a hybrid role. My view is that there are plenty of analysts who queried all the time, like myself, who might want to work more in the development space. Maybe from there someone will want to be a DBA, or a data scientist (CHEERS). So I’m looking into the high level tools if an analyst goes from having read permissions to having write & execute as well, and might have to use Reporting & Integration Services more often. It’s experience that I have been through, and now I think I can touch on this experience again in my current full-time role.

How does this get moving?

It is partially spectrum-related, but I am unsure where to start and how to approach someone for advice directly. A general blog post with references sounds like a perfect place to begin, and I’d like to see where I can go, and how some veterans learned their speaking skills. I’m nervous of talking about a subject that I don’t know much on, as I’ve had those over-my-head moments in front of fellow professionals. Now I have to ask myself, and others, how to target the audience and let everyone know that I’m impersonating an expert. Then there’s the matter of adjusting presentations and turning feedback into something constructive for another technical topic (or even the same one, refined for a different crowd).

I read Steve Jones’ post for this month as I was writing, and it made me think of one of my esteemed colleagues (yet another Steve who is part of PASS, affectionately known as SQL Steve in our company’s department) who did a series of weekly tips and tricks for our team, which included some practical examples we could use internally. It reflected the way of getting started with a small group. The other aspect that stuck out to me from the post was this:

[Your first technical speaking opportunity] can be hard as you need to be open to debate, accept you might get some things wrong, and that you might learn something. There is stress in trying to come across as an expert but I’d rather you try to teach something, with the idea that this is your understanding of a topic, but you are open to admitting you might be wrong if someone else knows more.

I don’t consider myself an expert at all. Intermediate in some areas, beginner in a few others, and court jester in the last one or two. It seems that a key for my microsession will be that experts in the house will have critiques, but it does not mean that they believe I’m ignorant here. I’ll have to keep my skin tough and stay aware that there will always be learning. The interaction and discussion will allow everyone to learn and explain how they came to their conclusions.

I’m excited about reading some of the other posts that are in the very long comment section, and what advice can be gleaned. Let me know that I can reach out to you (as Andy did on his host post), and I’ll do just that.

PASS Summit 2016: Wednesday

Now the sessions begin. Okay, began, since I’m posting on Friday.

First, Microsoft’s Joseph Sirosh provided the keynote address. The focus was on the power of data and analytics in this changing world. I didn’t write anything down during this session, so instead I’ll link to Kevin Kline’s recap of the talk.

My focus was towards some sessions to help with query writing. I first attended Andy Yun’s session, Why Your Data Type Choices Matter. Awesome session. Andy first focused on the internals (how data is stored). While I was aware that a table was not created in the order of the columns listed, I did not know much about the FIXVAR format. If using variable columns (ex. VARCHAR), the extra two bytes represent the variable column offset array. We need more metadata to read the actual data. One thing that stuck out was the question of using unicode versus non-unicode. If you use the former (VARCHAR), then data could be lost and refactoring may be required. If you use the latter (NVARCHAR), the storage requirement is doubled. The key is to right-size data types (even in temp tables…if a certain person is reading, the person is being called out!), match data types, and recall the 8KB page size limit.

During lunch, I sat with a table of developers and DBAs talking about query tuning. The major advice I received was during a conversation about date functions that related to  project I had worked on previously. Basically, I was told about how scalar user-defined functions can be a terrible thing in many situations. This may be open for debate, but scalar UDFs can bring down the IO. Now I’ll have to investigate that further.

My next session was Optimizing SQL Server and Databases for Large Fact Tables, presented by Thomas Grosher. The idea was that sometimes we have to pull data from large fact tables, and we can apply some tricks to make this happen. Say, if there’s a need to read a fact table for a report which runs for two hours, how do you help it. This session did go over my head a bit, as it seemed more performance-based than BI-development-based. Though I did take away a few things. First, it’s important to choose a clustered index key wisely, such as using a lookup table for common parameters. Second, table partitioning comes into play, which I haven’t tried to do much myself, but our engineering unit has. Third, row compression. We have some tables at my company with one billion rows, and Thomas explained that 1 byte less on 1 billion rows can save 1GB. A solid session, though I may have been surprised by the deeper infrastructure content.

Final session of the day was on Biml for Beginners, by Cathrine Wilhelmsen – a great example of the next generation of data platform stars already coming to fruition. So Biml is something I had brought up in passing at work and read a few blog articles about, but I had not followed through. Here was my chance. Basically, Biml (Business intelligence markup language) is helpful to many a business, including my company, by using business logic to easily repeat SSIS packages. It’s essentially XML. I learned about the beta BimlOnline as well, which does reverse engineering to help us gather what the Biml looks like. We can even extend Biml with C# or VB code blocks to import structures. Cathrine even explained tiered Biml files to repeat such attributes like admin, source, and destination. To use a practical example, there are some weekly reports to automate coming up, and this could be relatively useful to set these packages up at a base level. Biml is not meant for deployment, but it can save us valuable development time. Easy to learn more as well. I’m adding it to the personal projects list.

Lighten up, Francis. Biml is a good thing.

Exhibitors had a fun reception with appetizers and light dinner. I’m bringing home more Idera ducks. Then came more karaoke for the night, put on by Pragmatic Works at the Hard Rock Cafe. Same drill as the previous night with networking opportunities.

That was a lot of words. So next recap is Thursday.