Up next in 10
Working SQL code can be generated via an ORM or written directly, but most common code analysis techniques don’t help your confidence that the SQL code is “good.” Ideally, you can check the SQL code in a database or in source control for antipatterns to detect code that performs poorly, severely limits future structural changes, or doesn’t match your code formatting guidelines. There are several libraries capable of analyzing your T-SQL code and your selection will depend on your development and deployment workflows. In this session we’ll review the SqlCheck, ScriptDOM, and SQL projects code analysis options such that you can confidently add code analysis to the SQL components of your applications.
👉 Code Quality Conference 2024
https://codequalityconf.com/
📺 CSharp TV - Dev Streaming Destination http://csharp.tv
🌎 C# Corner - Community of Software and Data Developers https://www.c-sharpcorner.com/
#CSharpTV #csharpcorner #CodeQualityConf
Show More Show Less View Video Transcript
0:05
I do want to talk a bit about tsql code
0:08
I do want to talk a bit about tsql code
0:08
I do want to talk a bit about tsql code analysis um just a real quick recap I I
0:12
analysis um just a real quick recap I I
0:12
analysis um just a real quick recap I I work at Microsoft on that Azure data
0:14
work at Microsoft on that Azure data
0:14
work at Microsoft on that Azure data team uh with Anna Hoffman my my focus
0:17
team uh with Anna Hoffman my my focus
0:17
team uh with Anna Hoffman my my focus area is with SQL development um I did a
0:21
area is with SQL development um I did a
0:21
area is with SQL development um I did a a master's degree at Georgia Tech
0:23
a master's degree at Georgia Tech
0:23
a master's degree at Georgia Tech recently and some of that nerd is going
0:25
recently and some of that nerd is going
0:25
recently and some of that nerd is going to come out um today and that that'll be
0:27
to come out um today and that that'll be
0:27
to come out um today and that that'll be a lot of fun you're welcome to connect
0:28
a lot of fun you're welcome to connect
0:29
a lot of fun you're welcome to connect with me on LinkedIn especally if you
0:30
with me on LinkedIn especally if you
0:30
with me on LinkedIn especally if you have follow-up questions today I'm happy
0:31
have follow-up questions today I'm happy
0:32
have follow-up questions today I'm happy to chat recently I asked folks like are
0:34
to chat recently I asked folks like are
0:34
to chat recently I asked folks like are you looking to control or try to monitor
0:37
you looking to control or try to monitor
0:37
you looking to control or try to monitor the SQL qu code quality in your database
0:39
the SQL qu code quality in your database
0:39
the SQL qu code quality in your database and applications there were a lot of
0:41
and applications there were a lot of
0:41
and applications there were a lot of great talks today about improving the
0:45
great talks today about improving the
0:45
great talks today about improving the workflows that we have as developers
0:47
workflows that we have as developers
0:47
workflows that we have as developers whether it's what people are doing or um
0:50
whether it's what people are doing or um
0:50
whether it's what people are doing or um automation around things how do we how
0:52
automation around things how do we how
0:52
automation around things how do we how do we make sure that the applications we
0:54
do we make sure that the applications we
0:55
do we make sure that the applications we build are reliable and work
0:57
build are reliable and work
0:57
build are reliable and work well for SQL code
1:02
the the Paradigm of SQL being separate
1:06
the the Paradigm of SQL being separate
1:06
the the Paradigm of SQL being separate from the CP or the Java or the python or
1:10
from the CP or the Java or the python or
1:10
from the CP or the Java or the python or other application languages or writing
1:12
other application languages or writing
1:12
other application languages or writing it being separated kind of bites us a
1:15
it being separated kind of bites us a
1:15
it being separated kind of bites us a little bit um with with disadvantages we
1:18
little bit um with with disadvantages we
1:18
little bit um with with disadvantages we might think oh the OM is creating the
1:20
might think oh the OM is creating the
1:20
might think oh the OM is creating the database objects for me EF core does a
1:23
database objects for me EF core does a
1:23
database objects for me EF core does a great job but is it perfect do I need to
1:26
great job but is it perfect do I need to
1:26
great job but is it perfect do I need to double check um maybe someone else is
1:28
double check um maybe someone else is
1:28
double check um maybe someone else is responsible for the database there are
1:30
responsible for the database there are
1:30
responsible for the database there are dbas that are going to make sure that
1:33
dbas that are going to make sure that
1:33
dbas that are going to make sure that the the database is working well so I I
1:36
the the database is working well so I I
1:36
the the database is working well so I I don't need to worry about that and my
1:38
don't need to worry about that and my
1:38
don't need to worry about that and my personal favorite is it runs fine on my
1:40
personal favorite is it runs fine on my
1:40
personal favorite is it runs fine on my machine the query is super fast I know I
1:43
machine the query is super fast I know I
1:43
machine the query is super fast I know I know that it's going to return the right
1:44
know that it's going to return the right
1:44
know that it's going to return the right results so we're good to go maybe you're
1:47
results so we're good to go maybe you're
1:47
results so we're good to go maybe you're the only one who knows the database kind
1:49
the only one who knows the database kind
1:49
the only one who knows the database kind of back to that my code is perfect um
1:52
of back to that my code is perfect um
1:52
of back to that my code is perfect um but we can never be too perfect so if we
1:54
but we can never be too perfect so if we
1:54
but we can never be too perfect so if we think about the database is truly part
1:56
think about the database is truly part
1:56
think about the database is truly part of your application code and you you
1:58
of your application code and you you
1:58
of your application code and you you want all of your code to consistently
2:00
want all of your code to consistently
2:00
want all of your code to consistently have good quality um we don't review 500
2:04
have good quality um we don't review 500
2:04
have good quality um we don't review 500 lines of C code only to ignore the 50
2:08
lines of C code only to ignore the 50
2:08
lines of C code only to ignore the 50 lines of SQL code that go along with
2:09
lines of SQL code that go along with
2:09
lines of SQL code that go along with those store
2:11
those store
2:11
those store procedures So to that end what we're
2:14
procedures So to that end what we're
2:14
procedures So to that end what we're going to talk about for the
2:15
going to talk about for the
2:16
going to talk about for the next 40ish minutes is how can we bring
2:20
next 40ish minutes is how can we bring
2:20
next 40ish minutes is how can we bring SQL along for the ride into insuring
2:24
SQL along for the ride into insuring
2:24
SQL along for the ride into insuring code
2:25
code
2:25
code quality I've got three things to touch
2:27
quality I've got three things to touch
2:27
quality I've got three things to touch on the first one is a really cool
2:29
on the first one is a really cool
2:29
on the first one is a really cool project called SQL check the second is a
2:32
project called SQL check the second is a
2:32
project called SQL check the second is a net Library called scripto and then the
2:34
net Library called scripto and then the
2:34
net Library called scripto and then the third is a whole development Paradigm
2:37
third is a whole development Paradigm
2:37
third is a whole development Paradigm that leverages scrip Dom and provides a
2:40
that leverages scrip Dom and provides a
2:40
that leverages scrip Dom and provides a whole world of code analysis
2:43
whole world of code analysis
2:43
whole world of code analysis opportunities SQL check is a a graduate
2:47
opportunities SQL check is a a graduate
2:47
opportunities SQL check is a a graduate project of now Dr Joy rrj at Georgia
2:51
project of now Dr Joy rrj at Georgia
2:51
project of now Dr Joy rrj at Georgia Tech this is available on GitHub and
2:54
Tech this is available on GitHub and
2:54
Tech this is available on GitHub and it's database agnostic so that's a huge
2:56
it's database agnostic so that's a huge
2:56
it's database agnostic so that's a huge Advantage for
2:57
Advantage for
2:57
Advantage for it if you have SQL script like this the
3:01
it if you have SQL script like this the
3:01
it if you have SQL script like this the way SQL check works is that it is a
3:04
way SQL check works is that it is a
3:04
way SQL check works is that it is a command line interface that when you run
3:07
command line interface that when you run
3:07
command line interface that when you run it will provide some text output if I
3:11
it will provide some text output if I
3:11
it will provide some text output if I run SQL check against this create table
3:14
run SQL check against this create table
3:14
run SQL check against this create table statement it's going to give me three
3:17
statement it's going to give me three
3:17
statement it's going to give me three results in that text output the first
3:20
results in that text output the first
3:20
results in that text output the first one is there's a high risk around the
3:23
one is there's a high risk around the
3:23
one is there's a high risk around the primary key just generically being named
3:25
primary key just generically being named
3:25
primary key just generically being named ID we'll come back to that Concept in a
3:28
ID we'll come back to that Concept in a
3:28
ID we'll come back to that Concept in a minute and then the next to our more
3:31
minute and then the next to our more
3:31
minute and then the next to our more General hints around as we write queries
3:33
General hints around as we write queries
3:33
General hints around as we write queries we want to be careful in using null and
3:36
we want to be careful in using null and
3:36
we want to be careful in using null and not null
3:37
not null
3:37
not null statements as a general Practice in
3:40
statements as a general Practice in
3:41
statements as a general Practice in writing SQL code it can be really
3:42
writing SQL code it can be really
3:42
writing SQL code it can be really helpful to have a CLI like this that we
3:45
helpful to have a CLI like this that we
3:45
helpful to have a CLI like this that we can pass a SQL script and it can give us
3:47
can pass a SQL script and it can give us
3:47
can pass a SQL script and it can give us some feedback SQL check is is really
3:50
some feedback SQL check is is really
3:50
some feedback SQL check is is really cool some of the nice things about SQL
3:54
cool some of the nice things about SQL
3:54
cool some of the nice things about SQL check itself is that it's compiled from
3:56
check itself is that it's compiled from
3:56
check itself is that it's compiled from C++ so as far as the speed that it has
3:59
C++ so as far as the speed that it has
3:59
C++ so as far as the speed that it has has it's it's quite quick um this also
4:02
has it's it's quite quick um this also
4:03
has it's it's quite quick um this also means that it's crossplatform and you
4:04
means that it's crossplatform and you
4:04
means that it's crossplatform and you can compile it yourself if you have a
4:06
can compile it yourself if you have a
4:06
can compile it yourself if you have a really specific environment where you
4:08
really specific environment where you
4:09
really specific environment where you need SQL check um or your code analysis
4:11
need SQL check um or your code analysis
4:11
need SQL check um or your code analysis to be running really quickly it's it's
4:14
to be running really quickly it's it's
4:14
to be running really quickly it's it's open source and the code structure of it
4:17
open source and the code structure of it
4:17
open source and the code structure of it is in such a way that it is effectively
4:20
is in such a way that it is effectively
4:20
is in such a way that it is effectively extensible it is not extensible in the
4:23
extensible it is not extensible in the
4:23
extensible it is not extensible in the way that you write an extension for it
4:25
way that you write an extension for it
4:25
way that you write an extension for it um but is extensible in the way that if
4:27
um but is extensible in the way that if
4:27
um but is extensible in the way that if you pick up the code yourself you can
4:29
you pick up the code yourself you can
4:29
you pick up the code yourself you can write a additional
4:31
write a additional
4:31
write a additional rules the the things to be aware of when
4:34
rules the the things to be aware of when
4:34
rules the the things to be aware of when you're thinking about SQL check and
4:36
you're thinking about SQL check and
4:36
you're thinking about SQL check and passing it your your SQL scripts and
4:38
passing it your your SQL scripts and
4:38
passing it your your SQL scripts and using it is that it is based on red
4:41
using it is that it is based on red
4:41
using it is that it is based on red string
4:43
string
4:43
string matching I am not great at writing Rex
4:46
matching I am not great at writing Rex
4:46
matching I am not great at writing Rex so this commentary comes from that place
4:49
so this commentary comes from that place
4:49
so this commentary comes from that place if you are a
4:51
if you are a
4:51
if you are a Rex Master if you're an officient Auto
4:53
Rex Master if you're an officient Auto
4:53
Rex Master if you're an officient Auto or Rex maybe you can write the string
4:56
or Rex maybe you can write the string
4:56
or Rex maybe you can write the string parsing that you need to perfectly match
4:59
parsing that you need to perfectly match
4:59
parsing that you need to perfectly match individual statements but there are some
5:02
individual statements but there are some
5:02
individual statements but there are some there are some things that we want to
5:03
there are some things that we want to
5:03
there are some things that we want to watch out for with Rex parsing that
5:05
watch out for with Rex parsing that
5:05
watch out for with Rex parsing that we'll take a look at in a second the
5:07
we'll take a look at in a second the
5:07
we'll take a look at in a second the other limitation that I want to point
5:09
other limitation that I want to point
5:09
other limitation that I want to point out with SQL check is that it's
5:11
out with SQL check is that it's
5:11
out with SQL check is that it's outputting text output and so unless you
5:14
outputting text output and so unless you
5:14
outputting text output and so unless you parse that text output you can't
5:17
parse that text output you can't
5:17
parse that text output you can't necessarily do anything if you have that
5:19
necessarily do anything if you have that
5:19
necessarily do anything if you have that inside of like a a CI check for when you
5:22
inside of like a a CI check for when you
5:22
inside of like a a CI check for when you open up a PR so instead of having an API
5:25
open up a PR so instead of having an API
5:25
open up a PR so instead of having an API surface area it does do text output this
5:27
surface area it does do text output this
5:27
surface area it does do text output this makes it really easy to pick up and
5:29
makes it really easy to pick up and
5:29
makes it really easy to pick up and start using but it does present some
5:31
start using but it does present some
5:31
start using but it does present some limitations as your environment gets
5:35
limitations as your environment gets
5:35
limitations as your environment gets more tricky so if I step into VSS code
5:39
more tricky so if I step into VSS code
5:39
more tricky so if I step into VSS code here I have the the SQL check
5:42
here I have the the SQL check
5:42
here I have the the SQL check um uh tool the the whole uh source code
5:47
um uh tool the the whole uh source code
5:47
um uh tool the the whole uh source code downloaded I've built it locally here on
5:49
downloaded I've built it locally here on
5:49
downloaded I've built it locally here on my machine and so it's installed so when
5:51
my machine and so it's installed so when
5:51
my machine and so it's installed so when I run SQL check I'm going to use the C
5:54
I run SQL check I'm going to use the C
5:54
I run SQL check I'm going to use the C flag to get colorized output to make it
5:56
flag to get colorized output to make it
5:56
flag to get colorized output to make it easier to read and then I'm passing it
5:58
easier to read and then I'm passing it
5:58
easier to read and then I'm passing it the file that's open here on the top I'm
6:01
the file that's open here on the top I'm
6:01
the file that's open here on the top I'm going to expand the terminal just a bit
6:04
going to expand the terminal just a bit
6:04
going to expand the terminal just a bit and we're going to get similar results
6:06
and we're going to get similar results
6:06
and we're going to get similar results to what we saw in the PowerPoint I
6:08
to what we saw in the PowerPoint I
6:08
to what we saw in the PowerPoint I removed the uh not and all flag to
6:10
removed the uh not and all flag to
6:10
removed the uh not and all flag to simplify the results just to make this
6:12
simplify the results just to make this
6:12
simplify the results just to make this example easier to follow but I have a
6:14
example easier to follow but I have a
6:14
example easier to follow but I have a risk right now that my ID key is too
6:18
risk right now that my ID key is too
6:18
risk right now that my ID key is too vague when I need to join this table to
6:20
vague when I need to join this table to
6:20
vague when I need to join this table to another one it's going to be tough to
6:22
another one it's going to be tough to
6:22
another one it's going to be tough to track that so maybe we would want a more
6:24
track that so maybe we would want a more
6:24
track that so maybe we would want a more specific
6:26
specific
6:26
specific name being being a developer that is
6:29
name being being a developer that is
6:29
name being being a developer that is more famili with SQL server and azzure
6:30
more famili with SQL server and azzure
6:30
more famili with SQL server and azzure SQL I often times see square brackets
6:34
SQL I often times see square brackets
6:34
SQL I often times see square brackets around my column names so when I do that
6:38
around my column names so when I do that
6:38
around my column names so when I do that in this SQL script and save it and then
6:41
in this SQL script and save it and then
6:41
in this SQL script and save it and then run SQL check again I find that
6:46
run SQL check again I find that
6:46
run SQL check again I find that unfortunately this time it didn't pick
6:48
unfortunately this time it didn't pick
6:49
unfortunately this time it didn't pick up that the ID column is blank we would
6:52
up that the ID column is blank we would
6:52
up that the ID column is blank we would need to modify it to do that now we can
6:55
need to modify it to do that now we can
6:55
need to modify it to do that now we can get around
6:57
get around
6:57
get around that by now doing some re ugly things
7:00
that by now doing some re ugly things
7:00
that by now doing some re ugly things with a column name like adding spaces
7:02
with a column name like adding spaces
7:02
with a column name like adding spaces around it so the column name is
7:04
around it so the column name is
7:04
around it so the column name is technically space ID
7:07
technically space ID
7:07
technically space ID space no one's having a good
7:09
space no one's having a good
7:09
space no one's having a good time but to kind of take this example
7:13
time but to kind of take this example
7:13
time but to kind of take this example too far let me say hey the column name
7:18
too far let me say hey the column name
7:18
too far let me say hey the column name is actually ID space not space
7:24
null and I run this
7:26
null and I run this
7:26
null and I run this again now I'm getting h
7:30
again now I'm getting h
7:30
again now I'm getting h about not including null and not null
7:32
about not including null and not null
7:33
about not including null and not null Expressions when it was actually part of
7:35
Expressions when it was actually part of
7:35
Expressions when it was actually part of a column name and
7:37
a column name and
7:37
a column name and not a column specification so when we
7:40
not a column specification so when we
7:40
not a column specification so when we use reject parsing it is extremely
7:43
use reject parsing it is extremely
7:43
use reject parsing it is extremely helpful it can give
7:45
helpful it can give
7:45
helpful it can give us really good insights into our script
7:49
us really good insights into our script
7:49
us really good insights into our script so if I have notnull column it's going
7:52
so if I have notnull column it's going
7:52
so if I have notnull column it's going to correctly tell me this time you don't
7:55
to correctly tell me this time you don't
7:55
to correctly tell me this time you don't want to be using that online for it's
7:58
want to be using that online for it's
7:58
want to be using that online for it's really specific it tells us where to
8:00
really specific it tells us where to
8:00
really specific it tells us where to look it's extremely useful but it has
8:03
look it's extremely useful but it has
8:03
look it's extremely useful but it has those limitations that as your code base
8:05
those limitations that as your code base
8:05
those limitations that as your code base gets larger you might want to move past
8:09
gets larger you might want to move past
8:09
gets larger you might want to move past that so check out SQL check see if it's
8:12
that so check out SQL check see if it's
8:12
that so check out SQL check see if it's helpful for you but I do want you to
8:15
helpful for you but I do want you to
8:15
helpful for you but I do want you to know that there are additional
8:19
options if we don't do string parsing
8:23
options if we don't do string parsing
8:23
options if we don't do string parsing how do we how do we process our SQL to
8:26
how do we how do we process our SQL to
8:26
how do we how do we process our SQL to make sure that it's good we know we can
8:29
make sure that it's good we know we can
8:29
make sure that it's good we know we can throw it at a database and the database
8:32
throw it at a database and the database
8:32
throw it at a database and the database will tell us yes this is valid or no
8:34
will tell us yes this is valid or no
8:34
will tell us yes this is valid or no it's not we can do that kind of
8:36
it's not we can do that kind of
8:36
it's not we can do that kind of integration test but I want to know if
8:37
integration test but I want to know if
8:37
integration test but I want to know if it's good what do we use for
8:39
it's good what do we use for
8:39
it's good what do we use for that this is where syntax parsing comes
8:42
that this is where syntax parsing comes
8:42
that this is where syntax parsing comes in syntax parsing is a concept that
8:45
in syntax parsing is a concept that
8:45
in syntax parsing is a concept that exists across all all code all
8:48
exists across all all code all
8:48
exists across all all code all application code so C has syntax parsing
8:51
application code so C has syntax parsing
8:51
application code so C has syntax parsing that brings you intellisense and linting
8:55
that brings you intellisense and linting
8:55
that brings you intellisense and linting in the editor and the same thing exists
8:58
in the editor and the same thing exists
8:58
in the editor and the same thing exists for SQL code
9:00
for SQL code
9:00
for SQL code it gets a little bit more difficult
9:02
it gets a little bit more difficult
9:02
it gets a little bit more difficult because there are
9:03
because there are
9:03
because there are different uh flavors of SQL code there's
9:06
different uh flavors of SQL code there's
9:06
different uh flavors of SQL code there's postrest SQL and SQL server and MySQL
9:09
postrest SQL and SQL server and MySQL
9:09
postrest SQL and SQL server and MySQL but it does still
9:12
but it does still
9:12
but it does still exist why we like syntax parsing is that
9:17
exist why we like syntax parsing is that
9:17
exist why we like syntax parsing is that it can output to us an abstract syntax
9:20
it can output to us an abstract syntax
9:20
it can output to us an abstract syntax tree I promis we would nerd out a little
9:22
tree I promis we would nerd out a little
9:22
tree I promis we would nerd out a little bit today and here we go an abstract
9:25
bit today and here we go an abstract
9:25
bit today and here we go an abstract syntax tree gives us a a linked data
9:29
syntax tree gives us a a linked data
9:29
syntax tree gives us a a linked data structure or a tree as the name suggests
9:32
structure or a tree as the name suggests
9:32
structure or a tree as the name suggests of what the code represents um there's a
9:37
of what the code represents um there's a
9:37
of what the code represents um there's a there's a a SQL
9:39
there's a a SQL
9:39
there's a a SQL Explorer abstract syntax tree uh tool um
9:43
Explorer abstract syntax tree uh tool um
9:43
Explorer abstract syntax tree uh tool um that I I I use and I've linked at the
9:45
that I I I use and I've linked at the
9:45
that I I I use and I've linked at the bottom of the slide and when I pass that
9:47
bottom of the slide and when I pass that
9:47
bottom of the slide and when I pass that the example query that we were using
9:50
the example query that we were using
9:50
the example query that we were using with SQL check before it'll help me
9:53
with SQL check before it'll help me
9:53
with SQL check before it'll help me Branch out and see what are the elements
9:56
Branch out and see what are the elements
9:56
Branch out and see what are the elements of that syntax tree so it within the
9:59
of that syntax tree so it within the
9:59
of that syntax tree so it within the tree at the top level notices that we
10:02
tree at the top level notices that we
10:02
tree at the top level notices that we have two statements in our in in in our
10:06
have two statements in our in in in our
10:06
have two statements in our in in in our SQL code the first one is the first line
10:09
SQL code the first one is the first line
10:09
SQL code the first one is the first line where it says drop table if exists that
10:12
where it says drop table if exists that
10:12
where it says drop table if exists that one is collapsed in the Explorer and
10:15
one is collapsed in the Explorer and
10:15
one is collapsed in the Explorer and then I've expanded out the create table
10:17
then I've expanded out the create table
10:17
then I've expanded out the create table statement where I have a couple
10:20
statement where I have a couple
10:20
statement where I have a couple different keywords the create keyword
10:23
different keywords the create keyword
10:23
different keywords the create keyword the table keyword and then an identifier
10:25
the table keyword and then an identifier
10:25
the table keyword and then an identifier for the name so we can see where we can
10:28
for the name so we can see where we can
10:28
for the name so we can see where we can more speciic specifically access parts
10:31
more speciic specifically access parts
10:31
more speciic specifically access parts of our code in a way that we can check
10:35
of our code in a way that we can check
10:35
of our code in a way that we can check things about it so ideally we have a
10:39
things about it so ideally we have a
10:39
things about it so ideally we have a syntax parser that we can pass our code
10:41
syntax parser that we can pass our code
10:41
syntax parser that we can pass our code to and get an abstract syntax tree out
10:45
to and get an abstract syntax tree out
10:45
to and get an abstract syntax tree out that then a code analysis engine or a
10:48
that then a code analysis engine or a
10:48
that then a code analysis engine or a compiler can do better testing with and
10:51
compiler can do better testing with and
10:51
compiler can do better testing with and in this example on the right I have
10:53
in this example on the right I have
10:53
in this example on the right I have highlighted in yellow that column name
10:56
highlighted in yellow that column name
10:56
highlighted in yellow that column name for ID we could be checking that
11:00
for ID we could be checking that
11:00
for ID we could be checking that in in the world of we're looking for
11:02
in in the world of we're looking for
11:02
in in the world of we're looking for columns named
11:05
columns named
11:05
columns named ID for abstract syntax
11:08
ID for abstract syntax
11:08
ID for abstract syntax trees one way we can get them is through
11:11
trees one way we can get them is through
11:12
trees one way we can get them is through that scripton
11:13
that scripton
11:13
that scripton library now this is a net Library so for
11:16
library now this is a net Library so for
11:16
library now this is a net Library so for those of you the probably decent handful
11:19
those of you the probably decent handful
11:19
those of you the probably decent handful of you on this call that are comfortable
11:21
of you on this call that are comfortable
11:21
of you on this call that are comfortable with C development.net development this
11:24
with C development.net development this
11:24
with C development.net development this may be right up your alley um it is
11:26
may be right up your alley um it is
11:26
may be right up your alley um it is available for NET Framework it's also
11:27
available for NET Framework it's also
11:27
available for NET Framework it's also available for net 6 and8 and what this
11:30
available for net 6 and8 and what this
11:30
available for net 6 and8 and what this Library does is if you pass it one or
11:33
Library does is if you pass it one or
11:33
Library does is if you pass it one or many SQL scripts it parses them into
11:36
many SQL scripts it parses them into
11:36
many SQL scripts it parses them into their abstract syntax tree
11:38
their abstract syntax tree
11:38
their abstract syntax tree representation it represents the SQL
11:41
representation it represents the SQL
11:41
representation it represents the SQL server and Azure SQL syntax
11:44
server and Azure SQL syntax
11:44
server and Azure SQL syntax area what is especially interesting
11:47
area what is especially interesting
11:47
area what is especially interesting about it is it is not limited to just
11:51
about it is it is not limited to just
11:51
about it is it is not limited to just object scripts you can do some of the
11:53
object scripts you can do some of the
11:53
object scripts you can do some of the administrative scripts with it as well
11:55
administrative scripts with it as well
11:55
administrative scripts with it as well so if you're in an environment where
11:57
so if you're in an environment where
11:57
so if you're in an environment where you're worried about um
12:00
you're worried about um
12:00
you're worried about um metadata or management of SQL you can
12:03
metadata or management of SQL you can
12:03
metadata or management of SQL you can also use scrip Doom to keep an eye on
12:05
also use scrip Doom to keep an eye on
12:05
also use scrip Doom to keep an eye on those
12:06
those
12:06
those scripts something that I'm very proud of
12:08
scripts something that I'm very proud of
12:08
scripts something that I'm very proud of the team for is that we did open source
12:10
the team for is that we did open source
12:10
the team for is that we did open source scripton back in 2023 and so it's
12:13
scripton back in 2023 and so it's
12:13
scripton back in 2023 and so it's available on GitHub both for you to kind
12:15
available on GitHub both for you to kind
12:15
available on GitHub both for you to kind of take a look at but also interact with
12:17
of take a look at but also interact with
12:17
of take a look at but also interact with the
12:18
the
12:18
the team if I have a SQL parser like script
12:22
team if I have a SQL parser like script
12:22
team if I have a SQL parser like script down what what do I do with it how do I
12:24
down what what do I do with it how do I
12:24
down what what do I do with it how do I use this library to get more information
12:28
use this library to get more information
12:28
use this library to get more information out of my code for that I have a quick
12:32
out of my code for that I have a quick
12:32
out of my code for that I have a quick example we're going to take a file um
12:34
example we're going to take a file um
12:34
example we're going to take a file um we're going to go into this code here
12:36
we're going to go into this code here
12:36
we're going to go into this code here that's on the screen and we're going to
12:38
that's on the screen and we're going to
12:38
that's on the screen and we're going to take a look at a query and we're going
12:40
take a look at a query and we're going
12:40
take a look at a query and we're going to check it for two things first I want
12:42
to check it for two things first I want
12:42
to check it for two things first I want to make sure that the developers on my
12:44
to make sure that the developers on my
12:44
to make sure that the developers on my team aren't doing select star if we have
12:48
team aren't doing select star if we have
12:48
team aren't doing select star if we have um if we have components in the code we
12:50
um if we have components in the code we
12:50
um if we have components in the code we want to make sure that we're not getting
12:52
want to make sure that we're not getting
12:52
want to make sure that we're not getting any unexpected columns or we're losing
12:54
any unexpected columns or we're losing
12:55
any unexpected columns or we're losing columns which can happen if you do
12:56
columns which can happen if you do
12:56
columns which can happen if you do select star and the the objects
12:58
select star and the the objects
12:58
select star and the the objects underneath the query change so I want to
13:01
underneath the query change so I want to
13:01
underneath the query change so I want to avoid select star I need to be able to
13:02
avoid select star I need to be able to
13:03
avoid select star I need to be able to catch that the other one is the no lock
13:05
catch that the other one is the no lock
13:05
catch that the other one is the no lock hint on tables can kind get used like
13:08
hint on tables can kind get used like
13:08
hint on tables can kind get used like finger painting um in queries by
13:10
finger painting um in queries by
13:10
finger painting um in queries by well-intentioned developers I also want
13:13
well-intentioned developers I also want
13:13
well-intentioned developers I also want to avoid that from getting checked into
13:15
to avoid that from getting checked into
13:15
to avoid that from getting checked into code and so I want to go ahead and be
13:16
code and so I want to go ahead and be
13:16
code and so I want to go ahead and be able to catch both of those anytime a
13:18
able to catch both of those anytime a
13:18
able to catch both of those anytime a SQL script gets
13:20
SQL script gets
13:20
SQL script gets hared so if I go back over to vs
13:27
code I'm going to start with a query
13:29
code I'm going to start with a query
13:29
code I'm going to start with a query here it is parsing out a a table um it's
13:34
here it is parsing out a a table um it's
13:34
here it is parsing out a a table um it's just a sample query where I've declared
13:36
just a sample query where I've declared
13:36
just a sample query where I've declared the current year is
13:38
the current year is
13:38
the current year is 2024 and I'm selecting a bunch of
13:41
2024 and I'm selecting a bunch of
13:41
2024 and I'm selecting a bunch of columns from a table including select
13:43
columns from a table including select
13:43
columns from a table including select star on the end because I just want to
13:45
star on the end because I just want to
13:45
star on the end because I just want to be double sure that I'm getting all
13:46
be double sure that I'm getting all
13:46
be double sure that I'm getting all these columns this is this is not a
13:48
these columns this is this is not a
13:48
these columns this is this is not a pretty
13:50
pretty
13:50
pretty query the the project itself is just a
13:55
query the the project itself is just a
13:55
query the the project itself is just a plal vanilla. net console example but I
13:59
plal vanilla. net console example but I
13:59
plal vanilla. net console example but I have picked up the package reference for
14:02
have picked up the package reference for
14:02
have picked up the package reference for scripton this is this is the the
14:05
scripton this is this is the the
14:05
scripton this is this is the the important part where if I want to
14:06
important part where if I want to
14:06
important part where if I want to leverage that Library I need the package
14:08
leverage that Library I need the package
14:08
leverage that Library I need the package reference and now in my toy proof of
14:12
reference and now in my toy proof of
14:12
reference and now in my toy proof of concept here we have a little bit of
14:14
concept here we have a little bit of
14:14
concept here we have a little bit of code where in the the main method I'm
14:18
code where in the the main method I'm
14:18
code where in the the main method I'm going to ask for a file path and when I
14:21
going to ask for a file path and when I
14:21
going to ask for a file path and when I open up that file path we're going to
14:23
open up that file path we're going to
14:23
open up that file path we're going to use the surface area the API surface
14:25
use the surface area the API surface
14:26
use the surface area the API surface area of scrip Dom to do a few things the
14:29
area of scrip Dom to do a few things the
14:29
area of scrip Dom to do a few things the first thing is we're going to declare a
14:32
first thing is we're going to declare a
14:32
first thing is we're going to declare a parser and this parser is for tsql 160
14:37
parser and this parser is for tsql 160
14:37
parser and this parser is for tsql 160 if you're not familiar with the SQL
14:39
if you're not familiar with the SQL
14:39
if you're not familiar with the SQL versioning um it's a little bit weird um
14:44
versioning um it's a little bit weird um
14:44
versioning um it's a little bit weird um and tsql 160 represents the SQL 2022
14:49
and tsql 160 represents the SQL 2022
14:49
and tsql 160 represents the SQL 2022 surface area so I'm saying I want to
14:51
surface area so I'm saying I want to
14:51
surface area so I'm saying I want to make sure that this code is valid for
14:52
make sure that this code is valid for
14:52
make sure that this code is valid for SQL 2022 I could change it back to SQL
14:57
SQL 2022 I could change it back to SQL
14:57
SQL 2022 I could change it back to SQL 2019 and make sure that I'm not suddenly
14:59
2019 and make sure that I'm not suddenly
14:59
2019 and make sure that I'm not suddenly getting things that aren't
15:01
getting things that aren't
15:01
getting things that aren't supported when I in the next line line
15:05
supported when I in the next line line
15:05
supported when I in the next line line 26 go ahead and
15:07
26 go ahead and
15:07
26 go ahead and parse the the file that's been read in
15:10
parse the the file that's been read in
15:10
parse the the file that's been read in the first thing script Dom is going to
15:11
the first thing script Dom is going to
15:11
the first thing script Dom is going to tell me is whether or not it can be
15:13
tell me is whether or not it can be
15:13
tell me is whether or not it can be parsed to a valid syntax treat so at the
15:17
parsed to a valid syntax treat so at the
15:17
parsed to a valid syntax treat so at the right off the bat I'm already getting
15:19
right off the bat I'm already getting
15:19
right off the bat I'm already getting error checking so I could output
15:22
error checking so I could output
15:22
error checking so I could output those because I know that my SQL is
15:24
those because I know that my SQL is
15:24
those because I know that my SQL is always nearly perfect um I I don't
15:27
always nearly perfect um I I don't
15:27
always nearly perfect um I I don't expect to have to use that catch block
15:29
expect to have to use that catch block
15:29
expect to have to use that catch block today but I am going to do something
15:33
today but I am going to do something
15:33
today but I am going to do something called a visitor um a visitor pattern
15:37
called a visitor um a visitor pattern
15:37
called a visitor um a visitor pattern where tsql fragments that are found in
15:40
where tsql fragments that are found in
15:40
where tsql fragments that are found in the syntax tree can accept where we see
15:44
the syntax tree can accept where we see
15:44
the syntax tree can accept where we see tree. accept can accept overrides to a
15:49
tree. accept can accept overrides to a
15:49
tree. accept can accept overrides to a visit
15:51
visit
15:51
visit method what this looks like is when Tre
15:54
method what this looks like is when Tre
15:54
method what this looks like is when Tre do accept is
15:57
do accept is
15:57
do accept is called each
15:59
called each
15:59
called each statement is checked for whether or not
16:02
statement is checked for whether or not
16:02
statement is checked for whether or not it's going to be able to accept that
16:04
it's going to be able to accept that
16:04
it's going to be able to accept that visitor the select star visitor can only
16:08
visitor the select star visitor can only
16:08
visitor the select star visitor can only be accepted by select star expression so
16:12
be accepted by select star expression so
16:12
be accepted by select star expression so there are different classes for a
16:14
there are different classes for a
16:14
there are different classes for a different kinds of objects in that
16:17
different kinds of objects in that
16:17
different kinds of objects in that syntax tree one of them is the select
16:20
syntax tree one of them is the select
16:20
syntax tree one of them is the select star expression another is a table hint
16:24
star expression another is a table hint
16:24
star expression another is a table hint which represents uh the with no lock
16:26
which represents uh the with no lock
16:26
which represents uh the with no lock Clause we could do this for a column
16:29
Clause we could do this for a column
16:29
Clause we could do this for a column name or a table or an index there's all
16:32
name or a table or an index there's all
16:32
name or a table or an index there's all kinds of different classes that you can
16:33
kinds of different classes that you can
16:33
kinds of different classes that you can find in the Knight API
16:37
documentation in this example when I run
16:40
documentation in this example when I run
16:40
documentation in this example when I run it in a moment we're going to be passing
16:42
it in a moment we're going to be passing
16:42
it in a moment we're going to be passing the abstract syntax tree for that query
16:45
the abstract syntax tree for that query
16:45
the abstract syntax tree for that query to a select star visitor that's going to
16:46
to a select star visitor that's going to
16:46
to a select star visitor that's going to tell us whether or not finds a select
16:48
tell us whether or not finds a select
16:48
tell us whether or not finds a select star expression and then we're also
16:50
star expression and then we're also
16:50
star expression and then we're also going to pass in the nolock
16:52
going to pass in the nolock
16:52
going to pass in the nolock visitor the select star visitor is the
16:55
visitor the select star visitor is the
16:55
visitor the select star visitor is the most barebones one possible where it's
16:57
most barebones one possible where it's
16:57
most barebones one possible where it's just only to be accepted by select star
17:00
just only to be accepted by select star
17:00
just only to be accepted by select star expressions and then it's going to
17:01
expressions and then it's going to
17:01
expressions and then it's going to Output um a line you can do more complex
17:06
Output um a line you can do more complex
17:06
Output um a line you can do more complex operations in these visitors um not only
17:09
operations in these visitors um not only
17:09
operations in these visitors um not only can you um manage kind of control Flow
17:12
can you um manage kind of control Flow
17:12
can you um manage kind of control Flow by saying Hey I want to check the hint
17:14
by saying Hey I want to check the hint
17:14
by saying Hey I want to check the hint kind and make sure it's a no lock table
17:16
kind and make sure it's a no lock table
17:16
kind and make sure it's a no lock table hint but you can declare objects and
17:19
hint but you can declare objects and
17:19
hint but you can declare objects and accumulate information like you can only
17:23
accumulate information like you can only
17:23
accumulate information like you can only have one no lock you can't have five
17:26
have one no lock you can't have five
17:26
have one no lock you can't have five within a single query then I know you're
17:27
within a single query then I know you're
17:27
within a single query then I know you're really not paying attention you can do
17:29
really not paying attention you can do
17:29
really not paying attention you can do all kinds of things like that keep in
17:32
all kinds of things like that keep in
17:32
all kinds of things like that keep in mind right now this query does not have
17:36
mind right now this query does not have
17:36
mind right now this query does not have a with no lock claws so we should expect
17:38
a with no lock claws so we should expect
17:38
a with no lock claws so we should expect to see that it pared the query correctly
17:40
to see that it pared the query correctly
17:40
to see that it pared the query correctly and that it finds a select star
17:44
expression and let me go ahead and copy
17:47
expression and let me go ahead and copy
17:47
expression and let me go ahead and copy this path real
17:50
this path real
17:50
this path real quick do net run and then when it asks
17:53
quick do net run and then when it asks
17:53
quick do net run and then when it asks me for the file name in a moment I'll
17:56
me for the file name in a moment I'll
17:56
me for the file name in a moment I'll give it that file path
18:02
so it said no errors found but it did
18:04
so it said no errors found but it did
18:04
so it said no errors found but it did find a select star
18:07
find a select star
18:07
find a select star expression if I do with no
18:12
expression if I do with no
18:12
expression if I do with no lock it's going
18:16
to find a no lock
18:19
to find a no lock
18:19
to find a no lock hint now here's the thing that I
18:21
hint now here's the thing that I
18:21
hint now here's the thing that I hammered home earlier the very specific
18:24
hammered home earlier the very specific
18:24
hammered home earlier the very specific syntax
18:26
syntax
18:26
syntax matters when you are doing Rex parsing
18:30
matters when you are doing Rex parsing
18:30
matters when you are doing Rex parsing but what about
18:34
for syntax parsing when I'm not doing
18:37
for syntax parsing when I'm not doing
18:37
for syntax parsing when I'm not doing string parsing but when I'm doing syntax
18:38
string parsing but when I'm doing syntax
18:38
string parsing but when I'm doing syntax parsing now I can kind of mangle this
18:42
parsing now I can kind of mangle this
18:42
parsing now I can kind of mangle this nolock table hint up there's a number of
18:44
nolock table hint up there's a number of
18:44
nolock table hint up there's a number of different ways that are technically
18:50
accessible and I'm going to run it again
18:52
accessible and I'm going to run it again
18:53
accessible and I'm going to run it again and so I've removed the with but it
18:54
and so I've removed the with but it
18:55
and so I've removed the with but it still says no luck which is a valid
18:56
still says no luck which is a valid
18:56
still says no luck which is a valid table hint
19:01
and it did not find it that time so what
19:03
and it did not find it that time so what
19:03
and it did not find it that time so what did I mess
19:10
up I love it when things don't work like
19:12
up I love it when things don't work like
19:12
up I love it when things don't work like I expect that was awesome there we go so
19:15
I expect that was awesome there we go so
19:15
I expect that was awesome there we go so I had broken the table Hint by not
19:16
I had broken the table Hint by not
19:16
I had broken the table Hint by not including the parentheses around my list
19:18
including the parentheses around my list
19:18
including the parentheses around my list of table hints once I put the
19:20
of table hints once I put the
19:20
of table hints once I put the parentheses back we were good now I have
19:22
parentheses back we were good now I have
19:22
parentheses back we were good now I have to go file a bug that it didn't throw on
19:25
to go file a bug that it didn't throw on
19:25
to go file a bug that it didn't throw on the no lock you get more robust syntax
19:29
the no lock you get more robust syntax
19:29
the no lock you get more robust syntax parsing when you use a syntax parser one
19:32
parsing when you use a syntax parser one
19:32
parsing when you use a syntax parser one of the reasons why we wanted to open
19:34
of the reasons why we wanted to open
19:34
of the reasons why we wanted to open source scripton was that the surface
19:36
source scripton was that the surface
19:36
source scripton was that the surface area for SQL is always changing and we
19:39
area for SQL is always changing and we
19:39
area for SQL is always changing and we wanted to be more open to people
19:40
wanted to be more open to people
19:40
wanted to be more open to people bringing us issues that they find um
19:44
bringing us issues that they find um
19:44
bringing us issues that they find um yeah Jake is helping me out in the chat
19:46
yeah Jake is helping me out in the chat
19:46
yeah Jake is helping me out in the chat and reminding me that Nock is a valid
19:48
and reminding me that Nock is a valid
19:48
and reminding me that Nock is a valid Alias so it should have been okay
19:51
Alias so it should have been okay
19:51
Alias so it should have been okay without those uh parentheses so I get to
19:53
without those uh parentheses so I get to
19:53
without those uh parentheses so I get to go work on that later this
19:55
go work on that later this
19:55
go work on that later this afternoon so when we use scripto we able
19:59
afternoon so when we use scripto we able
19:59
afternoon so when we use scripto we able to use
20:01
to use
20:01
to use visitor patterns to catch different
20:05
visitor patterns to catch different
20:05
visitor patterns to catch different parts of the syntax tree and then react
20:07
parts of the syntax tree and then react
20:07
parts of the syntax tree and then react to them you can do more extensive things
20:11
to them you can do more extensive things
20:11
to them you can do more extensive things one example that I've seen more recently
20:14
one example that I've seen more recently
20:14
one example that I've seen more recently was taking um the SP execute SQL um
20:21
was taking um the SP execute SQL um
20:21
was taking um the SP execute SQL um expression where you you using SQL to
20:23
expression where you you using SQL to
20:23
expression where you you using SQL to execute Dynamic SQL and find what is
20:26
execute Dynamic SQL and find what is
20:26
execute Dynamic SQL and find what is actually being executed within that
20:29
actually being executed within that
20:29
actually being executed within that without having to run it um that is a
20:31
without having to run it um that is a
20:31
without having to run it um that is a another way of using the syntax tree and
20:34
another way of using the syntax tree and
20:34
another way of using the syntax tree and then kind of decompiling it from
20:37
then kind of decompiling it from
20:37
then kind of decompiling it from there let's head back over to our
20:42
there let's head back over to our
20:42
there let's head back over to our example
20:44
example
20:44
example with with our kind of quest towards code
20:49
with with our kind of quest towards code
20:49
with with our kind of quest towards code quality we know that we want code
20:52
quality we know that we want code
20:52
quality we know that we want code analysis code quality to be a part of
20:55
analysis code quality to be a part of
20:55
analysis code quality to be a part of our process and that's where I
20:58
our process and that's where I
20:58
our process and that's where I introduced uce CI automation as just
21:01
introduced uce CI automation as just
21:01
introduced uce CI automation as just something to keep in the back of our
21:02
something to keep in the back of our
21:02
something to keep in the back of our minds um today we are not going to go
21:05
minds um today we are not going to go
21:05
minds um today we are not going to go build out a a PR check pipeline but what
21:09
build out a a PR check pipeline but what
21:09
build out a a PR check pipeline but what I want us to think about is when I check
21:12
I want us to think about is when I check
21:12
I want us to think about is when I check some code in we do a few things like
21:15
some code in we do a few things like
21:15
some code in we do a few things like making sure that it can build sometimes
21:18
making sure that it can build sometimes
21:18
making sure that it can build sometimes we are using unit tests and net test or
21:21
we are using unit tests and net test or
21:21
we are using unit tests and net test or potentially we're generating migrations
21:23
potentially we're generating migrations
21:23
potentially we're generating migrations with ef migrations there's all of these
21:26
with ef migrations there's all of these
21:26
with ef migrations there's all of these pretty common things that know need to
21:29
pretty common things that know need to
21:29
pretty common things that know need to happen in the SQL world we do have one
21:33
happen in the SQL world we do have one
21:33
happen in the SQL world we do have one thing that's analogous to that and
21:35
thing that's analogous to that and
21:35
thing that's analogous to that and that's the concept of a SQL databased
21:37
that's the concept of a SQL databased
21:37
that's the concept of a SQL databased project these are a little bit of a
21:40
project these are a little bit of a
21:40
project these are a little bit of a divisive topic sometimes because the
21:43
divisive topic sometimes because the
21:43
divisive topic sometimes because the most common way that they are used is in
21:46
most common way that they are used is in
21:46
most common way that they are used is in the forward Direction Where You're
21:48
the forward Direction Where You're
21:48
the forward Direction Where You're Building your database objects
21:49
Building your database objects
21:49
Building your database objects specifically in a database project
21:51
specifically in a database project
21:51
specifically in a database project either in Visual Studio or VSS code um
21:55
either in Visual Studio or VSS code um
21:55
either in Visual Studio or VSS code um instead of using an OM like EF core to
21:58
instead of using an OM like EF core to
21:58
instead of using an OM like EF core to generate your objects for you this is
22:01
generate your objects for you this is
22:01
generate your objects for you this is not to say that you can't use them in
22:04
not to say that you can't use them in
22:04
not to say that you can't use them in conjunction with an OM it just is not a
22:06
conjunction with an OM it just is not a
22:06
conjunction with an OM it just is not a very common pattern to be honest but the
22:09
very common pattern to be honest but the
22:09
very common pattern to be honest but the database projects have similar to C
22:12
database projects have similar to C
22:12
database projects have similar to C projects a build process where then they
22:15
projects a build process where then they
22:15
projects a build process where then they have a build artifact which is a DAC
22:17
have a build artifact which is a DAC
22:17
have a build artifact which is a DAC pack so now for SQL we have two kind of
22:21
pack so now for SQL we have two kind of
22:22
pack so now for SQL we have two kind of tools in the back of our tool belt we
22:23
tools in the back of our tool belt we
22:23
tools in the back of our tool belt we have the script syntax parting library
22:26
have the script syntax parting library
22:26
have the script syntax parting library but we also have a data databased
22:28
but we also have a data databased
22:29
but we also have a data databased project that has a build process so can
22:32
project that has a build process so can
22:32
project that has a build process so can we link our ability to parse scripts
22:36
we link our ability to parse scripts
22:36
we link our ability to parse scripts with the fact that we have a a a process
22:41
with the fact that we have a a a process
22:41
with the fact that we have a a a process where we're going to be building and
22:43
where we're going to be building and
22:43
where we're going to be building and validating our database objects when a
22:46
validating our database objects when a
22:46
validating our database objects when a database object builds or when a
22:48
database object builds or when a
22:48
database object builds or when a database project builds it looks at all
22:51
database project builds it looks at all
22:51
database project builds it looks at all of the objects in the the scripts that
22:54
of the objects in the the scripts that
22:54
of the objects in the the scripts that are defined as a part of that project
22:55
are defined as a part of that project
22:56
are defined as a part of that project and validates their relationships it
22:57
and validates their relationships it
22:58
and validates their relationships it makes sure that if a table has a foreign
23:00
makes sure that if a table has a foreign
23:00
makes sure that if a table has a foreign key to another table that that table
23:02
key to another table that that table
23:02
key to another table that that table actually exists that that foreign key
23:03
actually exists that that foreign key
23:03
actually exists that that foreign key actually exists for example or a stored
23:05
actually exists for example or a stored
23:05
actually exists for example or a stored procedure if it's calling to columns in
23:08
procedure if it's calling to columns in
23:08
procedure if it's calling to columns in a table do those columns actually exist
23:11
a table do those columns actually exist
23:11
a table do those columns actually exist that kind of check is happening can we
23:13
that kind of check is happening can we
23:14
that kind of check is happening can we extend this to making sure that the code
23:17
extend this to making sure that the code
23:17
extend this to making sure that the code itself is not just valid but that it is
23:22
itself is not just valid but that it is
23:22
itself is not just valid but that it is good and this is where code analysis can
23:25
good and this is where code analysis can
23:25
good and this is where code analysis can be enabled on SQL projects and take us
23:28
be enabled on SQL projects and take us
23:28
be enabled on SQL projects and take us that Next Step um the the term that
23:31
that Next Step um the the term that
23:31
that Next Step um the the term that sometimes used in the code analysis
23:33
sometimes used in the code analysis
23:33
sometimes used in the code analysis world is anti- patterns um I I hesitated
23:37
world is anti- patterns um I I hesitated
23:37
world is anti- patterns um I I hesitated to put the term bad code on this slide
23:40
to put the term bad code on this slide
23:40
to put the term bad code on this slide because some anti-patterns you have to
23:43
because some anti-patterns you have to
23:43
because some anti-patterns you have to do them there's not a better way around
23:45
do them there's not a better way around
23:45
do them there's not a better way around it but you at least want to know that
23:47
it but you at least want to know that
23:47
it but you at least want to know that you have done this and that is why when
23:51
you have done this and that is why when
23:51
you have done this and that is why when you enable code analysis rules you can
23:55
you enable code analysis rules you can
23:55
you enable code analysis rules you can go two different routes um and for
23:58
go two different routes um and for
23:58
go two different routes um and for specific spefic rules which we'll talk
23:59
specific spefic rules which we'll talk
23:59
specific spefic rules which we'll talk about in a second you can either go to a
24:02
about in a second you can either go to a
24:02
about in a second you can either go to a a feedback to the developer where it's
24:04
a feedback to the developer where it's
24:04
a feedback to the developer where it's just a build warning we're familiar with
24:06
just a build warning we're familiar with
24:06
just a build warning we're familiar with build warnings developing with C or you
24:09
build warnings developing with C or you
24:09
build warnings developing with C or you can go all the way to a build
24:12
can go all the way to a build
24:12
can go all the way to a build error let's say you absolutely ban
24:17
error let's say you absolutely ban
24:17
error let's say you absolutely ban nullable
24:18
nullable
24:18
nullable columns you can block Builds on an error
24:22
columns you can block Builds on an error
24:22
columns you can block Builds on an error if a nullable column is detected so that
24:26
if a nullable column is detected so that
24:26
if a nullable column is detected so that is an option as far as code analysis and
24:30
is an option as far as code analysis and
24:30
is an option as far as code analysis and kind of just getting running with it
24:32
kind of just getting running with it
24:32
kind of just getting running with it today if you have a SQL project there
24:35
today if you have a SQL project there
24:35
today if you have a SQL project there are out of the box rules um in Visual
24:38
are out of the box rules um in Visual
24:38
are out of the box rules um in Visual Studio there's the nice project
24:41
Studio there's the nice project
24:41
Studio there's the nice project properties Pane and in the the project
24:44
properties Pane and in the the project
24:44
properties Pane and in the the project properties pane you have um the the code
24:49
properties pane you have um the the code
24:49
properties pane you have um the the code analysis tab where you can checkbox to
24:52
analysis tab where you can checkbox to
24:52
analysis tab where you can checkbox to enable code analysis on build this sets
24:56
enable code analysis on build this sets
24:56
enable code analysis on build this sets the project file prop for run SQL code
24:59
the project file prop for run SQL code
24:59
the project file prop for run SQL code analysis to
25:01
analysis to
25:01
analysis to true and that just gets it going by
25:05
true and that just gets it going by
25:05
true and that just gets it going by default the out of thee box rules are
25:07
default the out of thee box rules are
25:07
default the out of thee box rules are set as warnings so you're not going to
25:09
set as warnings so you're not going to
25:09
set as warnings so you're not going to break your build um by doing that first
25:16
break your build um by doing that first
25:16
break your build um by doing that first check you can enable and disable
25:19
check you can enable and disable
25:19
check you can enable and disable specific rules out of the box there's
25:21
specific rules out of the box there's
25:21
specific rules out of the box there's about 15 of them um there's some more
25:25
about 15 of them um there's some more
25:25
about 15 of them um there's some more around database design so that's select
25:28
around database design so that's select
25:28
around database design so that's select star example we hammered on earlier um
25:31
star example we hammered on earlier um
25:31
star example we hammered on earlier um you can start getting picky about how
25:33
you can start getting picky about how
25:33
you can start getting picky about how objects are
25:35
objects are
25:35
objects are named um perhaps avoiding using reserved
25:38
named um perhaps avoiding using reserved
25:38
named um perhaps avoiding using reserved words for type names things like that
25:41
words for type names things like that
25:41
words for type names things like that and then last but not least there's a
25:42
and then last but not least there's a
25:42
and then last but not least there's a whole performance category where we make
25:45
whole performance category where we make
25:45
whole performance category where we make some suggestions around how to improve
25:48
some suggestions around how to improve
25:48
some suggestions around how to improve your performance um so if you were
25:50
your performance um so if you were
25:50
your performance um so if you were coming today hoping to find out how do I
25:52
coming today hoping to find out how do I
25:52
coming today hoping to find out how do I write really good performance SQL code
25:55
write really good performance SQL code
25:55
write really good performance SQL code ay I am not the person that's going to
25:57
ay I am not the person that's going to
25:57
ay I am not the person that's going to make those final recommendations for you
26:00
make those final recommendations for you
26:00
make those final recommendations for you but here's tooling that while there are
26:03
but here's tooling that while there are
26:03
but here's tooling that while there are a handful of rules here in a moment
26:06
a handful of rules here in a moment
26:06
a handful of rules here in a moment we're going to talk about how can we go
26:08
we're going to talk about how can we go
26:08
we're going to talk about how can we go further on these
26:10
further on these
26:10
further on these rules so if I've enabled some rules and
26:14
rules so if I've enabled some rules and
26:14
rules so if I've enabled some rules and then the um specific ones that we want
26:17
then the um specific ones that we want
26:17
then the um specific ones that we want to serve as warnings or as errors
26:20
to serve as warnings or as errors
26:20
to serve as warnings or as errors instead of warnings when we check that
26:23
instead of warnings when we check that
26:23
instead of warnings when we check that we will also get a project file property
26:27
we will also get a project file property
26:27
we will also get a project file property that has that
26:29
that has that
26:29
that has that um exclamation point and then the rule
26:32
um exclamation point and then the rule
26:32
um exclamation point and then the rule name as you look at that rule name it is
26:35
name as you look at that rule name it is
26:35
name as you look at that rule name it is not a mistake if you're thinking geez
26:38
not a mistake if you're thinking geez
26:38
not a mistake if you're thinking geez that sounds a lot like a class in a
26:41
that sounds a lot like a class in a
26:42
that sounds a lot like a class in a library because in fact code analysis
26:45
library because in fact code analysis
26:45
library because in fact code analysis rules are a library and it is
26:49
rules are a library and it is
26:49
rules are a library and it is extensible the way that the code
26:51
extensible the way that the code
26:51
extensible the way that the code analysis engine works is that it will
26:55
analysis engine works is that it will
26:55
analysis engine works is that it will load up all of the rules that it can
26:57
load up all of the rules that it can
26:57
load up all of the rules that it can find
26:59
find
27:00
find which is a very weird way to phrase
27:01
which is a very weird way to phrase
27:01
which is a very weird way to phrase things I understand I promise I'll come
27:03
things I understand I promise I'll come
27:03
things I understand I promise I'll come back to the idea of the code analysis
27:05
back to the idea of the code analysis
27:05
back to the idea of the code analysis engine finding
27:06
engine finding
27:06
engine finding rules when you are running code analysis
27:09
rules when you are running code analysis
27:09
rules when you are running code analysis rules like during a SQL project build
27:12
rules like during a SQL project build
27:12
rules like during a SQL project build you can incorporate um rules either from
27:15
you can incorporate um rules either from
27:15
you can incorporate um rules either from a community project or from your own
27:18
a community project or from your own
27:18
a community project or from your own your own uh your own knowledge base so
27:20
your own uh your own knowledge base so
27:20
your own uh your own knowledge base so if you've got um if you've got specific
27:24
if you've got um if you've got specific
27:24
if you've got um if you've got specific rules for your development environment
27:25
rules for your development environment
27:25
rules for your development environment you can write those rules yourself but
27:27
you can write those rules yourself but
27:27
you can write those rules yourself but you can also adopt rules from a couple
27:29
you can also adopt rules from a couple
27:29
you can also adopt rules from a couple different Community projects there's um
27:32
different Community projects there's um
27:32
different Community projects there's um SQL server. rules um and then there's
27:35
SQL server. rules um and then there's
27:35
SQL server. rules um and then there's also tsql
27:36
also tsql
27:36
also tsql smells both of those are really cool
27:40
smells both of those are really cool
27:40
smells both of those are really cool projects where they spent a lot of time
27:42
projects where they spent a lot of time
27:42
projects where they spent a lot of time and took input from a number of
27:44
and took input from a number of
27:44
and took input from a number of developers to put together over 50 rules
27:47
developers to put together over 50 rules
27:47
developers to put together over 50 rules um that can be pretty
27:49
um that can be pretty
27:49
um that can be pretty helpful what is what's important to
27:52
helpful what is what's important to
27:52
helpful what is what's important to understand about writing code analysis
27:55
understand about writing code analysis
27:55
understand about writing code analysis rules is that the object def
27:58
rules is that the object def
27:58
rules is that the object def and the relationships are accessible
28:00
and the relationships are accessible
28:00
and the relationships are accessible while you write them um so keeping on
28:04
while you write them um so keeping on
28:04
while you write them um so keeping on theme with today it's not just about
28:07
theme with today it's not just about
28:07
theme with today it's not just about parsing the strings but you can go to um
28:13
parsing the strings but you can go to um
28:13
parsing the strings but you can go to um you can go to the level of looking at
28:15
you can go to the level of looking at
28:15
you can go to the level of looking at every
28:16
every
28:16
every table and throwing a warning if a table
28:20
table and throwing a warning if a table
28:20
table and throwing a warning if a table has no foreign
28:21
has no foreign
28:21
has no foreign [Music]
28:23
[Music]
28:23
[Music] Keys it's possible that you have some
28:26
Keys it's possible that you have some
28:26
Keys it's possible that you have some tables that are completely unrelated
28:27
tables that are completely unrelated
28:27
tables that are completely unrelated from from others in your
28:29
from from others in your
28:29
from from others in your environment but that is that is the kind
28:32
environment but that is that is the kind
28:32
environment but that is that is the kind of thing where you can Traverse the
28:34
of thing where you can Traverse the
28:34
of thing where you can Traverse the syntax tree as well as the relationships
28:37
syntax tree as well as the relationships
28:37
syntax tree as well as the relationships between them to to test that out so the
28:40
between them to to test that out so the
28:40
between them to to test that out so the relationships between them come from the
28:42
relationships between them come from the
28:42
relationships between them come from the DAC model um and then the syntax tree
28:45
DAC model um and then the syntax tree
28:46
DAC model um and then the syntax tree comes from the the scripton parsing
28:48
comes from the the scripton parsing
28:48
comes from the the scripton parsing Library so you kind of get both two for
28:50
Library so you kind of get both two for
28:50
Library so you kind of get both two for one when you're using this code analysis
28:53
one when you're using this code analysis
28:53
one when you're using this code analysis extensibility the rule configuration
28:56
extensibility the rule configuration
28:56
extensibility the rule configuration that's a part of this was what we saw on
28:57
that's a part of this was what we saw on
28:57
that's a part of this was what we saw on the previous slide where you get to
29:00
the previous slide where you get to
29:00
the previous slide where you get to check for whether or not rules are
29:02
check for whether or not rules are
29:02
check for whether or not rules are enabled and disabled as well as if they
29:04
enabled and disabled as well as if they
29:04
enabled and disabled as well as if they are an
29:05
are an
29:05
are an error and then the concept of picking up
29:07
error and then the concept of picking up
29:07
error and then the concept of picking up all the rules for a Target platform that
29:10
all the rules for a Target platform that
29:10
all the rules for a Target platform that is a special one so let's take a look at
29:15
is a special one so let's take a look at
29:15
is a special one so let's take a look at using Code analysis rules in real time
29:18
using Code analysis rules in real time
29:18
using Code analysis rules in real time now uh you already know I work for
29:20
now uh you already know I work for
29:20
now uh you already know I work for Microsoft so you're not surprised to see
29:22
Microsoft so you're not surprised to see
29:22
Microsoft so you're not surprised to see me using Adventure
29:24
me using Adventure
29:24
me using Adventure Works um in this example I'm I'm in v
29:28
Works um in this example I'm I'm in v
29:28
Works um in this example I'm I'm in v code and I'm using um not the SQL
29:31
code and I'm using um not the SQL
29:32
code and I'm using um not the SQL projects that are currently available in
29:33
projects that are currently available in
29:33
projects that are currently available in Visual Studio I am using um SQL projects
29:37
Visual Studio I am using um SQL projects
29:37
Visual Studio I am using um SQL projects SDK when I build this project it's going
29:41
SDK when I build this project it's going
29:41
SDK when I build this project it's going to run SQL code analysis and it's going
29:45
to run SQL code analysis and it's going
29:45
to run SQL code analysis and it's going to run SQL code analysis with some
29:47
to run SQL code analysis with some
29:47
to run SQL code analysis with some specific settings um first you see the
29:50
specific settings um first you see the
29:50
specific settings um first you see the minus example it's going to skip some of
29:53
minus example it's going to skip some of
29:53
minus example it's going to skip some of the outof thebox Microsoft rules I've
29:56
the outof thebox Microsoft rules I've
29:56
the outof thebox Microsoft rules I've loaded in tcq School smells rules and
29:59
loaded in tcq School smells rules and
29:59
loaded in tcq School smells rules and I'll show you how I did that in just a
30:01
I'll show you how I did that in just a
30:01
I'll show you how I did that in just a second and I've said I want these rules
30:03
second and I've said I want these rules
30:03
second and I've said I want these rules to be considered major issues so if I do
30:07
to be considered major issues so if I do
30:07
to be considered major issues so if I do a net build on my SQL project which
30:10
a net build on my SQL project which
30:11
a net build on my SQL project which again just a quick review on what a SQL
30:13
again just a quick review on what a SQL
30:13
again just a quick review on what a SQL project is it's a definition of all the
30:17
project is it's a definition of all the
30:17
project is it's a definition of all the objects that I want to be declared in my
30:21
objects that I want to be declared in my
30:21
objects that I want to be declared in my database and it compiles them together
30:23
database and it compiles them together
30:23
database and it compiles them together into a single build artifact
30:28
into a single build artifact
30:28
into a single build artifact to deploy that build artifact you would
30:30
to deploy that build artifact you would
30:30
to deploy that build artifact you would use tooling like the SQL package CLI or
30:33
use tooling like the SQL package CLI or
30:33
use tooling like the SQL package CLI or within vs code and visual studio there's
30:35
within vs code and visual studio there's
30:35
within vs code and visual studio there's a publish button but likely you're
30:36
a publish button but likely you're
30:36
a publish button but likely you're automating you can generate even a
30:38
automating you can generate even a
30:38
automating you can generate even a deployment
30:40
deployment
30:40
deployment script while the concept of writing all
30:42
script while the concept of writing all
30:42
script while the concept of writing all those create statements can be a little
30:44
those create statements can be a little
30:44
those create statements can be a little scary um because you're thinking about
30:46
scary um because you're thinking about
30:46
scary um because you're thinking about like now how do I update my database
30:48
like now how do I update my database
30:48
like now how do I update my database that's what the SQL package CLI does for
30:50
that's what the SQL package CLI does for
30:50
that's what the SQL package CLI does for you is it generates the deployment of
30:52
you is it generates the deployment of
30:52
you is it generates the deployment of figuring out how to make those changes
30:54
figuring out how to make those changes
30:54
figuring out how to make those changes that's beyond us today we want to focus
30:56
that's beyond us today we want to focus
30:57
that's beyond us today we want to focus on I have my object definitions how do I
30:59
on I have my object definitions how do I
30:59
on I have my object definitions how do I make sure that they're good code when I
31:01
make sure that they're good code when I
31:01
make sure that they're good code when I run net
31:03
run net
31:03
run net build it's going to take a look at all
31:05
build it's going to take a look at all
31:05
build it's going to take a look at all of the relationships in the database all
31:07
of the relationships in the database all
31:07
of the relationships in the database all the object definitions and then it's
31:09
the object definitions and then it's
31:09
the object definitions and then it's going to run the rules that I have
31:11
going to run the rules that I have
31:11
going to run the rules that I have enabled and smells
31:14
enabled and smells
31:14
enabled and smells Z5 is again the select star Rule and it
31:18
Z5 is again the select star Rule and it
31:18
Z5 is again the select star Rule and it found that and it found it in a view
31:21
found that and it found it in a view
31:21
found that and it found it in a view that I named select V select star that
31:24
that I named select V select star that
31:24
that I named select V select star that was very clever of me um and even tells
31:26
was very clever of me um and even tells
31:26
was very clever of me um and even tells me what line and character it's on so if
31:29
me what line and character it's on so if
31:29
me what line and character it's on so if I go into views select star it's on line
31:34
I go into views select star it's on line
31:34
I go into views select star it's on line three character eight and there's my
31:36
three character eight and there's my
31:36
three character eight and there's my select star so if you have a huge code
31:39
select star so if you have a huge code
31:39
select star so if you have a huge code base and you want to start improving it
31:42
base and you want to start improving it
31:42
base and you want to start improving it bit by bit enabling code analysis will
31:45
bit by bit enabling code analysis will
31:45
bit by bit enabling code analysis will tell you where to look even when it
31:47
tell you where to look even when it
31:47
tell you where to look even when it finds these
31:49
finds these
31:49
finds these issues now loading in custom code
31:52
issues now loading in custom code
31:52
issues now loading in custom code analysis rules is going to look from
31:55
analysis rules is going to look from
31:55
analysis rules is going to look from where the build engine is is loaded
31:58
where the build engine is is loaded
31:58
where the build engine is is loaded uh the the DAC
32:00
uh the the DAC
32:00
uh the the DAC effects um dlls from for visual studio
32:05
effects um dlls from for visual studio
32:05
effects um dlls from for visual studio builds that's a specific folder within
32:07
builds that's a specific folder within
32:07
builds that's a specific folder within the visual studio um installer folder
32:11
the visual studio um installer folder
32:11
the visual studio um installer folder for the net SDK we have a very
32:13
for the net SDK we have a very
32:13
for the net SDK we have a very interesting road ahead so first the
32:17
interesting road ahead so first the
32:17
interesting road ahead so first the workaround um right now the build um
32:22
workaround um right now the build um
32:22
workaround um right now the build um pulls its targets from the build folder
32:25
pulls its targets from the build folder
32:25
pulls its targets from the build folder in the NIT folder so if I go and
32:28
in the NIT folder so if I go and
32:28
in the NIT folder so if I go and packages and drop my compiled dll for
32:31
packages and drop my compiled dll for
32:31
packages and drop my compiled dll for tsql smells in there I'm able to go
32:34
tsql smells in there I'm able to go
32:34
tsql smells in there I'm able to go ahead and pick up those rules they're
32:36
ahead and pick up those rules they're
32:36
ahead and pick up those rules they're just picked up automatically no no
32:37
just picked up automatically no no
32:38
just picked up automatically no no further no further
32:39
further no further
32:39
further no further steps now that's that's still not as
32:43
steps now that's that's still not as
32:43
steps now that's that's still not as good as we want it to be because again
32:46
good as we want it to be because again
32:46
good as we want it to be because again our our idea with SQL code analysis is
32:49
our our idea with SQL code analysis is
32:49
our our idea with SQL code analysis is that we're getting SQL to be just as a
32:52
that we're getting SQL to be just as a
32:52
that we're getting SQL to be just as a part of our development processes the
32:55
part of our development processes the
32:55
part of our development processes the rest of our application code when I need
32:57
rest of our application code when I need
32:57
rest of our application code when I need to pick up development dependencies for
33:00
to pick up development dependencies for
33:00
to pick up development dependencies for my C projects I add package references
33:03
my C projects I add package references
33:03
my C projects I add package references the direction that we're headed and it
33:05
the direction that we're headed and it
33:05
the direction that we're headed and it is not there yet is to have a package
33:07
is not there yet is to have a package
33:07
is not there yet is to have a package reference for different packages like
33:11
reference for different packages like
33:11
reference for different packages like code
33:13
code
33:13
code analysis let me go back to the deck real
33:18
quick because I I want to I want to kind
33:21
quick because I I want to I want to kind
33:21
quick because I I want to I want to kind of close out by wrapping up where we're
33:24
of close out by wrapping up where we're
33:24
of close out by wrapping up where we're at in this SQL projects Evolution to
33:26
at in this SQL projects Evolution to
33:26
at in this SQL projects Evolution to bring code anal is to a better place um
33:30
bring code anal is to a better place um
33:30
bring code anal is to a better place um first I want to start by recognizing
33:32
first I want to start by recognizing
33:32
first I want to start by recognizing that there's a fantastic Community
33:33
that there's a fantastic Community
33:34
that there's a fantastic Community project for SQL projects that it's been
33:36
project for SQL projects that it's been
33:36
project for SQL projects that it's been really setting the bar for how do we
33:38
really setting the bar for how do we
33:38
really setting the bar for how do we make it as easy to use as possible
33:40
make it as easy to use as possible
33:40
make it as easy to use as possible especially in crossplatform environments
33:42
especially in crossplatform environments
33:42
especially in crossplatform environments and it brings in the and uh very
33:45
and it brings in the and uh very
33:45
and it brings in the and uh very extended code analysis rule set just
33:47
extended code analysis rule set just
33:47
extended code analysis rule set just right out of the box um so if you're
33:49
right out of the box um so if you're
33:49
right out of the box um so if you're looking for like how do I get started
33:51
looking for like how do I get started
33:51
looking for like how do I get started today with the least amount of friction
33:53
today with the least amount of friction
33:54
today with the least amount of friction um picking up the MS build. sdk. SQL PR
33:57
um picking up the MS build. sdk. SQL PR
33:57
um picking up the MS build. sdk. SQL PR is one way
33:58
is one way
33:58
is one way to be able to take your SQL project and
34:00
to be able to take your SQL project and
34:00
to be able to take your SQL project and get a larger rule set on it right
34:02
get a larger rule set on it right
34:02
get a larger rule set on it right away the other thing that the community
34:06
away the other thing that the community
34:06
away the other thing that the community um SDK for SQL projects at the top
34:08
um SDK for SQL projects at the top
34:08
um SDK for SQL projects at the top introduces is package references for DAC
34:11
introduces is package references for DAC
34:11
introduces is package references for DAC packs if I have a large database and I
34:14
packs if I have a large database and I
34:14
packs if I have a large database and I need to break it apart one of the ways
34:16
need to break it apart one of the ways
34:16
need to break it apart one of the ways to do this is to publish DAC packs as
34:18
to do this is to publish DAC packs as
34:18
to do this is to publish DAC packs as new get packages and then pick those up
34:21
new get packages and then pick those up
34:21
new get packages and then pick those up into a SQL project so that you can have
34:23
into a SQL project so that you can have
34:23
into a SQL project so that you can have a better architected database
34:28
a better architected database
34:28
a better architected database Now package references with SQL projects
34:30
Now package references with SQL projects
34:30
Now package references with SQL projects is a fantastic concept and that's why I
34:33
is a fantastic concept and that's why I
34:33
is a fantastic concept and that's why I mentioned before where where we're
34:35
mentioned before where where we're
34:35
mentioned before where where we're bringing code analysis references for
34:38
bringing code analysis references for
34:38
bringing code analysis references for SQL project is that instead of needing
34:41
SQL project is that instead of needing
34:41
SQL project is that instead of needing to do that ugly workaround you saw where
34:43
to do that ugly workaround you saw where
34:43
to do that ugly workaround you saw where I had copied the the dll for the tsql
34:47
I had copied the the dll for the tsql
34:47
I had copied the the dll for the tsql smells into the N get packages folder um
34:50
smells into the N get packages folder um
34:50
smells into the N get packages folder um this should also be a package reference
34:53
this should also be a package reference
34:53
this should also be a package reference for custom rules you can do that for DAC
34:55
for custom rules you can do that for DAC
34:55
for custom rules you can do that for DAC packs as well with that Microsoft . SQL
34:58
packs as well with that Microsoft . SQL
34:58
packs as well with that Microsoft . SQL SDK um but it it needs to be a package
35:01
SDK um but it it needs to be a package
35:01
SDK um but it it needs to be a package reference to make it even easier to
35:03
reference to make it even easier to
35:03
reference to make it even easier to bring in those additional custom rules
35:06
bring in those additional custom rules
35:06
bring in those additional custom rules the microsoft. build. SQL SDK that you
35:10
the microsoft. build. SQL SDK that you
35:10
the microsoft. build. SQL SDK that you saw me using with Adventure
35:12
saw me using with Adventure
35:12
saw me using with Adventure Works um is is is an evolution on the
35:16
Works um is is is an evolution on the
35:16
Works um is is is an evolution on the original style SQL projects um if you're
35:19
original style SQL projects um if you're
35:19
original style SQL projects um if you're an afficianado of SQL projects you
35:21
an afficianado of SQL projects you
35:22
an afficianado of SQL projects you probably are quite surprised at how
35:23
probably are quite surprised at how
35:23
probably are quite surprised at how short this file is the uh default glass
35:27
short this file is the uh default glass
35:27
short this file is the uh default glass ing of all the files for example is
35:29
ing of all the files for example is
35:29
ing of all the files for example is something that's a part of both sdks and
35:32
something that's a part of both sdks and
35:32
something that's a part of both sdks and so it makes it much easier to modify and
35:35
so it makes it much easier to modify and
35:35
so it makes it much easier to modify and edit the SQL project file and so this
35:37
edit the SQL project file and so this
35:37
edit the SQL project file and so this will be coming soon into Visual
35:40
will be coming soon into Visual
35:40
will be coming soon into Visual Studio overall when it comes to code
35:42
Studio overall when it comes to code
35:42
Studio overall when it comes to code analysis extensibility it's not required
35:46
analysis extensibility it's not required
35:46
analysis extensibility it's not required you are already going to get a win if
35:49
you are already going to get a win if
35:49
you are already going to get a win if you start in Visual Studio enable code
35:51
you start in Visual Studio enable code
35:51
you start in Visual Studio enable code analysis on build and just start getting
35:53
analysis on build and just start getting
35:53
analysis on build and just start getting warnings when your code needs some
35:56
warnings when your code needs some
35:56
warnings when your code needs some improvements but if you want to take it
35:58
improvements but if you want to take it
35:58
improvements but if you want to take it further and either load in community
36:00
further and either load in community
36:00
further and either load in community rules or you have specific requirements
36:02
rules or you have specific requirements
36:02
rules or you have specific requirements for your own environment this is where
36:05
for your own environment this is where
36:05
for your own environment this is where you can use Community examples or follow
36:07
you can use Community examples or follow
36:07
you can use Community examples or follow the walkth through in the Microsoft
36:08
the walkth through in the Microsoft
36:08
the walkth through in the Microsoft documentation to write custom rules or
36:11
documentation to write custom rules or
36:11
documentation to write custom rules or rule like even if a single rule will
36:13
rule like even if a single rule will
36:13
rule like even if a single rule will help your environment it's worth it um
36:16
help your environment it's worth it um
36:16
help your environment it's worth it um have those custom rules you get the
36:18
have those custom rules you get the
36:18
have those custom rules you get the analysis results as a part of a build so
36:21
analysis results as a part of a build so
36:21
analysis results as a part of a build so that becomes part of your CI and then
36:23
that becomes part of your CI and then
36:23
that becomes part of your CI and then you're all set this is one of the best
36:26
you're all set this is one of the best
36:26
you're all set this is one of the best times to hopping in on code analysis for
36:29
times to hopping in on code analysis for
36:29
times to hopping in on code analysis for SQL because we are bringing package
36:31
SQL because we are bringing package
36:31
SQL because we are bringing package references for custom rules to that
36:33
references for custom rules to that
36:33
references for custom rules to that build
36:34
build
36:34
build SDK right as it starts to land into
36:36
SDK right as it starts to land into
36:37
SDK right as it starts to land into Visual Studio very very soon so that's
36:40
Visual Studio very very soon so that's
36:40
Visual Studio very very soon so that's it's like best timing
36:42
it's like best timing
36:42
it's like best timing ever with that said we covered a whole
36:45
ever with that said we covered a whole
36:46
ever with that said we covered a whole breadth of topics today at a surface
36:48
breadth of topics today at a surface
36:48
breadth of topics today at a surface level I'm happy to take any questions if
36:51
level I'm happy to take any questions if
36:51
level I'm happy to take any questions if folks have it but we started with SQL
36:53
folks have it but we started with SQL
36:53
folks have it but we started with SQL check and looked at how even Rex parsing
36:55
check and looked at how even Rex parsing
36:55
check and looked at how even Rex parsing can give us valid feedback on things
36:58
can give us valid feedback on things
36:58
can give us valid feedback on things there are edges to what it can do um and
37:01
there are edges to what it can do um and
37:01
there are edges to what it can do um and that's why we like to head towards
37:03
that's why we like to head towards
37:03
that's why we like to head towards syntax parsing with scrip which is a net
37:06
syntax parsing with scrip which is a net
37:06
syntax parsing with scrip which is a net Library you can use it stand alone like
37:09
Library you can use it stand alone like
37:09
Library you can use it stand alone like you saw in that console app example that
37:12
you saw in that console app example that
37:12
you saw in that console app example that I had at parsing scripts I even got my
37:14
I had at parsing scripts I even got my
37:14
I had at parsing scripts I even got my demo to to kind of slap me back in the
37:16
demo to to kind of slap me back in the
37:16
demo to to kind of slap me back in the face a little bit where it didn't
37:18
face a little bit where it didn't
37:18
face a little bit where it didn't recognize the Nock alias in one way so
37:20
recognize the Nock alias in one way so
37:21
recognize the Nock alias in one way so now you get to see that pull request out
37:22
now you get to see that pull request out
37:22
now you get to see that pull request out in open source here soon and then last
37:25
in open source here soon and then last
37:25
in open source here soon and then last but not least SQL projects brings code
37:28
but not least SQL projects brings code
37:28
but not least SQL projects brings code analysis as just a checkbox on build so
37:31
analysis as just a checkbox on build so
37:31
analysis as just a checkbox on build so we can get those warnings in real time
37:33
we can get those warnings in real time
37:33
we can get those warnings in real time as a part of our CI
37:35
as a part of our CI
37:35
as a part of our CI [Music]