Skip to content

Instantly share code, notes, and snippets.

@SQLDBAWithABeard

SQLDBAWithABeard/ForGitHub.ipynb

Created May 1, 2020 11:13
Show Gist options
  • Save SQLDBAWithABeard/2c85a8d81cf8a9cc7a646afae4514ee7 to your computer and use it in GitHub Desktop.
Save SQLDBAWithABeard/2c85a8d81cf8a9cc7a646afae4514ee7 to your computer and use it in GitHub Desktop.
Download ZIP
public github notebook
Display the source blob
Display the rendered blob
Raw
ForGitHub.ipynb
{
"metadata": {
"kernelspec": {
"name": "SQL",
"display_name": "SQL",
"language": "sql"
},
"language_info": {
"name": "sql",
"version": ""
}
},
"nbformat_minor": 2,
"nbformat": 4,
"cells": [
{
"cell_type": "markdown",
"source": [
"# Comparing notebooks test\r\n",
"\r\n",
"I want to be able to easily compare results of running a notebook twice"
],
"metadata": {
"azdata_cell_guid": "61a5aae9-d5ae-4840-b95e-819649d24058"
}
},
{
"cell_type": "markdown",
"source": [
"Create a table"
],
"metadata": {
"azdata_cell_guid": "32d69704-2f22-4a44-9c77-bcafb27bf64a"
}
},
{
"cell_type": "code",
"source": [
"Use tempdb\r\n",
"\r\n",
"\r\n",
" ; WITH\r\n",
" L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2^1\r\n",
" L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), -- 2^2\r\n",
" L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), -- 2^4\r\n",
" L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), -- 2^8\r\n",
" L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), -- 2^16\r\n",
" L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), -- 2^32\r\n",
" Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k FROM L5)\r\n",
"\r\n",
"select k as StuffHere , k/2 as JunkHere, k/4 as DuffStuffHere\r\n",
" into StuffAndJunk\r\n",
"from nums\r\n",
"where k <= 1000000\r\n",
"\r\n",
" ; WITH\r\n",
" L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2^1\r\n",
" L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), -- 2^2\r\n",
" L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), -- 2^4\r\n",
" L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), -- 2^8\r\n",
" L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), -- 2^16\r\n",
" L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), -- 2^32\r\n",
" Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k FROM L5)\r\n",
"\r\n",
"select k as StuffHere , k/2 as JunkHere, k/4 as DuffStuffHere\r\n",
" into OrderedList\r\n",
"from nums\r\n",
"where k <= 1000000\r\n",
"\r\n",
"ALTER TABLE [dbo].[OrderedList] ALTER COLUMN StuffHere INT NOT NULL\r\n",
"\r\n",
"CREATE UNIQUE CLUSTERED INDEX [OrderList_index] ON [dbo].[OrderedList] -- don't judge I cant be bothered to name things properly\r\n",
"([StuffHere] ASC,[JunkHere],[DuffStuffHere])"
],
"metadata": {
"azdata_cell_guid": "9d92de4f-8fa7-4b24-9535-d02a2ac553ca",
"tags": [
"hide_input"
]
},
"outputs": [
{
"output_type": "display_data",
"data": {
"text/html": "(1000000 rows affected)"
},
"metadata": {}
},
{
"output_type": "display_data",
"data": {
"text/html": "(1000000 rows affected)"
},
"metadata": {}
},
{
"output_type": "display_data",
"data": {
"text/html": "Total execution time: 00:00:05.027"
},
"metadata": {}
}
],
"execution_count": 2
},
{
"cell_type": "markdown",
"source": [
"Query the table\r\n",
""
],
"metadata": {
"azdata_cell_guid": "7a1a1df3-5739-4450-93e9-0468f09df36c"
}
},
{
"cell_type": "code",
"source": [
"Use tempdb\r\n",
"\r\n",
"SELECT TOP 10 * FROM [dbo].[OrderedList] "
],
"metadata": {
"azdata_cell_guid": "11200f36-a1b3-4bd0-a12a-7a5b55165e6b"
},
"outputs": [
{
"output_type": "display_data",
"data": {
"text/html": "(10 rows affected)"
},
"metadata": {}
},
{
"output_type": "display_data",
"data": {
"text/html": "Total execution time: 00:00:00.045"
},
"metadata": {}
},
{
"output_type": "execute_result",
"metadata": {},
"execution_count": 6,
"data": {
"application/vnd.dataresource+json": {
"schema": {
"fields": [
{
"name": "StuffHere"
},
{
"name": "JunkHere"
},
{
"name": "DuffStuffHere"
}
]
},
"data": [
{
"0": "1",
"1": "0",
"2": "0"
},
{
"0": "2",
"1": "1",
"2": "0"
},
{
"0": "3",
"1": "1",
"2": "0"
},
{
"0": "4",
"1": "2",
"2": "1"
},
{
"0": "5",
"1": "2",
"2": "1"
},
{
"0": "6",
"1": "3",
"2": "1234"
},
{
"0": "7",
"1": "3",
"2": "1"
},
{
"0": "8",
"1": "4",
"2": "2"
},
{
"0": "9",
"1": "4",
"2": "2"
},
{
"0": "10",
"1": "5",
"2": "2"
}
]
},
"text/html": "<table><tr><th>StuffHere</th><th>JunkHere</th><th>DuffStuffHere</th></tr><tr><td>1</td><td>0</td><td>0</td></tr><tr><td>2</td><td>1</td><td>0</td></tr><tr><td>3</td><td>1</td><td>0</td></tr><tr><td>4</td><td>2</td><td>1</td></tr><tr><td>5</td><td>2</td><td>1</td></tr><tr><td>6</td><td>3</td><td>1234</td></tr><tr><td>7</td><td>3</td><td>1</td></tr><tr><td>8</td><td>4</td><td>2</td></tr><tr><td>9</td><td>4</td><td>2</td></tr><tr><td>10</td><td>5</td><td>2</td></tr></table>"
}
}
],
"execution_count": 6
},
{
"cell_type": "markdown",
"source": [
" I commit this with 'Run Number 1' and then update the table and run the query above again"
],
"metadata": {
"azdata_cell_guid": "cc149a84-83da-4fc0-8fb7-21821d7f1367"
}
},
{
"cell_type": "code",
"source": [
"Use tempdb\r\n",
"\r\n",
"UPDATE [dbo].[OrderedList] \r\n",
"SET DuffStuffHere = 1234\r\n",
"WHERE StuffHere = 6 AND JunkHere = 3 and DuffStuffHere = 1"
],
"metadata": {
"azdata_cell_guid": "c88b9d2e-d533-4b69-86ba-a605f839499b"
},
"outputs": [
{
"output_type": "display_data",
"data": {
"text/html": "(1 row affected)"
},
"metadata": {}
},
{
"output_type": "display_data",
"data": {
"text/html": "Total execution time: 00:00:00.035"
},
"metadata": {}
}
],
"execution_count": 5
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment