稀疏列是对 Null 值采用优化的存储方式的普通列。稀疏列减少了 Null 值的空间需求,但代价是检索非 Null 值的开销增加。当至少能够节省 20% 到 40% 的空间时,才应考虑使用稀疏列。
当您连接到 SQL Server 2008 或更高版本的服务器时,SQL Server JDBC Driver 3.0 支持稀疏列。可以使用 SQLServerDatabaseMetaData.getColumns、SQLServerDatabaseMetaData.getFunctionColumns 或 SQLServerDatabaseMetaData.getProcedureColumns 确定哪个列是稀疏列以及哪个列是列集列。
列集是返回非类型化 XML 形式的所有稀疏列的计算列。当表中有很多列、大于 1024 或分别对这些稀疏列进行操作很烦琐时,应考虑使用列集。列集最多可以包含 30,000 个列。
示例
说明
此示例说明如何检测列集。它还显示如何分析列集的 XML 输出,以便从稀疏列获取数据。
所列的第一个代码部分是您应该对服务器运行的 Transact-SQL。
所列的第二个代码部分是 Java 源代码。在编译应用程序之前,应更改连接字符串中服务器的名称。
代码
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
代码
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(); } } } } }