Category Archives: SQL Server

Installing SQL Server using PowerShell Desired State Configuration (DSC)

Posted on by Leave a comment

Note: DSC can only be run if you have at least Windows Management Framework 4.0 and if your operating system is at least Windows 8.1 or Windows Server 2012 R2.

The PowerShell team has released the xSQLPs module as part of the Desired State Configuration (DSC) Resource Kit. xSQLPs contains xSqlServerInstall, which you can use as a starting script when installing SQL Server via PowerShell and DSC. Download the module here.

This is what I needed to do to install SQL Server using DSC and the xSQLPs module. Many of these steps are from the xSQLPs documentation page.

Read more

Microsoft Big Data Hackathon is happening in Vancouver, April 18-19, 2015

Posted on by Leave a comment

Microsoft Big Data Hackathon April2015

The Microsoft Big Data Hackathon is happening in Vancouver. (Register here)

When
April 18-19, 2015 (Saturday and Sunday)

Where  
Simon Fraser University – Morris J Wosk Centre for Dialogue (Map)
580 W Hastings St
Vancouver British Columbia V6B 1L6
Canada

How
You will need to register for the event. Register here.
To participate, you will need to be part of a 3-5 member team.

Read more

Data Sets for BI/Analytics/Visualization Projects

Posted on by 2 comments

Just wanted to compile a list of datasets, or sources of datasets, that can be used for BI/Analytics/Visualization projects and explorations (not limited or specific to any tools). These come in different formats, and some may need to be cleaned up. Please do read the restrictions and EULAs that come with each of the links/sources.

This is a work in progress. I  am still updating this post, and will most likely re-arrange or re-categorize the links as I stumble across other data sets.

If you have any that you can share, I would love to add those to this list (and mention you shared it!) – please leave a comment below and I will add them to the list!

Last Updated : February 01, 2015

Canadian Data

US Data

Read more

Microsoft Second Shot through May 31, 2015

Posted on by Leave a comment

Microsoft Second Shot is back for 2015! If you’re planning to upgrade your Microsoft certifications (MCSE, MCSA, MCSD), you might want to take advantage of this insurance. You will be eligible for a free retake (if you don’t pass the first time) if you take your exam between January 5, 2015 and May 31, 2015.

Check all the details here:

https://www.microsoft.com/learning/en-us/second-shot.aspx

By the way, Microsoft has switched to Pearson as a testing provider. You may find that testing centres could be scarce in some locations at first, hopefully that picks up soon!

 

Getting Grand Total and Subtotals using SQL Server Window Functions

Posted on by Leave a comment

Did you know that you can use SQL Server’s window function in an aggregate, and partition by NULL to get a grand total?

Let me illustrate. In the example below I use my trusty ol’ pubs database. (Hey, it’s still a great database for simple examples. Great for people starting to learn SQL too).

Let’s get some basic numbers first so we know what we are expecting to get.

Using a basic query that gets some grand total, we get 493. This the total for the whole table.

SELECT
SUM(qty)
FROM
sales

sum_sales

Read more

Writing SQL statements is easy ….

Posted on by Leave a comment

I teach a number of courses on databases, including introductory relational databases which includes introductory SQL, as well as Microsoft’s Querying SQL Server courses.

When students first learn how to write SQL, the tendency is to get too focused on getting the syntax right, and making sure the query executes. Don’t get me wrong. That’s really important especially when you’re just beginning your database journey. I have been guilty of this, and still am sometimes guilty of this.

However, blame it on age or experience, my focus has shifted from worrying about the syntax to understanding the problem at hand. I no longer jump to “coding” right away. I still worry about the syntax, but I leave the worrying for later.

Read more

« Older Entries