PERSIST_SAMPLE_PERCENT

ON

The statistics will retain the set sampling percentage for subsequent updates that do not explicitly specify a sampling percentage.

OFF

The statistics sampling percentage will get reset to default sampling in subsequent updates that do not explicitly specify a sampling percentage.

Source: UPDATE STATISTICS (Transact-SQL) | Microsoft Docs

This keyword is added to T-SQL from SQL Server 2016 SP1 CU4.

Perdro Lopez (Microsoft Team) explained it deeply here .

Advertisements

T-SQL: How to Sort a Column that Contains Character-Separated Numbers 

we can use the sorting based on hierarchyid data type which in each node is based on numeric sort order.

SELECT *
FROM dbo.Letters
ORDER BY
CAST(N'/' + IndicatorCode + N'/' AS HIERARCHYID)

 

 

Source: T-SQL: How to Sort a Column that Contains Character-Separated Numbers – TechNet Articles – United States (English) – TechNet Wiki

Adaptive query processing in SQL Server 2017

The batch mode adaptive joins feature enables the choice of a hash join or nested loop join method to be deferred until after the first input has been scanned. The adaptive join operator defines a threshold that is used to decide when to switch to a nested loop plan. Your plan can therefore dynamically switch to a better join strategy during execution. Here’s how it works:

If the row count of the build join input is small enough that a nested loop join would be more optimal than a hash join, your plan switches to a nested loop algorithm.

If the build join input exceeds a specific row count threshold, no switch occurs and your plan continues with a hash join.

Source: Adaptive query processing in Microsoft SQL databases | Microsoft Docs

SQL Server Machine Learning Tutorials

This article provides a comprehensive list of the tutorials, demos, and sample applications that use machine learning features in SQL Server 2016 or SQL Server 2017. Start here to learn how to run R or Python from T-SQL, use remote and local compute contexts, and optimize your R and Python code for a SQL production environment:

Source: SQL Server Machine Learning Tutorials | Microsoft Docs

Using the FORCESEEK Table Hint

If a plan contains table or index scan operators, and the corresponding tables cause a high number of reads during the execution of the query, as observed in the STATISTICS IO output, forcing an index seek operation may yield better query performance. This is especially true when inaccurate cardinality or cost estimations cause the optimizer to favor scan operations at plan compilation time.

Source: Using the FORCESEEK Table Hint

TNWikiSummit15 Presentation: Error Handling in SQL Server 2012 and 2014

 

The follow text is copied from this blog:
Official Blog of TechNet Wiki

 

Using Error Handling within the Transact SQL language needs a deep knowledge. It depends on the SQL Server version, there are various implementations for handling the errors in T-SQL. But if you use SQL Server 2012 or later versions, you can use the simplest and fully structured error handling.

Saeid Hasani showed how to implement such simple error handling in T-SQL. He showed this topic from a problem solving approach and particularly in SQL Server 2012  and the later versions. In this session, he covered some questions to providing a step by step tutorial for designing a structured error handling mechanism in SQL Server.

This session is available for download using the following links:

Video:

https://gallery.technet.microsoft.com/TN-Summit15-Structured-dfdcb9a5

PowerPoint:

https://gallery.technet.microsoft.com/TN-Summit15-Structured-118896bb

Original TN Wiki Article:

http://social.technet.microsoft.com/wiki/contents/articles/20002.structured-error-handling-mechanism-in-sql-server-2012.aspx

Judges’ comments for the article are from TechNet GURU Awards – September 2013 :

Richard Mueller:

“Useful tutorial with lots of examples and very good links. Excellent comparison of THROW and RAISERROR.”

Jinchun Chen:

“Nice article. Thank you for the sharing.”

Ed Price:

“Incredibly well formatted with images, code, and explanations. And great interactions in the comments, making the improvements suggested by Naomi. ”

DB:

“Useful, clear and accurate guidance on error handling”

Samuel Lester:

“Great coverage of different techniques, challenges, pros/cons. Well done, very informative, and easy to read.”

 

Webinar: Error Handling in SQL Server

 

TN Summit 2015

 

March 19, 2015 9:45 PM GMT

Saeid Hasani

Structured Error Handling in SQL Server

The goal of this session is to provide a simple and easy to use error handling mechanism with minimum complexity. This session is compatible with MS SQL SERVER 2012 and 2014.

Biography

Saeid Hasani is a Senior Database Developer, SQL Server Consultant, freelance SQL author and T-SQL Trainer. His main expertise is T-SQL Programming and Query Tuning. As a database developer, he has several years of experience in developing software in various domains such as MIS, ERP and customized Enterprise applications. As a freelance SQL author, he has been writing about SQL since 2010. He has been one of the MSDN SQL Server Forums Moderators.

Webinar Link: https://www307.livemeeting.com/lrs/0000000379_116/Registration.aspx?pageName=gdx8jdv0r7rmftrk

T-SQL: Delete All Rows From a Table Except Top(N) Rows

I added the article which comes from a question that asked in MSDN forum. The problem is how to delete all rows from a table but remain the Top (N) rows, for example, remaining only the top (1000) rows. All Code samples in this article are downloadable from this   link . Please navigate to this link

T-SQL: Error Handling for CHECK Constraints

 

Happy New Year 2015 to all! 🙂

This is my first post in new year and wish I will be more active this year!
In the former article about CHECK constraints, we did not cover how to handle the errors when a CHECK constraint violates. In this article, we cover this important topic. It has worth to take a look at that article, if it needed. Please navigate to the article via this link.