I work in data warehousing in banking, where we handle large amounts of data and have to change complex sql statements to fix bugs etc.. Individual queries tend to get bigger and bigger. The danger is that they become so complex that fixing one bug can break other parts of the logic. Continue reading Validation of Data Using Set-Based Sql Operations
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
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
Back in the days of SQL Server 6.5 I developed a query to search stored procedures for a given string, which I used to email to myself on every assignment that I took up. It’s now well out of date (and I’ve lost the file) so I’ve decided to do a quick rewrite, this time including text from other types of objects such as views. Continue reading Finding text in SQL Server Objects By Querying System Tables