Posted in T-SQL, T-SQL Tuesday

T-SQL Tuesday #136 – Favorite Data Types

Happy T-SQL Tuesday!

This month is hosted by Brent Ozar (t | b). Brent asked us talk about our favorite or least favorite data type this month. You can check out the invitation here.

I never thought about whether I had a favorite data type. I mean, I’m a DBA so naturally, my favorite data type depends on the data.

But I think I’ll “wax poetic” about datetimes and all of the varieties. When I first got started with SQL Server back in the SQL 6.5 days, we really just had datetime, which was the date and time. If you needed just the time, you ignored the date portion. If you needed just the date, you put midnight (00:00:00.000) for the time. And when you dealt with the time portion, you deal with rounding to the .003 millisecond.

Now, we have all sorts of options – datetime, smalldatetime, datetime2, datetimeoffset, date, and time. The problem is when to use each of these.

The easiest ones are date and time. Now if you just need the date portion, you just use the date data type and you don’t have to deal with midnight or ignore the time portion of the datetime. If you just need the time, again you don’t have to use some random substitute date or ignore the date portion because you can use the time data type. Dates can hold any calendar date from January 1, 0001 through December 31, 9999 and Time can hold any time from 00:00:00.0000000 through 23:59.59.9999999

It starts get tricky from here. If you have to deal with multiple time zones, datetimeoffset is going to be your best bet. But then you’re having to deal with time zones, and we know how well we deal with those…. or rather don’t deal well with them. *Checks calendar and see Daylight Savings for the US coming in a few days…*

So now we’re left with smalldatetime, datetime, and datetime2. If you’re dealing with temporal tables, SQL Server says you have to use datetime2 for the system start and end times. Otherwise datetime2 is really a combination of date (all dates) and time (all times out to nanoseconds). Smalldatetime can hold any date from January 1, 1900 through June 6, 2079 (really – June 6??) and the time portion is accurate to the second. Datetime can hold any date from January 1, 1753 through December 31, 9999 and times out to the millisecond.

The fun really starts when you realize that what SQL Server stores internally isn’t what we see. We just see a formatted date time based on the default of the system or whatever we have chosen to have the format be if we specified the format somewhere. Datetime and smalldatetime can be converted to integers, but it looks like these values can be converted to varbinary. What’s even cooler is you can start to see how these different data types are related, regardless of the formatting that gets pulled back.

Query to set the different dates to the current datetime
The results of the previous query.

Which date to choose really depends on the dates and times you need to store and how exact you need to be. You need to understand your data and what you are really trying to capture. The problem of course is when you are stuck with legacy systems so you can’t replace that datetime field with a date because that truly is all that you need.

But if I had to choose, what’s my favorite date? Well, I’ll just leave you with this:

Thanks for the great topic, Brent! Can’t wait to read what data types everyone else likes or dislikes…

Posted in SQL Server, T-SQL, T-SQL Tuesday

T-SQL Tuesday #135 – Tools of the Trade

It’s another T-SQL Tuesday! Thanks to Mikey Bronowski (t|b) for hosting. Our challenge:

I would like you to write about the tools that help you at work, those that helped you the most or were the most effective. 

Whenever there is talk of switching tools, I always here someone say, “once we are on <insert tool>, we won’t have X problem anymore.” This statement is one of my pet peeves. It’s not the tool that solves the problem. It’s making sure we implement and use the tools properly to help solve the problem. The tools themselves are just that – tools. If we don’t understand the problem, then we’re just shifting the problem to another tool.

There area so many tools that get implemented at work these days. While I use them to varying degrees, I am not always using these tools on a daily basis. And in many cases, I’m not the person responsible for setting them up. I’m a SQL Developer so my daily toolset is much smaller.

Honestly, the vast majority of my time is split between Management Studio (SSMS) or Azure Data Studio. I’m pretty simple\straightforward this way. I started playing a lot more with Azure Data Studio over the past year, but I find I’m not able to make the switch to using it full time. It really depends on the task that I need to do.

So what tasks do I do often and which tool do I use?

Continue reading “T-SQL Tuesday #135 – Tools of the Trade”
Posted in SQL Server, T-SQL

T-SQL Tuesday #134 – Give me a break

Welcome to the first T-SQL Tuesday of 2021!

2020 ended up being a year for the record books and 2021 is already making its mark. So I appreciate the topic that our host, James McGillivray (b|t), has given us.

Breaks are critical for our mental health. Write a post about relaxation techniques, dream destinations, vacation plans or anything else relating to taking a break for your own mental health.

Continue reading “T-SQL Tuesday #134 – Give me a break”
Posted in Professional Development, Speaking, SQL Server, T-SQL

T-SQL Tuesday #133 – What else have I’ve learned presenting

It’s T-SQL Tuesday! The last one of 2020 in fact so I’m glad I’m able to pull things together to contribute.

Lisa Griffin Bohm (t|b) is hosting this month. Her challenge for us is this:

This month, I’d like those of you who have presented, or written a presentation, to share something technical THAT DID NOT RELATE to the topic of the presentation, that you’ve learned in writing or giving the presentation.

This is a great topic, so thanks for hosting this month, Lisa!

Continue reading “T-SQL Tuesday #133 – What else have I’ve learned presenting”
Posted in SQL Server, T-SQL

Working With Synonyms

Don’t you love it when you find out that the little quirk you thought existed didn’t? Here I was – thinking I had this great idea for an article about it. I wrote up my test cases and proved myself wrong. So I guess we’ll chalk this up to another edition of “Me and Jon Snow – we both know nothing.”

I can still use Game of Throne references even though the show’s over, right?

At some point, I got it in my head that while you could create synonyms successfully before the object, once the object was created the synonym still wouldn’t know about the object or underlying changes. I’m not sure where I got this idea, but it’s just not true. Well, not entirely true. And I guess I was very convincing in this argument because no one corrected me at the time.

So let’s takes a look at a couple of examples to see how this works:

Continue reading “Working With Synonyms”