DBAs are usually charged with the administration of Reporting Services, but are often short on guidance on how to go about such tasks as planning, documenting and troubleshooting those aspects that are specific to the SSRS service. Feodor gives some tips, custom SSMS reports, and useful TSQL queries, based on his experience.… Read more
SQL Server's functions are a valuable addition to TSQL when used wisely. Jeremiah provides a complete and comprehensive guide to scalar functions and table-valued functions, and shows how and where they are best used.… Read more
Are Common Language Runtime routines in SQL Server faster or slower than the equivalent TSQL code? How would you go about testing the relative performance objectively? Solomon Rutzky creates a test framework to try to answer the question and comes up with some surprising results that you can check for yourselves, and offers some good advice.… Read more
The basic texts for developing SQL code tend to leave unsaid the basic techniques for building routines such as stored procedures in T-SQL. Phil is well-known for his more lengthy and complex stored procedures, so we asked him to explain in more detail how he goes about developing things like that without the comfort of Visual Studio.… Read more
When William produced his second article on the free tool 'SQL Scripts Manager', revealing that it worked just as well with PowerShell and Python scripts as it does with TSQL, he thought that would be the end of the series. Oh no; in response to feedback, comes a small add-in called 'Script Generator' that makes a big difference to the speed of developing and producing new scripts.
… Read more
If you are still using the old Sybase DateTime datatype, it is a good idea to move your code to the more standard datatypes that were introduced in SQL Server 2008. Joe Celko explains why, and walks through some of the history of the TSQL way of storing and manipulating dates and times.
… Read more
The trouble with using BCP for reading data into SQL Server is that BCP is a command-line program. Fortunately, the same input functionality is available from TSQL within SQL Server. In fact there are two ways, and it is not always obvious which to use.… Read more
When one considers the amount of time and effort that Unit Testing consumes for the Database Developer, is surprising how few good SQL Server Test frameworks are around. tSQLt , which is open source and free to use, is one of the frameworks that provide a simple way to populate a table with test data as part of the unit test, and check the results with what should be expected. Sebastian and Dennis, who created tSQLt, explain.
… Read more
With SQL Server 2005, SQL Server introduced some of SQL's window functions, that apply, not to the full set, but a partitioned 'window'. Although the ROW_NUMBER, RANK, NTILE and DENSE_RANK bring great power to TSQL, the full versatility will not be available until SQL Server delivers the full implementation. As usual, Robert Sheldon explains all.
… Read more
Whatever happened to the idea that programming in TSQL can be fun? A Simple-Talk reader contributes an article to remind us all that there is more to TSQL than wrestling with DMVs and pumelling recalcitrant correlated subqueries.… Read more
Phil shows how to start squeezing powerful magic from SSMS for doing a detailed exploration of the metadata of your routines and tables, in this third part to his series on exploring your database schema with SQL.… Read more
We can all recognise good-quality database code: It doesn't break with every change in the server's configuration, or on upgrade. It isn't affected by concurrent usage, or high workload. In an extract from his forthcoming book, Alex explains just how to go about producing resilient TSQL code that works, and carries on working.… Read more
Phil Factor's SQL Speed Phreak challenge is an event where coders battle to produce the fastest code to solve a common reporting problem on large data sets. It isn't that easy on the spectators, since the programmers don't score extra points for commenting their code. Mercifully, Kathi is on hand to explain some of the TSQL coding secrets that go to producing blistering performance. … Read more
Phil returns to the old subject of 'Getting text-based data in and out of SQL Server'. He shows various easy ways of getting a file listings of directories from the file system, shows how one can access the Shell automation Objects, and demonstrates several ways of reading or writing data between database and f… Read more
It is a simple routine that we all need to use occasionally; parsing a delimited list of strings in TSQL. In a perfect relational world, it isn't necessary, but real-world data often comes in a form that requires one of the surprising variety of routines that Anith Sen describes, along with sage advice about their use.… Read more
In which Robyn and Phil continue with their popular series on TSQL String User-functions. In this final episode, they pull together the themes from their TSQL String Array Workbench and String User Function workbench, to provide a simple TSQL string-handling package.… Read more
Robyn and Phil show how to use XML-based arrays to make string handling easier in SQL Server 2005/2008, and illustrate the techniques with some useful functions, one of which copies the PHP str_Replace function.… Read more