Storing and Querying XML Data in SQL Server

Using Transact-SQL for XML

Using mode queries in TSQL for XML allows server to convert result set to related XML formats.Four modes are available for this process:

• RAW - converts each row and gives XML format related with each row, could get 'element' command
• AUTO - same as RAW format, however has less flexibility
• EXPLICIT - much more complexity in queries, PATH way is recommended
• PATH - more flexibility and simplicity,used instead of previous mode

For more practice you can test it on Management Studio:

Microsoft Machine Learning

In December 2016 Microsoft R Server 9.0 was released, and several additional big-data tools and features have been upgraded with new version, including brand new R package for Machine Learning — Microsoft ML.To handle large corpus of text data and high-dimensional categorical data mentioned package has more functionality, from speed-performance and scalability aspect too.

Designing and implementing views

Reducing the complexity and facilitating the readability of queries are realizing by implementing views. Possible to name it as 'virtual table' too. From both performance and complexity aspects, it allows us to turn situation to an advantageous side. Let's look at where to use views:in calling the queries without any executing of table, in giving permission to any user to view specific column,data, in boosting the performance mainly.
From security aspect, let's assume that person A gives table permission to person B, except displaying employees' salary info.Here A prefer to create a view and grant B a permission to access just only view, not whole table.

Executing Plan in SQL Server

Observing performance tuning, reducing or boosting the cost are displayed by executing plan.It allows us to see big picture how system works in background. Primarily it is needed to understand how a query is executed by server.Steps:

1. Parse - parsing whole statement whether it has mistake in it or not.
2. Resolve - getting info about tables,views so on.
3. Optimize - deciding how to execute the query more efficiently
4. Compile - creating executing plan about whole process
5. Execution - final point to realize the execution plan

All implemented execution plans are stored in Plan Cache, to prevent next queries' execution plans to reduce the performance by reading related plans throughout disc.

Querying SQL Server Metadata

To get administrative and informational activities, some functions are available to view mentioned tasks, like stored functions and dynamic management objects. Latter one provides server state information, that exist as 200 DMO functions. Each one's duty is separately shared, for databases,executions,transactions and so on. Most used are:

• dm_db_
• dm_os_
• dm_exec_
• dm_tran_
• dm_io_

DMO stored under sys schema, and executed by xp_ prefix via SELECT statement command.

General formula: select ... from sys.dm_<DMO>_<function>

Inline TVFs

Views are not sufficient in some conditions, to get parameterized function inside SQL queries.Adding parameters to queries makes them user defined,table valued.Latter one will be discussed as primary in this topic.Table Valued Functions(TVFs) have 2 types generally:Inline and Multi-line functions.

Inline TVFs let us to build basic functions embedded inside SELECT statements, also INSERT,UPDATE,DELETE clauses can be usable within.Setting parameters and function name creates new function where SELECT statement auto works,instead of writing each query manually.Latter one effects the performance of server profoundly,from cost aspect also has disadvantage.

Logical order of operations in SELECT statements

Lack of implementing of logical order in SELECT statements may force to encounter several obstacles. Elements of this command and writing some queries will be broadly explained in the post. It is needed to look at whole structure of SELECT statement, and understanding each conditions lets us put the command in logical orders, do not force yourself to memorize as formula.