SELECT
OBJECT_SCHEMA_NAME(a2.object_id) AS SchemaName,
a2.name AS TableName,
a1.rows as [RowCount],
CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB,
CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB,
CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB,
CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB
FROM
(SELECT
ps.object_id,
SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY ReservedSize_MB DESC
Similar to last year, I wanted to share a variety of types of data visualizations, and also ones that were relevant with particular events that happened this year.
Let’s dive into it!
Be sure toSUBSCRIBE hereto never miss another article on data science guides, tricks and tips, life lessons, and more!
1. NATO vs Russia
One of the biggest events this year was the war between Russia and Ukraine. Comforting or not,the infograph aboveshows the difference in military power between NATO and Russia. The actual data can be foundhere.
I love this infograph, it’s many pictographs combined into one, it’s clean, and it’s very clear what message it’s trying to convey.
NATO out-powers Russia in every aspect other than Nuclear Weapons… I wonder how this would change if Russia’s budget towards nuclear weapons went to other things 🤔.
Be sure toSUBSCRIBE hereto never miss another article on data science guides, tricks and tips, life lessons, and more!
2. Inflation and the cost of everyday items
One of the consequences of the war between Russia and Ukraine is inflation. If youclick on the visualizationabove, you’ll see how inflation has impacted our cost of every day items, like gas, coffee, and corn. (On the bright side, the cost of orange juice went down!)
If you’re interested, this type of data visualization is similar to abar chart race, which is a dynamic bar chart shown over a period of time. If you want to build one yourself,here’s a tutorialthat you can check out.
3. More on inflation and wages!
Every day items aren’t the only things affected by inflation, wages are also impacted by inflation. What does this mean? As inflation increases, the value of the dollar decreases, meaning our dollar doesn’t go as far as it used to.
This visualizationis a dynamic line chart that shows how wage growth and inflation has changed since 2015. In 2021, for the first time since 2015, inflation surpassed wage growth.
4. School Shootings
Sometimes, a static bar chart is all you need to convey a message.This visualizationshows the number of school shootings by country from 2009 to 2018. If this doesn’t imply that the US has a gun problem, I don’t know what does!
FYI, the US has48 times moreschool shootings than the second highest country… fourty eight!
Be sure toSUBSCRIBE hereto never miss another article on data science guides, tricks and tips, life lessons, and more!
5. What are people studying in school?
While we’re on the topic of school,the image aboveshows the fastest growing and shrinking fields of study in US colleges. STEM fields seem to make up the fastest growing fields, while arts and history fields seem to make up the fastest shrinking fields.
This data was provided by the U.S. Department of Education.
6. Time it takes for a hacker to brute force your password in 2022
Ever wonder why certain websites require a variety of characters and a minimum number ?This visualizationshows the time that it takes a hacker to brute force your password in 2022.
What makes this visualization so powerful is how comprehensive it is — part of this is attributed to the colour scheme depending on how long it takes to brute for the password.
7. Most popular web browsers over the last 28 Years
Now for the “Most Popular” visualizations of the year,this visualizationshows the most popular web browsers over the last 28 years! As of March 2022, it’s no surprise that Google Chrome takes 80% of the market share, but that wasn’t the case back in the day.
This type of visualization is called a pie chart race, and it’s serves a similar purpose as as bar chart race, except that it’s more useful when you’re trying to emphasize proportions as opposed to absolute numbers.
The data used to build this was taken from the following sources:
W3Schools (Jul-99 to present)
WebSideStory (Feb-99 to Jun-06)
GVU WWW user survey (Jan-94 to Oct-98)
EWS Web Server at UIUC (Jun-96 to Dec-98)
8. Most Popular websites since 1993
The most popular web browsers is one thing, but the most popular websites is another.This visualizationshows the most popular websites since 1993. What’s surprising is that Yahoo is still the ninth most visited website as of January 2022!
This type of data visualization is called abar chart race.I’m sure you’ve seen many of these all over YouTube and Reddit.
9. Most spoken languages in the world
Another simple yet powerful visualization,this bar chartshows the most spoken languages in the world, with the top three being English, Mandarin, and Hindi.
This visualization was created using ggplot in R with data provided fromWikipedia.
Be sure toSUBSCRIBE hereto never miss another article on data science guides, tricks and tips, life lessons, and more!
10. Biggest Fast Food Chains
For the last article,this visualizationshows the top 50 biggest fast food chains based on the number of stores in the US. You can see that it’s split by “food category”, and within each category, the size of each restaurant represents its magnitude.
Who know that there were more Subways and Starbucks than McDonalds?
This visualization is called a treemap and is typically used when you want to visualize hierarchical and partitioned data. If you want to learn how to build one in Python, check out thislink.
