Category Archives: Sql Server

Making a code book in sql server - Part 2

We have already covered how to add a data dictionary to a sql server table by using extended properties (see here). We can go one step further and making a simple stored procedure that will give us quick access to the code book. After building the stored procedure below we can now double click a table and use a keyboard shortcut. I have mine shortcut as Ctrl+5.

You can see what my shortcuts look like in the picture below.

If we follow the example in the article for building a code book, we can create a table with the iris data set and add a quick code book. Then when we highlight the table [iris] and use our keyboard shortcut of Ctrl+5, you should see this:

Now we can quickly check the definition of a column without having to leave our SSMS window. If you are interested in the other helper stored procedures besides GetDataDictionary, then take a look at this post.

Leave a Comment

Filed under Productivity, Sql Server

Quick Bar Graph in Sql Server

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.

 

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.

sqlhist

Leave a Comment

Filed under Sql Server

Tips For Dealing with Large Datasets in Sql Server

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.

Leave a Comment

Filed under Sql Server

Text bashing in R for SQL

Fairly often, a coworker who is strong in Excel, but weak in writing code will come to me for help in special details about customers in their datasets. Sometimes the reason is to call, email, or snail mail a survey, other times to do some classification grouping on the customer. Whatever the reason, the coworker has a list of ID numbers and needs help getting something out of a SQL database.

When it isn't as simple as just adding quotes and commas to the cells in Excel before copying all the ID's into the WHERE clause of a very basic SELECT statement, I often fall back to R and let it do the work of putting together the SELECT statement and querying the data.

Suppose that you're given an Excel file with 1.2 million ID's and there's some transformation that you need to do first. Obviously, you first read the file in using your protocol and package of choice. Since we're ultimately doing SQL, let's take advantage of the RODBC package's cool features.

Now that the data have been pulled into R any manipulations that need to be made can be done until you end up with a list of ID's that you need to query the SQL database for. Let's start by writing the basis of the query.

Notice the use of the \s switch. We're ultimately going to be dropping this into sprintf() with the ID's so that we don't have to clutter up the script with a stupidly-long list of hard-coded values. Next we need to make R build that stupidly-long list of values to put into the query.

What you should end up with in dbData is the list of things that you are after and a happy colleague.

Leave a Comment

Filed under R, Sql Server

Checking For Duplicate Records in Sql Server Table

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.

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 .

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

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.

Leave a Comment

Filed under Sql Server

Helper Stored Procedures in SQL Server - Part 2

Continuing with last week's post about Helper Stored Procedures, I wanted to introduce two more that I use frequently. The first one is a built in stored procedure (  using SQL Server) called 'sp_helptext'. This stored procedure allows you to view the content of other stored procedures (SP) and more, here is a link to the Microsoft documentation. Here is an example of how you use it:

This will show you the contents of sp_helptext  by using it. This comes in very handy as a keyboard shortcut. I assigned mine to 'Ctrl+3'.  Often I have a SP that does data manipulation and puts the final data in a table that occurs as the last step of my SP. I may not remember which table the data goes into, but I can simply highlight the SP ( I typically double click) and then use 'Ctrl+3' and the contents of the SP appears in my results tab. This also works nicely for function and triggers that you might use.

Another piece of information that I am often curious about is how large is a particular table. If you like going through menus, then you can left click on a table name in the object explorer and choose properties. Then select 'storage' from the left navigation menu. You will see under the 'general' section: "Data Space","Row Count" and "Index Space". While this is okay if you only perform this task once a month, it takes way to long if you are running this 5 + times per day. I wrote a stored procedure called 'sp_tablesize' that is a wrapper for another Microsoft SP, that is sp_spaceused.

After you create the SP 'tablesize_SP' you can assign it to a shortcut in SSMS. I choose to use 'Ctrl+4' for myself. We can create a table and add data and an index to make sure that our new SP works.

 

You will notice that our index size increased after adding one, which is to be expected. Hopefully this will allow you to quickly check the size of a table. Combine that with 'sp_helptext' and 'sp_top10' you can find out a lot of information about a table quite quickly. Let me leave you with a quick reference guide of which SP to use depending on what you want to know.

 

SP Name Use Shortcut
sp_helptext View the contents of a stored procedure or function Ctrl+3
tablesize_SP Check the size of a table including indicies Ctrl+4
sp_top10 View the top 10 records of a table Ctrl+1
sp_help View Information about an object for instance table schema Alt+F1

Note: SSMS 2012 was used for this example.

Leave a Comment

Filed under Sql Server

Helper Stored Procedures in SQL Server - Part 1

Often I have days where a large amount of my time is spent writing SQL code in SQL Server Management Studio (SSMS). Building tables, creating indices and verifying data are all common tasks. However, I often myself needing to checking which columns are in a table or to take a quick look at the data. I got tired of typing Select Top 10 * From [MyTable] more than five times per day.   In order to get around typing that repeatedly I made a function called 'sp_top10'.

This stored procedure can be invoked by typing

exec sp_top10'YourTableHere'  where you substitute in your own table name. The real power comes when you assign your new stored procedure to a shortcut. In SSMS you can do this by going to Tools > Options > Keyboard > Query Shortcuts .  Simply add  'sp_top10' to an open key combination and then restart SSMS. You should now be able to highlight ( or doubleclick) a tablename and then use your new shortcut to view the top 10 records in that table. I set my shortcut to 'Ctrl+F1'.

An example:

You output should look similar to figure 1.

sp_top10_example

Figure 1.

Feel free to edit the SP to suit your particular situation. I use this in conjunction with 'sp_help' which has a default shortcut of 'Alt+F1'  in SSMS almost everyday. So I can select a tablename and 'Alt+F1' if I need to see what type of columns I have and then 'Ctrl+1' to view the first ten records of that table. Hopefully this will help save you time in your daily work.

Note: SSMS 2012 was used for this example.

1 Comment

Filed under Sql Server

Making a Code Book in Sql Server

While working through a coursera course recently (https://www.coursera.org/course/getdata) I started to think about how a code book could be implemented in sql server. There are 3 points that Jeff Leek makes regarding a code book and they are as follows:

  1. Information about the variables ...
  2. Information about the Summary Choices  you made
  3. Information about the study design you used

My focus for this article will be able point number one, information about the variables. Often times it would be nice to know more about your data than simply what data structure it is defined by. For a simple working example, load the Iris datasets into Sql Server and then add some metadata about the columns to provide further information for the users.

 

Now, you should have a table called Iris with some basic metadata about the columns. While you can navigate to the extended properties through the object explorer, it would be nice to access this information through a query.

Now you have the framework for creating a data code book that can be self contained within the table itself. This will prove most useful when you can can share a sql server table with someone else.

 

Leave a Comment

Filed under Sql Server