Is the leading open data science platform and is the recommended tool for use with Theano.  It is a free, easy to install python distribution and package manager that has a collection of over 720 open source package.  You can find a cheat sheet at the following location: Anaconda cheat sheet

• Install Anaconda x64.
• Open the Anaconda prompt

Conda is the Anaconda command line for managing packages.  We will be using this to install all the python related packages required. Before doing this we must ensure we have the most up to date version of Conda.  To do this type the following in the Anaconda prompt:

conda update conda

Next, we update Anaconda and all installed packages. To do this type the following in the Anaconda prompt:

</code>conda update --all

Next, we install the packages required for Theano. To do this type the following in the Anaconda prompt:

conda install numpy scipy mkl-service libpython mingw m2w64-toolchain nose sphinx git

Install Theano

Theano is a Python library that allows you to define, optimize, and evaluate mathematical expressions involving multi-dimensional arrays efficiently. It can use GPUs and perform efficient symbolic differentiation.  To install the latest version of Theano type in the following in the Anaconda prompt:

pip install git+git://github.com/Theano/Theano.git

Install Keras

Keras is a high-level deep learning library written in Python which runs on top of either  TensorFlow, CNTK, or Theano.  It was developed so that developers can easily create convolutional and/or recurrent networks.  To install the latest version of Keras, type in the following in the Anaconda prompt:

pip install git+git://github.com/fchollet/keras.git

SELECT LTRIM(RTRIM('      hello world    '))

One thing to note is that TRIM with the LEADING parameter and ‘ ‘ as the character to be trimmed is exactly the equivalent of the LTRIM function in t-sql;  TRIM with the TRAILING parameter and ‘ ‘ as the character to be trimmed is exactly the equivalent of the RTRIM function in t-sql.

You can also create a user defined function for TRIM in your database.  The following listing below will do that for you.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Carlos Ferreira
-- Create date: January 29 2014
-- Description:	TRIM function (similar to Oracle)
-- =============================================
CREATE FUNCTION Trim
(
		@string Varchar(Max)
)
RETURNS Varchar(Max)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @stringTrimed Varchar(Max)

	SET @stringTrimed = ltrim(rtrim(@string))

	-- Return the result of the function
	RETURN @stringTrimed
END
GO

After running the above script you could test it by running the following:

SELECT dbo.Trim(' test me ')

References

 PL/SQL TRIM

TSQL LTRIM

TSQL RTRIM

Below I will now show how to write CSV files using CsvHelper.  The example will actually be reading a CSV file and then writing the contents of that CSV into another but doing it three times.  Each time we do this we will use another method (WriteRecords for all records at once, WriteRecord for writing one record, and WriteField for writing one field).

Our sample input CSV file will look like the following.

CommonName,FormalName,TelephoneCode,CountryCode

Argentina,Argentine Republic,54,ARG
Armenia,Republic of Armenia,374,ARM
Australia,Commonwealth of Australia,61,AUS
Austria,Republic of Austria,43,AUT
“Bahamas, The”,Commonwealth of The Bahamas,-241,BHS
Bangladesh,People’s Republic of Bangladesh,880,BGD

Note this CSV file is a simpler file based on the file which is located here.

The first thing to do is to Install CsvHelper.  To do this run the following command in the Package Manager Console:

Install-Package CsvHelper

Now add CsvHelper to the program by adding:

using CsvHelper;

The next step is to create a class which has properties with the same name of the column headings found in the csv file.  Below you will find an example of a class which does this:

class DataRecord
    {
        //Should have properties which correspond to the Column Names in the file
        //i.e. CommonName,FormalName,TelephoneCode,CountryCode
        public String CommonName { get; set; }
        public String FormalName { get; set; }
        public String TelephoneCode { get; set; }
        public String CountryCode { get; set; }
    }

Finally create an instance of CSVReader and CSVWriter  and invoke the GetRecords method using the DataRecord class to read the CSV file. 

var reader = new CsvReader(sr); 
var writer = new CsvWriter(sw);

IEnumerable records = reader.GetRecords<DataRecord>().ToList();

Once we have the completed we are now ready to write the file out.  We first will write the entire file out by using the WriteRecords method.  Note this method will also normally write the header out automatically.

writer.WriteRecords(records);

We then write the records out again but this time we will do so by iterating thru the records collection and writing the entire record using the WriteRecord method and also by writing the fields of the record using the WriteField method.

foreach (DataRecord record in records)
                    { 
                       //Write entire current record
                       writer.WriteRecord(record);

                       //write record field by field
                       writer.WriteField(record.CommonName);
                       writer.WriteField(record.FormalName);
                       writer.WriteField(record.TelephoneCode);
                       writer.WriteField(record.CountryCode);
                       //ensure you write end of record when you are using WriteField method
                       writer.NextRecord();
                    }

One point to note that if you were writing a file using the WriteRecord or WriteField methods, you will not get a header record in the file.   If you wish to have a header record us the WriteHeader method (example is commented out in final code).

A completed example can be found below:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CsvHelper;

namespace CSVHelperWriteSample
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var sr = new StreamReader(@"countrylist.csv"))
            {
                using (var sw = new StreamWriter(@"countrylistoutput.csv"))
                {
                    var reader = new CsvReader(sr); 
                    var writer = new CsvWriter(sw);

                    //CSVReader will now read the whole file into an enumerable
                    IEnumerable records = reader.GetRecords<DataRecord>().ToList();

                    //Write the entire contents of the CSV file into another
                    writer.WriteRecords(records);

                    //Now we will write the data into the same output file but will do it 
                    //Using two methods.  The first is writing the entire record.  The second
                    //method writes individual fields.  Note you must call NextRecord method after 
                    //using Writefield to terminate the record.

                    //Note that WriteRecords will write a header record for you automatically.  If you 
                    //are not using the WriteRecords method and you want to a header, you must call the 
                    //Writeheader method like the following:
                    //
                    //writer.WriteHeader<DataRecord>();
                    //
                    //Do not use WriteHeader as WriteRecords will have done that already.

                    foreach (DataRecord record in records)
                    { 
                       //Write entire current record
                       writer.WriteRecord(record);

                       //write record field by field
                       writer.WriteField(record.CommonName);
                       writer.WriteField(record.FormalName);
                       writer.WriteField(record.TelephoneCode);
                       writer.WriteField(record.CountryCode);
                       //ensure you write end of record when you are using WriteField method
                       writer.NextRecord();
                    }
                }
            }
        }
    }
}

You can download this sample code with CsvHelper and CSV file here:

SQL copy tables to new table

When performing this command, a table will created using the same data types as the columns which as selected in the select statement.  This is usually very useful when making a copy of the existing table before making any alterations to the data.

T-SQL:

SELECT * 
INTO Sales.CustomerBackup 
FROM Sales.Customer;

PL/SQL:

CREATE TABLE CustomerBackup 
AS 
SELECT * FROM Customer;

SQL copy tables to existing table

These following when executed will insert data into already existing table.  It will match fields based on name if you choose to list the fields in the select clause.  This normally useful for when you need to reinsert data (don’t ask why… )

T-SQL:

INSERT INTO Sales.CustomerBackup (PersonID, StoreID, ModifiedDate, AccountNumber, rowguid)  
SELECT PersonID, StoreID, ModifiedDate, AccountNumber, rowguid FROM Sales.Customer;

PL/SQL:

INSERT INTO CustomerBackup 
SELECT * FROM Customer;

I need a way to roll-up multiple rows into one row and one column.  In essence I needed to convert something that looked like this:

Original SubCategories Listing

USE AdventureWorks2012; 
GO 
SELECT C.Name, 
       SC.Name 
FROM   [Production].[ProductCategory] C 
       LEFT JOIN [Production].[ProductSubcategory] SC 
              ON C.ProductCategoryID = SC.ProductCategoryID;

SubCategories formatted data

The Solution:

The solution I will show will make use of the FOR XML PATH clause and STUFF commands in T-SQL.

FOR XML PATH Clause

Will will make use of the XML functionality of SQL Server to amalgamate the series of rows into one row.  In order to create a XML string you must append the FOR XML command after any regular query.   There are four options to append to the FOR XML PATH command which will alter what the result will look like.  These options are either RAW, AUTO, EXPLICIT, or PATH.  For this solution we will use the PATH option (will explain these four option in more detail in a later post).

The following query shows a regular query which retrieves the subcategory name in an XML string

SELECT Name from [Production].[ProductSubcategory] FOR XML PATH('');

The results look like the following:

 You will notice that you get a formatted XML string without a root node.  To convert this list to a CSV string we just need to append a comma (“,”)  to the name field.

The following sql query demonstrates the XML string being converted to a CSV string:

SELECT ',' + Name from [Production].[ProductSubcategory] FOR XML PATH('');

CSV Result

This is getting a bit closer to the results we want, but we if you haven’t noticed the string begins with a comma which we should remove.  In order to do this we will use the t-sql STUFF function

STUFF() Function

The syntax of the t-sql STUFF function looks like the following:

STUFF ( character_expression , start , length , replaceWith_expression )

To remove the first character of the string all we need to so is do something like the following:

Stuff( result of the query listed above, 1, 1, '')

This says, starting with the first 1 character place, replace 1 characters with nothing ''.

What does the final solution look like?

USE AdventureWorks2012
GO
SELECT CAT.Name AS [Category],
	   SubCatName =
            STUFF((    SELECT ','+ SUB.Name AS [text()]
                        FROM Production.ProductSubcategory SUB
                        WHERE
                        SUB.ProductCategoryID = CAT.ProductCategoryID
                        FOR XML PATH('') 
                        ), 1, 1,'')
FROM  Production.ProductCategory CAT;

• • STUFF (Transact-SQL)
  • FOR XML (SQL SERVER)
  • CSV

I recently received a script which creates a database and loads data.  I was unfortunately too large for SQL Server Management Studio to process.

Solution:

You can use the sqlcmd utility to run the script file. The example below shows the execution of a script file called script.sql which is located on the C drive and a connection is made to the SQL Server using integrated security.

To run the script file

1. Open a windows command prompt window.
2. In the command prompt window, type: sqlcmd –S SQLServerName-i C:\DatabaseLoad.sql
3. Press ENTER.

Method 1:

This method will only work in SQL Server 2005 and later; if you need to remove duplicates from an earlier version please upgrade you SQL Server version.  Just kidding!  Use the technique in method 2.  Actually you really should upgrade your version of SQL Server…

This method uses the Common Table Expression functionality and row_number function which was introduced in Microsoft SQL Server 2005.   The key strategy to this method is to generate a number next to the  records which denotes the row number.

So in essence we want to transform this:

Duplicated Records

To this:

Duplicated Results with Numbers

And remove all instances of those rows which the row number column do not correspond to 1.

Below is a script which can be used to demonstrate this.   Also you can use this link in SQL Fiddle.

-- Create Table
CREATE TABLE PhoneticAlphabet (Alphabet char(1),  CodeWord varchar(20))

-- Load Sample Data in Table
INSERT INTO  PhoneticAlphabet VALUES ('A', 'Alfa')
INSERT INTO  PhoneticAlphabet VALUES ('B', 'Brave')
INSERT INTO  PhoneticAlphabet VALUES ('C', 'Charlie')
INSERT INTO  PhoneticAlphabet VALUES ('D', 'Delta')
INSERT INTO  PhoneticAlphabet VALUES ('E', 'Echo')
INSERT INTO  PhoneticAlphabet VALUES ('F', 'Foxtrot')
INSERT INTO  PhoneticAlphabet VALUES ('G', 'Gulf')
INSERT INTO  PhoneticAlphabet VALUES ('H', 'Hotel')
INSERT INTO  PhoneticAlphabet VALUES ('I', 'India')
INSERT INTO  PhoneticAlphabet VALUES ('J', 'Juliett')
INSERT INTO  PhoneticAlphabet VALUES ('A', 'Alfa')
INSERT INTO  PhoneticAlphabet VALUES ('B', 'Brave')
INSERT INTO  PhoneticAlphabet VALUES ('C', 'Charlie')
INSERT INTO  PhoneticAlphabet VALUES ('D', 'Delta')
INSERT INTO  PhoneticAlphabet VALUES ('E', 'Echo')
INSERT INTO  PhoneticAlphabet VALUES ('F', 'Foxtrot')
INSERT INTO  PhoneticAlphabet VALUES ('G', 'Gulf')
INSERT INTO  PhoneticAlphabet VALUES ('H', 'Hotel')
INSERT INTO  PhoneticAlphabet VALUES ('I', 'India')
INSERT INTO  PhoneticAlphabet VALUES ('J', 'Juliett')

--See table before deletion
SELECT * FROM PhoneticAlphabet ORDER BY Alphabet;

--Display what the table will look like when we sort it by Alphabet and CodeWord
--and use the row_number function over the dataset see http://technet.microsoft.com/en-us/library/ms186734.aspx
--for infromation of row_number function.
SELECT
  Alphabet
  , CodeWord
  , row_number() over (partition by Alphabet, CodeWord order by Alphabet) as rn
FROM PhoneticAlphabet;

--Create a Common Table Expression and delete all rows which value greater than 1
WITH PhoneticAlphabetWithRows AS (
SELECT
  Alphabet
  , CodeWord
  , row_number() over (partition by Alphabet, CodeWord order by Alphabet) as rn
FROM PhoneticAlphabet
)
DELETE PhoneticAlphabetWithRows where rn > 1;

--See the table after deletion
select * from PhoneticAlphabet ORDER BY Alphabet;

Method 2:

This method should work on all versions for SQL Server.  It basically works by copying the unique rows out of the original table into a temporary table, and then deleting all records from the original table;  the unique rows from the temporary table are then copied into the original table.

Link to SQL Fiddle is here.

— Create Table

CREATE TABLE PhoneticAlphabet (Alphabet char(1),  CodeWord varchar(20))

-- Load Sample Data in Table
INSERT INTO  PhoneticAlphabet VALUES ('A', 'Alfa')
INSERT INTO  PhoneticAlphabet VALUES ('B', 'Brave')
INSERT INTO  PhoneticAlphabet VALUES ('C', 'Charlie')
INSERT INTO  PhoneticAlphabet VALUES ('D', 'Delta')
INSERT INTO  PhoneticAlphabet VALUES ('E', 'Echo')
INSERT INTO  PhoneticAlphabet VALUES ('F', 'Foxtrot')
INSERT INTO  PhoneticAlphabet VALUES ('G', 'Gulf')
INSERT INTO  PhoneticAlphabet VALUES ('H', 'Hotel')
INSERT INTO  PhoneticAlphabet VALUES ('I', 'India')
INSERT INTO  PhoneticAlphabet VALUES ('J', 'Juliett')
INSERT INTO  PhoneticAlphabet VALUES ('A', 'Alfa')
INSERT INTO  PhoneticAlphabet VALUES ('B', 'Brave')
INSERT INTO  PhoneticAlphabet VALUES ('C', 'Charlie')
INSERT INTO  PhoneticAlphabet VALUES ('D', 'Delta')
INSERT INTO  PhoneticAlphabet VALUES ('E', 'Echo')
INSERT INTO  PhoneticAlphabet VALUES ('F', 'Foxtrot')
INSERT INTO  PhoneticAlphabet VALUES ('G', 'Gulf')
INSERT INTO  PhoneticAlphabet VALUES ('H', 'Hotel')
INSERT INTO  PhoneticAlphabet VALUES ('I', 'India')
INSERT INTO  PhoneticAlphabet VALUES ('J', 'Juliett')

--Lets see the duplicated records
SELECT * FROM PhoneticAlphabet;

--Insert the distinct rows from the duplicate rows table to a new temporary table.
SELECT DISTINCT * INTO #tmp FROM [PhoneticAlphabet];

--Delete all the rows from the original table
DELETE FROM PhoneticAlphabet;

--Copy the distinct rows from the temporary table back into the original table (which was empty).

INSERT INTO PhoneticAlphabet
SELECT * FROM #tmp; 

DROP TABLE #tmp;

--Lets now see the table without duplicates
SELECT * FROM PhoneticAlphabet;

1) Create a lock on a table.  

The example below demonstrates a lock on the Person table in the AdventureWorks2012 sample table.    The key points which make this work is to create a transaction, and then issuing a query using the TABLOCKX and HOLDLOCK table lock hints.  This will force an exclusive lock on the table.  To keep the lock for significant amount of time we specify that the transaction stays alive for a minute by introducing the WAITFOR DELAY ’00:01:00′ command, which will prevent any further execution of statements until a minute has passed.

USE AdventureWorks2012;
GO
BEGIN TRANSACTION
SELECT * FROM Person.Person WITH (TABLOCKX, HOLDLOCK);
WAITFOR DELAY '00:01:00' ---Wait a minute!
ROLLBACK TRANSACTION
--Release the lock

2)  Now open up another session in SQL Server (new query from Microsoft SQL Server Management Studio), and execute the following query:

USE AdventureWorks2012;
GO
SELECT * FROM Person.Person;

You will notice that after this query is excuted, the query seems to run forever.  It will actually never  execute until the query in step 1 is finished.

3) Check for blocked transaction.  You can find this by opening up an new query in Microsoft SQL Server Management Studio and executing the following query:

USE [master]
GO
SELECT  session_id
 ,db_name(database_id) as 'Database_Name'
 ,sql_text.text as 'SQL_Blocked'
 ,blocking_session_id
 ,wait_time
 ,wait_type
 ,last_wait_type
 ,wait_resource
 ,transaction_isolation_level
 ,lock_timeout
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as sql_text
WHERE blocking_session_id != 0

I will explain in more detail how this blocking query works in a later post.

If you have any other tips on how to create a blocked transaction please share them in the comments below.

CommonName,FormalName,TelephoneCode,CountryCode
Argentina,Argentine Republic,54,ARG
Armenia,Republic of Armenia,374,ARM
Australia,Commonwealth of Australia,61,AUS
Austria,Republic of Austria,43,AUT
“Bahamas, The”,Commonwealth of The Bahamas,-241,BHS
Bangladesh,People’s Republic of Bangladesh,880,BGD

Note this CSV file is a simpler file based on the file which is located here.

The first thing to do is to Install CsvHelper.  To do this run the following command in the Package Manager Console:

Install-Package CsvHelper

Now add CsvHelper to the program by adding:

using CsvHelper;

The next step is to create a class which has properties with the same name of the column headings found in the csv file.  Below you will find an example of a class which does this:

class DataRecord
    {
        //Should have properties which correspond to the Column Names in the file
        //i.e. CommonName,FormalName,TelephoneCode,CountryCode
        public String CommonName { get; set; }
        public String FormalName { get; set; }
        public String TelephoneCode { get; set; }
        public String CountryCode { get; set; }
    }

Finally create an instance of CSVReader and invoke the GetRecords method using the DataRecord class.  Below you will find an example of this:

var reader = new CsvReader(sr);
IEnumerable<DataRecord> records = reader.GetRecords<DataRecord>();

An completed example can be found below:

using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using CsvHelper;

namespace CSVHelperReadSample
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            using (var sr = new StreamReader(@"countrylist.csv"))
            {
                var reader = new CsvReader(sr);

                //CSVReader will now read the whole file into an enumerable
                IEnumerable<DataRecord> records = reader.GetRecords<DataRecord>();

                //First 5 records in CSV file will be printed to the Output Window
                foreach (DataRecord record in records.Take(5)) 
                {
                    Debug.Print("{0} {1}, {2}, {3}", record.CommonName, record.CountryCode, record.FormalName,
                        record.TelephoneCode);
                }
            }
        }
    }
}

You can download this sample code with  CsvHelper and CSV file here: CVSHelperReadSample