TableSample in FROM clause to limit number of rows

TableSample clause started with SQL Server 2015 limits the number of rows returned from a table to a sample % or sample numbers.

Here is how TableSample is defined in the MSDN site:
------------
TABLESAMPLE (10 PERCENT) /*Return a sample 10 percent of the rows of the result set. */
TABLESAMPLE (15 ROWS) /* Return a sample of 15 rows from the result set. */.
--------------
For these scenarios it cannot be applied:

Derived tables
Linked Server Tables
Tables from Table-Valued functions
Row-set functions
Open XML

This here is the syntax for TableSample clause:
----------------
TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )
[ REPEATABLE (repeat_seed) ]

TableSample in FROM Clause does not behave as defined in the syntax and could provide a suprprisining results:

I queried the Northwind databases Orders table using the above syntax.  The 829 rows table yielded the following. These are results of actual queries run in SQL Server 2016 Developers edition.

 Select * From Orders---829 rows

 Select * From Orders TableSample (10 PERCENT)

83 Rows

42 rows

43 rows

162 rows

 Select * From Orders TableSample (10)

210 rows

163 rows

 Select * From Orders TableSample (10 rows)

0 Rows

43 rows

81 rows

If you are using this in the FROM Clause, look out for surprises.

Formatting date using the T-SQL Convert() function

Date is of datatype datetime and you can format it to a string. Date as used in different countries is formatted differently some may use month name, some use hyphens between date, month and year while others use slashes, etc.

The syntax of the Convert() function is as follows:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The optional attributes are as usual within []. The data_type and expression are required. The style is where you can look for adoption to the format (different countries use differently). While length is optional, it should be adequately chosen.

The expression is converted and the resulting data_type with specified length will be displayed.

The queries for this post were run in Microsoft SQL Operations Studio (sqlops). One can only query in SQL Operations studio after being connected to one of the SQL Server instances

Here is just Select GetDate() query.

The formatting style is of ISO 8601 (126)

The Japanese 4 and 2-digit year formats.

--4 digit year

----2 digit year

The British and French style

The Hijiri style as in Islamic Countries. Note this may be at slight variance with this site:

https://www.islamicfinder.org/islamic-date-converter/

And finally the default for datatime/smalldatetime

Hands-on Learning Event in Honolulu 1 : Introduction to Structured Query Language

Database skills are essential in whatever organization and in whatever position and it is crucial that you have this in your arsenal (Resume')
Databases, organized repositories of information, have become indispensable in today's world. In this introductory course you will learn about relational databases and the basics of Structured Query Language (SQL) including sorting; grouping result sets; using DDL, DML, DCL, and TCL. All SQL statements will initially be written for one table. Most practical, modern and relational databases will include a large number of tables and SQL queries have to access information from several tables. This course will introduce you to querying more than one table. With this skill you will be able to query two or more tables in a database.
This is a hands-on course which will take you from installing SQL Server 2016 to learning the ins and outs of SQL Server Management Studio and of course a full dose of SQL. The course covers most of the requirements to take Microsoft's certification (98-364), the Microsoft Technology Associate. During each meeting, you will be assessed for your knowledge, competency and proficiency in SQL.

What's new in this session:

SQL Server Using Powershell
JSON and XML

 Section 1 - Apr 24 - May 24; TTh; 5:30 pm - 7:30 pm; 10 meetings (Register Online)

Get the March 2018 Power BI update

The update is out and you can get it from here:

You can also get it from Windows Store if you are running Windows 10.

Well what is new?


On reporting you have the following:

Report page tooltips (preview)
Bookmarking is now generally available
Display units and precision control for table & matrix columns
Turn off the visual header in reading mode for a report
Improved default visual placement

On custom visuals you have the following:

Custom visuals
Mapbox
User List by CloudScope
Timeline by CloudScope
KPI Chart by Akvelon
R DataTable
Outliers Detection
Data Insights by MAQ Software
Dumbbell Chart by MAQ Software
Clustering using OPTICS by MAQ Software

For data connectivity you have these new ones:

SAP HANA connector enhancements
DirectQuery multidimensional support is now generally available
SSL certificate validation support
SAP BW DirectQuery is now generally available

And you also have the following:

Improved error reporting
View previous errors you’ve encountered

Watch this video to learn the new stuff in the March 2018 update:

R package for Machine Learning - caret

The most trending topics these days are Artificial Intelligence and Machine Learning. Every day you get tons of news about AI and ML. R programming language is well placed to initiate you into these 'esoteric' topics. There is a learning curve and should you learn to scale the peaks there will be rewards. So much of 'carrot' talk.

In R, the package that does a lot of work for you as far as AI and ML are concerned is the package 'caret'. You should load this package and use its library to begin your AI/ML related activities. Of course there are more roads to Rome than one.

If have been working with R, you can launch R and install the package. If you are using Microsoft R (R Server/ R Client), you can launch the R GUI.

Run the install statement ass shown:

> install.packages("caret")

Installing package into ‘C:/Users/Owner/Documents/R/win-library/3.3’

(as ‘lib’ is unspecified)

also installing the dependencies ‘minqa’, ‘nloptr’, ‘RcppEigen’, ‘lme4’, ‘SparseM’, ‘MatrixModels’, ‘pbkrtest’, ‘quantreg’, ‘car’

After contacting the site to download a number of packages are downloaded and checked as shown here:

package ‘minqa’ successfully unpacked and MD5 sums checked

package ‘nloptr’ successfully unpacked and MD5 sums checked

package ‘RcppEigen’ successfully unpacked and MD5 sums checked

package ‘lme4’ successfully unpacked and MD5 sums checked

package ‘SparseM’ successfully unpacked and MD5 sums checked

package ‘MatrixModels’ successfully unpacked and MD5 sums checked

package ‘pbkrtest’ successfully unpacked and MD5 sums checked

package ‘quantreg’ successfully unpacked and MD5 sums checked

package ‘car’ successfully unpacked and MD5 sums checked

package ‘caret’ successfully unpacked and MD5 sums checked

Note that the 'caret' library requires ggplot2 which will also be loaded, if you already have it.

Please look up other R related posts on this blog:

http://hodentekhelp.blogspot.com/search?q=ggplot

Let us come back and look at how it may be used.

As is, this post is not 100% SQL Server except that I used the Microsoft R for this post.

You need Python to install the command-line query tool mssql-cli

In my previous post you have seen how to download mssql-cli. Installing mssql-cli requires a Python command-line tool for managing Python packages.

In this post you will learn downloading and installing Python that will enable to use PIP.

You downlaod Python from here:

https://www.python.org/downloads.

This site has a vast number of downloads not only for Windows but also for other platforms. For Windows 10 there are a number of them.

I installed the following:

python-3.7.0b2-webinstall

Python_00.png

Double click the downloaded file to begin installation.

Python_0

Python_2

You get these files into your computer:

Python_4

The PIP tool is in the Scripts directory in the above.

On a Windows 10 computer the following are installed in the All Programs

Python_6

You can get help on PIP as shown here by launching Python 3.7 from the above shortcut and when you are in the Python (>>>) use HELP:

Python_5