Bearded Analytics

Have you ever needed a bar graph and didn't want to leave Sql Server? Well if you don't have Sql Server 2016 yet I have some code for you. The code below creates a table with the numbers 1-10. We then sample repeatedly from that table and make a histogram of our sample. We should have something approaching a uniform distribution when we are done.

If Object_id('tempdb..#Test','U') is not null 
Drop Table #Test
Create Table #Test ( numbers float )

If Object_id('tempdb..#RandomNumbers','U') is not null 
Drop Table #RandomNumbers
Create Table #RandomNumbers ( numbers float )


-- Create Table with numbers 1 -10
Declare @Counter int
Set @Counter=1
While @Counter <=10
Begin
    Insert Into #Test Select @counter
    Set @Counter=@Counter+1
End

-- Repeated Sample with replacement from table
Declare @Counter2 int
Set @Counter2=1
While @Counter2 <=1000
Begin
    Insert Into #RandomNumbers Select Top 1 Numbers From #test Order By newid()
    Set @Counter2=@Counter2+1
End

-- Make histogram
Select
    numbers,
    replicate('>',count(*)) [Levels]
From
    #RandomNumbers
Group By
    numbers
Order By
    count(*) desc

You should now have a nice little histogram showing you the distribution of your data. Another useful thing is to approach it like a pareto chart and order the data descending.

Select
    numbers,
    replicate('>',count(*))[Levels]
From
    #RandomNumbers
Group By
    numbers
Order By
    count(*) desc

Are you dealing with large (100 million row +) datasets that live in a sql database? Have you found your old methods not to be satisfactory? Here are a couple of tips from my own experience.

1. Do not use count(*) to figure out how big of a table you have.
2.  Do not use the max function to figure out when the last record was inserted

1.

I found this out the hard way when I actually got an error when I tried to run
Select Count(*) From Table  and received an arithmetic overflow error. I was puzzled at first and after some  searching I found the problem. A 'count' in sql server returns the datatype int, which means it can only be 2^31-1 or 2,147,483,647. The table that I was working with had more than 2.1 billion rows, so that caused a problem. Now, you may be thinking that you could just use a Count_Big instead, but that is probably not the right answer. Try using sp_spaceused instead. If you are interested in turning this into a shortcut for SSMS, look here.

2.

Sometimes I need to figure out when the latest record was inserted. Instead of taking a max on a datetime field. I can often get to my answer by using information about an index. Hopefully your table has a unique auto incrementing primary key that can aid you in finding the last record inserted. Make sure you understand the process that builds or alters your table. It could be that the maximum value of your primary key is not related to the most recent records.

Select max(LocalTimeStamp) From Table ( Slow )

vs

Select LocalTimeStamp From Table Where PrimaryKey = ( Select Max(PrimaryKey) From Table ) ( Quick )

Have some useful tips to add? Please post them in the comments.

I often find myself needing to check if a table is unique with respect to a particular column. For instance, if I have a table of customer data I want to ensure that a customer only exists once. There are several ways that you can do this.  If I want to write a quick select if usually looks like this.

-- Method 1
Select count(*),Count(Distinct ColumnofInterest)
From Table

A while back I got tired of writing the above query over and over, so I created a stored procedure (SP). The SP below take two arguments, a table name and a column name and will determine if your column is unique. Currently it is written using a  count(*)  , which is not meant for large tables, i.e. > 2,000,000,000 rows .

-- Method 2
USE [YourDataBase]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



Create Procedure [dbo].[DupeChecker]
@Table nvarchar(128),
@Column nvarchar(128)
as



-- Construct SQL Statement Using Name Provided
Declare @MyQuery as nvarchar(max)='
Select 	@DupeFlag =case when count(*) = count(Distinct ' + @Column + ') then 0 else 1 end 
From ' + @Table + ' with(nolock)
'


Declare @MyNumber smallint
Set @MyNumber = 0

exec sp_executesql @MyQuery, N'@DupeFlag smallint out', @MyNumber out



-- Raise Error if Table is not uqniue
Declare @ErrorText Varchar(100)
Set @ErrorText = 'Your column ' + @Column + ' on table ' + @Table + ' is not unique.'
If (@MyNumber =1)
	Begin
		RAISERROR(@ErrorText,16,1)
	  RETURN
	End

-- If unique print a message to confirm
Declare @ErrorText2 Varchar(100)
Set @ErrorText2 = 'Your column ' + @Column + ' on table ' + @Table + ' is unique.'
If (@MyNumber =0)
	Begin
	Print (@ErrorText2)
	End
		
GO

There is one more method that I commonly use to determine if my table is unique and that is an index.

-- Method 3
Create Unique NonClustered Index [NCI_Dupe_Checker] on TableName
( MyColumn )

So, now you have three different ways to check your table to make sure that your column is unique. Keep in mind that adding an index will take up additional space, where methods 1 and 2 will not.