One of the common task that I have come across is the need to convert a list of results into a comma separated list. There are many ways to use this, one if which is in reports. Download the AsCSV.sql sample here. Often times I have told developers, here is how you do it, and if you Google on it …
Converting part of a result set to a comma separated list Read more »
Join us May 11th @ 12 pm PST – Steve Stedman will teaching a FREE lesson on SQL Server Join Types. TSQL joins – what’s the difference, and how can you use joins correctly? Sometimes an inner join is the right join type to use, other times the left outer join, right outer join, full outer join, cross join or …
Occasionally you need to take inventory of how much free space each database has. You can find this quickly and easily in Database Health Monitor, but if you want to get it programmatically you can do it with the following query. This query has been tested on SQL Server 2000, 2005, 2008, 2008R2, 2012, 2014, 2016, 2017, and 2019. This …
The following is chapter 7 from my Common Table Expressions book in its entirety. I can remember the first time that I worked on data paging code. I had to page through web site search results 20 at a time on a given web page. My task was to understand how it had been written and to do some bug fixing. After reviewing …
The following is chapter 5 from my Common Table Expressions book in its entirety. READER NOTE: Please run the CTEBookSetup.sql script in order to follow along with the examples in this chapter. The script mentioned in this chapter is available at http://SteveStedman.com. Hierarchical CTEs When looking at the table of contents for a book it is generally represented as an outline of some kind utilizing indentation, …
The following is chapter 4 from my Common Table Expressions book in its entirety. READER NOTE: Please run the CTEBookSetup.sql script in order to follow along with the examples in this chapter. The script mentioned in this chapter is available at http://SteveStedman.com. Recursive CTEs Have you ever solved a maze puzzle on paper, or had the opportunity to visit a …
I received this question from someone visiting the blog, and realized that rather than just replying to them, it might be good to put the options out there for everyone to see. Hello, I came across your website and it seems that you are an SQL expert, I’m just getting started. I was wondering can you please give some guidance on …
So often I run into examples where someone is inserting a row and trying to get one of the values from that row that was just inserted. For instance an integer identity id value or just to be able to see the row after it was inserted. Introducing the OUTPUT clause, and often overlooked or forgotten tool in SQL Server. …
The following is Chapter 3 from my Common Table Expressions book it its entirety. CTEs Instead of Derived Tables One time I was handed an eight page print out with an 8pt font showing a single SQL statement. It was handed to me with a question about why it was not returning the right data. After looking the query over …
When working with SQL Server transactional replication, one of the common checks to see if things look healthy is to insert a tracer token into the replication data stream and see how long it takes to get to the subscribers. This is a process that can be very tedious if you have multiple publications to check, as you need to …