Get the new version of SQLite

A new version of SQLite is available

SQLite 64-bit is an in-process library that provides you with a zero config, transactional database engine.

The latest version, SQLite 3.23.0(x64) is available for download from here:
https://www.filehorse.com/download-sqlite-64/SQLite

Features of SQLite from the above link listed here:

SQLite Features:

• Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures.
• Zero-configuration - no setup or administration needed.
• Full SQL implementation with advanced features like partial indexes and common table expressions. (Omitted features)
• A complete database is stored in a single cross-platform disk file. Great for use as an application file format.
• Supports terabyte-sized databases and gigabyte-sized strings and blobs. (See limits.html.)
• Small code footprint: less than 500KiB fully configured or much less with optional features omitted.
• Simple, easy to use API.
• Written in ANSI-C. TCL bindings included. Bindings for dozens of other languages available separately.
• Well-commented source code with 100% branch test coverage.
• Available as a single ANSI-C source-code file that is easy to compile and hence is easy to add into a larger project.
• Self-contained: no external dependencies.
• Cross-platform: Android, *BSD, iOS, Linux, Mac, Solaris, VxWorks, and Windows (Win32, WinCE, WinRT) are supported out of the box. Easy to port to other systems.
• Sources are in the public domain. Use for any purpose.
• Comes with a standalone command-line interface (CLI) client that can be used to administer SQLite databases.

However, if you are a Visual Studio user, you can get the SQLite for Universal Windows Platform from the Visual Studio Marketplace that you can access from within Microsoft Visual Studio 2017 Community as shown here.

Get to review all the SQLite posts on this blog here.

For repeatability, TableSample with Repeatable option with a repeat-seed is better

TableSample returns approximately the rows specified with random values for each of physical 8KB page in a table. The returned rows are variable from zero to a value even higher than specified rows or % of rows. It all depends on how evenly data is distributed on the pages.

TableSampleRepeat_0

The REPEATBALE option results in selected sample returned gain. The repeat_seed must be an integer greater then 0.

With Repeatable option specified with the same repeat_seed value, SQL Server will typically returns the same subset of rows as long as no changes have been made to table.

With repeatable(10) option

10 rows out of 209 row is shown in the above.

With REPEATABLE option, the same rows are returned again and again for each run.

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.