>> There's new capabilities around
designing tables in Azure Data Studio.
Learn from Timi about all the things you need to know related to
Table Designer in Azure Data Studio this week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman and welcome
to this episode of Data Exposed.
Today I have joining us, Timi,
a Program Manager on the Azure SQL Database team.
Timi, thanks so much for joining us today.
Can you kick us off by telling us a little bit about what you do?
>> Sure, it's a pleasure to be here Anna.
My name is Timi Oshin,
and I'm a Program Manager at Microsoft,
particularly working on the SQL experiences team
within the Azure Data platform.
>> Awesome. Cool. Today we're going to be
talking about Azure Data Studio,
which is always a hot topic on Data Exposed.
We love all the innovations that you
and your team are doing in this space.
Specifically today we're going to be
talking about Table Designer,
which is something I don't know anything about.
So I'm so glad you're on the show.
Maybe before we get into it,
can you just tell us a little bit about what
Table Designer is and what it's for.
>> Yeah, of course. The main thing is before I joined Microsoft,
I actually had zero experience with databases.
I'm grateful to be on a team that's
working on creating products to
make working with databases easier and simpler.
That being said, the Table Designer is
simply a way to organize and
structure your database schema without
need for writing SQL queries or writing Transact-SQL.
If you're very new to SQL or have no experience with SQL,
it gives you an opportunity to actually do
those same things on the graphical user interface.
>> Cool, I love it.
It's going to make it easier for someone getting
started and also it might help you
even learn how these things are designed and how they work in SQL.
That's exciting. I'd love to just take a look.
I know you have some stuff planned.
>> Yeah. I'll dive right in.
First of all, I'll talk about the why of Table Designer.
It wasn't just to create parody with what's currently out there,
SSDT, which is SQL Server Database Tool.
It's really a way to create a modern visual way to
create and manage your database schema
directly on Azure Data Studio.
If you're familiar with Visual Studio code,
you will notice some similarities in the user interface.
As you can see here, you have your connection pane.
I'll show what the actual Table Designer looks like.
The first thing is you have to be connected to your server.
Right here we are connected to local host and
you traverse down that server connection and as you can see,
you have your different databases.
In this database folder,
for the purpose of this demo,
we'll be using the Adventure Works object,
which is basically a database object containing your tables.
Within Adventure Works,
you have your tables folder and as you can see,
you have all of these different tables
and I can just click on one for example.
I'm going to click on the
"Person Address," and you hit "Design."
That essentially shows you your Table Designer.
As you can see, you have three different panes.
Like I said, you have your Object Explorer
and then what you have here is
your actual main pane that allows you
to define the properties of the table.
As you can see, this is
an address table that contains your address ID,
your address line, the city,
state's code, all of that.
Then what you see here on
this right side is your table properties.
This is basically your properties
relating to each of these different columns,
basically describing the properties of the table or
the column that you've referred to.
As you can see here in the script,
you have basically a read only script.
If you didn't have any experience with Table Designer,
you would have to write SQL.
This is basically the Transact-SQL statement
of what this Table Designer is.
You can either write the SQL statement or you
can just have it here on the Table Designer just by clicking.
It provides an easy way for those who are not familiar with
SQL to still be able to manage their tables directly on this UI.
Like I said, you see here you have
these different T-SQL statements describing the table.
You have your primary keys,
you have your types, your data types,
you can basically select all of these table types.
Same thing with your spatial location,
you have this datatype here.
You can align primary keys just by clicking.
You can define your foreign keys,
which basically creates relationships between different tables.
You're able to assign constraints.
For example, if you had a table of different weights,
you don't want the value of a weight to be
negative because that's practically impossible,
unless you're in space or something but maybe not.
That's basically a way to define the constraints for your table.
You also have a way to assign
your indexes and define whether
or not you want them to be ascending or descending.
It's just very easy to create
these properties directly on the table
just by clicking and not having to write SQL statements.
>> Okay. This is cool. Quick question,
let's say I change something here,
add a new index, add a new column,
change a column, how do I save it or run it?
How do I make it work?
>> Great question. I will be going into that.
>> Oh, I'm jumping the gun.
>> No, that's actually a great question because this is one of
the very important functions on the Table Designer.
You want to make sure that you're always saving your tables.
I will go into creating
a table now and just to show you an example.
>> Cool.
>> Let's say we wanted to create a table called Employees.
You can see here you can create the table name just by typing in
the table name field and you can add your columns,
define what type of properties
you want this employee table to have.
Let's say you want name,
you want the ID of the employee.
You're going to assign your datatype to the name.
Just for the purpose of this demo,
we're going to use the nvarchar.
ID will be int,
integer, and we'll assign that as our primary key.
If we wanted to save, you have two options.
You can either do a Control S and I'll speak to
code users or you could hit this "Publish" icon,
which basically creates a SQL script or just updates the database.
For this we'll just hit the "Generate Script."
Basically what happens is we just created
a T-SQL script of that table that we just created.
You can either save this to your local machine or you could
just do a quick save here and update that database.
Now our table is saved.
That's essentially how you save a table on that.
>> Cool.
>> Next we'll be talking about
basically editing the table if you wanted to drop the table,
make different changes on this table.
To edit the table,
you can just click in the field and make changes,
whatever changes you want,
and just notice what happens in your script.
Let's say we wanted to add a new column to our table.
Let's say we wanted to add age.
Then we hit this "Publish" icon.
>> Right now, let's publish the changes.
We can see that
a new column was added to the script and I meant to type H there.
Keep up the new column was added to the script.
It's also a way for people who are
not familiar with SQL to actually see what
a SQL statement looks like and that way it's easy for them
to well transact SQL while working with the table designer.
>> Yeah. Definitely because I can see how I can be like,
"Oh, I don't know how to create a table.
Let me go through this thing."
Now I'm learning because I see,
we put the type there,
we put the name and we save it can be null or not null.
You could get a lot of learning just from
that. That's really cool.
I really hadn't seen this before.
I had one more question.
I know you probably have other things you want to show.
There's little like two bars next to age and ID,
is that rearrange the column?
>> Yes.
>> That's so cool
>> You guessed right. You can actually drag and drop.
It can seek attract that table.
Let's say we want age to be the top,
you could just drag there.
One other cool thing is you can
actually decide where you want to add a column.
It's just, say if you want to add
a new column by default it pass to the bottom.
But if you wanted to add a column
between name and ID, for example,
you could just say it's her before and it
gives you right there in between the name and your ID column.
>> It's something really simple,
but something that makes such a big difference in
actual use of table designer. I appreciate that.
>> Exactly because if you had
a large table with different properties,
you don't want to be always have to move between them.
>> Yeah.
>> You want to be able to easily
insert columns wherever you want to.
>> Cool. I think the other thing you had mentioned
is how to drop a table?
>> Yes.
>> How would you do that?
>> To drop a table,
that's done by let me resize this.
To drop a table,
you traverse down the tables folder.
You can right-click and select script as drop.
What that does is creates a transact SQL query
that you can actually run which actually drops a table.
>> Hope don't need that.
>> It gives you the confirmation that the truth table is dropped.
>> Awesome this is a great tool.
I'm excited to get my hands on this.
Any other tips and tricks you have for
folks as they're getting started with table designer?
>> Yes. The cool thing about table designer,
it actually supports different table types.
You have the system version tables,
the temporal tables, as well as the graph tables.
Before the table desire to Azure Data Studio,
you would actually have to write the T-SQL statements
to determine what type of table you're working with.
But with the table designer Azure Data Studio,
you can easily just click when
creating a table what type of table you want that to be,
let's say we wanted a graph table with easily just
while creating the table
determine what type of graph table we want,
little introduction to Graph tables.
If you're not for those who are not familiar,
think of your social media networks where you
have the different nodes or edges.
Your nodes would be your users,
and your edges would be basically the relationships
between different users on social media website.
You could make that relationship here.
Grading edge are known and as you can see in the T-SQL,
that actually is updated right there.
It gives you the confirmation that
you're actually creating a graph table.
Same thing applies to creating a system version table,
basically creating a different table that
tracks the changes made
on your original table at any point in time.
In the event of data loss or a server crowding,
you can go back to a specific point in
time where that data was lost
and basically recreate all the data
that you lost up until that very point in time.
You can do that by just clicking system versioning table
enabled and that basically creates
a table that tracks all the changes made.
The same thing with the memory optimized.
You could do that by just clicking the memory-optimized,
it supports all of these table types.
For memory optimized, you could
either determine whether you want your
schema and your data to be saved to memory or either all,
so it gives you that flexibility as well.
>> Cool, this is awesome.
I also notice it shows like any errors
that you might hit when
you're creating that, which is also helpful,
especially if you're someone new work with
system version tables or memory optimized tables,
or maybe you have the wrong skewed deployed of Azure SQL database.
It doesn't support that,
it's awesome that it has those built-in safe guards.
I guess as we wrap like as folks get started with this,
are there any words of advice you have for
them as they start playing around the table designer?
>> Yes. I will say,
first I should have given a shout out earlier to
the engineering team creating this [inaudible],
Alan, Ran, Hugh,
shout out to all you guys because right now we're trying to get
this to a stage where we are
actually able to make this generally available.
Right now it is currently in preview,
we expect a lot of issues here and there.
Basically try to get it to
a state where we're able to make this product general available.
For right now, I would say to documentation,
which is currently on Microsoft Docs,
Azure Data Studio table designer.
That would be a good place to check
for more guidance on how to work with the table designer
as well as our Azure Data Studio Twitter account
where we usually track issues on GitHub.
Yes, just basically going on our resources docs,
documentation is really good place to go
for more information on the table designer.
>> Awesome. Well, Timi thanks so much for
coming on the show. I learned a lot.
It was really cool to see hands-on and have you
interactive live and showing us how table designer works.
I'm sure our viewers did as well.
To our viewers, go ahead and give this video
a like subscribe to our channel. Leave us a comment.
Let us know what you think of table designer.
Check it out for yourself.
See the docs get hands-on,
and we hope to see you next time on Data Exposed.
[MUSIC]