‘necessity is the mother of invention’ - microsoft€¦ · arvind shyamsundar 11 years at...
TRANSCRIPT
![Page 1: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/1.jpg)
1
![Page 2: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/2.jpg)
Arvind Shyamsundar11 years at MicrosoftMicrosoft Certified Master: SQL Server 2008Master Trainer: SQL 2012 PTO; SQL 2012 HA/DR workshopsCreator and global owner: T-SQL Patterns and Practices ReviewSQL Server Risk Assessment Programs (SQLRaaS & SQLRAP) lead, Asia
Contact InformationTwitter: @arvisamBlog: http://aka.ms/arvindsh
2
![Page 3: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/3.jpg)
‘Necessity is the mother of invention’
3
![Page 4: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/4.jpg)
4
![Page 5: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/5.jpg)
http://blogs.msdn.com/b/gertd/archive/2008/08/21/getting-to-the-crown-jewels.aspx
5
![Page 6: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/6.jpg)
6
![Page 7: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/7.jpg)
Feature pack: http://www.microsoft.com/en-us/download/details.aspx?id=35580
7
![Page 8: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/8.jpg)
8
![Page 9: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/9.jpg)
9
![Page 10: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/10.jpg)
http://technet.microsoft.com/en-us/library/microsoft.sqlserver.transactsql.scriptdom.likepredicate_properties.aspx
In the above example, here is how LikePredicate looks:
LikePredicateScalarExpression FirstExpressionScalarExpression SecondExpressionBoolean NotDefinedBoolean OdbcEscapeScalarExpression EscapeExpression
Here is the inheritance graph from ScalarExpression all the way down to StringLiteral:
ScalarExpression -> PrimaryExpression -> ValueExpression -> Literal -> StringLiteral
10
![Page 11: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/11.jpg)
11
![Page 12: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/12.jpg)
12
![Page 13: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/13.jpg)
Analyzing the token stream directly is useful for specific cases such as identifying single line statements etc.
13
![Page 14: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/14.jpg)
14
![Page 15: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/15.jpg)
http://blogs.msdn.com/b/arvindsh/archive/2013/04/04/using-the-transactsql-scriptdom-parser-to-get-statement-counts.aspx
15
![Page 16: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/16.jpg)
16
![Page 17: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/17.jpg)
17
![Page 18: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/18.jpg)
18
![Page 19: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/19.jpg)
19
![Page 20: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/20.jpg)
For example, if we want to find out statements without a WHERE clause, it is tedious to do that with visitor pattern code.
The inspiration for representing the T-SQL AST as XML originates from the older Microsoft.SqlServer.SqlParser.Parser.Parser class, which would emit XML version of the AST.
http://blogs.msdn.com/b/arvindsh/archive/2013/10/30/xml-visualizer-for-the-transactsql-scriptdom-parse-tree.aspx
20
![Page 21: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/21.jpg)
21
![Page 22: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/22.jpg)
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/42702baf-d5f5-401a-b384-c37aeadd3d03/is-using-the-api-to-create-a-diff-script-inherently-slower-than-shelling-sqlpackageexe-sqlcmd?forum=ssdt
22
![Page 23: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/23.jpg)
23
![Page 24: ‘Necessity is the mother of invention’ - Microsoft€¦ · Arvind Shyamsundar 11 years at Microsoft Microsoft Certified Master: SQL Server 2008 Master Trainer: SQL 2012 PTO; SQL](https://reader035.vdocuments.us/reader035/viewer/2022070107/60213c98f6f7064c9904cd32/html5/thumbnails/24.jpg)
24