Slides for DocumentDB talk - SoCalCode Camp - USC

Latest slide deck for SQL Server Tips & Tricks for Seattle & London

Tips & Tricks SQL in the City Seattle 2014 from Ike Ellis

and here's the London video:

SQL in the City - My Session

I'm very excited to be speaking at SQL in the City this year. I'm going to try to run through as many SQL Server software development tips and tricks as possible. I've been thinking about the session and I think instead of doing 20 tricks, I'm going to try to fit in 50 tricks. That's a new trick every 60 - 90 seconds. Let's see if this works! I figure with our short attention spans, this should keep everyone engaged, learning, and laughing. Click on the banner on the right of the blog to sign up for SQL in the City. I can't wait!

Azure DocumentDB First Look

Step 1: Run the PowerShell Scripts

Step 2: Use the new Azure portal at portal.azure.com

Step 3: Run NuGet: Install-Package Microsoft.Azure.Documents.Client -Pre

Step 4: This is the Flashcard class

public class FlashCard
    {
        [JsonProperty(PropertyName = "id")]
        public string ID { get; set; }
        [JsonProperty(PropertyName = "question")]
        public string Question { get; set; }
        [JsonProperty(PropertyName = "answer")]
        public string Answer { get; set; }

    }

Step 5: This is the console application:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.Documents.Linq;
using System.Configuration;
namespace FlashCardDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            CreateSaveAndGet().Wait();
            //CleanUp().Wait();
        }

        public static async Task CreateSaveAndGet()
        {
            //connect database
            var client = GetClient();

            //find or create a database
            var database = await GetDatabase(client);

            //create a collection
            var collection = await GetCollection(client, database);

            Console.WriteLine("Save new flashcards...");
            var flashcard1 = await client.CreateDocumentAsync(collection.SelfLink, new FlashCard()
            {
                ID = "1",
                Question = "When did Azure DocumentDB release in preview?",
                Answer = "August 21st, 2014",
            });

            var flashcard2 = await client.CreateDocumentAsync(collection.SelfLink, new FlashCard()
            {
                ID = "2",
                Question = "What is Azure DocumentDBs twitter handle?",
                Answer = "@DocumentDB",
            });

           
            var flashcards = await Task<List<FlashCard>>.Run(() =>
            client.CreateDocumentQuery<FlashCard>(collection.DocumentsLink)
                .AsEnumerable()
                .ToList<FlashCard>());

            Console.WriteLine("Iterating through flashcards...");
            foreach (var flashcard in flashcards)
            {
                Console.WriteLine("QUESTION " + flashcard.ID.ToString() + ": " + flashcard.Question );
                Console.WriteLine("Answer: " + flashcard.Answer);
                Console.WriteLine("Press a key");
                Console.ReadKey();
            }
            Console.ReadKey();


            var flashcardGet = await Task<FlashCard>.Run(() =>
                client.CreateDocumentQuery<FlashCard>(collection.DocumentsLink)
                    .Where(d => d.ID == "1")
                    .AsEnumerable()
                .FirstOrDefault());

            Console.WriteLine(flashcardGet.Question);
            Console.ReadKey();

            var doc = client.CreateDocumentQuery<Document>(collection.DocumentsLink)
                    .Where(d => d.Id == flashcardGet.ID)
                    .AsEnumerable().FirstOrDefault();

            await client.DeleteDocumentAsync(doc.SelfLink);

            flashcardGet = await Task<FlashCard>.Run(() =>
                client.CreateDocumentQuery<FlashCard>(collection.DocumentsLink)
                    .Where(d => d.ID == "1")
                    .AsEnumerable()
                .FirstOrDefault());

            Console.WriteLine(flashcardGet.Question);
            Console.ReadKey();
        }

        private static async Task CleanUp()
        {
            //connect database
            var client = GetClient();

            //find or create a database
            var database = await GetDatabase(client);

            //create a collection
            var collection = await GetCollection(client, database);
            await client.DeleteDocumentCollectionAsync(collection.SelfLink);

        }
        private static DocumentClient GetClient()
        {
            string endpoint = ConfigurationManager.AppSettings["EndPoint"];
            string authKey = ConfigurationManager.AppSettings["AuthKey"];

            Uri endpointUri = new Uri(endpoint);
            var client = new DocumentClient(endpointUri, authKey);
            return client;
        }
        private static async Task<Database> GetDatabase(DocumentClient client)
        {
            Database database;
            var databaseName = "flashcards";
            var databases = client.CreateDatabaseQuery()
                .Where(db => db.Id == databaseName).ToArray();

            if (databases.Any())
            {
                database = databases.First();
            }
            else
            {
                database = new Database { Id = databaseName };
                database = await client.CreateDatabaseAsync(database);
            }
            return database;
        }
        private static async Task<DocumentCollection> GetCollection(DocumentClient client, Database database)
        {
            var collectionName = "flashcards";
            DocumentCollection collection;

            var collections = client.CreateDocumentCollectionQuery(database.SelfLink)
                .Where(col => col.Id == collectionName).ToArray();

            if (collections.Any())
            {
                collection = collections.First();
            }
            else
            {
                collection = await client.CreateDocumentCollectionAsync(database.SelfLink,
                    new DocumentCollection {Id = collectionName});
            }
            return collection;
        }

     

     
    }
}

Video of Learn JavaScript Properly Part 1 - SQL Pass Book Readers

SQL Tip: The Importance of Changing Schema

SQL Tip: How to share passwords over email

SQL Tip: Change coloring on status bar of SSMS

New SQL Tip - SET XACT_ABORT ON

I have a new SQL Tip about SET XACT_ABORT.  If you're unfamiliar with this setting, you should definitely spend three minutes and watch this tip:



CREATE TABLE t1(
col1 INT PRIMARY KEY)

GO

INSERT INTO t1
VALUES
(1)
,(2)
,(3)
,(4)-- Assuming SET IMPLICIT_TRANSACTIONS is OFF

INSERT INTO t1
VALUES(1)

INSERT INTO t1
VALUES(99)
-- Which rows get inserted?
SELECT * FROM t1-- Which rows get inserted for this explicit transaction?
BEGIN TRANSACTION;
   INSERT INTO t1
   VALUES
   (1)

   INSERT INTO t1
   VALUES
   (100)
COMMIT TRANSACTION;
GO-- Which rows get inserted?  Did 100 get inserted?
SELECT * FROM t1-- With SET XACT_ABORT?
SET XACT_ABORT ON;
BEGIN TRANSACTION;
   INSERT INTO t1
   VALUES
   (1)

   INSERT INTO t1
   VALUES
   (200)
COMMIT TRANSACTION;
GO
DROP TABLE t1



New SQL Tip: The Mouse Middle Button

Proper Dashboard Design

Made a new SQL tip:  Dashboard design - 2 minutes - 

Against the Cloud

I talk to a lot of people about their opinions about the cloud.  I find that most sysadmins, network managers, and IT-Pro staff members are strongly against the cloud.  I repeatedly hear the same reasons why they don't like it:

1)  The company will lose control of their resources and will have to trust a 3rd party.

2)  The 3rd party will not be as responsive to tech support issues as on premise IT guys.

3)  The cloud goes down a lot.

4)  What happens if your cloud vendor goes out of business?  

5)  Once you choose a cloud vendor, you're tied to it.

6)  They won't customize their offerings to fit our special needs.

When I talk to developers about the cloud, they love it.  When I ask them why they don't just stay on premise, they answer with these reasons:

1)  Internal IT is too slow to respond to our needs.

2)  Internal IT is too overworked to do what I ask them to do when I ask them to do it.

3)  It's too expensive.

4)  Internal IT tends to go down a lot, while the cloud stays up more often.

5)  Internal IT knows that I'm a captive customer.  Unless I have executive buy-in they will ignore me.

6)  Internal IT won't customize their offerings to fit my specific needs.

I think the real reason why IT-Pro staff don't like the cloud is that they don't all want to end up working for Amazon, Microsoft, or Google.  I think they realize that once all of the infrastructure is in the cloud, that's where their jobs will go.  I think all of the other reasons are really minor compared to that central one.

I think developers love the cloud because they gain control over the resources where they felt they lose them when it goes on-prem.  I also think C-levels love it because it's cheaper and more predictable.

Good Quote for Business Intelligence

"An ounce of hard data is worth a ton of speculation"

SQL Pass Architecture Slides

SQL Pass Architecture SQL Tips & Tricks from Ike Ellis