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 […]
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 […]
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’, T.name AS ‘Table Name’, C.name […]
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 […]
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 […]
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 […]