In this short article, Greg Larsen performance benchmarks alternative CLR-based and T-SQL stored procedure solutions for retrieving operating system file information. He also assesses other factors to be considered when choosing the correct implementation for your system, such as ease of development and maintenance.… Read more
SQL Server provides several "standard" techniques by which to read and write to files but, just occasionally, they aren't quite up to the task at hand - especially when dealing with large strings or relatively unstructured data. Phil Factor provides some T-SQL stored procedures, based on use of the FileSystem Object (FSO), that may just get you out of a tight corner...… Read more
For those times when you absolutely, positively got to perform a cross tab query in SQL, Keith Fletcher's T-SQL stored procedure will allow you to do it "on the fly". You can add it to your database and start cross tabbing immediately, without any further setup or changes to your SQL code. Check it out, and then take the cross tab challenge. If you can compile a cross tab report that displays the order value by customer, by quarter, using the stored procedure, you may win a much-coveted prize!… Read more
A process in a complex database occasionally, and apparently randomly, manages to put table locks on vital tables. Several applications are brought to a complete halt. Armed with a T-SQL stored procedure, a violin and a keen investigative spirit, Pop Rivett tracks down the rogue SPIDs that are causing all the problems...… Read more
Cursors and iterations are both renowned for slowing down Transact SQL code, but sometimes seem unavoidable. In this workbench, Robyn Page and Phil Factor demonstrate some set-based techniques for string manipulation and time interval-based reporting, which use helper tables rather than the dreaded cursor.… Read more
Robyn Page and Phil Factor present practical T-SQL techniques for controlling access to sensitive information within the database, and preventing malicious SQL injection attacks.… Read more
The need to produce Excel reports from SQL Server is very common. Here, Robyn Page and Phil Factor present practical techniques for creating and manipulating Excel spreadsheets from SQL Server, using linked servers and T-SQL. The pièce de résistance is a stored procedure that uses OLE Automation to allow you full control over the formatting of your Excel report, and the ability to include sums, ranges, pivot tables and so on.… Read more
Is your IT project in trouble? Do you need to create a smokescreen so that no-one can pin the blame on you? No problem! Marvel at Phil Factor's versatile use of T-SQL functions in creating the "ultimate excuse database" and then start generating your excuses immediately!
… Read more
William Brewer investigates an unsung hero in SQL Server, extended properties, and shows how to use them to create self-documenting SQL Server databases.… Read more
Itzik Ben-Gan is a writer and mentor, he writes a monthly column for SQL Server Magazine and co-authored Advanced Transact-SQL for SQL Server 2000.… Read more
