SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

The Voice of the DBA

Reporting on Data

I would think that most DBAs and SQL developers out there realize they need to understand some basic statistics and mathematics to write meaningful reports for their customers. Since many of us write our reports in T-SQL, if we correctly write the formula for one row, it expands to work the same way for all rows returned from the database. That should hold true in Excel, if you cut and paste the formulas across the all the rows or columns. However since each cell could have a different formula, you can't always be sure that the calculations for all rows are the same.

This was brought to light in a blog that looked at the problems with a public policy analysis based on an Excel worksheet. The audit-ability problem of validating results in Excel was one issue, and it comes about because you would have to manually verify the formulas in every cell to be sure they were correct. I'm not sure how many people want to do that, though I like the transparency of providing the formulas with the data. Someone will verify them.

The other part of the blog talks briefly about other issues. One is that to accurately represent the meaning of data, we need to do more than show simple aggregates like range or average. The reduction of a set of data to a single or small group of representative values may not describe the information in the data. An average without knowing the variance or standard deviation may not be the data you want to base a business decision on.

As we seek to provide more accurate information to our customers and clients, we need to better understand the data we are querying. At some point it won't be enough to understand the basic aggregates in order to report on data. Big data can provide many false patterns, but tiny data suffers from a similar problem. We need to learn more about statistics and analysis to ensure that as we work with business people to query data, we understand what information we are extracting out of data sets.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com with  comments definitely appreciated. Overall RSS Feeds:  

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

 You can also follow Steve Jones on Twitter and find links and database related items and announcements.

ADVERTISEMENT
SQL Backup Pro presents Transaction Log Management

Free eBook! SQL Server Transaction Log Management

Find out how understanding how log files work makes all the difference in a crisis. Then try SQL Backup Pro to put the tips into practice. Download your free resources now.

SQL Prompt Pro

Make working with SQL a breeze

SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.

SQL Monitor

Optimize SQL Server performance

“With SQL Monitor, we can be proactive in our optimization process, instead of waiting until a customer reports a problem,” John Trumbul, Sr. Software Engineer. Optimize your servers with a free trial.

Featured Contents

 

Using a Recursive CTE to Generate a List

Svetlana Marinova from SQLServerCentral.com

Learn how to generate lists of column values from multiple rows by grouping and using a recursive CTE. This is a very flexible and easy implementation, compared to using cursors or PIVOT operators. More »


 

SQL Saturday #230 Rheinland

Press Release

SQL Saturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held Jul 13 2013 at Grantham-Allee 20, Sankt Augustin, Cologne/Bonn, Rheinland, 53757 , Germany. More »


 

Identify and Correct SQL Server Forwarded Records

Additional Articles from MSSQLTips.com

Forwarded records in SQL Server can cause performance issues on heap tables because the record outgrows the page and the database engine uses pointers to reference the data. The best practice is to have a clustered index on every table, however, sometimes there are cases when a clustered index is not needed. More »


 

From the SQLServerCentral Blogs - SQL Server: Part 4 : All About SQL Server Statistics : Auto Update Statistics Threshold - Importance of Manual statistics maintenance

nelsonaloor from SQLServerCentral.com

In the last post, we have discussed about the auto create and auto update property of the statistics. Do we... More »

Question of the Day

Today's Question (by pramod singla):

select concat(null,'testString') as a
     , null+'testString' as b

What is the output of the query?

Think you know the answer? Click here, and find out if you are right.


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: CONCAT (T-SQL).

We'd love to give you credit for your own question and answer.
To submit a QOD, simply log in to the Contribution Center.

ADVERTISEMENT

SQL Server Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Prasad N):

What is the output of this query?
declare @Vchar varchar(12)='prasad  '
      , @Char char(12)='prasad  ' 

select len(@Vchar)
     , len(@Char)
     , len(reverse(@Vchar))
     , len(reverse(@Char))

Answer: 6,6,8,12

Explanation: len():Returns the number of characters of the specified string expression, excluding trailing blanks.

Ref: http://technet.microsoft.com/en-us/library/ms143726.aspx


» Discuss this question and answer on the forums

ADVERTISEMENT

SQL Server Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.

Featured Script

usp_addcolumns

Wilfred van Dijk

(See comments in script)

Example:

exec usp_addcolumns 'ref%', 'modifieddate', 'smalldatetime not null'

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

DATABASE SLOW - Hie, can any one help me what are the steps to b take when database is running slow?

Creating Deployment Change Script from the Source Database Only - I was wondering if there is such a tool to generate deployment change script by looking at the [u]source database...

Search Columns in all Databases - I'm using the SQL Statement listed below to search the current database for column names. What I need is a Statement...

Disable queue msdb - Hello All, I'm Install software to capture deadlock. Then uninstall the software and now my DB msdb grows. currently has...

Display the columns in Alphabetical order in 2005 Management Studio? - I have a monster itemmstr table with 403 columns in it. When I look at this table in Management studio,...

SQL Server 2005 : Backups

Read MS SQL 2000 backup file without restoring the database - I want to read certain data from the backup file of MS SQL 2000 without restoring the database backup file....

SQL Server 2005 : Business Intelligence

Flat file Connection 4096 character limit - Hello I am having to create a very wide flat file in a SSIS package with hundreds of columns. When I...

SSIS to get to underlying data in an excel pivot - Hi, I have a pivot table that shows rev/mgn information of tablets selling in top markets in the world. My interest...

Hierarchy repeating levels - I am trying to create a very simple geographical hierarchy on a Carrier dimension. My data is something like this: Carrier...

SQL Server 2005 : Development

Data in date column to decrement with one - Hi, problem. I have a table that is having composite primary key e.g startdate, counterparty, center, costname, currency, month, year start date...

SQL Server 2005 : SQL Server 2005 Integration Services

SQL job failed - I did an SFTP to command the job via winSCP. I manage to run execute it in SSIS. When I try to...

SQL Server 2005 : T-SQL (SS2K5)

How to make this block dynamic? - I'm just not sure how to do it. When I run: [code="sql"] select @liststr = coalesce(@liststr+',' ,'') + quotename(column_name) from [server].j3688802s.information_schema.columns where table_name = 'sample' print (@liststr) [/code] I get a...

Output results to text file - I need to output a result set into a text file.. not just in the form of data.. i need...

SQL Server 2005 : SQL Server Newbies

Order of rows in a Table with Identity Column. - I have a table with an Identity Column as TransactionId and also it is a primary key. The table has around...

Grouping by day on a rollover count - Hi, I have a table containing a count that rollsover at [u]around[/u] 32000 to 0 possibly many times a day and...

SQL Server 7,2000 : General

Compare two databases - I need to compare two databases (e.g. Development DB and Test DB) to make sure they are 100% identical. Is...

SQL Server 7,2000 : T-SQL

Problem using date comparison in Dynamic SQL Please help - Ok I am writing an archive process in SQL 2000 for an application. Long story short I need to grab...

SQL Server 2008 : SQL Server 2008 - General

log differences - what is the difference between general log file and the log file in replication ?

Error with Linked Server and Dynamic SQL - I am attempting to execute the following code via SQL agent job. USE [dataASH] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[usp_CaptureStatusData] ( @RunFeat...

prerequisites - what are the prerequisites to configure replicatin in sql 2008 /

Export data into Existing Excel File - I am returing data into TableVariables using Cursors .So I am having data for Each date of month . I want...

syntax of building a hyperlink - Thanks in advance for any help... I am trying to build a query that will have as one of its...

Suggestions about ERD needed - Hi All, I am developing a very big project.I have one question about ERD. I know Normalization is good for big projects...

Delete Table Variable - I am using TableVariable inside Cursor ,So it is not returing the results for all values provided .Its returing only...

How to solve - Hello, declare @date1 datetime declare @date2 datetime set @date1='2013-08-01' set @date2='2013-08-07' now I want the output between @date1 and @date2, how can I do that?

Defecting a target server - Hi We use a Central Management Server to target multiple servers with various jobs. I have created a multi server job,...

Sleeping SPID - Hello everyone, I have stuck in a situation where I am seeing more than 15K sleeping SPID, I know this is...

MSDTC errors - SQL server stopped responding to any request - Hi Rather a strange issue with multiple SQL server instances - SQL server abruptly not responding to client applications for a brief...

creating new ldf file - there is one ldf file for our database. if we create one more ldf for the same database. then both...

Maxium rownum groupwise - create table #Abc(ID int,Name varchar(20),SAl int,Rownum int) insert #Abc values(1,'Anees',1000,1) insert #Abc values(2,'Rick',1200,1) insert #Abc values(3,'John',1100,1) insert #Abc values(3,'John',1500,2) insert #Abc values(3,'John',1200,3) insert #Abc values(4,'stephen',1300,1) insert #Abc...

Difference between Full backup and Copy-only full backup - I saw in this [url=http://www.sqlservercentral.com/Forums/Topic567010-357-1.aspx#bm1311005]link[/url] that full backup does not truncate the log. So what is the difference between full backup...

Tempdb issue - we are facing issue in tempdb file size , initial size we allocate 2 GB , but after restart sql server , size...

Regular Mirroring Timeout Error Messages - We are running SQL Server 2008R2 RTM (10.50.1617) We get time out messages that read like "the mirroring connection for database...

syntax for using /SET for SSIS config file in SQL 2008? - I used to use /CONF to represent an XML config file running SSIS packages via DTExec with SQL 2005. With...

Please HELP !! :SQL SERVER with MySQL Linked Server. Error Executing Trigger - Hi sirs ! If somebody can help me i would appreciate a lot ! I have SQL server with MySQL Linked server...

How to load dynamically created excel into DB table (SQL Server 2012) using SSIS - Hi All, I need to load excel sheet data into database table in SQL Server (2012) using SSIS (2012). The columns...

SELECTING A CASE VALUE ??? - Hi I am using the following to retrive a clients "secure" name if alias-type of 004 exists. Problem I have is if...

Curious question about Transaction Replication from a backup - I've read various articles on how to do this but tried a different approach and while it appears to work...

Mixed Mode Authentication not consistent between Windows Users using Mgmt Studio - Currently experiencing strange issue I have not seen before on a SQL Server 2008 R2 instance running on Windows Server...

problem select insert - hi in database have many tables for selection for example table 1 phone kod1 kod2 adress kod3 email kod4 kod5 --- --- --- --- --- ------- ---- --- ---- ---- table2 name1 kod1 ----...

Issue while creating indexed view after joining two non related tables - I am trying an create the indexed view after joining two non related tables. Please correct me if my approach...

Can I change a column from smallint to float? - I've got a column, in 1 table, that's a smallint. Then I've got another table with the same column name...

How to import non-delimited text file - Hi, all. I (will) have a non-delimited text file containing 35 lines each for over 5000 records. Below is an...

table creation on one table referenced by many tables - hi Here a requirement that I am trying to implement One table Address is used by many tables. like member, provider and...

Inserting Results from SPROC into table - I was able to finally develop this SPROC that runs fine and returns the results but i would like to...

SSIS SQL Command parameter issue - Hi I have inherited an ETL from someone with far better SQL skills than I and I need to add...

Unknown error messages during generate script from SQL2008 database - I have a SQL2008 database, running Standard Edition 64-bit, database owns by sa, connected to Management Studio using Windows Authentication...

SQL Server 2008 : T-SQL (SS2K8)

with no check - hi i want to add column with no check constraint like alter table emp with nocheck add column1 not null and then i need to...

time elapsed between sp completed and RPC completed - I ran a trace on a slow running remote stored proc call: exec db1.mydb.dbo.spInsertSomeRows The trace ran on the desination server (db1)....

Split data - Hi all, I was trying to split the values using DENSE RANK but I faced some issues... here is the problem...

Different between two query - Hi Dear, What is different between two query? query 1: [code="sql"]SELECT dbo.Person.Code FROM dbo.Person LEFT OUTER JOIN dbo.CustomerSegment ON dbo.Person.CustomerSegment = dbo.CustomerSegment.Code INNER JOIN dbo.CustomerCreditD...

blocking remove - hi i am adding new column with default value this table has millions of rows, so while i am executing my query...

Sequential numeric - I have a need to update numeric values in a table column so that they are unique and sequencial, the...

Running Total Query - Hi there people, I'm writting a T-SQL procedure to get a running total over a certain period, the data may have...

financial year sql statement please help - You are only given the transaction date in your transactions table and your client requires you to be able to...

Put first line as column name - Hi people, I am creating a function to be used in a specific application that a user can insert any kind...

Max of 2 dates - I search for Date functions for finding the max of the 2 dates. But such function does not exits. Is there...

SQL Server 2008 : SQL Server Newbies

Update Novell eDirectory from a SQL Server database - I'm really sorry, here's yet another of my terrible questions. I apologise in advance! I've been asked to provide some information...

Generate Script with Data - I am trying to generate a script with data. I have imported this data from Excel to SQL and now need...

SQL Server 2008 : Security (SS2K8)

bad Windows login when connect to SQL - Hello, I have a problem with Windows authentication on a SQL server. The problem occurs when in my company, a woman marries...

Linked Server Issue - I am having an issue with some linked servers. Here is the scenario: ProdA, ProdB, and DevC are all identical servers...

SQL Server 2008 : SQL Server 2008 High Availability

Restore the primary database of a log shipping configuration - I support a company that is using Log Shipping setup by the previous DBA. The log shipping is setup to...

SQL Low performance only in afternoons - In one of our database, there is a stat about the whole day : SQL CPU Usage spikes b/w 8AM & 6PM...

DR Testing Question - Hi Guys, I am currently in the planning process of a DR testing(Mirroring Failover) and I need some clarification for couple...

Log Shipping or Transactional Replication - Hi, We have an ecommerce site for which primary infrastructure (DB and web servers) is based in Chicago. We have a...

Table lock on MSmerge_contents - We are running merge replication over a WAN. Only changes to the Subscriber are replicated to the Publisher ( -ExchangeType 1 ). We...

SQL Server 2008 : SQL Server 2008 Administration

What can I do to an offline database? - I am in the process of migrating DBs from an old server to a new server. Currently, what I've been...

Drop Database before Restore - Any Downside ? - I have a job that copies the production database backup to a reporting server every night, and then restores over...

copy system database files - Dear All, I would like to have a backup of the SQL system databases (the files themselves) not the SQL backup....

SQL job or task prioritization - Dear All, My current SQL configuration is SQL 2008 standard edition (Vmware virtualized 2 node failover cluster) on windows 2008 R2...

Notifying SQL of additional CPU additions - Dear All, Iam planning to add two more CPU’s to each node on my SQL 2008 standard edition (Vmware virtualized 2...

SQL 2008 Express Edition Server Agent won't start - I can't get Server Agent to 'start' on my install of Express Edition. I did some research and it seems...

Login failure to SQL instance in 2012 - Hi All, I'm seeing login failures to our end users on our server which has recently migrated to Windows/SQL 2012. Strange...

Career : Employers and Employees

Contractors - I'm curious to hear from contractors who work independently and how they find their clients. Right now I'm working corp-to-corp...

Programming : General

C# - Passing parameters on launch - Hi, I am writing a windows form application that will pass parameters on lauch. The parameter is A then it will...

AddWithValue issue - Update the current field content ASP.Net/SQL - I have an ASP.Net VB shopping cart app where when an item is "Removed" from a shopping cart, and the...

stored procedure unable to find file on another server - helo, i have created an application that needs to find a file on the network and ingest data. I have been...

Programming : XML

LOADING COMPLEX XML FILE - The file below came from a web request I want to load it into SQL Server but it is too...

SQLServerCentral.com : Anything that is NOT about SQL!

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

SQLServerCentral.com : SQLServerCentral.com Website Issues

"Advanced Search" for Scripts - I would appreciate some kind of advanced search on "scripts", or maybe a "search within results" feature. We have a...

Reporting Services : Reporting Services

Replicating a MS Access report in SSRS - I am trying to replicate a report a user uses in MS Access. In MS Access a user can enter a...

Database Design : Disaster Recovery

SS2012 - Log Shipping vs Mirroring vs AlwaysOn - I'm looking for an easy failover technology, in the case where our primary SQL Server 2012 instance goes down. I have...

Data Warehousing : Integration Services

Microsoft.ACE.OLEDB.12.0 provider not registered on local Machine - My source file was created in Excel 2007, so i had to install ACE drivers on server (I did it...

Automate Editng of SSIS packages in XML - Hi All, I am searching for some help on editing the SSIS packages as XML files, Here is the scenario, I...

SSIS error when configuring data flow task with .xls file - Quick environment details: 64 bit Server 2008; SQL Server 2012; Studio 2010 I have a package that connects to a .xlsx...

Data Warehousing : Analysis Services

Star vs Snowflake and general help on a dimensional quandry! - Hi, I'm relatively new to OLAP/SSAS. I've designed a few cubes/dimensions and they've seemed to work quite well, but I'm in...

cube accessing speed issue - This is a weird thing I am facing in my project. Just a brief below... We have an SSAS server...

Microsoft Access : Microsoft Access

Access 2013 Custom Web App Resources - Is there any robust documentation on Access 2013 Custom Web Apps? Specifically, macro definitions with examples, system examples (beyond Microsoft's...

Access Query ported to SQL - Here is the "inner" part of a query in Access. UPDATE [Vehicle Detail] SET [Vehicle Detail].ReturnDate = #4/29/2013#, [Vehicle Detail].ReturnSource = 'ReturnProcess' WHERE ((([Vehicle...

To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com