Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non null values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent.

The SQL Server JDBC Driver 3.0 supports sparse columns when you connect to a SQL Server 2008 (or later) server. You can use SQLServerDatabaseMetaData.getColumns, SQLServerDatabaseMetaData.getFunctionColumns, or SQLServerDatabaseMetaData.getProcedureColumns to determine which column is sparse and which column is the column set column.

Column sets are computed columns that return all sparse columns in untyped XML form. You should consider using column sets when the number of columns in a table is large or greater than 1024 or operating on individual sparse columns is cumbersome. A column set can contain up to 30,000 columns.

Example

Description

This sample demonstrates how to detect column sets. It also shows how to parse a column set's XML output to get the data from the sparse columns.

The first code listing is the Transact-SQL you should run on the server.

The second code listing is the Java source code. Before you compile the application, change the name of the server in the connection string.

Code

use AdventureWorks
CREATE TABLE ColdCalling
(
ID int IDENTITY(1,1) PRIMARY KEY,
[Date] date,
[Time] time,
PositiveFirstName nvarchar(50) SPARSE,
PositiveLastName nvarchar(50) SPARSE,
SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
);
GO

INSERT ColdCalling ([Date], [Time])
VALUES ('10-13-09','07:05:24')
GO
      
INSERT ColdCalling ([Date], [Time], PositiveFirstName, PositiveLastName)
VALUES ('07-20-09','05:00:24', 'AA', 'B')
GO
      
INSERT ColdCalling ([Date], [Time], PositiveFirstName, PositiveLastName)
VALUES ('07-20-09','05:15:00', 'CC', 'DD')
GO

Code

import java.sql.*;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.xml.sax.InputSource;

import java.io.StringReader;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class SparseColumns {

   public static void main(String args[]) {
      final String connectionUrl = "jdbc:sqlserver://my_server;databaseName=AdventureWorks;integratedSecurity=true;";
      
      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;
      
      try {
         conn = DriverManager.getConnection(connectionUrl);
         
         stmt = conn.createStatement();
         // Determine the column set column
         String columnSetColName = null;
         String strCmd = "SELECT name FROM sys.columns WHERE object_id=(SELECT OBJECT_ID('ColdCalling')) AND is_column_set = 1";
         rs = stmt.executeQuery(strCmd);
         
         if (rs.next()) {
            columnSetColName = rs.getString(1);
            System.out.println(columnSetColName + " is the column set column!");
         }
         rs.close();

         rs = null; 
             
         strCmd = "SELECT * FROM ColdCalling";
         rs = stmt.executeQuery(strCmd);
            
         // Iterate through the result set
         ResultSetMetaData rsmd = rs.getMetaData();
         
         DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
         DocumentBuilder db = dbf.newDocumentBuilder();
         InputSource is = new InputSource();
         while (rs.next()) {
            // Iterate through the columns
            for (int i = 1; i <= rsmd.getColumnCount(); ++i) {
               String name = rsmd.getColumnName(i);
               String value = rs.getString(i);
   
               // If this is the column set column
               if (name.equalsIgnoreCase(columnSetColName)) {
                  System.out.println(name);
                  
                  // Instead of printing the raw XML, parse it
                  if (value != null) {
                     // Add artificial root node "sparse" to ensure XML is well formed
                     String xml = "<sparse>" + value + "</sparse>";
   
                     is.setCharacterStream(new StringReader(xml));
                     Document doc = db.parse(is);
   
                     // Extract the NodeList from the artificial root node that was added
                     NodeList list = doc.getChildNodes();
                     // This is the <sparse> node
                     Node root = list.item(0); 
                     // These are the xml column nodes
                     NodeList sparseColumnList = root.getChildNodes(); 
   
                     // Iterate through the XML document
                     for (int n = 0; n < sparseColumnList.getLength(); ++n) {
                        Node sparseColumnNode = sparseColumnList.item(n);
                        String columnName = sparseColumnNode.getNodeName();
                        // Note that the column value is not in the sparseColumNode, it is the value of the first child of it
                        Node sparseColumnValueNode = sparseColumnNode.getFirstChild();
                        String columnValue = sparseColumnValueNode.getNodeValue();
                        
                        System.out.println("\t" + columnName + "\t: " + columnValue);
                     }
                  }
               } else {   // Just print the name + value of non-sparse columns
                  System.out.println(name + "\t: " + value);
               }
            }
            System.out.println();//New line between rows
         }
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         if (rs != null) {
            try {
               rs.close();
            } catch (Exception e) {
               e.printStackTrace();
            }
         }
         if (stmt != null) {
            try {
               stmt.close();
            } catch (Exception e) {
               e.printStackTrace();
            }
         }
         if (conn != null) {
            try {
               conn.close();
            } catch (Exception e) {
               e.printStackTrace();
            }
         }
      }
   }      
}

Example

Description

Code

Code

See Also