Posted in Development, SQL Server 2017

SQL Server – TRIM() function in SQL Server 2017

Hello friends,

Please check the below link of my new article on TRIM() function in SQL Server 2017 –

https://social.technet.microsoft.com/wiki/contents/articles/51047.introduction-to-trim-function-in-sql-server-2017.aspx

 

Advertisements
Posted in Development, SQL Server 2017

SQL Server – TRANSLATE() function in SQL Server 2017

Hello Friends,

Wish you all a very Happy New Year 2018.

Please find the link of my first article of year 2018 at below link –

https://social.technet.microsoft.com/wiki/contents/articles/51008.translate-function-in-sql-server-2017.aspx

Posted in Performance Tuning, Uncategorized

SQL Server Execution Plan Operator – Table Scan

In my previous blog we discussed about the different format of SQL Server execution plan. Now in upcoming blogs we will discussed about the most commonly seen operators we see when execution plan generates. In this post I will start the easy one that is Table Scan operator.

As with the name of operator Table Scan it indicates that it returns the rows after scanning every row of a table.

When this operator appears in execution plan?

When there is no index present on the table then query optimizer has to scan every row of table to return the result set.

tablescan

In the returned result set data is retrieved after traversing data row by row.

When a table is having fewer rows and returning maximum rows then it’s faster for query optimizer to scan all rows rather than checking indexes.

That’s all for the day !!!

 

Posted in Development, Performance Tuning, Uncategorized

SQL Server Execution Plan formats

Execution plan helps in knowing how a query gets executed and troubleshooting a query which is performing poor.

In SQL Server execution plan can be of 2 types:

  1. Estimated Execution plan
  2. Actual Execution plan

Execution plan can be viewed in 3 different formats:

  • Text format
  • XML format
  • Graphical format

We can choose the format on depending what level of details we want to see.

Let’s discuss these format one by one.

  1. Text format

Execution plan in text format can be seen using these 3 methods.

  • Showplan_ALL
  • Showplan_Text
  • Statistics Profile

Showplan_ALL : Its shows the estimated execution plan of the query. To enable it we need to execute the query:

SET SHOWPLAN_ALL ON;

1

Once we analyze our query plan we can turn off this option by executing below query so that our subsequent queries will not get affected from this.

SET SOWPLAN_ALL OFF;

Showplan_Text: It’s also works with the estimated execution plan of the query. To enable it we need to execute the query:

SET SHOWPLAN_TEXT ON;

2

SET SHOWPLAN_TEXT OFF;

Statistics Profile: It works with the actual execution plan. We need to execute the below query to enable it:

SET STATISTICS PROFILE ON;

3

SET STATISTICS PROFILE OFF;

2 XML format plans

Execution plan can be seen in XML format by using following two methods:

Showplan XML

Statistics XML

Statistics XML: It is used to generate the execution plan in XML format. After clicking on XML link a graphical execution plan gets opened in a new window. We can save that graphical plan to our local disk with extension .sqlplan.

4

Showplan XML: It’s also generates the XML plan and only one result set appears in outcome with XML link.

5

3. Graphical Plan

This is the most easiest and readable option to generate the execution plan. It also has two option for generating execution plan:

Estimated execution plan

Actual execution plan

To enable the estimated execution plan we can press the shortcut key Ctrl +L. Also, we can navigate to Query tab and select Display Estimated Execution Plan.

Estimated execution plan is beneficial when we have complex query and not aware how much it will take for complete execution in such situation we can use this as this doesn’t execute the query to generate the plan.

6

Similarly, actual execution plan we can be enabled by using shortcut key Ctrl+M.

We can also activate it by selecting option Include Actual Execution Plan from Query menu tab.

That’s all for the day folks.

 

Posted in Development

Limitation of In-Memory OLTP in SQL Server 2016 – Sp_rename not allowed

Hi friends, one more limitation of In-Memory OLTP is SQL Server 2016 is that sp_rename is not allowed to change the name of in-memory OLTP objects.

Sp_rename is a system stored procedure which allows to change the name of the user-defined objects like table name, column name, datatype of a column etc. in the current database.

Let’s see an example of this –

There is already an In-memory table created with name ‘Test_OLTP’ and now I like to rename it with a new name ‘MemOLTP’.

I have executed the below query to rename the table name.

sp_rename ‘dbo.Test_OLTP’,’MemOLTP’

When I executed the query it comes up with an error stating –

‘Operations that require a change to the schema version,

for example renaming, are not supported with memory optimized tables.’

Lim_Sp_rename

That’s all for the day folks 🙂