Sparse Columns
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();
}
}
}
}
}