Importing Excel Data into SQL Server

Recently I had to import a huge load of surveys gathered in Excel sheets to SQL Server for further processing and analysis. Don’t ask why they were not entered on the first place to a database via some interface…

Importing an Excel sheet to SQL Server seems like a trivial task. Here is an example:

SELECT * INTO BenchmarkSurvey


'Data Source=C:Benchmark SurveyReceivedSurvey1009.xls;

 Extended Properties="Excel 8.0"')...[Sheet1$];

On a first look seemed the data was imported OK and I could start cleaning and processing. However, making a few sample tests to match responses from selected Excel surveys proved that a lot of the answers in Excel are imported as NULLs. Digging deeper in to the source data showed that those spreadsheet cells were merged and had mixed numbers and text values. Apparently that was causing the data to be imported as NULLs.

One solution that came to mind was to write a macro to parse all Excel files and to change the merged cells data to text. But that just didn’t seem right.

Sure enough, there was a more elegant solution. Looking closer at the OLE DB provider properties I found the IMEX property (short for Import/Export Mode). Changing the IMEX value tells the provider how to convert/format the data to fit the column type. Possible values for IMEX are 0, 1, and 2. In my case setting IMEX to 1 did the trick (IMEX=1 tells the provider to read those mixed data columns as text). Here is the final statement that imported the data from all merged/mixed cells correctly:

SELECT * INTO BenchmarkSurvey


'Data Source=C:Benchmark SurveyReceivedSurvey1009.xls;

 Extended Properties="Excel 8.0;IMEX=1"')...[Sheet1$];

Another interesting setting is HDR. Settings HDR=Yes indicates that the first row contains column names, not data.

Use ABS to Find the Closest Match

The ABS function in SQL Server is a little bit overlooked and unutilized. It returns the absolute, positive value of the given numeric expression.

One particular set of problems that ABS is very useful is when needing the find the closest match to a given value. That could be to find the closest ZIP code, date, etc. Below are two examples of utilizing ABS for this purpose.

-- Create table with sales regions

CREATE TABLE SalesRegions (

 zip_code INT PRIMARY KEY,

 region_name VARCHAR(35));


-- Insert sampel data

INSERT INTO SalesRegions VALUES(40320, 'North Area');

INSERT INTO SalesRegions VALUES(40324, 'North/East Area');

INSERT INTO SalesRegions VALUES(40326, 'North/West Area');


-- Define search zip code

DECLARE @search_zip INT;

SET @search_zip = 40323;


-- Find the closes region by zip code

SELECT TOP(1) zip_code, region_name

FROM SalesRegions

ORDER BY ABS(zip_code - @search_zip);


-- Results

zip_code    region_name

----------- ---------------

40324       North/East Area



-- Create Employee table

CREATE TABLE Employees (

 employee_nbr INT PRIMARY KEY,

 employee_name VARCHAR(35));


-- Employee evaluation dates

CREATE TABLE EmployeeEvaluations (

 employee_nbr INT,

 evaluation_date DATETIME,

 PRIMARY KEY (employee_nbr, evaluation_date));


-- Sample data

INSERT INTO Employees VALUES(1, 'John Doe');

INSERT INTO Employees VALUES(2, 'Jeff Brown');


INSERT INTO EmployeeEvaluations VALUES(1, '20070101');

INSERT INTO EmployeeEvaluations VALUES(1, '20080101');

INSERT INTO EmployeeEvaluations VALUES(1, '20080304');

INSERT INTO EmployeeEvaluations VALUES(2, '20080604');


-- Find the closest evaluation date, could be in the future

SELECT A.employee_nbr, A.employee_name, B.evaluation_date

FROM Employees AS A


(SELECT TOP(1) B.evaluation_date

 FROM EmployeeEvaluations AS B

 WHERE A.employee_nbr = B.employee_nbr



-- Results

employee_nbr employee_name  evaluation_date

------------ -------------- -----------------------

1            John Doe       2008-03-04 00:00:00.000

2            Jeff Brown    2008-06-04 00:00:00.000

Hierarchies with CTEs

Common table expressions (CTEs) have many applications. However, one of their capabilities to implement recursive queries is very useful for navigating and manipulating hierarchies.

Here is one brief example of utilizing that. Given a table with employees and their managers represented as adjacency list, provide list of employees that report to particular manager, ordered by the natural hierarchy order of listing each employee under the corresponding manager.

-- Create sample table with employees and managers



 employee_name VARCHAR(35),

 manager_nbr INT NULL REFERENCES Employees(employee_nbr));


INSERT INTO Employees VALUES (1, 'John Doe', NULL);

INSERT INTO Employees VALUES (2, 'James Brown', 1);

INSERT INTO Employees VALUES (3, 'Keith Green', NULL);

INSERT INTO Employees VALUES (4, 'Peter Roth', 2);

INSERT INTO Employees VALUES (5, 'Hans Gruber', 2);

INSERT INTO Employees VALUES (6, 'Kris Evans', 4);

INSERT INTO Employees VALUES (7, 'Jeff Colleman', NULL);


-- Use recursive CTE to build binary and charachter paths

WITH EmployeeHierarchy


(SELECT employee_nbr, employee_name, manager_nbr,

        CAST(employee_nbr AS VARBINARY(MAX)) AS bpath,

        CAST('.' +

            CAST(employee_nbr AS VARCHAR(4)) +

            '.' AS VARCHAR(MAX)) AS cpath

 FROM Employees

 WHERE manager_nbr IS NULL


 SELECT E.employee_nbr, E.employee_name, E.manager_nbr,

        H.bpath + CAST(E.employee_nbr AS BINARY(4)),

        H.cpath + CAST(E.employee_nbr AS VARCHAR(4)) + '.'

 FROM Employees AS E

 JOIN EmployeeHierarchy AS H

   ON E.manager_nbr = H.employee_nbr)

SELECT employee_nbr, employee_name, manager_nbr, bpath, cpath

FROM EmployeeHierarchy

WHERE cpath LIKE '%.2.%' -- filter all employees for manager 2

ORDER BY bpath;          -- order by natural hierarchy path


-- Results

employee_nbr employee_name  manager_nbr bpath                              cpath

------------ -------------- ----------- ---------------------------------- ----------

2            James Brown    1           0x0000000100000002                .1.2.

4            Peter Roth    2           0x000000010000000200000004        .1.2.4.

6            Kris Evans    4           0x00000001000000020000000400000006 .

5            Hans Gruber    2           0x000000010000000200000005        .1.2.5.

The method above creates two manager/employee paths: a binary path that preserves the natural ordering at each level, which can be used to sort; a character path that can be used to filter by manager.

Parameter Sniffing

What is “parameter sniffing”? When a stored procedure is first executed SQL Server looks at the input parameters and uses this as guidance to build the query plan. This is known as “parameter sniffing”.

This is good as long as the input parameters for the first invocation are typical for future invocations. But if that is not the case this will cause performance problems.

For example, a procedure is supposed to retrieve all rows for customer orders with non-clustered index on the customer column. If the first invocation returns a small set of orders it may be most efficient to use index seek. Further invocations may be for large set of orders, but the first cached plan with index seek will be used resulting in poor performance (instead of using a scan).

Here is one example stored procedure and different methods to handle parameter sniffing.


 @customerid NCHAR(5)




    SELECT orderid, customerid, orderdate, shippeddate

    FROM Orders

    WHERE customerid = @customerid;



Replace parameters with local variables

This solution is based on assigning the stored procedure parameters to local variables and then using the local variables in the query. This works because SQL Server is not sniffing local variables and using the local variables in place of parameters forces plan generated based on statistics (in effect this disables parameter sniffing).


 @customerid NCHAR(5)




    DECLARE @local_customerid NCHAR(5);


    SET @local_customerid = @customerid;


    SELECT orderid, customerid, orderdate, shippeddate

    FROM Orders

    WHERE customerid = @local_customerid;



Execute using WITH RECOMPILE

This solution forces recompile of the stored procedure on each run, that way forcing a fresh query plan for the current parameters. Note that this will recompile all statements inside the stored procedure.

EXEC GetCustomerOrders @customerid = N'CACYK' WITH RECOMPILE;

Query hint RECOMPILE

SQL Server 2005 offers the new query hint RECOMPILE which will force recompilation of the individual query. This method is better than the prior method because recompilation will affect only one statement and all other queries in the stored procedure will not be recompiled.


 @customerid NCHAR(5)




    SELECT orderid, customerid, orderdate, shippeddate

    FROM Orders

    WHERE customerid = @customerid





Another new query hint in SQL Server 2005 is OPTIMIZE FOR. It allows specifying a constant that will be used to optimize the query plan instead of the variable. This could be useful if it is known that particular selective value is frequently used to invoke the stored procedure. However, any other parameter value will suffer the same performance problems.


 @customerid NCHAR(5)




    SELECT orderid, customerid, orderdate, shippeddate

    FROM Orders

    WHERE customerid = @customerid

    OPTION (OPTIMIZE FOR (@customerid = N'CACYK'));



Note: SQL Server 2008 adds a new option to specify “OPTION (OPTIMIZE FOR UNKNOWN)”. This specifies that the query optimizer will use statistical data instead of the initial value to determine the value for query optimization.

Plan Guides

Plan guides in SQL Server 2005 provide the opportunity to optimize a query without changing the actual code of the query. This is especially useful when dealing with third party vendor applications where access to code may not be available. A plan guide allows associating query hints with a query without changing the query.

EXEC sp_create_plan_guide

    @name = N'SolveParameterSniffing',

    @stmt = N'SELECT orderid, customerid, orderdate, shippeddate

               FROM Orders

               WHERE customerid = @customerid',

    @type = N'OBJECT',

    @module_or_batch = N'GetCustomerOrders',

    @params = NULL,

    @hints = N'OPTION (RECOMPILE)';

USE PLAN query hint
Another plan stability feature in SQL Server 2005 is the USE PLAN “xml_plan” query hint, which allows forcing the use of a specific plan every time the query is run.

Additional Resources:

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

Column Properties

There are different ways to query the meta data in SQL Server. The system catalog views in SQL Server are one great improvement. Here is one example on retrieving properties for all columns, including default values and description (if defined as extended property).

SELECT SCHEMA_NAME(T.schema_id) AS 'Schema', AS 'Table Name', AS 'Column Name', AS 'Data Type',

       C.precision AS 'Precision',

       C.scale AS 'Scale',

       CASE WHEN IN ('nvarchar', 'nchar')

            THEN C.max_length/2

            ELSE C.max_length

       END AS 'Length',

       CASE WHEN C.is_nullable = 1

            THEN 'Y'

            ELSE 'N'

       END AS 'Allow NULLs',

       D.definition AS 'Default Value',

       P.value AS 'Description'

FROM sys.tables AS T

JOIN sys.columns AS C

  ON T.object_id = C.object_id

JOIN sys.types AS S

  ON C.user_type_id = S.user_type_id

LEFT OUTER JOIN sys.default_constraints AS D

  ON C.object_id = D.parent_object_id

 AND C.column_id = D.parent_column_id

LEFT OUTER JOIN sys.extended_properties  AS P

  ON C.object_id = P.major_id

 AND C.column_id = P.minor_id

 AND P.class = 1

 AND = 'MS_Description'

WHERE T.type = 'U'

ORDER BY, C.column_id;

Shredding XML in SQL Server 2005

Using XML data has many applications. In databases in particular it can be used for passing parameters from client applications, exchange data between SQL modules, or storing details in XML columns. SQL Server 2005 offers new capabilities and native support for XML. Below is one example of utilizing some of the new XML methods in XML to shred XML data to table format.

-- Declare XML variable



-- Element-centered XML

SET @data =




      <name>Allied Industries</name>




      <name>Trades International</name>




-- Using the query() method

SELECT T.customer.query('id').value('.', 'INT') AS customer_id,

       T.customer.query('name').value('.', 'VARCHAR(20)') AS customer_name

FROM @data.nodes('data/customer') AS T(customer);


-- Using the value() method

SELECT T.customer.value('(id)[1]', 'INT') AS customer_id,

       T.customer.value('(name)[1]', 'VARCHAR(20)') AS customer_name

FROM @data.nodes('data/customer') AS T(customer);


-- Select only customer which id equals 2 using the exist() method

SELECT T.customer.value('(id)[1]', 'INT') AS customer_id,

       T.customer.value('(name)[1]', 'VARCHAR(20)') AS customer_name

FROM @data.nodes('data/customer') AS T(customer)

WHERE T.customer.exist('id/text()[. = "2"]') = 1;


-- Attribute-centered XML

SET @data =


    <customer id="1" name="Allied Industries"/>

    <customer id="2" name="Trades International"/>



-- Using the value() method

SELECT T.customer.value('@id', 'INT') AS customer_id,

       T.customer.value('@name', 'VARCHAR(20)') AS customer_name

FROM @data.nodes('data/customer') AS T(customer);



-- Results

customer_id customer_name

----------- --------------------

1           Allied Industries

2           Trades International

Testing the processing for element-centered XML versus attribute-centered XML does show that attribute-centered XML is processed faster.


XML Support in Microsoft SQL Server 2005

Anatomy of a Query

To write a good and correct query it is very important to understand the logical processing of a query. It is sad that very few SQL programming manuals start with that. Here is a look at the insides of logical query processing.

First, to start with a note: the logical processing of a query does not guarantee that the query will be executed that way. Since the cost based optimization of SQL Server goes through a few steps to find the optimal execution plan, it may choose to process the query different way that the sequence of steps below. Examples are when a WHERE predicate is applied in a JOIN, or when it is pushed down to the HAVING clause.

The logical query processing determines logically what the final result set would look like, regardless of how the database engine will manage to generate that result physically.

Here are the phases of the logical query processing in order of processing:

FROM: This is where it all starts! The FROM defines the source table(s) for the processing. If more than one table is included, then a Cartesian product (cross join) is performed between the first two tables. The result of the Cartesian product is every possible combination of a row from the first table and a row from the second table. That is, if one of the tables has M rows and the other one has N rows then the result set will be M x N rows.

ON: Next, the ON filter is applied to the result set. Only rows that match the join condition are selected.

OUTER: If an OUTER join is used, then rows from the preserved table for which a match was not found are added back. In INNER joins this phase is skipped. If more than two tables are in the FROM clause, then FROM/ON/OUTER are applied between the current result set and the next table, until all tables are processed.

WHERE: The next step is to apply the WHERE filter to the result set. Rows that satisfy the where conditions are selected. Only conditions that evaluate to TRUE (not UNKNOWN or FALSE) will pass.

GROUP BY: The result set rows are divided in groups based on the column list specified in GROUP BY. Rows of the grouped set must be only grouping columns, aggregate functions (i.e. COUNT, SUM, AVG, MIN, MAX), function or constants, and an expression made up of the first three items. Important to note here is that NULL values are considered as equal and grouped into one group.

HAVING: Next, the HAVING filter is applied. Only groups that match the having conditions are selected. If a group doesn’t satisfy the conditions, then the whole group is removed from the result set. If there is no GROUP BY the entire result set is treated as one group.

SELECT: Here it is, the SELECT list! The SELECT list is processed down here, even that it is the first line in the query. At this time column aliases are added. Steps prior to this in the list cannot use the column aliases, only the following steps will see the aliases. This is the step that will define the columns in the result set.

If there is a SELECT DISTINCT, then the duplicate rows are removed. Just like in GROUP BY, NULLs are treated as matching.

ORDER BY: The rows of the result set are sorted according to the column list specified in the ORDER BY clause. Only using ORDER BY can guarantee a sort order for rows. Otherwise the tables are unordered sets. At this step the result set is transformed to a cursor. Also, NULLs are considered equal for sorting. The ANSI standard doesn’t allow to order by columns that are not included in the SELECT list, but SQL Server allows doing that (even by expressions based on those columns). Columns in the ORDER by can be referred to by the alias or by their ordinal number.

A couple more notes. The CUBE & ROLLUP are processed after GROUP BY. At that point the super-groups are added to the result set. Also, TOP which is T-SQL specific is processed after ORDER BY, and there the specified number or percentage or rows are selected in the final result set.

For nested queries, the innermost queries can reference columns and tables in the queries in which they are contained.

Bulk Loading Images in SQL Server

Loading images and any binary files (like Adobe PDF documents) to a database is not always the best option but sometimes needed. In SQL Server 2005 this process is simplified a lot with the BULK option of OPENROWSET. Here is an example of inserting image data into a VARBINARY(MAX) column (the same applies to loading an Adobe PDF file or any other binary file):


  image_data VARBINARY(MAX));


INSERT INTO Foobar (image_data)

SELECT image_data


        BULK N'C:image.jpg',

        SINGLE_BLOB) AS ImageSource(image_data);

Note the use of the SINGLE_BLOB option.

Keeping History Data in SQL Server

Very often there is the need to archive data on daily basis. Just had that question today and here is trimmed down solution based on a recent project.

The task is to keep history data on daily basis of changed data only (that is only rows that have changed for the last day). The scenario is a Loan table with loan number and loan amount. If there is change in any of the columns in the table, that change needs to be inserted into a history table. Further, the history table should be able to provide a convenient way to query the latest data (most recent updated row) for reporting purposes.

Here is the code to that solution:

-- Create sample tables.




  loan_amount DECIMAL(12, 2) DEFAULT 0.0 NOT NULL);


CREATE TABLE LoansHistory (

  loan_nbr INTEGER NOT NULL,

  loan_amount DECIMAL(12, 2) NOT NULL,

  change_date DATETIME


              NOT NULL,

  PRIMARY KEY (loan_nbr, change_date),

  archive_date DATETIME NULL,

  CHECK (change_date < archive_date));




-- Sample data.


INSERT INTO Loans VALUES (1, 100.00);

INSERT INTO Loans VALUES (2, 150.00);

INSERT INTO Loans VALUES (3, 120.00);

INSERT INTO Loans VALUES (4, 160.00);


INSERT INTO LoansHistory VALUES (1, 100.00, '20070501', NULL);

INSERT INTO LoansHistory VALUES (2, 95.00, '20070503', NULL);

INSERT INTO LoansHistory VALUES (3, 80.00, '20070506', '20070508');

INSERT INTO LoansHistory VALUES (3, 85.00, '20070508', NULL);




-- View to use for latest loans.


CREATE VIEW CurrentLoans (loan_nbr, loan_amount)


SELECT loan_nbr, loan_amount

FROM LoansHistory

WHERE archive_date IS NULL;




-- Insert changed rows.


-- SQL Server 2005.


INSERT INTO LoansHistory (loan_nbr, loan_amount)

SELECT loan_nbr, loan_amount

FROM Loans


SELECT loan_nbr, loan_amount

FROM CurrentLoans;


-- SQL Server 2000.


INSERT INTO LoansHistory (loan_nbr, loan_amount)

SELECT loan_nbr, loan_amount

FROM ( SELECT 'Live' AS Source,



       FROM Loans 

       UNION ALL

       SELECT 'History' AS Source,



       FROM CurrentLoans ) AS L

GROUP BY loan_nbr, loan_amount


   AND MIN(Source) = 'Live';


-- Update archive date for old history.

-- Runs in the same transaction as the insert of new history.


UPDATE LoansHistory


WHERE archive_date IS NULL


              FROM LoansHistory AS H

              WHERE H.loan_nbr = LoansHistory.loan_nbr

                AND H.change_date > LoansHistory.change_date);




-- Use the view for all reporting purposes.


SELECT loan_nbr, loan_amount

FROM CurrentLoans

ORDER BY loan_nbr;


-- Loans as of 2007-05-08.


SELECT loan_nbr, loan_amount

FROM LoansHistory

WHERE change_date <= '20070508'

  AND COALESCE(archive_date, CURRENT_TIMESTAMP) > '20070508';

The process is fairly simple with keeping two dates: change date for when the last change occurred (and that is the date when the row was inserted in history), and archive date which indicates that this row is archived and there is another more recent change. If the archive data is NULL it indicates that this is the most recent change. To simplify the querying process both dates have time portion set to midnight.

Cleaning Data with Recursive CTE

SQL Server 2005 added a great new feature: Common Table Expressions (CTE). And even better than that – recursive CTEs. That provides a new powerful tool to solve many SQL problems. One of the areas where recursive CTEs shine is the hierarchical data management.

Here is another side of the recursive CTEs – utilizing them for some common tasks like cleaning data. The problem: a table has a column with values that have invalid characters. The task is to replace all those invalid characters with a space. Unfortunately the REPLACE function does not support pattern matching and each character in the column has to be verified individually and replaced if it falls in the invalid range. The solution below utilizes a recursive CTE to walk though the ACSII table of characters and to replace the invalid characters in the column values.

-- Create test table.



  key_col INT PRIMARY KEY,

  text_col NVARCHAR(100));


-- Populate sample data.



INSERT INTO Foobar VALUES (2, N'~!102WXY&*()_Z');


-- Perform the cleanup with recursive CTE.


WITH Clean (key_col, text_col, ch)


(SELECT key_col,

        REPLACE(text_col, CHAR(255), ' '),


 FROM Foobar


 SELECT key_col,

        CASE WHEN

            CHAR(ch - 1) NOT LIKE '[A-Z]'

            THEN REPLACE(text_col, CHAR(ch - 1), ' ')

            ELSE text_col END,

        ch - 1

 FROM Clean

 WHERE ch > 1)

SELECT key_col, text_col

FROM Clean

WHERE ch = 1


On a side note – the recursive CTEs are not the best performers. Also, by default a CTE allows only 100 levels of recursion. The MAXRECURSION hint can be used to set higher level (a value between 0 and 32767; setting to 0 will remove the limit). Be aware that settings MAXRECURSION to 0 may create an infinite loop.

Here is a different method using utility table with numbers and FOR XML PATH, which is more effective:

WITH Clean (key_col, text_col)



        (SELECT CASE

                  WHEN SUBSTRING(text_col, n, 1) LIKE '[A-Z]'

                  THEN SUBSTRING(text_col, n, 1)

                  ELSE '.'


        FROM (SELECT number

               FROM master..spt_values

               WHERE type = 'P'

                AND number BETWEEN 1 AND 100) AS Nums(n)

        WHERE n <= LEN(text_col)

        FOR XML PATH('')) AS NVARCHAR(100)), '.', ' ')

 FROM Foobar)

SELECT key_col, text_col

FROM Clean;