Easily query your GitHub Contribution activity

For a variety of reasons, including your Microsoft MVP activity reports, you might want to summarize your GitHub activity across multiple repos. 

For contributors to Microsoft open-source docs on GitHub, all your contributions would end up in the MicrosoftDocs organization. Microsoft owns thousands of open-source repositories across many organizations, if you've contributed to others, variations on this same query work.

Absent a dashboard making this easier (keep an eye on this), here are some GitHub queries helpful to you.

You can find all merged PRs in public MicrosoftDocs org repositories using this search syntax on GitHub.com's main search bar:

is:pr author:<your GitHub username> is:public org:microsoftdocs merged:>=YYYY-MM-DD

For example:

is:pr author:williamadba is:public org:microsoftdocs merged:>=2019-01-01

Which results in the URL:

https://github.com/search?q=is%3Apr+author%3Awilliamadba+is%3Apublic+org%3Amicrosoftdocs+merged%3A%3E%3D2019-01-01&type=pullrequests

You can also use the GitHub API for programmatic access:

https://api.github.com/search/issues?q=is%3Apr+author%3Awilliamadba+org%3Amicrosoftdocs+is%3Apublic+created%3A%3E%3D2019-04-01&type=pullrequests

Feel free to comment if you have other preferred methods of tracking your Docs contributions on GitHub.com.

Migrate datetime data to datetimeoffset with AT TIME ZONE

I recently reviewed, worked on, and added a similar example to the DATETIMEOFFSET Microsoft Learn Docs article at the recommendation of my colleague Randolph West, who guessed (accurately) I would enjoy such a task. It was a nice pre-Build diversion. 

This topic is one that I have co-presented on in the past and hounded project capstone review presentations about. If you're not storing time zone offset in your date/time data, you're setting yourself up for future pain. That future pain is not what this blog post is about.

One of the big complications of converting date/time data without time zone info is Daylight Savings Time. There are various versions of time zone weirdness around the globe, but in most of the United States, we fall backwards and spring forwards by one hour each year. (This is why the state of Indiana has its own time zone, my dear friend and editor Drew Lanclos noted.)

Simply subtracting the current offset hours to arrive at Eastern/Central/Mountain/Pacific Standard Time will be incorrect for half of each year. This is most invisibly painful when applied to historic data.

How NOT to convert data to datetimeoffset

DECLARE @TimeZone VARCHAR(50);

SELECT @TimeZone = [name]

FROM sys.time_zone_info

WHERE [name] = 'Pacific Standard Time';

--Pretend that audit_created below is a UTC date that needs -- to be converted to the local timezone for display.

--If executed between Nov-March, 'Bad Strategy' below is wrong for historical dates between March-Nov.

--Between March-Nov, 'Bad Strategy' below is wrong for historical dates between Nov-March.

SELECT

    UTCDate = audit_created  

,   BadStrategy = DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), audit_created ) --Don't use!

FROM #audit_created

GO

How to convert data to datetimeoffset

Use the AT TIME ZONE syntax, introduced in SQL Server 2016.

DECLARE @TimeZone VARCHAR(50);

SELECT @TimeZone = [name]

FROM sys.time_zone_info

WHERE [name] = 'Pacific Standard Time';

--Pretend that audit_created below is a UTC date that needs to be converted to the local timezone for display.

SELECT

    UTCDate = audit_created  

,   CorrectStrategy = audit_created  AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone --SQL 2016+ only

FROM #audit_created

GO

In the SELECT statement, the AT TIME ZONE syntax first adds UTC time zone offset to the existing audit_created column data, then converts the data from UTC to Pacific Standard Time, correctly adjusting the historical data for each prior Daylight Savings Time range in the United States.

A longer sample lab for you to try it yourself

DROP TABLE IF EXISTS dbo.audit

GO

CREATE TABLE dbo.audit

(audit_created datetime2(0) primary key)

GO

INSERT INTO dbo.audit (audit_created) VALUES ('1/1/2024 12:00:00');

INSERT INTO dbo.audit (audit_created) VALUES ('5/1/2024 12:00:00');

INSERT INTO dbo.audit (audit_created) VALUES ('12/1/2024 12:00:00');

GO

ALTER TABLE dbo.audit

ADD audit_created_offset datetimeoffset(0) NULL

GO

DECLARE @TimeZone VARCHAR(50);

SELECT @TimeZone = [name]

FROM sys.time_zone_info

WHERE [name] = 'Pacific Standard Time';

UPDATE dbo.audit

SET audit_created_offset = audit_created AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone

WHERE audit_created_offset is NULL

GO

SELECT * FROM dbo.audit

audit_created audit_created_offset 2024-01-01 12:00:00 2024-01-01 04:00:00 -08:00 2024-05-01 12:00:00 2024-05-01 05:00:00 -07:00 2024-12-01 12:00:00 2024-12-01 04:00:00 -08:00

Note how the January and December dates were shifted to UTC -08:00, but the May date was shifted to UTC -07:00.

Pre-2016 lab

Pre-2016 was infinitely more pleasant than the present day in many ways. Time zone conversion is not one of them.

Trying to do this while working in a version of SQL Server prior to 2016? My condolences, but here's the solution I worked out a five years ago when I was in the same situation, and could not use AT TIME ZONE.

--This lab demonstrates a common antipattern for converting UTC to the local timezone.

--Will work <SQL 2016. Optional code at end to uncomment if SQL 2016+

--Create the DST population table in comment below at bottom. This could be useful for your apps pre-SQL2016.

declare @audit_created table

(audit_created datetime2(0))

insert into @audit_created (audit_created)

values

 ('3/12/2017 03:00')

,('3/12/2017 04:00')

,('3/12/2017 05:00') --This will be wrong in the the Incorrect pre2016 method if we are currently in DST (March-Nov)

,('3/12/2017 06:00')

,('3/12/2017 07:00')

,('3/12/2017 08:00')

,('3/12/2017 09:00')

,('11/5/2017 03:00')

,('11/5/2017 04:00')

,('11/5/2017 05:00') --This will be wrong in the the Incorrect pre2016 method if we are currently not in DST (Nov-March)

,('11/5/2017 06:00')

,('11/5/2017 07:00')

,('11/5/2017 08:00')

,('11/5/2017 09:00')

,('1/1/2017 05:00') --One of these two rows will be wrong for Central US Time in the Incorrect pre2016 method. It'll be the one that doesn't match our CURRENT DST setting.

,('6/1/2017 05:00') --One of these two rows will be wrong for Central US Time in the Incorrect pre2016 method. It'll be the one that doesn't match our CURRENT DST setting.

select          

    audit_created  

,   audit_created_actually_at_UTC               =   TODATETIMEOFFSET(audit_created, 0)

,   Incorrect_pre2016_method_Central_time       =   DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), audit_created )

,   Incorrect_pre2016_method_Central_time_date  =   CONVERT(date, DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), audit_created ))

,   Correct_pre2016_method_Central_time         =  

    SWITCHOFFSET(TODATETIMEOFFSET(audit_created, 0),  CASE WHEN EXISTS (select * from dbo.DSTDates where BeginDate<=TODATETIMEOFFSET(audit_created, 0) and EndDate>TODATETIMEOFFSET(audit_created, 0))

                                                            THEN -300 -- -5:00, CDT

                                                            ELSE -360 -- -6:00, CST

                                                            END)

,   Correct_pre2016_method_Central_time_date    =   CONVERT(DATE,

    SWITCHOFFSET(TODATETIMEOFFSET(audit_created, 0),  CASE WHEN EXISTS (select * from dbo.DSTDates where BeginDate<=TODATETIMEOFFSET(audit_created, 0) and EndDate>TODATETIMEOFFSET(audit_created, 0))

                                                            THEN -300 -- -5:00, CDT

                                                            ELSE -360 -- -6:00, CST

                                                            END)

    )

--Uncomment the following two rows for the right way to do this in SQL 2016+

--,    Correct_2016_method              =   audit_created  AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time'

--,    Correct_2016_method__date        =   convert(date,  (audit_created AT TIME ZONE 'UTC'  AT TIME ZONE 'Central Standard Time'))

from @audit_created as A

/*

--Here's a table of DST dates since 1970 to share at parties and impress your friends.

DROP TABLE dbo.DSTDates

GO

 CREATE TABLE dbo.DSTDates

 (  BeginDate datetimeoffset(0)

 ,  EndDate datetimeoffset(0)

 )

 GO

 CREATE CLUSTERED INDEX IDX_CL_DSTDates on dbo.DSTDates (BeginDate, EndDate)

 GO

  INSERT INTO dbo.DSTDates (BeginDate, EndDate)

VALUES

 ('4/26/1970 02:00 -06:00','10/25/1970 02:00 -05:00'),

('4/25/1971 02:00 -06:00','10/31/1971 02:00 -05:00'),

('4/30/1972 02:00 -06:00','10/29/1972 02:00 -05:00'),

('4/29/1973 02:00 -06:00','10/28/1973 02:00 -05:00'),

('1/6/1974 02:00 -06:00','10/27/1974 02:00 -05:00'),

('2/23/1975 02:00 -06:00','10/26/1975 02:00 -05:00'),

('4/25/1976 02:00 -06:00','10/31/1976 02:00 -05:00'),

('4/24/1977 02:00 -06:00','10/30/1977 02:00 -05:00'),

('4/30/1978 02:00 -06:00','10/29/1978 02:00 -05:00'),

('4/29/1979 02:00 -06:00','10/28/1979 02:00 -05:00'),

('4/27/1980 02:00 -06:00','10/26/1980 02:00 -05:00'),

('4/26/1981 02:00 -06:00','10/25/1981 02:00 -05:00'),

('4/25/1982 02:00 -06:00','10/31/1982 02:00 -05:00'),

('4/24/1983 02:00 -06:00','10/30/1983 02:00 -05:00'),

('4/29/1984 02:00 -06:00','10/28/1984 02:00 -05:00'),

('4/28/1985 02:00 -06:00','10/27/1985 02:00 -05:00'),

('4/27/1986 02:00 -06:00','10/26/1986 02:00 -05:00'),

('4/5/1987 02:00 -06:00','10/25/1987 02:00 -05:00'),

('4/3/1988 02:00 -06:00','10/30/1988 02:00 -05:00'),

('4/2/1989 02:00 -06:00','10/29/1989 02:00 -05:00'),

('4/1/1990 02:00 -06:00','10/28/1990 02:00 -05:00'),

('4/7/1991 02:00 -06:00','10/27/1991 02:00 -05:00'),

('4/5/1992 02:00 -06:00','10/25/1992 02:00 -05:00'),

('4/4/1993 02:00 -06:00','10/31/1993 02:00 -05:00'),

('4/3/1994 02:00 -06:00','10/30/1994 02:00 -05:00'),

('4/2/1995 02:00 -06:00','10/29/1995 02:00 -05:00'),

('4/7/1996 02:00 -06:00','10/27/1996 02:00 -05:00'),

('4/6/1997 02:00 -06:00','10/26/1997 02:00 -05:00'),

('4/5/1998 02:00 -06:00','10/25/1998 02:00 -05:00'),

('4/4/1999 02:00 -06:00','10/31/1999 02:00 -05:00'),

('4/2/2000 02:00 -06:00','10/29/2000 02:00 -05:00'),

('4/1/2001 02:00 -06:00','10/28/2001 02:00 -05:00'),

('4/7/2002 02:00 -06:00','10/27/2002 02:00 -05:00'),

('4/6/2003 02:00 -06:00','10/26/2003 02:00 -05:00'),

('4/4/2004 02:00 -06:00','10/31/2004 02:00 -05:00'),

('4/3/2005 02:00 -06:00','10/30/2005 02:00 -05:00'),

('4/2/2006 02:00 -06:00','10/29/2006 02:00 -05:00'),

('3/11/2007 02:00 -06:00','11/4/2007 02:00 -05:00'),

('3/9/2008 02:00 -06:00','11/2/2008 02:00 -05:00'),

('3/8/2009 02:00 -06:00','11/1/2009 02:00 -05:00'),

('3/14/2010 02:00 -06:00','11/7/2010 02:00 -05:00'),

('3/13/2011 02:00 -06:00','11/6/2011 02:00 -05:00'),

('3/11/2012 02:00 -06:00','11/4/2012 02:00 -05:00'),

('3/10/2013 02:00 -06:00','11/3/2013 02:00 -05:00'),

('3/9/2014 02:00 -06:00','11/2/2014 02:00 -05:00'),

('3/8/2015 02:00 -06:00','11/1/2015 02:00 -05:00'),

('3/13/2016 02:00 -06:00','11/6/2016 02:00 -05:00'),

('3/12/2017 02:00 -06:00','11/5/2017 02:00 -05:00'),

('3/11/2018 02:00 -06:00','11/4/2018 02:00 -05:00'),

('3/10/2019 02:00 -06:00','11/3/2019 02:00 -05:00'),

('3/8/2020 02:00 -06:00','11/1/2020 02:00 -05:00'),

('3/14/2021 02:00 -06:00','11/7/2021 02:00 -05:00'),

('3/13/2022 02:00 -06:00','11/6/2022 02:00 -05:00'),

('3/12/2023 02:00 -06:00','11/5/2023 02:00 -05:00'),

('3/10/2024 02:00 -06:00','11/3/2024 02:00 -05:00'),

('3/9/2025 02:00 -06:00','11/2/2025 02:00 -05:00'),

('3/8/2026 02:00 -06:00','11/1/2026 02:00 -05:00'),

('3/14/2027 02:00 -06:00','11/7/2027 02:00 -05:00'),

('3/12/2028 02:00 -06:00','11/5/2028 02:00 -05:00'),

('3/11/2029 02:00 -06:00','11/4/2029 02:00 -05:00'),

('3/10/2030 02:00 -06:00','11/3/2030 02:00 -05:00'),

('3/9/2031 02:00 -06:00','11/2/2031 02:00 -05:00'),

('3/14/2032 02:00 -06:00','11/7/2032 02:00 -05:00')

*/

Yes, I know this blog post could have been written in 2016.

Tips, ideas, and recommendations on your technical blog

This technical blog is hardly the ideal, but I'll use this space here to summarize some of the guidance that my colleague Randolph West and I presented at a Microsoft MVP PGI this week. Their summary and mine specifically on the technical blogging are hopefully a resource for you, especially if you are early in your career or a Microsoft Student Ambassador.

Technical blogging can grow your technical depth and writing skillsets simultaneously. Ideas, tips, and recommendations:

Blog content ideas:

• Reference, samples, labs, especially for newcomers to the field.
• Deep dives into a topic you're interested in.
• Summaries and use cases of new features or changes.
• What's new? pages in Microsoft Docs are rich with blog ideas
• Checklists, best practices and lessons learned are great blog content.
• Your clients and customers are a fertile farm of reusable scripts, patterns and antipatterns, tools, and blog posts. "It depends" answers are great blog posts.
•  Technically reusable content from client to client is a value add. A public bucket or toolbox of lessons learned is valuable.
•  Remember the best way to learn a topic is to try and teach it (or explain it in a public blog).

Tips on blogging:

• You don't have to be unique (but don't plagiarize). You can write about any topic, even if it's been covered by bigger names. Your voice is valuable.
• Don't steal content or plagiarize, but you can admire and emulate (and attribute with links) the style/format of another author or blogger. Emulate things you like about someone else's process or research style or content format.
• Make it into a story if you can, "It tried this, it broke, I tried this, it didn't work, I fixed it this way..."
• A problem with no solution is worth blogging about. Sometimes, blogging about a problem is a great way to work the problem, and figure it out in the process.
• Write regularly, set a schedule. Pick a topic. Not every post has to be a novel.
• Do you ever write too much? Blogging can be great practice in distilling the core problem/concept to a palatable, communicable summary. It's easy to be wordy and redundant. It's a skill to practice writing more concise technical summaries. "If I Had More Time, I Would Have Written Less"
• Challenge your preconceived notions. Be humble in defeat and write about it. If you're proven wrong, your immediate reaction is usually to be defensive. The second reaction should be to learn from it, perhaps blog about it.

Get an editor, or volunteer to edit for blogs, newsletters, articles.

• Ask for a volunteer (or pay a) technical editor for your own blog.
• Listen to feedback. You trusted someone to edit you for a reason.
• Edits can feel like a gut punch. Don't take it personally.
• Politely ask to be someone else's volunteer technical editor for their blog.
• Provide constructive feedback, challenge assumptions, test technical scripts.
• Easier to find inconsistencies or gaps in someone else's work, it can be instructive to your own work.

Updated training list for a new DBA

Met an eager young volunteer at SQLSatBR over the weekend who inspired me to update a blog post that hadn't been refreshed in a few years. See the [UPDATED] Training To-Do List for New DBA.

SQLSat Baton Rouge 2023!

Looking forward to #jambalaya at #SQLSatBR on July 29!

Register today: sqlsatbr.com

I hope to see as many people as possible that weekend, I'll be there at the Friday night speaker event.

On Saturday, I am speaking at:

• 9:45am about how you can contribute to the same Microsoft Docs I work on every day, Microsoft Docs + GitHub + You. Slidedeck available for download.
• 11am I'll be moderating the annual panel conversation about Careers in IT Baton Rouge 2023.
• 1:15pm after lunch meet me in the big auditorium for a brand new exciting SQL Saturday Jeopardy! A Quiz Show for Devs and Data Pros. I'm still looking for at least one more contestant, reach out to me on LinkedIn if you're interested!

See you there!

SQL Server 2022 Administration Inside Out

SQL Server 2022 Administration Inside Out is now available for pre-order from Microsoft Press. 

This book is far more than a quick update. We've rewritten and refreshed large portions of the book, with applicability for modern security and Azure integration features. We reorganized content especially around Azure SQL DB and Azure SQL MI and all the new performance features and integrations.

Biggest of thanks for countless personal hours of deep edits and rewrites go out to Randolph West. Major contributions from the book team: Joseph D'Antoni, Louis Davidson, Meagan Longoria, Elizabeth Noble, and Melody Zacharias. Thanks also and congrats to technical editors William Carter and Josh Smith for their first big book experience. And thanks for infinite patience and tireless updates from our Pearson editor Loretta Yates.

The book should be on shelves and available in digital formats on May 8.

https://www.microsoftpressstore.com/store/sql-server-2022-administration-inside-out-9780137899883

30 Interview Questions for a Database Administrator and Developer

This blog post summarizes the type of technical questions I would ask candidates for a Microsoft SQL Server data platform administrator and database developer role.

Hopefully this helps both candidates and managers prepare for interviews. I have no qualms in providing the brief answers because your interview, like mine, should be: 

• behavioral: based on scenarios, not multiple choice answers.
• open ended: ask for an explanation, not a single word answer.
• conversational: testing how the candidate would explain this to a client or colleague.
• applicable: only ask questions relevant to your environment and in the job description.

When I was the manager of a SQL Server managed service provider and a principal consultant at a consulting company, I interviewed and hired database administrators to be consultants, remote DBAs, and database developers for our app dev internal projects.

I've divided the 30 questions into four categories.

Exam Cram with DataWeekender talk - Think Like a Certification Exam

Fresh off of a two-part, co-authored blog series on certification exams and test-taking prep, I've got a fresh batch of fun sample questions for the January 2023 Data Weekender Exam Cram series. Looking forward to discussing the recently-released Microsoft Azure certification prep resources too at 5:30pm Irish time or 9:30AM PT. 

Register to attend at: https://www.meetup.com/DataWeekender/events/289815767/

Azure cert prep materials are also receiving quite a bit of investment in new resources: career guides, test sandbox, live Azure resource sandboxes, free practice assessments, and more. I'll look forward to discussing those!

You can find the .pptx for my presentation here on GitHub. Questions? Comments? Reach out on LinkedIn.

A video recording of this session is available: 

Q&A on certification exams, exam prep, and test-taking skills

Part 2 of 2. This blog post is co-authored by Ajayi Anwansedo, PhD and William Assaf, who met and worked together at The Futures Fund, a STEM non-profit which offers introductory coding and web development classes to teens and adults.

In the previous blog post, we discussed the why, what, when and how of taking a certification exam. Here, we'll go through a variety of related Q&A, answering from our different perspectives. 

Q&A on certification exams

Question 1: How to start preparing for a certification exam?

William: Start by understanding who the intended audience of the certification exam is. Is it a fundamentals exam covering concepts, or is it (more commonly) a cert exam looking to test years of experience? A fundamentals exam (like Microsoft’s AZ-900 Microsoft Azure Fundamentals or Google's Cloud Digital Leader or IBM’s C1000-068: Foundations of IBM Blockchain Platform V2) is intended to get newcomers rolling in a field with no prerequisites. A fundamentals exam will cover more book learning, but a more advanced exam will ask scenario-based questions, intending to test experience. For a fundamentals exam, usually the provider will provide a significant amount of onboarding information, because they want you to pass and start gaining experience! Other exams like DP-300 or AZ-305 are intended to test experienced professionals with years in the field. For a more advanced exam, you need to be working in the field already and gaining that experience. First step - make sure you’re targeting an exam that is meant for your level of experience.

Ajayi: You prepare for the certification exam by asking the following questions - “What is the certification exam about?“, “Why do I want to take this certification exam?”, “What are the benefits of taking the certification exam?” and “How will my career be affected if I do not take the certification exam?” You can help yourself prepare for the certification exam by answering these questions. Additionally, they will keep you motivated to finish the process.

 

Q2: What kind of test questions are the most difficult for item writers to create?

Ajayi: Multiple choice questions (MCQ) can be difficult to create. MCQs are composed of the question and a selection of possible answers. The test-taker must choose the correct answer from the selection of possible answers. First, the item writer must determine the type of question to create – direct questions, single statement questions or complex questions. Then they must decide if the test-taker can select only one answer or multiple answers. In terms of answer options, it is necessary to strike a balance between making the questions neither too simple nor too complex for test-takers to guess. The item writer must also decide whether the incorrect options should be variants of the correct option or differ significantly from it. Usually, there is the best option, a correct option and all the other options are distractors.

William: Questions that are based on the exact order of operations are tricky to write, and test-takers should understand why. Oftentimes in system setups, there is no specific order necessary, but there are common or best practice orders. But certification exams cannot test best practices or industry standards. A build list question often includes extremely specific complications to force one exact order of operations. Advice for test takers: look for those complications in the question's text, they are there for a reason and are a clue to what wrong solutions can be eliminated. 

Q3: What issues with diversity, equity, and inclusion (DE&I) do you see in some certification exams?

William: Cultural bias is definitely a challenge for exam question writers. There’s a famous SAT question that included an analogy to a “regatta” (a fancy boat race). Many students would not understand this question for no other reason but not to have been exposed to that type of event in their lives. This cuts across socioeconomic and racial lines, not just geographic lines. Many questions involving sports have the same problem because not everyone is familiar with the rules of cricket or racquetball or baseball. Using regional phrases or cultural idioms in an exam can be biased too. This is why item writers, reviewers, and subject matter experts for a cert exam must be from a diverse set of backgrounds and experiences. Questions should test technical credentials, not racial, socioeconomic, geographic, or cultural backgrounds.

Ajayi: To piggyback off William’s sentiments – the same can be said about soccer and football, fries and chips, and trashcans and dustbins. It’s the responsibility of the test taker to recognize that certain items may have different names or meanings. The content of the exam is tailored to the specific industry, but shouldn't be exclusive to a culture or region. In terms of inclusion, there can be accessibility issues in developing questions/answers that cater to various types of learners (Visual, Auditory, and Kinaesthetic learners) and different types of abilities (intellectual ability and physical ability).

Q4: How do you study for certification exams? 

Ajayi: Identify the certification you want to take from the company’s website. Review the certification preview page for important information about the certification. For IBM certifications, the certification preview page contains the certification overview, recommended skills, requirements, exam objectives, exam resources and FAQs. In the exam objectives session, you will find the number of questions you need to answer correctly to pass the exam. You will also find an outline of the exam content and the percentage of exam questions taken from each content area. Next, ask for tips from someone who has taken and passed the exam you are about to take. These tips will help you avoid making the same mistakes others have made. Then determine your study habits – how you like to study; in groups or by yourself, best time to study, and so on. Understanding your study habits can make your study plan efficient. Next, create a study routine and plan. Also, review questions early in your study process.

William: Use the preparation materials provided by the company behind the subject matter, of course. Start by reviewing a syllabus and study guide for the exam. In the case of Microsoft, they are now offering free practice tests for many Microsoft certification exams. Practice tests can be used to validate what you know about the technical content. If you see something on the syllabus, study guide, or practice tests that you don't know or haven't worked with yet, that's where to start. When it comes to actually learning the material covered on the test, study by doing. Take this from some who has written books for Microsoft – don't study only by reading books, study by gaining experience with the features and products covered on the exam.

Q5: Why become certified? How would it help my career?

Ajayi: A standard reason to get certified is because it is required to qualify for certain roles or projects. Generally, people get certified because of past, present, and future events. Remember the time when you had to work on a project and you had no idea what you were doing, or the time you did not get that role because you did not have enough experience? These past events may influence your decision to get certified so that you will be better prepared for similar situations in the future. At present, you may want to get certified because of the company, new role, or new project requirements. You may want to equip yourself with the knowledge or tools to perform better in your current role, to stand out from the crowd or make your team better (team metrics). Future reasons to get certified may include increasing your earning capacity, marketability, and credibility. Other reasons include promotion, career advancement, prestige, and personal development.

William: I always say that the process of preparing for a certification exam is more beneficial to your skillset than the certificate itself. Having taken many SQL Server certification exams, there are parts of the product I only know because of my preparation for past exams, because I never encountered them at work. Most helpful are the times I get hands-on with something new to learn it before the exam. There are also many examples where the prep for a certification exam allowed me to be immediately familiar with new customer challenges. Those are times I can say that exam prep directly benefited my work product.

Q6: What is the main purpose of certification exams?

William: Fundamentals exams are meant to encourage adoption, especially of cloud services. Higher-level exams are intended to test a candidate's specific solutions delivery experience, not their memorization.

Ajayi: Certification exams are used to gauge an individual’s expertise in a particular profession or content area. 

Q&A on test-taking skills

Q7: It’s the day of the exam. I’ve studied, what should I do now?

Ajayi: Everyone has a state of mind where they are most productive – What is yours? and how do you get yourself into that state? Some people have rituals. For me, wearing the same hairstyle for every exam helped. Your thing could be to wear comfortable clothes, eat your best food or no food at all– whatever gets you into that state, do that.

William: Get good sleep the night before. Eat something with protein. Wear comfortable clothes. Go into the exam with confidence that regardless of the outcome, your career has already benefited from the preparation process.

Q8: What should I do in the final few minutes before an exam begins?

Ajayi: I would say be calm and be quiet. Although, I have seen test-takers chatting until it's exam time and still getting good scores. Like I said in the previous question, do what works for you.

William: Review a quick reference card or test prep if you like, but otherwise, try to make sure your heartbeat is calm and cool. Take a few moments to quiet your mind before.

Q9: What strategies should I remember during the test?

William: Remember, each correct answer must be 100% correct and the other answers are 100% incorrect. (Lawyers enforce this!) At no point should the right answer depend on best practices or industry standards or unofficial naming conventions. Don't assume that's what the question is asking, always look for details or requirements to eliminate wrong answers.

Ajayi: If it is a lengthy question like the questions in the PMP exam, read the last statement or the statement containing the question first, before reading the entire question. This way you are not distracted by unimportant details. Read and understand all the answer options before choosing your answer.

 

Q10: What should I do if two answers appear to be correct?

Ajayi: Understand the “ask” of the question. Go to court. Attempt to defend your answer. Explain why you think either answer is the best answer. Remember there is a correct and the best answer. Have scenarios and examples.

William: There will be some details in the question text to make only one answer is 100% correct. Go back through the question text, reading carefully anything you may have read quickly at first. There will be some detail in place to eliminate one of the answers.

 

Q11: What test-taking advice could I share with my kids?

Ajayi: Make sure they know how to prepare for the test in advance. This helps them to be more relaxed as the test date approaches and during the test. Tell them Rome was not built in a day. They cannot complete the test content in a day. They need to have a study routine and plan. Tell them to relate and connect whatever they learn to their everyday life and what they already know. This helps them retain and recall the content during the exam easily. Tell them to answer the questions they are sure of first, then go over the others carefully. Tell them to have fun.

William: Build their confidence through their successes, even if through other subjects in school. Build their confidence through a process of preparation. Build their confidence with repeatable processes so that a kiddo has confidence in their preparation before an exam. And make sure they eat breakfast!

Q12: How do I build confidence with my test-taking skills?

William: Remember that an exam, especially a certification exam, is beneficial to you because of the preparation process. The preparation process, especially where you get hands-on with something you hadn't yet encountered at work or school, is time well spent. The exam itself is just the final step of a journey.

Ajayi: Practice, Practice, Practice. Be your own examiner.

So, you're considering pursuing a certification exam

Part 1 of 2. This blog post is co-authored by Ajayi Anwansedo, PhD and William Assaf, who met and worked together at The Futures Fund, a STEM non-profit which offers introductory coding and web development classes to teens and adults.

So, you're considering pursuing a certification exam. Congratulations for taking your career into your own hands! Preparing for a certification is a commitment but it doesn't require derailing your personal life.  This co-authored blog post is mean to encourage, empower, and enlighten you. 

Does passing a certification exam make you a better developer, programmer, analyst, or administrator? We would argue the process of preparing for an exam, the preparation of the materials, and the mindset that led you to the self-improvement path of the certification exams, are all productive, causative, and beneficial for your career.

The benefits to your own learning and knowledge are obvious but a 2015 International Data Corporation (IDC) study found that certified employees resulted in 56% less unplanned downtime, 58% faster time to market for applications, and 39% less time for new hires to reach full productivity. These are beneficial to your future employers as well, which is why certifications are valuable on your resume.

Let's discuss why, what, and when of taking a certification exam. Then, we'll discuss how: how to prepare, how to have success test-taking, and how to build confidence in test-taking skills. In the next blog post, we'll address some frequently asked questions about certifications, exams, test-taking skills, and more.

Why pursue a certification exam?

First, you prepare for the certification exam by asking the following questions:

“Why do I want to take a certification exam?”

“What are the benefits of taking a certification exam?” and

“How will my career be affected if I do not take the certification exam?”

Answering these questions will help you prepare for the certification exam. Additionally, a clear understanding of the reason you need to take the exam and the potential consequences of not taking the exam will keep you motivated to finish the process.

 - Ajayi

What exam should you pursue?

After deciding you are prepared to take a certification exam, the next step is to decide what exam to take.

Sometimes taking a certification exam is mandated by your organization or job prospect. Here we will address taking a certification exam for self-improvement/career advancement purposes. Consider:

1. Deciding on what certification will enhance your current job performance or career prospects.
2. Get an idea from someone who is at the ideal job/position you are pursuing.

Next, it's important to understand who the intended audience of the certification exam is.

Is it a fundamentals exam covering concepts, or is it (more commonly) a cert exam looking to test experience? A fundamentals exam (like Microsoft’s AZ-900) is intended to get newcomers rolling in a field, whereas exams like DP-300 are intended to test solutions experience.

A fundamentals exam will be more like book learning, but a more advanced exam will ask scenario-based questions, intending to test experience. For a fundamentals exam, usually the provider will provide you with a significant amount of onboarding information, because they want you to pass and start gaining experience!

For a more advanced exam, you need to be working in the field already and gaining that experience. Make sure you’re targeting an exam that is meant for your level of experience.

- William

When should you pursue a certification exam?

Timing, they say, is everything. A friend took a certification exam a few months before their company went through reorganization. The reorganization of the company resulted in said friend having to interview for a new position. During the interview process, one of the major requirements for this new position was the certification they had taken months earlier. I had no idea I was friends with Nostradamus.

- Ajayi

Taking certification exams before you need them can help you prepare for a future role. Here are some of the best times to take certification exams.

1. You're a student or new to the field (target fundamentals/introductory certs first!)

2. When you are job-hunting

3. After you get a new job

4. Before the promotion cycle

5. Whenever you have idle time

6. When you need to shake up your career

7. When you're afraid of becoming obsolete*

*That last reason for taking a certification exam should be a bigger concern for folks who work on aging technology and don't get an opportunity to see new products or features. Preparing for a certification exam is a great way to make sure you aren't vulnerable to being left behind by progress or vulnerable after a layoff. Your employer is not motivated to keep you up to date as you maintain legacy systems, that is up to you!

So now that you know how to prepare before taking a certification exam, when are you taking your next certification exam in 2023?

In the next blog post, we'll address some frequently asked questions about certifications, exams, test-taking skills, and more.

Bios:

Ajayi Anwansedo, PhD is an Associate Training and Change Consultant for IBM. She has over a decade's experience in training and development. She is passionate about STEM education and is a 2-time National Center for Women in Technology Award (NCWIT) Winner – for playing a pivotal role in promoting gender equity in computing and technology. She works with various organizations to make STEM accessible to all.

William Assaf is a senior content developer for Microsoft Learn for the Database Docs team, a veteran writer and SME for Microsoft certification exams, and author of the last three editions of SQL Server Administration Inside Out by Microsoft Press. William has been writing and taking certification exams for the past decade and has been authoring Microsoft Learn content since 2020.

Speaking at SQLSaturday MN 2022

Thanks all for joining me for two sessions at SQLSaturday Minnesota 2023, and for great feedback and questions. I'm always a big supporter of SQLSaturday events whenever I can and I was honored to speak virtually to this conference again!

Slide decks for my two presentations are available for download on GitHub.

In SQL Server 2022, set your transaction log file autogrowth rate to 64 MB

A late-breaking change that arrived in SQL Server 2022 is an important one that could be a new line item in your health checks.

The basics:

Starting with SQL Server 2022, transaction log file growth events up to 64 MB in size can benefit from instant file initialization (IFI). As usual, the transaction log is otherwise unable to benefit from instant file initialization. 

This should be a big performance improvement if your transaction log files unexpectedly grow. Of course, you should try to avoid autogrowth events altogether. 

There's lots more cool new stuff in SQL Server 2022, too.

Impact to health checks:

The default autogrowth rate for transaction log files in new databases is already 64 MB. Unless you have specific needs for your workload, consider keeping this default now, considering the dramatic advantage IFI brings to file space allocation.

In both your database and log files you should proactively pre-allocate enough space to avoid autogrowth events anyway. Regularly monitor the space in database files to prevent autogrowth events. The proactive DBA, outside of busy hours, should grow database data and log files manually, so that they do not autogrow during busy hours. 

And whatever you do, don't shrink your data and log files, only to have them autogrow again the next business cycle.

Details:

• Applies to both manual file size changes and autogrowth events <=64 MB.
• Transaction log growth events larger than 64 MB are unaffected by this performance improvement. No change.
• Still no IFI for database restores or initial transaction log file initialization for new databases.
• This change to autogrowth events up to 64 MB is new for SQL Server 2022, and arrives Azure SQL Database soon if not already, from what I understand. 
• It is not yet available for Azure SQL Managed Instance, but I would guess it will be there soon.
• This feature is available on all editions of SQL Server.

Make sure IFI is enabled:

Of course, this only works if your SQL Server instance is capable of instant file initialization. The SQL Server service account must be granted the Perform Volume Maintenance Tasks permission in Local Security Policy on the Windows server. A quick script works for modern versions of SQL Server: 

SELECT servicename, instant_file_initialization_enabled

FROM sys.dm_server_services

WHERE filename LIKE '%sqlservr.exe%';

I'm speaking at PASS Data Community Summit 2022

Looking forward to speaking in Seattle at next week's PASS Data Community Summit by Redgate. I'll be speaking both as a representative of my role on the Microsoft Database Docs team and as a data professional. 

On Wednesday Nov 16, you'll find me in the main exhibit hall in the Microsoft Booth Theater (#217) giving a presentation on contributing to Microsoft Docs, at 530pmPT. Hope to see you there. If prizes are available to give away, you know I'll give em up.

On Thursday afternoon, Christine and I are presenting together on a topic that is important to us, Ethics in Modern Data. I bring my years as a data professional and Christine brings her experience and a Masters degree in Organization Psychology, combined with our passion for history, civil rights, and technology. We'll be discussing issues ripped from the news headlines and in history. These will frame a discussion about bias in data collection and analysis, and our responsibilities as data professionals. I hope we spark your interest in these important topics that shape our data industry.

On Friday morning, I'll be presenting a full hour on Database Docs. We'll discuss how they work behind the scenes and how you can contribute to open-source docs via GitHub. This will also be an interactive feedback panel for the entire Docs platform inside Microsoft Learn. Hope to see you there, inspire you to contribute to the Docs that the entire data community uses daily, and answer any questions.

A simple lab to demonstrate the danger of NOLOCKs in INSERT statements

The READ UNCOMMITTED isolation level, which the NOLOCK table hint activates, is not to be trusted when it comes to writing data. You are not always protected by error 1065 in cases where writes meet NOLOCK. 

Error 1065 states "The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements."  However, NOLOCK can still dangerously be used as the source of the write. (NOLOCK can also fail with error 601 on even basic SELECT statements, but coders just aren't always scared off by that.)

Here's a very elementary example to share at parties in order to scare developers away from using NOLOCKs adventurously:

--Connection 1
DROP TABLE IF EXISTS dbo.testnolock1
GO
CREATE TABLE dbo.testnolock1
(id int not null IDENTITY (1,1) PRIMARY KEY,
text1 varchar(10) not null
)
GO
INSERT INTO dbo.testnolock1 (text1) VALUES (1);
GO

Now we have a table with 1 row. Let's begin a transaction to insert another row, but not commit it.

--Connection 1
BEGIN TRAN
INSERT INTO dbo.testnolock1 (text1) VALUES (2); 

And in a second table, let's do an INSERT based on a SELECT statement with the NOLOCK table hint.

--Connection 2
DROP TABLE IF EXISTS dbo.testnolock2
GO
CREATE TABLE dbo.testnolock2
(id int not null IDENTITY (1,1) PRIMARY KEY,
text1 varchar(10) not null
)
GO
INSERT INTO testnolock2
SELECT TEXT1 FROM testnolock1 (NOLOCK)

Without the NOLOCK hint, would be blocked by the INSERT in progress in Connection 1. 

With the NOLOCK hint, the INSERT statement in Connection 2 completes immediately...  however, back in Connection 1:

--Connection 1
ROLLBACK TRAN

What have we got now? From any connection:

SELECT * FROM testnolock1;
SELECT * FROM testnolock2;

One row in testnolock1, two rows in testnolock2

While the INSERT into testnolock1 rolled back (because of one of many different transient errors or failures, not just due to a ROLLBACK), the INSERT with the doomed row where text1=2 succeeded just fine in testnolock2. This is bad! Should Connection 1 retry its INSERT, we could have duplicated data, primary key violations, etc.

If this seems basic to you, great! You have a healthy understanding and fear of the chaos that NOLOCK can unleash. 

SQLSaturday Baton Rouge is back!

Looking forward to jambalaya at #SQLSatBR in THIS SATURDAY! Also looking forward to speaking and seeing all of you there, I guess... Register today: https://sqlsaturday.com/2022-08-06-sqlsaturday1026/

After I stepped down from the SQLSaturday Baton Rouge organizing committee lead in 2019, I had no idea what was to come. A pandemic, the dissolution of SQL PASS, and two missed Augusts later, SQLSaturday Baton Rouge is back! One thing I did get right in 2019: other volunteers deserved the chance to lead and organize and network and they've been doing a great job, from what I can tell so far. I'm happy to see the assets and resources we'd built together over 11 years of SQLSaturdays at LSU continue to be put to use.

I am speaking at:

- 9:45am in the Careers in IT panel

- 11am for Tabletop Role-playing Games at Work for Fun & Profit with Stacy Vicknair and Christine Assaf - Presentation available for download

- 1:30pm for How to Think Like a Certification Exam - Presentation available for download

See you there!

Speaking on Ethics in Modern Data at Improving Edge 2022

Christine and I are looking forward to speaking at the Improving Edge conference, co-presenting our session on Monday, June 6th. Our presentation on “Ethics in Modern Data” features topics relevant to modern developers and data professionals, using historical and current events to discuss ethics in data collection and analysis. 

This is an important topic that lives at the crossroads of our careers, Christine's career in organizational psychology and human resources, my career in data, our work and volunteerism in civic non-profits, and our joint passion for history and civil rights. It's important to understand that when dealing with bias: outcomes matter, intentions don't. 

Our slide deck, references, and citations are available for download.

SQL Server linked server to .xlsx Excel files

A question came up recently concerning whether it was still possible to connect to .xlsx Excel files as  SQL Server linked servers.

Short answer: Yes, it's possible as recently as SQL 2019 using ACE.OLEDB.12.0 provider. 

Medium answer: Yes, but linked servers from SQL Server to Access and Excel are only supported when using the deprecated 32-bit Microsoft.JET.OLEDB.4.0 OLE DB provider. 

Long answer: Yes, but this shouldn't be part of any automated ETL process, please please engineer around using Excel file interchange in any automated data ingestion processes. That said... 

Ethics in Modern Data at Music City Tech 2021

 

On Sept 16, Christine and I presented our joint presentation on Ethics in Modern Data at the Music City Tech 2021 Virtual Conference. Thanks for joining our presentation!

This session explores a variety of considerations that modern data scientists and data practitioners must account for when gathering and presenting data, including topics on bias, construct analysis, and machine learning. We'll discuss examples from history and current headlines.

This is an important topic that lives at the crossroads of our careers, my wife's career in organizational psychology and human resources, my career in data, our work in civic non-profits, and our joint passion for history and civil rights. It's important to understand that when dealing with bias: outcomes matter, intentions don't. 

Our slidedeck and all citations and references will be made available for download here.

Ethics in Modern Data this Saturday with the South Florida Data Geeks

This Saturday Aug 7, Christine and I will present our joint presentation on Ethics in Modern Data. 

This session will explore a variety of considerations that modern data scientists and data practitioners must account for when gathering and presenting data, including topics on bias, construct analysis, and machine learning. We'll discuss examples from history and headlines.

This is an important topic that lives at the crossroads of our careers, my wife's career in organizational psychology and human resources, my career in data, our work in civic non-profits, and our joint passion for history and civil rights. It's important to understand that when dealing with bias: outcomes matter, intentions don't. While many of our examples come from the historical context of the United States, not all, and we have added additional context for international audiences. 

Our slidedeck and all citations and references will be made available for download here.

Register for the virtual event here, and we'll see you Saturday afternoon at 2pmET.

Ethics in Modern Data at the Baton Rouge Analytics and Intelligence Network (BRAIN)

On July 27, Christine and I will present our joint presentation on Ethics in Modern Data. Looking forward to speaking at yet another technical user community looking to jumpstart after a COVID-induced hiatus. 

This session will explore a variety of considerations that modern data scientists and data practitioners must account for when gathering and presenting data, including topics on bias, construct analysis, and machine learning. We'll discuss examples from history and headlines.

This is an important topic that lives at the crossroads of our careers, my wife's career in organizational psychology and human resources, my career in data, our work in civic non-profits, and our joint passion for history and civil rights. It's important to understand that when dealing with bias: outcomes matter, intentions don't. While many of our examples come from the historical context of the United States, not all, and we have added additional context for international audiences. 

Our slidedeck and all citations and references will be made available for download here.

Register for the virtual event at 5pmCT here.