Mike Scalise

Mike Scalise

My thoughts on SQL Server, Project Management, Fitness, and all points in between

GREATEST / LEAST Function Alternatives in SQL Server

One of the most upvoted requests on feedback.azure.com is for Microsoft to add MAX/MIN as non-aggregate functions in SQL Server. In other words, the GREATEST and LEAST functions that we have been able to find in MySQL, PostgreSQL, and Oracle for a while.

These functions are fairly simple in nature. They’ll return the highest or lowest value from a list of values or fields. In other words, the greatest or least value out of values on the same row. So it’s a bit of a mystery why something so simple and readily available in other RDBMSes hasn’t been included in SQL Server…until now…kinda.

As of 4/14/21, Microsoft has officially announced that the GREATEST and LEAST functions are in Azure SQL Database and SQL Managed Instance. Unofficially, it seems they had silently included these functions in at least (no pun intended) SQL Managed Instance several months prior. In any case, here we are today with official Microsoft documentation on GREATEST and LEAST. This is all great news. What’s also great is that, in their statement, Microsoft stated they would be including these two functions in the next version of SQL Server.

But what about all of us on SQL Server 2019 and prior? Fortunately, there’s a way to mimic these two functions in your queries using a correlated subquery in the SELECT clause.

In the simplest form with just a list of static values, you can use this syntax:

SELECT (SELECT MAX(val) FROM (VALUES (10), (30), (20), (5)) val_tbl(val)) AS Greatest,
       (SELECT MIN(val) FROM (VALUES (10), (30), (20), (5)) val_tbl(val)) AS Least;

Here’s an example querying a table of values:

SELECT t.val1,
       t.val2,
       t.val3,
       (SELECT MAX(val) FROM (VALUES (t.val1), (t.val2), (t.val3)) val_tbl(val)) AS Greatest,
       (SELECT MIN(val) FROM (VALUES (t.val1), (t.val2), (t.val3)) val_tbl(val)) AS Least
FROM   (VALUES (151,275,179),
               (64,268,240),
               (216,29,156),
               (252,271,200)) t(val1, val2, val3);

Or, if you happen to have the Stack Overflow Database, you can try this example:

SELECT p.Id,
       p.AnswerCount,
       p.CommentCount,
       (SELECT MAX(val) FROM (VALUES (p.AnswerCount), (p.CommentCount)) val_tbl(val)) AS Greatest,
       (SELECT MIN(val) FROM (VALUES (p.AnswerCount), (p.CommentCount)) val_tbl(val)) AS Least
FROM   Posts p;

While it would’ve been great to have had these functions all along, I’m happy they’re on their way and that there’s a viable alternative in the meantime.

T-SQL Tuesday #136: Blog About Your Favorite Data Type (Or Least Favorite)

T-SQL Tuesday

This month’s T-SQL Tuesday is hosted by Brent Ozar (b|t) and the topic is “Blog About Your Favorite Data Type (Or Least Favorite)”

A Little Background

For those of you who don’t know, T-SQL Tuesday is the brainchild of SQL Server Guru Adam Machancic (t). It is a monthly blog party where a person is chosen to be the blog “host” for the month, chooses a topic (can be technical or non-technical), and charges IT professionals to write about the topic on their respective blogs, then link back to the host blog.

Once the month has concluded, the host makes a roundup post comprised of links to each participant’s post. It’s a great way for people to learn a lot about a topic from incredibly knowledgeable people, and it’s wonderful for the participants as they get to contribute to the community and the greater good.

I’m sorry but you’re not my type.

I was planning to kick this T-SQL Tuesday off by writing about my favorite SQL Server data type–the VARCHAR. Yes, it has many good qualities, not the least of which is its fairly high data type precedence, allowing it to be implicitly converted to several lower-precedence data types. A real forgiving type…

But instead I’ve decided to focus on a data type that I really, really dislike–TEXT. Sure, it sounds like it would fit the bill for…well…anywhere you need to store large amounts of text….but did you know that this fun data type has been deprecated since SQL Server 2005? That’s 16 years ago. 7 SQL Server versions ago. Yet it still finds its way into modern versions of SQL Server. I mean, sure, Microsoft has announced that it will be removed in a future version (in favor of VARCHAR(MAX)), but until then, here are some fun facts about the TEXT data type:

  1. You can’t use it with the = operator. You read that correctly. The most common operator in the WHERE clause. Can’t use it. You have to use LIKE or convert the field beforehand.
  2. You can’t use it in a UNION or UNION ALL because it’s not comparable.
  3. INTERSECT? Nope.
  4. EXCEPT? No.
  5. Well surely you can sort by it, right? Haha no.

See a pattern here?

It’s not me. It’s you.

It’s easy to see the myriad limitations of the TEXT data type–which is probably why Microsoft has encouraged developers to use VARCHAR(MAX) instead. It has served its purpose for earlier versions of SQL Server, but it’s time to move on.

And honestly, I don’t like focusing on the negative as there’s enough of that going around in this world, but I can make an exception for a data type that has no place in our beloved SQL Server databases.

T-SQL Tuesday #123: Life Hacks to Make Your Day Easier

T-SQL Tuesday

This month’s T-SQL Tuesday is hosted by Jess Pomfret (b|t) and the topic is “Life hacks to make your day easier.”

A Little Background

For those of you who don’t know, T-SQL Tuesday is the brainchild of SQL Server Guru Adam Machancic (t). It is a monthly blog party where a person is chosen to be the blog “host” for the month, chooses a topic (can be technical or non-technical), and charges IT professionals to write about the topic on their respective blogs, then link back to the host blog.

Once the month has concluded, the host makes a roundup post comprised of links to each participant’s post. It’s a great way for people to learn a lot about a topic from incredibly knowledgeable people, and it’s wonderful for the participants as they get to contribute to the community and the greater good.

This 123rd T-SQL Tuesday is particularly special to me because it’s the first one I’ve participated in.

Life Hack

A life hack I learned about relatively recently is the Apple “Speak Screen” option on iPhones. This feature, which has apparently been around since the iOS 6 days, allows iPhones to speak the contents of web pages to you. How did I not know this existed?

When I first used this feature, I was blown away. My backlog of websites, blogs, and articles to read had been growing, and it seemed I would never make my way through all of the material. Now, when I’m in the shower, in the car, heck–even when I’m cleaning the house I can bring up a page I’ve been wanting to read and simply have Siri dictate it to me.

Here’s how to turn it on:

Settings –>Accessibility–>Spoken Content–>Toggle “Speak Screen” On

Or, just go to a web page ask Siri to “Speak Screen.” If it’s not enabled, it’ll give you the option to go to the aforementioned settings area and enable it; if it is enabled, it’ll speak the page to you.

There are several other related options you can set in this area, like whether text gets highlighted as it’s spoken, the rate at which text is spoken, etc.

As we all know, time is one of the few resources we simply cannot make more of, so our next best option is to be as efficient as we can with the time that we have available to us. “Speak Screen” has allowed me to significantly improve my efficiency and overall productivity. I highly recommend exploring this to see if it can help you too. If you want to do more reading on it, head over to Apple.

At The Arnold Classic, I Bench Pressed My Body Weight 25 Times

I graduated from college the first time in May 2006. I moved back home to Utica and lived with my parents for the next month while I interviewed for jobs. Interestingly enough, my first offer was for a company that also was in the Utica area. I took the opportunity to extend my stay with my parents and save some money (which was really helpful–thanks, Mom and Dad!). Aside from the financial benefit of living with my parents, I would be remiss not to mention the extensive gym my Dad had built in his garage over a couple of decades. And I don’t mean just bought equipment into a room. He actually designed and upholstered a lot of the benches and machines himself (well, ok, with the help of my Mom too). It was a really great setup.

My Dad had been working out for around four decades, and I knew he wouldn’t mind showing his son the ropes. Well over the next three years, my Dad and I turned into really great workout buddies. We knew the drill–get in the gym every other day, work out hard, get out of the gym and rest. Rinse and repeat. If nothing else we were disciplined. by 2009, I was in the best shape of my life. I had benched over 300lbs the prior year, my arms, shoulders, and chest had all grown in size. Even though my Dad was a little bit older than me, he grew quite a bit too. We worked well together.

In March 2009, we decided to go to the Arnold Sports Festival, put on by none other than Arnold Schwarzenegger. During the Expo, MHP Strong held a contest to see who could bench press their weight 25x. That’s 25 reps of your body weight, not 25x body weight ;-). My Dad encouraged me to try it. I thought, “Why not!?”

So here I am, at the ripe age of 24, weighing in at 173lbs and benching my body weight 25 times.

Mike Scalise Benching His Body Weight 25 Times

Video of How I Solved a Rubik’s Cube

I enjoy challenges. Actually, let me clarify that–I enjoy challenges that lead to a greater understanding of a subject, an increased skill set, and a sense of accomplishment. I do not like challenges just for the sake of a challenge if it makes things harder for me without any noticeable benefit.

To that end, nearly a decade ago, I thought it would be kind of cool to learn how to solve a Rubik’s cube. I believed it would challenge my brain, occupy my down time, and give me a good story. I would argue that it accomplished all three. My myriad failed attempts, studying, and eventual solution also gave me reason to create a video tutorial to share with others who were interested in tackling this puzzle.

So without further ado, here is my approach to solving the elusive Rubik’s Cube.

Mike Scalise Solving the Rubik’s Cube

Solving this puzzle not only gave me a great sense of accomplishment, but it also inspired a cake I made for a square-themed IT holiday party!

Rubik's Cube Cake
Rubik’s Cube Cake

How to Make Utica Greens: The Right Way

Utica Greens cooking on the stove

As a native Utican, I’ve tried my fair share of Utica Greens over the past 25 years, from restaurants in both the Utica and Rochester areas. I believe Georgio’s Village Cafe in New Hartford makes the best, and this is my recipe that is as close to them as you can get:

The way I made the greens is really in three separate parts (the oreganato, potatoes, and greens themselves), then I assembled them right at the end. Obviously you can adjust the recipe any way you want (e.g., if you don’t like potatoes, just skip that section).

1) The Oreganato:

1/2 cup extra-virgin olive oil
1 cup bread crumbs (I used Progresso Seasoned)
1/2 cup Parmigiano-Reggiano and/or Romano, grated (I used the Wegmans brand and got it grated)

Mix the oil, bread crumbs, and cheese until well blended. It should have the texture of moist beach sand. Set aside.

2) The Potatoes:

1 cup Yukon Gold potatoes, cut into 1” cubes
1/2 tsp. dried parsley (or 1 TBS fresh)
1/2 tsp. onion powder
1/2 tsp. garlic powder
1/2 tsp. paprika
salt
pepper

Preheat oven to 350 F. Toss in a bag with 1/4 cup oil, parsley, garlic and onion powders and paprika.

Roast for 1/2 hour. Add salt and pepper to taste. Set aside.

3) The Greens:

3-4 heads escarole
2 tbsp olive oil
1/2 chopped Vidalia onion
6 minced garlic cloves
8 oz. chicken or vegetable broth

Clean thoroughly as shown in this video

Follow this video to a tee, washing the escarole multiple times until the water is clear, then put it in a salad spinner.

In a large pan, sauté onions in 2 tbsp of olive oil until it gets a little brown (about 5 minutes). Add garlic and cook for another minute. Chopped and add escarole to the pan. Cook down (about 5 minutes). Add broth, cover, and simmer until broth is reduced (about 45 minutes). Use tongs to move escarole to a bowl. Set aside.

ASSEMBLY:

1/8 cup Parmigiano-Reggiano and/or Romano, grated
6 oz. thin sliced hard salami (optional)
3-5 thin sliced sweet or hot cherry peppers (optional)
1-2 thin sliced sweet bell peppers (optional). I used these (https://www.mezzetta.com/mm5/graphics/00000001/Deli-Sliced-Sweet-Bell-Pepper-Sandwich-Strips_lg_2.png)
banana peppers (optional)

In a large pan, combine salami, peppers, and potatoes. Cook at medium heat for 3-5 minutes. Add the greens you set aside. Add enough oreganato mix to cover the greens. Add cheese. Mix together in the pan. Transfer to a pan or baking sheet that can go in the oven and put under the broiler for about 2 minutes or until crisp. Serve.

On a side note, the oreganato recipe makes more than you’ll likely need. The idea is to mix it in good with the greens. You can always add more. Same with the potatoes. I had maybe 1/4 of the diced potatoes left over. I may have just started with a bigger potato, so adjust accordingly.

The final product:

Utica Greens served with a side of homemade garlic bread

I Received The Ellucian Outstanding Contributor Award

Today I was notified by Ellucian, a software and professional services company (primarily in higher education), that I had been awarded their Outstanding Contributor Award for my contributions to my peers and colleagues on their forums. Honestly, while I’m elated about the news, I wasn’t expecting this at all.

I have been involved with Ellucian products in some capacity for around 17 years, and as I’ve developed my skills over the years, I’ve always tried to share my knowledge with others so that they too could be great at their jobs. I never expected anything in return. I know everything I know because people were selfless enough to share and collaborate with me, so in some sense, I owe it to the community to help where I can.

In addition to a very nice letter, Ellucian also mailed me this beautiful award that I can display in my office. I’m truly grateful. Thank you to Ellucian and all of my colleagues who trusted me to help them with their questions and issues.

The Parker’s Heritage Collection from Heaven Hill Distillery

Last Updated: 06/17/2020

Why an entire post dedicated to Parker’s Heritage?

Well, when I became interested in rare whiskeys in late 2016, I came across a website that recounted the top ten rare whiskeys of the world. On that list was Parker’s Heritage 8th edition (13 Year Wheat Whiskey) from 2014. It described its color, nose, palate, and finish so eloquently that I had to try it. The problem was that it was, well, a rare whiskey. Intrigued, I began to research more about the collection and became hooked. The story, the quality, the rarity all worked for me. So if you want to know about Parker’s Heritage and my experience, read on.

Read More

PASS Summit 2016

It was amazing, to say the least.

And yes, I know I’m a few months late to the game to blog about last year’s PASS Summit, but I only recently got my blog up and running and have had a chance to compile myriad pictures I took during the conference. Whether you’re relatively new to SQL Server and are considering going or a veteran who has been multiple times, I thought I’d share my experience in hopes that you’ll glean something from it–maybe a new nugget of information.

Read More

Pages:12