Get head start in Basic SQL at PCATT

A new session is going to start soon. Sign-up here.

Microsoft SQL Azure Database: SQL Server in Microsoft Cloud
Windows PowerShell for SQL Server

My sincere thanks and best wishes to my students in the previous meets of this course.

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

You have a new updated mssql-cli, the interactive command-line query tool for SQL Server

The public preview was announced in December last year. Now there is an update.

Read here.

You can download mssql-cli from here:


Using mssql-cli you get to do all this:

    T-SQL IntelliSense
    Syntax highlighting
    Pretty formatting for query results, including Vertical Format
    Multi-line edit mode
    Configuration file support

You can install mssql-cli if you have Python on your computer. For Windows you can install Python from here and make sure it is added to your PATH.

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

Encrypting email password in SQL Server

There is a simple but not most secure way of encrypting a password in SQL Server. It uses the T-SQL function EncryptByPassPhrase().

This function takes two arguments: PassPhrase and ClearText. You provide both the PassPhrase and the ClearText(this is your email password that needs to be encrypted using this function). The PassPhrase generates a TripleDes algorithm with a 128 Key bit length.

ClearText can be a variable of the following types:
nvarchar,
char,
varchar,
binary,
varbinary,
or nchar

PassPhrase can be of the following types:
nvarchar,
char,
varchar,
binary,
varbinary,
or nchar

The following code uses the following variables:
PassPhrase -> "Happy birthday to you"
ClearText->   "$#MyPassword#$"
---------
CREATE TABLE ENCRPTPSWD (EncryptedText varbinary(200))
go
declare @pswrd nvarchar(15)
set @pswrd = '$#MyPassword#$'--ClearText
INSERT INTO ENCRPTPSWD (EncryptedText)
values (EncryptByPassPhrase('Happy birthday to you', @pswrd))
go

The column ENCRPTPSWD contains the result of encrypting and is therefore of type varbinary. The phrase to de-encrypt is the phrase 'Happy birth day to you'.

Now if we access that column we see the encrypted value as shown.

SQL, CLR and SQLCLR

SQL is a language that is needed to run queries (post questions to) against SQL Server databases. It is not specific to Microsoft SQL Server. It can also be used with other databases like Oracle, Sybase, etc.

However Transact SQL (T-SQL) is the enhancement to add some programming capability to SQL. Each database vendor has his own database specific programming capability. For example PL/SQL is Oracle specific procedural programming capability on top of SQL.

Common Language Runtime is the programming that manages the execution of programs written in any of the supported languages such as C#, VB, Visual C++ etc to share common object orineted classes. Thiss is somewhat like Java Virtual Machine for programs run from Java. CLR is also called 'managed execution environment'. CLR is therefore related to .NET programming.

SQL Common Language Runtime (SQLCLR) is the combo technology (SQL Server and .NET) for hosting .NET common language runtime engine within SQL Server. In other words you can run managed code from within SQL Server.

Creating a Visual C++ CLR Console Application in Visual Studio 2015 go here.
http://hodentekhelp.blogspot.com/2015/08/how-do-you-create-visual-c-clr-console.html

Running queries against both structured and unstructured data

It is a Microsoft Data tool. It simplifies management of relational and non-relational data with the ability to query both.

With enormous amount of data that is unstructured that grows bigger each day and structured data both archived and live, a single querying technology is most desired. SQL Server has geared up with a new tool, the Polybase.

You want to query non-relational data. Do you modify it and bring it into SQL Server which is relational and then query it? Or do you buy another product to query non-relational data (like data in Hadoop, blobs and files)?

Well Polybase provides the capability to query non-relational data in-situ using the SQL Server using T-SQL. You need not move the data over to SQL Server although SQL Server gives the option to store in SQL Server if you want to do so. Polybase is supported out of the box in SQL Server 2016 CTP2 which means it will be available in SQL Server 2016.

Polybase was not supported out of the box in earlier version. Of course Polybase can process the queries whether it is on the premises or in the cloud.

Here is a rough schematic of what it is about.

 

SQL Training and SQL Server Reporting Services 2012 training in Honolulu

These are the training schedules for the2nd Quarter of 2014 at the Pacific Center for Advanced Technical Training (PCATT). There are two courses that you may be interested in, it is recommended that they are taken in the order shown to reap maximum benefit.

You can also access the reviews of the previous session in 2013 at the PCATT site.

Introduction to Structured Query Language (SQL)

 

SQL Server Reporting Services 2012: Native Mode Implementation

Please register with  http://pcatt.org/registration    or send an email to gcerny@hawaii.edu

Query Designer in SQL Server 2012 Express

 Running queries is one of the basic tasks that you undertake. You have two options in SQL Server 2012 Express (also in all other versions), use T-SQL and execute the statements in the query pane of SQL Server 2012 or use the query designer. You will be able to design the query in the designer but you will not be able to execute inside the designer. Once you design the query in the editor the T-SQL code that gets generated is copied to the query pane. Once the T-SQL code is in the pane you can execute (using the Toolbar item EXECUTE(!) to run the query

There are two ways you can bring up the query designer

Step 1. Open a Query pane by right clicking either the Server Node or right clicking one
of the user databases as shown.

 SERVER NODE

USER DATABASE NODE

Step 2. Once the query pane is open, click inside the query pane. The main menu
changes
to display the Query menu item as shown.

 

Step 3.Click Query menu item to display a drop-down list and click on Design Query in Editor or Ctrl+Shift+Q

 

This should bring up the designer as shown.

Step 3b. Another way is to right click inside the query pane and choose Design Query in Editor as before.