>> SQL Server 2022 had
the most T-SQL enhancements than previous releases.
In this episode we'll learn all about them,
see some code examples,
and learn more with Bob Ward.
This week on Data Exposed.
[MUSIC]
>> Hi. I'm Anna Hoffman.
Welcome to this episode of Data Exposed.
We are continuing our SQL Server 2022 series,
I'm joined once again by Bob Ward.
Bob, thanks for joining us. Are you getting tired of coming on?
>> Not at all. Love talking SQL Server,
love Data Exposed for sure.
>> Great. Well, we're glad to have you on.
I'm excited about this one because we're going to be
talking about T-SQL enhancement.
Do we still invest in T-SQL? What happens here?
>> Well, let's remind ourselves
we're on the final and fifth leg of the wheel of power.
Yes, Anna, here's the thing.
Developers want new features,
but they don't want to learn a new language.
You've told me that before. SQL is a very powerful language.
Why cannot take advantage of SQL Server,
putting some of that functionality
and don't force me to put in my application.
We took a look at this in SQL 2022.
We've invested in the last few releases and said,
we need to add some more into the engine,
into the T-SQL language. Consider this.
We're enhancing our support for JSON,
that's for sure, even producing JSON Object,
JSON Array, etc.
We put a lot of enhancements into the core T-SQL set of functions,
whether we're enhancing existing ones or adding new ones.
We even have bit functions in SQL,
I know I have to show you some examples of those.
Then this is interesting,
if you have time series data
that you're trying to process for your application,
maybe not a time series database,
but just time series-based data.
Like you've seen in Azure SQL Edge,
we're bringing that functionality into SQL Server 2022 itself.
There's a lot going on here as far as all these functions go,
you can go on the documentation, see each examples.
But what I thought would be cool is if I showed you some code.
>> Yeah, let's code.
>> Instead of showing you demos of this stuff Anna,
I'm going to flip over and show you,
this is a local GitHub clone of my workshop.
In the workshop, we have a module for T-SQL functions.
For example for JSON, if I looked here,
here's an example of how to produce
JSON Object based on a query to SQL Server.
One of the cool things about the exercise
is if you go through our workshop,
almost none of it require database.
You're going to run these queries against
either system table data or just a constant variables.
Not a lot of install required to try these.
I mentioned about the core SQL surface area,
things like string split, we've enhanced that.
You noticed my example here,
Anna, that in this example,
the Cowboys end up being always
the best team in the way I've actually built the string.
>> Interesting how that works.
>> I don't know why that would be the case.
But even look at this, trim functions have been enhanced.
A lot of the work we've done here, Anna,
is about trying to be compatible with ANSI standards,
but also keep up with the industry.
You see in other database systems.
You see a lot of different examples here that you can try out.
The other one is Bit functions,
I know that sounds crazy.
Anna there was a time in a place where all I did
was manipulate bits to do my job at Microsoft.
Well, you can do SQL now.
You can actually use bit functions to create
mathematical calculations or even doing
something that I call bit packing,
where you take an integer value and you
pack multiple values inside an integer value.
It's very common practice to do in say C ++ programming.
You can do it all now in T-SQL.
Pretty exciting stuff. Then finally,
I want to talk about time series.
One of the things you can do is use time series to generate
time series data that you want to see in your system.
Now this is really important.
I've talked to Kendall Van **** about this.
He's an expert in this area,
and that is what we call gap filling.
You may see telemetry data come from
a device that is going to be time series-based.
But sometimes these measurements,
these devices don't produce data during certain time intervals.
If you're trying to build a system,
how do you fill in the gaps instead of null values?
You can use functions like first value to
take the previous value and use that as your time gap value,
making an assumption that that might be
similar with the previous reading was.
A whole gamut of T-SQL functionality.
If you use our workshop,
you can go through and try all these examples yourself.
Again, you can do it on developer edition,
and you really don't even need a database to try them out.
>> This is pretty cool. Things like JSON,
Bit, Time Series.
We're investing here because we're hearing from
customers that they need this thing, they're doing this thing.
Of course we know IoT scenarios are huge right now.
Really cool to see that we're following along suit with that.
Are these capabilities going to be in Azure SQL as well?
>> That's the cool thing. This is the stuff that edged the Cloud.
It's the T-SQL language,
and this is going to show up in Azure SQL as well.
Anywhere SQL exists,
you can use this functionality.
Think about this, we know you've invested your time with T-SQL,
it's a skill you develop.
You don't want to lose that skill,
you want enhance that skill.
You don't have to build this code in your own app,
let the server process it for you
and just use the power of SQL to make it happen.
>> Love it.
>> What do you think?
>> Pretty cool. I'm excited.
I'm going to go play around in the workshop.
>> Sounds good. Let me flip back
over here and I'm just going to summarize
what we did a little bit here.
It is the most enhances by the T-SQL
than in previous three releases we've done,
so we're pretty excited about the invest we put in there.
We talked about processing JSON Data.
We have these new ANSI compatible in
industry standard core SQL functions in there,
and then finally processing time series data.
It's a pretty good gamut of investment.
I think you're going to continue to see
our team really still spend time here
because we really believe the T-SQL language is very
important in the industry and to customers.
>> Awesome. That's great to hear.
You know Bob, we've learned a lot in the series so far.
How do we get started?
Where do we get hands on?
>> We try to provide some of
the most comprehensive set of
resources we've ever done to really SQL Server product,
whether it's just looking at our docs,
getting our PowerPoint decks that we do publicly that
you can use yourself or open-source presenters as you know,
or maybe read a book. There's a new book.
>> You wrote another book.
>> I wrote another book, SQL Server 2022 Revealed, 475 pages.
If you want to take a nap on a Sunday,
I guess, and go through all this information.
We talked about the workshop,
it's all open-source, it's GitHub-based,
you can try it on your own.
It's all free, just use developer edition or eval edition of SQL.
We have mechanics videos,
we have all the data exposed series.
Also really excited to see the Microsoft learning path.
We got a learning path module very loft as well.
A lot of information and resource for you to try out.
>> Cool. If you want to learn SQL Server 2022,
this series and all these resources are a great place to start.
Bob, thanks again for joining us on the series.
To our viewers, we hope you liked this episode.
If you liked it, leave us a comment and let us know
your favorite new T-SQL function and stay tuned.
We hope to see you next time on Data Exposed.
[MUSIC]