T-SQL Tuesday #132 – Coping With the Global Pandemic – PIVOT

T-SQL Tuesday is a monthly blog party on the second Tuesday of each month. Currently, Steve Jones (Blog|Twitter) organizes the event and maintains a website with all previous posts. Everyone is welcome to participate in this monthly blog post.  Thank you Taiob Ali for this month’s blog topic: ‘How each of us is coping with a global pandemic’.

During this time of a global pandemic, I have tried to take a positive route working my way through life.  It felt like my life was always very busy pre-Covid and I did not have enough time for myself.  But I have been known to make lemonade when life hands me lemons.  Sometimes certain situations that were not pleasant were the push I needed to get moving.  So, when the pandemic hit and things changed, I have kept moving with one motto: pivot. Do you remember the Friends episode when they were moving the couch and Ross kept yelling ‘Pivot.’ I hear his voice in my head when I have to pivot. Saying ‘pivot’ when the situation changes and we need to redo our plans also lightens the mood a little.

The state of Colorado shut down on Friday, March 13, 2020.  I was supposed to be on vacation with friends in Moab, Utah that weekend.  Some of us were going to do an organized bike ride and some of us were going to run a half marathon then do some mountain biking.  The bike ride was postponed until 2021 and the half marathon offered a virtual option so I was able to run the half marathon on my own.  PIVOT On Saturday, March 14, I got out and ran the half marathon in Denver that I had been training for four months to do.  A couple of friends shadowed me along the route, provided water, snacks, pacing, and moral support.  It ended up being on of my favorite half marathons. 

On Tuesday, March 17, I had to go to downtown Denver to close on the sale of my parents house. Normally, St Patrick’s Day brings a lot of people to downtown Denver for all day celebrations.  There are people in the streets wearing green and celebrating. Instead, it was cold, overcast, and there was hardly anyone on the streets.  It was very eerie.  On my drive home, the clouds broke and the sun came out. I knew everything would be ok. 

There are a lot of things I miss that did not happen this year.  For one thing, I was supposed to travel a lot in 2020.  I had more trips planned this year than any year before.  One by one the trips started getting cancelled and tentatively scheduled for 2021.  Now it looks like some may not happen until 2022.  PIVOT Like everything else, we have figured out how to get out of the house and change a negative into a positive.  We changed our idea of travel from ‘we need to fly’ to ‘we can drive’. In July we stayed in a cabin outside of Steamboat, Colorado for a long weekend.  We were able to be outside with friends but be socially distant from other people.  In August, we were supposed to do a bike ride from Seattle to Vancouver. That was cancelled (PIVOT) so we decided to drive to Glacier National Park in Montana for five days instead. It was a long drive for a short stay but totally worth it.  We stayed in a silo that was converted into an apartment.  Our friends brought their camper and parked in the field next to the silo.  We were able to get away from the house, be outside, and be with friends at a distance.  We spent one day biking in Glacier including riding up Going-to-the-Sun Road.  Any other summer, the road would have been too busy for us to enjoy the ride.

No cars on Going-to-the-Sun Road

PASS Summit is happening this week and I was supposed to be in Houston. PIVOT When it was announced that the conference was going virtual, I decided to still get away from the house during the conference. I reserved a condo in Breckenridge, Colorado for the week of PASS Summit. I was going to get away, have no household interruptions, and enjoy the conference. Then on Saturday, I was told my mom was COVID positive. I had just taken her to the doctor on Thursday which meant I was exposed. PIVOT One of the first things I did was cancel my condo reservation. So far I have no symptoms and I can still enjoy the conference, just from home.

There are many things from my pre-Covid life that I do not miss and will work hard so they do not creep back into my routine once we return to a ‘normal’ schedule.  But a big thing that has come out of this is really understanding how important my friends are in my life.  My friends and I have figured out how to get together yet be distant.  It took some adjustments, thinking outside of the box and a few pivots, but we have been creative and successful.  I even had a birthday celebration outside with friends this year.  With winter approaching, we will need to come up with new activities that we can do together while being distant.

This year has been a challenge and I have found that you have to be flexible.  I know people who feel ‘stuck’ because things they had planned to do are not happening. Being flexible and being able to change is important right now. Also, instead of focusing on what I can’t do, I have been trying to focus on what I can do.  I have made changes and tried to work on things that I don’t normally have time to complete because I am too busy with things outside of the house.  For that, I am thankful, but I am ready to get back out and see my friends/co-workers/family and give them all hugs.  I can’t wait to start hugging again!

Build Your Brand Presentation — Aunt Kathi’s SQL Server Home

Tonight I gave my “Build Your Brand with Technical Writing” presentation (slides) to the Triangle SQL Server User Group (Raleigh, NC). I love giving this talk because I typically get one or two people who really want to get started writing ask me for help because of the talk. Writing has been a passion of […]

Build Your Brand Presentation — Aunt Kathi’s SQL Server Home

T-SQL Tuesday #129 – SQL Community Time Capsule

It is time for another T-SQL Tuesday! Thank you Tammy Clark (t|b) for hosting this month. This month we are building a SQL Community Time Capsule.

I am contributing to the SQL Community Time Capsule my experiences from PASS Summit.  There are two parts to PASS Summit.  There is the conference itself and the time spent in Seattle outside of the conference.

PASS Summit is amazing and I love the conference, but some of my favorite experiences have been outside of the conference. Each morning, before the conference starts, and before most people are even awake, I run the streets of downtown Seattle.  It has become one of my favorite places to run.

Some attendees of PASS get together each morning and run at 6:00am.  I have joined the group on some runs.  It is a great place to meet people outside of the conference. 

I don’t run with the group every morning.  My favorite time to run in Seattle is at 5:00am.  Coming from Colorado, running in Seattle is so different.  There is usually a light mist, sometimes a light snow, or if nothing else, there is humidity.  I really love running next to the water.  There are the lights, the smells, and the sounds of Seattle.  It is amazing.  Running through downtown Seattle at 5:00am, you get to see the city wake up.  There are delivery trucks coming and going and not many people.  It is active but in a very different way.

I finish my run a few blocks away from my hotel at Top Pot Doughnuts.  This place is a PASS Summit tradition for many for different reasons.  I like getting there early in the morning and enjoying a fresh donut after my run.

I am going to miss running in downtown Seattle at this year’s PASS Summit. But all is not lost. I have rented a condo in the mountains of Colorado for the week of Summit. I still plan to get out and run at 5:00am. Instead of lights and water, who knows what I will see. All I know is there better be a good donut place nearby!

Beware the pronoun: How inclusive language doesn’t mean an inclusive environment — THE CLANDESTINE

Quote

Michela Quecchia is a third year at KCL and a cancer survivor with an interest in healthcare and the environment. Currently juggling OCD and philosophy studies. [Featured Image: Three people with purple hair and green shirts reading they, them, theirs against a light purple background.] Whether you are a student or a professional, or simply […]

via Beware the pronoun: How inclusive language doesn’t mean an inclusive environment — THE CLANDESTINE

Understanding SQL Server indexes

When you work as either a SQL Server database administrator or developer, you hear a lot about indexes.  So what is an index and what is it purpose?  An index is created on a data table or view and is used to quickly locate data without having to search every row within a data table.

While they can improve the amount of time it takes to search through your data, an index does require its own storage space.  When an index is created on a table, the data table is not changed.  Instead, a new data structure that refers to the original table is created.  This requires additional storage space so users need to consider storage vs speed when adding new indexes.

An index is made up of a set of pages that are organized in a B-tree structure.  This is a hierarchical structure that contains three levels: Root, Intermediate, and Leaf.  All of the data is hosted on the Leaf level.  The key values and pointers are contained in the Root and Intermediate levels.

BTree

 

An index can include one or more columns for the specific table that you are working with.  Without an index, a query search goes through all of the records in the database to retrieve only those records that are needed.  With an index, the search is on the index first to find the desired records and the corresponding records are retrieved.

An index can contain several columns, but not all data types can be used within an index.  Indexes can be created on most columns within a table or view except for those that are of image, ntext, or text data type.  Columns of varchar(max), nvarchar(max), varbinary(max) and xml data type are only allowed in a non-clustered index as a non-key index column.

There are two basic types of SQL Server indexes: clustered and non-clustered.  A clustered index defines the order in which the data is sorted within the table.  Since the data can only be sorted one way, there can only be one clustered index per table.  If there is a primary key in your table, it automatically creates a clustered index on that particular column.

Unlike the clustered index, the non-clustered index does not sort the data within the table.  Since the non-clustered index does not define the sort order, there can be more than one non-clustered index on a table.  Depending on the version of SQL Server you are using, you can have 999 non-clustered indexes in SQL Server.  This is true if you are using SQL Server 2008 R2 or greater, which most people are now using

Let’s look at the impact that an index can have on a relatively small, simple table. For this example, I have made a copy of the Purchasing. PurchaseOrderLines table in the WideWorldImporters database named PurchaseOrderLine_TestIndexing.  We will run the following query against the table with different indexes on the StockItemID field and look at the results.

SELECT StockItemID
FROM [Purchasing].[PurchaseOrderLine_TestIndexing]
WHERE StockItemID = 77 

For the first run of the query, there are no indexes on the table.  Because we have no indexes, a table scan was completed to find the requested data.  That means resources were used to go through the entire table to find the records where StockItemID = 77.  This table does not have a lot of records so the impact was not as intensive as a table with a lot more records, but it is a consideration.  The Estimated Subtree Cost is 0.131745 and it took 0.121s.  The Estimated Subtree Cost is the SQL Server cost to execute the query.  The lower the Estimated Subtree Cost number, the less resource intensive the query is for SQL Server.

Noindexes

 

For the next example, we added a non-clustered index to the StockItemID field.  This time, an Index Seek was completed against the table instead of a Table Scan, the Estimated Subtree Cost was reduced to 0.005226, and the amount of time it took to run the query was reduced to 0.101s.  Since the query searched through the Index instead of the entire table, it was a more efficient use of resources to find the associated records.

NonCluseredIndex

 

For the third run of the query, we removed the non-clustered index and added a clustered index to the StockItemID field.  This time a Clustered Index Seek was done, the estimated Subtree Cost and time spent to run the query was close but just slightly higher than with a non-clustered index on the same field.

ClusteredIndex

 

The clustered and non-clustered index provided similar results for our second and third examples.  You are allowed only one clustered index per table, this might not be the appropriate field for a clustered index.  For the PurchaseOrderLine_TestIndexing table, a non-clustered index on the StockItemID field is the most appropriate type of index.

I hope this gives you a good overview of the basics of SQL Server indexes.  Over the next few weeks we will explore the different types of indexes in SQL Server and the uses for each type.

Scripting Objects in SSMS

There are a multitude of reasons why users script existing objects within SQL Server.  Depending on the reason will dictate whether you are scripting one object, a few objects, or the entire database.  There are different tools within SQL Server Management Studio (SSMS) that will help you create object scripts.

 

Single Object

To script a specific SQL object: Right click on the object -> Script [Object Type] As [CREATE To: ALTER To: DROP To: DROP And CREATE To: SELECT To: INSERT To: UPDATE To: DELETE To:] -> Location [New Query Editor Window – File – Clipboard – Agent Job]

SingleObject

 

Object Explorer

Users are able to script an entire database from the Object Explorer.  This option scripts out only the database configuration options and not specific objects within the database.

Right click the selected Database -> Script Database As -> [CREATE To: DROP To: DROP And CREATE To:]  -> [New Query Editor Window File Clipboard Agent Job]

ObjectExplorer

 

Generate Scripts Wizard

This option provides the most options for selecting what is scripted and what is included.

Right click database -> Tasks -> Generate Scripts

GeerateScriptsWizard

 

This will open the Generate and Publish Scripts wizard.  You can select to script the entire database or specific object(s) within the database (Tables, Views, Stored Procedures, User-Defined Functions, User-defined Data Types, DDL Triggers, Xml Schema Collections, Schemas, Full Text Catalog).

ChooseObjects

 

Once you have selected everything to be scripted, click Next.  This will take you to the Set Scripting Options page.  Select the Output type and where the script will be saved.

SetOptions

 

Click on the Advanced button to modify any of the advanced scripting options.  When you are done making changes to the scripting options, click on the Close button.

advancedscriptingoptions

 

Click Next to review your scripting selections.

Summary

 

Click Next to save/publish scripts.

SaveScripts

 

Click Finish to close the Wizard.

T-SQL Tuesday #115: Dear 20 Year Old Self

This month our host is Mohammad Darab (b/t) he has given us the subject ‘Dear 20 year old self’. We are supposed to write a letter to ourselves in our early twenties.  I am writing this a couple of days late because I was out of town and did not get it done for Tuesday, but I still wanted to put it out there.  I love the topic and want to thank Mohammad for hosting this month.

This is a topic that I think about at times, usually around my birthday.  It is interesting that our host posted this after his 41st birthday.  I snicker at people who think they are old when they are 41.  You have a long way to go and there are going to be so many new things in your life.  It might seem like you have lost your youth, but you are not even close.

My advice to my 20 year old self is to continue to listen to your heart, it will not lead you astray.  I have made several career and personal decisions since I was 20.  Some were easy to make, others took some deep reflection.  But when I listened to my heart, I feel like I made the right decision for me.

The second thing I would tell myself is don’t be afraid to try new things.  I am where I am today because I tried new things and moved to new places.  You don’t know if you will like something until you try it, so give it a shot.  If you don’t like it, that is ok.  You can move on.  But don’t have any regrets in the future because you did not try something when you had the chance.

One of the hardest decisions I had to make was to leave my life in Texas to move back to Colorado to be near my parents.  I had a great social life and really didn’t want to leave, but my dad was having heart surgery and I wanted to be with him.  I didn’t know how many years I would have to spend with him.  My dad lived 20 years after his heart surgery and during that time we built a better, stronger relationship.  And my friends in Texas are still my friends in Texas.  I would not change a thing because there is no way I would get that time with my dad back.

I won’t say that I have had a perfect life, and there are things I wish I would have done when I was younger like buy stock in Apple a long time ago and buy a house in Denver before the real estate boom began.  But I have some of the best people in my life that I feel that if I had changed anything earlier, these people may not be in my life right now.  And these people are very important to me…more important material items.

I See Her Success. I Want That Success. — Advancing Women

Quote

“You can’t be what you can’t see”. My job is to help bosses become aware, stop talking and start doing, to create a gender balanced and inclusive workplace. Celebrating Women is just one way of doing that.

via I See Her Success. I Want That Success. — Advancing Women

SSMS Keyboard Shortcuts – Menu Activation

short·cut: An accelerated way of doing or achieving something

Within computing, there are so many shortcuts keys that make working in a specific app or program easier.  We even talk in shortcuts…BRB, LOL, TTYL.  There are even a lot of keyboard shortcuts in SQL Server Management Studio (SSMS) that can make your job easier.

 

CTRL+N

Open a new query window

 

ALT

Move to the SQL Server Management Studio menu bar

ALT
Once on the menu bar, hit F (File), E (Edit), V (View), Q (Query), P (Project), D (Debug), T (Tools) or H (Help) to access the appropriate menu.

ALTPLUS

 

ALT+HYPHEN

Activate the menu for a tool component

ALTHyphen

 

SHIFT+F10

Display the context menu

SHIFTF10

 

CTRL+O

Display the Open File dialog box to open an existing file

CTRLO

 

CTRL+SHIFT+Q

Display the Query Designer: This is a quick and dirty way to start creating a new query.

CTRLSHIFTQ

 

CTRL+SHIFT+N

Display the New Project dialog box to create a new project

CTRLSHIFTN

 

CTRL+SHIFT+O

Display the Open Project dialog box to open an existing project

CTRLSHIFTO

 

CTRL+SHIFT+A

Display the Add New Item dialog box to add a new file to the current project

CTRLSHIFTA

 

SHIFT+ALT+A

Display the Add Existing Item dialog box to add an existing file to the current project

SHIFTALTA

 

ESC

Close a menu or dialog box, canceling the action

Undock Tabs and Windows in SSMS to Create More Workspace

With all of the tabs and windows open in SSMS, you can start running out of space to work.  Most people have multiple screens now so you can take advantage of your set-up.  Starting with SQL Server 2012, users are able to undock the tabs and windows in SSMS and move them to their other monitor, thus creating more space in the query window to work.  The more monitors you have, the more you can spread out.  The tabs and windows that can be undocked and moved outside of your SSMS instance include:

  • Bookmark Window
  • Error List
  • Object Explorer
  • Object Explorer Details Tab
  • Output
  • Properties Tab
  • Registered Servers Tab
  • Solution Explorer
  • Task List
  • Template Browser
  • Toolbox
  • Web Browser

 

Below is the ‘normal’ setup we are used to with SSMS.  Everything is tucked in nicely in our application.

OneScreen

 

When you undock the tabs and windows, you are able to create more workspace within your open query window as well as increase the size of the other windows you are using.

TwoScreens