Automatic Index Rebuilding

While working on a large project, I found that the queries were running slower and slower as the days went on. We were working with current and historical data and were only joining about 3-5 tables at a time. Each join was bringing back between only about 50K rows or so, and the results were aggregated and tossed up on an SSRS page. Since things were going well at first and started slowing down over time, I figured the indices were probably the cause. If you don’t know much about indices, or you need to brush up on your knowledge, … Continue reading Automatic Index Rebuilding

Importing Data into SQL Server – Using Excel Shortcuts

I’ll be the first to say, the switch from DTS to SSIS was painful. DTS would go through a lot of implicit conversions so you didn’t have to worry or think about them. But SSIS was not as merciful. From different fields lengths to different encodings, SSIS wants you to be specific. Sometimes, it is just quicker to use some excel trickery than figuring out that you should have defined a column as a String 65 instead of the default String 50. We will use the CountryList.csv file and CountryList table from the original Importing Data in SQL Server post. … Continue reading Importing Data into SQL Server – Using Excel Shortcuts

Importing Data into SQL Server – Using the INSERT statement

Sometimes, the easiest and quickest way to get data into SQL Server is to simply use an INSERT statement.  Here is what the INSERT statement looks like in Books Online(BOL)

Ok, let stick to the basic and learn some quick insert techniques that you can use today. Recall that we are using the CountryList table, which we created in earlier examples. Here is the script to create the table:

The easiest INSERT statement goes something like this:

Here is a sample using the CountryList table:

Recall that the country table has an IDENTITY Column, so we … Continue reading Importing Data into SQL Server – Using the INSERT statement

Importing Data into SQL Server – Using the Import Export Wizard

Today begins the series on importing data into SQL Server 2005/2008 using the various built in tools. With SQL Server, there are several ways of getting your data from one place to another. Today, we will focus on using the wizard from SQL Server Management Studio. Before we begin, I am using a simple database called testdb. I am also using a country code file found at http://www.andrewpatton.com/countrylist.html. The file contains the 2 and 3 character ISO 3166 Country Code abbreviations, which we will use in our sample. The CSV file as seen in Excel 2007 Here is the script … Continue reading Importing Data into SQL Server – Using the Import Export Wizard

Storing Computed Columns in SQL Server

From time to time you will run into a case where you need to store computed columns in your SQL Server tables. Examples of computed columns include taxes on a sale or business days between several dates. Setting  up computed columns in SQL Server 2008 is fairly simple. You simply give the column a name and then specify your calculation instead of giving a datatype. You could even use a case statement instead of functions and calculations. Here is an example of a table that calculates days between 2 dates.

The sample table has an ID, 2 dates and … Continue reading Storing Computed Columns in SQL Server