This is the last posting in my brief discussion of SQL tricks. I’m dealing here with an issue which causes quite a bit of confusion to newer developers. What’s the point in having LEFT and RIGHT joins defined in the SQL standard, how do you decide which to use, and is there a way to keep things simple? Continue reading Sql Tricks for Tricky Logic (3)
I’ve been working with Linq in .NET lately and there have been a few posts I would have liked to add but since I said I was going to blog SQL tricks I’m putting other topics to one side for the moment.
So… another situation where it would be useful to have an SQL trick is the case of the filtered outer join. Continue reading SQL Tricks for Tricky Logic (2)
One reason occasionally offered for using dynamic SQL widely, or even for keeping business logic out of database stored procedures, is the difficulty of coding optional filters in SQL. Sometimes we may want a row from table X where column A = this, column B = this and column C = this. Other times we may want all the rows where column A = this but we’ll accept any value in columns B and C. Continue reading SQL Tricks for Tricky Logic
There’s now a wide range of objects that a database developer can use to solve problems. For this post or two I’m considering certain objects (as implemented in MS SQL Server) and how I think they are probably best used . Continue reading Database Object Types – Some Design Considerations
Late in March I released (started using a lot) a small C#/SQL Server project. It utilises the new User-Defined Table Type in SQL Server 2008 and the “structured” parameter type in ADO.NET. This allows a table originated on the client to be passed in to a stored procedure as a parameter. Oracle has had table data types for a while of course, and there are still some limitations to the SQL Server version, but it’s certainly very easy to use. Continue reading Using the User-defined Table types with ADO.Net structured parameter type
I’d like to enforce the user-defined table type returned by a sql function, making the following code possible (I haven’t of course defined the function so it won’t execute): Continue reading Enforcing the Table Type Returned by a Sql Function
I recently got around to setting up Integrated CLR in my SQL Server 2008 installation, and coding a couple of stored procedures. These notes reflect my first look and a minor “gotcha” to be aware of. Continue reading CLR Stored Procedures in SQL Server 2008