How to get maximum row size of a SQL Server table


I am doing a lot of ETL work (typically) and one of the particulars I want to know planning my packages design is the maximum length of a table row.

Just to expand further, it is often prudent to know the row size for future performance or for capacity planning.

So, without further ado, here is the SQL code (works on SQL Server 2005 and onward):

   1: DECLARE    @table_name NVARCHAR(115),
   2:         @1stCol NVARCHAR(115),
   3:         @sql NVARCHAR(MAX);
   4:  
   5: -- Initialize the table name to sample
   6: SET @table_name = 'THE TABLE NAME';
   7:  
   8: SELECT TOP 1 
   9:     @1stCol = name
  10: FROM sys.columns
  11: WHERE object_id = OBJECT_ID(@table_name);
  12:  
  13: -- If you need the total rows for say an eaverage then drop the TOP N clause
  14: SET @sql = 'SELECT TOP 1 ' + @1stCol + ', ROW_NUMBER() OVER (ORDER BY ' + @1stCol + ') AS [Record Number]' + ' , (0';
  15:  
  16: SELECT
  17:     @sql = @sql + ' + ISNULL(DATALENGTH(' + name + '), 1)'
  18: FROM sys.columns
  19: WHERE object_id = OBJECT_ID(@table_name)
  20: SET @sql = @sql + ') AS [Row Size in Bytes] FROM ' + @table_name + ' ORDER BY [Row Size in Bytes] DESC';
  21:  
  22: -- Optionally, print the statement
  23: PRINT @sql
  24:  
  25: -- Execute
  26: EXEC (@sql)

Using my code you can find the average row size or can calculate the total table size (e.g. using Excel)

author: Compudicted | Posted On Friday, October 3, 2014 11:55 AM | Comments (2)

Cloudera Administration Handbook by Rohit Menon, Packt Publishing Book Review


Cloudera Administration Handbook is just another great what I call 'desk companion' book, especially a must for a beginner Cloudera Administrator.

Written in a well balanced volume of material to feature coverage ratio, by a person from "the trenches" Rohit expands exactly on what a Hadoop Admin needs and should be using in retrospect to the Cloudera offerings in this area of expertize to successfully accomplish ones day-to-day tasks.
However, it is actually a lot more than just an admin's book, it also teaches how to install most of the Cloudera Hadoop ecosystem components, what components are typically in use by what in a business and how to configure each. That all is done in a thorough, precise and professional manner without any extra fuss or foofaraw.

I liked that the author expanded briefly, but nicely on the new features in Hadoop 2.0. For me the coverage on Map-Reduce appeared the most valuable. I admit it is a rough area of Hadoop.

The troubleshooting part must be the one to read on and re-read, but also high availability, backup, balancing, and security. Especially the Kerberos setup, I deem it a very necessary, yet rarely covered topic, that also appears very hard to understand, may be at least to me, but it was worth going through that very much. Overall, as an aside, CDH distribution is very extensive and feature rich no wonder a whole book can be dedicated to just this topic. The Cloudera Manager now after reading the book I must say is an awesome tool to have on board, it is just a great helper, but it requires a good book as Cloudera Administration Handbook by Rohit Menon to get acquitted with.

Have it beside you, at your desk.

Five out of five stars.

author: Compudicted | Posted On Wednesday, September 24, 2014 4:56 PM | Comments (0)

SQL Server 2014 Development Essentials by Basit A. Masood-Al-Farooq, Packt Publishing Book Review


Being “stuck” with SQL Server as a heavy user for over 16 years makes my heart tic each time I see a new book or any other reference released. Therefore, naturally I was glad to hear from a Packt Publishing representative on the opportunity to review a fresh off the press (or imaging) book SQL Server 2014 Development Essentials (publisher’s book site) by a very trusted in the #SQLFamily person as Basit.

Read it in one large gulp as the book is not lengthy at 170 + or so actually useful pages. The material is written in concise, clear manner. Besides, I expected least at as many more pages for such a complex and feature rich product.

But what did the book promise?The primary goal is to have a reader developed enough skills to deliver a successful database application.

The book targets database developers, administrators and architects.

However, the book deserves a lot of criticism, for example the many-to-many relationship in the book is represented in form of two tables, unfortunately, the true many-to-many relationship in RDBMS` cannot be achieved without an interim, third table, this will make many folks upset so I have submitted errata, but I can’t understand how Packt makes it shared for all readers. I shall continue on this note and also tell that even a greater flaw in this book exists – overall,s i it not providing enough guidance, advice or reference. I mean if a topic say on locking is covered why the author would not advocate on which locking option to use under what circumstances? The same applies to most topics. Furthermore, I was surprised almost nothing was covered about a database operating in the Cloud (Azure), CLR functions, CDC, no mention on Service Broker, Master Data Management, Data Quality, etc. the same is true to many more canned features (just too many to mention); without the aforesaid this book is of much less help to software architects and incomplete from for developers. The not so advocated to be used SQL Profiler is covered beside the Dynamic Management Views whereas I expect the database tuning and troubleshooting become a separate chapter on its own.

In short, I am disappointed this time, I just fail to see what gap this book closes and simply how it is any better than just reading on product features on Microsoft’s site, then more in-depth in BOL, MSDN and blog posts of the most prominent SQL Server industry leaders.

Two stars out of five because it may be served as a guide or read as preamble to starting developing a new SQL Server based database.

Disclaimer: this book was given to me for free by Packt Publishing in exchange to publishing a timely review.

author: Compudicted | Posted On Sunday, August 31, 2014 2:36 PM | Comments (0)

SQL Server 2014 Business Intelligence Development Beginner’s Guide by Reza Rad, Packt Publishing Book Review


Microsoft SQL Server 2014 Business Intelligence Development Beginner’s Guide

I was very happy to hear a new book was released from Packt Publishing by my fellow MVP Reza Rad, I was even happier when I heard I have an opportunity to review it because I could not expect anything else than another superb content this time, too.

It stood to its promises!

The book, even though is marketed under the “Beginner’s Guide” moniker, is actually suitable for an intermediate Business Intelligence professional. Judge yourself: the book covers as advanced topics as:

  • Data Modeling using SSAS Multidimensional and Tabular with MDX and DAX
  • Manage Master Data with MDS
  • Reveal Knowledge Driven Data Quality with DQS
  • Understand prediction and Data Mining
  • Identify data patterns
  • Design Dashboards with PerformancePoint and Power View
  • Explore Power Query and Power Map as components of Power BI
  • Create powerful reports using Visual Studio

     

    I think this list speaks for itself, it is an impressive, unprecedented coverage of topics.

    A little more about the book: it is divided into sections as to first acknowledge the reader on what are the advantages to using a specific technology, then a short deeper dive into the exact usage example summed up into a detailed coverage of how a task was tackled and what would be readers’ take-aways. The book has high quality graphics and was read on my laptop as well as on Android tablet. All that makes this book a winner in absorbing a lot of technical content in short time plus make it stick in the head.

    The book references enough external resources in case one would like to explore any topic further. In my opinion the book serves as a huge time saver in getting familiar with the latest Microsoft BI offerings and would easily allow anybody, even new to Power BI or DQS enter a Proof of Concept mode or make a quick presentation for a project stakeholder.

    I liked the most the content on predictions and the cool visualizations that can be done with Power Map and Power View. My personal opinion, Power BI overall as a data visualization and analysis platform is just sassy!

    A few notes to a prospective reader: one needs MS Office 2013 installed, have access to Microsoft Server 2012, an active Azure account and preferably run the whole software suite on a Virtual Machine.

    Verdict: I am giving this book a 5 out 5 rating.

  • author: Compudicted | Posted On Friday, August 1, 2014 4:12 PM | Comments (0)

    Let’s Celebrate Packt Publishing’s 10 th Anniversary with $10 per Book or Video!


    10 days 10 years - Home Banner

    I like to hear good news, and I was especially happy to find out Packt Publishing has reached a very significant milestone: 10 years in business!

    For us, humans, 10 years signifies the beginning of the teen years, leading to maturity, the same is true for businesses. I am sure Packt is well poised to begin its teen years as an even more IT centric educational content provider to IT professionals, students, and enthusiasts. I am sure it will continue to remain DRM free and support OSS as it did for the past 10 years.

    Happy Birthday Packt and its employees!

    You may ask why do I care so much? What ties you with Packt?

    These are valid questions and I have multiple reasons:

    Firstly, I read a lot, and Packt has offered me numerous opportunities to review hot, off the press content. I shared all my reviews in this blog and also on GoodReads. Packt allowed my to meet and made connections with many wonderful people and authors as Davide Moraschi, Kevin Dunglass, Rafal Kuc and many more. Also I had an opportunity to review books from my fellow MVPs as Reza Rad and Jen Stirrup. I highly recommend their books. Lastly, I want to personally thank Pack for being such a generous sponsor for our SQL Server 2014 Ontario Launch Party at Microsoft Canada which was such a huge success.

    On a side note, for us, the IT folks, keeping up with the technology is utterly important, even if you think you will not be able to implement something you learned at work today or become more marketable, it is good to keep ones brain in a good working order and try new things, keep abreast with what is coming and sharpen your skills.

    So avail to the offer which runs until July the 5th.

    Again, Happy Anniversary Packt!

    author: Compudicted | Posted On Monday, June 30, 2014 10:22 AM | Comments (0)

    Flask Web Development - Developing Web Applications with Python by Miguel Grinberg, O’Reilly Media Book Review


    Developing Web Applications with Python

    I remember Miguel at one of the O’Reilly educational webcasts greeting the audience in three or four languages. Energetic, full of wisdom, creativity and warmth. How happy I was when I saw his forthcoming book available for review as part of the Reader Reviewer Program? Immensely! And my expectations were met: both Miguel and Flask ROCK!

    Written in a very professional manner, concise and at the same time with a feeling that the author is your friend, advocate and teacher.

    So Flask itself, seems relatively a newcomer to the Web 2.0 stage looks complete and mature with an active, long list of committers. My questions (including to the author) were answered in no time. A very good support. Cannot be happier about the product, both book and Flask.

    So more on the book: it will guide you through the bricks and mortar of Flask and how would you use it to build a fully working, yet modern Web 2.0 App.

    I used Ubuntu 14 without any difficulty. The book is extremely well organized, it feels like I attend a Web App craft shop of a kind. Gradually climbing the ropes of a powerful giant Flask.

    Python as a language is very welcoming, yet powerful at the same time, I hardly concentrated on its internals so no solid prior knowledge of any Python is really necessary.

    Again, Miguel is a skillful teacher, even the part of the book where there was database involvement was written so I felt completely detached from what exactly is my persistence layer, even toward the end when you are ready to deploy you do not feel overwhelmed by specifics, Miguel remains neutral and unbiased to 3rd party tools, I do not know how Miguel managed to convey such material this way.

    I am sure Flask is Web 3.0 compliant as well as armed and ready for any purpose. Very inspiring!

    I give this book 5 starts, 100 out of 100 wholeheartedly. Grab it even if you hesitate.

    author: Compudicted | Posted On Monday, June 23, 2014 4:12 PM | Comments (0)

    SQL Server 2014 Ontario Launch Party is this Saturday, June the 21st!


    image

    I am very excited to remind that the SQL Server 2014 Launch Party in Ontario is this Saturday with the registration closing on Friday.

    We will have tasty food, cool swag, and top notch speakers providing with bleeding edge educational content, so… good times await!

    And this all would not be possible without our generous sponsors:

      DSI logo (WMF)

    Some glimpse on the swag: a Windows 8.1 Tablet, discount codes for all on books + win free books, hardware, software, and more!

    We will be glad to see all who registered on the event!

    Cheers and stay healthy!

    author: Compudicted | Posted On Monday, June 16, 2014 11:36 AM | Comments (2)

    Python in Finance by Yuxing Yan, Packt Publishing Book Review


    Python for Finance

    I picked Python in Finance from Packt Publishing to review expecting to bore myself with complex algorithms and senseless formulas while seeing little actual Python in action, indeed at 400 pages plus it may seem so. But, it turned out to be quite the opposite. I learned a lot about practical implementations of various Python modules as SciPy, NumPy and several more, I think they empower a developer a lot. No wonder Python is on the track to become a de-facto scientist language of choice! But I am not going to compromise the truth, the book does discuss numerous financial terms, many of them, and this is where the enormous power of this book is coming from: it is like standing on the shoulders of a giant. Python is that giant - flexible and powerful, yet very approachable. The TOC is very detailed thanks to Packt, any one can see what financial algorithms are covered, I am only going to name a few which I had most fun with (though all of them are covered in enough details): Fama*, Fat Tail, ARCH, Monte-Carlo and of course the volatility smile!

    I am under an impression this book is best suited for students in Finance, especially those who are about to join the workforce, but I suspect the material in this book is very well suited for mature Financists, an investor who has some programming skills and wants to benefit from it, or even a programmer, or a mathematician who already knows Python or any other language, but wants to have fun in Quantitative Finance and earn a few buck!

    Pure fun, real results, tons of practical insight from reading data from a file to downloading trade data from Yahoo!

    Lastly, I need to complement Yuxing – he is a talented teacher, this book could not be what it is otherwise.

    It is a 5 out of 5 product.

    Disclaimer: I received a  free copy of this book for review purposes from the publisher.

    author: Compudicted | Posted On Wednesday, June 4, 2014 3:36 PM | Comments (0)

    Building a Data Mart with Pentaho Data Integration Video Review by Diethard Steiner, Packt Publishing


    The Building a Data Mart with Pentaho Data Integration Video by Diethard Steiner from Packt Publishing is more than just a course on how to use Pentaho Data Integration, it also implements and uses the principals of the Data Warehousing (and I even heard the name of Ralph Kimball in the video). Indeed, a video watcher should be familiar with its concepts as the Star Schema, Slowly Changing Dimension types, etc. so I suggest prior to watching this course to consider skimming through the Data Warehouse concepts (if unfamiliar) or even better, read the excellent Ralph’s The Data Warehouse Tooolkit. By the way, the author expands beyond using Pentaho along to MySQL and MonetDB which is a real icing on the cake!

    Indeed, I even suggest the name of the course should be ‘Building a Data Warehouse with Pentaho’.

    To successfully complete the course one needs to know some Linux (Ubuntu used in the course), the VI editor and the Bash command shell, but it seems that similar requirements would also apply to the Windows OS. Additionally, knowing some basic SQL would not hurt. As I had said, MonetDB is used in this course several times which seems to be not anymore complex than say MySQL, but based on what I read is very well suited for fast querying big volumes of data thanks to having a columnstore (vertical data storage). I don’t see what else can be a barrier, the material is very digestible.

    On this note, I must add that the author does not cover how to acquire the software, so here is what I found may help:

    • Pentaho: the free Community Edition must be more than anyone needs to learn it. Or even go into a POC.
    • MonetDB can be downloaded (exists for both, Linux and Windows) from http://goo.gl/FYxMy0 (just see the appropriate link on the left).
    • The author seems to be using Eclipse to run SQL code, one can get it from http://goo.gl/5CcuN. To create, or edit database entities and/or schema otherwise one can use a universal tool called SQuirreL, get it from http://squirrel-sql.sourceforge.net.

     

    Next, I must confess Diethard is very knowledgeable in what he does and beyond. However, there will be some accent heard to the user of the course especially if one’s mother tongue language is English, but it I got over it in a few chapters.

    I liked the rate at which the material is being presented, it makes me feel I paid for every second Smile

    Eventually, my impressions are:

    • Pentaho is an awesome ETL offering, it is worth learning it very much (I am an ETL fan and a heavy user of SSIS)
    • MonetDB is nice, it tickles my fancy to know it more
    • Data Warehousing, despite all the BigData tool offerings (Hive, Scoop, Pig on Hadoop), using the traditional tools still rocks
    • Chapters 2 to 6 were the most fun to me with chapter 8 being the most difficult.

     

    In terms of closing, I highly recommend this video to anyone who needs to grasp Pentaho concepts quick, likewise, the course is very well suited for any developer on a “supposed to be done yesterday” type of a project. It is for a beginner to intermediate level ETL/DW developer. But one would need to learn more on Data Warehousing and Pentaho, for such I recommend the 5 star Pentaho Data Integration 4 Cookbook.

    Enjoy it!

    Disclaimer: I received this video from the publisher for the purpose of a public review.

    author: Compudicted | Posted On Sunday, June 1, 2014 6:56 PM | Comments (0)

    Building a Data Mart with Pentaho Data Integration Video Review by Diethard Steiner, Packt Publishing


    The Building a Data Mart with Pentaho Data Integration Video by Diethard Steiner from Packt Publishing is more than just a course on how to use Pentaho Data Integration, it also implements and uses the principals of the Data Warehousing (and I even heard the name of Ralph Kimball in the video). Indeed, a video watcher should be familiar with its concepts as the Star Schema, Slowly Changing Dimension types, etc. so I suggest prior to watching this course to consider skimming through the Data Warehouse concepts (if unfamiliar) or even better, read the excellent Ralph’s The Data Warehouse Tooolkit. By the way, the author expands beyond using Pentaho along to MySQL and MonetDB which is a real icing on the cake!

    Indeed, I even suggest the name of the course should be ‘Building a Data Warehouse with Pentaho’.

    To successfully complete the course one needs to know some Linux (Ubuntu used in the course), the VI editor and the Bash command shell, but it seems that similar requirements would also apply to the Weindows OS. Additionally, knowing some basic SQL would not hurt. As I had said, MonetDB is used in this course several times which seems to be not anymore complex than say MySQL, but based on what I read is very well suited for fast querying big volumes of data thanks to having a columnstore (vertical data storage). I don’t see what else can be a barrier, the material is very digestible.

    On this note, I must add that the author does not cover how to acquire the software, so here is what I found may help:

    • Pentaho: the free Community Edition must be more than anyone needs to learn it. Or even go into a POC.
    • MonetDB can be downloaded (exists for both, Linux and Windows) from http://goo.gl/FYxMy0 (just see the appropriate link on the left).
    • The author seems to be using Eclipse to run SQL code, one can get it from http://goo.gl/5CcuN. To create, or edit database entities and/or schema otherwise one can use a universal tool called SQuirreL, get it from http://squirrel-sql.sourceforge.net.

     

    Next, I must confess Diethard is very knowledgeable in what he does and beyond. However, there will be some accent heard to the user of the course especially if one’s mother tongue language is English, but it I got over it in a few chapters.

    I liked the rate at which the material is being presented, it makes me feel I paid for every second Smile

    Eventually, my impressions are:

    • Pentaho is an awesome ETL offering, it is worth learning it very much (I am an ETL fan and a heavy user of SSIS)
    • MonetDB is nice, it tickles my fancy to know it more
    • Data Warehousing, despite all the BigData tool offerings (Hive, Scoop, Pig on Hadoop), using the traditional tools still rocks
    • Chapters 2 to 6 were the most fun to me with chapter 8 being the most difficult.

     

    In terms of closing, I highly recommend this video to anyone who needs to grasp Pentaho concepts quick, likewise, the course is very well suited for any developer on a “supposed to be done yesterday” type of a project. It is for a beginner to intermediate level ETL/DW developer. But one would need to learn more on Data Warehousing and Pentaho, for such I recommend the 5 star Pentaho Data Integration 4 Cookbook.

    Enjoy it!

    Disclaimer: I received this video from the publisher for the purpose of a public review.

    author: Compudicted | Posted On Sunday, June 1, 2014 6:46 PM | Comments (0)