Optimized SQL for tree structures

by

It really depends on how you are going to access the tree.

One clever technique is to give every node a string id, where the parent’s id is a predictable substring of the child. For example, the parent could be ’01’, and the children would be ‘0100’, ‘0101’, ‘0102’, etc. This way you can select an entire subtree from the database at once with:

SELECT * FROM treedata WHERE id LIKE '0101%';

Because the criterion is an initial substring, an index on the ID column would speed the query.

More Related Contents:

  1. Sql to select the rows and ignore the duplicate userid in SQL Server [closed]
  2. How to concatenate text from multiple rows into a single text string in SQL Server
  3. What’s the best practice for primary keys in tables? [closed]
  4. How do you list the primary key of a SQL Server table?
  5. Calculating distance between two points (Latitude, Longitude)
  6. SQL Server check case-sensitivity?
  7. Concat groups in SQL Server [duplicate]
  8. Multiplication aggregate operator in SQL
  9. How to update Identity Column in SQL Server?
  10. SQL Server: Get table primary key using sql query [duplicate]
  11. How to determine the number of days in a month in SQL Server?
  12. Concatenate values based on ID
  13. Finding duplicate rows in SQL Server
  14. Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
  15. Detect consecutive dates ranges using SQL
  16. varbinary to string on SQL Server
  17. how to declare global variable in SQL Server..?
  18. Scope of temporary tables in SQL Server
  19. Query comparing dates in SQL
  20. Convert text value in SQL Server from UTF8 to ISO 8859-1
  21. How to write UPDATE SQL with Table alias in SQL Server 2008?
  22. SQL Server 2008- Get table constraints
  23. Restrict an SQL Server connection to a specific IP address
  24. Why are logical reads for windowed aggregate functions so high?
  25. Getting Dates between a range of dates
  26. Select corresponding to row from the same table SQL Server
  27. How do I expand comma separated values into separate rows using SQL Server 2005?
  28. Backup a single table with its data from a database in sql server 2008
  29. Try_Convert for SQL Server 2008 R2
  30. SQL Server variable scope in a stored procedure

Leave a Comment